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;
  • I just copied all the binary files from one server to another. With the same MySQL version? if not, have you executed mysql_upgrade? – Akina Sep 6 '18 at 4:23
  • Same version. But I ran mysql_upgrade --force just to be sure. It didn't fix it. – Glenn J. Schworak Sep 6 '18 at 10:08
  • The pages suggest rebuilding the table, which I have done. One suggestion suggested turning off persistant stats. That does for sure get the error out of my log file but it doesn't actually fix the problem. That is like saying my car sterio is scratchy and turning it off so the scratchy sound stops. Sure, the sound stopped but the problem still exists. I used this command to turn the feature off temporarily in case anyone cares: set global innodb_stats_persistent=0 – Glenn J. Schworak Sep 6 '18 at 11:17
  • The pages suggest rebuilding the table, which I have done. No, they suggest rebuilding the pack of tables. – Akina Sep 6 '18 at 11:21
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.

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.