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

SQL discovery adds DEFAULT/ON UPDATE CURRENT_TIMESTAMP to TIMESTAMP NOT NULL; CREATE TABLE does not

    XMLWordPrintable

Details

    Description

      TABLE_SHARE::init_from_sql_statement_string calls promote_first_timestamp_column unconditionally; CREATE TABLE only when explicit_defaults_for_timestamp is OFF. Suite sql_discovery:

      --source inc.inc
      		 
      SELECT @@explicit_defaults_for_timestamp;
      		 
      CREATE TABLE t1 (ts TIMESTAMP NOT NULL);
      		 
      SHOW CREATE TABLE t1;
      		 
      SET test_sql_discovery_statement='t2:CREATE TABLE t2 (ts TIMESTAMP NOT NULL)';
      		 
      SHOW CREATE TABLE t2;
      		 
      DROP TABLE t1, t2;

      Result (11.8.9):

      SELECT @@explicit_defaults_for_timestamp;
      		 
      @@explicit_defaults_for_timestamp
      		 
      1
      		 
      CREATE TABLE t1 (ts TIMESTAMP NOT NULL);
      		 
      SHOW CREATE TABLE t1;
      		 
      Table	Create Table
      		 
      t1	CREATE TABLE `t1` (
      		 
        `ts` timestamp NOT NULL
      		 
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
      		 
      SET test_sql_discovery_statement='t2:CREATE TABLE t2 (ts TIMESTAMP NOT NULL)';
      		 
      SHOW CREATE TABLE t2;
      		 
      Table	Create Table
      		 
      t2	CREATE TABLE `t2` (
      		 
        `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
      		 
      ) ENGINE=TEST_SQL_DISCOVERY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
      		 
      DROP TABLE t1, t2;

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-28632 Change default of explicit_defaults_for_timestamp to ON

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-29227 deprecate explicit_defaults_for_timestamp=0

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            People

              serg Sergei Golubchik
              nikitamalyavin Nikita Malyavin
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.