4

I want to load my tables from some csv files. I want all the data import to be transaction bound i.e., if error occurs in any of the data import, all records loaded in other tables are also rolled back. The load statements are executed from a sql file that looks like this:

SET AUTOCOMMIT=0;
START TRANSACTION;
LOAD DATA INFILE 'file1.csv' INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '"';
LOAD DATA INFILE 'file2.csv' INTO TABLE table2 FIELDS TERMINATED BY ',' ENCLOSED BY '"';
.
.
.
LOAD DATA INFILE 'fileN.csv' INTO TABLE tableN FIELDS TERMINATED BY ',' ENCLOSED BY '"';
COMMIT;

But whenever an error is occuring during one of the LOAD statements for a table, rollback happens only for that table and the data in other tables stay committed. Please suggest what to do to make this transaction-bound..

Pratap
  • 655
  • 2
  • 10
  • 20
  • I don't know of a solution, but I can comment that `LOAD DATA` intentionally does not have a lot of the overhead that a full database has, precisely in order to be able to run fast. This might mean that transaction management which you want isn't available. – Tim Biegeleisen Mar 29 '17 at 09:00
  • What storage engine are you using? `LOAD DATA` causes an implicit commit for `NDB` storage engine. This shouldn't happen for InnoDB. – Barmar Aug 14 '23 at 16:16

0 Answers0