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..