[GNC] MySQL

[John Ralls]

> On Mar 7, 2019, at 7:39 AM, Jean-David Beyer via gnucash-user <gnucash-user at gnucash.org> wrote:
> 
> On 3/7/19 7:22 AM, David G Hamblen wrote:
>> In the past, I've had problems with a mysql file, just getting
>> seqfaults.  Turned out that I was entering a transaction with a 1962
>> date.  I forget the details, but mysql stored it as a zero, which I
>> couldn't reopen.  I changed the date (to post-1970?)  with the mysql
>> command line tool.  Perhaps more recent versions of GC handle this better.
>> 
> 
> In the old days, and perhaps to this day, UNIX and Linux systems had no
> dates prior to 1970/01/01. Maybe mysql still has this limitation. In the
> early days of UNIX, it ran on 16-bit machines and they kept time to the
> second in a double-precision format.
> 
> Now, with 64-bit machines being de rigueur, time is kept more
> accurately, and will not run out in 2038. ;-)
> 

MySQL has two date-time field types, TIMESTAMP and DATETIME. TIMESTAMP uses a 32-bit unix time and doesn't like 0, so it works only after 1970-01-01 00:00:01. It's also subject to the 2038 bug. DATETIME is supposed to be good from 1000-01-01 to 9999-12-31 and we changed the MySQL schema in GnuCash to use DATETIME for GnuCash 3.2.

Note that many Linux distros actually install MariaDB for their "MySQL" package and MariaDB's documentation says that it treats TIMESTAMP as DATETIME, but Mechtilde found a problem with that, see https://bugs.gnucash.org/show_bug.cgi?id=797121. There's also at least one case where the conversion either failed or didn't run, https://bugs.gnucash.org/show_bug.cgi?id=797112.

Regards,
John Ralls