Cannot create items in collection with both date_created and date_updated system fields

[aliemba]

Preflight Checklist

• I have completed all Troubleshooting Steps.
• I'm on the latest version of Directus.
• There's no other issue that already describes my problem.

Describe the Bug

Can't create records in collection with both user_created and user_updated

To Reproduce

• Create a collection with both date_created and date_updated system fields
• Add a string input field to be able to create new items in this collection
• Try to add a new item in the collection
• Server returns 400

I tested this on two different dbs; mysql and mariadb - both with the same result.

Errors Shown

0|directus | 17:59:17 🐛 "date_updated" is required
0|directus | 17:59:17 ✨ request completed POST 400 /items/test 6ms

What version of Directus are you using?

9.5.1

What version of Node.js are you using?

12.21.0

What database are you using?

Mysql

What browser are you using?

Chrome

What operating system are you using?

Ubuntu

How are you deploying Directus?

locally

[jasloe]

I am experiencing the same issue. With the identical configuration @aliemba describes, reproduce as follows:

• Create new collection
• Add sort together with either of the optional system user-relationship fields to the collection configuration
• Add input field
• Create new item returns: POST http://localhost:8056/items/example 400 (Bad Request)

[azrikahar]

I can't seem to reproduce this on a fresh MySQL Server 8.0.28, here's a short clip:

DIxErZPNBy.mp4

---

@aliemba would you mind providing the following:

• the exact version of MySQL and MariaDB you have tested

• Whether you have set the DB_CHARSET environment variable or not. Related to it, do let us know if you have seen any of the following messages in the logs after start up:

  • Using custom DB_CHARSET <charset here>. Using a charset different from the database's default can cause problems in relationships. Omitting DB_CHARSET is strongly recommended.

  • Some tables and columns do not match your database's default collation ...

@jasloe just to be sure, are you testing on an existing or fresh install/db? Would you mind answering the questions above as well? Thanks!

[aliemba]

DB_CHARSET is not set in the env vile

Some tables and columns do not match your database's default collation ... is shown. Please see the original post for an image of the startup log. (from a fresh install of db + directus)

Versions this occurs on for me
Fresh install (also db server) 9.5.1 fails:
mariadb Ver 15.1 Distrib 10.3.31-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

9.4.3 works - but if i upgrade to 9.5.1 i fails (same db install):
mysql Ver 15.1 Distrib 10.5.13-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper

UPDATE
I did a fresh install using a "real" mysql;
mysql Ver 8.0.27-0ubuntu0.21.04.1 for Linux on x86_64 ((Ubuntu))

And this works!

So this seems related to mariadb (probably 15.1) both as a fresh directus install and when doing an upgrade

[azrikahar]

Please see the original post for an image of the startup log. (from a fresh install of db + directus)

@aliemba ahh gotcha. I was totally fixated in #11069 which is likely the cause of this issue and ended up missing the exact sentence in your screenshot 🤦‍♂️

Thanks again for the update in that this might be something specific to MariaDB instead. It should be 10.3.31 and 10.5.13 being the versions instead, rather than 15.1, since MariaDB latest version is only 10.x 😄

With that said, @Oreilles any thoughts on what is happening here?

[Oreilles]

The warnings about table collations not matching the database default appears to be because when creating a json column in mysql, it actually aliases to a longtext with collation utf8mb4_bin. So we should probably ignore such columns for the collation check. But the check is only done once and the message is only informative, it doesn't affect any further query.

I haven't been able to replicate the issue on a fresh install on MariaDB 10.7 and after following the exact steps mentioned. Edit: can't replicate either on 10.5.13.

@aliemba could you try to reproduce the issue, but setting LOG_LEVEL=trace in your env and submit the logs ?

[aliemba]

@Oreilles here is the part from the log - when I press save.

0|directus | 17:59:17 🔍 [0.452ms] select ip_access from directus_roles where id = ? limit ? [15fa4103-c53d-4530-b66b-c60c9369af04, 1]
0|directus | 17:59:17 🐛 "date_updated" is required
0|directus | 17:59:17 ✨ request completed POST 400 /items/test 6ms

Not much to work with.

This is an 9.4.3 (that works) i upgrade to 9.5.1 - create a new collection - tick all the "extras" - and create an item in the collection.

If I add an item (record) to an existing collection - it works fine. Seems related to newly created collections with the "sort + updated_by and so on"

[Oreilles]

Ok, I've been able to replicate the issue. You said in your original report that your only checked sort, user_created and user_updated, but the issue here happens when you tick date_created and date_updated.
If you create a collection with only date_created it works fine.
If you create a collection with only date_updated it works fine.
If you create a collection with both date_created and date_updated, you get a 400 when creating new items, which is caused by:

0|directus | 17:59:17 🐛 "date_updated" is required

[jasloe]

Late to the party.

Confirming @Oreilles refinement of the initial report, both date_* fields selected throws a 400.

My config includes the latest from directus/directus:image and fresh installation of 10.6.5-MariaDB-1:10.6.5+maria~focal mariadb.org binary distribution.

No DB_CHARSET variables in my configuration.

New to Directus (and node, in general) and not sure where logs live but I'll look into it. Meantime, schemata shows the following, if it helps:

+--------------+----------------------+----------------------------+------------------------+----------+----------------+
| CATALOG_NAME | SCHEMA_NAME          | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | SCHEMA_COMMENT |
+--------------+----------------------+----------------------------+------------------------+----------+----------------+
| def          | information_schema   | utf8mb3                    | utf8mb3_general_ci     | NULL     |                |
| def          | performance_schema   | utf8mb3                    | utf8mb3_general_ci     | NULL     |                |
| def          | mysql                | utf8mb4                    | utf8mb4_general_ci     | NULL     |                |
| def          | sys                  | utf8mb3                    | utf8mb3_general_ci     | NULL     |                |
| def          | data                 | utf8mb4                    | utf8mb4_general_ci     | NULL     |                |
| def          | example | utf8mb4                    | utf8mb4_general_ci     | NULL     |                |
+--------------+----------------------+----------------------------+------------------------+----------+----------------+

I can't seem to reproduce this on a fresh MySQL Server 8.0.28, here's a short clip:

DIxErZPNBy.mp4
@aliemba would you mind providing the following:

• the exact version of MySQL and MariaDB you have tested

• Whether you have set the DB_CHARSET environment variable or not. Related to it, do let us know if you have seen any of the following messages in the logs after start up:

  • Using custom DB_CHARSET <charset here>. Using a charset different from the database's default can cause problems in relationships. Omitting DB_CHARSET is strongly recommended.
  • Some tables and columns do not match your database's default collation ...

@jasloe just to be sure, are you testing on an existing or fresh install/db? Would you mind answering the questions above as well? Thanks!