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

EXCHANGE PARTITION with different ROW_FORMAT

    XMLWordPrintable

    Details

      Description

      I would like to raise FR for EXCHANGE PARTITION to work with different ROW_FORMAT. I found similar FR reported upstream here https://bugs.mysql.com/bug.php?id=95484

      Currently, EXCHANGE PARTITION with different row format fails with error "Non matching attribute 'ROW_FORMAT' between partition and table" as shown below:

      CREATE TABLE e (
      id INT NOT NULL,
      fname VARCHAR(30),
      lname VARCHAR(30)
      )
      PARTITION BY RANGE (id) (
      PARTITION p0 VALUES LESS THAN (50),
      PARTITION p1 VALUES LESS THAN (100),
      PARTITION p2 VALUES LESS THAN (150));
      Query OK, 0 rows affected (0.034 sec)
       
      SET GLOBAL innodb_default_row_format=compact;
      Query OK, 0 rows affected (0.000 sec)
       
      ALTER TABLE e ADD PARTITION (PARTITION p4 VALUES LESS THAN (200));
      Query OK, 0 rows affected (0.029 sec)
       
      SET GLOBAL innodb_default_row_format=dynamic;
      Query OK, 0 rows affected (0.000 sec)
       
      CREATE TABLE e2 LIKE e;
      Query OK, 0 rows affected (0.070 sec)
       
      SELECT TABLE_SCHEMA, TABLE_NAME, ROW_FORMAT FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test" AND TABLE_NAME LIKE 'e%';
      +--------------+------------+------------+
      | TABLE_SCHEMA | TABLE_NAME | ROW_FORMAT |
      +--------------+------------+------------+
      | test | e | Dynamic |
      | test | e2 | Dynamic |
      +--------------+------------+------------+
       
      SELECT NAME, ROW_FORMAT, SPACE_TYPE FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE "test/e%";
      +--------------+------------+------------+
      | NAME | ROW_FORMAT | SPACE_TYPE |
      +--------------+------------+------------+
      | test/e#P#p0 | Dynamic | Single |
      | test/e#P#p1 | Dynamic | Single |
      | test/e#P#p2 | Dynamic | Single |
      | test/e#P#p4 | Compact | Single |
      | test/e2#P#p0 | Dynamic | Single |
      | test/e2#P#p1 | Dynamic | Single |
      | test/e2#P#p2 | Dynamic | Single |
      | test/e2#P#p4 | Dynamic | Single |
      +--------------+------------+------------+
      

      You can notice here that partition "p4" is created with "compact" row format while all others partitions are with default row format "dynamic"

      ALTER TABLE e2 REMOVE PARTITIONING;
      Query OK, 0 rows affected (0.107 sec)
       
      INSERT INTO e VALUES
      (139, "Jim", "Smith"),
      (99, "Mary", "Jones"),
      (16, "Frank", "White"),
      (20, "Linda", "Black")
      (180, "irfan", "Brown");
      Query OK, 5 rows affected (0.005 sec)
       
      SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
      +----------------+------------+
      | PARTITION_NAME | TABLE_ROWS |
      +----------------+------------+
      | p0 | 2 |
      | p1 | 1 |
      | p2 | 1 |
      | p4 | 1 |
      +----------------+------------+
      4 rows in set (0.000 sec)
      

      So, each partition contains one row.

      ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
      Query OK, 0 rows affected (0.043 sec)
      

      Partition exchange works as p0 partition is created with same row format as table i.e. dynamic row format.

      ALTER TABLE e EXCHANGE PARTITION p4 WITH TABLE e2;
      ERROR 1731 (HY000): Non matching attribute 'ROW_FORMAT' between partition and table
      

      However, p4 partition exchange fails because of different row format as p4 partition is created with COMPACT row format while table e2 is created with DYNAMIC row format.

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            muhammad.irfan Muhammad Irfan
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.