[MDEV-23743] setting SQL_MODE to ANSI hides important table details from SHOW CREATE TABLE Created: 2020-09-16  Updated: 2020-09-18

Status: Confirmed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.4.14
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Rick Pizzi Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 4
Labels: None

Issue Links:
Relates
relates to MDEV-22260 Add a comment about potentially missi... Closed

 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]>



 Comments   
Comment by Rick Pizzi [ 2020-09-17 ]

I do not agree that this is a duplicate of MDEV-22260.
This customer is not using ORACLE_MODE, just the ANSI option.

Comment by Sergei Golubchik [ 2020-09-17 ]

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.

Comment by Sergei Golubchik [ 2020-09-17 ]

Logically, in your particular case, ENGINE=InnoDB is not part of the ANSI standard and thus it is correctly omitted from the table definition.

Comment by Rick Pizzi [ 2020-09-17 ]

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.

Comment by Rick Pizzi [ 2020-09-17 ]

Just wonder, if ANSI supports comments, a better approach would be to show the missing informations that way,
rather than omitting them.

Comment by Sergei Golubchik [ 2020-09-18 ]

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.

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