I want to set up n8n to use MySQL instead of SQLite but can’t get it to work. n8n launches with no errors but it is still using SQLite despite the fact that I have setup the MySQL environment vars. I’ve also updated my config file with the appropriate JSON strings to use MySQL and it still loads up SQLite. I’ve tried deleting the SQLite database and it recreates it. I’ve created an empty n8n MySQL database and tried the root user/pass and I created a new user/pass and gave it full permissions to the n8n database and tried both in my testing and neither worked (no tables were added to the n8n database). I tested logging in mysql via command line and bother user/pass’s were able to access the n8n database and create a dummy table.
Are there any mysql modules or other requirement needs for n8n to be able to use MySQL?
n8n version:
n8n/0.226.2 linux-x64 node-v16.20.0
Running n8n via (Docker, npm, n8n cloud, desktop app):
As an app in Linux (typing “n8n” and pressing ENTER at the command prompt).
^^ Note I’ve tried “n8n” and “n8n start” to launch it at the end of the script.
^^ Also tried “0.0.0.0” and the internal IP address for DB_MYSQLDB_HOST and neither helped.
I’ve tried changing the owner and group to “root” (which by default was my username before) and that didn’t help. I also changed permissions to 777 to give read/write/execute to everyone to see if that would help and it did not.
I’ve searched n8n documentation and googled it and can’t figure out why it’s not working. Any and all help is very much welcome! And thanks in advance for taking the time to read this and possibly responding!
If you are running n8n from npm that should work, I take it to check if it is is using the database you are checking to see if it has created the tables?
I am not sure why the env options wouldn’t be picked up so a bit more information about your setup would be handy, It may also be worth noting that MySQL / MariaDB will no longer be supported when 1.0 is released soon so it might be worth using Postgres now to save on a migration later.
So after your revelation @Jon that MySQL won’t be supported in v1.0 I’m switching to using Postgres and having the same issue so I have to be doing something wrong! My colleague spun up a new Ubuntu Linux server and we installed Postgres, Curl, and n8n. I logged in to Postgres and created a test table in my n8n database just fine with the default credentials. I’ve created a bash file with my export commands in it using the default credentials and it still doesn’t work. I’ve quintuple checked everything and don’t see anything wrong. Here’s a copy of my bash file:
I’ve tried removing all exports except the postgres ones and still nothing. By nothing I mean I type in the bash filename “./start.sh” and it sits there for 5-10 seconds and comes back to the prompt. If I start it with “n8n” at the command prompt it does start up but still uses the default sqlite db (I’ve checked my postgres db and only see my test table). I’ve deleted the sqlite db and the next time I run n8n it recreates it rather than using the settings from the EXPORT commands.
A config file is created in “/home/user/.n8n” with the encryption key and nothing else. I tried adding the equivalent JSON commands for the export variables in the config file and that didn’t work either.
I suspect the env options are not being correctly picked up although the script should still start n8n.
If it is going back to a prompt it could be an error on the startup as well, have you tried replacing the n8n line at the bottom to n8n start to see if that helps?
It could be worth trying a smaller test to start to rule things out as well, what happens if you just set WEBHOOK_URL in the same way does that work?
Have you also thought about using docker instead it makes the process a lot easier and it is our recommend approach.
Yes, I’ve tried several variations of SET commands and “n8n” vs “n8n start” and typing (copy/paste) the set commands at the command prompt and then running “n8n” and “n8n start” and all combinations in between to no avail.
I tried downgrading from PostgreSQL 14 to 12 and that didn’t work. My network admin uses a different container system (LXC) and not Docker.
I haven’t seen the WEBHOOK_URL parameter so I will investigate that to see if it will help.
It seems this should be easier. Is there any step-by-step instructions on how to install n8n to Linux using Postgres? I’ve searched and not found anything yet. I’m wondering if I’m doing things in order correctly and supplying the correct parameters and/or if I need additional software or Postgres addons or something.
Thanks again for your help!
(edit) Also, for this server my network admin created a new Linux “box” via LXC and then installs curl, then the “node red pi” script (I think to get a compatible version of npm), then n8n. He says n8n won’t load with newer versions of npm/python and that’s why he does it this way.
We don’t really have a step by step guide for running it in the way you are doing it but I can have a play in the morning and see what I can come up.
Assuming you are running node 16 it will be fine, I have seen issues in the past where using the export option to set envs doesn’t always work but after a couple of goes it eventually get there.
What does the console log actually look like when you are running the script does it output anything at all and are you planning to launch n8n through systemd or do you have another plan for that like pm2?
I have just set up an Ubuntu 22.04.2 VM with Node 16.20.1 installed through NVM, To get n8n running I first ran npm install -g n8n which downloaded n8n from npm then I used a similar version of your start script.
I then did a quick chmod +x start.sh and ran ./start.sh this then displayed the n8n ready message and I took a look in the database I created and the tables are there and are good to go. What this does mean though is I am not sure why it isn’t working for you but I suspect it is environmental, Maybe it is not able to connect to the database with the options provided?
Yes, that was from my script output. Running the script I’ve gotten nothing and I’ve got the above output. I’ve tried tweaking the parameters (like the db username/password/etc) and because of that I’ve gotten both results.
When you ran your script above did you delete the sqlite db first? Do you have a config file in the “.n8n” folder and if so does it have just your encryptionKey value pair?
Did you have to create a linux user “installtest” or was that all internal to Postgres? I tried using the “postgres” user with and without a password. I’ve also created a new user and assigned them “admin” access to the “n8n” db that I created. What was your process for the whole Postgres side of things?
I didn’t delete the sqlite database as I tend to make sure it works first then I remove it, With the .n8n folder that will always exist with a config file that only contains the encryption key so nothing to worry about with that.
installtest is the name of the databse user, database and password that exists in Postgres, To create the user in Postgres I just followed the normal postgres process to make them.
CREATE DATABASE installtest;
CREATE USER installtest WITH ENCRYPTED PASSWORD 'installtest';
GRANT ALL PRIVILEGES ON DATABASE installtest TO installtest;
This was connecting to database that is running on the same machine I was running the start script from.
It’s working! I have my 20 new n8n tables in my Postgres database!
I deleted the test user and database that I had created and then used your CREATE/GRANT statements above to create a new test user and database. Then I copied your bash script lines overwriting mine and replacing the PORT, DATABASE, USER and PASSWORD with mine and it worked!
Since the script seems pretty much identical it has to be something in the process I used to create the database and user initially. I initially used the postgres user that was created by default when I installed Postgres and when that didn’t work I created a new user and granted it access to the database and that didn’t work either.
Thanks for all your hard work and informative responses @Jon !
