[MDEV-28307] utf8 disappear in character set Created: 2022-04-13  Updated: 2022-05-29  Resolved: 2022-05-29

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

Type: Bug Priority: Major
Reporter: Artie Assignee: Daniel Black
Resolution: Incomplete Votes: 0
Labels: None
Environment:

Server version: 10.1.22-MariaDB MariaDB Server


 Description   

I get some character errors in the error log,
Incorrect definition of table mysql.db: expected the type of column 'Select_priv' at position 3 to have character set 'utf8' but found character set 'utf8mb4'.

I found there are no utf8 in the character set, and utf8mb4 is duplicated, how to fix it?

MariaDB [(none)]> SHOW CHARACTER SET like 'utf8%';
 
+---------+---------------+--------------------+--------+
 
| Charset | Description   | Default collation  | Maxlen |
 
+---------+---------------+--------------------+--------+
 
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      3 |
 
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
 
+---------+---------------+--------------------+--------+



 Comments   
Comment by Daniel Black [ 2022-04-13 ] 

Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 2
 
Server version: 10.1.22-MariaDB-1~jessie mariadb.org binary distribution
 
 
 
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> SHOW CHARACTER SET like 'utf8%';
 
+---------+---------------+--------------------+--------+
 
| Charset | Description   | Default collation  | Maxlen |
 
+---------+---------------+--------------------+--------+
 
| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
 
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
 
+---------+---------------+--------------------+--------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> show create table mysql.db\G
 
*************************** 1. row ***************************
 
       Table: db
 
Create Table: CREATE TABLE `db` (
 
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
  PRIMARY KEY (`Host`,`Db`,`User`),
 
  KEY `User` (`User`)
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'
 
1 row in set (0.01 sec)

I have no idea how you got into this state. Character sets are compiled in (as I understand it). So it shouldn't be possible to remove/alter (without editing the executable). Is /usr/share/mysql/charsets/Index.xml the correct and original for your MariaDB version?

Speaking of which, there have been 3.5 years of bug fixes on the 10.1 series, which is now end of life.

Comment by Artie [ 2022-04-13 ] 

<charset name="utf8">
 
  <family>Unicode</family>
 
  <description>UTF-8 Unicode</description>
 
  <alias>utf-8</alias>
 
  <collation name="utf8_general_ci"     id="33">
 
   <flag>primary</flag>
 
   <flag>compiled</flag>
 
  </collation>
 
  <collation name="utf8_bin"            id="83">
 
    <flag>binary</flag>
 
    <flag>compiled</flag>
 
  </collation>
 
</charset>
 
 
 
<charset name="utf8mb4">
 
  <family>Unicode</family>
 
  <description>UTF-8 Unicode</description>
 
  <alias>utf-8</alias>
 
  <collation name="utf8mb4_general_ci"     id="33">
 
   <flag>primary</flag>
 
   <flag>compiled</flag>
 
  </collation>
 
  <collation name="utf8mb4_bin"            id="83">
 
    <flag>binary</flag>
 
    <flag>compiled</flag>
 
  </collation>
 
</charset>

Yes, /usr/share/mysql/charsets/Index.xml is the correct and the original version.

Comment by Daniel Black [ 2022-04-13 ]

What is the history of the database? What previous versions was it running? What it upgrade from some other version? What version was the data originally? What distro/OS/package is the version you are running?

Comment by Artie [ 2022-04-13 ]

OS: CentOS release 6.7 (Final)
MariaDB install version is 10.1.22, and never upgrade.
MariaDB-client-10.1.22-1.el6.x86_64
MariaDB-common-10.1.22-1.el6.x86_64
MariaDB-compat-10.1.22-1.el6.x86_64
MariaDB-server-10.1.22-1.el6.x86_64

I have three nodes in the MariaDB Galera Cluster, and only this node have character problem.
The other two nodes version is 10.1.12-MariaDB.

Comment by Sergei Golubchik [ 2022-04-26 ]

There were similar issues. See, for example, MDEV-26165, does it ring any bells?

Generated at Thu Feb 08 09:59:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.