Upgrade to 2.10.1: Error create table `migrations` (permission denied)

[dz-igner]

Checklist

• Have you pulled and found the error with jc21/nginx-proxy-manager:latest docker image?
  • Yes
• Are you sure you're not using someone else's docker image?
  • Yes
• Have you searched for similar issues (both open and closed)?
  • Yes

Describe the bug

After update to version 2.10.1 I got the following error

[Global ] › ✖ error create table migrations (id int unsigned not null auto_increment primary key, name varchar(255), batch int, migration_time timestamp) - ER_CANT_CREATE_TABLE: Can't create table npm.migrations (errno: 13 "Permission denied")

Nginx Proxy Manager Version

2.10.1

Additional context downgrade to 2.9.22

[Trolann]

Duplicate of #2771

[remisharrock]

check the owner (using ls -ln) of the mysql folder located inside the data folder, is it 100 101 like so ?

drwxr-xr-x 5 100 101 4096 29 mars 18:38 mysql

if not, you can change it using

sudo chown -R 100:101 mysql/

[vawaver]

I checked the situation and sent screenshot. Can you help me how to solve it in my case?

[remisharrock]

try ls -ln not ls-lh you will see numerical id instead of systemd-network and systemd-journal
then just do sudo chown -R 100:101 mysql/ by replacing 100 and 101 by the numerical id that appear instead of systemd-network and systemd-journal

[vawaver]

I am sending new screenshot. ls -ln I did not anything else... no sudo chown...yet

[dz-igner]

Same here

[remisharrock]

check also that inside data/mysql/npm you might want to do this as a superuser (using sudo su) if some files or folders exist , their permissions are like so

-rw-r----- 1 100 101 954 29 mars 17:48 migrations_lock.frm -rw-r----- 1 100 101 16384 29 mars 19:18 migrations_lock.MAD -rw-r----- 1 100 101 16384 29 mars 19:18 migrations_lock.MAI -rw-r----- 1 100 101 16384 29 mars 17:48 migrations.MAD -rw-r----- 1 100 101 16384 29 mars 17:48 migrations.MAI

especially -rw-r----- 1 100 101 at the begining

if not you can go with my previous

sudo chown -R 100:101 mysql/

otherwise it might be a problem with your password in the docker compose file.

[vawaver]

I am sending another info. I am not IT pro so I am happy for your patience.

[remisharrock]

you have to use sudo su to become superuser then navigate to the folders as describe above

[vawaver]

Please check my log. mysql_permission.txt

[remisharrock]

this looks OK to me, so it might be a problem with your password in the docker compose file...

[dz-igner]

check also that inside data/mysql/npm you might want to do this as a superuser (using sudo su) if some files or folders exist , their permissions are like so

-rw-r----- 1 100 101 954 29 mars 17:48 migrations_lock.frm -rw-r----- 1 100 101 16384 29 mars 19:18 migrations_lock.MAD -rw-r----- 1 100 101 16384 29 mars 19:18 migrations_lock.MAI -rw-r----- 1 100 101 16384 29 mars 17:48 migrations.MAD -rw-r----- 1 100 101 16384 29 mars 17:48 migrations.MAI

especially -rw-r----- 1 100 101 at the begining

if not you can go with my previous

sudo chown -R 100:101 mysql/

otherwise it might be a problem with your password in the docker compose file.

Permissions in folder /data/mysql/npm

What do you mean with "problem with your pssword in the docker-compose.yml file?

[Shineson1001]

Hi, i have the same problem ... NPM Version 2.9.22 works fine. The upgrade/update to NPM Version 2.10.x does not work. The Error is: error create table 'migrations' ('id' int unsigned not null auto_increment primary key, 'name' varchar(255), 'batch' int, 'migration_time' timestamp) - ER_CANT_CREATE_TABLE: Can't create table 'npm'.'migrations' (errno: 13 "Permission denied")

The table 'migrations' already exists ... so why the update tries to "re-create" it?

I think the problem occurs only in a mysql environment ... sqlite works fine with the NPM Version 2.10.x

[vawaver]

I found out that I use special characters in docker-compose.yml file . So I changed special characters in passwords for normal. After I run docker again, I am not able to login to NGINX admin interface again. Sending logs as attachment.

_nginx-new_app_1_logs01.txt

[Shineson1001]

@vawaver .. use your old password with the special characters again. I can not imagine it is a password issue. If you want to change the password, you have to change it in the database and in the docker-compose.yml file.

[vawaver]

