No queries working on table after LOAD DATA INFILE


March 2019


21 time


I've tried to upload a large CSV file (14.8GB) to a MySQL DB using InnoDB engine and the LOAD DATA INFILE command. The query was completed since InnoDB status shows the total number of rows were inserted:

 1 queries inside InnoDB, 0 queries in queue
 1 read views open inside InnoDB
 Process ID=5592, Main thread ID=0000000000001D80 , state=sleeping
 Number of rows inserted 200370545, updated 387, deleted 6, read 12473
 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

After the query was completed, before doing any operation on the table, an error popped up in MYSQL Workbench ("Index out of range") and I force-closed the program since it would not respond.

Now, any query that I try on this table gets stuck on "Sending data" state, even the simplest SELECT * FROM table

4   event_scheduler localhost       Daemon  28941   Waiting on empty queue  
27  root    localhost:62797 truck_database  Sleep   820     
28  root    localhost:62798 truck_database  Query   1339    Sending data    SELECT * FROM gps_data LIMIT 10
29  root    localhost:62800 truck_database  Sleep   130     
30  root    localhost:62801 truck_database  Query   0   starting    show processlist

I have previously loaded this same CSV file to another MySQL server and it works just fine over there. Only difference is that this time I tried to upload the whole file in one time instead of cutting it into smaller chunks.

It seems that the table is damaged in some way, maybe after forced closing Workbench. Is there any way I can check if this is the case? The only command I know is SHOW TABLE STATUS and this is the result I get:

gps_data    InnoDB  10  Dynamic 134694075   141 19028508672 0   0   3145728     2019-03-07 17:21:28         utf8mb4_0900_ai_ci                  

Also it might be relevant to know that I modified the InnoDB_buffer_pool_size to 5G before loading the data and I have just set it back to 500M. This is the only modification I did on my.ini

0 answers