1

I use LOAD DATA INFILE from C# using the MySQLBulkLoader object of the MySQL Connector for .NET, to load 24 GB of data into a MySQL 5.5 table.

I am wondering whether in case of failure (for any reason, even warnings), the bulk insertion would be properly rolled back as if it had been done within a transaction, or if I should expect to see the first successful records already commited.

The relevant page on the MySQL manual is not very informative in this regard.

Erwin Mayer
  • 18,076
  • 9
  • 88
  • 126
  • possible duplicate of [Can MySqlBulkLoader be used with a transaction?](http://stackoverflow.com/questions/4974934/can-mysqlbulkloader-be-used-with-a-transaction) – Preet Sangha Sep 20 '11 at 20:26
  • The answer to this other question is "no", obviously. I had indeed read it before posting. Here the question is whether LOAD DATA INFILE is treated as a standalone transaction by MySQL, per se. – Erwin Mayer Sep 20 '11 at 20:28
  • 1
    [this](http://www.mysqlperformanceblog.com/2008/07/03/how-to-load-large-files-safely-into-innodb-with-load-data-infile/) might be relevant – nos Sep 20 '11 at 21:25
  • Thanks, I had read it before posting, it says "There are several problems with the very large transaction caused by the single statement." which suggests the LOAD DATA INFILE statement would be treated as a transaction, but it seems like a guess. – Erwin Mayer Sep 20 '11 at 21:41
  • 1
    With InnoDB, it should be a transaction. But with MyISAM tables, it obviously cannot be transactional – nos Sep 20 '11 at 21:52

1 Answers1

3

MySQL in auto-commit mode (the default) tries to wrap every single statement in its own transaction.

If you use InnoDB then yes
If you use MyISAM then no

Johan
  • 74,508
  • 24
  • 191
  • 319