[MDEV-27144] TIMESTAMP default value 1970-01-01 fails in GMT time zone Created: 2021-11-30  Updated: 2022-03-29  Resolved: 2022-03-29

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table, Time zones
Affects Version/s: 10.5.13
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: richard Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: None
Environment:

OS: Debian 11
OS time zone: GMT



 Description   

create table statement for a field having TIMESTAMP datatype, specifying a DEFAULT value of '1970-01-01 00:00:01' works okay when the timezone on the computer is EST.

when shipping the software to a computer where the timezone is set to GMT, this silently fails, connection is dropped (to client requesting the table be created), table fails to exist.

even `error.log` does not indicate what the problem is.

only discovered the problem through trial-and-error in the mysql cli directly:
1. when changing the default time to one hour ahead, 1AM, create table statement works okay
2. ultimate solution i chose was to convert the TIMESTAMP columns to DATETIME columns since the lower bound restrictions are not as strict, and using `1970-01-01 00:00:01` works fine in all time zones

but i cannot tell you how much time this required to find, and has been a thorn in my side for more than a year with a client trying to evaluate my software. not a good look.

all's well that ends well for me, but for anyone else who doesn't have the fortitude or patience to see this through could actually lose a client, at least those living in GMT, good thing it's just a bunch of small islands.

thanks,
richard



 Comments   
Comment by Sergei Golubchik [ 2021-12-17 ]

Could you show a complete test case? I wasn't able to repeat it:

MariaDB [test]> set time_zone='GMT';
Query OK, 0 rows affected (0.000 sec)
 
MariaDB [test]> create table t1 (a timestamp default '1970-01-01 00:00:01');
Query OK, 0 rows affected (0.005 sec)

Comment by richard [ 2021-12-17 ]

the use case i described had the time zone set at the OS level, not the DB
level.

and i will follow up with my complete table, plus the output of the failure
when i have time, which won't be until the new year.

thanks,
richard

On Fri, Dec 17, 2021 at 12:29 PM Sergei Golubchik (Jira) <jira@mariadb.org>

Comment by Sergei Golubchik [ 2022-02-21 ]

With GMT it still works for me, on OS level or not. With CET (GMT+1) it doesn't.

May be the timezone for some reason wasn't GMT on that particular box, but GMT+1?

Generated at Thu Feb 08 09:50:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.