@vawaver .. use your old password with the special characters again. I can not imagine it is a password issue. If you want to change the password, you have to change it in the database and in the docker-compose.yml file.

I changed special characters back in docker-compose.yml and my NGINX to v2.9.22. I hope the issue will be fixed soon because as I see it is not my problem only.

[Shineson1001]

Hi, the problem is the ownership of the mysql data folder and files ..

it seems, that the new NPM Version 2.10.x set the ownership to 911 to the mysql data. But in the mysql container is no user with this number.

docker exec -ti nginx-proxy-manager-db-1 /bin/sh
/ # cd /var/lib/mysql
/var/lib/mysql # ls -l
total 6472
-rw-rw----    1 911      911              2 Mar 29 18:00 0db1d4585602.pid
-rw-rw----    1 911      911              2 Mar 29 19:12 337f4c4f096e.pid
-rw-rw----    1 911      911              2 Mar 29 17:45 6e40638de91d.pid
-rw-rw----    1 911      911              2 Mar 29 19:21 97e33b4c9638.pid
-rw-rw----    1 911      911              2 Mar 29 19:13 9c71d13d0556.pid
-rw-rw----    1 911      911        6578176 Mar 29 19:20 aria_log.00000001
-rw-rw----    1 911      911             52 Mar 29 19:20 aria_log_control
-rw-rw----    1 911      911              2 Mar 29 17:40 b92484905a71.pid
-rw-rw----    1 911      911              2 Mar 29 17:49 d16f212e201b.pid
-rw-rw----    1 911      911              0 Jul 14  2022 multi-master.info
drwx------    2 911      911           4096 Mar 29 19:16 mysql
drwx------    2 911      911           4096 Jul 14  2022 npm
drwx------    2 911      911           4096 Jul 14  2022 performance_schema

you have to change it to the user and group 'mysql'

docker exec -ti nginx-proxy-manager-db-1 /bin/sh
cd /var/lib/mysql
chown -R mysql:mysql *

But this is a temporary workaround. Every "stack" restart change the ownership back to 911. Use the NPM Version 2.9.22 ... NPM Version 2.10.0 and 2.10.1 does not work with mysql

[vawaver]

@Shineson1001 If I get you right, we have to wait for the new version of NGINX that fix this issue. Until then we need to use v2.9.22

[Shineson1001]

@vawaver The problem occurs, if you use the settings from the Setup Instructions - Using MySQL / MariaDB Database If the mapped data folder of the 'app' container contains the mapped data folder of the the 'db' container. So the 'app' container has access to the database files and it seemes that the start process of the 'app' container change the ownership for all files and folders, inclusiv the database files.

You have to separate the mapped folders for each container. Example:

version: "3"
services:
  app:
    image: jc21/nginx-proxy-manager:latest
    ...
    ...
    volumes:
      - ./data/app:/data
      - ./data/letsencrypt:/etc/letsencrypt
    ...
    ...
  db:
    image: 'jc21/mariadb-aria:latest'
    ...
    ...
    volumes:
      - ./data/mysql:/var/lib/mysql

You have to move some folders/files to the new 'app' subdirectory: access, custom_ssl, logs, nginx, keys.json

[TritonB7]

@Shineson1001 Thank you, I was using the same volume structure as mentioned with the issue and moved all app volume related files and folders that were in ./data (except for the mysql/ folder) to a subfolder ./data/app/ instead. Afterwards, I redeployed without any permission related issues.

[vawaver]

@Shineson1001 What kind of magic do you use? I did as you mentioned above and IT WORKS!!! I wish you all the best!

[s-wighton]

Thanks @Shineson1001 as suggested, moving all files / folders except mysql to a new app folder inside data

And updating the docker compose file worked.

I had to re-apply ownership to the mysql folder as suggsted by @remisharrock

sudo chown -R 100:101 mysql/

Back up and running for now.

[yms2772]

check the owner (using ls -ln) of the mysql folder located inside the data folder, is it 100 101 like so ?

drwxr-xr-x 5 100 101 4096 29 mars 18:38 mysql

if not, you can change it using

sudo chown -R 100:101 mysql/

It works for me!

[tekjockey]

@vawaver The problem occurs, if you use the settings from the Setup Instructions - Using MySQL / MariaDB Database If the mapped data folder of the 'app' container contains the mapped data folder of the the 'db' container. So the 'app' container has access to the database files and it seemes that the start process of the 'app' container change the ownership for all files and folders, inclusiv the database files.

