Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.14
-
None
Description
When the SQL_MODE contains the ANSI keyword, show create table no longer displays important table details, like engine and charset.
This causes confusion, and tools that rely on show create table like mydumper fail to work properly. For example, when dumping a table using the well known and widely used tool mydumper, it saves the table definition in the dump using show create table, and when that dump is later reloaded the table is created with server default engine and charset, which is incorrect (especially when you are trying to build a replica of another server).
MariaDB [test]> set session SQL_MODE='';
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [test]> show create table rick\G
|
*************************** 1. row ***************************
|
Table: rick
|
Create Table: CREATE TABLE `rick` (
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
`diffcharset` char(200) CHARACTER SET utf8 DEFAULT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> set session SQL_MODE='ANSI';
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [test]> show create table rick\G
|
*************************** 1. row ***************************
|
Table: rick
|
Create Table: CREATE TABLE "rick" (
|
"id" int(11) NOT NULL AUTO_INCREMENT,
|
"diffcharset" char(200) CHARACTER SET utf8 DEFAULT NULL,
|
PRIMARY KEY ("id")
|
)
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]>
|
|
Attachments
Issue Links
- relates to
-
MDEV-22260 Add a comment about potentially missing table options on SHOW CREATE in ORACLE mode
-
- Closed
-
Activity
This was the case since at least MySQL as of 2006. In any "non-mysql" (or, say, "foreign" or "emulation") sql mode SHOW CREATE TABLE will omit MariaDB/MySQL specific table attributes.
These "foreign" sql modes are ANSI, DB2, POSTGRESQL, MSSQL, MAXDB, and ORACLE.
Logically, in your particular case, ENGINE=InnoDB is not part of the ANSI standard and thus it is correctly omitted from the table definition.
Sorry serg but the fact this was this way in 2006 doesn't necessarily mean it's correct today.
Also, I am not worried about the Engine, but more about the charset, which actually caused my team to spend 2 days trying to understand what was going on with the just built replica.
Just wonder, if ANSI supports comments, a better approach would be to show the missing informations that way,
rather than omitting them.
the fact this was this way in 2006 doesn't necessarily mean it's correct today
I only wanted to clarify the "no longer displays" part of the original bug report. It created an impression that this is a recent change and I pointed out that it's not.
Yes, the standard support both /* bracketed */ and -- end of line comments. We can put MariaDB-specific clauses in the comment, all right.
I do not agree that this is a duplicate of
MDEV-22260.This customer is not using ORACLE_MODE, just the ANSI option.