Repair tool and Upgrade script errors while updating from SMF 1.1 to 2.0.15

[OddballGreg]

Hi there.

So I find myself in what seems to be something of a chicken and egg situation while updating a Simple Machines forum from version 1.1 RC3 to 2.0.15 using the upgrade.php script. The original website was running on php 5.2.

To provide some context, I'm updating a website that has been on the internet for nearly as long as I've been alive. (The website copyright is from 1998). As a result, the website has been available for longer than the hosting provider has had many of their hosting functionalities, and needed to be migrated to a new hosting solution they have in order to use a higher php version, necessary to make use of SMF 2.0.15. This hosting migration copies the server files and database to their new solution and gives you time to ensure that it is functional before proceeding.

So this brings us to the troubles and my suspicions as to their cause. After configuring the new servers Settings.php to point to the new database, I downloaded the upgrade.php tar file and unpacked it into the forum's directory and attempted to run it, opting to allow it to backup the data before running the upgrade. It seemed to begin without issue and began copying the database tables until it got stuck about 80% through on smf_flooding_control, a table which I examined and found to be empty. A few retries did not seem to achieve anything new, at which point I began to question if I had mis-configured something and downloaded the repair_tool.php script. It's worth pointing out that the upgrade tool had some warnings relating to boolean type variables being passed to mysqli functions at the time, but I moved ahead as they were merely warnings.

The repair script seemed to indicate that I had indeed mis-configured the db settings, as I had not set the SMF table prefix to "smf_", which makes sense as the upgrade script had "failed to find the members table". Upon setting this however, the repair tool revealed it's database settings such as forcing the default theme, but due to two errors near the end of the script, does not render the save button, preventing me from actually making use of these settings if they are the issue. The errors are as follows:

Notice: Undefined index: database_error in /home/clients/7cba3aafb453b09f2584595cb0e6a8cb/web/blabla/Sources/Subs-Db-mysql.php on line 1407

Fatal error: Call to undefined function allowedTo() in /home/clients/7cba3aafb453b09f2584595cb0e6a8cb/web/blabla/Sources/Subs-Db-mysql.php on line 1408

Navigating to these lines within that filet reveals a comment which mentions that these issues may be caused by an un-upgraded SMF database. The problem being that as soon as I do make this configuration to use the 'smf_' prefix, the upgrade script breaks nearly completely, with no html or css barring a smattering of error messages denoting undefined indexes for the upgrade steps, time steps and the below message:

The upgrader was unable to find some crucial files.

Please make sure you uploaded all of the files included in the package, including the Themes, Sources, and other directories.

I was quite sure that I had included all the packages correctly, and even unpacked this file via SSH to watch the tar command's output, which produced no errors while unpacking, leading me to believe this is not related to permissions preventing files from overwriting.

So that is essentially my position now. I do suspect that this issue may be related to the fact that I was not able to reset the forum to it's standard theme and remove any mods, as the new server has no support for PHP5.2, meaning I'm unable to access the forum admin interfaces on this new server. I had believed I would be able to do this once the upgrade was completed, but this does not seem to be the case now.

Is there a setting anywhere that I might be able to manually force the default theme to check if that is the issue? Or remove the other theme's and mods which may be interfering with the process? I tried the packages.php script which from what I understood was meant to remove the non standard themes and mods, but it does not seem to function at all, merely returning "Please try again later".

The new mysql version is 5.7 in case that may be part of the issue.

Additionally, in case the current files are unrecoverable for some reason, is there any chance I might be able to install a clean SMF 2.0.15 install and then move the SMF 1.1 database information over and upgrade just the database?

Thank you very much for your assistance, and please let me know if you require any further information.

[Kindred]

I believe that you can not upgrade from an RC version of 1.1 to the full release of 2.0.x


So... upgrade to 1.1.21 THEN try to upgrade to 2.0.15
resetting and uninstalling mods is not required.


Please note that 2.0.14 and above require at least php 5.4 (suggested is 5.6 or 7.0)


but no...  you can not use a 1.1.x database on 2.0.x -- the upgrade MUST be run to change all of the database tables and columns that were altered.

[Sir Osis of Liver]

You'll have to dick around with php version, earlier upgrade scripts may fail in higher php versions.  Do it in steps, start in php 5.3 until you get to 1.1.21, them bump php up to 5.4, get to 2.0, then you should be good to patch to 2.0.15 in php 5.6. 

[OddballGreg]

What do you think my odds are of managing to update up to 1.1.21 and it's php 5.5 support using php 5.5? 

The new server hosting on from this provider does not offer anything lower than php 5.5. So my options are to hope that I can install the updates despite this, or to start cutting and pasting updates manually up to 1.1.21

[Kindred]

I was told that php5.5 was extremely buggy and should never be used on any system that you want to work.

[Arantor]

BS, there's nothing wrong with PHP 5.5.

The only "bugs" in it are where it deprecated things and people didn't fix their software.

[Kindred]

Ah. Thanks for the correction arantor.

I'll whip the person with a wet noodle...

