EMERGENCY! Update Statement for critical mysql production database now running for 18 hours, need help.
- by Tim
We have a table with 500 million rows. Unfortunately, one of the columns was int(11), which is a signed int, and it was an incrementing value that just rolled over the 2.1 billion magic number. This immediately caused downtime for about 10.000 users. We discussed many solutions, and decided that we could just roll back this value safely, by say, a billion. But we had to roll it back for every row.
Here is what we did:
update Table1 Set MessageId = case when MessageId < 1073741824 then 0 else MessageId - 1073741824 end;
I tested this on a table with 10 million rows and it took 11 minutes. So I assumed the larger table would take 550 minutes, or 9 hours. This was going to be our biggest downtime in 3 years. (We're a startup). It's now going on 18 hours.
What should we do?
Please don't say what we should have done. I think we should have updated a few million rows at a time.
Is there a way we can see progress? Could Mysql have hung? Using mysql 5.0.22.
Thanks!
