[MDEV-22260] Add a comment about potentially missing table options on SHOW CREATE in ORACLE mode Created: 2020-04-16  Updated: 2020-10-15  Resolved: 2020-10-15

Status: Closed
Project: MariaDB Server
Component/s: Documentation
Fix Version/s: N/A

Type: Task Priority: Minor
Reporter: Hartmut Holzgraefe Assignee: Ian Gilfillan
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-23743 setting SQL_MODE to ANSI hides import... Confirmed

 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)



 Comments   
Comment by Alice Sherepa [ 2020-09-17 ]

the same repeatable for sql_mode ANSI, DB2, POSTGRESQL,MSSQL,MAXDB on 5.5-10.5

Comment by Sergei Golubchik [ 2020-09-17 ]

This was the case since at least 2006. In any "non-mysql" (or, say, "foreign" or "emulation") sql mode SHOW CREATE TABLE will omit MariaDB/MySQL specific table attributes.

Let's make sure it's documented properly.

Comment by Hartmut Holzgraefe [ 2020-09-17 ]

We were not advertising these other modes as prominently as the new ORACLE mode though, and these older modes were really for mysqldump only, while now ORACLE is a mode that will be used on normal operations, and not just for mysqldump.

So it is more likely to fool someone running SHOW CREATE interactively now, especially when not remembering that you have set sql_mode=ORACLE for the current session.

So I still think adding a comment (or maybe a warning instead?) makes sense here, even if the documentation becomes clearer about this.

Comment by Sergei Golubchik [ 2020-09-18 ]

okay, let's use this for the documentation and tackle the comment part in the linked MDEV-23743

Comment by Ian Gilfillan [ 2020-10-15 ]

Documented on https://mariadb.com/kb/en/sql_modeoracle-from-mariadb-103/, https://mariadb.com/kb/en/sql-mode/ and https://mariadb.com/kb/en/show-create-table/

Generated at Thu Feb 08 09:13:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.