Timestamp "invalid default value" - doesn't work on Percona server only

MySQL & MariaDB Percona Server for MySQL 5.7
1

Is there any other option that I’m missing? Both systems are on GMT time-zone.

Percona Server 5.7.20-19:

mysql> SELECT @@version, @@version_comment;
+---------------+--------------------------------------------------------+
| @@version | @@version_comment |
+---------------+--------------------------------------------------------+
| 5.7.20-19-log | Percona Server (GPL), Release 19, Revision 3c5d3e5d53c |
+---------------+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tbl1 (col1 TIMESTAMP DEFAULT '1970-01-01 00:00:01');
[COLOR=#FF0000][B]ERROR 1067 (42000): Invalid default value for 'col1'[/B]

MySQL 5.7.21:

mysql> SELECT @@version, @@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment |
+-----------+------------------------------+
| 5.7.21 | MySQL Community Server (GPL) |
+-----------+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tbl1 (col1 TIMESTAMP DEFAULT '1970-01-01 00:00:01');
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tbl1\G
*************************** 1. row ***************************
Table: tbl1
Create Table: CREATE TABLE `tbl1` (
`col1` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
2

Always best to test against same versions.

MySQL 5.7.20:

5.7.20>SELECT @@version, @@version_comment;
+--------------+------------------------------+
| @@version | @@version_comment |
+--------------+------------------------------+
| 5.7.20-debug | MySQL Community Server (GPL) |
+--------------+------------------------------+
1 row in set (0.00 sec)

5.7.20>SELECT @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5.7.20>SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.00 sec)

5.7.20>CREATE TABLE tbl1 (col1 TIMESTAMP DEFAULT '1970-01-01 00:00:01');
ERROR 1067 (42000): Invalid default value for 'col1'

So it’s likely a 5.7.21 fix. Percona Server 5.7.21 (containing upstream MySQL 5.7.21 bug fixes) is not too far off.

Thank you!

3

Hmm. An internal version of Percona Server 5.7.21 also gives the same

5.7.21>CREATE TABLE tbl1 (col1 TIMESTAMP DEFAULT '1970-01-01 00:00:01');
ERROR 1067 (42000): Invalid default value for 'col1'

I will flag this to the Percona Server lead

4

Logged as [url][PS-3808] Difference in TIMESTAMP DEFAULT query failure between MS and PS 5.7.21 - Percona JIRA

5

Thanks. I’ll watch this Jira.