During a INSERT INTO new_table (f1, f2, ..., fn) (SELECT f1, f2, ..., fn FROM table) I clicked the new_table in php-myadmin which ran a simple
SELECT * FROM new_table LIMIT 0, 30 which ofcourse couldn't be completed because new_table was not ready.
I tried Killing the query, but it just kept itself in
Kill state and therefore I assume it caused some sort of rollback in
The copy data query is for a 200GB table and was running for about 10hours till this interruption (I think it was almost finished). I tried to also kill that query thinking it will free things up, but again it got stuck.
I'm seeing this in
SHOW ENGINE INNODB STATS
---TRANSACTION 5254EC173, ACTIVE 39375 sec rollback mysql tables in use 2, locked 2 ROLLING BACK 4169557 lock struct(s), heap size 371276216, 34345786 row lock(s), undo log entries 26939759
Is there anything I can do? Any time estimation? Will it try to rollback the whole table and then try to return that whole data? Will it try to use diskspace for all these operations? Is there anything to avoid the lock? would it be a good idea to do
mysql restart or will this just start the operation over again?