update to RC2 - "99.99% complete"... and then DEAD..

[Enc0der]

Hi

I'm trying to update my RC1 forum, with more than 1.3 million messages.. (1GB database for SMF...)
I'm getting stuck on: upgrade.php?step=2;maint=1;substep=121;s=1;m=1351000
that's a "99.99% complete" of the 'log database' update...

The query (from the status.php file) is:

Code Select Expand

SELECT b.ID_BOARD, IF(b.lastUpdated = 0, 0, IFNULL(MIN(m.ID_MSG), 0)) AS ID_MSG
FROM boards AS b
LEFT JOIN messages AS m ON (m.posterTime >= b.lastUpdated)
GROUP BY b.ID_BOARD

And it takes about 500 seconds to complete (again, I know that from status.php), so the browser times-out....
I tried it twice, with the same results. 

any help ?
thanks!

[Grudge]

I'll rewrite that part of the upgrade script for you again, please bear with me a few moments.

[Enc0der]

great, thanks!

[Grudge]

OK, Can you try the attached?

If you still have the old script active in the browser you should be able to upload the attached script and hit refresh on the browser to avoid going back round the loop again. Please let me know if it doesn't work, or still times out. It will now try to do five boards at a time.

If it does timeout could you confirm that your messages table has an index on posterTime? If not add one and try again!

Grudge

[Prasad007]

it happened for me too
i just hit refresh
and it said the process had completed

[Grudge]

OK, you were clearly just on the edge, whereas Enc0der is just too big for it to complete at once.

[Enc0der]

hi!

it still times out...

The indexes I have for the 'messages' table are:

Code Select Expand

Keyname  Type  Cardinality  Action  Field
PRIMARY PRIMARY 1329207  Edit Drop ID_MSG
topic UNIQUE 1329207  Edit Drop ID_TOPIC
ID_MSG
ID_BOARD UNIQUE 1329207  Edit Drop ID_BOARD
ID_MSG
ID_TOPIC INDEX 147689  Edit Drop ID_TOPIC
ipIndex INDEX 664603  Edit Drop posterIP 15
ID_TOPIC
participation INDEX 664603  Edit Drop ID_MEMBER
ID_TOPIC
modifiedTime INDEX 132920  Edit Drop modifiedTime
body FULLTEXT 1  Edit Drop body

don't had any index on posterTime.. weird... I was supposed to have one ?
I'm creating a new index (name: posterTime, field: posterTime) and trying again..

[Grudge]

posterTime should help. You don't need posterTime, but it should help with the upgrade, it will be automatically dropped when upgrade has finished.

[Enc0der]

Worked..
Now i'm on "Updating Last Board ID".. I hope it'll over soon.. 

[Enc0der]

hi..

Seems like it's crashing now when 'Making SMF MySQL strict compatible...', on:

Code Select Expand

ALTER TABLE messages
CHANGE COLUMN subject subject tinytext CHARACTER SET hebrew COLLATE hebrew_general_ci NOT NULL

(Edit: According to status.php, query took around 650 seconds to complete..)

[Grudge]

Hi, on a really large forum it might time out then. The problem is that query might take bloody ages on a large forum. However, if you refresh upgrade.php it shouldn't try it again and again. There are lots of queries like that it needs to run to make it strict compatible, so you may find yourself refreshing a few times. If it keeps running that same query and it's taken more than 20 seconds a time I may need to help you some more :/

BTW - Your forum should now be upgraded safe enough to take it out of maintenance if you wish.

[Enc0der]

...and then it'll even take more time as we'll get hundreds of exciting users.. 


ok, that's wierd.. I tried to refresh before, but it simply ran the same query again..
now, refresh worked..

Upgrade Progress: 94.89%
Current Step ("Adding MySQL strict compatibility") Progress: 60.78%

*holding hands*

[Grudge]

Yea, the STRICT changes are VERY database intensive I'm afraid, but it's a "do once" type change, that we needed to make to ensure people don't have problems as hosts start upgrading to MySQL and turning on STRICT mode

[Enc0der]

OMG lol...

State     Time     Query
Repair by sorting    2074s

ALTER TABLE messages
CHANGE COLUMN subject subject tinytext CHARACTER SET hebrew COLLATE hebrew_general_ci NOT NULL

I dunno.. it keeps running the same query.. sometimes the time-counter starts over, but with the same query.. and the refresh on the browser doesn't brings me back to the normal page anymore..



Wierd thing, the time-counter doesn't always reset to zero.. now it started over from around "942s"...


edit,
oh, I think that hiting 'refresh' was a bad idea..
on 'SHOW PROCESSLIST' (with phpmyadmin) I saw that same query a few times.. one running, and the other ones are locked.. I killed the locked ones, and now I won't hit 'refresh' until the current query will end.. I hope this will work...
current url is: upgrade.php?step=2;maint=1;substep=130;s=1;m=1351000;bdi=35;prep_lt=3;strict_step=31
if the stupid browser will make it run the same query again, I'll change 'strict_step' to 32... right?

[Enc0der]

hmm...
it seems like all these queries wasn't changing anything.. the structure was already how they tried to make it... so.. I killed them with phpmyadmin until I've got some necessary queries.. 

Still running................

[Grudge]

Enc0der, I'm cautious that maybe the upgrade script is altering when not needed. The important thing is that once you've got to the STRICT stuff, all the important changes have been made, so you needn't proceed if you don't wish.

[Enc0der]

hmm...
it seems like all these queries wasn't changing anything.. the structure was already how they tried to make it... so.. I killed them with phpmyadmin until I've got some necessary queries.. 

Still running................

And it was over successfully a couple of hours ago..

The new default template is completely different.. a lot of work.. 
anyway, thanks for the help!