CODEWITHSUNDEEP
mysqlmariadb
ProudOne
ProudOne

Reputation: 355

Using DateTime in MariaDB 10.3.9 system versioned tables works for rowStart but not rowEnd

I'm testing out system versioned tables in MariaDB 10.3.9 - but I'd like to version past 2038-01-19 03:14:07.999999 because I'm assuming I'll still be around in 19 years. To that end I'm using DATETIME(6) instead of TIMESTAMP(6). This does not work. The AS ROW END generation comes out as 0000-00-00 00:00:00.000000.

As a result, I cannot select or alter the current state of the table. Is this a bug or am I missing some configuration option, implementation detail that makes DATETIME unfeasible?

Here's the printout from a test session:

MariaDB [tests]> CREATE TABLE dateTimeTest
    -> (
    ->   dtt_id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   dtt_value    VARCHAR(6)   NOT NULL,
    ->   dtt_rowStart DATETIME(6)           GENERATED ALWAYS AS ROW START,
    ->   dtt_rowEnd   DATETIME(6)           GENERATED ALWAYS AS ROW END,
    ->   PERIOD FOR SYSTEM_TIME(dtt_rowStart, dtt_rowEnd),
    ->   --
    ->   PRIMARY KEY DTT_pk(dtt_id)
    -> ) WITH SYSTEM VERSIONING;
Query OK, 0 rows affected (0.057 sec)

MariaDB [tests]> INSERT INTO dateTimeTest (dtt_value)
    -> VALUES ('valueA'),
    ->        ('valueB');
Query OK, 2 rows affected (0.009 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [tests]> SELECT *
    -> FROM dateTimeTest;
Empty set (0.000 sec)

MariaDB [tests]> SELECT * FROM dateTimeTest FOR SYSTEM_TIME ALL;
+--------+-----------+----------------------------+----------------------------+
| dtt_id | dtt_value | dtt_rowStart               | dtt_rowEnd                 |
+--------+-----------+----------------------------+----------------------------+
|      1 | valueA    | 2018-10-04 20:49:50.763456 | 0000-00-00 00:00:00.000000 |
|      2 | valueB    | 2018-10-04 20:49:50.763456 | 0000-00-00 00:00:00.000000 |
+--------+-----------+----------------------------+----------------------------+
2 rows in set (0.000 sec)

MariaDB [tests]>

Upvotes: 1

Views: 459

Answers (2)

Eugene Kosov
Eugene Kosov

Reputation: 993

Here is some discussion about DATETIME and system-versioning tables. In short, it's disallowed intentionally and someday there will be a data type with a broader range. https://jira.mariadb.org/browse/MDEV-17448

Upvotes: 1

Rick James
Rick James

Reputation: 142306

You may be around, but it won't be. Walk with me through a thought exercise...

19 years ago, MySQL was in version 3.xx running MyISAM only. And Windows was running a version that will not run today unless you saved that old hardware. And it had much less than 1GB of RAM and the backups were probably on floppies. (Remember floppies?) And the display device was a CRT connected with a VGA cable. (When did you last see a CRT?)

Would you trust anything that old? Could you even get it to run?

How can you keep alive something for 19 years? You must upgrade everything every 5 years -- hardware, input device, output devices, memory, storage, OS, MySQL, etc, etc.

Shift forward 19 years.

I claim you will need to do 4 major upgrades between now and 2038. Dealing with the Y-2038 problem will be just one minor thing to deal with.

If you do need to peek ahead, you can at least use DATE until the Y10K problem arises. That will be long after we are dead.

Upvotes: 1

Related Questions