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]
