[MDEV-20228] `mysql_upgrade` fails on every version upgrade: "ERROR 1267 (HY000) at line 7: Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'" Created: 2019-08-01  Updated: 2019-09-13  Resolved: 2019-09-13

Status: Closed
Project: MariaDB Server
Component/s: Character Sets, Server
Affects Version/s: 10.4.7, 10.4
Fix Version/s: 10.4.9

Type: Bug Priority: Critical
Reporter: pgnd Assignee: Alexander Barkov
Resolution: Fixed Votes: 3
Labels: None

Issue Links:
Duplicate
duplicates MDEV-20251 mysql_upgrade from 10.4.6 10.4.7 failure Closed

 Description   

after each build/install of new MariaDB version, e.g., from 10.4.7 ->

	mysqld -V
 
		mysqld  Ver 10.4.8-MariaDB-log for Linux on x86_64 (Source distribution)
 
 
 
	initially config'd & installed with
 
		...
 
		 -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci \
 
		  -DWITH_EXTRA_CHARSETS=all \
 
		...

exec of subsequent

	mysql_upgrade

fails with

	mysql_upgrade
 
		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

that^^ occurs when config includes

	[client]
 
	default-character-set = utf8mb4
 
 
 
	[server]
 
	init-connect          = 'SET NAMES utf8mb4'
 
	collation-server      = utf8mb4_unicode_ci
 
	character-set-server  = utf8mb4

editing to

	[client]
 
	#default-character-set = utf8mb4
 
 
 
	[server]
 
	#init-connect          = 'SET NAMES utf8mb4'
 
	#collation-server      = utf8mb4_unicode_ci
 
	#character-set-server  = utf8mb4

allows the upgrade to proceed

	mysql_upgrade
 
		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'
 
		Phase 5/7: Fixing table and database names
 
		Phase 6/7: Checking and upgrading tables
 
		Processing databases
 
		information_schema
 
		performance_schema
 
		Phase 7/7: Running 'FLUSH PRIVILEGES'
 
		OK

after which edit BACK to

	[client]
 
	default-character-set = utf8mb4
 
 
 
	[server]
 
	init-connect          = 'SET NAMES utf8mb4'
 
	collation-server      = utf8mb4_unicode_ci
 
	character-set-server  = utf8mb4

works, without apparent issue, for subsequent runtime

 Comments   
Comment by Matthias Fechner [ 2019-09-02 ]

I see a similar problem while execute `mysql_upgrade -u root -p` for version `10.4.7`:
```
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1267 (HY000) at line 7: Illegal mix of collations (ascii_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 59: Illegal mix of collations (ascii_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 168: Illegal mix of collations (ascii_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operatio n 'like'
ERROR 1267 (HY000) at line 179: Illegal mix of collations (ascii_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operatio n 'like'
ERROR 1267 (HY000) at line 195: Illegal mix of collations (ascii_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operatio n 'like'
ERROR 1267 (HY000) at line 322: Illegal mix of collations (ascii_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operatio n 'like'
ERROR 1267 (HY000) at line 393: Illegal mix of collations (ascii_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operatio n 'like'
ERROR 1267 (HY000) at line 400: Illegal mix of collations (ascii_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operatio n 'like'
ERROR 1267 (HY000) at line 407: Illegal mix of collations (ascii_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operatio n 'like'
FATAL ERROR: Upgrade failed
```
The upgrade to version `10.4.6` was no problem.
Maybe a bug implemented with version `10.4.7`?

Comment by Alexander Barkov [ 2019-09-05 ]

Repeatable with this test file:

-- source include/mysql_upgrade_preparation.inc
 
-- source include/have_working_dns.inc
 
-- source include/have_innodb.inc
 
-- source include/have_partition.inc
 
 
 
let $MYSQLD_DATADIR= `select @@datadir`;
 
 
 
SET sql_mode="";
 
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
 
 
 
--echo #
 
--echo # Changing character_set_client and collation_connection
 
