0

Recently I installed MariaDB 10.5.9 from the Rocky Linux 8 @appstream repo. After configuring the database server to use utf8mb4_unicode_ci for everything, I hit a strange error with my database client tool. The error was reproducible on the command line:

$ MariaDB > SELECT user FROM mysql.user WHERE is_role = 'N' ;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='

I "fixed" the error by dropping and re-creating the view, which forced the view to use utf8mb4_general_ci.

However, I do not understand why the query chokes, when the query below which uses utf8mb4_unicode_ci with a utf8mb4_general_ci connection collation does not:

MariaDB > select * from wp_options where option_name = 'N';
Empty set (0.000 sec)

Both datasets are utf8mb4_unicode_ci:

MariaDB > show create table wp_options;
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| wp_options | CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `option_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `autoload` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`),
  KEY `autoload` (`autoload`)
) ENGINE=InnoDB AUTO_INCREMENT=62383 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB > show create view mysql.user;
+------+------------------------------------------------------+----------------------+----------------------+
| View | Create View                                          | character_set_client | collation_connection |
+------+------------------------------------------------------+----------------------+----------------------+
| user | CREATE ALGORITHM=UNDEFINED DEFINER=`mariadb.sys` ... | utf8mb4              | utf8mb4_unicode_ci   |
+------+------------------------------------------------------+----------------------+----------------------+
1 row in set (0.001 sec)

MariaDB > SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME  FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'mysql' ORDER BY TABLE_NAME;
| mysql        | user                      | Create_view_priv         | utf8mb4_unicode_ci |
| mysql        | user                      | Repl_client_priv         | utf8mb4_unicode_ci |
| mysql        | user                      | Repl_slave_priv          | utf8mb4_unicode_ci |
| mysql        | user                      | Reload_priv              | utf8mb4_unicode_ci |
| mysql        | user                      | Drop_priv                | utf8mb4_unicode_ci |
| mysql        | user                      | Create_priv              | utf8mb4_unicode_ci |
| mysql        | user                      | Delete_priv              | utf8mb4_unicode_ci |
| mysql        | user                      | Update_priv              | utf8mb4_unicode_ci |
| mysql        | user                      | Insert_priv              | utf8mb4_unicode_ci |
| mysql        | user                      | Select_priv              | utf8mb4_unicode_ci |
| mysql        | user                      | Password                 | utf8mb4_bin        |
| mysql        | user                      | User                     | utf8_bin           |
| mysql        | user                      | Shutdown_priv            | utf8mb4_unicode_ci |
| mysql        | user                      | Process_priv             | utf8mb4_unicode_ci |
| mysql        | user                      | File_priv                | utf8mb4_unicode_ci |
| mysql        | user                      | Execute_priv             | utf8mb4_unicode_ci |
| mysql        | user                      | Lock_tables_priv         | utf8mb4_unicode_ci |
| mysql        | user                      | Create_tmp_table_priv    | utf8mb4_unicode_ci |
| mysql        | user                      | Super_priv               | utf8mb4_unicode_ci |
| mysql        | user                      | Show_db_priv             | utf8mb4_unicode_ci |
| mysql        | user                      | Alter_priv               | utf8mb4_unicode_ci |
| mysql        | user                      | Index_priv               | utf8mb4_unicode_ci |
| mysql        | user                      | References_priv          | utf8mb4_unicode_ci |
| mysql        | user                      | Grant_priv               | utf8mb4_unicode_ci |
| mysql        | user                      | Host                     | utf8_bin           |
+--------------+---------------------------+--------------------------+--------------------+

Here are the system character sets and collations:

MariaDB > show variables like '%char%';
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| character_set_client     | utf8mb4                      |
| character_set_connection | utf8mb4                      |
| character_set_database   | utf8mb4                      |
| character_set_filesystem | binary                       |
| character_set_results    | utf8mb4                      |
| character_set_server     | utf8mb4                      |
| character_set_system     | utf8                         |
| character_sets_dir       | /usr/share/mariadb/charsets/ |
+--------------------------+------------------------------+
8 rows in set (0.001 sec)

MariaDB > show variables like '%collat%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

What causes the first query to fail but not the second?

jamieburchell
  • 761
  • 1
  • 5
  • 18
  • that is simnle mysql,user has no unicode collation your table does and the session is also unicode – nbk Jan 15 '22 at 18:08
  • Are you able to expand on what this means a little more? If you can write this as an answer I can accept, I'd be extremely grateful. This issue has had me perplexed for a couple of days. Why would "no collation" cause the error of illegal mixed collations? – jamieburchell Jan 15 '22 at 18:41
  • the proplem lies in `= 'N'` it has the cpllation of the session which is utf8mb4_unicode_ci wp_options is also utf8mb4_unicode_ci but mysql.user has utf8mb4_general_ci the dfault when it was created – nbk Jan 15 '22 at 18:44
  • The connection collation is utf8mb4_general_ci according to `show variables`. I still don't understand why the second query works with this apparent mix between connection and storage collation. – jamieburchell Jan 15 '22 at 18:51
  • the session will be in utf8mb4_unicode_ci as the server uses it and if you didn't specify other collation when connecting – nbk Jan 15 '22 at 18:54
  • I have updated my question to include what I believe shows that the data in mysql.user is utf8mb4_unicode_ci on a fresh installation. – jamieburchell Jan 15 '22 at 19:16
  • *MariaDB > show create view mysql.user;* You do not check what you post? look at this at least now... *I do not understand why the query chokes, when the query below which uses utf8mb4_unicode_ci with a utf8mb4_general_ci collation does not:* Because the collation of the column is explicitly set and not coercible, hence it can be altered/ignored. – Akina Jan 15 '22 at 19:23
  • The data is stored as utf8mb4_unicode_ci in wp_options table. The data in the mysql.user view is also stored or represented as utf8mb4_unicode_ci. The connection I'm sending the queries in is utf8mb4_general_ci. One query works, one doesn't. I don't see the difference, sorry. I have updated my question to clarify I was talking about the connection being utf8mb4_general_ci and nothing else. – jamieburchell Jan 15 '22 at 19:43
  • *the session will be in utf8mb4_unicode_ci as the server uses it and if you didn't specify other collation when connecting* - the default according to the docs and show variables when not specifying is utf8mb4_general_ci. – jamieburchell Jan 15 '22 at 19:50
  • Change `collation_connection` to `utf8mb4_unicode_ci`, perhaps when connecting. – Rick James Jan 16 '22 at 22:07
  • That does indeed fix the first query, but why is the second unaffected? My web app connects with utf8mb4_general_ci and doesn't hit any problems querying utf8mb4_unicode_ci stored data. It seems literally only this user view is affected. – jamieburchell Jan 16 '22 at 22:51

0 Answers0