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

ALTER and CREATE with default partitioning differently react to SQL_MODE => unusable SHOW CREATE

Details

    Description

      With default SQL_MODE (which includes STRICT_TRANS_TABLES in all affected versions):

      --source include/have_partition.inc
      		 
       
      		 
      create table t (a int) with system versioning;
      		 
      alter table t partition by system_time partitions 3;
      		 
       
      		 
      show create table t;
      		 
      --let $show= query_get_value(show create table t, 'Create Table', 1)
      		 
       
      		 
      drop table t;
      		 
      --eval $show
      		 
       
      		 
      # Cleanup
      		 
      drop table if exists t;

      10.5

      		 
      MariaDB [test]> create table t (a int) with system versioning;
      		 
      Query OK, 0 rows affected (0.033 sec)
      		 
       
      		 
      MariaDB [test]> alter table t partition by system_time partitions 3;
      		 
      Query OK, 0 rows affected, 1 warning (0.084 sec)   
      Records: 0  Duplicates: 0  Warnings: 1
      		 
       
      		 
      MariaDB [test]> show warnings;
      		 
      +---------+------+----------------------------------------------------------------------------------+
      		 
      | Level   | Code | Message                                                                          |
      		 
      +---------+------+----------------------------------------------------------------------------------+
      		 
      | Warning | 4115 | Maybe missing parameters: no rotation condition for multiple HISTORY partitions. |
      		 
      +---------+------+----------------------------------------------------------------------------------+
      		 
      1 row in set (0.000 sec)

      So, ALTER produces a warning, but still partitions the table:

      MariaDB [test]> show create table t;
      		 
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | Table | Create Table                                                                                                                                        |
      		 
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | t     | CREATE TABLE `t` (
      		 
        `a` int(11) DEFAULT NULL
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
      		 
       PARTITION BY SYSTEM_TIME 
      PARTITIONS 3 |
      		 
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      1 row in set (0.023 sec)

      Attempt to re-run this CREATE TABLE however leads to an error:

      MariaDB [test]> drop table t;
      		 
      Query OK, 0 rows affected (0.040 sec)
      		 
       
      		 
      MariaDB [test]> CREATE TABLE `t` (
      		 
          ->   `a` int(11) DEFAULT NULL
      		 
          -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
      		 
          ->  PARTITION BY SYSTEM_TIME 
          -> PARTITIONS 3;
      		 
      ERROR 4115 (HY000): Maybe missing parameters: no rotation condition for multiple HISTORY partitions.

      Unsetting strict SQL mode makes it a warning again (notoriously, STRICT_TRANS_TABLES affects it, regardless whether it's InnoDB or MyISAM).

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31822 ALTER TABLE ENGINE=x started failing instead of producing warning on unsupported TRANSACTIONAL=1

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

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            Description With default {{SQL_MODE}} (which includes {{STRICT_TRANS_TABLES}} in all affected versions):

            {code:sql}
            --source include/have_partition.inc

            create table t (a int) with system versioning;
            alter table t partition by system_time partitions 3;

            show create table t;
            --let $show= query_get_value(show create table t, 'Create Table', 1)

            drop table t;
            --eval $show

            # Cleanup
            drop table t;
            {code}

            {code:sql|title=10.5 }
            MariaDB [test]> create table t (a int) with system versioning;
            Query OK, 0 rows affected (0.033 sec)

            MariaDB [test]> alter table t partition by system_time partitions 3;
            Query OK, 0 rows affected, 1 warning (0.084 sec)
            Records: 0 Duplicates: 0 Warnings: 1

            MariaDB [test]> show warnings;
            +---------+------+----------------------------------------------------------------------------------+
            | Level | Code | Message |
            +---------+------+----------------------------------------------------------------------------------+
            | Warning | 4115 | Maybe missing parameters: no rotation condition for multiple HISTORY partitions. |
            +---------+------+----------------------------------------------------------------------------------+
            1 row in set (0.000 sec)
            {code}

            So, ALTER produces a warning, but still partitions the table:

            {code:sql}
            MariaDB [test]> show create table t;
            +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
            | t | CREATE TABLE `t` (
              `a` int(11) DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
             PARTITION BY SYSTEM_TIME
            PARTITIONS 3 |
            +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
            1 row in set (0.023 sec)
            {code}

            Attempt to re-run this {{CREATE TABLE}} however leads to an error:
            {code:sql}
            MariaDB [test]> drop table t;
            Query OK, 0 rows affected (0.040 sec)

            MariaDB [test]> CREATE TABLE `t` (
                -> `a` int(11) DEFAULT NULL
                -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
                -> PARTITION BY SYSTEM_TIME
                -> PARTITIONS 3;
            ERROR 4115 (HY000): Maybe missing parameters: no rotation condition for multiple HISTORY partitions.
            {code}

            Unsetting strict SQL mode makes it a warning again (notoriously, {{STRICT_TRANS_TABLES}} affects it, regardless whether it's InnoDB or MyISAM).             		With default {{SQL_MODE}} (which includes {{STRICT_TRANS_TABLES}} in all affected versions):

            {code:sql}
            --source include/have_partition.inc

            create table t (a int) with system versioning;
            alter table t partition by system_time partitions 3;

            show create table t;
            --let $show= query_get_value(show create table t, 'Create Table', 1)

            drop table t;
            --eval $show

            # Cleanup
            drop table if exists t;
            {code}

            {code:sql|title=10.5 }
            MariaDB [test]> create table t (a int) with system versioning;
            Query OK, 0 rows affected (0.033 sec)

            MariaDB [test]> alter table t partition by system_time partitions 3;
            Query OK, 0 rows affected, 1 warning (0.084 sec)
            Records: 0 Duplicates: 0 Warnings: 1

            MariaDB [test]> show warnings;
            +---------+------+----------------------------------------------------------------------------------+
            | Level | Code | Message |
            +---------+------+----------------------------------------------------------------------------------+
            | Warning | 4115 | Maybe missing parameters: no rotation condition for multiple HISTORY partitions. |
            +---------+------+----------------------------------------------------------------------------------+
            1 row in set (0.000 sec)
            {code}

            So, ALTER produces a warning, but still partitions the table:

            {code:sql}
            MariaDB [test]> show create table t;
            +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
            | t | CREATE TABLE `t` (
              `a` int(11) DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
             PARTITION BY SYSTEM_TIME
            PARTITIONS 3 |
            +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
            1 row in set (0.023 sec)
            {code}

            Attempt to re-run this {{CREATE TABLE}} however leads to an error:
            {code:sql}
            MariaDB [test]> drop table t;
            Query OK, 0 rows affected (0.040 sec)

            MariaDB [test]> CREATE TABLE `t` (
                -> `a` int(11) DEFAULT NULL
                -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
                -> PARTITION BY SYSTEM_TIME
                -> PARTITIONS 3;
            ERROR 4115 (HY000): Maybe missing parameters: no rotation condition for multiple HISTORY partitions.
            {code}

            Unsetting strict SQL mode makes it a warning again (notoriously, {{STRICT_TRANS_TABLES}} affects it, regardless whether it's InnoDB or MyISAM).
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.7 [ 24805 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.8 [ 26121 ]
            midenok Aleksey Midenkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            midenok Aleksey Midenkov added a comment -

            Please review bb-10.5-midenok

            midenok Aleksey Midenkov added a comment - Please review bb-10.5-midenok
            midenok Aleksey Midenkov made changes -
            Assignee Aleksey Midenkov [ midenok ] Nikita Malyavin [ nikitamalyavin ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            midenok Aleksey Midenkov made changes -
            Component/s Data Definition - Alter Table [ 10114 ]
            Component/s Partitioning [ 10802 ]
            Component/s Versioned Tables [ 14303 ]
            nikitamalyavin Nikita Malyavin made changes -
            Assignee Nikita Malyavin [ nikitamalyavin ] Aleksey Midenkov [ midenok ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            midenok Aleksey Midenkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            nikitamalyavin Nikita Malyavin added a comment -

            ok to push

            nikitamalyavin Nikita Malyavin added a comment - ok to push
            midenok Aleksey Midenkov made changes -
            Fix Version/s 10.5.22 [ 29011 ]
            Fix Version/s 10.6.15 [ 29013 ]
            Fix Version/s 10.9.8 [ 29015 ]
            Fix Version/s 10.10.6 [ 29017 ]
            Fix Version/s 10.11.5 [ 29019 ]
            Fix Version/s 11.0.3 [ 28920 ]
            Fix Version/s 11.1.2 [ 28921 ]
            Fix Version/s 11.2.1 [ 29034 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.9 [ 26905 ]
            Fix Version/s 10.10 [ 27530 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -

            People

              midenok Aleksey Midenkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.