News:

Bored?  Looking to kill some time?  Want to chat with other SMF users?  Join us in IRC chat or Discord

Main Menu

Upgrade Stuck at Making SMF MySQL strict compatible...

Started by Biology Forums, July 19, 2019, 11:05:01 PM

Previous topic - Next topic

Biology Forums

My table is HUGE, to begin (5 gb). The upgrade is stuck at:

Executing: "Making SMF MySQL strict compatible..." (16 of 19 - of this script)

The "click here to try again" doesn't work either. Keeps giving me the error:

Error!
Server has not responded for x seconds. It may be worth waiting a little longer before trying again.Click here to try again.


I've tried 60, 120, etc. seconds, and it makes no difference. How can I tweak the upgrade.php file to help me here? Has the upgrade.php been designed for extremely large databases?

Sir Osis of Liver

Have gotten that error at least a couple of times, I believe upgrading 1.1 to 2.0.  Don't think I've upgraded anyone from 2.0 to 2.1 (not recommending it til it goes final), which I assume is what you're doing.  Never saw a 10 sec server timeout, it's always been 30 sec any upgrade I've run.  Anyway, believe I got around that error by changing php version, either up or down, depending where you're at.
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

Biology Forums

#2
I'm going from 1.x to 2.1.

I'm using php 5.4, but can switch to 7.0.

I put it @ 10 just for fun, but as mentioned in my post, I tried others as well, up to 240!

Sir Osis of Liver

#3
You'll have a long night if you try to go directly from 1.1 to 2.1.  You've removed version number from copyright, so don't know where you are in 1.1, but if you're at 1.1.21, try upgrading to 2.0 in php 5.4 or 5.6.  Don't think you'll get there in 7.0.  The mysql strict compatible function is in upgrade_1-1.sql, if you get desperate you could remove it, but that wouldn't be a real good idea.  If you get to 2.0.15, you should be able to upgrade to 2.1 in php 5.6 or 7.0.


Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

Biology Forums

I'm using a fresh copy of 1.x, with the database of my main forum, so it's not the SMF version.

The upgrade.php is designed to handle 1.x to 2.1, and PHP 5.4 and 7.0. Why would a 2.0 conversion help?

Just tried PHP 7.0.27, and no change. Back to 5.4.


Sir Osis of Liver

It's designed to do a lot of things that won't work in some server configurations.  The mysql strict conversion goes back to 1.1 upgrade, it's been updated some but may still have problems if you try to make a long jump.  I've done quite a few upgrades from early 1.1 to 2.0 that were supposed to work but didn't, had to go up in steps and juggle php versions along the way.
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

Biology Forums

That's what I want to know, how to tweak the file so that it works :-[ Given that the table is massive, what can I do to prevent it from timing out?

QuoteUpdating indexes on "messages"...done
Updating table indexes...done
Reordering boards and categories...done
Updating indexes and data on "smileys"...done
Updating indexes on "log_boards"...done
Updating indexes on "log_mark_read"...done
Updating indexes on "themes"...done
Updating data in "settings"...done
Adding new settings...done
Adding PM spam protection settings...done
Cleaning old values from "settings"...done
Encoding SMTP password...done
Adjusting timezone settings...done
Checking for "classic" and removing it if necessary...done
Renaming personal message tables...done
Updating indexes on "pm_recipients"...done
Updating columns on "pm_recipients"...done
Updating columns on "members"...done
Updating columns on "members" - part 2...done
Updating member approval...done
Adding new holidays...done
Updating event start and end dates...done
Converting other date columns...done
Checking for an old table...done
Creating "message_icons"...done
Inserting "message_icons"...done
Creating "package_servers"...done
Inserting "package_servers"...done
Updating flood control log...done
Updating ip address storage...done
Converting "log_online"...done
Updating poll column sizes...done
Updating attachments table...done
Updating boards and topics...done
Updating members...done
Recounting member pm totals (step 1)...done
Recounting member pm totals (step 2)...done
Converting server stored setting...done
Converting avatar upload setting...done
Updating attachments...done
Updating settings...done
Registering thumbs...done
Adding image dimensions...done
Splitting ban table...done
Updating ban statistics...done
Deleting some very old permissions...done
Renaming permissions...done
Upgrading "deny"-permissions...done
Upgrading post based group permissions...done
Upgrading by-board permissions...done
Removing all guest deny permissions...done
Removing guest admin permissions (if any)...done
Creating search cache tables...done
Rebuilding fulltext index...done
Indexing topic subjects...done
Converting settings...done
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...done
Cleaning up old log indexes...done
Preparing messages table for strict upgrade...done
Adjusting text fields......

