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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]