Unlike PostgreSQL’s postgres, the mysql database is meant for system tables. Don’t use it.

SQL Time Travel

MariaDB grew some cool new features during the last year or so, one of them — and not the least — being SQL:2011's Temporal Data Tables: columns (or even entire tables) can now be versioned for easy storage of time-varying data like usernames, status messages, or the price of the Bitcoin Pizza. Here is an example of a CREATE statement using this feature:

CREATE TABLE users
(
	id
		INTEGER
		PRIMARY KEY
		AUTO_INCREMENT,
	username
		VARCHAR(255)
		NOT NULL,
	status
		TEXT
		NOT NULL
		WITH SYSTEM VERSIONING
);

Temporal data tables act like regular tables when no time period is specified (FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP is assumed), but they support querying of past state either at a specific point in time or over a time period. The latter may return multiple results with the same primary key:

MariaDB [test]> SELECT *,ROW_START,ROW_END FROM users FOR SYSTEM_TIME ALL;
+----+----------+----------+----------------------------+----------------------------+
| id | username | status   | ROW_START                  | ROW_END                    |
+----+----------+----------+----------------------------+----------------------------+
|  1 | morgan   | Pensive  | 2019-08-08 18:30:51.188262 | 2019-08-08 18:34:05.200261 |
|  1 | morgan   | Happy!   | 2019-08-08 18:34:05.200261 | 2019-08-08 18:54:48.105328 |
|  1 | morgan   | Inspired | 2019-08-08 18:54:48.105328 | 2038-01-19 03:14:07.999999 |
+----+----------+----------+----------------------------+----------------------------+
3 rows in set (0.001 sec)
Note
 Article 5 of the GDPR states that, unless of scientific or historical value, personal data of european citizens shall be kept for no longer than is necessary for the purposes for which the personal data are processed. You might want to take a look at the DELETE FOR PORTION statement before storing PII in a temporal table. If in doubt, consult with your Data Protection Officer (you have a DPO, right? 😛).

Error 4132: This mine not yours.

If you have Docker and a POSIX shell, this "one-liner" (ahem) will get you a MariaDB REPL in less than a minute:

container=$(docker run --rm -d -e MYSQL_ROOT_PASSWORD=secret mariadb);
while ! docker exec -it $container mariadb --password=secret; do
	clear;
	docker logs $container --tail $(tput lines);
	sleep 10;
done;
docker stop $container;

After a couple of Access denied warnings while the server is starting, you’ll get greeted by this command prompt:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
...

MariaDB [(none)]>

That’s probably a familiar sight for anyone who has used MariaDB before, but if like me you got so used to PostgreSQL’s database-scoped connections and public schema you forgot it’s not actually part of the SQL standard, that [(none)] might give you pause for a second: surely that’s not the name of the default schema, is it?

> SHOW DATABASES;

Ah, there it is:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

Obviously performance_schema and information_schema shouldn’t be messed with: the latter contains the info describing the tables, views, triggers and partitions on your server, while the former is used in monitoring server performance. But mysql, that’s not a system DB, is it?

It is.

It contains grant and privilege information, but more importantly it also contains the transaction registry: the table storing transaction-precise history, allowing system-versioned tables to be viewed exaclty as they were at some point in time, excluding any not-yet-commited changes. And this is why you’ll be left with this error message if you try to create a Temporal Data Table in the mysql DB:

ERROR 4132 (HY000): System-versioned tables in the `mysql` database are not suported

You could easily end up with a badly messed up DB if the engine allowed you to enable versionning on some of these system tables, so they’re competely banned from the mysql base. Probably a bit overkill, yes. But storing application data in a system table was a very bad idea in the first place so…​ it’s probably for the best.