Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.1, 10.0, 5.5
-
Fix Version/s: 10.0.18
-
Component/s: Data Definition - Alter Table
-
Labels:
-
Environment:Linux
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
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
[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
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.
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'
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)