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.

[shawnb61]

I suspect both the css load & the repair_settings issues are due to the http vs https problems.

[Sir Osis of Liver]

I'm now unable to run repair_settings in this install, getting 500 error.  Not seeing anything in server log.  Forcing https in .htaccess has no effect.  I've removed all mod settings from settings table, tried using absolute path to style.css in index.template.php, still can't get css to load.

[Sir Osis of Liver]

Got it working.  repair_settings requires DbExtra-mysql.php, must remove Subs-Db-mysql.php if present.  Neither is in 1.1 package.  Forum is running ok, no errors, was able to register.  It's seeing 0 attachments, so that was not a result of the upgrade.  That can't be fixed unless OP has a better db dump.

[shawnb61]

OK, back to the original topic...

Do you have a fully working copy of 1.1?  (Attachments issues aside...)

No mod tables or columns?
Columns have defaults?   (at least as much as you see in db_compare?)
All tables & columns utf8mb3?
$db_character_set 'utf8'?
global_character_set 'UTF-8'?

If so, back everything up, again again, update PHP to something current, & run the 2.1.4 upgrader.  The whole point of this was to attempt to reproduce the reported bug.

Can you reproduce it?

I cannot - and I have tried multiple times.

[Sir Osis of Liver]

The 1.1.21 install seems to be working normally in php 5.4, not logging any errors.  .htaccess is forcing https.  Registration now works (it wasn't) and new attachments can be posted.  I deleted all the mod settings in settings table, and removed mod tables from database.  Paths were all screwed up, but finally got them fixed.  It looks and works like a clean 1.1.21 install.  Will check the things you mentioned tomorrow, and if OP confirms that everything's working and content is intact, I'll try upgrading it.

[Sir Osis of Liver]

Ok, this is what I've done -

• Delete all mod tables from 1.1.21 database, contains 41 core tables
• Delete mod settings from settings table
• Clean 1.1.21 install running normally, no mods, default theme.  Everything works except old attachments, no errors
• Uploaded 2.1.4 upgrade package, run upgrade in php 8.1

Fails with same error -

Executing upgrade script 1 of 3.
Executing: "Adding and updating member data..." (4 of 19 - of this script)

Table 'thekrash_ducati1121.smf_instant_messages' doesn't exist

1.1.21 database contains pm_recipients and personal_messages, does not contain instant_messages or im_recipients.

[shawnb61]

Hmmm...

Can you share mysql info - version?  MariaDB/Percona?

Is this the same environment as prior result, or a new environment?

[Sir Osis of Liver]

Version Information:
Forum version: SMF 2.1.4 (more detailed)
Current SMF version: SMF 2.1.4
GD version: 2.3.3
MySQL engine: MariaDB
MySQL version: 10.11.11-MariaDB-cll-lve
SMF file based caching: 2.1.4
SQLite3 database based caching: 3.45.2
PHP: 8.1.31 (more detailed)
Server version: LiteSpeed

[Sir Osis of Liver]

IIRC (this is getting confusing) the original upgrade was done on production install on GoDaddy vps, failed with same errors, different platform.  I've commented out the two RENAME TABLEs, upgrade is running normally.

[Sir Osis of Liver]

Upgrade completed without issues, will check the install now but looks ok.

[shawnb61]

OMG, it's Maria DB...

If smf_personal_messages exists & smf_instant_messages does NOT exist...  And you attempt a:

Code Select Expand

RENAME TABLE smf_instant_messages TO smf_personal_messages;
Using Mysql:
    #1050 - Table 'smf_personal_messages' already exists

This is as expected, & the upgrader handles this properly (and has, for many years).

Using MariaDB:
    #1146 - Table 'van1121.smf_instant_messages' doesn't exist

Yikes...  They're actually returning different errors than MySQL...  This is bad enough I'm kinda shocked MariaDB works at all, TBH... 

To the best of my knowledge, SMF has never formally acknowledged support for MariaDB.  Quirks like this are exactly the reason why.  We've had LOTS of issues in the past with the upgrader & MariaDB.  You're normally OK once you get past the upgrader.

I know that there have been discussions about how to best support MariaDB going forward.  But this is a whole new level of behavioral difference.

Whether this is a bug or not depends on whether you consider MariaDB supported.  There is no formal SMF statement on MariaDB beyond "it usually works"...

@Sesquipedalian - for your awareness...

From the support threads here at SMF, I see two issues with MariaDB.

First, is that some MariaDB implementations have reserved words that conflict with SMF column names.  This causes syntax errors, primarily in installs, upgrades & I believe package installs will have the same issues.  Anywhere columns are added or altered. 

Second, is that their numbering scheme has strayed from MySQL.  ~10.0.2 is somewhat equivalent to ~5.6.   There are several places in SMF code where versions are checked.  This is either used to enable/disable features or to use alternate code.  SMF checks against MySQL version #s, not MariaDB version #s, and there will be issues there.  As a hypothetical example, if MariaDB starts supporting a specific feature in 10.0.5, which mySQL has in 5.7, MariaDB 10.0.2 users will experience "bugs" as SMF thinks the feature is supported, as 10.0.2 > 5.7. 

IMO, these are two fairly significant issues that must be resolved before MariaDB can be formally supported. 

[Sir Osis of Liver]

We ran upgrade on GoDaddy vps, it was not running MariaDB.  That's where we first got the error.

[shawnb61]

The above is reproducible.

If you can reproduce it in MySQL, please share the steps.

Unless you want to spend time to reproduce on GoDaddy, I'd say all that is a writeoff. 

[Sir Osis of Liver]

We did reproduce it in mysql, in the original upgrade on the GD vps.  Ran upgrade by the book.  Here's the server setup -

https://ducatimonsterforum.org/phpinfo.php

Upgrade was run in php 8.3.  Original mysql version was 3.0, not mariadb.  At some point, most likely when php was upgraded, mysqli was upped to 8.2.27, but don't know that for sure.

[shawnb61]

A note on the above...   The deeper problem with MariaDB issuing "#1146 - Table 'van1121.smf_instant_messages' doesn't exist" is that that is indistinguishable from the real error, e.g., if the tables were truly missing.

You cannot just ignore an 1146 exactly like SMF currently ignores a 1050...

I.e., if the target table already exists, it's safe to ignore the rename error, which is what the upgrader curently does.  The table exists so you're fine.  However, if the error indicates that the source table does NOT exist, that might be OK or it might NOT be OK... 

So if you were to try to fix, you cannot use the existing upgrader mechanisms at all.  You'd have to add logic to the old 1.1 upgrade scripts to detect which tables exist & which don't, etc... 

Specifically to support MariaDB 1.1 upgrades. 

[Sir Osis of Liver]

I think we're overthinking here.  The original upgrade failed with this error on a mysql platform, and now fails in mariadb.  AFAIK the error has never been reported here.  That leads me to believe there's something squirrelly about the original 1.1.21 database, which has been subjected to considerable abuse over many years, and more recently on GoDaddy.  I removed all the mod tables, and all mod and extraneous settings from settings table, and still got the error.  Only thing I can suggest is import the attached settings table into a clean 1.1.21 install and see if upgrade fails in mysql.

[shawnb61]

So, not a bug then, correct?

[Sir Osis of Liver]

Well, that's what we're trying to determine.  The upgrader is apparently incorrectly trying to upgrade a 1.1 forum as if it were 1.0. 

[shawnb61]

The upgrader is apparently incorrectly trying to upgrade a 1.1 forum as if it were 1.0.

That's been explained multiple times in these two threads.  The upgrader ALWAYS starts with the FROM version, to ensure it's solid.  Always has.  E.g., if you do a 2.0 => 2.1 upgrade, it runs two steps, the 2.0 step and the 2.1 step.  If you do a 1.0 => 2.0 upgrade, it will run three steps, 1.0, 1.1 & 2.0. 

You know this.  You've run upgrades before.  One step per file. 

This works, because the upgrader is intended to be able to be rerun. 

Well, that's what we're trying to determine.

The answer there is no.  I haven't been able to reproduce it (outside of MariaDB).  And yes, I've run many 1.1 => 2.1 and even 1.1 => 3.0 upgrades since this discussion started. 

They run fine.  Like they always have....   If the bug were as you described, nobody would have ever been able to upgrade from 1.1, ever...  Right?

You know that bugs must be reproducible.  And there is a big difference between seeing an issue once and being able to reproduce it.

Please - just go ahead & run a 1.1 upgrade in MySQL... 

[Sir Osis of Liver]

I've already run the upgrade in mariadb with those two steps commented out, ran fine, everything works, not logging any errors.  Will try running upgrade again in mysql on GoDaddy server using cleaned up 1.1.21 database if that's what OP wants to do, else I can have him import upgraded database from my server into his production install.  It's not my forum, I don't have control panel or database access, so I'm limited in what I can do. 

[Sir Osis of Liver]

Well, here ya go.  phpinfo reports mysqli 8.2.27, but I now have access to Plesk and it shows database server running MariaDB 10.5.27.  Best guess is database is running on different server than forum.  Go figure.