[MDEV-29156] mysql.proc is corrupt - has encoding utf8mb4 expecting utf8mb3 Created: 2022-07-22  Updated: 2022-09-14  Resolved: 2022-09-14

Status: Closed
Project: MariaDB Server
Component/s: Character Sets
Affects Version/s: 10.6.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Jeff Dyke Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: character-set, upgrade
Environment:

Ubuntu 20.04, EC2 Instance.

Attachments: Text File db04_dpkg.txt     Text File db05_dpkg.txt     File db4_proc_after_sst_and_restart-1.sql     File db4_proc_after_sst_and_restart.sql     File db5_proc_after_sst.sql     File dbreplica_dpkg.out     File mariadb_post_reload_dpkg.out     File proc.MAD     File proc.MAI     File proc.frm     File restore_target_dpkg.out    
Issue Links:
Duplicate
duplicates MDEV-28915 mysql_upgrade fails due to old_mode="... Closed

 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.



 Comments   
Comment by Elena Stepanova [ 2022-07-25 ]

I have set all default character sets to utf8mb4

Do you mean that you have it set in the config files, or that you also converted all tables (including system tables) into utf8mb4? Can you paste your mysql.proc definition from 10.6, before any attempts to upgrade?

I can alter this table and then dump it with mysqldump and i'll see the character set correctly as utf8mb3

ALTER from what to what? Can you provide the ALTER command and the resulting dump (schema only, no data)?

if i restart the server it goes back to utf8mb4

Do you mean that you see a table as utf8mb3, you don't do anything to the table or the data, restart the server, don't run any mysql_upgrade or other tools, and see the table as utf8mb4? If so, can you enable general log in your config file, perform this exercise and attach the resulting general log?

Please also attach the output of 

show global variables

from the MariaDB server
and of

dpkg -l | grep -Ei 'mysql|mariadb|percona'
 
dpkg -S /usr/share/mysql/

from the system?

Comment by Jeff Dyke [ 2022-07-25 ]

Thanks for the detailed questions. I wanted to cull this down to one item, as i'm starting to think more and more there was a botched upgrade, and i'm paying the price a couple upgrades later. I will try to answer all your questions as i go.

Replica/Backup Server Settings

I made a backup from the cluster using mariadb-backup the proc table is defined as 

