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.