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

innodb_table_stats/innodb_index_stats broken after upgrade from 5.6

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.0.12
    • 10.0.13
    • None
    • 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

              serg Sergei Golubchik
              andrew.garner Andrew Garner
              Votes:
              1 Vote for this issue
              Watchers:
              4 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.