Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.6.11, 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
-
None
Description
After upgrade from Mysql 5.7 to MariaDB 10.3 then to 10.6, I'm now seeing the following error in the logs periodically:
"InnoDB: Column last_update in table mysql.innodb_table_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL"
The last_update column appears to be correctly defined (TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP).
Running mysql_upgrade does not resolve the issue,
2023-01-27 10:16:23 8756 [ERROR] InnoDB: Column last_update in table mysql.innodb_table_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL
2023-01-27 10:16:23 8756 [ERROR] InnoDB: Fetch of persistent statistics requested for table `cl`.`#sql-alter-6a27-2234` 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.
2023-01-27 10:16:23 8756 [ERROR] InnoDB: Column last_update in table mysql.innodb_table_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL
2023-01-27 10:16:23 8756 [ERROR] InnoDB: Column last_update in table mysql.innodb_table_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL
2023-01-27 10:16:23 8756 [ERROR] InnoDB: Column last_update in table mysql.innodb_table_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Open
-
-
-
- Stalled
-
Activity
Hello marko,
Can you please review a patch:
https://github.com/MariaDB/server/commit/fdd338fc735697d2def4c19dd92dedfd3a7bef85
Thanks!
OK to push after fixing the link issue related to the experimental data type plugin that you added for mimicing the MySQL 5.7 signed timestamp type.
The InnoDB fix simply makes the InnoDB schema check ignore the DATA_UNSIGNED flag differences for the timestamp column. When the DATA_UNSIGNED flag is missing, InnoDB will invert the sign bit both when reading and writing the data, so that comparisons of signed integers stored in big-endian format can use straight memcmp().
According to bar, the timestamps are 31-bit unsigned time_t (never before the Unix epoch of January 1970). Therefore, the sign bit in InnoDB should always be the same for all rows of a given table. Even if timestamps before 1970 were allowed in the statistics tables, this should not matter, because those columns are not indexed and therefore no other than equality comparisons inside InnoDB should take place.
According to Marko, InnoDB stores last_update column values (of the two mentioned statistical tables) directly to InnoDB record buffer without using Field methods. Values are stored directly to the InnoDB record using big-endian format.
These scripts prove that this should work with both:
- Field_timestamp0 - UINT4 based - mysql56_temporal_format=0
- Field_timestampf - BINARY(4) based - mysql56_temporal_format=1
SQL layer, which used Field methods, also always produces big-engian values, no matter what mysql56_temporal_format is:
mysql test <<END |
SET @@global.mysql56_temporal_format=0; |
CREATE OR REPLACE TABLE t1 (a CHAR(4), b TIMESTAMP, c CHAR(4)) ENGINE=InnoDB; |
INSERT INTO t1 VALUES ('xxxx', from_unixtime(0x61626364), 'xxxx'); |
FLUSH TABLES t1 for EXPORT; |
UNLOCK TABLES;
|
END
|
strings /opt/mariadb-10.6/data/test/t1.ibd
|
infimum
|
supremum
|
*xxxxabcdxxxx
|
mysql test <<END |
SET @@global.mysql56_temporal_format=1; |
CREATE OR REPLACE TABLE t1 (a CHAR(4), b TIMESTAMP, c CHAR(4)) ENGINE=InnoDB; |
INSERT INTO t1 VALUES ('yyyy', from_unixtime(0x61626364), 'yyyy'); |
FLUSH TABLES t1 for EXPORT; |
UNLOCK TABLES;
|
END
|
strings /opt/mariadb-10.6/data/test/t1.ibd
|
infimum
|
supremum
|
*yyyyabcdyyyy
|
As discussed, I think that it is a good idea to extend the regression test suite with some validation of the contents of last_update.
Theoretically, we could relax the schema check (which was refactored in 10.6) and accept both 32-bit formats.
We would need to debug and understand what happens with each value of @@global.mysql56_temporal_format, and to check which internal representations are compatible.
We might also simply ignore differences in the timestamp column definitions and on mismatch, refrain from updating the columns. When InnoDB internally updates the statistics tables in the background, it will use the InnoDB internal SQL interpreter, which does not implement or support any DEFAULT or ON UPDATE clauses.