Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23743

setting SQL_MODE to ANSI hides important table details from SHOW CREATE TABLE

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.4.14
    • 10.4, 10.5
    • Server
    • 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

          Activity

            People

              rucha174 Rucha Deodhar
              rpizzi Rick Pizzi
              Votes:
              4 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.