Cannot create collection: [INVALID_PAYLOAD] Collection "xxx" doesn't exist

[cdwmhcc]

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

I cannot create a collection for the latest three versions.

To Reproduce

Just create a collection,
Check the basic fields provided by the system such as user_created.
Then create

DB_CHARSET="UTF8MB4"

DB_CHARSET="UTF8"

## DB_CHARSET="UTF8"

I have tried all the above settings, but it doesn't work.

Centos 8.3 MySQL: 8.0 utf8mb4_general_ci

I also tried to run it locally in Windows 10.

Errors Shown

[INVALID_PAYLOAD] Collection "test" doesn't exist

What version of Directus are you using?

v9.2.0

What version of Node.js are you using?

16.13.0

What database are you using?

mysql 8.0

What browser are you using?

chrome

What operating system are you using?

Centos 8.3

How are you deploying Directus?

running locally

[Oreilles]

Regarding which character set to use, it should usually be the value returned by SELECT @@character_set_database.
Regarding your inability to create a collection, does creating it without selecting any of the default ones work ?
And do you still face your issue after downgrading to an older version like 9.0.0 ?

[cdwmhcc]

Regarding your inability to create a collection, does creating it without selecting any of the default ones work ?

1. Uncheck the default field, the collection can be created.
2. Check the fields except user_created and user_updated, the collection can be created.

However, after the collection is created successfully, I cannot add fields, and it prompts [FORBIDDEN] You don't have permission to access this.

I created a lot of collections before the official version, and they worked normally. But I forgot the specific version from which the collection cannot be created. I just remember that everything worked before the official version 9.0.0.(rc100?)

[cdwmhcc]

By the way, I freshly installed directus, and the same error was thrown when creating a collection.

[Oreilles]

Can you run and check the result of this query in a normal client: SELECT @@character_set_database ?
Is the table actually created after you added it through the interface - e.g. can you see them in a normal database client ?
Finally, could you check the character set for the table directus_users, specifically the field id ?

[cdwmhcc]

Can you run and check the result of this query in a normal client: SELECT @@character_set_database ?

[cdwmhcc]

I converted my database from utf8mb4_general_ci to utf8mb4_0900_ai_ci and it worked!

I don't know why.

@Oreilles

[cdwmhcc]

Use Running Locally:

https://docs.directus.io/contributing/running-locally/

character_set	work?
utf8mb4_general_ci	no
utf8mb4_0900_ai_ci	yes

Windows 10 + Nodejs 16.13.0 + (MySQL 8.0.24 by CentOS 8.3)

Use Manual:

https://docs.directus.io/getting-started/installation/manual/)](https://docs.directus.io/getting-started/installation/manual/

character_set	work?
utf8mb4_general_ci	no
utf8mb4_0900_ai_ci	no

CentOS 8.3 + MySQL 8.0.24 + Nodejs 16.13.0

@Oreilles

[Oreilles]

utf8mb4_general_ci and utf8mb4_0900_ai_ci are collations, not character set.

utf8mb4_0900_ai_ci is the default collation for character set utf8mb4, so with env DB_CHARSET=utf8mb4 it is logical that you can now create compatible table after changing your database to this collation.

However, the default collation for character set utf8 should be utf8mb4_general_ci, so having DB_CHARSET=utf8 and all database columns using utf8mb4_general_ci you shouldn't face any issue either...

If you search for "character set" in this repository issues you'll find many duplicates of this type of issue, so I'll close this one - It most likely isn't an issue with Directus, but rather with the way your database and environment variables are configured. If you find a solution to your issue, please let us know as it might help others too.

[cdwmhcc]

utf8mb4_general_ci and utf8mb4_0900_ai_ci are collations, not character set.

This is a very strange question.
In a previous version, utf8mb4_0900_ai_ci did not work, but converted to utf8mb4_general_ci it worked.

For this strange problem, rijkvanzanten may have the impression that I communicated with him in an issue.

Both of these times are caused by the collations, and it can work after modifying the collations. So it's very strange.

#8561 (comment)

[flihub]

i tried every combination with fresh start ::
only working with "utf8mb4_general_ci" all other collations gives same error very annoying issue:(

node v16.14.0
directus v9.6.0
mysql v8.0.26

[isawk]

We have started experiencing this problem on latest version of directus 9.19.2,
Locally connecting to Azure MySQL database works
Azure deployed service connecting to the same database, using same user details produces [INVALID_PAYLOAD] error. Regardless of what collations we use behaviour is the same, even doing a fresh install.

[licitdev]

@isawk Kindly open a new issue with minimal reproduction steps to replicate your issue. Do include the error message provided in the INVALID_PAYLOAD error too. Thanks.