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

Add a comment about potentially missing table options on SHOW CREATE in ORACLE mode

    XMLWordPrintable

Details

    Description

      With sql_mode=ORACLE being active, SHOW CREATE TABLE does not show MariaDB specific table options like ENGINE, AUTO_INCREMENT, etc.

      This is fine as such options would not be understood when pasting such a CREATE statement back into an actual Oracle instance, but it is also easy to overlook.

      So I'd suggest to at least add an SQL comment that hints towards the output being potentially incomplete.

      Consider e.g. the following table:

      MariaDB [test]> show create table t1;
      +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                               |
      +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `msg` varchar(100) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> set sql_mode=ORACLE;
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [test]> show create table t1;
      +-------+--------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                           |
      +-------+--------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE "t1" (
        "id" int(11) NOT NULL,
        "msg" varchar(100) DEFAULT NULL,
        PRIMARY KEY ("id")
      ) |
      +-------+--------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
      

      With sql_mode=ORACLE this becomes:

      MariaDB [test]> set sql_mode=ORACLE;
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB [test]> show create table t1;
      +-------+--------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                           |
      +-------+--------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE "t1" (
        "id" int(11) NOT NULL,
        "msg" varchar(100) DEFAULT NULL,
        PRIMARY KEY ("id")
      ) |
      +-------+--------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
      

      I'd suggest adding at least a comment like:

      MariaDB [test]> show create table t1;
      +-------+--------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                           |
      +-------+--------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE "t1" (
        "id" int(11) NOT NULL,
        "msg" varchar(100) DEFAULT NULL,
        PRIMARY KEY ("id")
        -- some options may be missing due to sql_mode=ORACLE
      ) |
      +-------+--------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
      

      Attachments

        Issue Links

          Activity

            People

              greenman Ian Gilfillan
              hholzgra Hartmut Holzgraefe
              Votes:
              1 Vote for this issue
              Watchers:
              6 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.