Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30809

mysql_upgrade is not upgrading the type of last_update column for innodb_index_stats and innodb_table_stats tables

Details

    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

          Activity

            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.

            otto Otto Kekäläinen added a comment - 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.

            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.

            marko Marko Mäkelä added a comment - 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 .
            otto Otto Kekäläinen added a comment - PR pending review at https://github.com/MariaDB/server/pull/2555

            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.

            marko Marko Mäkelä added a comment - 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.
            danblack Daniel Black added a comment -

            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
            

            danblack Daniel Black added a comment - 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

            People

              bar Alexander Barkov
              Christianggm Christian Gonzalez
              Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.