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

ALTER TABLE is not enforcing prefix index size limit

    XMLWordPrintable

Details

    Description

      As noted in MDEV-17816, ALTER TABLE fails to enforce the column index prefix size limit in ALGORITHM=INPLACE.
      The operation can fail, but in a different way than ALGORITHM=COPY would. Here is an example of a failure:

      --source include/have_innodb.inc
       
      SET @save_format = @@GLOBAL.innodb_file_format;
      SET @save_prefix = @@GLOBAL.innodb_large_prefix;
      SET GLOBAL innodb_file_format=barracuda;
      SET GLOBAL innodb_large_prefix=ON;
      CREATE TABLE t1 (c VARCHAR(1024),
      c1 CHAR(255) NOT NULL,c2 CHAR(255) NOT NULL,c3 CHAR(255) NOT NULL,
      c4 CHAR(255) NOT NULL,c5 CHAR(255) NOT NULL,c6 CHAR(255) NOT NULL,
      c7 CHAR(255) NOT NULL,c8 CHAR(255) NOT NULL,c9 CHAR(255) NOT NULL,
      ca CHAR(255) NOT NULL,cb CHAR(255) NOT NULL,cc CHAR(255) NOT NULL,
      cd CHAR(255) NOT NULL,ce CHAR(255) NOT NULL,cf CHAR(255) NOT NULL,
      d0 CHAR(255) NOT NULL,d1 CHAR(255) NOT NULL,d2 CHAR(255) NOT NULL,
      d3 CHAR(255) NOT NULL,d4 CHAR(255) NOT NULL,d5 CHAR(255) NOT NULL,
      d6 CHAR(255) NOT NULL,d7 CHAR(255) NOT NULL,d8 CHAR(255) NOT NULL,
      d9 CHAR(255) NOT NULL,da CHAR(255) NOT NULL,db CHAR(255) NOT NULL,
      dc CHAR(255) NOT NULL,dd CHAR(255) NOT NULL,de CHAR(255) NOT NULL,
      UNIQUE KEY(c))
      ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
      INSERT INTO t1 VALUES
      (repeat('a',999),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','',''),
      (CONCAT(repeat('a',999),'b'),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
      --enable_info
      ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
      --disable_info
      SELECT COUNT(*) FROM t1;
      CHECK TABLE t1;
      DROP TABLE t1;
      SET GLOBAL innodb_file_format=@save_format;
      SET GLOBAL innodb_large_prefix=@save_prefix;
      

      10.0 14f6b0cdfd696ec0e4f24d914fc3123deaece2f6

      mysqltest: At line 24: query 'ALTER TABLE t1 ROW_FORMAT=REDUNDANT' failed: 1118: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
      

      The expected failure is as follows:

      mysqltest: At line 24: query 'ALTER TABLE t1 ROW_FORMAT=REDUNDANT' failed: 1709: Index column size too large. The maximum column size is 767 bytes.
      

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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