hi, yes you did it and i failed to do so. thats why i am here. i am using MYSQL 5.0.3 beta-standard with RedHat 9 , the test involves only innodb as i mentioned in the very first post, i have read docs and it says that there are 3 ways i can start a transaction and i tried all three i.e. begin work statement, start transaction and SET AUTOCOMMIT=0; regards
----- Original Message ----- From: <[EMAIL PROTECTED]> To: "°l||l° Jinxed °l||l°" <[EMAIL PROTECTED]> Cc: <mysql@lists.mysql.com> Sent: Tuesday, June 07, 2005 11:08 AM Subject: Re: LOAD DATA INFILE with INNODB > 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 > > > > ___________________________________________________________ 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]
