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

too long values in mysql.innodb_table_stats.table_name

Details

    Description

      create table extralongname_extralongname_extralongname_extralongname_ext (
        id int(10) unsigned not null,
        created_date date not null,
        created timestamp not null,
        primary key (created,id,created_date)
      ) engine=innodb stats_persistent=1 default charset=latin1
        partition by range (year(created_date))
        subpartition by hash (month(created_date))
        subpartitions 2 (
          partition p2007 values less than (2008),
          partition p2008 values less than (2009)
        );
      select length(table_name) from mysql.innodb_table_stats;
      

      The last statement returns 79, even though the column is defined as varchar(64).

      This can potentially fail in the upper layer in many different ways. For example, it makes copying ALTER TABLE to fail with "duplicate key value".

      The same applies to innodb_index_stats too.

      Attachments

        Issue Links

          Activity

            The necessary length for table_name is actually 199 characters, because the name can include up to 3 components: table, partition, and subpartition name, each being 64 characters. With delimiters, the maximum length is 199 characters, or 597 bytes (actually a little less, because the delimiters are 1-byte characters).

            InnoDB uses its internal SQL parser for updating the persistent statistics, and it would ignore the declared maximum length. This would ultimately cause trouble in MySQL 5.7 (MariaDB 10.2), because InnoDB could hang due to mispredicting page splits (MDEV-14637).

            In MariaDB, we should eventually get rid of these problematic tables and make the InnoDB data files more self-contained by storing the statistics directly in the .ibd files (MDEV-15020).

            If a too old server is started with newer data files, it would refuse startup. Hopefully there will be no need to revise the InnoDB statistics table definitions after MDEV-14637.

            marko Marko Mäkelä added a comment - The necessary length for table_name is actually 199 characters, because the name can include up to 3 components: table, partition, and subpartition name, each being 64 characters. With delimiters, the maximum length is 199 characters, or 597 bytes (actually a little less, because the delimiters are 1-byte characters). InnoDB uses its internal SQL parser for updating the persistent statistics, and it would ignore the declared maximum length. This would ultimately cause trouble in MySQL 5.7 (MariaDB 10.2), because InnoDB could hang due to mispredicting page splits ( MDEV-14637 ). In MariaDB, we should eventually get rid of these problematic tables and make the InnoDB data files more self-contained by storing the statistics directly in the .ibd files ( MDEV-15020 ). If a too old server is started with newer data files, it would refuse startup. Hopefully there will be no need to revise the InnoDB statistics table definitions after MDEV-14637 .

            Fixed by the InnoDB 5.7.23 merge in MariaDB 10.2.18 and 10.3.9?

            hholzgra Hartmut Holzgraefe added a comment - Fixed by the InnoDB 5.7.23 merge in MariaDB 10.2.18 and 10.3.9?

            I was actually off by one, it was fixed in 10.2.17 via

            Merge Revision #976f920514 2018-08-03 08:37:05 +0300 - MDEV-16850 Merge new release of InnoDB 5.7.23 to 10.2

            hholzgra Hartmut Holzgraefe added a comment - I was actually off by one, it was fixed in 10.2.17 via Merge Revision #976f920514 2018-08-03 08:37:05 +0300 - MDEV-16850 Merge new release of InnoDB 5.7.23 to 10.2

            Also fixed in 10.3.9

            hholzgra Hartmut Holzgraefe added a comment - Also fixed in 10.3.9

            Still present in current 10.1.38 though ....

            hholzgra Hartmut Holzgraefe added a comment - Still present in current 10.1.38 though ....
            danblack Daniel Black added a comment -

            10.2 out of support now.

            danblack Daniel Black added a comment - 10.2 out of support now.

            This affects all MariaDB versions and has not been fixed yet.

            marko Marko Mäkelä added a comment - This affects all MariaDB versions and has not been fixed yet.

            People

              Unassigned Unassigned
              serg Sergei Golubchik
              Votes:
              1 Vote for this issue
              Watchers:
              5 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.