1

This is very strange. I just copied all the binary files from one server to another. The new server comes up just fine with one exception. I keep getting this error message in my log every couple minutes...

2018-09-06T01:27:56.710923Z 284261 [ERROR] InnoDB: Column table_name in table `mysql`.`innodb_table_stats` is VARCHAR(576) NOT NULL but should be VARCHAR(192) NOT NULL (length mismatch).
2018-09-06T01:27:56.711052Z 284261 [ERROR] InnoDB: Fetch of persistent statistics requested for table `mydatabase`.`mytable` 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.

Now I know for a fact that the error is bogus. I have used ALTER TABLE to change the field size all the way down to 64 and the exact same error shows. So i dropped the table and I get the error that the table is missing as expected. Then I create the table with the proper length and this error comes back. It always says the field is 576 characters no matter what the actual size is. And it is only this one field and one table. None of the other innodb_ tables are coming up with an error. And running a database check says all the tables are correct but the log loads up with this error as each table gets checked.

For the life of me, I can't think of anything else to do. Any help out there?

MySQL version: 5.7.22

Script used to drop and rebuild the table run one line at a time checking the log each time.

drop TABLE if exists `innodb_table_stats`;
CREATE TABLE if not exists `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(192) 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,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
flush tables;
Glenn J. Schworak
  • 119
  • 1
  • 5
  • 1
    Same version. But I ran mysql_upgrade --force just to be sure. It didn't fix it. – Glenn J. Schworak Sep 06 '18 at 10:08
  • Look at https://stackoverflow.com/a/16362871/10138734 and at https://dba.stackexchange.com/a/48189/150107 – Akina Sep 06 '18 at 10:21
  • Well, I miss-spoke... Switching off persistent stats stopped only the "Fetch of persistent statistics requested for table" entry. Every so often MySQL still checks that table and still loggs the "inconsistant size" error and still shows the incorrect value for the size of the field. The field really is 192 characters but still reports as 576 no matter what size I set it to. – Glenn J. Schworak Sep 06 '18 at 11:51
  • Yes, the articles suggest rebuilding several tables, not just the one. But what possible good could that do to rebuild a table that is not an issue? But yes, I have rebuilt them all way back in my early attempts to fix this and it had no impact as I expected. If rebuilding table A fixes a problem with table B, there is a very serious defect in the database engine's core code. – Glenn J. Schworak Sep 06 '18 at 12:10
  • I am not ready to call the issue fixed but it is looking good. Around 8am today I ran a script that checked and reindexed every table in every schema. It finished around noon (faster than I expected) it logged a couple index issues but nothing I would have expected to be an issue. But I have not seen a single error reported in my log since. I just ran this code to switch persistent status tracking back on and still nothing. set global innodb_stats_persistent=1 (if this holds I will call that the fix) – Glenn J. Schworak Sep 07 '18 at 00:04

1 Answers1

0

I am not 100% sure what solved the issue because I ended up writing a script to do the following and when it finished (unattended) several hours later, the errors stopped. I am betting it was the mysqlcheck command but only in conjunction with rebuilding every index on every table. Not sure which one actually was the issue.

My script did the following. (not posting the code because it is ugly and quick and dirty)

1) Alter every table only setting the engine to innodb (which they already were but this forces a rebuild) 2) ran mysqlcheck on each database one at a time

I logged all of this to a file but no errors were detected. Everything said it was OK.

So with everything saying it was OK that would make me think nothing was ever wrong but it was and it no longer is so this was just some bit of confusion in some supporting file.

Bogus error with a bogus fix but it actually worked so I don't care any more.

Glenn J. Schworak
  • 119
  • 1
  • 5