Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.11
Description
NOTE: The information below will refer only to innodb_table_stats table for simplicity but it can be reproduced exactly the same way for innodb_index_stats table. Also, this was reproduced using MySQL 5.7.38 and MariaDB 10.6.11 version but other versions could be affected by this.
After upgrading a fresh installed DB without any changes from MySQL 5.7.38 to MariaDB 10.6.11 (including running mysql_upgrade tool), the server is printing the following errors:
Mar 08 00:01:13 ip-172-31-35-234.us-west-2.compute.internal mariadbd[3373]: 2023-03-08 0:01:13 13 [ERROR] InnoDB: Column last_update in table mysql.innodb_table_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL
|
Mar 08 00:01:14 ip-172-31-35-234.us-west-2.compute.internal mariadbd[3373]: 2023-03-08 0:01:14 13 [ERROR] InnoDB: Column last_update in table mysql.innodb_table_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL
|
Mar 08 00:01:14 ip-172-31-35-234.us-west-2.compute.internal mariadbd[3373]: 2023-03-08 0:01:14 13 [ERROR] InnoDB: Column last_update in table mysql.innodb_table_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL
|
Mar 08 00:01:14 ip-172-31-35-234.us-west-2.compute.internal mariadbd[3373]: 2023-03-08 0:01:14 13 [ERROR] InnoDB: Fetch of persistent statistics requested for table `sys`.`sys_config` 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.
|
Mar 08 00:01:14 ip-172-31-35-234.us-west-2.compute.internal mariadbd[3373]: 2023-03-08 0:01:14 13 [ERROR] InnoDB: Column last_update in table mysql.innodb_table_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL
|
Mar 08 00:01:14 ip-172-31-35-234.us-west-2.compute.internal mariadbd[3373]: 2023-03-08 0:01:14 13 [ERROR] InnoDB: Column last_update in table mysql.innodb_table_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL
|
Mar 08 00:01:14 ip-172-31-35-234.us-west-2.compute.internal mariadbd[3373]: 2023-03-08 0:01:14 13 [ERROR] InnoDB: Column last_update in table mysql.innodb_table_stats is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL
|
As described here by marc_r in MDEV-5151, it seems that the PRTYPE at innodb_sys_columns is not properly upgraded:
# BEFORE UPGRADE
|
mysql> SELECT a.name AS `Table`, b.TABLE_ID,b.NAME,b.POS,b.MTYPE,b.PRTYPE,b.LEN from innodb_sys_tables a, innodb_sys_columns b where a.name like 'mysql/innodb_table_stats' and a.table_id=b.table_id order by b.table_id,b.pos; |
+--------------------------+----------+--------------------------+-----+-------+---------+-----+ |
| Table | TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN | |
+--------------------------+----------+--------------------------+-----+-------+---------+-----+ |
.
|
.
|
| mysql/innodb_table_stats | 27 | last_update | 2 | 3 | 525575 | 4 |
|
.
|
.
|
+--------------------------+----------+--------------------------+-----+-------+---------+-----+ |
6 rows in set (0.00 sec) |
 |
# AFTER UPGRADE |
MariaDB [information_schema]> SELECT a.name AS `Table`, b.TABLE_ID,b.NAME,b.POS,b.MTYPE,b.PRTYPE,b.LEN from innodb_sys_tables a, innodb_sys_columns b where a.name like 'mysql/innodb_table_stats' and a.table_id=b.table_id order by b.table_id,b.pos; |
+--------------------------+----------+--------------------------+-----+-------+---------+-----+ |
| Table | TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN | |
+--------------------------+----------+--------------------------+-----+-------+---------+-----+ |
.
|
.
|
| mysql/innodb_table_stats | 27 | last_update | 2 | 3 | 525575 | 4 |
|
.
|
.
|
+--------------------------+----------+--------------------------+-----+-------+---------+-----+ |
6 rows in set (0.001 sec) |
The PRTYPE has a different value for a fresh MariaDB 10.6.11 installation:
MariaDB [information_schema]> SELECT a.name AS `Table`, b.TABLE_ID,b.NAME,b.POS,b.MTYPE,b.PRTYPE,b.LEN from innodb_sys_tables a, innodb_sys_columns b where a.name like 'mysql/innodb_table_stats' and a.table_id=b.table_id order by b.table_id,b.pos; |
+--------------------------+----------+--------------------------+-----+-------+---------+-----+ |
| Table | TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN | |
+--------------------------+----------+--------------------------+-----+-------+---------+-----+ |
.
|
.
|
| mysql/innodb_table_stats | 18 | last_update | 2 | 3 | 526087 | 4 |
|
.
|
.
|
+--------------------------+----------+--------------------------+-----+-------+---------+-----+ |
6 rows in set (0.019 sec) |
Looking at the code, it seems that scripts/mysql_system_tables_fix.sql already contains changes to address this problem:
# update table_name and timestamp fields in the innodb stat tables |
alter table innodb_index_stats modify last_update timestamp not null default current_timestamp on update current_timestamp, modify table_name varchar(199); |
But the alter table is not having any effect, I have tried running the statement manually having the same result.
The output of DESCRIBE mysql.innodb_table_stats; shows the same before and after running mysql_upgrade and the only two ways I've found to fix the PRTYPE is either running optimize table mysql.innodb_table_stats; as described at marc_r comment, which results in a table rebuild, or dumping and restoring the database. This makes me thing that the issue is only related with the specific PRTYPE field not being updated and once the table is recreated the right PRTYPE value is used.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-