Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.6.7
-
Ubuntu 20.04, EC2 Instance.
Description
I have set all default character sets to utf8mb4, for quite a few years. When trying to upgrade from 10.6 to 10.7, or even when use mariadb-backup to backup and restore to a new server, I get the following errors at start up, or running mariadb-upgrade
2022-07-22 19:32:09 15 [ERROR] Incorrect definition of table mysql.proc: expected the type of column 'db' at position 0 to have character set 'utf8mb3' but found character set 'utf8mb4'.
2022-07-22 19:32:09 15 [ERROR] Incorrect definition of table mysql.event: expected the type of column 'name' at position 1 to have character set 'utf8mb3' but found character set 'utf8mb4'.
2022-07-22 19:32:09 15 [ERROR] Incorrect definition of table mysql.event: expected the type of column 'definer' at position 3 to have character set 'utf8mb3' but found character set 'utf8mb4'.
2022-07-22 19:32:09 15 [ERROR] Incorrect definition of table mysql.event: expected the type of column 'character_set_client' at position 18 to have character set 'utf8mb3' but found character set 'utf8mb4'.
2022-07-22 19:32:09 15 [ERROR] Incorrect definition of table mysql.event: expected the type of column 'collation_connection' at position 19 to have character set 'utf8mb3' but found character set 'utf8mb4'.
2022-07-22 19:32:09 15 [ERROR] Incorrect definition of table mysql.event: expected the type of column 'db_collation' at position 20 to have character set 'utf8mb3' but found character set 'utf8mb4'.
I can alter this table and then dump it with mysqldump and i'll see the character set correctly as utf8mb3. But if i restart the server it goes back to utf8mb4. Also if i run mysql-upgrade on a new upgraded 10.6->10.7 it will do the same thing, if i dump it right before proc shows all character sets as utf8mb3, after it will show utf8mb4, just like a restart and the upgrade fails.
I have removed the default character set from the server configuration, but that behaves the same way. I noticed in Index.xml of charsets, there is only a utf8mb3, not sure if that is important, but saw a note on another issue.
I'm guessing it may have something to do with https://jira.mariadb.org/browse/MDEV-26863, as that is the only thing i can find that comes close to this issue.
I have gotten around this on a single server restores/rebuilds, i'll update this with that detail, but on the production and staging clusters its an upgrade blocker.
I'm hoping i'm in some odd state and that someone can simply help me get out of it. But also thought it may be a bug from the utf8->utf8mb3 change. as these tables have been utf8mb4 for years from setting the default character set on the server and client, rather than just utf8.
Thanks for all you do.
Attachments
Issue Links
- duplicates
-
-
- Closed
-
Activity
In order for the above to work. You have to (i'm not done testing)
- run the alter
- upgrade the database
- run the alter
- check the logs.
I'll update this in a bit b/c i'm not sure this is the exact order and it may differ per instance. I have yet to make it to the galera cluster.
I have been able to work around this, thank you for the comment on /etc/mysql/debian-start, though i would not have wanted to go there b/c it just masks the error. In order to get by the error i either commented out #ExecStartPost=/etc/mysql/debian-start in /etc/systemd/system/multi-user.target.wants/mariadb.service. I have seen this still crop up on galera nodes. Want to ignore, but want to know more.
I need to/should do a full debug on the script that is sourced in /etc/mysql/debian-start but i don't see how it could revert to a different character set. I can live with a win, but fearful it could come up again. These scripts were never changed on install.
It seems to be MDEV-28915. You likely have old_mode='' and that's why mysql_upgrade changes system tables to utf8mb4. Debian runs mysql_upgrade in background, so tables change for you at some point after the server has started.
I'll wait for you to confirm that you have old_mode='' and then will close this issue as a duplicate.
got a reply per email
It seemed i did have that, and saw there was a setting for UTF8_IS_MTF8MB3 or similar, that i tried to put into old_mode= but seems much better to leave it commented out.
ok. So in the end, while i did think it would have something to do with /etc/mysql/debian-start it never had a configuration where it was told to use any other character set than the one that is set for normal tables utf8mb4 utf8mb3 are still for system_table_charsets. But why would we have a latin1 charset in 10.6 on a table that wanted to be utf8mb3.
I was able to add the new repository to ubuntu 22.04, after disabling /etc/mysql/debian-start and upgrade correctly. There has to be an easier way, no? I have yet to move to a cluster, hoping it goes the same, will update here. Also 22.04 sees the deprecation of apt-key and apt-key add-repository so the majority of online help sites will still require that you fix this if you don't know whats sup.