Your periodic reminder that we are closer to the Y2038 bug than the Y2K bug:

SELECT UNIX_TIMESTAMP("2038-01-18 19:14:07") → 2147483647;
SELECT UNIX_TIMESTAMP("2038-01-18 19:14:08") → NULL;
SELECT VERSION() → 10.4.22-MariaDB-log

Previously, previously, previously, previously, previously.

Tags: , , , ,

14 Responses:

  1. jwz says:

    And yes, this actually made something break and I had to code around it. Yesterday. In this, the far future year of two thousand twenty two.

    • db48x says:

      You should tell the story in more detail. What broke, and how did you work around it? Why did upgrading the database server not fix the problem?

      • jwz says:

        This is the latest MariaDB available on my system. This is not some ancient bug, this is current events.

        The "datetime" type use 64 bit storage, but UNIX_TIMESTAMP is garbage, so if you are trying to get a time_t back out of the DB, you have to query it as a string and then have the caller parse that string, which is both wasteful and asinine.

    • pakraticus says:

      I have dim memories of AIX 4.3 resolving this in 1999 with something that broke in 2075...
      That and memories of seeing '19100' on the login banners...

      • Big says:

        I too, had poorly written Perl code cut-n-pasted into server side includes in my copyright footers on the big website I ran back then…

        (I am fortunate to be able to say that was the only thing of mine that didn’t sail through 1999 NYE working perfectly.)

  2. MrSpookTower says:

    Yet more proof that if God had intended for us to use DBMSes, He would have never invented command line tools:

    $ date -u -d@2147454848 +%FT%T
    $ uname -a
    Linux victim 4.9.0-14-686-pae #1 SMP Debian 4.9.240-2 (2020-10-30) i686 GNU/Linux

  3. グレェ「grey」 says:

    Ooph. Yeah, we’ll see how it goes. Writing as someone who was working in a NOC for Y2K, it was mostly a non event. However, that employer’s mission critical systems were almost all running on Solaris.

    I doubt Windows users will be exempt either, especially given how WSL has gained traction thus also inviting the entire Linux attack surface to Microsoft’s OSes now.

    As of OpenBSD 5.5, released in 2014, they claim to have replaced all 32bit instances of UNIX epoch time_t with 64bit ones in their base OS (further reading here: https://www.openbsd.org/55.html). While I realize that not everyone runs OpenBSD, hopefully their changes will be useful as reference for other implementors? I seem to recall related bugs discovered in ports also getting fixed and upstreamed on occasion.

    After your Let’s Encrypt posts last year, I took it upon myself to update MacPorts’ LibreSSL port. It took that project a few months to merge my PR. Meanwhile, Apple is still shipping a version of LibreSSL which is 4 years out of date with known vulnerabilities (more than just the Let’s Encrypt related stuff too). So, using that as a reference, I expect commercial UNIX vendors such as Apple, IBM, etc. will continue to be the slowest to catch up. They’ve got years to close the gap at least?

    I’m guessing the sorts of snakeoil scheisters who made oodles of unmerited funds during Y2K will find ways to milk a new cash cow? Though ISA Y2038 clock cards probably won’t be of much good, maybe someone will hawk similarly useless USB dongles? People such as I probably won’t see a dime (though I was earning $17/hour at that NOC job circa 1999/2000 which is more than I have earned in more recent years at many employers, ah the lucrative pre dot-bomb era!).

    In realms of real nerding out, I seem to recall that most Amiga related time problems don’t occur until late in the 22nd century? Though I sure as heck doubt I will be alive to witness them. However, given that the Amiga user(fan?)base continues to release new versions of Workbench, decades after Commodore declared bankruptcy, I am guessing that whatever Amiga users may still be alive for their 22nd century time bugs, will have developed workarounds and patches by then too. ;)

    • k3ninho says:

      I think that most Linux is running on AMD64, ARM64 hosts with 64-bit values throughout, though 32-bit systems remain supported and devices are anticipated to be in existence, using in a fallback for those architectures still running in 16 years time. GNU CLib has support, the work was mostly done by 2019. See https://lwn.net/Kernel/Index/#Year_2038_problem for details of how the work progressed.

      I wonder if Jamie can get hold of the guy that wrote [1], or maybe someone knows Terry Lambert, to answer what the kernel and library side of macOS's preparation is like.

      1: Quora -- How I got Mac OS X certified as UNIX-compliant


  4. cmt says:

    Also, Y2038 was 10000 days away in September 2010, guess how I found out back then.
    Luckily, all of the newer 64bit systems have upgraded their time_t. And that other RDBMS does handle dates and unix seconds beyond 2038 just well. Yes, I know that's just adding insult to injury.

  5. Eric TF Bat says:

    Thank you! I was curious if my own system had this flaw, so I attempted to log in using a MySQL tool, only to discover that none of my computers could remember the password. I got in eventually (hand-wave, hand-wave, no names no pack drill) and changed to a nice secure password that I will remember next time (hint: what you do is replace the p with a capital P and the a and o with the at sign and zero, so nobody will ever guess it!) Then I did the test, and it reassured me that I had been sufficiently careful in choosing a database version so I would be able to continue partying beyond four days after my 69th birthday (yes, really).

    • messju says:

      Are you sure yout MySQL is working properly beyond y38k?
      Maybe you are in a different time zone that jwz, then you'll have to add a few hours to "2038-01-18 19:14:08" to test it.

      • Eric TF Bat says:

        Good question! Excellent application of the Fourth Programmer Virtue, Paranoia. Checking...

        UNIX_TIMESTAMP("2038-01-18 19:14:07") → 2147454847, which is 0x7FFF8F7F

        Your hypothesis is demonstrated! Let's adjust for the time zone and see what happens...

        UNIX_TIMESTAMP("2038-01-19 03:14:07") → 2147483647, which is 0x7FFFFFFF. Eight hours later. What the hell time zone is my server running in? I should check that.

        Now for the moment of truth:

        UNIX_TIMESTAMP("2038-01-19 03:14:08") → 2147483648

        Right! So thank you for your question. I now have a new one to look at -- why is my server running in a timezone other than my local time or UTC? It's turtles all the way down, this job...

        • Andrew Klossner says:

          MySQL fixed this in version 8.0.28 for systems with 64-bit integers. That's relatively recent. I see quite a few systems running older versions.

  6. Kyzer says:

    MySQL bug 12654: 64-bit unix timestamp is not supported in MySQL functions was filed on 18 Aug 2005. That was 6036 days ago. It is merely 5788 days until the year 2038.

    We are closer to 2038 than this bug is to getting fixed.

    Incidentally, the bug was fixed in MySQL 8.0.28, released last month. It's just not fixed in MySQL's spiritual successor, MariaDB.

  • Previously