[MDEV-7842] Create table from select will define timestamp columns with invalid default value of '0000-00-00 00:00:00' - JIRA

Details

Type: Bug
Status: Closed
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)

Gliffy Diagrams

Attachments

Issue Links

duplicates

MDEV-7778 impossible create copy of table, if table contain default value for timestamp field

Closed

Activity

Hide

Permalink

Elena Stepanova added a comment - 2015-03-26 00:05

Thanks for the report.
It's not really an invalid default value, it's an ordinary zero value which you can get by simple CREATE TABLE as well:

MariaDB [test]> create table ttt (t1 timestamp, t2 timestamp);
Query OK, 0 rows affected (0.57 sec)

MariaDB [test]> show create table ttt;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                         |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ttt   | CREATE TABLE `ttt` (
  `t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `t2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Still, there is a bug, see ~~MDEV-7778~~ which this issue is a duplicate of.

Show

Elena Stepanova added a comment - 2015-03-26 00:05 Thanks for the report. It's not really an invalid default value, it's an ordinary zero value which you can get by simple CREATE TABLE as well: MariaDB [test]> create table ttt (t1 timestamp, t2 timestamp); Query OK, 0 rows affected (0.57 sec) MariaDB [test]> show create table ttt; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ttt | CREATE TABLE `ttt` ( `t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `t2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) Still, there is a bug, see MDEV-7778 which this issue is a duplicate of.

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

Details

Description

Gliffy Diagrams

Attachments

Issue Links

Activity

People

Dates

Agile