2.1.4 upgrade fails

[Sir Osis of Liver]

1.1.21 > 2.1.4 upgrade fails looking for two nonexistent tables -

upgrade_1-1.sql

Code Select Expand


---# Renaming personal message tables...
-- RENAME TABLE {$db_prefix}instant_messages
-- TO {$db_prefix}personal_messages;

-- RENAME TABLE {$db_prefix}im_recipients
-- TO {$db_prefix}pm_recipients;
---#


Successful after commenting out those lines.  Ran it in php 8.3.

[shawnb61]

I cannot reproduce in a 1.1 => 2.1.4 upgrade.  Currently php 8.4.2 & mysql 8.4.0. 

Some notes on the upgrader:
- Those tables were valid 1.0 tables, removed in 1.1.
- The upgrader always re-runs the upgrader for the step you're starting FROM (e.g., for a 1.1 to 2.1 upgrade, it will run 3 steps, 1.1, 2.0 & 2.1); the intent here is to ensure you're starting point is valid.
- You can rerun the upgrader as often as needed to get it to run to completion.
- It ignores many specific DB errors, e.g., it ignores 'table doesn't exist' errors for renames.  It must, in order to be able to be rerun....

If it didn't ignore such errors, literally 100% of all upgrades from 1.1 to either 2.0 or 2.1 would have failed...  Because those tables don't exist in 1.1...

So...  Something unusual happened here. 

 - What was the actual error?  The exact text & error # are important. 
 - Also what eversion of MySQL were you running?
 - Did the personal_messages & pm_recipients tables exist before running the upgrader?

[Sir Osis of Liver]

This upgrade has been a bit of a slog.  The long version is here.  Shorter version, it was a 1.1.21 forum running on a GoDaddy vps in php 5.6 mysql/mysqli 3.0.  I don't have access to control panel or database, just ftp.  Tried to get him to upgrade to php 8.1 or 8.2, ended up at 8.3.  Ran the upgrade in 8.3, it errored out right at start looking for 1.0 tables that don't exist in later versions -

Table 'dmf_smf-.smf_instant_messages' doesn't exist

The 1.1.21 install had the normal complement of tables, the script was looking for two 1.0 tables that shouldn't have been there, it should know that.

Edited upgrade_1-1.sql as posted above, upgrade ran normally and completed.  It made a mess of attachments and avatars.  Most attachments went missing, none had .dat extension, some but not all avatars were moved to /custom_avatar.  I had made a backup of 1.1.21 /attachments, uploaded attachments to 2.1.4 /attachments, added .dat extension, uploaded avatars to /custom_avatar.  Now, for reasons unknown, we have 1330 files in /attachments, 2477 files in /custom_avatar, but the /attachments backup I uploaded them from contains 2502 files total.  I'm not seeing why.

Forum is currently in php 8.2, mysqli 8.2 (phpinfo here), there were a few of these errors -

Code Select Expand


Guest
216.244.66.196
fda25c918408e92dcd903dc7f0b9bd41
https://ducatimonsterforum.org/index.php?topic=10531.msg1467559
/var/www/vhosts/ducatimonsterforum.org/httpdocs/Sources/Load.php (Line 3199)
Backtrace information

Type of error: General
Error message Select
2: Trying to access array offset on value of type null


Looks like they were from php 8.3, none since last night in 8.2.  I'm not sure if upgrade ran in mysql 3.0 or mysqli 8.2.  Forum seems to be working ok, we're having a problem installing ezportal, but I think that's a mod conflict, will work on it tonight.

[shawnb61]

- Did the personal_messages & pm_recipients tables exist before running the upgrader?

[Sir Osis of Liver]

pm_recipients did exist in 1.1.21 install -

https://www.simplemachines.org/community/index.php?topic=591142.msg4186897#msg4186897

We didn't check for personal_messages, but I believe it was also there.

[shawnb61]

This is not a bug.  The upgrader normally properly handles the scenario ID'd above.  Again, it if didn't, no upgrade would ever have run successfully... 

This error indicates that all 4 files were missing, instant_messages, personal_messages, im_recipients and pm_recipients. 

The link provided above shows in fact that the table did not exist, confirming this... 

This DB, somehow, was in a terribly wounded state.

[shawnb61]

Going forward, when helping folks with long-overdue or problematic upgrades, the VERY FIRST thing I suggest doing is confirming the DB is complete.  This tool will help you do that - it's why it exists:
https://raw.githubusercontent.com/sbulen/sjrbTools/refs/heads/master/smf_db_compare.php

[Sir Osis of Liver]

Actually, no, OP confirmed that pm_recipients was in the 1.1.21 database.  Forum was running normally before upgrade other than logging a lot of errors in php 5.6, so I'd assume all tables were there.  Didn't have access to the database, so couldn't check anything.  Upgrade completed after editing upgrade_1-1.sql, but there were other problems, most obviuosly with attachments and avatars.  Forum seems to be running ok now in 2.1.4, not logging any errors.

[shawnb61]

Actually, no, cannot reproduce.  Seriously, I've tried.

Not a bug.

Try it locally.

If you & others can reproduce, that's a bug.

If not, it's not.

[Sir Osis of Liver]

Will ask if I can get copy of the 1.1.21 database.  It's quite large, around half a gig.

[shawnb61]

Just do a 1.1 install.

[Sir Osis of Liver]

I have one, but don't think it will replicate the problem.  Something went wrong with this upgrade, but need the database to run it down.

[shawnb61]

