Details
-
Task
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
None
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
- relates to
-
MDEV-23743 setting SQL_MODE to ANSI hides important table details from SHOW CREATE TABLE
-
- Confirmed
-
Activity
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.
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.
okay, let's use this for the documentation and tackle the comment part in the linked MDEV-23743
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/
the same repeatable for sql_mode ANSI, DB2, POSTGRESQL,MSSQL,MAXDB on 5.5-10.5