Problem creating credential (google sheet)

Valerian_Lebert · September 7, 2023, 7:03am

Hi, me again

I have a new problem on Google Sheet Oauth credentials. When trying to create such credential I have the following error after clicking on “Sign in with Google”

Problem creating credential
SQLITE_CONSTRAINT: FOREIGN KEY constraint failed

I tried other credential types and I didn’t have errors so it seems specific to google sheet ?

See attached, I tried a Google Drive credential with the same Oauth IDs and got no error.

cheers

Information on your n8n setup
n8n version: 1.4.1
Database (default: SQLite): SQlite
n8n EXECUTIONS_PROCESS setting (default: own, main): ?
Running n8n via (Docker, npm, n8n cloud, desktop app): npm
Operating system: Ubuntu server

EmeraldHerald · September 7, 2023, 1:46pm

Hi @Valerian_Lebert Sorry you’re running into this - this should have been fixed, as our engineers had eyes on this last week Can you update / restart your instance and see if that fixes it up?

Valerian_Lebert · September 7, 2023, 1:59pm

Hi @EmeraldHerald

I am on version 1.4.1 and updated/restarted the instance this morning.

Still having this issue. Do you know which version is supposed to fix this ?

Thanks

EmeraldHerald · September 7, 2023, 2:08pm

Hi @Valerian_Lebert - ah, sorry about that. Are you also self-hosting n8n?

Could you try to update to latest, and also try creating new credentials? If you’re self-hosting, there may be an issue with your database If that fails, it might be worth looking for any other signs of corruption (and then try fixing them as per Recovering Data From A Corrupt SQLite Database), or recover a backup of your database. It also would be worth to see if you’ve got any other information in your server logs, too

Valerian_Lebert · September 7, 2023, 2:27pm

Hi @EmeraldHerald

I think I am on the latest as I don’t have any notification of a new version (updated this morning). Maybe the fix you are talking about is not yet released ??

I could create new credentials, only Google Sheets seems to cause problem (of course I didn’t try EVERY credentials type but I tried Airtable, Google Drive …)

I would be glad to provide useful logs. Which logs could be helpful ?

EmeraldHerald · September 7, 2023, 2:32pm

Don’t worry, I wouldn’t have asked you to try every credential

And the fix was something that went out on cloud What do your instance or Docker logs have to say? Any logs would be helpful! You may need to set your N8N_LOG_LEVEL to DEBUG for this

Valerian_Lebert · September 8, 2023, 9:59am

Hi @EmeraldHerald

Well something really strange happend. I reproduced the issue while showing PM2 logs. I could litterally reproduce this issue like 10 times or more.

Then I restarted n8n in N8N_LOG_LEVEL=debug mode

I could reproduce the issue once, but then on second try I could create google sheet credentials again.

So it seems thad debug mode magically fixed my issue

EmeraldHerald · September 8, 2023, 10:01am

I’d say the instance restarts might have fixed this, but please do let us know if you see the error again

Valerian_Lebert · September 8, 2023, 10:04am

@EmeraldHerald

Not really, see current behaviour:

I need multiples attempt to create a new google sheet credential

Jon · September 8, 2023, 10:28am

Hey @Valerian_Lebert,

That looks like there could be an issue with your database, How long have you been running n8n for and what sort of hardware are you using?

Valerian_Lebert · September 8, 2023, 12:08pm

Hi @Jon ,

I run on an n8n OVH VPS server (2vcores, 4GB RAM) for about 6 monthes now.

What kind of database issue do you think of ?

Jon · September 8, 2023, 1:58pm

Hey @Valerian_Lebert,

Could be that during a previous upgrade something went wrong and it was downgraded. What I would do is export all of your workflows and credentials, Stop n8n, rename the current database (just so you have a backup), Start n8n, Go through the initial setup then import the workflows and credentials and see if that helps.

Jon · September 8, 2023, 2:44pm

Did you also do an update? 1.6 came out this week and 1.5 came out last week so you are a couple of versions behind.

Valerian_Lebert · September 8, 2023, 2:51pm

Hi @Jon

When updating with npn I think your releases are available with a delay isn’t it ?

currently “latest” is 1.5.1

I’ll try the DB restoration later on

Jon · September 8, 2023, 2:52pm

Hey @Valerian_Lebert,

Yeah we typically have a 1 week delay so next week 1.6.1 will likely become latest and 1.7.0 will be released as Next.

Valerian_Lebert · September 8, 2023, 7:03pm

Hey @Jon

So I did backup credentials and wf to JSON, removed my sqlite DB to start fresh (I had ti create my pasword etc.)

and… Same error

I’ll try 1.6 as soon it goes “latest”

At the end it’s not a blocking issue as I can just spam the click button to create the credential but it still a strange issue. Tell me if you need other material to investigate on your side

Valerian_Lebert · September 8, 2023, 7:07pm

By the way the backup/restore process is very neat and efficient

Just to know, i used the --decrypted option but if you don’t use it, how can you know the encryption key if you migrate to a new instance ?

tenil · September 9, 2023, 1:38pm

Just coming here to report that the same thing is happening here.

The only difference is that I was trying to add the credential to the Gmail API.

Exactly the same problem.

Ubuntu 20.04.6 LTS (GNU/Linux 5.15.0-1042-oracle aarch64)

n8n Version 1.5.1

Valerian_Lebert · September 11, 2023, 1:32pm

Thanks for reporting @tenil

So @Jon is it an issue identified on your side ?

Jon · September 11, 2023, 1:48pm

Hey @Valerian_Lebert,

From what I can see in this thread it may be fixed in a new release already, I am just not sure which release fixed it. I know I am not able to reproduce this on 1.6.1 which is what I am currently running though.