You have to separate the mapped folders for each container. Example:

version: "3"
services:
  app:
    image: jc21/nginx-proxy-manager:latest
    ...
    ...
    volumes:
      - ./data/app:/data
      - ./data/letsencrypt:/etc/letsencrypt
    ...
    ...
  db:
    image: 'jc21/mariadb-aria:latest'
    ...
    ...
    volumes:
      - ./data/mysql:/var/lib/mysql

You have to move some folders/files to the new 'app' subdirectory: access, custom_ssl, logs, nginx, keys.json

Confirmed working for me. Thanks!

[azchoi]

Issue is solved @2.10.2. Many Thanks!!!

[marcosvfc]

I make some tests to confirm what I did. For me, the problem was that the environment variable MYSQL_PASSWORD for db is protected by single quotes (i believe that is the correct way?) and my password contains special characters. For the environment variable DB_MYSQL_PASSWORD, the password was protected by double quotes. Changed that for single quotes, and nginx can login correct in mysql. That's work for me. (sorry about my poor English)

[AGauseTT]

Been playing around with this issue for a few hours, its definitely and issue with permissions regarding the database user and/or the tables. I'm able to change the permissions on the npm user to get it to work sometimes, but the fix keeps being undone every time i tear the container down and relaunch it via terraform.

To be clear, the container works fine for "Clean" Deployments. But for those who have data, domains, and/or ssl certs they want to carry over the newer version, you are currently out of luck.

Im going to rock 2.9.22 for now until this issue gets fixed, because I have over 30 domains locally I am trying to keep in tact.

[Bolex80]

Changing the app folders and moving the old files there worked for me. I really thought I would have to redeploy and build it all again from scratch. Saved my life

Thank you so much...

[g1uliano]

Thank you very much @Shineson1001, changing the apps folders solved the issue for me too.

[AGauseTT]

Ok I think I got it now,

the issue is the permissions on the mysql folder directory

"[insert your path here]:/var/lib/mysql"

for some reason the "Other User" Value needs to be set to read,write,execute

easier wasy to do this is go to the folder where your mysql data is on the host machine (NOT INSIDE THE CONTAINER), and "chmod 777 -R [mysql folder]"

I know there are hard feelings about security and giving a container full read right permissions in a folder, but if you take other precautions this should be fine, and probably best to back up your volume as well. But this fixed the issue for me, even if I tear the container down and build it back up again with terraform.

[scubeedu2]

Ok I think I got it now,

the issue is the permissions on the mysql folder directory

"[insert your path here]:/var/lib/mysql"

for some reason the "Other User" Value needs to be set to read,write,execute

easier wasy to do this is go to the folder where your mysql data is on the host machine (NOT INSIDE THE CONTAINER), and "chmod 777 -R [mysql folder]"

I know there are hard feelings about security and giving a container full read right permissions in a folder, but if you take other precautions this should be fine, and probably best to back up your volume as well. But this fixed the issue for me, even if I tear the container down and build it back up again with terraform.

This also worked for me!

[doquoctuan]

Many thanks!

[FroggMaster]

Ok I think I got it now,

the issue is the permissions on the mysql folder directory

"[insert your path here]:/var/lib/mysql"

for some reason the "Other User" Value needs to be set to read,write,execute

easier wasy to do this is go to the folder where your mysql data is on the host machine (NOT INSIDE THE CONTAINER), and "chmod 777 -R [mysql folder]"

I know there are hard feelings about security and giving a container full read right permissions in a folder, but if you take other precautions this should be fine, and probably best to back up your volume as well. But this fixed the issue for me, even if I tear the container down and build it back up again with terraform.

I encountered the same problem while upgrade to 2.10.2. I can also confirm that changing the permissions from 755 to 777 on the mysql folder allowed me to logon.

[NeoTrace82]

tried changing the permissions, did allow me to login but all my hosts are gone managed to get it working with 2.9.22 and will keep it on that version till a fix or walkthrough is available to switch from mariadb to sqlite or leave it on mariadb (which I doubt)

I would start "fresh" if anyone can point me to the information how to keep my existing hosts :) If I set up a new npm instance I can't add the host manually as it would not let me create new certs as ofc they have been created allready

[AGauseTT]

NeoTrace82

if you followed my guide then you should've have done it on your persistent storage via your docker Volume, and shouldnt have lost anything. are you sure you are using persistent storage via your containers?

you can check by going into that folder and see if there are any files in there from your database. but it sounds like you may be using the SQL lite version of NPM.

