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
        • 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.

            Gliffy Diagrams

              Attachments

                Issue Links

                is duplicated by

                Bug - A problem which impairs or prevents the functions of the product. MDEV-5151 mysql_upgrade does not fix "last_update" in "mysql.innodb_table_stats"

                • Major - Major loss of function.
                • Closed

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  Alexander Barkov,

                  Is it related to what you're working on in scope of MDEV-5377?

                  Show
                  elenst Elena Stepanova added a comment - Alexander Barkov , Is it related to what you're working on in scope of MDEV-5377 ?
                  Hide
                  Permalink
                  CEPE Cédric PEINTRE added a comment -

                  Same problem for me when migrate from Percona 5.6.16 to MariaDB 10.0.12

                  Show
                  CEPE Cédric PEINTRE added a comment - Same problem for me when migrate from Percona 5.6.16 to MariaDB 10.0.12
                  Hide
                  Permalink
                  serg Sergei Golubchik added a comment -

                  duplicate of MDEV-5151

                  Show
                  serg Sergei Golubchik added a comment - duplicate of MDEV-5151

                    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: