How to migrate / export / import the database

[chaptergy]

Unfortunately there is no built-in way to export or import data from nginxproxymanager. When backing up the /data directory, and mounting it in a new instance of npm, it does proxy everything as before, however the admin interface is empty, as the data used there is inside the database. (Unless you use SQLite, then your sqlite file could be inside the data directory). The database exports / imports here are in addition to backing up the /data and /etc/letsencrypt volumes!

Some steps require a database tool. Those are prefixed with [DBeaver] since I am using DBeaver, which is available for all platforms. I am working on Linux, if your npm instance is hosted on a machine with a different OS you might need to do other things than I have shown to open a terminal and run docker commands.

All commands are executed inside the folder containing the docker-compose.yml file, so when opening a terminal make sure to navigate to that folder first e.g. using the cd command, like cd /home/pi/nginxproxymanager.

Please note, that NPM no longer uses the MariaDB/MySQL by default. To backup and restore a SQLite database, simply copy the database file. To create a dump, use a database tool like DBeaver for export / imprt or refer to this comment to use the SQLite CLI inside the container.

🖥️➡️🖥️ Migrate to new npm instance #

⚠ Make sure both the old NPM instance and the new NPM instance are on the same version of NPM before doing this! You can see the version number on the login screen or at the very bottom of the screen within the admin panel.

1. Copy the /data and /etc/letsencrypt volumes to your new instance and mount them into your new instance. If you use the default config

   volumes:
     - ./data:/data
     - ./letsencrypt:/etc/letsencrypt

   they will be two folders data and letsencrypt next to your docker-compose.yml, which you can just copy over.
2. Export the database if you are using the MariaDB / MySQL database. If you are using the SQLite database it is likely included in your data folder, otherwise just copy the file over. Also possible using DBeaver.
3. Import the database to your new database container (if you are using the MariaDB / MySQL database). Also possible using DBeaver.

🥳 You have migrated the data to a new system

[DBeaver] Migrate MariaDB to a SQLite database #

Click to view steps

migrate-mariadb-to-sqlite.mp4

⚠ Make sure NPM was not updated since you last used the old database! If you are not sure, just run the NPM container once with the old database so it migrates the database to its current version and then switch the NPM container back to using SQLite.

⚠ This will delete all data from the new SQLite file and replace it with the data from the old database

1. Connect to your old database
2. Get the new SQLite file and add it to DBeaver
3. Expand the connection to your MariaDB in DBeaver by clicking on the small arrow next to it
4. Expand Databases, your specific database and Tables by once again clicking on the small arrow
5. Select all tables by clicking on the first table, pressing and holding the shift key and clicking on the last table
6. Right click on your selected tables
7. Click on Export Data
8. Select the target type Database (with the description Database table(s)) and click Next
9. At the very top click on the right click on the folder icon and select the connection to your SQLite file as set up in step 2
10. Click on Auto assign to automatically match the table names and click on next
11. Check the Truncate target table(s) before load checkbox and confirm the dialog popping up with Yes
12. Select the Replace method of INSERT OR REPLACE and click on Next
13. You can check if source and target names correctly match and then click on Start
14. Copy the SQLite file back into the NPM container as described at the end of the Download and connect to a SQLite database section

🥳 You have successfully migrated from using a MariaDB/MySQL database to using the SQLite database. You can now remove the old database container if you wish.

Export data from a MariaDB/MySQL database #

Click to view steps

⚠ This only exports the database! To make a full backup you need to additionally save the /data and /etc/letsencrypt folders, as described in step 1 of Migrate to new npm instance.

Replace the placeholders (remove the square brackets as well) and run

docker exec -it [db-container-name] mysqldump --user=[mysql-user] --password=[mysql-password] [mysql-db-name] -h 127.0.0.1 > npm-export.sql

Replace [db-container-name] with the docker container name of your database container. If you are using the official docker-compose it is just called db.
Replace [mysql-user], [mysql-password] and [mysql-db-name] with the values you have set for the variables DB_MYSQL_USER, DB_MYSQL_PASSWORD and DB_MYSQL_NAME in your docker-compose file.
You can also replace npm-export.sql with a path where to save the export to, but make sure it ends in a file with the extension .sql.

🥳 You have successfully exported the database! You should now find a .sql file inside the folder where you executed the command from or where you set the path to.

Import data to a MariaDB/MySQL database #

Click to view steps

⚠ Make sure to only import data exported from the same version of NPM! Otherwise things might break if the database structure changed between versions.

⚠ This only imports the database! To make restore a full backup you need to additionally restore the /data and /etc/letsencrypt folders.

Replace the placeholders (remove the square brackets as well) and run

docker exec -i [db-container-name] mysql --user=[mysql-user] --password=[mysql-password] [mysql-db-name] -h 127.0.0.1 < npm-export.sql

Replace [db-container-name] with the docker container name of your database container. If you are using the official docker-compose it is just called db.
Replace [mysql-user], [mysql-password] and [mysql-db-name] with the values you have set for the variables DB_MYSQL_USER, DB_MYSQL_PASSWORD and DB_MYSQL_NAME in your docker-compose file.
Replace npm-export.sql with a path where the export you'd like to import is located.

🥳 You have successfully imported the database!

[DBeaver] Connect to a MariaDB database #

Click to view steps

connect-to-mariadb.mp4

1. Edit the docker-compose file to expose the database port on your database container by adding

   ports:
     - '3306:3306'

2. Start / restart your database container
3. Add a new connection in DBeaver using the MySQL driver (it might have to download it)
4. Use host localhost if the container is running on the same machine as your DBeaver, or use the IP of the machine hosting the container otherwise. Use port 3306.
5. Use the database name from your docker-compose environment variable MYSQL_DATABASE
6. Use username and password from your docker-compose environment variables MYSQL_USER and MYSQL_PASSWORD
7. Click on Test Connection or Finish

🥳 You are now connected to the database!

[DBeaver] Export data from a MariaDB/MySQL database #

Click to view steps

export-mariadb.mp4

1. Connect to your database
2. Expand the connection to your MariaDB in DBeaver by clicking on the small arrow next to it
3. Expand Databases by once again clicking on the small arrow
4. Right click on your database (called npm by default unless changed through the MYSQL_DATABASE environment variable)
5. Hover over Tools
6. Click on Dump database
7. Make sure all tables and your database is selected. You don't need to select information_schema. Click next.
8. Leave everything as is and select an output folder so you find the file. Remember the file name pattern.
9. Click on Start and wait

🥳 You have successfully exported the database! You should now find a file inside the selected folder similar to the file name pattern.

[DBeaver] Import data into a MariaDB/MySQL database #

Click to view steps

import-mariadb.mp4

⚠ Make sure to only import data exported from the same version of NPM! Otherwise things might break if the database structure changed between versions.

1. Connect to your database
2. Expand the connection to your MariaDB in DBeaver by clicking on the small arrow next to it
3. Expand Databases by once again clicking on the small arrow
4. Right click on your database (called npm by default unless changed through the MYSQL_DATABASE environment variable)
5. Hover over Tools
6. Click on Restore database
7. Select an the input file by clicking on the folder icon and selecting a previously exported file
8. Click on Start and wait

🥳 You have successfully imported the database!

[DBeaver] Download and connect to a SQLite database #

Click to view steps

Unfortunately you can't connect to the database file inside the container directly, but instead you'll have to download the database file from the container and upload it again if you have changed anything.

download-edit-sqlite.mp4

1. Make sure in your docker-compose file you have set the DB_SQLITE_FILE environment variable, so NPM has generated a SQLite file.
2. Start your npm container
3. In a terminal run the command docker ps and copy the container id of your NPM container. You'll find it by searching for image jc21/nginx-proxy-manager and it should be a combination of numbers and letters just before it.
4. Run docker cp <container-id>:/path/as/set/in/DB_SQLITE_FILE path/where/to/save/to to copy the sqlite file to your local machine. The command will look something like this:

   docker cp 2c88aa4ba4d5:/data/database.sqlite /home/pi/database.sqlite

5. Copy this database.sqlite file to the machine with DBeaver on it if it is not the same machine
6. Add a new connection in DBeaver using the SQLite driver (it might have to download it)
7. Select the database.sqlite in the path field
8. Click on Test Connection or Finish

🥳 You are now connected to the database file! Please remember to copy the database file back into your container if you have made any changes. Just switch the last two parameters of the docker cp command around like this:

docker cp /home/pi/database.sqlite 2c88aa4ba4d5:/data/database.sqlite

[hockey6611]

Thank you very much for the thorough guide. I am sure that many users will find this increasingly useful once V3 is released, as it seems SQLite will be the only db used. I followed the guide and was successfully able to migrate from MariaDB to a SQLite. SQLite seems like a better option for this service, and I'm glad its moving in that direction.

Additionally, I was stuck on mariadb-aria:10.4 (due to innodb errors: v10.4.12 release notes). Migrating to SQLite still worked, and I imagine users who wish to remain on MariaDB but are similarly stuck on :10.4, could migrate following the import/export sections of this guide.

[hockey6611]

I was converting another instance and came across a frustrating issue. I was unable to login after conversion. No issues in logs, and my only error upon attempting to sign in was the message: No relevant user found.

After much pain and repeating the process countless times, I was able to determine that when this instance was setup the admin username contained a capital letter (e.g. [email protected]) The app appears to store the username with all lower case though ([email protected]), confirmed via the email listed under users. However, I didn't realize this and continued to successfully login with my original username ([email protected]), while using the external MariaDB container. Upon conversion to SQLite I was unable to login with ([email protected]). However, I ultimately discovered [email protected] works.

Hope this helps anyone else converting databases!

[jordiorlando]

Hi, thank you for this detailed guide! I run NPM on an Alpine VPS where I only have CLI access. I would like to migrate from MariaDB to SQLite without DBeaver, do you have any advice or things I should watch out for?

[chaptergy]

Migration should be pretty straight forward, no matter what database management tools you use. Exporting and importing the databases should be possible using the mysql and sqlite3 CLIs as well.

[instantdreams]

I have two machines that run Nginx Proxy Manager v2.9.14. I set up my configuration on one of them and have my router pointed at this one. I would like to duplicate / backup & restore / export & import / synchronise the settings from server 1 to server 2. This way if there is an issue with server 1 I can easily change my router forwarding to server 2.

If I perform the following steps, am I right in expecting everything to be backed up properly?

1. Copy /data folder contents to backup location
2. Copy /letsencrypt folder contents to backup location
3. Create a SQL dump of the database to backup location

For example, this would look something like:

cp "$npm_home/data/."        "$backup_target/data/."        --recursive
cp "$npm_home/letsencrypt/." "$backup_target/letsencrypt/." --recursive
docker exec -it db mysqldump --user=$mysql_user --password=$mysql_password $mysql_db -h 127.0.0.1 > "$backup_target/npm-export.sql"

If this is correct, I'm assuming performing the same steps in reverse will restore / import the configuration. Any confirmation would be much appreciated.

[chaptergy]

Yeah, that should work. For continuous synchronization you can also use rsync instead of cp.

[instantdreams]

A very good idea, rsync is next on my todo list.

I have an issue where appropriately set variables for this command:
docker exec -it $npm_db_name mysqldump --user=$mysql_user --password=$mysql_pass $mysql_db -h 127.0.0.1 > "$backup_target/npm-export.sql"

Which, if parsed with variables, looks like:
docker exec -it db mysqldump --user=npm --password=[password] npm -h 127.0.0.1 > "/media/Backups/nginx-proxy-manager/npm-export.sql"

Produce the following response:

$ bash /srv/bin/npm_backup.sh
[2022-01-06T11:51:15-07:00] Backing up /srv/nginx-proxy-manager/data/ to /media/Backups/nginx-proxy-manager/data/
[2022-01-06T11:51:15-07:00] Backing up /srv/nginx-proxy-manager/letsencrypt/ to /media/Backups/nginx-proxy-manager/letsencrypt/
[2022-01-06T11:51:15-07:00] Backing up db to /media/Backups/nginx-proxy-manager/npm-export.sql
Error: No such container: db

In my docker-compose.yml file I can see the container name is correct:

  db:
    image: 'jc21/mariadb-aria:latest'

My script changes the working directory to $npm_home which is set to /srv/nginx-proxy-manager in my example, so docker should be running this under the correct context.

Any ideas?

[instantdreams]

I did some more digging. Running docker ps on one of my pi-hole servers gives me this:

$ docker ps
IMAGE                             COMMAND             CREATED       STATUS                 NAMES
pihole/pihole:latest              "/s6-init"          9 hours ago   Up 9 hours (healthy)   pihole
jc21/mariadb-aria:latest          "/scripts/run.sh"   2 days ago    Up 2 days              nginx-proxy-manager_db_1
jc21/nginx-proxy-manager:latest   "/init"             2 days ago    Up 2 days              nginx-proxy-manager_app_1
lscr.io/linuxserver/duckdns       "/init"             2 days ago    Up 2 days              duckdns

Inspecting the container mounts for the app gives the expected volumes:

$ docker container inspect nginx-proxy-manager_app_1 -f '{{range .Mounts}}{{.Type}}:{{.Source}}:{{.Destination}}{{println}}{{ end }
}'
bind:/srv/nginx-proxy-manager/letsencrypt:/etc/letsencrypt
bind:/srv/nginx-proxy-manager/data:/data

The same for the db shows the database:

$ docker container inspect nginx-proxy-manager_db_1 -f '{{range .Mounts}}{{.Type}}:{{.Source}}:{{.Destination}}{{println}}{{ end }}
'
bind:/srv/nginx-proxy-manager/data/mysql:/var/lib/mysql

I'll try my script with nginx-proxy-manager_db_1... and it worked!

$ bash npm_backup.sh
[2022-01-06T15:59:52-07:00] Backing up /srv/nginx-proxy-manager/data/ to /media/Backups/nginx-proxy-manager/data/
[2022-01-06T15:59:52-07:00] Backing up /srv/nginx-proxy-manager/letsencrypt/ to /media/Backups/nginx-proxy-manager/letsencrypt/
[2022-01-06T15:59:52-07:00] Backing up nginx-proxy-manager_db_1 to /media/Backups/nginx-proxy-manager/npm-export.sql

Excellent. Now I'll write the restore script and test it on the other server :)

[instantdreams]

That worked! I was able to log into my second server with the same credentials as the first server, and all settings were duplicated.

I'll make the script more robust (automated creation of target folders, more error checks) and look into the use of rsync, but for now this will work a treat.

[instantdreams]

For reference I have created gist versions of my scripts to show how I implemented the database dump and restore.

• npm_backup.sh
• npm_restore.sh
• .env.example

Place these in a suitable location (I use /srv/bin/) and adjust the values accordingly. Create a copy of .env.example called .env and edit the values to reflect your implementation. Run using sudo bash npm_backup.sh and sudo bash npm_restore.sh. There is currently no logging to /var/log/ or any error checking, but it will do what is needed.

[aarste]

This guide had me stumped for a while because the mysqldump command was not found, it turns out NPM (as of Nov 2021) doesn't use mysql anymore, I only found this out by chance when I looked at one of the other 'Show and tell' articles on here.

To back up the sqlite database, I use:
sqlite3 /home/aarste/docker_volumes/npm/data/database.sqlite ".backup '/home/aarste/docker_volumes/npm/backup/npm-db-$(date '+%d-%m-%Y-%H-%M').sqlite'"

[asdfcxv324]

how to fix?

OCI runtime exec failed: exec failed: container_linux.go:380: starting container process caused: exec: "mysqldump": executable file not found in $PATH: unknown

[onlydev]

I was able to resolve it by doing the following.

docker exec -i [container_name] apt-get update
docker exec -i [container_name] apt-get install -y mysql-client

docker exec -i [conatiner_name] mysqldump --column-statistics=0 --user=[user_id] --password=[user_password] --databases npm > npm-backup.sql

[sadrian80]

If you encounter issues after migration with writing to the DB (like, when altering proxy hosts for example), the SQLite DB table properties are most likely wrong. For me, this happened when migrating from MariaDB to SQLite via DBeaver as per above tutorial.

I solved this by using the database.sqlite from a freshly deployed instance and leaving "Truncate tables" unchecked during migration. This makes sure, that the table properties won't get overwritten.

See also here: #1643

[andzejsp]

Thanks for the tutorial but im running into issues where i cant get letsencrypt certificates no more, the ui shows internal error, and the docker logs shows:

FROM
bash: line 1:   126 Trace/breakpoint trap   (core dumped) node --abort_on_uncaught_exception --max_old_space_size=250 index.js
❯ Starting backend ...
[4/5/2023] [4:03:39 PM] [Global   ] › ℹ  info      Using MySQL configuration
[4/5/2023] [4:03:40 PM] [Migrate  ] › ℹ  info      Current database version: 20211108145214
[4/5/2023] [4:03:40 PM] [Setup    ] › ℹ  info      Logrotate Timer initialized
[4/5/2023] [4:03:40 PM] [Setup    ] › ℹ  info      Logrotate completed.
[4/5/2023] [4:03:40 PM] [IP Ranges] › ℹ  info      Fetching IP Ranges from online services...
[4/5/2023] [4:03:40 PM] [IP Ranges] › ℹ  info      Fetching https://ip-ranges.amazonaws.com/ip-ranges.json
[4/5/2023] [4:03:40 PM] [IP Ranges] › ℹ  info      Fetching https://www.cloudflare.com/ips-v4
[4/5/2023] [4:03:40 PM] [IP Ranges] › ℹ  info      Fetching https://www.cloudflare.com/ips-v6
[4/5/2023] [4:03:40 PM] [SSL      ] › ℹ  info      Let's Encrypt Renewal Timer initialized
[4/5/2023] [4:03:40 PM] [SSL      ] › ℹ  info      Renewing SSL certs close to expiry...
[4/5/2023] [4:03:40 PM] [IP Ranges] › ℹ  info      IP Ranges Renewal Timer initialized
[4/5/2023] [4:03:40 PM] [Global   ] › ℹ  info      Backend PID 904 listening on port 3000 ...
[4/5/2023] [4:03:41 PM] [Nginx    ] › ℹ  info      Reloading Nginx
[4/5/2023] [4:03:41 PM] [SSL      ] › ℹ  info      Renew Complete
[4/5/2023] [4:03:41 PM] [SSL      ] › ✖  error     Certificate is not valid (Command failed: openssl x509 -in /etc/letsencrypt/live/npm-8/fullchain.pem -subject -noout
Can't open /etc/letsencrypt/live/npm-8/fullchain.pem for reading, No such file or directory
140114550527168:error:02001002:system library:fopen:No such file or directory:../crypto/bio/bss_file.c:69:fopen('/etc/letsencrypt/live/npm-8/fullchain.pem','r')
140114550527168:error:2006D080:BIO routines:BIO_new_file:no such file:../crypto/bio/bss_file.c:76:
unable to load certificate
)
[4/5/2023] [4:03:41 PM] [SSL      ] › ✖  error     Certificate is not valid (Command failed: openssl x509 -in /etc/letsencrypt/live/npm-7/fullchain.pem -subject -noout
Can't open /etc/letsencrypt/live/npm-7/fullchain.pem for reading, No such file or directory
139666895643840:error:02001002:system library:fopen:No such file or directory:../crypto/bio/bss_file.c:69:fopen('/etc/letsencrypt/live/npm-7/fullchain.pem','r')
139666895643840:error:2006D080:BIO routines:BIO_new_file:no such file:../crypto/bio/bss_file.c:76:
unable to load certificate
)

not sure whats wrong here

[Krojack]

I just converted over to SqLite from Mariadb/MySQL. This is how I did it. Keep in mind you need access to a Linux command line that has mysqldump and sqlite3 commands.

First, I used the script mysql2sqlite found here.

1. Create a dump using mysqldump.
   mysqldump --skip-extended-insert --compact -h <DB_HOST> -u<DB_USER> -p nginx_proxy > dump_mysql.sql

2. Use the mysql2sqlite script to convert that to a sqlite DB
   ./mysql2sqlite dump_mysql.sql | sqlite3 database.sqlite

3. Stop the npm container if it's running.

4. Copy the database.sqlite file into your container's persistent storage locations data directory.

5. Update my docker-compose.yaml and remove (or comment out) the DB_MYSQL_* environment entries then add the following environment entry
   DB_SQLITE_FILE: "/data/database.sqlite"

6. Start the container back up.

7. Check startup logs for the following:
   Using Sqlite: /data/database.sqlite

8. Check admin to see if everything is work!

[FroggMaster]

@Krojack
I appreciate the time you've taken to write your steps up; Though I had to make some minor adjustments to the provided commands it did save me some time. I'm going to post my adjusted instructions in case anyone may in-turn find them as useful as I found your post. :)

Some of the below was in response to another comment, but the steps remain consistent:
I ended up converting from a MariaDB to SQLite and this resolved not being able to logon, as well as other startup errors I was facing due to the MariaDB.

So far I've found that the docker configuration is easier and it's an overall easier DB format to manage.

1. First we're going to prepare the Pre-Requisites to be able to follow these steps. You require the following packages installed:
   mysqldump, sqlite3 and the script mysql2sqlite
   a) If you're on FreeBSD the packages can be installed VIA:
   pkg install mysqldump sqlite3
   b) If you're on Ubuntu/Debian the packages can be installed VIA:
   sudo apt-get install mysql-client sqlite3

2. Download the mysql2sqlite script.
   git clone https://github.com/dumblob/mysql2sqlite.git

3. Export your MariaDB MYSQL Database from your NPM DB Container
   docker exec -it [db-container-name] mysqldump --user=[mysql-user] --password=[mysql-password] [mysql-db-name] -h 127.0.0.1 > npm-export.sql

4. Convert the .SQL file to a .SQLite file
   ./mysql2sqlite npm-export.sql | sqlite3 database.sqlite

5. Stop the NPM / DB Containers if they're running.
   docker-compose stop [container-name]

6. Copy the database.sqlite file into your container's persistent storage location. For me and most that followed the default configurations this will be the data directory.

7. Update the docker-compose.yaml and remove every DB_MYSQL_* environment entries.

8. OPTIONAL STEP: Update the docker-compose.yaml and add the following environment entry
   DB_SQLITE_FILE: "/data/database.sqlite"

Note: This step is OPTIONAL as /data/database.sqlite is the default location NPM will read the database file from. You only really need this line in the YAML if you want to relocate the database.sqlite file. If so, you can adjust the above path to something else.

9. Start your NPM container
   docker-compose up -d

10. Check the docker Logs for the following line to validate the SQLlite file is being used. Using Sqlite: /data/database.sqlite This can be done easily with the following command:
    docker compose logs|grep -i database.sqlite

11. Rejoice everything should hopefully be working now.

[markusbaker]

Very helpful thread, many thanks for the details of dumping the SQL database and migrating to SQLite.

@FroggMaster On Ubuntu, the mysqldump command is associated with the repository package mysql-client, so users can install mysqldump using
sudo apt-get install mysql-client

[ThRPctmylSgltwH]

I have followed the instructions above to migrate from a mysql database to an sqlite database. After restarting the container, I receive the following error for both the SSL Certificates tab and the Proxy Hosts tab - "domain_names.map is not a function" with a box to "Try again". All is functioning properly and I can connect with all my services but I cannot edit proxy hosts nor SSL Certificates. Is this an issue that others see? Can it be related to Docker 26.0 being released which has also caused issues with Portainer? Any suggestions would be highly appreciated.

[andzejsp]

look into using caddy for proxy management. ever since my ssl certs didnt upgrade with this app due to some kind of outdated package, and looking that development on this has stopped, i moved to caddy and never looked back. Certs auto renews, and i dont have to manage nothing. Set it once and forget about it.

[ThRPctmylSgltwH]

I was able to solve this myself. The issue is that the export of the mysql database using the command docker exec -it [db-container-name] mysqldump --user=[mysql-user] --password=[mysql-password] [mysql-db-name] -h 127.0.0.1 > npm-export.sql produces a file where quotes are escaped so " becomes \". When I imported this data, it was not in the correct format in the sqlite database. Additionally, not all datetime values were imported correctly. The solution was to have the original database available on port 3306 and connect to it with DBeaver and then advance-copy and advance-paste the data from the mysql database to the sqlite database and ensure that each value remained identical. The frontend display works perfectly now. In the end, I tried to avoid mapping the database to 3306 and connecting directly and this is what caused the issue. I hope that this helps if anyone else follows the same path.

[MatousVondrejka]

I don't know if anybody need to know, but if you migrate from docker to baremetal or lxc, you need to do this:

/data/nginx/proxy_host# sed -i "s#conf.d#/etc/nginx/conf.d#g" *

Or service keep crashing.

[scyllaL]

Thanks a lot, thats exactly what I needed

[antoninchadima]

I migrated from SQLite to MariaDB (I thought it would be a more enterprise setup).
I have used Dbeaver and this tutorial and everything is working great
except adding any new record to npm (and deleting an old record).
I get this error:

[9/27/2024] [11:01:02 AM] [Express ] › ⚠ warning insert into proxy_host (access_list_id, advanced_config, allow_websocket_upgrade, block_exploits, caching_enabled, certificate_id, created_on, domain_names, forward_host, forward_port, forward_scheme, hsts_enabled, hsts_subdomains, http2_support, locations, meta, modified_on, owner_user_id, ssl_forced) values (0, '', true, true, true, 0, NOW(), '[\"npm.ucl.cas.cz\"]', 'npm.localdomain', 81, 'http', false, false, false, '[]', '{\"letsencrypt_agree\":false,\"dns_challenge\":false}', NOW(), 1, false) - ER_NO_DEFAULT_FOR_FIELD: Field 'id' doesn't have a default value

Any suggestion?

[ThRPctmylSgltwH]

It seems that the field id should be self incrementing and that is defined as a property of the field to be a self incrementing key field in the table.

…

________________________________ From: Antonín Chadima ***@***.***> Sent: Friday, September 27, 2024 3:22:32 PM To: NginxProxyManager/nginx-proxy-manager ***@***.***> Cc: Pat ***@***.***>; Comment ***@***.***> Subject: Re: [NginxProxyManager/nginx-proxy-manager] How to migrate / export / import the database (Discussion #1529) I migrated from SQLite to MariaDB (I thought it would be a more enterprise setup). I have used Dbeaver and this tutorial and everything is working great except adding any new record to npm (and deleting an old record). I get this error: [9/27/2024] [11:01:02 AM] [Express ] › ⚠ warning insert into proxy_host (access_list_id, advanced_config, allow_websocket_upgrade, block_exploits, caching_enabled, certificate_id, created_on, domain_names, forward_host, forward_port, forward_scheme, hsts_enabled, hsts_subdomains, http2_support, locations, meta, modified_on, owner_user_id, ssl_forced) values (0, '', true, true, true, 0, NOW(), '[\"npm.ucl.cas.cz\"]', 'npm.localdomain', 81, 'http', false, false, false, '[]', '{\"letsencrypt_agree\":false,\"dns_challenge\":false}', NOW(), 1, false) - ER_NO_DEFAULT_FOR_FIELD: Field 'id' doesn't have a default value Any suggestion? — Reply to this email directly, view it on GitHub<#1529 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AUXEO2VNS4LE7FAO5N25XRTZYVLZRAVCNFSM5G2W6JXKU5DIOJSWCZC7NNSXTOSENFZWG5LTONUW63SDN5WW2ZLOOQ5TCMBXG42TQMJS>. You are receiving this because you commented.Message ID: ***@***.***>

[antoninchadima]

the error was caused by a stupid mistake:
you have to first inicialize the database by dry running the npm docker container firts
and then it is ok to migrate the database from sqlite to mariadb with truncate enabled

but then you get a new error:

Error occurred during batch insert
(you can disable batch insert in order to skip particular rows).

Reason:
Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

which is caused by the table migrations and the column migation_time

should i convert this column to the desired format? or?
what is the purpose of the table migrations?

[antoninchadima]

ok - i will stick with sqlite, after reading this discussion it seems wrong to go for mariadb/mysql...

[ThRPctmylSgltwH]

Sqllite is perfectly suited for this application.

…

________________________________ From: Antonín Chadima ***@***.***> Sent: Monday, September 30, 2024 7:51:45 AM To: NginxProxyManager/nginx-proxy-manager ***@***.***> Cc: Pat ***@***.***>; Comment ***@***.***> Subject: Re: [NginxProxyManager/nginx-proxy-manager] How to migrate / export / import the database (Discussion #1529) ok - i will stick with sqlite, after reading this discussion it seems wrong to go for mariadb/mysql... — Reply to this email directly, view it on GitHub<#1529 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AUXEO2TLCUHD54DQWYHTUQDZZDYIDAVCNFSM5G2W6JXKU5DIOJSWCZC7NNSXTOSENFZWG5LTONUW63SDN5WW2ZLOOQ5TCMBXHE2DCMZQ>. You are receiving this because you commented.Message ID: ***@***.***>