Large Data File

MySQL & MariaDB Other MySQL® Questions
1

Hallo,

I have developed a web application where users will have to import data into an Innodb table from csv files. The files has about 15000 records. Am using a mysql procedure where the procedure is called after reading each one of the lines in the data file. My problem is that incase of a failure (e.g. power failure) i would like to use the ACID property such that the process is undone if the entire file is not imported.

Any help will be appreciated.

2

Could you give some more information? Like the definition of the procedure?

If you use InnoDB tables, and the insert is done in one statement (Multiple Rows Insert Statement Syntax), the Insert will be done in a whole ore not at all.

So after processing the insert statement, all data has been inserted, or in case of an error, none will have been inserted.
(This is because each query is done in its own transaction when using InnoDB Tables)

If you do need more than one statement, you could manually start a transaction in the beginning, commit it at the end if everything goes fine, or roll it back (not commit it) in case of an error.

(See “13.2.10. InnoDB Transaction Model and Locking” in the Manual)