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
-