Elena Stepanova added a comment - 2015-03-14 19:15 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)

Mikhail Gavrilov added a comment - 2015-03-16 09:41 [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

Elena Stepanova added a comment - 2015-03-16 12:17 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.

Alexander Barkov added a comment - 2015-04-30 11:19 - edited 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'