MySQL failed to start after killed a long running “LOAD DATA INFILE” process

MySQL

I killed a MySQL process which is running a LOAD DATA INFILE command to load a 9GB .csv file into a table in a InnoDB database.

Now, when I run mysql DATABASE, it just stopped and showed

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

What can I do now? I guess it's doing some repair and cleanning work caused by the interrupted LOAD DATA INFILE command.

Solution:

I tried service mysql restart but I decided to kill all mysqld processes forcibly after restarting took more than 1 hours and still no clue how many more hours ahead to complete.

Then I used service mysql start to run MySQL server again. Fortunately the InnoDB rebuild process is quite fast, took about 15 minutes to complete, no data loss.

Best Answer

Reading table information can be an intensive process depending on the size or load on the database.

While you killed the system process running the MySQL command, you likely did not have the MySQL connection terminate.

Run mysql -A DATABASE or just mysql, look at the process list with show full processlist; and if you want the connection to terminate run kill processnumber;.

Related Topic