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

EXCHANGE PARTITION allows different index direction, but causes further errors

Details

    Description

      EXCHANGE PARTITION allows a key to be ascending on one participating table and descending on another, but further DML on the table causes errors.

      --source include/have_partition.inc
      		 
       
      		 
      CREATE TABLE t1 (a INT, KEY(a DESC)) PARTITION BY KEY(a) PARTITIONS 4;
      		 
      INSERT INTO t1 VALUES (6),(3),(2),(1);
      		 
       
      		 
      CREATE TABLE t2 (a INT, KEY(a));
      		 
       
      		 
      ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2;
      		 
      INSERT INTO t1 VALUES (5),(4);
      		 
       
      		 
      # Cleanup
      		 
      DROP TABLE t1, t2;

      10.8 0c5d1342 with MyISAM

      		 
      mysqltest: At line 9: query 'INSERT INTO t1 VALUES (5),(4)' failed: ER_GET_ERRNO (1030): Got error 190 "Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You may have retry " from storage engine MyISAM

      With InnoDB INSERT doesn't fail, but errors are written in the log:

      with InnoDB

      		 
      ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2;
      		 
      INSERT INTO t1 VALUES (5),(4);
      		 
      DROP TABLE t1, t2;
      		 
      bug.part2                                [ fail ]  Found warnings/errors in server log file!
      		 
              Test ended at 2022-01-30 15:14:13
      		 
      line
      		 
      2022-01-30 15:14:13 4 [ERROR] Found index a whose column info does not match that of MariaDB.
      		 
      2022-01-30 15:14:13 4 [ERROR] InnoDB indexes are inconsistent with what defined in .frm for table ./test/t1#P#p0
      		 
      ^ Found warnings in /mnt8t/bld/10.8-debug-nightly/mysql-test/var/log/mysqld.1.err

      With Aria, no errors are returned, which is more suspicious than soothing.

      In MariaDB KB the description of EXCHANGE PARTITION is scarce, but MySQL manual says

      Table nt is not itself partitioned.
      Table nt is not a temporary table.
      The structures of tables pt and nt are otherwise identical.
      ...

      (there are 9 more points, but none of them changes the above)

      However MySQL as of 8.0.28 also allows such EXCHANGE, and InnoDB errors/warnings also end up in the error log.

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-13756 Implement descending index: KEY (a DESC, b ASC)

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            elenst Elena Stepanova added a comment -

            Here is a slightly longer test case based on the same idea which ends with a server crash:

            --source include/have_partition.inc
            		 
             
            		 
            CREATE TABLE t1 (a INT, b INT, KEY (b DESC)) PARTITION BY KEY (b) PARTITIONS 4;
            		 
            INSERT INTO t1 VALUES (8,6),(6,3),(9,2),(5,0);
            		 
             
            		 
            CREATE TABLE t2 (a INT, b INT, KEY (b));
            		 
             
            		 
            ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2;
            		 
            SELECT * FROM t1 PARTITION (p1,p2);
            		 
             
            		 
            --error ER_GET_ERRNO
            		 
            INSERT INTO t1 VALUES (3,5);
            		 
            DELETE FROM t1 PARTITION (p1,p2) WHERE a = 1 ORDER BY b, a LIMIT 1;
            		 
             
            		 
            # Cleanup
            		 
            DROP TABLE t1, t2;

            10.8 0c5d1342

            		 
            #3  <signal handler called>
            		 
            #4  ha_partition::init_record_priority_queue (this=0x7faddc0f92f0) at /data/src/10.8/sql/ha_partition.cc:5450
            		 
            #5  0x000055b7a031fec1 in ha_partition::index_init (this=0x7faddc0f92f0, inx=0, sorted=true) at /data/src/10.8/sql/ha_partition.cc:5587
            		 
            #6  0x000055b79fb2c6d6 in handler::ha_index_init (this=0x7faddc0f92f0, idx=0, sorted=true) at /data/src/10.8/sql/handler.h:3446
            		 
            #7  0x000055b79fb440b8 in init_read_record_idx (info=0x7fadedf83630, thd=0x7faddc000db8, table=0x7faddc06a708, print_error=true, idx=0, reverse=true) at /data/src/10.8/sql/records.cc:85
            		 
            #8  0x000055b79fc13d63 in mysql_delete (thd=0x7faddc000db8, table_list=0x7faddc0155c8, conds=0x7faddc015e50, order_list=0x7faddc005ca8, limit=1, options=0, result=0x0) at /data/src/10.8/sql/sql_delete.cc:694
            		 
            #9  0x000055b79fc7b865 in mysql_execute_command (thd=0x7faddc000db8, is_called_from_prepared_stmt=false) at /data/src/10.8/sql/sql_parse.cc:4804
            		 
            #10 0x000055b79fc861c1 in mysql_parse (thd=0x7faddc000db8, rawbuf=0x7faddc015410 "DELETE FROM t1 PARTITION (p1,p2) WHERE a = 1 ORDER BY b, a LIMIT 1", length=66, parser_state=0x7fadedf84500) at /data/src/10.8/sql/sql_parse.cc:8027
            		 
            #11 0x000055b79fc72822 in dispatch_command (command=COM_QUERY, thd=0x7faddc000db8, packet=0x7faddc00b999 "DELETE FROM t1 PARTITION (p1,p2) WHERE a = 1 ORDER BY b, a LIMIT 1", packet_length=66, blocking=true) at /data/src/10.8/sql/sql_parse.cc:1894
            		 
            #12 0x000055b79fc711dc in do_command (thd=0x7faddc000db8, blocking=true) at /data/src/10.8/sql/sql_parse.cc:1402
            		 
            #13 0x000055b79fe433c0 in do_handle_one_connection (connect=0x55b7a3cafb38, put_in_cache=true) at /data/src/10.8/sql/sql_connect.cc:1418
            		 
            #14 0x000055b79fe4305f in handle_one_connection (arg=0x55b7a3cb21b8) at /data/src/10.8/sql/sql_connect.cc:1312
            		 
            #15 0x000055b7a033718e in pfs_spawn_thread (arg=0x55b7a3caf6a8) at /data/src/10.8/storage/perfschema/pfs.cc:2201
            		 
            #16 0x00007fadf3a8bea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
            		 
            #17 0x00007fadf368adef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

            elenst Elena Stepanova added a comment - Here is a slightly longer test case based on the same idea which ends with a server crash: --source include/have_partition.inc   CREATE TABLE t1 (a INT , b INT , KEY (b DESC )) PARTITION BY KEY (b) PARTITIONS 4; INSERT INTO t1 VALUES (8,6),(6,3),(9,2),(5,0);   CREATE TABLE t2 (a INT , b INT , KEY (b));   ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2; SELECT * FROM t1 PARTITION (p1,p2);   --error ER_GET_ERRNO INSERT INTO t1 VALUES (3,5); DELETE FROM t1 PARTITION (p1,p2) WHERE a = 1 ORDER BY b, a LIMIT 1;   # Cleanup DROP TABLE t1, t2; 10.8 0c5d1342 #3 <signal handler called> #4 ha_partition::init_record_priority_queue (this=0x7faddc0f92f0) at /data/src/10.8/sql/ha_partition.cc:5450 #5 0x000055b7a031fec1 in ha_partition::index_init (this=0x7faddc0f92f0, inx=0, sorted=true) at /data/src/10.8/sql/ha_partition.cc:5587 #6 0x000055b79fb2c6d6 in handler::ha_index_init (this=0x7faddc0f92f0, idx=0, sorted=true) at /data/src/10.8/sql/handler.h:3446 #7 0x000055b79fb440b8 in init_read_record_idx (info=0x7fadedf83630, thd=0x7faddc000db8, table=0x7faddc06a708, print_error=true, idx=0, reverse=true) at /data/src/10.8/sql/records.cc:85 #8 0x000055b79fc13d63 in mysql_delete (thd=0x7faddc000db8, table_list=0x7faddc0155c8, conds=0x7faddc015e50, order_list=0x7faddc005ca8, limit=1, options=0, result=0x0) at /data/src/10.8/sql/sql_delete.cc:694 #9 0x000055b79fc7b865 in mysql_execute_command (thd=0x7faddc000db8, is_called_from_prepared_stmt=false) at /data/src/10.8/sql/sql_parse.cc:4804 #10 0x000055b79fc861c1 in mysql_parse (thd=0x7faddc000db8, rawbuf=0x7faddc015410 "DELETE FROM t1 PARTITION (p1,p2) WHERE a = 1 ORDER BY b, a LIMIT 1", length=66, parser_state=0x7fadedf84500) at /data/src/10.8/sql/sql_parse.cc:8027 #11 0x000055b79fc72822 in dispatch_command (command=COM_QUERY, thd=0x7faddc000db8, packet=0x7faddc00b999 "DELETE FROM t1 PARTITION (p1,p2) WHERE a = 1 ORDER BY b, a LIMIT 1", packet_length=66, blocking=true) at /data/src/10.8/sql/sql_parse.cc:1894 #12 0x000055b79fc711dc in do_command (thd=0x7faddc000db8, blocking=true) at /data/src/10.8/sql/sql_parse.cc:1402 #13 0x000055b79fe433c0 in do_handle_one_connection (connect=0x55b7a3cafb38, put_in_cache=true) at /data/src/10.8/sql/sql_connect.cc:1418 #14 0x000055b79fe4305f in handle_one_connection (arg=0x55b7a3cb21b8) at /data/src/10.8/sql/sql_connect.cc:1312 #15 0x000055b7a033718e in pfs_spawn_thread (arg=0x55b7a3caf6a8) at /data/src/10.8/storage/perfschema/pfs.cc:2201 #16 0x00007fadf3a8bea7 in start_thread (arg=<optimized out>) at pthread_create.c:477 #17 0x00007fadf368adef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

            People

              serg Sergei Golubchik
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.