--echo # for the VIEW mysql.user to utf8mb4/utf8mb4_unicode_ci,
 
--echo # to emulate that mysql.user was created by 'mysqld --bootstrap'
 
--echo # using mysqld compiled with
 
--echo # -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci
 
--echo #
 
 
 
--disable_query_log
 
let $def= `SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHE
 
--eval ALTER VIEW mysql.user AS $def;
 
--enable_query_log
 
 
 
SELECT CHARACTER_SET_CLIENT, COLLATION_CONNECTION
 
FROM INFORMATION_SCHEMA.VIEWS
 
WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME='user';
 
 
 
--echo # Running mysql_upgrade
 
--exec $MYSQL_UPGRADE --default-character-set=utf8mb4 --force 2>&1
 
--file_exists $MYSQLD_DATADIR/mysql_upgrade_info
 
--remove_file $MYSQLD_DATADIR/mysql_upgrade_info

mtr returns the following output:

==============================================================================
 
 
 
TEST                                      RESULT   TIME (ms) or COMMENT
 
--------------------------------------------------------------------------
 
 
 
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
 
SET sql_mode="";
 
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
 
#
 
# Changing character_set_client and collation_connection
 
# for the VIEW mysql.user to utf8mb4/utf8mb4_unicode_ci,
 
# to emulate that mysql.user was created by 'mysqld --bootstrap'
 
# using mysqld compiled with
 
# -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci
 
#
 
SELECT CHARACTER_SET_CLIENT, COLLATION_CONNECTION
 
FROM INFORMATION_SCHEMA.VIEWS
 
WHERE TABLE_SCHEMA='mysql' AND TABLE_NAME='user';
 
CHARACTER_SET_CLIENT	COLLATION_CONNECTION
 
utf8mb4	utf8mb4_unicode_ci
 
# Running mysql_upgrade
 
main.mysql_upgrade-20228 'innodb'        [ fail ]
 
        Test ended at 2019-09-05 13:35:37
 
 
 
CURRENT_TEST: main.mysql_upgrade-20228
 
mysqltest: At line 29: exec of '/home/bar/maria-git/server.10.4/client/mysql_upgrade --defaults-file=/home/bar/maria-git/server.10.4/mysql-test/var/my.cnf --default-character-set=utf8mb4 --force 2>&1' failed, error: 256, status: 1, errno: 11
 
Output from before failure:
 
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
 
 
 
 - saving '/home/bar/maria-git/server.10.4/mysql-test/var/log/main.mysql_upgrade-20228-innodb/' to '/home/bar/maria-git/server.10.4/mysql-test/var/log/main.mysql_upgrade-20228-innodb/'
 
--------------------------------------------------------------------------
 
The servers were restarted 0 times
 
Spent 0.000 of 5 seconds executing testcases
 
 
 
Failure: Failed 1/1 tests, 0.00% were successful.

Comment by Alexander Barkov [ 2019-09-05 ]

Hi Sergei,

Please review a patch:

https://github.com/MariaDB/server/commit/4595666a15c248c7b382f24e72652895fe8ba255

Comment by Dominique Ottello [ 2019-09-12 ]

Hi,
Exactly the same problem (except for collation names) on Windows 7 and Windows 10 64 bits.
The fatal error always occurs with 10.4.8, even after a blank installation.
download mariadb-10.4.8-win32.zip

unzip into j:\wamp\bin\mariadb\
It is :
j:\wamp\bin\mariadb\mariadb-10.4.8-win32\
renamed into :
j:\wamp\bin\mariadb\mariadb10.4.8\
Create mysql database by :
Nota : command windows is "administrator"
cd /d j:\wamp\bin\mariadb\mariadb10.4.8\bin
mysql_install_db.exe --default-user --datadir=j:\wamp\bin\mariadb\mariadb10.4.8\data
Result is OK:
Running bootstrap
2019-09-12 13:33:53 0 [Note] j:\wamp\bin\mariadb\mariadb10.4.8\bin\mysqld.exe (mysqld 10.4.8-MariaDB) starting as process 9984 ...
Creating my.ini file
Creation of the database was successful

j:\wamp\bin\mariadb\mariadb10.4.8\bin>

J:\wamp\bin\mariadb\mariadb10.4.8\data\my.ini file deleted and replaced by
J:\wamp\bin\mariadb\mariadb10.4.8\my.ini

Create service with :
j:\wamp\bin\mariadb\mariadb10.4.8\bin>mysqld.exe --install-manual wampmariadb
Service successfully installed.

Start service with :
net start wampmariadb
Service wampmariadb start
Service wampmariadb is started

InnoDB: using atomic writes.
2019-09-12 13:41:29 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2019-09-12 13:41:29 0 [Note] InnoDB: Uses event mutexes
2019-09-12 13:41:29 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-09-12 13:41:29 0 [Note] InnoDB: Number of pools: 1
2019-09-12 13:41:29 0 [Note] InnoDB: Using SSE2 crc32 instructions
2019-09-12 13:41:29 0 [Note] InnoDB: Initializing buffer pool, total size = 256M, instances = 1, chunk size = 128M
2019-09-12 13:41:29 0 [Note] InnoDB: Completed initialization of buffer pool
2019-09-12 13:41:29 0 [Note] InnoDB: Resizing redo log from 2*50331648 to 2*67108864 bytes; LSN=139836
2019-09-12 13:41:29 0 [Note] InnoDB: Starting to delete and rewrite log files.
2019-09-12 13:41:29 0 [Note] InnoDB: Setting log file .\ib_logfile101 size to 67108864 bytes
2019-09-12 13:41:29 0 [Note] InnoDB: Setting log file .\ib_logfile1 size to 67108864 bytes
2019-09-12 13:41:29 0 [Note] InnoDB: Renaming log file .\ib_logfile101 to .\ib_logfile0
2019-09-12 13:41:29 0 [Note] InnoDB: New log files created, LSN=139836
2019-09-12 13:41:29 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2019-09-12 13:41:29 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-09-12 13:41:29 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-09-12 13:41:29 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2019-09-12 13:41:29 0 [Note] InnoDB: 10.4.8 started; log sequence number 139836; transaction id 21
2019-09-12 13:41:29 0 [Note] InnoDB: Loading buffer pool(s) from J:\wamp\bin\mariadb\mariadb10.4.8\data\ib_buffer_pool
2019-09-12 13:41:29 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-09-12 13:41:29 0 [Note] Server socket created on IP: '::'.
2019-09-12 13:41:29 0 [Note] InnoDB: Buffer pool(s) load completed at 190912 14:41:29
2019-09-12 13:41:29 0 [Note] Reading of all Master_info entries succeeded
2019-09-12 13:41:29 0 [Note] Added new Master_info '' to hash table
2019-09-12 13:41:29 0 [Note] wampmariadb: ready for connections.
Version: '10.4.8-MariaDB' socket: '' port: 3306 mariadb.org binary distribution

Verify that mariadb console is OK:
mysql.exe -u root -p
Result:
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.4.8-MariaDB 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)]> exit
Bye

Run mysql_upgrade.exe
mysql_upgrade.exe --user=root --host=localhost --password=
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 (latin1_swedish_ci,COERCIBLE) and (cp850_general_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 59: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (cp850_general_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 168: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (cp850_general_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 179: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (cp850_general_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 195: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (cp850_general_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 322: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (cp850_general_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 393: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (cp850_general_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 400: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (cp850_general_ci,COERCIBLE) for operation 'like'
ERROR 1267 (HY000) at line 407: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (cp850_general_ci,COERCIBLE) for operation 'like'
FATAL ERROR: Upgrade failed
j:\wamp\bin\mariadb\mariadb10.4.8\bin>

Comment by Sergei Golubchik [ 2019-09-13 ]

ok to push

Generated at Thu Feb 08 08:57:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.