Problems updating 1.x to 2.1 RC3

[Douglas]

Step 5: Convert serialized strings to JSON

Overall Progress: 69%
Step Progress: 53%

Converting data from serialize to JSON...
Completed 7 out of 13 tables.
Current Table: "qanda"

Time Elapsed: 457 mins, 53 seconds.

I did a search, and it seems someone else reported this (which was supposedly fixed for 2.1 RC2), but there's not a lot of info about this.

(Please do not complain about me running a 1.x branch... this is why I'm updating, hah!)

This is a test copy, and not the live site, thankfully. repair_settings.php has already been run to change/verify URL changes/path statement changes, etc. There is no reference to the old path.

FYI, this would be considered to be a big board, as well (one of the ones I've run... and the first one I switched to (then) YaBB SE (and what got me into YSE/SMF)).

Server:                 MariaDB
Server version:         10.2.19-MariaDB-log MariaDB Server

[shawnb61]

The problem here is that your database is creating tables in utf8mb4. 

SMF doesn't support mb4. 

You should change the default collation for that database to utf8_general_ci & retry.

[albertlast]

from where you see that mb4 is used?
And where did he defined mb4?

[shawnb61]

from where you see that mb4 is used?
And where did he defined mb4?

We are seeing this A LOT...

Most recent mysql installs are using utf8mb4 as their default charset/collation.

And SMF chokes on that, even 2.1. 

The problem is that 2.0 & 2.1 both default to the database-level default charset/collation when creating new tables. 

The solution is to set the database-level default that is used when creating new tables to something that SMF supports - utf8. 

Tables that have issues with mb4, e.g., qanda & members, will fail on a variety of issues when the target database is defaulting to mb4.

[Douglas]

Code Select Expand

Good catch!  I changed the DB and the SMF tables over to utf8_general_ci.

Now, it's stuck on an earlier step:

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: "Update members ip with ipv6 support" (46 of 69)


Overall Progress: 44%
Step Progress: 61%
Convert member ips: 99.9%
Time Elapsed: 132 mins, 41 seconds.

With the understanding that I am appreciative of the help working through these issues, it does beg the question: Why does the upgrade script not perform the collation change, as needed?

I was able to execute, via phpMyAdmin, the following query on all SMF tables, without any issues:
[codelALTER TABLE [PREFIX]_admin_info_files COLLATE utf8_general_ci;

It would be logical to do the collation check and changes as part of the upgrade process, before executing DB changes (right now, it's step 6 while DB changes are step 4. I would recommend moving the conversion to step 4, just before DB changes, especially since the original issue (JSON) also needs this.

Again, I am appreciative of the help, y'all!

[shawnb61]

I would check the ip columns for strange, non-ip values.   Sometimes we see values like 'unknown' in there.

We have an open issue for that...

[albertlast]

from where you see that mb4 is used?
And where did he defined mb4?

We are seeing this A LOT...

Most recent mysql installs are using utf8mb4 as their default charset/collation.

And SMF chokes on that, even 2.1. 

The problem is that 2.0 & 2.1 both default to the database-level default charset/collation when creating new tables. 

The solution is to set the database-level default that is used when creating new tables to something that SMF supports - utf8. 

Tables that have issues with mb4, e.g., qanda & members, will fail on a variety of issues when the target database is defaulting to mb4.

when i read the docu right,
than also utf8 get to be an alias for utf8mb4 so in newer version you had to use utf8mb3 when you want to use it.
and also utf8mb3 looks to be slower than mb4: https://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/