Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6349

innodb_table_stats/innodb_index_stats broken after upgrade from 5.6

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.0.12
    • Fix Version/s: 10.0.13
    • Component/s: None
    • Labels:
      None

      Description

      Similar to closed issue (MDEV-5151), when upgrading from MySQL 5.6.19 to MariaDB 10.0.12, innodb_table_stats are broken even after a mysql_upgrade:

      2014-06-16 19:28:22 7fbcc0f1f700 InnoDB: Error: Column last_update in table "mysql"."innodb_table_stats" is BINARY(4) NOT NULL but should be INT NOT NULL (type mismatch).
      2014-06-16 19:28:22 7fbcc0f1f700 InnoDB: Error: Fetch of persistent statistics requested for table "mysql"."gtid_slave_pos" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

      Internally this table (as created by MySQL 5.6.19 mysql_install_db) is using MYSQL_TYPE_TIMESTAMP2 (per inspection of the .frm). Changing this to MYSQL_TYPE_TIMESTAMP via the following queries and restarting the MariaDB 10.0.12 instance seems to resolve the error:

      mysql> ALTER TABLE `mysql`.`innodb_table_stats`
      	MODIFY `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
      mysql> ALTER TABLE `mysql`.`innodb_index_stats`
      	MODIFY `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

      It would be nice if mysql_upgrade would fix these tables automatically.

      This is easy to verify on a fresh "empty" mysql 5.6 install and then upgrading that data directory to MariaDB 10.0.12. Tested on CentOS 6.5 using mariadb-10.0.12-linux-x86_64.tar.gz binary tarball distribution, upgrading a default mysql_install_db from a mysql-5.6.19-linux-glibc2.5-x86_64.tar.gz binary tarball distribution.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                andrew.garner Andrew Garner
              • Votes:
                1 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: