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

Create table from select will define timestamp columns with invalid default value of '0000-00-00 00:00:00'

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.0.15, 10.0.17
    • N/A
    • Temporal Types
    • None
    • Windows 7

    Description

      [NOTE] This might be related to issue MDEV-7843

      Creating a new table by selecting from an existing table will cause any new column that are of the type timestamp to have invalid default value of '0000-00-00 00:00:00'

      This does not happen when using "create table like".

      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 6
      Server version: 10.0.17-MariaDB mariadb.org binary distribution
       
      Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> use test
      Database changed
      MariaDB [test]> create table foo (col1 timestamp)\G
      Query OK, 0 rows affected (0.02 sec)
       
      MariaDB [test]> show create table foo\G
      *************************** 1. row ***************************
             Table: foo
      Create Table: CREATE TABLE `foo` (
        `col1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAM
      P
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.00 sec)
       
      MariaDB [test]> create table bar select * from foo\G
      Query OK, 0 rows affected (0.04 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> show create table bar\G
      *************************** 1. row ***************************
             Table: bar
      Create Table: CREATE TABLE `bar` (
        `col1` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.00 sec)
       
      MariaDB [test]> create table spam like foo\G
      Query OK, 0 rows affected (0.02 sec)
       
      MariaDB [test]> show create table spam\G
      *************************** 1. row ***************************
             Table: spam
      Create Table: CREATE TABLE `spam` (
        `col1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAM
      P
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.00 sec)

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              Tuco Tuco
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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