Hi, i did it. If you have myisam tables tables rather than innodb say it. if you have specific os, say it.
i think you should elaborate, or read carrefully dev.mysql.com/doc Mathias Selon °l||l° Jinxed °l||l° <[EMAIL PROTECTED]>: > 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]
