Marshall Sutherland

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

dwatney@hub.farthinghalearms.com

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?

show all 12 comments

Sat, 01 Jun 2024 14:06:46 -0400

elmussol

elmussol@streams.elsmussols.net

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

Sat, 01 Jun 2024 14:57:23 -0400

Waitman Gobble

hello@rumbly.net

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

Sat, 01 Jun 2024 14:58:39 -0400

elmussol

elmussol@streams.elsmussols.net

Sorry, misunderstood.

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

Marshall Sutherland

dwatney@hub.farthinghalearms.com

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;

Sat, 01 Jun 2024 18:25:23 -0400

Marshall Sutherland

dwatney@hub.farthinghalearms.com

Well, that was a pain in the ass. The the SQL that generated that SQL didn't include default values, so I lost all the default values of all the character columns. Fortunately, my test of the original problem errored because of that, so I noticed the problem. I changed the generated code to include default values. Fortunately, most of them were empty string, so I was able to put that in every line, then go back and change the ones that weren't (or had no default). I hope I did it all right.

I once had a tool which would compare the schema code to the actual database and tell me what didn't match. Maybe I still do, but don't know what it is called. That would have been very handy.

Oh, and the original problem is solved: 🇮🇳 🇬🇧 🇪🇺 🇺🇲 🇿🇦 🇮🇩 🇵🇰 🇮🇷 🇮🇱 🇷🇸 🇲🇽 🇻🇪 🇧🇾 🇷🇺 🇧🇬

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

Marshall Sutherland

dwatney@hub.farthinghalearms.com

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.

Sat, 01 Jun 2024 21:49:21 -0400

Waitman Gobble

hello@rumbly.net

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.

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

Marshall Sutherland

dwatney@hub.farthinghalearms.com

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.

Sun, 02 Jun 2024 07:08:32 -0400

Waitman Gobble

hello@rumbly.net

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.

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

Marshall Sutherland

dwatney@hub.farthinghalearms.com

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.

Tue, 04 Jun 2024 19:07:27 -0400 last edited: Tue, 04 Jun 2024 19:10:40 -0400

Waitman Gobble

hello@rumbly.net

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.

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

Marshall Sutherland

dwatney@hub.farthinghalearms.com

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