Sir Osis of Liver

Messing with the upgrade scripts is a bad idea.  I believe the server timeouts are caused by the script stalling, not because it has to run for a long time to upgrade a large table.  As long as the script is executing, it doesn't time out, that only happens when the script gets stuck and nothing happens for the timeout interval.  The script stalls because it's having a problem trying to make too long a jump in the current php version.  It's mostly server related, I've upgraded several forums that could not be upgraded on their host, had to export the db, import it to my server, run upgrade, then export and import it back to original host.  There are more server configs than you can shake a stick at, and it's difficult to write code that will work everywhere.  The upgraders are fussy about the environment they're run in.
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters


shawnb61

To help diagnose, it would really help to have the "Output extra debugging information" checkbox checked at the start of the upgrade.   It provides a greater level of detail. 

What exact version are you starting from? 

Is this a vanilla version or has it been modded?   

Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp


shawnb61

Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp


albertlast

I wouls skip the 1.1 part,
by changing the version number of smfVersion,
maybe to 2.0.0

Arantor

But that won't help with getting the tables/columns renamed from 1.1 to 2.0 style, right?

I'm assuming the issue is that the table conversions prompt full table rewrites and under the hood, MySQL isn't responding to the PHP side promptly enough so it's triggering "MySQL has gone away" issues.

albertlast

based on the information here.
he got a 1.1.21 board,
he stuck on the 1.1 upgrade part(the next would 2.0 and than 2.1).
i would litterly say the real issue is,
that the 1.1 upgrade got no checks for the need of this change.

Since he is already on the right version,
is ther no need to run the 1.1 upgrade script part.

So in my eyes this would fix it.

Arantor

But if the version is tagged as 2.0, it's not going to run the upgrade-to-2.0 script, is it?

Better question: why would the upgrade to 1.1 script have been changed for strict upgrade in the first place?

albertlast

when he change the version from 1.1 to 2.0 (in the database),
only the upgrade script for 2.0 and 2.1 get called,
not any more the 1.1 script which is fine.

smf upgrade logic is:
first run the upgrade script of the current version number,
than call the newer ones.
reason why he got three "mayor" steps
1.1, 2.0 and 2.1

albertlast

Okay i checked the upgrade logic,
the best way to skip the 1.1 upgrade script,
is to remove this line from the upgrade.php:

if (!isset($modSettings['smfVersion']) || $modSettings['smfVersion'] < 2.0)
$check &= @file_exists(dirname(__FILE__) . '/upgrade_1-1.sql');

shawnb61

I agree with albertlast's suggestion to bypass the 1.1 script. 

If that doesn't work, the next thing I'd try is to download the latest from github, as there have been improvements made.  I wouldn't use RC2 at the moment, actually. 

Upgrade versions:  The way it was explained to me is that the upgrader tries to ensure the DB is properly in version X before it upgrades to version Y...  When moving from 2.0 to 2.1, it runs both upgrade steps.  If moving from 1.0 to 2.1, it will execute the 1.0, 1.1, 2.0 & 2.1 scripts. 

The "strict mysql" step has been around for 6-7 years, and is in the original 1.1 script put up on github.  It's not new.  It cleans up text field definition. 

Much of the 1.x logic is quite old and does not even execute under the newer versions of mysql.  Albertlast has been fixing execution of the 1.x scripts, e.g., by getting rid of the ALTER IGNOREs, which are no longer supported.  That should make it smoother for folks making the 1.x to 2.1 jump in the future. 

Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Advertisement: