Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.0.12
-
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
- is duplicated by
-
MDEV-5151 mysql_upgrade does not fix "last_update" in "mysql.innodb_table_stats"
- Closed
- relates to
-
MDEV-30483 After upgrade to 10.6 from Mysql 5.7 seeing "InnoDB: Column last_update in table mysql.innodb_table_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL"
- Closed
-
MDEV-30809 mysql_upgrade is not upgrading the type of last_update column for innodb_index_stats and innodb_table_stats tables
- Stalled