[Maria-discuss] clean MDB 10.4.7 install fails `mysql_upgrade` with FATAL ERROR: "Illegal mix of collations (utf8mb4_unicode_ci, COERCIBLE) and (utf8mb4_general_ci, COERCIBLE) for operation 'like'"
I'm setting up a new MDB instance, mysqld -V mysqld Ver 10.4.7-MariaDB-log for Linux on x86_64 (Source distribution) I've configured my charsets/collations according to: https://mariadb.com/kb/en/library/setting-character-sets-and-collations/#exa... my config includes cat /usr/local/etc/mariadb/my.cnf [client] default-character-set = utf8mb4 ... [mysqld] init-connect = 'SET NAMES utf8mb4' collation-server = utf8mb4_unicode_ci character-set-server = utf8mb4 ... [mysql] default-character-set = utf8mb4 ... MDB was built with ... -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DWITH_EXTRA_CHARSETS=all ... I initialize data with: /opt/mariadb/scripts/mysql_install_db --user=mysql --datadir=/data/db/mariadb --defaults-file=/usr/local/etc/mariadb/my.cnf No error during initialization, & subsequently MDB starts as expected. But if I next exec `mysql_upgrade`, it FAILS with Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.global_priv OK mysql.gtid_slave_pos OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.roles_mapping OK mysql.servers OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.transaction_registry OK Phase 2/7: Installing used storage engines... Skipped Phase 3/7: Fixing views mysql.user OK Phase 4/7: Running 'mysql_fix_privilege_tables' ERROR 1267 (HY000) at line 7: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like' ERROR 1267 (HY000) at line 59: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like' ERROR 1267 (HY000) at line 168: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like' ERROR 1267 (HY000) at line 179: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like' ERROR 1267 (HY000) at line 195: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like' ERROR 1267 (HY000) at line 322: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like' ERROR 1267 (HY000) at line 393: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like' ERROR 1267 (HY000) at line 400: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like' ERROR 1267 (HY000) at line 407: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like' FATAL ERROR: Upgrade failed checking what's installed mysqlshow +--------------------+ | Databases | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ and the charset/collation mysql SHOW CREATE DATABASE information_schema; SHOW CREATE DATABASE mysql; SHOW CREATE DATABASE performance_schema; SHOW CREATE DATABASE test; | information_schema | CREATE DATABASE `information_schema` /*!40100 DEFAULT CHARACTER SET utf8 */ | | mysql | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ | performance_schema | CREATE DATABASE `performance_schema` /*!40100 DEFAULT CHARACTER SET utf8 */ | | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ | looks to be a mix ... and the source of the errors. What config etc. needs to be changed to avoid the upgrade errors, and get charsets/collations correctly set up at data initialization -- as spec'd in my.cnf?
On 21.06.19 07:31, PGNet Dev wrote: blind educated guess:
init-connect = 'SET NAMES utf8mb4'
init-connect is only executed for non-SUPER users
But if I next exec `mysql_upgrade`, it FAILS with and you're probably running mysql_upgrade as "root", or another user with "SUPER" privileges?
-- Hartmut Holzgraefe - Principal Support Engineer (EMEA) MariaDB Corporation - http://www.mariadb.com/
my config includes
cat /usr/local/etc/mariadb/my.cnf
[client] default-character-set = utf8mb4 ...
[mysqld] init-connect = 'SET NAMES utf8mb4' collation-server = utf8mb4_unicode_ci character-set-server = utf8mb4 ...
[mysql] default-character-set = utf8mb4 ...
Can't say anything about the particular bug (haven’t touched 10.4.x yet), but wanted to comment on your configuration. If everything is UTF8 imo a more simple way to "force" particular character set is to do this way: [mysqld] character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci skip-character-set-client-handshake So the server ignores what the clients sends and you don't need to do 'SET NAMES .. ' on every connect (for example on older php versions/mysql binaries the default charset was 'latin1' and if the programmer forgot to add 'set names' query could mess up the db). rr
I'm not clear on why root/superuser would NOT be functional here; that doesn't make a lot of sense to me. I need to read up further about that. And I note the proposed 'skip' workaround ... but isn't it just that -- a workaround, around an existing 'problem'? Do note from my OP, the existing/installed charset/collation for the 4 initialized DBs | information_schema | CREATE DATABASE `information_schema` /*!40100 DEFAULT CHARACTER SET utf8 */ | | mysql | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ | | performance_schema | CREATE DATABASE `performance_schema` /*!40100 DEFAULT CHARACTER SET utf8 */ | | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ | Given the config in my.cnf already defined at time of 1st data initialization, it seems that that's an initial problem, no? Shouldn't the initialization result be all charset/collation 'correctly' set, as specified? Also, noting in the errant script mariadb_10_4/build/scripts/mysql_fix_privilege_tables.sql posted fyi here, https://paste.fedoraproject.org/paste/DQXBxBbyBMQS7eCZRgEwbA you can see the numerous ... COLLATE utf8_general_ci ... (as well as a couple of ... COLLATE utf8_bin ...) which I'd guess are causing those ERRORs I see.
changing [mysqld] - init-connect = 'SET NAMES utf8mb4' + skip-character-set-client-handshake collation-server = utf8mb4_unicode_ci character-set-server = utf8mb4 does successfully workaround the problem; 'mysql_fix_privilege_tables', and the rest of the update succeeds, ... Phase 4/7: Running 'mysql_fix_privilege_tables' Phase 5/7: Fixing table and database names Phase 6/7: Checking and upgrading tables Processing databases information_schema performance_schema test Phase 7/7: Running 'FLUSH PRIVILEGES' OK notably, immediately changing it BACK [mysqld] + init-connect = 'SET NAMES utf8mb4' - skip-character-set-client-handshake collation-server = utf8mb4_unicode_ci character-set-server = utf8mb4 and exec'ing mysql_upgrade --force STILL, now works, ... Phase 4/7: Running 'mysql_fix_privilege_tables' Phase 5/7: Fixing table and database names Phase 6/7: Checking and upgrading tables Processing databases information_schema performance_schema test Phase 7/7: Running 'FLUSH PRIVILEGES' OK not sure what that tells me -- whether this is a one-time-fix issue, or that the subsequent '--force' is simply not ATM sensitive to the problem, this time
participants (3)
-
Hartmut Holzgraefe -
PGNet Dev -
Reinis Rozitis