MySQL loop in upgrading from 1.1RC1 to 1.1.2?

[zarth]

My issue seems to be not too different from this one.

For the longest time, I've been stuck at the following:

It shows this:

Code Select Expand


Upgrading log system...
    Creating log table indexes (this might take some time!)... done.
    Preparing log table upgrade... done.
    Converting log tables (this might take some time!)... done.
    Updating last message IDs for boards. Incomplete.


..and then skips to a new page and begins with the blue progress bar.

Code Select Expand


Upgrading...
Upgrading log system...
Incomplete.

Not quite done yet!
This upgrade has been paused to avoid overloading your server. Don't worry, nothing's wrong - simply click the continue button below to keep going.

Upgrade Progress: 78.52%
Current Step ("Updating Last Board ID") Progress: 65.22%


The URL at this point is something like:

upgrade.php?step=2;maint=1;substep=117;s=1;bdi=94

Okay. And then the browser stopped responding. I open status.php to check it out, and find that it's repeating two queries, over and over and over.

Code Select Expand


ALTER IGNORE TABLE smf_messages
DROP INDEX ipIndex


That's the one it starts out with. It runs for about 240 seconds, and then proceeds to do this query:

Code Select Expand


ALTER TABLE smf_messages
ADD INDEX ipIndex (posterIP(15), ID_TOPIC)


It loops this for a while, sometimes 8 or 9 times, each query taking 240s+ to complete, while others, it only does it once or twice before the status.php shows no queries at all. I thought the fact that no queries were being sent meant that it had completed, so I go back and refresh the page holding the upgrade.php...... But no. It starts over with the same two queries above, repeating them until I want to rip my hair out.

Refreshing the upgrade.php only made the queries start over, so I opened a new window and executed upgrade.php there. It zapped right up to where I left it of last time, did a few really fast queries and then began with the ADD INDEX/DROP INDEX again.

Code Select Expand


SELECT MIN(ID_MSG)
FROM smf_messages
WHERE posterTime >= 1181262851


That's the preliminary queries prior to the looping ones. It runs the query a good 5-10 times, each one completed in under a second, and with a different number each time. After it completes those, it begins anew with the ADD/DROP.

What can I do?

[zarth]

The problem persists, though it is no longer urgent. MySQL went nowhere in 12 hours, so I have aborted and am now restoring my backups.

Although, if someone knowledagble happens to see this thread, I would very much appreciate some feedback, as I want to upgrade SMF.

[SleePy]

The smf_messages table is big so droping and creating an index might take some time.
if it has done it already (by looking at the database) you can remove or comment out that section from the upgrade_1-1.sql to have it skip it and continue on.

[zarth]

It could take a fair amount of time, yes, I realize that. But 12+ hours? smf_messages contains 18,493 records and is 83.2mb in size. Maybe I'm being a fool, but those numbers don't seem correct.

However, if we encounter it again, I'll be sure to comment out that section. Thanks.

[Sarge]

Do a backup of the database in its current form. Then repair the database tables as described here:
http://www.simplemachines.org/community/index.php?topic=176088.msg1129597#msg1129597

Note: Database backup can also be performed from cPanel or phpMyAdmin or even the create_backup script attached to this post:
http://www.simplemachines.org/community/index.php?topic=18350.msg168327#msg168327

Then try upgrade.php again. If it still times out or lasts longer than expected, ask your host if they can check the logs about anything unusual with either MySQL server or your account connections to it. It wouldn't hurt to point them to this topic too to give them a more specific idea about what to look for.