Getting InnoDb internal Errors on every query run

Question

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.

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

Have you upgraded from an older version or is this the first install? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented May 30, 2016 at 13:13
I have done RnD and found one solution dba.stackexchange.com/a/48189/91142 but not sure about it — Jatin Seth
– Jatin Seth, Commented May 30, 2016 at 13:30
Did your installing involve any of the weird things mentioned in those questions? ( ie. replacing or deleting ibdata files?) — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented May 30, 2016 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
– Jatin Seth, Commented May 30, 2016 at 13:37

Demis Palma ツ · Accepted Answer · 2016-07-28 12:12:47Z

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

Stack Exchange Network

Getting InnoDb internal Errors on every query run

1 Answer 1

Your Answer

Linked

Hot Network Questions

Getting InnoDb internal Errors on every query run

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions