[2.1 & 3.0]: Upgrader serialize-to-json logic issues

[sbulen]

Basic Information

I've been helping a forum member with a very large & problematic upgrade, 3.3M+ posts. This is one of the issues faced. This is a 2.0 => 2.1 upgrade. Forum is MariaDB, MyISAM, latin1.

The upgrade crashes repeatedly at the json conversion step, before the utf8 conversion. The fatal error is caused by this query:

Note that it is trying to decrement the value of the 'memberlist_cache' setting via updateSettings. The DB query is failing because it's a serialized string, not a numeric value... Why did it do that? The json_encode of the unserialized string comes back false. The call to updateSettings with a new value of false tells upgradeSettings to set the string to 'value - 1', which fails.

EDIT: After further tests & diagnosing the issue, I don't think this has anything to do with MariaDB vs MySQL, nor CLI vs browser. It has nothing to do with how things are escaped.

The root, I believe, is more basic than that... json_encode ONLY works on utf8... And we're not on utf8 yet, at this stage of the upgrade... When you try to json_encode a string with a singe-byte, latin1 umlaut in it, it fails & returns false.

Steps to reproduce

Expected result

No response

Actual result

No response

Version/Git revision

2.1.7 & 3.0 Alpha 4

Database Engine

All

Database Version

MySQL 8.4.4 & MariaDB 11.5.2

PHP Version

8.4.5

Logs

Additional Information

This is followup to the forum threads regarding failed upgrades & restores:
https://www.simplemachines.org/community/index.php?topic=594485.0
https://www.simplemachines.org/community/index.php?topic=594487.0

[sbulen]

Peeling the onion...

At start, this upgrade took 18 hours & would not complete. It timed out repeatedly, & crashed during the serialize_to_json step. The serialize_to_json step was partially completed, and the parts that were completed were mostly incorrect.

Steps taken:

• Convert to InnoDB before upgrading. This reduced the time of execution from 18 hours to < 5 hours. The reason is that MyISAM tables must be rebuilt during most ALTER TABLE operations. And the upgrade has A LOT of ALTER TABLE operations on the smf_messages table. Don't do that.
• Change the upgrader's set_time_limit calls to set_time_limit(0) - unlimited. The upgrader no longer crashes silently on timeouts, & no longer needs restarts. It just runs all the way through, like it should.
• Add $db_character_set = 'latin1'; to Settings.php prior to upgrade. This causes SMF & the upgrader to issue an explicit SET NAMES, and queries return the proper latin1 values as stored in the DB. After doing this, the serialize_to_json step is 99% successful.

The above 3 steps make the upgrader far faster & cleaner. It just runs, in a fraction of the time, and does a far better job on the json conversion. However...

Open issue:

• serialize_to_json still crashes under some circumstances. The circumstance is when the string includes extended latin1 characters, such as single-byte umlauts.

Why? Because json_encode ONLY operates on utf8, and those extended latin1 characters are not valid utf8.

In this instance, there are usernames with extended characters in the memberlist_cache... Although the memberlist_cache is now being properly unserialized, the subsequent json_encode is failing & returning false due to invalid utf8 chars. This triggers the 'value - 1' in updateSettings(), and the crash.

There is a json_encode option to ignore invalid utf8. This avoids the crash, however, json_encode then substitutes entity encoded values in their place, e.g., '\u0665\u01a5'. But even this is incomplete - there are many chars it simply strips (yikes!!!)... I haven't found a set of json_encode flags that just leave the string alone.

Still exploring solutions.

[sbulen]

In the error above, where MariaDB was asked to decrement the value of memberlist_cache & instead blew up...

The next obvious question is... what about MySQL?

This is what MySQL did when asked to decrement the memberlist_cache string: 😆

[sbulen]

This isn't helping... It should be $new_sting, not $string... So it's not even using the fixed serialized strings.

SMF/other/upgrade.php

Line 3528 in 2a89e0b

$data = @safe_unserialize($string);

[jdarwood007]

Send the fixes to move the InnoDB around for 3.0. I don't know if we can for 2.1. Seems like it shouldn't cause any possible issues, but I would like to see what @Sesquipedalian feels on that.

The timeout issue, send for both.

For the character set fix.. This seems dangerous if the character set is actually UTF-8 already to specify latin1.

The new_string fix should be sent up as well.

[sbulen]

Yeah, I wasn't going to send InnoDB conversion up for 2.1. Fortunately, 3.0 already does it. Just sharing for info. I certainly wasn't aware before this that, for these operations, MyISAM is dramatically slower, like 3-4x slower.

The others are pretty straightforward.

The charset issues are actually getting the db to treat latin1 like latin1... I specified latin1 above because the test DB I'm working with is latin1.