[MDEV-7778] impossible create copy of table, if table contain default value for timestamp field Created: 2015-03-14  Updated: 2015-05-05  Resolved: 2015-05-05

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 5.5, 10.0, 10.1
Fix Version/s: 10.0.18

Type: Bug Priority: Major
Reporter: Mikhail Gavrilov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: upstream-fixed, verified
Environment:

Linux

Issue Links:
Duplicate
is duplicated by MDEV-7842 Create table from select will define ... Closed
Relates
relates to MDEV-8082 ON UPDATE is not preserved by CREATE ... Closed

 Description   

MariaDB has wonderful feature which allows easy create copy of any table.

create table `new_name` select * from `old_table`

But I found that this is not worked if `old_table` contain default value for timestamp field.

CREATE TABLE `table1` (
 
  `f1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 

0 row(s) affected
 

CREATE TABLE `table2` (
 
  `f2` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 

0 row(s) affected
 

create table `table1copy` select * from `table1` limit 0;
 

Error Code: 1067
 
Invalid default value for 'f1'
 

create table `table2copy` select * from `table2` limit 0;
 

0 row(s) affected
 

show create table table2copy
 

CREATE TABLE `table2copy` (
 
  `f2` timestamp NULL DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8



 Comments   
Comment by Elena Stepanova [ 2015-03-14 ]

Works for me (see below).
Could you please attach your cnf file(s)? We need to find what's different.

MariaDB [test]> CREATE TABLE `table1` (
 
    ->   `f1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
 
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
Query OK, 0 rows affected (0.19 sec)
 
 
 
MariaDB [test]> create table `table1copy` select * from `table1` limit 0;
 
Query OK, 0 rows affected (0.06 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> show create table table1copy;
 
+------------+----------------------------------------------------------------------------------------------------------------------------+
 
| Table      | Create Table                                                                                                               |
 
+------------+----------------------------------------------------------------------------------------------------------------------------+
 
| table1copy | CREATE TABLE `table1copy` (
 
  `f1` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+------------+----------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select @@version;
 
+-----------------+
 
| @@version       |
 
+-----------------+
 
| 10.0.17-MariaDB |
 
+-----------------+
 
1 row in set (0.00 sec)

Comment by Mikhail Gavrilov [ 2015-03-16 ] 

[root@localhost ~]# cat /etc/my.cnf
 
#
 
# This group is read both both by the client and the server
 
# use it for options that affect everything
 
#
 
[client-server]
 
 
 
#
 
# include all files from the config directory
 
#
 
!includedir /etc/my.cnf.d
 

[root@localhost ~]# ls /etc/my.cnf.d
 
my.cnf  my_log.disable  mysql-clients.cnf  server.cnf  tokudb.cnf
 

[root@localhost ~]# cat /etc/my.cnf.d/my.cnf 
[mysqld]
 
sql-mode="NO_BACKSLASH_ESCAPES,STRICT_ALL_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
 
innodb_strict_mode=on
 
innodb_file_format=Barracuda
 
innodb_large_prefix=on
 

[root@localhost ~]# cat /etc/my.cnf.d/mysql-clients.cnf 
#
 
# These groups are read by MariaDB command-line tools
 
# Use it for options that affect only one utility
 
#
 
 
 
[mysql]
 
 
 
[mysql_upgrade]
 
 
 
[mysqladmin]
 
 
 
[mysqlbinlog]
 
 
 
[mysqlcheck]
 
 
 
[mysqldump]
 
 
 
[mysqlimport]
 
 
 
[mysqlshow]
 
 
 
[mysqlslap]
 

[root@localhost ~]# cat /etc/my.cnf.d/server.cnf 
#
 
# These groups are read by MariaDB server.
 
# Use it for options that only the server (but not clients) should see
 
#
 
# See the examples of server my.cnf files in /usr/share/mysql/
 
#
 
 
 
# this is read by the standalone daemon and embedded servers
 
[server]
 
 
 
# this is only for the mysqld standalone daemon
 
[mysqld]
 
 
 
# this is only for embedded server
 
[embedded]
 
 
 
# This group is only read by MariaDB servers, not by MySQL.
 
# If you use the same .cnf file for MySQL and MariaDB,
 
# you can put MariaDB-only options here
 
[mariadb]
 
 
 
# This group is only read by MariaDB-10.0 servers.
 
# If you use the same .cnf file for MariaDB of different versions,
 
# use this group for options that older servers don't understand
 
[mariadb-10.0]
 

[root@localhost ~]# cat /etc/my.cnf.d/tokudb.cnf 
[mariadb]
 
# See https://mariadb.com/kb/en/how-to-enable-tokudb-in-mariadb/
 
# for instructions how to enable TokuDB
 
#
 
# See https://mariadb.com/kb/en/tokudb-differences/ for differences
 
# between TokuDB in MariaDB and TokuDB from http://www.tokutek.com/
 
 
 
#plugin-load-add=ha_tokudb.so

Comment by Elena Stepanova [ 2015-03-16 ]

Thank you. It's sql_mode="NO_ZERO_DATE".

set sql_mode="NO_ZERO_DATE";
 
 
 
CREATE TABLE `t1` (
 
  `f1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
 
);
 
create table `t1copy` select * from `t1` limit 0;
 
drop table t1, t1copy;
 

query 'create table `t1copy` select * from `t1` limit 0' failed: 1067: Invalid default value for 'f1'

Same result can be observed in MySQL 5.5, but in MySQL 5.6 it works.

There were at least 3 changes in MySQL related to this use case.

First, there was the bug https://bugs.mysql.com/bug.php?id=34280, which complained that 0 as a default didn't work for timestamps under NO_ZERO_DATE. It was fixed in 5.6.6.

After that, the table would be created, although with a warning: 

Warning	 1264 Out of range value for column 'f1' at row 1

That's because the second table's definition was different from the first one:

Initial table

 
Table	Create Table
 
t1	CREATE TABLE `t1` (
 
  `f1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Table copy

 
Table	Create Table
 
t1copy	CREATE TABLE `t1copy` (
 
  `f1` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1

It was also fixed some time between 5.6.10 and current version, and now in 5.6 the second table has the same definition as the initial one:

Table	Create Table
 
t1copy	CREATE TABLE `t1copy` (
 
  `f1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Finally, in current 5.6 NO_ZERO_DATE is deprecated.

None of these changes are present in 10.0 – NO_ZERO_DATE still gets set without a warning, the copy of the table is produced with zero as a default, and NO_ZERO_DATE forbids it.

Comment by Alexander Barkov [ 2015-04-30 ]

A smaller SQL script reproducing the problem:

SET sql_mode="NO_ZERO_DATE";
 
DROP TABLE IF EXISTS t1,t2;
 
CREATE TABLE t1 (
 
  ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE t2 AS SELECT * from t1 LIMIT 0;

It returns:

ERROR 1067 (42000): Invalid default value for 'ts'

Generated at Thu Feb 08 07:22:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.