Mysql – Getting InnoDb internal Errors on every query run

innodbMySQLmysql-5.6

I am having following errors in error.log at the run of each query due to which I am not able to use error.log properly.

2016-05-30 09:39:58 7f7c7e3cb700 InnoDB: Error: Column last_update in table "mysql"."innodb_table_stats" is INT UNSIGNED NOT NULL but should be BINARY(4) NOT NULL (type mismatch).
2016-05-30 09:39:58 7f7c7e3cb700 InnoDB: Error: Fetch of persistent statistics requested for table "mysql"."innodb_table_stats" 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.

I have checked the "mysql"."innodb_table_stats" and mysql.innodb_index_stats tables are present but still getting these errors.

The Structures for both tables are given below:

CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) UNSIGNED NOT NULL,
  `sample_size` bigint(20) UNSIGNED DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) UNSIGNED NOT NULL,
  `clustered_index_size` bigint(20) UNSIGNED NOT NULL,
  `sum_of_other_index_sizes` bigint(20) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

MySql Server details:
Server type: MySQL
Server version: 5.6.19-1~dotdeb.1 – (Debian)

I would like add more details about same, some weeks ago I was facing the some issues with default location of Mysql, So; I changed the location of Mysql by using following steps.

  1. Shutdown Mysql
  2. Move All Mysql file inside old folder to new folder
  3. Change the datadir variable inside config file my.cnf
  4. Restart the MySql Server

Best Answer

The problem is related with the internal representation of "timestamp" fields, probably caused by a bug in the database conversion procedure, occurred while upgrading from a previous version.

Exporting and importing the database "mysql" has fixed the problem in my debian server.

During the repair procedure, it's preferable to stop Apache and any other software that make use of the database, just in case.

Export the database:

mysqldump --events --quick --single-transaction mysql > mysql-dump.sql

Import back immediately after the export:

mysql mysql < mysql-dump.sql

Important: You must restart mysql service at this point to reload privileges.

service mysql restart

Of course I recommend to repair all your databases, because they could be affected. You can do it one by one by hand, or all at once with a simple script:

mysqldump="mysqldump --events --quick --single-transaction"
exclude="information_schema|performance_schema"

cd /tmp

# Backup and re-import each database on the system
for db in $(mysql -e "show databases;" -s --skip-column-names | grep -vE "($exclude)")
do
        echo "Repairing database $db"
        $mysqldump $db > $db.sql
        mysql $db < $db.sql
done
Related Question