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
-