I have one, but don't think it will replicate the problem.  Something went wrong with this upgrade, but need the database to run it down.

That's my point...  The issue here is a very broken DB.  The upgrader can't work on a DB that's that broken. 

I'm curious what the db compare utility shows you once you get your hands on the DB.

To the best of my knowledge (which is far from perfect, I admit...), the only way to get a 'Table xxx doesn't exist' message is if both the FROM & TO in that RENAME don't exist.  If the TO table exists at all, you get a 'Table xxx exists' error when attempting the RENAME, which is detected & suppressed by the upgrader.  I.e., it does nothing, which is why normally you're good.
https://github.com/SimpleMachines/SMF/blob/7f63cea8f851e9dfa07094a6f5c3d1181e47a5fd/other/upgrade.php#L2484

[Sir Osis of Liver]

Shouldn't that be an IF EXISTS?  There wouldn't be any reason to rename a table that doesn't exist in a 1.1 install if you're upgrading from 1.1.

[Sir Osis of Liver]

Will have 1.1.21 database tonight.  Database compare tool works in 2.0.19 php 8.1, wsod in 1.1.21 php 5.4/8.1.

[Sir Osis of Liver]

Shawn, I have the upgraded 2.1.4 database running in a clone on my server.  You can view the compare tool here.  I've been able to clear up most of the problems by manually editing the settings table.
 

[shawnb61]

The question is whether or not there was a bug. 

To determine that we need to know more about the 1.1 DB, before upgrade.  Running it against a working 2.1.4 doesn't help us answer that question.

Could you at least get a list of tables from the 1.1 backup?

Hmmm...  smf_db_compare needs a copy of subs_db_mysql.php...  Really any version...  If you have the 1.1 forum & source around, & copy that file over into /Sources from smf 2.1.4 & it'll work...   That'd be best.

[Sir Osis of Liver]

I have the 1.1.21 database running here in php 5.4.  Had to upload Subs-Db-mysql.php from 2.1 package to make compare tool run -

https://www.thekrashsite.com/ducati11/db_compare.php

Cannot get css to load, repair_settings doesn't fix it.

Later.

[shawnb61]

Cannot get css to load, repair_settings doesn't fix it.

Browser console shows a mix of http & https.  They all gotta be one way or the other.  Don't forget about .htaccess - e.g., you may have https forced at the site level but http internally.  Probably easiest to make everything https.

Challenges are:
- http vs https are mixed.
- Multiple portals installed (& I doubt the DB structures for the 1.1 versions of these portals is even close to the 2.1 version of the portals...)
- Many columns have no defaults; newer DBs hate that
- A few other mods installed like Tapatalk, Join Reason, media (AEVA or SAVE)
- DB had been converted to UTF8MB3 already, though it's incomplete; some tables are still latin1

I am going to assume you want to keep Join Reason, SAVE & TP.  I am also going to assume you want to keep historical TP content. 

My suggested next steps - how I would approach this:
- Get 1.1 working PERFECTLY, including attachments & avatars.
- BACK EVERYTHING UP, files & DB...
- I don't know if TP has an upgrade utility?  Might want to research that...  It may simplify subsequent steps...
- Remove all tables/columns/indexes for Tapatalk, AEVA, Simple Portal (again, assuming you're keeping TP)
- Install, ***into a 2.1 DB***, TP & Join Reason.
- Make the DB structures for TP & Join Reason in the 1.1 DB match that of a 2.1 install - Add tables, columns & defaults as necessary.
- Hunt down defaults for all columns.
- Convert any tables that are still latin1 to utf8mb3_general_ci using phpmyadmin.  Check the box to ensure all columns are converted also. 

At this point, double & triple check:
- The ONLY custom tables & columns should be for TP & Join Reason. 
- The tables & columns for TP & Join Reason should match 2.1 installs.
- All columns have defaults.
- All tables & columns are utf8mb3.  No exceptions.
- You should be able to confirm all the above via the db compare tool.

IMPORTANT...  You need to tell SMF it's already all UTF8, since the utf8 conversion was somehow done outside of SMF...  There are 2 important settings to look at:
- $db_character_set in your Settings.php file should be 'utf8'
- The 'global_character_set' entry in your smf_settings table should be 'UTF-8'

Next steps:
- BACK EVERYTHING UP...
- I'd delete /Sources & /Themes & /Packages
- Upgrade PHP to current.
- Install & run the 2.1 upgrader.
- Once everything works fine, install 2.1 versions of TP, SAVE, Join Reason

Hope this helps,

[Sir Osis of Liver]

Just getting back to this.  The 1.1.21 database is running with clean set of files, will not load css.  repair_settings runs but does not update paths in database.  Didn't see the 1.1.21 production forum, but I believe he was running SimplePortal, which has not been updated for 2.1.  OP tried to uninstall mods before his initial attempts to run upgrade, but pm reported errors so he left them in.  I've been able to fix some of the problems with the upgraded database by manually deleting hooks and mod settings.  The major problem that remains is forum is reporting 0 attachments.  All id_msg and id_thumb fields in attachments table are set to 0 for 2400 attachments.  That's a major part of his content.

Plan right now is to manually update paths in database.  If I can get the forum working in 1.1.21, will try upgrading to 2.0.19.  At this point it appears that repair_settings and 2.1.4 upgrade do not work on this database.  It's possible GoDaddy support did things to the database, but no way to know what was done.

Just looked at attachment table in 1.1.21 db, id_msg and id_thumb fields are all 0.  If this is the only backup he has (he says it is) there's no way to recover the attachments.