SQLite Database is Corrupted - HELP

Questions
1

Hello, I had an issue few months ago with reducing the size of my DATABASE. I managed to reduce the size manually the first time and then vacuum it. Now that I tried to manually empty it again and I guess I had an error( I have tried to delete rows with “DELETE FROM execution_entity” ) which then makes the program stuck. I used DB Browser for SQLite on my Raspberry Pi. I guess theres too much data in table…once I ended the process there was a file names databasename.journal something and I deleted it since my database didnt get any smaller. (Dont know what that file is)

After starting n8n process again and trying to execute my workflow I get a an error database is locked … file is corrupted something.

How do I fix this?

Fyi. My database is filled with execution logs → i rly dont need this tho but nevertheless I just need it to work. Also I am not a skilled programmer so please keep language as simple as possible. Thanks!

2

Update: I get the SQLITE CODE: CORRUPT in terminal.
And failed sq quarry gets written in terminal…its never happened before

3

Hey @Leon,

Before you started with the SQLite file did you make sure n8n wasn’t running as that can cause issues. You might be able to run .recover so something like sqlite3 database.sqlite ".recover" | sqlite3 newdb.sqlite then rename newdb.sqlite to database.sqlite and start up n8n and see if it has helped.

Depending on how you have installed n8n as well it might be worth setting the prune, max age and data save defaults so for my install on my Pi that is using SQLite I use the options below as I don’t care about any success logging just what failed and I only keep it for 60 days although that can really be changed to 7 as normally if something goes wrong I would have it fixed within a week but that is down to preference.

- EXECUTIONS_DATA_SAVE_ON_ERROR=all
- EXECUTIONS_DATA_SAVE_ON_SUCCESS=none
- EXECUTIONS_DATA_SAVE_ON_PROGRESS=true
- EXECUTIONS_DATA_SAVE_MANUAL_EXECUTIONS=false
- EXECUTIONS_DATA_PRUNE=true
- EXECUTIONS_DATA_MAX_AGE=60
- DB_SQLITE_VACUUM_ON_STARTUP=true
1 Like
4

Unfortunately I think I didn’t close the file etc. I don’t seem to be able to run this command. Is there a way to create a fresh db that will record executions?

5

https://docs.locusmap.eu/doku.php?id=manual:advanced:map_tools:corrupted_sqlite

Also would this be a possible fix? To copy the file to another PC and then try with these tools?

6

You shouldn’t need to copy to a different machine, SQLite has a built in recovery option so as long as you have the sqlite3 command installed on your machine you should be able to run it if not you can follow the steps you have found.

Depending on how many workflows you had running though and if you have a backup it might be quicker to stop n8n, delete the sqlite file then start n8n back up again and you will at least be back up and running and you will be able to make or import your workflows again.

1 Like
8

Workflow works but now that my database is corrupted I can’t run it because it gets stopped when it can’t write the execution log. Now I don’t have a backup file but is it possible to create a fresh database with same tables etc. I have the info of every other table and execution logs are just logs(i dont use them)
Thanks :slight_smile:

9

image
image1920×2560 972 KB

10

What I would do is export your workflows using either the n8n command line tool or from the UI then stop n8n, Delete the sqlite file, Start n8n which will make a new file then import them back in.

Make sure you set the options I mentioned earlier if you are using docker as well to prevent any similar issues from happening in the future.

11

We talked here about the Docker options :sweat_smile:
→ Workflow & Database Error - #13 by jon
I am a huuuge noob to this haha so I will now try to open database.sqlite with DB Browser. Export execution_entity and then I should delete the first file database.sqlite (size 17 GB) ?

After I start n8n again it will autocreate new database.sqlite which I can then open with UI program and import execution_entity?

Did I understand it correctly?

12

Don’t run anything with the db browser :slightly_smiling_face:

Do you really need the execution history?

13

I think I kinda messed up hah. I stopped the process. Opened the database.sqlite (17GB one ) and made a backup for:

  • whole database
  • execution history
  • database scheme without data

Now after that I deleted database.sqlite and ran the process again which created the new file but when I opened it in browser I don’t have my workflows anymore.

Now I opened the new db.sqlite and tried to import the old data from “whole database” and I get an error → sqlite import error in argument #8: not an error
TLDR I guess I deleted all my workflows and everything :cold_sweat: :sob: :nauseated_face:

However I have all the info from the last database → it is 12 rows from the photo applied before. I DON’T NEED EXEUCTION HISTORY.

What do I do @Jon ? :pray:

14

Okay I want to update you. I have created the new database with DB Browser from “whole database” backup file and it has all my data and it has 0 execution files. I wil try to start workflow now with this

15

Did you try exporting the workflows from the UI or using the n8n command line tool like I suggested? That would have saved you some time.