[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'"

21 Jun 2019

      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?

[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'"

PGNet Dev