Not the answer you need?
Register and ask your own question!

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

HTF1HTF1 ContributorInactive User Role Beginner
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)

Comments

  • RoelVandePaarRoelVandePaar Contributor Inactive User Role Beginner
    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!
  • RoelVandePaarRoelVandePaar Contributor Inactive User Role Beginner
    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
  • RoelVandePaarRoelVandePaar Contributor Inactive User Role Beginner
  • HTF1HTF1 Contributor Inactive User Role Beginner
    Thanks. I'll watch this Jira.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.