Details

    Description

      mysql_upgrade command is failing with below error on 10.11.7 version.

      my.cnf character/collation configuration:

      [mysqld] 
      character-set-server=utf8 
      collation-server=utf8_unicode_ci
      

      Output:

      # mysql_upgrade --force
      Phase 1/8: 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/8: Installing used storage engines... Skipped
      Phase 3/8: Running 'mysql_fix_privilege_tables'
      ERROR 1267 (HY000) at line 3127: Illegal mix of collations (utf8mb3_general_ci,IMPLICIT) and (utf8mb3_unicode_ci,IMPLICIT) for operation '='
      ERROR 1267 (HY000) at line 3176: Illegal mix of collations (utf8mb3_general_ci,IMPLICIT) and (utf8mb3_unicode_ci,IMPLICIT) for operation '='
      FATAL ERROR: Upgrade failed

      Attachments

        Activity

          alice Alice Sherepa added a comment -

          What was the version before the upgrade?

          alice Alice Sherepa added a comment - What was the version before the upgrade?
          mathnao mathnao added a comment -

          In my example, it is using same version.

          mathnao mathnao added a comment - In my example, it is using same version.

          It seems the problem is repeatable under these two conditions at the same time:

          • The server is running with a non-default collation for utf8:

            [mysqld] 
            character-set-server=utf8 
            collation-server=utf8_unicode_ci
            

          • The db.opt file in the `sys` database is missing (e.g. was deleted in a mistake)

          The script scripts/sys_schema/before_setup.sql should be modified to handle such situations, to something like this:

          CREATE DATABASE IF NOT EXISTS sys DEFAULT CHARACTER SET utf8;
          ALTER DATABASE sys DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; -- NEW STATEMENT
          USE sys;
          

          bar Alexander Barkov added a comment - It seems the problem is repeatable under these two conditions at the same time: The server is running with a non-default collation for utf8: [mysqld] character-set-server=utf8 collation-server=utf8_unicode_ci The db.opt file in the `sys` database is missing (e.g. was deleted in a mistake) The script scripts/sys_schema/before_setup.sql should be modified to handle such situations, to something like this: CREATE DATABASE IF NOT EXISTS sys DEFAULT CHARACTER SET utf8; ALTER DATABASE sys DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; -- NEW STATEMENT USE sys;
          bar Alexander Barkov added a comment - - edited

          The problem is repeatable with this MTR test:

          --source include/mysql_upgrade_preparation.inc
           
          let $MYSQLD_DATADIR= `select @@datadir`;
           
          --echo #
          --echo # MDEV-34014 mysql_upgrade failed
          --echo #
           
          SHOW CREATE DATABASE sys;
           
          --echo # Emulate db.opt file was removed in a mistake
          --remove_file $MYSQLD_DATADIR/sys/db.opt
          FLUSH TABLES;
          SHOW CREATE DATABASE sys;
           
          --exec $MYSQL_UPGRADE --force 2>&1
          --remove_file $MYSQLD_DATADIR/mysql_upgrade_info
          SHOW CREATE DATABASE sys;
          

          bar Alexander Barkov added a comment - - edited The problem is repeatable with this MTR test: --source include/mysql_upgrade_preparation.inc   let $MYSQLD_DATADIR= `select @@datadir`;   --echo # --echo # MDEV-34014 mysql_upgrade failed --echo #   SHOW CREATE DATABASE sys;   --echo # Emulate db.opt file was removed in a mistake --remove_file $MYSQLD_DATADIR/sys/db.opt FLUSH TABLES; SHOW CREATE DATABASE sys;   --exec $MYSQL_UPGRADE --force 2>&1 --remove_file $MYSQLD_DATADIR/mysql_upgrade_info SHOW CREATE DATABASE sys;

          Hello Wlad,

          Please review a patch fixing this issue:

          https://github.com/MariaDB/server/commit/5d66b3bab153563116ca41e1dd05c5ac306c7085

          Thanks.

          bar Alexander Barkov added a comment - Hello Wlad, Please review a patch fixing this issue: https://github.com/MariaDB/server/commit/5d66b3bab153563116ca41e1dd05c5ac306c7085 Thanks.

          Looks good to me.

          wlad Vladislav Vaintroub added a comment - Looks good to me.

          People

            bar Alexander Barkov
            mathnao mathnao
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.