V8-archive: Duplicate key on creation if id is changed via database

[alrick]

Bug Report

Steps to Reproduce

1. Setup some records in a collection
2. Alter the id of said records in the database directly
3. Try to add some record via the App

Expected Behavior

The records is added correctly.

Actual Behavior

An error message pop with a "duplicate key" message.

Other Context & Screenshots

Sorry for the title of the report, couldn't find something short and simple to describe it.

The problem is that it seems there is a specific count for the app (I couldn't find where), so if I change the id in the database, the app try to set existing ids. It happens when I take an existing database to integrate with Directus.

Btw the counter is incremented even if the record isn't added to the database, which is another problem but not so problematic.

Technical Details

• Device: Desktop
• OS: MacOS
• Browser: Chrome
• Directus App: 7.0.19
• Directus API:
• Install Method: git clone

[rijkvanzanten]

If you create a new item, I believe the application will rely on the API to set the primary key.

[shealavington]

When you're editing the items, are you making their ID's higher than the Auto-Increment? If so, I would imagine this is expected behavior...

I've seen before where someone created records 1,2,3, but then went to the records and set the ID of record 1 to 4, but because the Auto-Increment was ready for the next creation to become 4, when it tried to increment, the key already existed, hence Duplicate Key.

The simple answer to this is: don't Alter the id of said records in the database directly 😛

[benhaynes]

OK, so I just tested and confirmed this issue. Here's a little more info:

If you have 5 existing items:

1: Apple
2: Orange
3: Pear
4: Grapefruit
5: Grape

Then you change the id (INT, auto-increment) of 2 (Orange) to 7 directly in the database, you get:

1: Apple
3: Pear
4: Grapefruit
5: Grape
7: Orange

Now if you try to add some new items to the database, the _first_ one goes through fine... as: 6: Banana. But then the next one will get Duplicate key "7" for collection "fruit". What's interesting is that if you try to save that item _again_... it works and adds it as ID 8!

So after all that you end up with:

1: Apple
3: Pear
4: Grapefruit
5: Grape
6: Banana
7: Orange
8: Lemon

If you rely on the database (directly) it will work fine, as the auto-increment finds the next available ID int and uses it. There must be some code in Directus that chooses/guesses the ID and then _sets_ it when writing the item.

My guess is that this is because Directus needs to know the ID before saving the item for some reason — perhaps because of deeply relational items (where we need to know the parent ID for the relationship).

The easy answer (Shea mentioned) is to not change the PKs in the database (good practice either way). But this should not be an issue with Directus and needs to be resolved. Let's start by finding where this happens in the API and researching _why_ it's doing it in the first place.

Also... this won't be an issue when we switch to UUID as the default Directus PK! :)

[alrick]

Thanks @benhaynes for the testing, that's the same behavior I experienced.

Regarding the easy answer mentioned by Shea, I agree there is no real reason to directly alter the ID in the database.
But it can happen in a very simple way : if you setup Directus with an existing database (that was a selling point for Directus when I choose the platform). If you have an existing database, you will probably have some tables with gaps between the IDs (deleted records for example).
As I said, it's not app breaking but I think it should be fixed at some point.

[shealavington]

Missing keys shouldn't be an issue, as the auto increment should still be above the highest ID inserted. _(Unless someone's been manually inserting above the AI, of which this would become an issue issue with the data)_

Is this definitely an API issue? — I'm also thinking it could it be that the APP is taking records 1-6 & 9-10, 0 indexing the items making 1-8 and then trying to create # 9 rather than # 11?

[benhaynes]

I think Rijk ruled out the App, and DB auto-increment certainly handles this correctly... so it must be the API doing something funky.

🔔 @bjgajjar @hemratna

[rijkvanzanten]

If you create a new item, I believe the application will rely on the API to set the primary key.

Haven't verified this though.

[benhaynes]

Ahh, let's take a look at the App today to rule that out before @bjgajjar looks through the API. @rijkvanzanten

[bjgajjar]

@alrick
This is the issue is related to MySQL server and not with the Directus.

If the database table uses the InnoDB as a storage engine, while updating the id from the DB directly, will not increment/adjust the value of AUTO_INCREMENT variable of the system.

In the case of the MyISAM as a storage engine, then this will work as expected.

You can read more about InnoDB here

Can you please provide the storage engine for the database as well as a table? So we can verify the same

[alrick]

You're correct, the table uses InnoDB and it seems there was something wrong with the auto-increment of the table.

I will investigate a bit more as the database comes from another department of the university, maybe they exported it the wrong way.

Sorry and thank you for the time you spent investigating.