HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Getting InnoDb internal Errors on every query run

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
innodbqueryeverygettinginternalerrorsrun

Problem

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;

Solution

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

Code Snippets

mysqldump --events --quick --single-transaction mysql > mysql-dump.sql
mysql mysql < mysql-dump.sql
service mysql restart
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

Context

StackExchange Database Administrators Q#139917, answer score: 7

Revisions (0)

No revisions yet.