Waitman Gobble shared a comment of this post/conversation

Sat, 01 Jun 2024 14:04:26 -0400 from hubzilla

The previous message had flag emojis in it originally, but I got a posting error because they couldn't insert in the body column of the item table. Comparing my hubzilla database to the source schema, I see that my tables have character set utf8mb3 while the source schema currently says utf8mb4. Casting about online, I found a script that would generate all the alter table commands where needed, such as:

ALTER DATABASE hubzilla CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Looking at my streams database, I see that the tables have a collation of utf8mb4_0900_ai_ci, which appears to be newer and be accent insensitive.

Any thought as to whether which collation to use and whether running this script to change all the character columns is a wise thing to do?

elmussol

Sat, 01 Jun 2024 14:06:46 -0400 from Streams at els Mussols

Ask @Waitman Gobble. Exactly the same issue a couple of weeks ago.

Waitman Gobble

Sat, 01 Jun 2024 14:57:23 -0400 from Get Ready To Rumbly

I haven't fixed mine yet. At least now i know i wasn't the one to change to 3 byte utf8 :)

I was thinking i need to change every column though. Doesn't that statemeng in your example only change the "default" (for new columns?) I will test

elmussol

Sat, 01 Jun 2024 14:58:39 -0400 from Streams at els Mussols

Sorry, misunderstood.

Marshall Sutherland

Sat, 01 Jun 2024 15:02:21 -0400 from hubzilla

You are right. I just grabbed the first line of the script, not noticing that it was the alter database line. The next one is (and subsequent ones like):

ALTER TABLE hubzilla.`abconfig` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Marshall Sutherland

Sat, 01 Jun 2024 18:26:38 -0400 from hubzilla

Hmm, I wonder if this was why I sometimes see people sharing posts from people who show up as null for their name. Maybe that will go away now.

Waitman Gobble

Sat, 01 Jun 2024 21:49:21 -0400 from Get Ready To Rumbly

Could be. AFAIK i need to make a script to iterate through all the tables, do a 'show create table', find all the mb3 and do an alter for those. Downside is possible data loss. For example 24/3=8 but 24/4=6 at least that's what i read.

Marshall Sutherland

Sun, 02 Jun 2024 00:17:42 -0400 from hubzilla

The string lengths are in characters, not bytes, so you shouldn't be losing data, just making the column take more space.

I would have shared the script I used to generate the final script, but with it not including the default values, I didn't want someone to try using it and have the same issue. I found a couple online. If I had known about this problem, maybe I'd have found one that included everything properly. Here is the link where I got the script I used. None of the suggestions appear to include the default value, but it is a place to start.

Waitman Gobble

Sun, 02 Jun 2024 07:08:32 -0400 from Get Ready To Rumbly

This is what i read, i glanced through the tables and don't think it's an issue with streams though.

How to support full Unicode in MySQL databases · Mathias Bynens

Step 4: Check the maximum length of columns and index keys
This is probably the most tedious part of the whole upgrading process.

When converting from utf8 to utf8mb4, the maximum length of a column or index key is unchanged in terms of bytes. Therefore, it is smaller in terms of characters, because the maximum length of a character is now four bytes instead of three.

Marshall Sutherland

Sun, 02 Jun 2024 09:47:55 -0400 from hubzilla

Oh my, that is not what I expected. I guess I am trying to apply what I know of MS SQL Server to mySQL, which doesn't always match. Now that I think about it, I'm not sure what it does with variable length strings. I'm used to dealing with single-byte char strings or double-byte char strings. Maybe MS SS does have variable length char types that I've never actually worked with.

Still, I wouldn't expect to risk losing data since my existing data had been limited to 3-byte characters. It is only new data, which could include 4-byte characters, that could result in smaller string lengths.

Waitman Gobble

Tue, 04 Jun 2024 19:07:27 -0400 last edited: Tue, 04 Jun 2024 19:10:40 -0400 from Get Ready To Rumbly

I finally got around to converting my tables from mb3 to mb4. Here's what i did if anyone needs it. I didn't use "sed" because i didn't want to mess up any posts that had utf8mb3 in the content, etc. Maybe there's a way to do that with sed but i didn't take the time to research it.

$$$do this 'outside' your web root$$$

stop nginx and dump the database

# service nginx stop
# mysqldump rumbly > rumbly.sql

make a php script to replace utf8mb3 with utf8mb4 (i randomly picked 160 char limit on line, should avoid all "inserts"

<?php

$handle = fopen("rumbly.sql", "r");
if ($handle) {
    while (($line = fgets($handle)) !== false) {
                if (strlen($line)<160)
                {
                                $line=str_replace('utf8mb3','utf8mb4',$line);
                }
                echo $line;
    }

    fclose($handle);
}

run it

# php -q fixutf8.php  > rumbly4.sql

compare the # of lines just in case :)

# wc -l rumbly4.sql
# wc -l rumbly.sql

create a 'new' database and assign existing user perm

# mysql <<< "CREATE DATABASE rumbly4"
# mysql <<< "GRANT ALL PRIVILEGES ON rumbly4.* to 'rumbly'@'127.0.0.1'"
# mysql <<< "FLUSH PRIVILEGES"

edit .htconfig.php in streams path
change the $db_data to the new db ie 'rumbly4'

# vi /www/rumbly/streams/.htconfig.php

import the data

# mysql rumbly4 < rumbly4.sql

start web server

# service nginx start

when you 'feel good' you can drop the old database and delete the 2 sql files, etc.

Marshall Sutherland

Tue, 04 Jun 2024 19:19:47 -0400 from hubzilla

By comparison, I was swinging on the trapeze without a net,