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:
-------------- ROW OPERATIONS -------------- 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 ---------------------------- END OF INNODB MONITOR OUTPUT ============================
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