CreateDate and UpdateDate columns always get recreated when generating migrations for latest version of MariaDB

[jeremija]

Issue type:

[ ] question
[x] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[x] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] cockroachdb
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

The Issue

I've described this issue here. After taking a deeper look into this problem, I've realized that the cause of issue #2737 and this issue is not the same.

MariaDB 10.2.7 introduced the following change in information_schema.COLUMNS table:

Literals are now quoted in the COLUMN_DEFAULT column to distinguish them from expressions

Since 10.2 (the earliest version I could find on Docker Hub was 10.2.5) the expressions are returned in lowercase. This means we now get current_timestamp(6) instead of CURRENT_TIMESTAMP(6) for create/update date columns like it is in MariaDB 10.1 and MySQL.

Since the MysqlDriver#mappedDataTypes.createDateDefault and updateDateDefault are defined as CURRENT_TIMESTAMP(6), the MysqlDriver#findChangeColumns function marks these date columns as changed, and new migrations for these columns will be generated indefinitely.

Default Values Quoted Twice

While trying to fix this issue, I noticed that the literal default values are double quoted in MariaDB 10.2, since they will already have quotes in the information_schema.COLUMNS table, and quotes will be added again on this line:

typeorm/src/driver/mysql/MysqlQueryRunner.ts

Line 1294 in 13ac222

tableColumn.default = dbColumn["COLUMN_DEFAULT"] === "CURRENT_TIMESTAMP" ? dbColumn["COLUMN_DEFAULT"] : `'${dbColumn["COLUMN_DEFAULT"]}'`;

---

More Info

COLUMN_DEFAULT: Default value for the column. From MariaDB 10.2.7, literals are quoted to distinguish them from expressions. NULL means that the column has no default. In MariaDB 10.2.6 and earlier, no quotes were used for any type of default and NULL can either mean that there is no default, or that the default column value is NULL.

• https://mariadb.com/kb/en/library/information-schema-columns-table/

---

Examples:

MariaDB 10.1.41

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Server version: 10.1.41-MariaDB-1~bionic mariadb.org binary distribution

MariaDB [test]> CREATE TABLE test(stamp datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6));
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> SELECT table_name, column_name, column_default FROM information_schema.COLUMNS WHERE table_schema = DATABASE();
+------------+-------------+----------------------+
| table_name | column_name | column_default       |
+------------+-------------+----------------------+
| test       | stamp       | CURRENT_TIMESTAMP(6) |
+------------+-------------+----------------------+
1 row in set (0.00 sec)

MariaDB 10.2

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Server version: 10.2.5-MariaDB-10.2.5+maria~jessie mariadb.org binary distribution

MariaDB [test]> CREATE TABLE test(stamp datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6));
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> SELECT table_name, column_name, column_default FROM information_schema.COLUMNS WHERE table_schema = DATABASE();
+------------+-------------+----------------------+
| table_name | column_name | column_default       |
+------------+-------------+----------------------+
| test       | stamp       | current_timestamp(6) |
+------------+-------------+----------------------+
1 row in set (0.00 sec)

MySQL 8.0.17

Welcome to the MySQL monitor.  Commands end with ; or \g.
Server version: 8.0.17 MySQL Community Server - GPL

mysql> CREATE TABLE test(stamp datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6));
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT table_name, column_name, column_default FROM information_schema.COLUMNS WHERE table_schema = DATABASE();
+------------+-------------+----------------------+
| TABLE_NAME | COLUMN_NAME | COLUMN_DEFAULT       |
+------------+-------------+----------------------+
| test       | stamp       | CURRENT_TIMESTAMP(6) |
+------------+-------------+----------------------+
1 row in set (0.01 sec)