Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. 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'"

    XMLWordPrintable

Details

    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

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              pgnd pgnd
              Votes:
              3 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.