1

I use a batch file to load my tables from CSV files using numerous LOAD DATA INFILE commands. When an error occurs during one of a table's LOAD statements, rollback occurs only for that table with 0 rows, while the data in other tables remains committed. Please advise on how to make this transaction-bound. I discovered a similar article here MySQL: Executing LOAD statements inside Transaction from a sql script file, however there is no answer.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Mandy Li
  • 21
  • 1
  • 1
    "Batch file" meaning what, specifically, here? – tadman Aug 14 '23 at 14:25
  • a set of load data infile, 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; – Mandy Li Aug 14 '23 at 14:36
  • If one load failed, 0 rows inserted, but the rest of the tables still loaded. what I want to do is, if any of the load failed, nothing will load, tables and data remain the same as the last load. – Mandy Li Aug 14 '23 at 14:39
  • 1
    Unlike other databases, MySQL does not have transaction support for schema changes, so there's no way to lock this all inside a transaction. You may need to unwind manually. – tadman Aug 14 '23 at 14:40
  • 1
    @tadman LOAD DATA is not a schema change. – Barmar Aug 14 '23 at 16:22
  • 1
    What storage engine are you using? The documentation mentions that `LOAD DATA` causes an implicit commit with NDB. – Barmar Aug 14 '23 at 16:24
  • I don't know what storage engine I am using. How to find out? – Mandy Li Aug 14 '23 at 16:30
  • Please add details of your "batch file", and how you are executing it, to your question. If using `mysql < your_batch_file` then execution will stop on error, causing a rollback. You can run `show engines;` to see which ones are available, or `select table_name, engine from information_schema.tables where table_schema = 'your_db_name' and table_type = 'BASE TABLE';` to list the engine for each table in your db. – user1191247 Aug 14 '23 at 17:05

0 Answers0