[NeoTrace82]

i do have mariadb running

v

ersion: '3.8' services: app: image: 'jc21/nginx-proxy-manager:2.9.22' restart: always ports:

• '80:80' #HTTP Traffic
• '82:81' #Dashboard Port
• '443:443' #HTTPS Traffic labels:
• com.centurylinklabs.watchtower.enable=false volumes:
• /home/pi/portainer/nginx/config.json:/app/config/production.json
• /home/pi/portainer/nginx/data:/data

• /home/pi/portainer/nginx/letsencrypt:/etc/letsencrypt container_name: nginx_app

  db: image: 'yobasystems/alpine-mariadb:latest' restart: always labels:

• com.centurylinklabs.watchtower.enable=false environment: MYSQL_ROOT_PASSWORD: 'npm' MYSQL_DATABASE: 'npm' MYSQL_USER: 'npm' MYSQL_PASSWORD: 'npm' volumes:
• /home/pi/portainer/nginx/data/mysql:/var/lib/mysql container_name: nginx_db

[AGauseTT]

Forgive me as I use Terraform to launch my Containers, but from my limited knowledge of Docker Compose it looks like you're missing the "depends_on" flag under your NPM configuration. and closer inspection of your NPM configuration, you have nothing specifying to use an external database. Hense you arent actually using the mysql container for your NPM

Reference here: https://nginxproxymanager.com/setup/#using-mysql-mariadb-database

[NeoTrace82]

just realized it myself, poor me ... :)

did add the dependancies and environment and it works now, with the latest image

[truthsword]

What is the consensus here? I rolled back my docker image to 2.9.22 as a workaround.

Is there a simple migration now available for v2.10 that will work going forward?

Thanks for any advice!

[AGauseTT]

What is the consensus here? I rolled back my docker image to 2.9.22 as a workaround.

Is there a simple migration now available for v2.10 that will work going forward?

Thanks for any advice!

The Easiest thing you can do is do what I suggested in regards to the mysql persistent storage area.

[truthsword]

I'll try that, but w/chmod, it seemed like a workaround, so I was less sure to try that. Thanks!

[blassley]

Can anyone explain how to fix this on unraid? My sonarr will at times load too a blank web page and I'm thinking it's due to this issue. Or is there a eta on the fix?

[ChronoWerX82]

@vawaver The problem occurs, if you use the settings from the Setup Instructions - Using MySQL / MariaDB Database If the mapped data folder of the 'app' container contains the mapped data folder of the the 'db' container. So the 'app' container has access to the database files and it seemes that the start process of the 'app' container change the ownership for all files and folders, inclusiv the database files.

You have to separate the mapped folders for each container. Example:

version: "3"
services:
  app:
    image: jc21/nginx-proxy-manager:latest
    ...
    ...
    volumes:
      - ./data/app:/data
      - ./data/letsencrypt:/etc/letsencrypt
    ...
    ...
  db:
    image: 'jc21/mariadb-aria:latest'
    ...
    ...
    volumes:
      - ./data/mysql:/var/lib/mysql

You have to move some folders/files to the new 'app' subdirectory: access, custom_ssl, logs, nginx, keys.json

Here not working under 2.10.2

[FroggMaster]

@ChronoWerX82 I personally 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: apt-get install mysqldump 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 DBMYSQL* environment entry.

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.

[katinatez]

Same issue here read all the post, and decided to downgrade NPM 2.9.22, all working now!

[fbirlik]

If anybody is struggling, mounting following or similar script into pre-exec folder of mariadb would resolve the permission issue without drastic changes:

update-permission.sh:

#!/bin/sh

( echo "waiting for launch.."; sleep 3;
echo "updating permissions.."; chown -vR mysql:mysql /var/lib/mysql/npm ) &

docker-compose.yml important parts

version: '3'
services:
  app:
...
  db:
...
    volumes:
...
      - ./update-permission.sh:/scripts/pre-exec.d/update-permission.sh

works with 2.10.3

[truthsword]

I was leery about futzing with permissions, so I segregated the bind mount paths of the two containers, as suggested by Shineson1001 and it works perfectly.

Here is my compose file. In it you can see the volume path change I made within the db container. Update to "latest" happened without error.

version: '3'
services:
  db:
    image: 'jc21/mariadb-aria:latest'
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: 'abc123'
      MYSQL_DATABASE: 'npm'
      MYSQL_USER: 'abc'
      MYSQL_PASSWORD: '123'
    volumes:
