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'

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.0.15, 10.0.17
    • Fix Version/s: N/A
    • Component/s: Temporal Types
    • Labels:
      None
    • Environment:
      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

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

                Dates

                • Created:
                  Updated:
                  Resolved: