5

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
  • Have you upgraded from an older version or is this the first install? – ypercubeᵀᴹ May 30 '16 at 13:13
  • 1
    No, It's the first install – Jatin Seth May 30 '16 at 13:15
  • I have done RnD and found one solution dba.stackexchange.com/a/48189/91142 but not sure about it – Jatin Seth May 30 '16 at 13:30
  • Did your installing involve any of the weird things mentioned in those questions? ( ie. replacing or deleting ibdata files?) – ypercubeᵀᴹ May 30 '16 at 13:34
  • No, It was smooth. I would like to mention one thing might be the reason for these errors, I changed the location of MYSQL on Server couple of weeks ago. – Jatin Seth May 30 '16 at 13:37
6

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.