i used start transaction before using SET AUTOCOMMIT=0; also i dont see any
difference between the two. if there is please elaborate.
----- Original Message ----- .
From: <[EMAIL PROTECTED]>
To: "°l||l° Jinxed °l||l°" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Tuesday, June 07, 2005 3:17 AM
Subject: Re: LOAD DATA INFILE with INNODB
> Hi,
> you transaction is implicit, so there has been an autocommit.
>
> Look at this example !
>
>
> mysql> start transaction;
> ^^^^^^^^^^^^^^^^^^
>
> mysql> load data infile 'd:\\ldfile.txt' into table ldfile;
> Query OK, 3 rows affected (0.00 sec)
> Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
>
> mysql> select * from ldfile;
> +------+
> | i |
> +------+
> | 1 |
> | 2 |
> | 3 |
> +------+
> 3 rows in set (0.00 sec)
>
> mysql> rollback;
> Query OK, 0 rows affected (0.03 sec)
>
> mysql> select * from ldfile;
> Empty set (0.00 sec)
>
>
> This a not a good idea if the file is big. Ideally, truncate the table if
there
> has been a problem witha big file.
>
>
> Mathias
>
>
> Selon °l||l° Jinxed °l||l° <[EMAIL PROTECTED]>:
>
> > hi, I have been pulling my hair for last couple of days.i want to put
few sol
> > statements in TRANSACTION BLOCK. all the tables involved are of type
innodb.
> > the first SQL statement in the block is LOAD DATA INFILE. inside the
block (
> > using PHP ) i am checking for errors and incase of error i want to
rollback.
> > but strangely when i tried to rollback it just wouldn't do. i thought
may be
> > PHP is giving problems. then i did this
> >
> > ===========================================================
> > SET AUTOCOMMIT=0;
> > Query OK, 0 rows affected (0.00 sec)
> >
> > select @@autocommit as autocommit;
> > +-----------------+
> > | autocommit |
> > +-----------------+
> > | 0 |
> > +-----------------+
> > 1 row in set (0.00 sec)
> >
> > LOAD DATA INFILE '1116560400.csv' INTO TABLE tbltemp FIELDS TERMINATED
BY ','
> > LINES TERMINATED BY '\r\n' (tmp_crdd_no,tmp_serial_no,tmp_date);
> > Query OK, 27265 rows affected (4.48 sec)
> > Records: 27265 Deleted: 0 Skipped: 0 Warnings: 0
> >
> > rollback;
> > Query OK, 0 rows affected (0.00 sec)
> >
> > ===========================================================
> >
> > when i looked in tbltemp i found out that the CSV file has been loaded
> > although i rolled back the transaction. i used insert statement and
rolled
> > back with no problem, so the problem was narrowed down to LOAD DATA
INFILE. i
> > have read about LOAD DATA INFILE and found nothing about this strange
> > behavior. is there anything that i am missing out?
> >
> > Regards
> > Haseeb Iqbal
>
>
___________________________________________________________
Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail
http://uk.messenger.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
