Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 new_table.

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?

share|improve this question

closed as off-topic by RolandoMySQLDBA, Phil, swasheck, Max Vernon, ypercube May 16 at 18:41

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – RolandoMySQLDBA, Phil, swasheck, Max Vernon, ypercube
If this question can be reworded to fit the rules in the help center, please edit the question.

1  
Before you make any hasty actions like restarting the service, I suggest you calm down and wait for some answers here. The rollback is probably going to take a few hours, probably 10 or even more. It could even be days. But restarting the service will probably cause even more delay. –  ypercube Nov 14 '13 at 20:49
    
Thanks for that, will wait. Does it even make sense that the simple select query from new_table caused such a bad situation? –  Noam Nov 14 '13 at 20:51
    
I don't think that was the cause. –  ypercube Nov 14 '13 at 20:54
    
And no simple way to restart MySQL while saying "Don't Rollback" ? –  Noam Nov 14 '13 at 20:59
    
Well, no. You killed the query, there is no "Unkill" as far as I know. –  ypercube Nov 14 '13 at 21:03

1 Answer 1

Please seem my answer to your other similar question involving innodb_force_recovery = 4 http://dba.stackexchange.com/a/53355/30545

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.