Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27144

TIMESTAMP default value 1970-01-01 fails in GMT time zone

    XMLWordPrintable

Details

    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

      Attachments

        Activity

          People

            Unassigned Unassigned
            ivor richard
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.