CREATE TABLE `proc` (
 
  `db` char(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '',
 
  `name` char(64) NOT NULL DEFAULT '',
 
  `type` enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') NOT NULL,
 
  `specific_name` char(64) NOT NULL DEFAULT '',
 
  `language` enum('SQL') NOT NULL DEFAULT 'SQL',
 
  `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
 
  `is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
 
  `security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
 
  `param_list` blob NOT NULL,
 
  `returns` longblob NOT NULL,
 
  `body` longblob NOT NULL,
 
  `definer` varchar(384) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '',
 
  `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '',
 
  `comment` text CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
 
  `character_set_client` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
 
  `collation_connection` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
 
  `db_collation` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
 
  `body_utf8` longblob DEFAULT NULL,
 
  `aggregate` enum('NONE','GROUP') NOT NULL DEFAULT 'NONE',
 
  PRIMARY KEY (`db`,`name`,`type`)
 
) ENGINE=Aria DEFAULT CHARSET=utf8mb3 PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Stored Procedures'

The global variables around character sets

*************************** 1. row ***************************
 
Variable_name: character_set_client
 
        Value: utf8mb4
 
*************************** 2. row ***************************
 
Variable_name: character_set_connection
 
        Value: utf8mb4
 
*************************** 3. row ***************************
 
Variable_name: character_set_database
 
        Value: utf8mb4
 
*************************** 4. row ***************************
 
Variable_name: character_set_filesystem
 
        Value: binary
 
*************************** 5. row ***************************
 
Variable_name: character_set_results
 
        Value: utf8mb4
 
*************************** 6. row ***************************
 
Variable_name: character_set_server
 
        Value: utf8mb4
 
*************************** 7. row ***************************
 
Variable_name: character_set_system
 
        Value: utf8mb3
 
*************************** 8. row ***************************
 
Variable_name: character_sets_dir
 
        Value: /usr/share/mysql/charsets/

The output of dpkg is in dbreplica_dpkg.out
Output of dpkg -S /usr/share/mysql/:

mariadb-server-core-10.6, mariadb-server-10.6: /usr/share/mysql

New Instance for backup restore

I have then created a new ec2 instance and installed mariadb 10.6. Not surprising, but including the same outputs.
dpkg -l | grep -Ei 'mysql|mariadb|percona' located here: restore_target_dpkg.out 

$> dpkg -S /usr/share/mysql/
 
mariadb-server-10.6, mariadb-server-core-10.6: /usr/share/mysql

mysql.proc schema on this server is identical to the above.

MariaDB [(none)]> show global variables like 'character%' \G
 
*************************** 1. row ***************************
 
Variable_name: character_set_client
 
        Value: utf8mb4
 
*************************** 2. row ***************************
 
Variable_name: character_set_connection
 
        Value: utf8mb4
 
*************************** 3. row ***************************
 
Variable_name: character_set_database
 
        Value: utf8mb4
 
*************************** 4. row ***************************
 
Variable_name: character_set_filesystem
 
        Value: binary
 
*************************** 5. row ***************************
 
Variable_name: character_set_results
 
        Value: utf8mb4
 
*************************** 6. row ***************************
 
Variable_name: character_set_server
 
        Value: utf8mb4
 
*************************** 7. row ***************************
 
Variable_name: character_set_system
 
        Value: utf8mb3
 
*************************** 8. row ***************************
 
Variable_name: character_sets_dir
 
        Value: /usr/share/mysql/charsets/

Post Upgrade

mysql.proc after a mariadb-backup. 

MariaDB [(none)]> show create table mysql.proc \G
 
*************************** 1. row ***************************
 
       Table: proc
 
Create Table: CREATE TABLE `proc` (
 
  `db` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
 
  `name` char(64) NOT NULL DEFAULT '',
 
  `type` enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') NOT NULL,
 
  `specific_name` char(64) NOT NULL DEFAULT '',
 
  `language` enum('SQL') NOT NULL DEFAULT 'SQL',
 
  `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
 
  `is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
 
  `security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
 
  `param_list` blob NOT NULL,
 
  `returns` longblob NOT NULL,
 
  `body` longblob NOT NULL,
 
  `definer` varchar(384) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
 
  `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '',
 
  `comment` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
 
  `character_set_client` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
 
  `collation_connection` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
 
  `db_collation` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
 
  `body_utf8` longblob DEFAULT NULL,
 
  `aggregate` enum('NONE','GROUP') NOT NULL DEFAULT 'NONE',
 
  PRIMARY KEY (`db`,`name`,`type`)
 
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Stored Procedures'

Other answers after upgrade, in case they changed.

jeff@devbackuptest  salt dpkg -S /usr/share/mysql/
 
mariadb-server-10.6, mariadb-server-core-10.6: /usr/share/mysql

salt dpkg -l | grep -Ei 'mysql|mariadb|percona' mariadb_post_reload_dpkg.out

So here the errors, from the first post, appear on each restart in error.log.

Trying the alter and restart

use mysql;
 
alter table proc modify `modified` timestamp NOT NULL DEFAULT current_timestamp(); -- this is also very interesting, the default 0 filled date string, which was not compatible
 
alter table proc CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin;
 
alter table proc modify `db` char(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '';
 
alter table proc modify `name` char(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '';
 
alter table proc modify `definer` varchar(384) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '';
 
alter table proc modify `character_set_client` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '';
 
alter table proc modify `collation_connection` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '';
 
alter table proc modify `db_collation` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '';

Proc Schema after alter

CREATE TABLE `proc` (
 
  `db` char(64) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
 
  `name` char(64) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
 
  `type` enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') COLLATE utf8mb3_bin NOT NULL,
 
  `specific_name` char(64) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
 
  `language` enum('SQL') COLLATE utf8mb3_bin NOT NULL DEFAULT 'SQL',
 
  `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') COLLATE utf8mb3_bin NOT NULL DEFAULT 'CONTAINS_SQL',
 
  `is_deterministic` enum('YES','NO') COLLATE utf8mb3_bin NOT NULL DEFAULT 'NO',
 
  `security_type` enum('INVOKER','DEFINER') COLLATE utf8mb3_bin NOT NULL DEFAULT 'DEFINER',
 
  `param_list` blob NOT NULL,
 
  `returns` longblob NOT NULL,
 
  `body` longblob NOT NULL,
 
  `definer` varchar(384) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
 
  `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 
  `modified` timestamp NOT NULL DEFAULT current_timestamp(),
 
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') COLLATE utf8mb3_bin NOT NULL DEFAULT '',
 
  `comment` text COLLATE utf8mb3_bin NOT NULL,
 
  `character_set_client` char(32) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
 
  `collation_connection` char(32) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
 
  `db_collation` char(32) COLLATE utf8mb3_bin NOT NULL DEFAULT '',
 
  `body_utf8` longblob DEFAULT NULL,
 
  `aggregate` enum('NONE','GROUP') COLLATE utf8mb3_bin NOT NULL DEFAULT 'NONE',
 
  PRIMARY KEY (`db`,`name`,`type`)
 
) ENGINE=Aria DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Stored Procedures';

Restarting mariadb-server, and the proc schema

CREATE TABLE `proc` (
 
  `db` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
 
  `name` char(64) NOT NULL DEFAULT '',
 
  `type` enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') NOT NULL,
 
  `specific_name` char(64) NOT NULL DEFAULT '',
 
  `language` enum('SQL') NOT NULL DEFAULT 'SQL',
 
  `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
 
  `is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
 
  `security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
 
  `param_list` blob NOT NULL,
 
  `returns` longblob NOT NULL,
 
  `body` longblob NOT NULL,
 
  `definer` varchar(384) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
 
  `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 
  `modified` timestamp NOT NULL DEFAULT current_timestamp(),
 
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '',
 
  `comment` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
 
  `character_set_client` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
 
  `collation_connection` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
 
  `db_collation` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
 
  `body_utf8` longblob DEFAULT NULL,
 
  `aggregate` enum('NONE','GROUP') NOT NULL DEFAULT 'NONE',
 
  PRIMARY KEY (`db`,`name`,`type`)
 
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Stored Procedures';

An interesting thing about this schema, is that modified is still set to the altered definition, but character sets have changed. I realize this is kind of apples to oranges, but may be a hint.

Perhaps i should do some digging in information schema? To finish where i started, this seems like an upgrade gone wrong before this one.

Thanks again for any input!

Comment by Jeff Dyke [ 2022-07-25 ]

Wanted to follow up on the information_schema comment. All columns in proc from the COLUMNS table that require a character set are set to uft8mb3

Also i thought it would helpful, to demystify the backup and restore process. Even thought its pretty basic.

Backup

sudo /usr/bin/mariabackup -u root --backup --binlog-info=AUTO --safe-slave-backup --target-dir="${BACKUP_DIR}"/"${DATE}"

Restore

sudo mariabackup --prepare --target-dir=$LOCAL_RESTORE
 
MYSQL_PID=$(pgrep mariadb)
 
if [ "n${MYSQL_PID}" != "n" ]; then
 
  sudo kill -9 $MYSQL_PID;
 
fi
 
sudo rm -rf $MYSQL_DIR/* $MYSQL_LOG_DIR/*
 
sudo mariabackup --move-back --target-dir=$LOCAL_RESTORE
 
sudo chown -R mysql:mysql $MYSQL_DIR $MYSQL_LOG_DIR
 
sudo systemctl daemon-reload
 
sudo service mariadb restart

Comment by Jeff Dyke [ 2022-08-05 ]

Syncing newly created db04 via mysqldump to cluster

Today i was hoping i could get around this on the cluster nodes, by creating a new mariadb 10.6.8 server running ubuntu, and using 

wsrep_sst_method=mysqldump

And setting the permissions properly. After this i have a new node that was sync'd to the cluster, and the following looks fine

Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 38
 
Server version: 10.6.8-MariaDB-1:10.6.8+maria~focal-log mariadb.org binary distribution
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> show global variables like '%gtid%' \G
 
*************************** 1. row ***************************
 
Variable_name: gtid_binlog_pos
 
        Value: 0-1-6653825,1-1-1
 
*************************** 2. row ***************************
 
Variable_name: gtid_binlog_state
 
        Value: 0-1-6653825,1-1-1
 
*************************** 3. row ***************************
 
Variable_name: gtid_cleanup_batch_size
 
        Value: 64
 
*************************** 4. row ***************************
 
Variable_name: gtid_current_pos
 
        Value: 0-1-8613953,1-1-609
 
*************************** 5. row ***************************
 
Variable_name: gtid_domain_id
 
        Value: 1
 
*************************** 6. row ***************************
 
Variable_name: gtid_ignore_duplicates
 
        Value: OFF
 
*************************** 7. row ***************************
 
Variable_name: gtid_pos_auto_engines
 
        Value:
 
*************************** 8. row ***************************
 
Variable_name: gtid_slave_pos
 
        Value: 0-1-8613953,1-1-609
 
*************************** 9. row ***************************
 
Variable_name: gtid_strict_mode
 
        Value: OFF
 
*************************** 10. row ***************************
 
Variable_name: wsrep_gtid_domain_id
 
        Value: 0
 
*************************** 11. row ***************************
 
Variable_name: wsrep_gtid_mode
 
        Value: ON
 
11 rows in set (0.001 sec)

and with a proc table with the following db4_proc_after_sst_and_restart.sql (its correct)

SST to db05 using rsync, with db04 as doner.

Both machines were set to wsrep_sst_method=rsync, the donor was the newly created db04
After starting mariadb on db05
– wait for sst –
sudo mysqldump mysql proc.sql > db5_proc_after_sst.sql which has character set to utf8mb4, with the same errors reported above about the character sets being wrong.

So it stays correct with a mysqldump sst, but using that as a donor node still converts it it into utfmb4 with wsrep_sst_method=rsync.

Any help is appreciated, i really just want to get past this. Thanks

Comment by Jeff Dyke [ 2022-08-05 ]

Wanted to share the outputs asked for earlier

db04 (populated with wsrep_sst_method=mysqldump)

Character sets

 
 
MariaDB [(none)]> show global variables like 'character_set%' \G
 
*************************** 1. row ***************************
 
Variable_name: character_set_client
 
        Value: utf8mb4
 
*************************** 2. row ***************************
 
Variable_name: character_set_connection
 
        Value: utf8mb4
 
*************************** 3. row ***************************
 
Variable_name: character_set_database
 
        Value: utf8mb4
 
*************************** 4. row ***************************
 
Variable_name: character_set_filesystem
 
        Value: binary
 
*************************** 5. row ***************************
 
Variable_name: character_set_results
 
        Value: utf8mb4
 
*************************** 6. row ***************************
 
Variable_name: character_set_server
 
        Value: utf8mb4
 
*************************** 7. row ***************************
 
Variable_name: character_set_system
 
        Value: utf8mb3
 
*************************** 8. row ***************************
 
Variable_name: character_sets_dir
 
        Value: /usr/share/mysql/charsets/
 
8 rows in set (0.001 sec)

Dpkg output

jeff@db04  ~ sudo dpkg -S /usr/share/mysql
 
mariadb-server-10.6, mariadb-server-core-10.6: /usr/share/mysql

sudo dpkg -l | grep -Ei 'mysql|mariadb|percona' > db04_dpkg.txt

db05 (synced from donor db04 with wsrep_sst_method=rsync)

Character sets

*************************** 1. row ***************************
 
Variable_name: character_set_client
 
        Value: utf8mb4
 
*************************** 2. row ***************************
 
Variable_name: character_set_connection
 
        Value: utf8mb4
 
*************************** 3. row ***************************
 
Variable_name: character_set_database
 
        Value: utf8mb4
 
*************************** 4. row ***************************
 
Variable_name: character_set_filesystem
 
        Value: binary
 
*************************** 5. row ***************************
 
Variable_name: character_set_results
 
        Value: utf8mb4
 
*************************** 6. row ***************************
 
Variable_name: character_set_server
 
        Value: utf8mb4
 
*************************** 7. row ***************************
 
Variable_name: character_set_system
 
        Value: utf8mb3
 
*************************** 8. row ***************************
 
Variable_name: character_sets_dir
 
        Value: /usr/share/mysql/charsets/

h.3 Dpkg

jeff@db05  ~ sudo dpkg -S /usr/share/mysql
 
mariadb-server-10.6, mariadb-server-core-10.6: /usr/share/mysql

sudo dpkg -l | grep -Ei 'mysql|mariadb|percona' > db05_dpkg.txt

Comment by Jeff Dyke [ 2022-08-19 ]

Is there ANYONE that can give me some feed back, or ask some questions about what may retain on disc before restarts when my utf8_mb3 gets magically changed to utf8_mb4 and then fails an upgrade. I thank the first response, and think i have supplied ample updates. Just a point in a direction. I'm not asking for much.

Comment by Sergei Golubchik [ 2022-08-23 ]

I wasn't able to repeat it so far. Could you please attach your proc table? Files proc.frm, proc.MAI, proc.MAD?

Comment by Jeff Dyke [ 2022-08-24 ]

Hello Sergei. Thanks for the response. Here are proc files, the proc table has been altered, so you will see the schema showing utf8mb3. proc.frm proc.MAI proc.MAD

Happy to provide anything else. Thanks again.

Comment by Sergei Golubchik [ 2022-08-29 ]

I still cannot repeat it. But if you ALTER the table to the correct definition and on restart it goes back to the wrong one, it means that something happens when you restart the server. Debian is known to running mysql_upgrade every time MariaDB is started. See /etc/mysql/debian-start

Two thoughts: you can run mysql_upgrade manually using the commanf from debian-start, perhaps its output will reveal something useful.

Or you can simply set MYUPGRADE to, for example, /bin/true and see if it'll make any difference to the charset behavior of the mysql.proc table.

Comment by Jeff Dyke [ 2022-09-06 ]

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.

Comment by Jeff Dyke [ 2022-09-06 ]

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.

Comment by Jeff Dyke [ 2022-09-08 ]

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.

Comment by Sergei Golubchik [ 2022-09-13 ]

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.

Comment by Sergei Golubchik [ 2022-09-14 ]

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.

Generated at Thu Feb 08 10:06:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.