N8n no longer starts, error SQLITE_CONSTRAINT: UNIQUE constraint failed: execution_entity.id

Questions
1

I had to write an image backup to the SD card of my Raspberry pi.

But now n8n does not start anymore. Here is the log:

today at 20:49:25 2021-10-04T18:49:25.231Z | info     | Initializing n8n process {"file":"start.js"}
today at 20:49:25 2021-10-04T18:49:25.257Z | info     | 
today at 20:49:25 n8n now checks for new versions and security updates. You can turn this off using the environment variable N8N_VERSION_NOTIFICATIONS_ENABLED to "false"
today at 20:49:25 For more information, please refer to https://docs.n8n.io/getting-started/installation/advanced/configuration.html
today at 20:49:25  {"file":"start.js"}
today at 20:49:31 2021-10-04T18:49:31.308Z | debug    | No codex available for: ElasticSecurity.node.js {"file":"LoadNodesAndCredentials.js","function":"addCodex"}
today at 20:49:48 2021-10-04T18:49:48.721Z | debug    | No codex available for: Misp.node.js {"file":"LoadNodesAndCredentials.js","function":"addCodex"}
today at 20:49:52 2021-10-04T18:49:52.518Z | debug    | No codex available for: Netlify.node.js {"file":"LoadNodesAndCredentials.js","function":"addCodex"}
today at 20:49:52 2021-10-04T18:49:52.531Z | debug    | No codex available for: NetlifyTrigger.node.js {"file":"LoadNodesAndCredentials.js","function":"addCodex"}
today at 20:49:52 2021-10-04T18:49:52.767Z | debug    | No codex available for: N8nTrainingCustomerDatastore.node.js {"file":"LoadNodesAndCredentials.js","function":"addCodex"}
today at 20:49:52 2021-10-04T18:49:52.777Z | debug    | No codex available for: N8nTrainingCustomerMessenger.node.js {"file":"LoadNodesAndCredentials.js","function":"addCodex"}
today at 20:50:01 2021-10-04T18:50:01.004Z | debug    | No codex available for: Splunk.node.js {"file":"LoadNodesAndCredentials.js","function":"addCodex"}
today at 20:50:08 2021-10-04T18:50:08.840Z | debug    | No codex available for: UrlScanIo.node.js {"file":"LoadNodesAndCredentials.js","function":"addCodex"}
today at 20:50:38  ›   Error: There was an error: SQLITE_CONSTRAINT: UNIQUE constraint failed: 
today at 20:50:38  ›   execution_entity.id
today at 20:50:39 Container stopped

It seems to me that something is wrong with the execution_entity. I have already copied the sqlite-db separately and opened it with dbeaver. However, with the Delete command in dbeaver the following message appears:

SQL error [11]: [SQLITE_CORRUPT] The database disk image is malformed (database disk image is malformed).
  [SQLITE_CORRUPT] The database disk image is malformed (database disk image is malformed)
  [SQLITE_CORRUPT] The database disk image is malformed (database disk image is malformed)

I do not necessarily need all execution data. Does anyone have an idea how I can fix this?

Thanks a lot!

2

Did you check the post below?

3

Hi!

Yes I have, but the version of n8n has not changed. Before it worked, now it doesn’t.

Or are you referring to the temp-database? I don’t have it at all, at least I don’t see anything in dbeaver.

4

I thought that you might have updated n8n. If you have not, then the post does not apply to you. In the forum, the only reference related to that error seems to have happened when updating n8n. I’m going to assign this to one of my coworkers that might have a better insight. In the meantime, I will have a look at the link below.

https://www.sqlite.org/howtocorrupt.html

6

Looks like when you restored the backup the database got all corrupted, quickest option if you have been backing up your workflows would be to delete it and start it again.

1 Like
7

Just the database or also the full n8n data folder?

8

Just the database should do it although you could just bin it all and let it recreate it self. It isn’t ideal but if you have a production environment down it would get you back up and running. I would be tempted to maybe move to an external database like Postgres or MySQL if it is an option.

9

Thank you. I’ll wait for the feedback from ricardo. If there is no other solution, I will take the opportunity to switch everything to Postgres.

10

Hey @amalgam perhaps you can drop the execution_entity table and recreate it using dbeaver?

For latest n8n versions the create command should be as follows:

CREATE TABLE IF NOT EXISTS "execution_entity" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "data" text NOT NULL, "finished" boolean NOT NULL, "mode" varchar NOT NULL, "retryOf" varchar, "retrySuccessId" varchar, "startedAt" datetime NOT NULL, "stoppedAt" datetime, "workflowData" text NOT NULL, "workflowId" varchar, "waitTill" DATETIME);

CREATE INDEX "IDX_cefb067df2402f6aed0638a6c1" ON "execution_entity" ("stoppedAt");

CREATE INDEX "IDX_ca4a71b47f28ac6ea88293a8e2" ON "execution_entity" ("waitTill");

If the problem is with the execution table alone, this might help you reuse the existing backup. If you have a previous backup, this is also an option, as long as it contains your workflows.

One note is that you should save n8n’s folder because it contains a config file that stores the encryptionKey. This is used to safely store your credentials. Without it you’d have to recreate all credentials.

Lastly, I strongly recommend using MySQL or Postgres on a production environment as SQLite, although amazing, has some limitations that can cause problems like this one.

I wish you the best of luck!

2 Likes
11

Hello!

Thank you for the suggestions. I used the circumstance to make the switch from SQLite to Postgres. Luckily I had a backup of all workflows :slight_smile:
Now everything is running again with base DB Postgres.

Thanks for your efforts!

4 Likes