"Invalid Input Data Please Verify Unique Constraints " after migrating from OneClick to AWS

[NOR-TB]

Bug report

Describe the bug

Moved my strapi build from digitaloceans one click service to aws self-installed strapi on ubuntu 18. with postgres rds.

Database was migrated using aws datamigration service. Content was successfully migrated. There were some initial errors, but repeat of the process with truncate enabled allowed the database to be moved successfully.

Strapi is showing the data, but when trying to edit the migrated content you get met with the error "Invalid Input Data Please Verify Unique Constraints ".

Steps to reproduce the behaviour

1. Open a digitalocean one click strapi app add some content.
2. Set up aws strapi server and database and s3 bucket and steps.
3. move database from digitalocean to aws.
4. try to change content after logging in.

Expected behavior

No error and successful edit of the fields.

System

• Node.js version: 10.19.0
• NPM version: 6.14.4
• Strapi version: 3.46
• Server: AWS EC2 Ubuntu 18
• Database: postgres on aws rds
• Operating system: windows os

Additional context

from the backside the postgres database is giving a 400 error. But if i were to add a item to the collection content, then that item works fine. I just cant edit or change SOME fields in the cms for content that was migrated over.

Ive tried with node_env to both dev and production.

ive tried strapi development mode.

ive tried strapi production.

ive tried updating all the npm packages and updating to latest strapi build.

still getting the same error.

is this issue related to security groups on aws? or the s3 bucket for file uploads?

any help is appreciated.

[derrickmehaffy]

@Tej-Bains

Strapi version: 3.46

We issued a release in v3.5.0 last week that should address this, can you try updating and see if that fixes your issue?

[mrmadhat]

I was getting this error because of a mismatch in package versions. During my deploy I switched the upload provider from strapi-provider-upload-aws-s3 to strapi-provider-upload-cloudinary but when I installed the new provider I didn't realise that the installed version of the upload provider was 3.5.1 and strapi (and other strapi related packages) was 3.4.6. Making sure the provider version matched the strapi version fixed the issue for me.

[brnpimentel]

I updated to last version 3.5.x and fixed the issue

[derrickmehaffy]

Marking as closed as two confirmations stated it's fixed.

[gramsco]

I have the same issue with 3.6.1.
My hypothesis is that bugs like in Strapi that occur when:

• you add a field to a collection
• you add data to the field for an object in said collection
• you remove the field

because Strapi does not remove the data associated to the field. Which could be a good opt-in by the way: "do you want to remove the data associated to the field you're deleting?"

I will try to go directly into the DB to fix the issue (and update my post)

[shadracnicholas]

This is a "by design" Postgress issue that occurs when you mass import. To solve it;

SELECT MAX(the_primary_key) FROM the_table;
SELECT nextval('the_primary_key_seq');
SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);

Do this across all affected tables.

[gramsco]

I should have mentioned it's a mongoDB project!

[sidsaxena0]

I am still facing this issue on 3.6.6 in postgresql.

[sidsaxena0]

This is a "by design" Postgress issue that occurs when you mass import. To solve it;

SELECT MAX(the_primary_key) FROM the_table;
SELECT nextval('the_primary_key_seq');
SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);

Do this across all affected tables.

Thanks it helped me!

[coderdix]

This is a "by design" Postgress issue that occurs when you mass import. To solve it;

SELECT MAX(the_primary_key) FROM the_table;
SELECT nextval('the_primary_key_seq');
SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);

Do this across all affected tables.

I'm new to sequel and had this same error. I'm using TablePlus Query area to input this - could you maybe offer some explanation into what its doing & why it (could) work

[shadracnicholas]

@coderdix When you import the database you need to reset the sequence for each table. What the above script does is increase the sequence id by 1 from the last record in your table. The next time you save, the updated sequence will be the id and auto increased by one. I hope I have not confused you even more. You can DM me on Twitter @shadracnicholas in case this explanation doesn't seem to make sense.

[ahmadjoya]

today I faced this issue as well. 10/3/2021

[shadracnicholas]

@ahmadjoya You are using Postgress as your database?

[bakeruk]

I had the same issue and found that it was a custom utility function producing uncaught errors.

[wintercounter]

Me too, after I cloned an entity (and changed every field) I get the same issue.

[derrickmehaffy]

Me too, after I cloned an entity (and changed every field) I get the same issue.

Double check your server console as that error is more of a database "catch-all" in v3 and the issue will have more details in the server log.

[skworden]

I also had this problem. I was using an existing DB that had a UUID based primary key. Strapi only supports integer primary keys for tables that are modified using Strapi. If you don't ever use relations or image fields you won't have a problem.

If you add a relation or photo field - Strapi will automatically use an integer for the reference regardless of the actual PK field type. I.e., if you have an article table with UUID PK and add an image field, the DB upload table will set the reference field to Int4, making it throw this error.

To fix this – make sure all of your PK fields are Integers.

[i-bsd]

To fix this – make sure all of your PK fields are Integers.

Can you explain how to change this? I'm having the same issue and think this might be it.

[thiboot]

This is a "by design" Postgress issue that occurs when you mass import. To solve it;

SELECT MAX(the_primary_key) FROM the_table; SELECT nextval('the_primary_key_seq'); SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);

Do this across all affected tables.

Resolved my problem, thx @shadracnicholas, further informations here:
https://www.ankursheel.com/blog/reset-primary-key-sequence-out-of-sync-postgres

[fono97]

I also have a same issue.
I am trying to post image on strapi on production.
everything is ok on my development but on production, I have this issue.
I use mongodb

[ashokselva-js]

This is a "by design" Postgress issue that occurs when you mass import. To solve it;

SELECT MAX(the_primary_key) FROM the_table; SELECT nextval('the_primary_key_seq'); SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);

Do this across all affected tables.

Works like a charm✌✌

[harshSinghCondenast]

This is a "by design" Postgress issue that occurs when you mass import. To solve it;

SELECT MAX(the_primary_key) FROM the_table; SELECT nextval('the_primary_key_seq'); SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);

Do this across all affected tables.

I'm new to the postgres. So can any one tell me, "Where Should I execute this query ?" as in strapi we can't do this and if it has to be done in postgres (pgAdmin) in which section it has be done, as I'm trying to do this in query editor but It is not helping me in any way.😫😫 Any video link document will help.

[strapi-bot]

This issue has been mentioned on Strapi Community Forum. There might be relevant details there:

https://forum.strapi.io/t/getting-400-error-while-adding-data/20449/1

[strapi-bot]

This issue has been mentioned on Strapi Community Forum. There might be relevant details there:

https://forum.strapi.io/t/invalid-input-data-please-verify-unique-constraints/20533/1