[MDEV-30809] mysql_upgrade is not upgrading the type of last_update column for innodb_index_stats and innodb_table_stats tables Created: 2023-03-08  Updated: 2023-11-30

Status: Stalled
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.6.11
Fix Version/s: 10.4, 10.6, 10.11

Type: Bug Priority: Major
Reporter: Christian Gonzalez Assignee: Alexander Barkov
Resolution: Unresolved Votes: 1
Labels: innodb

Issue Links:
Relates
relates to MDEV-6349 innodb_table_stats/innodb_index_stats... Closed
relates to MDEV-29128 Possible table (mysql.innodb_table_st... Open
relates to MDEV-30483 After upgrade to 10.6 from Mysql 5.7 ... Closed
relates to MDEV-5151 mysql_upgrade does not fix "last_upda... Closed

 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.

 Comments   
Comment by Otto Kekäläinen [ 2023-03-09 ]

The same error message Error: Column last_update in table "mysql"."innodb_table_stats" is BINARY(4) NOT NULL is also in MDEV-6349, MDEV-29128 and MDEV-30483 which of many are open and the latest MDEV-30483 is open and specifically for MySQL 5.7 to MariaDB 10.6 upgrades and assigned to marko.

The issue MDEV-27044 is about upgrade test being unstable and hitting this (open since 10.6 was new) and MDEV-27517 is about upgrade from MySQL 5.7 leaking strings and resulting in (false?) errors about mysql.proc not being updated and the need to run mariadb-upgrade.

Comment by Marko Mäkelä [ 2023-03-09 ]

otto, thank you for your comment. Based on a comment in MDEV-30483, it looks like the problem may have been introduced in MariaDB 10.4 already, possibly related to MDEV-15564.

Comment by Otto Kekäläinen [ 2023-03-18 ]

PR pending review at https://github.com/MariaDB/server/pull/2555

Comment by Marko Mäkelä [ 2023-03-21 ]

I provided some feedback in MDEV-30483. I think that we must find and fix the root cause of this, instead of modifying the upgrade script.

Comment by Daniel Black [ 2023-11-30 ] 

2023-11-29 23:43:04 0 [Note] InnoDB: 10.4.32 started; log sequence number 12226685; transaction id 1794
 
2023-11-29 23:43:04 0 [Note] Plugin 'FEEDBACK' is disabled.
 
2023-11-29 23:43:04 6 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist
 
2023-11-29 23:43:04 0 [Note] Reading of all Master_info entries succeeded
 
2023-11-29 23:43:04 0 [Note] Added new Master_info '' to hash table
 
2023-11-29 23:43:04 0 [Note] mysqld: ready for connections.
 
Version: '10.4.32-MariaDB-1:10.4.32+maria~ubu2004'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  mariadb.org binary distribution
 
2023-11-29 23:43:05+00:00 [Note] [Entrypoint]: Temporary server started.
 
2023-11-29 23:43:05+00:00 [Note] [Entrypoint]: Backing up system database to system_mysql_backup_unknown_version.sql.zst
 
2023-11-29 23:43:05 9 [ERROR] InnoDB: Column last_update in table `mysql`.`innodb_table_stats` is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL (flags mismatch).
 
2023-11-29 23:43:05 9 [ERROR] InnoDB: Fetch of persistent statistics requested for table `mysql`.`gtid_executed` 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-11-29 23:43:05+00:00 [Note] [Entrypoint]: Backing up complete
 
2023-11-29 23:43:05+00:00 [Note] [Entrypoint]: Starting mariadb-upgrade
 
...
 
mysql.time_zone_transition                         OK
 
mysql.time_zone_transition_type                    OK
 
mysql.user                                         OK
 
Phase 2/7: Installing used storage engines... Skipped
 
Phase 3/7: Fixing views from mysql
 
2023-11-29 23:43:05 14 [ERROR] Column count of mysql.proc is wrong. Expected 21, found 20. Created with MariaDB 50744, now running 100432. Please use mysql_upgrade to fix this error
 
2023-11-29 23:43:05 14 [Note] View `sys`.`schema_auto_increment_columns`: the version is set to 100432, algorithm restored to be MERGE
 
2023-11-29 23:43:05 14 [Note] View `sys`.`schema_object_overview`: the version is set to 100432, algorithm restored to be TEMPTABLE
 
2023-11-29 23:43:05 14 [Note] View `sys`.`schema_redundant_indexes`: the version is set to 100432, algorithm restored to be TEMPTABLE
 
2023-11-29 23:43:05 14 [Note] View `sys`.`schema_unused_indexes`: the version is set to 100432, algorithm restored to be MERGE
 
sys.host_summary
 
Error    : Table 'performance_schema.memory_summary_by_host_by_event_name' doesn't exist
 
status   : Operation failed
 
sys.host_summary_by_file_io
 
Error    : Column count of mysql.proc is wrong. Expected 21, found 20. Created with MariaDB 50744, now running 100432. Please use mysql_upgrade to fix this error
 
...
 
(skipping sys and proc)
 
...
 
Phase 4/7: Running 'mysql_fix_privilege_tables'
 
2023-11-29 23:43:05 15 [ERROR] InnoDB: Column last_update in table `mysql`.`innodb_table_stats` is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL (flags mismatch).
 
2023-11-29 23:43:05 15 [ERROR] InnoDB: Column last_update in table `mysql`.`innodb_table_stats` is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL (flags mismatch).
 
2023-11-29 23:43:05 15 [ERROR] InnoDB: Column last_update in table `mysql`.`innodb_table_stats` is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL (flags mismatch).
 
...
 
Phase 5/7: Fixing table and database names ... Skipped
 
Phase 6/7: Checking and upgrading tables... Skipped
 
Phase 7/7: Running 'FLUSH PRIVILEGES'
 
OK
 
2023-11-29 23:43:06+00:00 [Note] [Entrypoint]: Finished mariadb-upgrade
 
2023-11-29 23:43:06+00:00 [Note] [Entrypoint]: Stopping temporary server
 
2023-11-29 23:43:06 0 [Note] mysqld (initiated by: unknown): Normal shutdown
 
...
 
2023-11-29 23:43:07 0 [Note] mysqld: Shutdown complete
 
 
 
2023-11-29 23:43:07+00:00 [Note] [Entrypoint]: Temporary server stopped
 
2023-11-29 23:43:07 0 [Note] Starting MariaDB 10.4.32-MariaDB-1:10.4.32+maria~ubu2004 source revision c4143f909528e3fab0677a28631d10389354c491 as process 1
 
...
 
2023-11-29 23:43:07 0 [Note] InnoDB: Buffer pool(s) load completed at 231129 23:43:07
 
2023-11-29 23:43:07 6 [ERROR] InnoDB: Column last_update in table `mysql`.`innodb_table_stats` is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL (flags mismatch).
 
2023-11-29 23:43:07 6 [ERROR] InnoDB: Fetch of persistent statistics requested for table `mysql`.`gtid_slave_pos` 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-11-29 23:43:07 0 [Note] Reading of all Master_info entries succeeded
 
2023-11-29 23:43:07 0 [Note] Added new Master_info '' to hash table
 
2023-11-29 23:43:07 0 [Note] mysqld: ready for connections.
 
Version: '10.4.32-MariaDB-1:10.4.32+maria~ubu2004'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution

Generated at Thu Feb 08 10:19:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.