Inconsistent behavior with 'Add Existing' for Many-to-Any Relations in Directus Collections

[alirezahamid]

Describe the Bug

I'm experiencing an inconsistency when managing a Many-to-Any relationship between two collections: page and card_list in Directus.

Schema Overview:

Page Collection: title (string) blocks (Many-to-Any relation with card_list) Card List Collection: title (string)

The Problem: While updating an existing page, I can successfully create new card_list items from the blocks field. However, when trying to use the "Add Existing" option to select card_list items, the list appears empty—despite the fact that I'm certain there are existing card_list entries in the collection.

Here are two specific scenarios where this inconsistency occurs:

1. When updating an existing page: The "Add Existing" card_list option displays an empty list, preventing me from selecting existing items.

2. When adding a new page: The "Add Existing" card_list option works as expected, and I can see the available card_list items to select from.

You can try and see the screenshot attached here:

Errors on Console and Network:

Request URL:
http://localhost:8055/items/card_list?filter[_and][0][$FOLLOW(page_blocks,item,collection)][_none][_and][0][page_id][_eq]=1&filter[_and][0][$FOLLOW(page_blocks,item,collection)][_none][_and][1][collection][_eq]=card_list&aggregate[countDistinct]=id

Response:
{
    "errors": [
        {
            "message": "select count(distinct `card_list`.`id`) as `countDistinct->id` from `card_list` left join `page_blocks` as `wiwht` on `wiwht`.`collection` = 'card_list' and `wiwht`.`item` = CAST(`card_list`.`id` AS CHAR(255)) where (CAST(`card_list`.`id` AS CHAR(255)) not in (select `page_blocks`.`item` as `item` from `page_blocks` where `page_blocks`.`item` is not null and (`page_blocks`.`page_id` = 1 and `page_blocks`.`collection` = 'card_list'))) limit 100 - Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='",
            "extensions": {
                "code": "INTERNAL_SERVER_ERROR"
            }
        }
    ]
}

Request URL:
http://localhost:8055/items/card_list?limit=25&fields[]=title&fields[]=id&sort[]=sort&page=1&filter[_and][0][$FOLLOW(page_blocks,item,collection)][_none][_and][0][page_id][_eq]=1&filter[_and][0][$FOLLOW(page_blocks,item,collection)][_none][_and][1][collection][_eq]=card_list

Response:
{
    "errors": [
        {
            "message": "select `card_list`.`title`, `card_list`.`id` from `card_list` inner join (select distinct `card_list`.`id`, `card_list`.`sort` as `sort_dmcsg` from `card_list` left join `page_blocks` as `iiwzd` on `iiwzd`.`collection` = 'card_list' and `iiwzd`.`item` = CAST(`card_list`.`id` AS CHAR(255)) where (CAST(`card_list`.`id` AS CHAR(255)) not in (select `page_blocks`.`item` as `item` from `page_blocks` where `page_blocks`.`item` is not null and (`page_blocks`.`page_id` = 1 and `page_blocks`.`collection` = 'card_list'))) order by `card_list`.`sort` asc limit 25) as `inner` on `card_list`.`id` = `inner`.`id` order by `inner`.`sort_dmcsg` asc - Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='",
            "extensions": {
                "code": "INTERNAL_SERVER_ERROR"
            }
        }
    ]
}

Request URL:
http://localhost:8055/items/card_list?aggregate[countDistinct]=id&filter[_and][0][$FOLLOW(page_blocks,item,collection)][_none][_and][0][page_id][_eq]=1&filter[_and][0][$FOLLOW(page_blocks,item,collection)][_none][_and][1][collection][_eq]=card_list

Response:
{
    "errors": [
        {
            "message": "select count(distinct `card_list`.`id`) as `countDistinct->id` from `card_list` left join `page_blocks` as `lgopk` on `lgopk`.`collection` = 'card_list' and `lgopk`.`item` = CAST(`card_list`.`id` AS CHAR(255)) where (CAST(`card_list`.`id` AS CHAR(255)) not in (select `page_blocks`.`item` as `item` from `page_blocks` where `page_blocks`.`item` is not null and (`page_blocks`.`page_id` = 1 and `page_blocks`.`collection` = 'card_list'))) limit 100 - Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='",
            "extensions": {
                "code": "INTERNAL_SERVER_ERROR"
            }
        }
    ]
}

To Reproduce

1. Have 2 collections like this:

• page (collection): | title(string) | blocks(Many 2 Any with card_list)

• card_list: | title(string)

2. Create a page and within the page create one card_list under blocks and Save the page

3. Open the same page you just created and try to add card_list from Add Existing button

You will see the select from list of card_list is empty

But when you try to add just create a fresh/new page and try to add existing card_list for blocks, you can see the list is just okay and returns the existing card_list.

1. Create two collections: page and card_list as described.

2. Create a few card_list items.

3. Try adding an existing card_list item to the page collection via the blocks Many-to-Any relation on an existing page record.

4. Observe the empty list in the "Add Existing" modal, even though there are card_list items in the collection.

5. Now, add a new page record and use "Add Existing" for card_list. The list shows the available items.

Expected Behavior:

The "Add Existing" option should consistently show the list of card_list items regardless of whether I'm updating an existing page or adding a new one.

Directus Version

V11.1.0

Hosting Strategy

Self-Hosted (Docker Image)

Database

MySQL 8

[m430]

Same problem

[br41nslug]

Quick question, did you create all the relevant collections through the Directus App and/or API? If so these should have been created using the same collation 🤔

Sidenote: This is manually fixable in SQL by adjusting the collation for the collections so that they are consistent across the database

[alirezahamid]

Yes I created them using Directus App. @br41nslug

[ammarqureshiO3]

same problem

[iprokhorov]

We had same problem. I found out by trying that: if you check the "Allow Duplicates" checkbox on the relation field, it works properly. Go to your blocks field configuration on your page collection and check inferface->allow duplicates.

i dont belive this workaround is a stable solution, but maybe it could help on further analysis

[ammarqureshiO3]

@iprokhorov thanks, it worked

[iprokhorov]

The problem is resolved by explicitly setting DB_CHARSET: "UTF8_GENERAL_CI" (in our case, the default collation of the DB) in as ENV. I hadn’t initially added this, as the documentation states that it is the default setting (see documentation).

However, this setting was not actually applied by default in this case if it isn’t explicitly specified.

[jjeising]

Setting DB_CHARSET: "UTF8_GENERAL_CI" partially works, but this is not a correct configuration value. The string is an export from the mysqljs module, but it looks like these cannot be used in configuration. The following configuration also works for creating tables:

DB_CHARSET: utf8mb4

(you can also use utf8, if needed).

[ComfortablyCoding]

I can confirm that the default charset value for DB_CHARSET is correctly set as specified in our documentation. The mysql2 driver enforces stricter charset comparison rules, meaning the DB_CHARSET must exactly match the charset set on the tables. However, it's important to note that DB_CHARSET applies only to the connection and does not set the table collation.

To configure the table collation, you need to use the --character-set-server and --collation-server options. For example, setting --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci resolved the issue for me, while keeping the DB_CHARSET at its default value.

As this a configuration issue I will proceed to close it. Happy to continue discussing if needed in the thread.