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

    XMLWordPrintable

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

            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.