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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


    ---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 '14 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.

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

Please seem my answer to your other similar question involving innodb_force_recovery = 4

share|improve this answer

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