[MDEV-6349] innodb_table_stats/innodb_index_stats broken after upgrade from 5.6 Created: 2014-06-16  Updated: 2023-03-09  Resolved: 2014-08-05

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.12
Fix Version/s: 10.0.13

Type: Bug Priority: Major
Reporter: Andrew Garner Assignee: Sergei Golubchik
Resolution: Duplicate Votes: 1
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-5151 mysql_upgrade does not fix "last_upda... Closed
Relates
relates to MDEV-30483 After upgrade to 10.6 from Mysql 5.7 ... Closed
relates to MDEV-30809 mysql_upgrade is not upgrading the ty... Stalled

 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.



 Comments   
Comment by Elena Stepanova [ 2014-06-17 ]

bar,

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

Comment by Cédric PEINTRE [ 2014-06-19 ]

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

Comment by Sergei Golubchik [ 2014-08-05 ]

duplicate of MDEV-5151

Generated at Thu Feb 08 07:11:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.