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

ALTER TABLE…DROP PRIMARY KEY, ADD PRIMARY KEY fails when VIRTUAL columns exist

    XMLWordPrintable

Details

    Description

      I got a suspicion that there might be something wrong with DROP PRIMARY KEY, ADD PRIMARY KEY with ROW_FORMAT=REDUNDANT tables.
      The reason for this is the call in row_log_table_low_redundant():

      		old_pk_size = rec_get_converted_size_temp(
      			new_index, old_pk->fields, old_pk->n_fields,
      			ventry, &old_pk_extra_size);
      

      The logic is duplicated differently in row_log_table_low(), where ventry is replaced with NULL:

      		old_pk_size = rec_get_converted_size_temp(
      			new_index, old_pk->fields, old_pk->n_fields,
      			NULL, &old_pk_extra_size);
      

      This turns out to be a bigger issue. I can repeat corruption and a crash with a small modification to an existing test:

      diff --git a/mysql-test/suite/innodb/t/innodb-table-online.test b/mysql-test/suite/innodb/t/innodb-table-online.test
      index a1977fa06a4..3ed206d0f44 100644
      --- a/mysql-test/suite/innodb/t/innodb-table-online.test
      +++ b/mysql-test/suite/innodb/t/innodb-table-online.test
      @@ -241,6 +241,12 @@ reap;
       # when the above error was noticed.
       eval $innodb_metrics_select;
       
      +SET @saved_max = @@GLOBAL.innodb_online_alter_log_max_size;
      +SET GLOBAL innodb_online_alter_log_max_size = 131072;
      +ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
      +ALTER TABLE t1 ADD COLUMN v1 INT AS (c1) VIRTUAL, LOCK=NONE;
      +#ALTER TABLE t1 ADD INDEX(v1), LOCK=NONE;
      +
       # Accumulate and apply some modification log.
       SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt3 WAIT_FOR dml3_done';
       --error ER_MULTIPLE_PRI_KEY
      @@ -258,7 +264,7 @@ SET DEBUG_SYNC = 'now WAIT_FOR rebuilt3';
       # Generate some log (delete-mark, delete-unmark, insert etc.)
       eval $innodb_metrics_select;
       BEGIN;
      -INSERT INTO t1 SELECT 320 + c1, c2, c3 FROM t1 WHERE c1 > 240;
      +INSERT INTO t1 (c1,c2,c3) SELECT 320 + c1, c2, c3 FROM t1 WHERE c1 > 240;
       DELETE FROM t1 WHERE c1 > 320;
       ROLLBACK;
       BEGIN;
      @@ -273,8 +279,11 @@ SET DEBUG_SYNC = 'now SIGNAL dml3_done';
       connection con1;
       reap;
       eval $innodb_metrics_select;
      +SET GLOBAL innodb_online_alter_log_max_size = @saved_max;
      +
       SELECT COUNT(c22f) FROM t1;
       CHECK TABLE t1;
      +ALTER TABLE t1 DROP COLUMN v1;
       
       # Create a column prefix index.
       --error ER_DUP_ENTRY
      

      I did request this kind of tests when I reviewed WL#8149 while I was employed at Oracle.

      In the above patch, there are a couple of ‘parameters’:

      1. the ROW_FORMAT of the table (ROW_FORMAT=REDUNDANT is logged differently)
      2. whether an index exists on the virtual column

      Actually, virtual columns should not matter at all for the logging of table-rebuilding ALTER TABLE operations. No matter if the columns are indexed, all that we need the values of the PRIMARY KEY and the base columns.

      Creating secondary indexes is a different matter. There currently exist unnecessary limitations around that (for example, adding a virtual column and adding an index is not supported in a single ALTER TABLE statement).

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.