#     - /home/me/docker/npm/data/mysql:/var/lib/mysql
      - /home/me/docker/npm/mysql:/var/lib/mysql
  app:
    depends_on:
      - db
    image: 'jc21/nginx-proxy-manager:latest'
#   image: 'jc21/nginx-proxy-manager:2.9.22'
    restart: always
    ports:
      - '80:80'
      - '81:81'
      - '443:443'
    environment:
      DB_MYSQL_HOST: "db"
      DB_MYSQL_PORT: 3306
      DB_MYSQL_USER: "abc"
      DB_MYSQL_PASSWORD: "123"
      DB_MYSQL_NAME: "npm"
#     DISABLE_IPV6: "true"
    volumes:
      - /home/me/docker/npm/data:/data
      - /home/me/docker/npm/letsencrypt:/etc/letsencrypt

[Apollon77]

ALso did like proposed in https://github.com/NginxProxyManager/nginx-proxy-manager/issues/2774#issuecomment-1489466154 and works!

[dayunnas]

docker exec -it nginxproxymanager_nginxproxymanager-db_1 chown -R mysql:mysql /var/lib/mysql/nginxproxymanager

[PolGZ]

I'm having the same issues. But I am running my npm instance using portainer.

Anybody knows where ./data would be on my host??

In fact, if I get into the npm-app container, not the db one, I don't have a /data folder!

I can see in the db container the /var/lib/mysql folder, and I already did the chown -R 100:101 mysql/ there, and that solved issues within the db container.

But on the "app" one it didn't. And I don't know how I would copy files that I don't find!

docker exec -u 33 -it nginx-proxy-manager-npm-app-1 /bin/sh 

$ ls -l
total 244
-rw-r--r--   1 root root   2277 Nov 22 01:03 app.js
drwxr-xr-x   2 root root   4096 Nov 22 01:03 config
-rw-r--r--   1 root root    604 Nov 22 01:03 db.js
drwxr-xr-x   2 root root   4096 Nov 22 01:03 doc
drwxr-xr-x   6 root root   4096 Nov 22 01:04 frontend
drwxr-xr-x   1 root root   4096 Nov 22 01:03 global
-rw-r--r--   1 root root   1153 Nov 22 01:03 index.js
drwxr-xr-x   2 root root   4096 Nov 22 01:03 internal
-rw-r--r--   1 root root    339 Nov 22 01:03 knexfile.js
drwxr-xr-x   5 root root   4096 Nov 22 01:03 lib
-rw-r--r--   1 root root    483 Nov 22 01:03 logger.js
-rw-r--r--   1 root root    346 Nov 22 01:03 migrate.js
drwxr-xr-x   2 root root   4096 Nov 22 01:03 migrations
drwxr-xr-x   2 root root   4096 Nov 22 01:03 models
drwxr-xr-x 282 root root  12288 Nov 22 01:08 node_modules
-rw-r--r--   1 root root     77 Nov 22 01:03 nodemon.json
-rw-r--r--   1 root root   1056 Nov 22 01:03 package.json
drwxr-xr-x   3 root root   4096 Nov 22 01:03 routes
drwxr-xr-x   3 root root   4096 Nov 22 01:03 schema
-rw-r--r--   1 root root   5173 Nov 22 01:03 setup.js
drwxr-xr-x   2 root root   4096 Nov 22 01:03 templates
-rw-r--r--   1 root root 150084 Nov 22 01:03 yarn.lock

I have 6 proxies already configured, so I hope I don't need to start from scratch!

---

Here are my docker compose:

version: '3'
services:
  npm-app:
    image: 'jc21/nginx-proxy-manager:latest'
    restart: always
    ports:
      - '80:80'
      - '81:81'
      - '443:443'
    env_file:
      - stack.env
    volumes:
      - ./data:/data
      - ./letsencrypt:/etc/letsencrypt
      - /home/user/dockers/web/MyHugoSite/public:/site
  db:
    image: 'jc21/mariadb-aria:latest'
    restart: always
    env_file:
      - stack.env
    volumes:
      - ./data/mysql:/var/lib/mysql

and env file:

# environment variables for npm-app:
DB_MYSQL_HOST=db
DB_MYSQL_PORT=3306
DB_MYSQL_USER=user
DB_MYSQL_PASSWORD=password
DB_MYSQL_NAME=npm

# environment variables for db:
MYSQL_ROOT_PASSWORD=password
MYSQL_DATABASE=npm
MYSQL_USER=user
MYSQL_PASSWORD=password