search

YunoHost-Apps / n8n_ynh

n8n package for YunoHost
https://n8n.io/
GNU General Public License v3.0
14 stars 19 forks source link

n8n database.sqlite is more than 40gb #18

Closed theRAGEhero closed 2 years ago

theRAGEhero commented 2 years ago

Hello, my database.sqlite is very big and I just have some RSS feed bot on n8n.

image

I'm using YH 4.3.6.2 and the last version of n8n avalaible for YH.

I think it is related to this thread. https://community.n8n.io/t/why-is-my-database-so-big/7981/17

Would it be possible to fix it from the app? Thank you

ericgaspar commented 2 years ago

sqlite? I thought we were using mysql as database... I added those extra env settings in this branch: https://github.com/YunoHost-Apps/n8n_ynh/tree/database

fflorent commented 2 years ago

Indeed, it's weird. The extra settings does not solve the issue, the database is still empty:

MariaDB [n8n]> show tables;
Empty set (0.001 sec)
theRAGEhero commented 2 years ago

Also my n8n db on Mysql is empty while the database.sqlite now is 50gb!

ericgaspar commented 2 years ago

n8n seems to be using sqlite by default...

Edit: I added the missing DB type in the .env file #21.

theRAGEhero commented 2 years ago

Should I save my apps, uninstall and install the new one? It's becoming huge!

fflorent commented 2 years ago

Do you have many workflows? If not, I guess that is the best choice (EDIT: in case you cannot wait).

theRAGEhero commented 2 years ago

Do you have many workflows? If not, I guess that is the best choice (EDIT: in case you cannot wait).

Just some. I removed it. Should now be working on mysql if I reinstall it?

fflorent commented 2 years ago

When the CI tell us that's OK, we should be good to merge: https://github.com/YunoHost-Apps/n8n_ynh/pull/21#issuecomment-1082344401

Florent

theRAGEhero commented 2 years ago

I installed the testing version and now the n8n mysql db is populated, but it doesn't allow me to log in.

edit. Now it's working.

The first time I tried with sudo yunohost app install https://github.com/YunoHost-Apps/n8n_ynh/tree/testing --debug and the second time from the gui.

xplosionmind commented 2 years ago

As noted in the log file itself, I should write in this issue that I am experiencing a problem while upgrading the app.

Here is the full log

What should I do?

fflorent commented 2 years ago

If you're OK keeping only the workflow and credential

You may try to export the workflow and credentials using the proposed solution here: https://community.n8n.io/t/could-i-transfer-it-to-mysql-database/7597/5

If you want to keep more than that (untested)

If you want to keep absolutely everithing, I may do the following steps (which are untested, it may not work):

  1. Backup somewhere the n8n package;
  2. Export the tables list on your database;
  3. Install the package sqlite3-to-mysql: pip3 install sqlite3-to-mysql;
  4. For each table, run this command: https://github.com/techouse/sqlite3-to-mysql/issues/40#issuecomment-1083195399
  5. Ensure you have your backup of step 1, then remove the n8n package and install the latest version
  6. Import the tables values (you may disable the foreign key constraint)

If it does not work, remove n8n and restore the backup of the step 1 using these commands (adapt the cp command): yunohost app remove n8n && cp /path/to/n8n_backup /home/yunohost.backup/archives/ && yunohost backup restore

Thanks in advance If you can give feedback whether that works!