[Arantor]

We still have stuff running on PHP 5.5 on the basis that the underlying operating system is Ubuntu 14.04 and that's the version of PHP that it has in it - and still gets security backports until next April. The main issues in 5.5 centre around deprecation of preg_replace with /e and with people not understanding what the opcache does - which can certainly make things look buggy. Incidentally this feature being more and more prevalent is the reason for the rash of issues with the installer munging the theme details in 2.0 (except 2.0.7).

As to the OP, best route I would suggest: head to PHP 5.4 (not PHP 5.5; SMF 1.1.x is not properly compatible though it shouldn't be a huge deal) - 5.4 should get you through not only the end of the 1.1.x upgrade cycle if you're not already on 1.1.21 but also through to 2.0.15. Once on 2.0.15, updating to PHP 5.6 or beyond is a much easier deal.

Alternative route, PHP 5.3, go through 1.1.x to 2.0.13, update to PHP 5.5 or 5.6 and then complete 2.0.13 to 2.0.15 through the package manager. But honestly... I'd do the upgrade on 5.4.

[Sir Osis of Liver]

hope that I can install the updates despite this

'Those who live in hope often die in despair.'

If you can't get it, let me know, I've done this for several forums, and can work as far back as php 5.3.  Make sure you have a good backup of the original database, or you may be screwed.

[OddballGreg]

Thankfully the web hosts allow us to keep the old server for 30 days during the migration process, so I've recovered from there.

The trouble with the new hosting solution only offers php 5.5, 5.6, 7.0 and 7.1. Whereas the old server runs on 5.2. We devised the plan to run the updates on the old server until we got to 1.1.21 and then remigrate to the new server to use php 5.5 and run the final upgrade.

The old server (with php 5.2) however, does not seem interested, as, starting with the SMF 1.1.1 patch, any attempt to install it frustratingly results in the message:

No installation or uninstallation actions have been defined!

Am I starting at the wrong version, or is there some other issue at hand here?

The forum seems to have been previously patched with this:

SMF 1.0.9 and 1.1 RC3-1 Security Patch

Apart from me being at a loss as to how you can be at version 1.0.9 and 1.1 RC3-1, this seems to imply the server was originally installed with SMF 1.0.8.

[Arantor]

It just means the one patch covers both versions, meaning you probably had the 1.1 version unless your forum is so old you had 1.0... 1.1 RC3 was late 2006.

Ok, so go with 5.5, ignoring any mentions in the error log of deprecated features. At this point since you're not sure what version you have, I'm going to suggest something unusual.

First step, get you to 1.1.9, use the "large upgrade zip" from https://download.simplemachines.org/index.php?archive;b=3;v=57

This should get you to a known good point in terms of upgrading the database since 1.1.9 brought some specific changes.

In theory you could then install all the patches but honestly, the next step is probably quicker jumping to 1.1.21 with large upgrade again, from https://download.simplemachines.org/index.php?archive;b=3;v=91

Then as you're on PHP 5.5, jump directly to 2.0.15 with the large upgrade there.

[Sir Osis of Liver]

Full upgrade may not work from 1.1.9 -> 1.1.21 in php 5.5, iirc you get the missing files error.  And yeah, trying to patch from RC is going to ruin your day.

[OddballGreg]

@Arantor. Thank you for the fantastic advice! I didn't realise that there would still be the older large upgrade packages. After backing up the database and files of the original server, I went to run the 1.1.9 upgrade on the original server as I figured these upgrade packages would need the older PHP. I had to modify a couple of SQL queries to get the upgrade to work due to the "LENGTH(code)" syntax not being accepted by mysql. I found this solution here: http://www.simplemachines.org/community/index.php?topic=178857.msg1138626#msg1138626

Once I made those modifications to the upgrade code it successfully updated to 1.1.9, and then again with the next large upgrade packages I managed to get it up to 1.1.21.

These are both good steps so far, but now I've stumbled on the final hurdle. Taking the functional forum with 1.1.21, I migrated its files and database to the new hosting servers as it now supports php 5.5 at that version, but the database host references seem to be causing me issue from what I can guess. As far as I'm aware, I've configured the Setting.php file correctly, but the repair_settings.php script continues to have this issue I mentioned in my first post:

Notice: Undefined index: database_error in /home/clients/7cba3aafb453b09f2584595cb0e6a8cb/web/blabla/Sources/Subs-Db-mysql.php on line 1407

Fatal error: Call to undefined function allowedTo() in /home/clients/7cba3aafb453b09f2584595cb0e6a8cb/web/blabla/Sources/Subs-Db-mysql.php on line 1408

(And the 2.0.15 upgrade tool has the aforementioned breakages too.)

From what I've found reading up on the SMF forum from Kindred back in 2014, https://www.simplemachines.org/community/index.php?topic=527583.0, it seems that my troubles are arising from the host migration, which seems reasonable as when I press any of the buttons on the preview forum for this new site, all the urls forward me to the old hosted server. (Which is to say, the URL has not updated, even though my $board_url variable is set correctly in Settings.php.)

Is there any way I might be able to go find these database entries myself to see if I might be able to manually make the necessary modifications to fix this? Or are there any other config files apart from Settings.php I should go examine? I also seem to vaguely remember reading these links might be coded into the theme files, though that seems unlikely to break the Subs-Db-mysql.php file.

Lastly, I did manage to change to the them to default theme on the website via the administrator, and this has made the upgrade.php tool look less mangled, but still return the same undefined index errors.

Thanks again for all the help so far. I feel like I'm very close to getting this right.

[OddballGreg]

An update:

So, I continued tinkering around to try and see what I had done wrong, and, I'm not sorry to admit, this issue seems to have been my fault. It would appear the repair_settings.php script picks up on the version of SMF and tries to run differently based on that version. I had unpacked the upgrade.php files before trying to run the repair_settings.php script for the new host, making it impossible to run the repair necessary to then successfully run the upgrade. Copying the clean files over and doing these two things in the correct order fixed the issues for both the repair_settings.php script and the upgrade.php script.

However, as luck would have it, this process is not done giving me trouble yet, as during the running of the upgrade process I now receive the following error:

Error!
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE smf_log_floodcontrol CHANGE COLUMN ip ip char(16) NOT NULL default ' at line 1

I can only assume upon looking through the sql upgrade file for 2.0 that this is a generated statement due to the "line 1", but I'm unfamiliar with the IGNORE TABLE statement which I was able to confirm as the source of the issue in the php myadmin linked to the site. I can't seem to find any documentation on this IGNORE TABLE syntax, the closest thing I found being INSERT IGNORE to prevent errors on duplication or other errors when inserting into a table.

Is there a possibility that I could modify these queries as this seems to be an MySql version mismatch, or I have missed something once again?

Edit: The more I look into it, the more I doubt that "IGNORE TABLE" is an intentional output of the upgrade package. I'm going to redownload it and try again in case something strange occured.

[OddballGreg]

Progress:

I continued looking into the aforementioned SQL break, and came to realise that IGNORE is a valid command for table alterations, but that the SQL being run was in fact being mangled and/or truncated, as the valid SQL to be run was actually:

ALTER IGNORE TABLE smf_log_floodcontrol CHANGE COLUMN ip ip char(16) NOT NULL default '0'

This was quite peculiar to me, and with further investigation, I suddenly had a hunch when I looked into the upgrade_2.0_mysql.sql file and found the following:

'log_floodcontrol' => array(
      'logTime' => 'logTime log_time int(10) unsigned NOT NULL default \'0\'',

You might notice here that the column being changed is logTime, not ip. This was when I got a hunch and deleted the upgrade_1.0.sql files that were still in my forum directory. It seems the upgrade tool has a lackluster filename check in terms of sql upgrade, as it was not running the 2.0 sql upgrades, and progressed once I deleted these files.

Of course, as if it would be too easy for this escapade to be at an end now, I have now received the following, slightly unhelpful error:

Executing database changes
Please be patient - this may take some time on large forums. The time elapsed increments from the server to show progress is being made!
Executing upgrade script 2 of 2.
Executing: "Adding permission profiles for boards." (16 of 42 - of this script)

Database Updates Complete! Click Continue to Proceed.
!!Error!
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id_board IN (2,4,8,10,23,24,40,47,49,58,61,66,75,77,85,86,90,91,92,93,95)' at line 2

On goes the process I suppose.

EDIT:

Modifying this message to prevent providing misinformation to other people with these issues, it seems to the 1.0 and 1.1 .sql upgrade scripts do indeed come from the 2.0.15 upgrade package. Deleting them merely glossed over the issue and likely caused the above issue.

[shawnb61]

A lot of helpful info in this thread.  I wonder if an "Upgrading from 1.x" sticky or wiki might be in order.  Hopefully we'll see a bit of a push as the last 1.x folks move off 1.x.

[vbgamer45]

The last couple of boards I upgraded had to go upgrade via 2.0.13 then later upgrade to 2.0.15 to get it to work correctly.

[OddballGreg]

So looking through the documentation, I found this webpage: https://dba.stackexchange.com/questions/143862/mysql-alter-ignore-add-index-removed-in-mysql-5-7-4-what-to-use-instead [nofollow]

The ALTER IGNORE TABLE syntax was deprecated in MySql 5.6. The server to which I am attempting to run these migrations runs on is using MySql 5.7 which has completely removed these commands.

It seems my only possible options left are to use the old server once again and perhaps upgrade to SMF 2.0, or to set the site and db up on an external system with the necessary versions to get it to upgrade before I can finally move it up to 2.0.15

[OddballGreg]

And so the escapade ends. I was thankfully able to run the 2.0 large upgrade on the old server, which allowed me to run those old MySql syntax commands and migrate the database. From there I was able to push the files and DB across to the new server and DB package and update all the way to 2.0.15.

Thanks to everyone for bearing with me through all this process and all the greatly helpful suggestions, and I hope all this information serves to help future migrations for others.