search

NginxProxyManager / nginx-proxy-manager

Docker container for managing Nginx proxy hosts with a simple, powerful interface
https://nginxproxymanager.com
MIT License
22.99k stars 2.66k forks source link

Bad Gateway - error create table npm.migrations Permission Denied #1499

Open myki57 opened 3 years ago

myki57 commented 3 years ago

Hi Team, looking some help regarding NPM. Strangely I manage to make NPM work perfectly yesterday. But now that I started again my docker compose I have a strange issue.

Problem:

Sadly I didn't change anything from the initial config (except ports) :

app:
    image: 'jc21/nginx-proxy-manager:latest'
    restart: unless-stopped
    ports:
      - '80:80'
      - '81:81'
      - '8091:443'
    environment:
      DB_MYSQL_HOST: "db"
      DB_MYSQL_PORT: 3306
      DB_MYSQL_USER: "npm"
      DB_MYSQL_PASSWORD: "npm"
      DB_MYSQL_NAME: "npm"
    volumes:
      - ./data:/data
      - ./letsencrypt:/etc/letsencrypt
    depends_on:
      - "db"

  db:
    image: 'jc21/mariadb-aria:latest'
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: 'npm'
      MYSQL_DATABASE: 'npm'
      MYSQL_USER: 'npm'
      MYSQL_PASSWORD: 'npm'
    volumes:
      - ./data/mysql:/var/lib/mysql

Do you guys have any idea about this issue ?

myki57 commented 3 years ago

After verification 172.29.0.7 is the NPM IP address. So maybe there's a simple issue with NPM that is not connected to mariadb.

myki57 commented 3 years ago

I successfully connect from another container to mariadb (after installing mysql client) to the db IPadress (172.29.0.7) or with the internal docker name "db".

zippyy commented 2 years ago

i have the same issue except mine says migrations lock read only

chaptergy commented 2 years ago

@zippyy those two issues have nothing to do with each other, yours is entirely different.

But issues like these is why we now recommend using the SQLite database, which is much less error prone than having a second container for the database. If you have not set up much just use the new docke-compose.yml provided in the quick setup. If you have, and would like to migrate to SQLite, see https://github.com/jc21/nginx-proxy-manager/discussions/1529#migrate-mariadb-to-sqlite-dbeaver

dbeeren commented 1 year ago

In the new release there are no dependencies with a database anymore.

There are two options:

  1. Rollback to a previous version
  2. Build a new image without the "db" parameters for the my sql. (I seperate those two and the latest release worked for me).

Somehow the database is intergrated in the image. The only disadvantage is that you need to build up the url's in the proxy again. No need for seperate mysql db anymore.

dbeeren commented 1 year ago

Hi Team, looking some help regarding NPM. Strangely I manage to make NPM work perfectly yesterday. But now that I started again my docker compose I have a strange issue.

Problem:

* UX side : Bad gateway on the login page

* Logs from NPM :
  ```
  ✖  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")
  ```

* Logs from DB (only warnings) :
  ```
  2021-10-19 16:21:01 0 [Warning] 'user' entry '@ed85e71078a5' ignored in --skip-name-resolve mode.
  2021-10-19 16:21:01 0 [Warning] 'proxies_priv' entry '@% root@ed85e71078a5' ignored in --skip-name-resolve mode.
  2021-10-19 16:27:06 3 [Warning] Aborted connection 3 to db: 'npm' user: 'npm' host: '172.29.0.7' (Got an error reading communication packets) 
  ```

Sadly I didn't change anything from the initial config (except ports) :

app:
    image: 'jc21/nginx-proxy-manager:latest'
    restart: unless-stopped
    ports:
      - '80:80'
      - '81:81'
      - '8091:443'
    environment:
      DB_MYSQL_HOST: "db"
      DB_MYSQL_PORT: 3306
      DB_MYSQL_USER: "npm"
      DB_MYSQL_PASSWORD: "npm"
      DB_MYSQL_NAME: "npm"
    volumes:
      - ./data:/data
      - ./letsencrypt:/etc/letsencrypt
    depends_on:
      - "db"

  db:
    image: 'jc21/mariadb-aria:latest'
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: 'npm'
      MYSQL_DATABASE: 'npm'
      MYSQL_USER: 'npm'
      MYSQL_PASSWORD: 'npm'
    volumes:
      - ./data/mysql:/var/lib/mysql

Do you guys have any idea about this issue ?

There are two options:

Rollback to a previous version
Build a new image without the "db" parameters for the my sql. (I seperate those two and the latest release worked for me).

Somehow the database is intergrated in the image. The only disadvantage is that you need to build up the url's in the proxy again. No need for seperate mysql db anymore.

PowerUser64 commented 1 year ago

I get the same error on the latest build. I switched to the github-pr-2659 release (from around a month ago) and the backend works now. I can't log in to the management panel, but at least all the proxies work! :tada:

Here's the error that's getting spammed in my log:

nginx-proxy-manager     | [4/1/2023] [9:53:32 AM] [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")
jornl commented 1 year ago

I got it working. Had to access the db-container and change the owner of the folder /var/lib/mysql/npm.


docker exec -it proxydatabase /bin/sh
cd /var/lib/mysql
chown -R mysql:mysql npm
exit
domidetres commented 1 year ago

hey @jornl i also got it working like that, but that would suggest that on every update of the db-container this configuration needs be done - right? - any fix on that?

ggriffinorg commented 1 year ago

Hi,

If you are updating from 2.9.22 and using a Docker Compose file, the Persistent Volume for the MariaDB Aria Container must not be the same as the Persistent Volume for the NPM Container.

So you if you have a "mysql" folder in ./data folder from NPM, create a new Persistent Volume for MariaDB Aria Container, copy the "mysql" folder into it. Then recreate both containers with "latest" and you'll have no issues.

In Docker Compose it would look like this:

NPM Container

    volumes:
      - /etc/localtime:/etc/localtime:ro
      - /var/run/docker.sock:/var/run/docker.sock:ro
      - /var/docker/nginx-proxy-manager/data:/data
      - /var/docker/nginx-proxy-manager/letsencrypt:/etc/letsencrypt

MariaDB Aria Containter

    volumes:
#      - /var/docker/nginx-proxy-manager/data/mysql:/var/lib/mysql # OLD PATH THAT CAUSES ISSUES
      - /var/docker/mariadb-aria/data/mysql:/var/lib/mysql

Trust me it works.

Please check here:

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

BR, Greg

jornl commented 1 year ago

@domidetres you're correct about having to change this every time. A permanent solution would be what @ggriffinorg posted.

pthoelken commented 1 year ago

If you don't want to rebuild the database image with new entrypoints you can start the docker environment with a start script like this: 

#!/bin/bash -e
docker-compose pull
docker-compose up -d
sleep 5
docker exec -it <containername> chown -R mysql:mysql /var/lib/mysql/nginxproxymanager

exit 0

Save this as startup.sh in your docker-compose.yml file location and make it executable with chmod +x startup.sh. This will start up the containers and change the permissions from the database folder into the database container to the right one.

Maybe you have to adjust the path "/var/lib/mysql/nginxproxymanager" to your specific (from your docker-compose.yml) file.

In my case I took the startup.sh file to my crontab file as @reboot.

neil-west-oz commented 1 year ago

Hi,

If you are updating from 2.9.22 and using a Docker Compose file, the Persistent Volume for the MariaDB Aria Container must not be the same as the Persistent Volume for the NPM Container.

So you if you have a "mysql" folder in ./data folder from NPM, create a new Persistent Volume for MariaDB Aria Container, copy the "mysql" folder into it. Then recreate both containers with "latest" and you'll have no issues.

In Docker Compose it would look like this:

NPM Container

    volumes:
      - /etc/localtime:/etc/localtime:ro
      - /var/run/docker.sock:/var/run/docker.sock:ro
      - /var/docker/nginx-proxy-manager/data:/data
      - /var/docker/nginx-proxy-manager/letsencrypt:/etc/letsencrypt

MariaDB Aria Containter

    volumes:
#      - /var/docker/nginx-proxy-manager/data/mysql:/var/lib/mysql # OLD PATH THAT CAUSES ISSUES
      - /var/docker/mariadb-aria/data/mysql:/var/lib/mysql

Trust me it works.

Please check here:

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

BR, Greg

Many thanks for this solution - it was starting to do my head in!

ajeffco commented 1 year ago

Hi,

If you are updating from 2.9.22 and using a Docker Compose file, the Persistent Volume for the MariaDB Aria Container must not be the same as the Persistent Volume for the NPM Container.

So you if you have a "mysql" folder in ./data folder from NPM, create a new Persistent Volume for MariaDB Aria Container, copy the "mysql" folder into it. Then recreate both containers with "latest" and you'll have no issues.

In Docker Compose it would look like this:

NPM Container

    volumes:
      - /etc/localtime:/etc/localtime:ro
      - /var/run/docker.sock:/var/run/docker.sock:ro
      - /var/docker/nginx-proxy-manager/data:/data
      - /var/docker/nginx-proxy-manager/letsencrypt:/etc/letsencrypt

MariaDB Aria Containter

    volumes:
#      - /var/docker/nginx-proxy-manager/data/mysql:/var/lib/mysql # OLD PATH THAT CAUSES ISSUES
      - /var/docker/mariadb-aria/data/mysql:/var/lib/mysql

Trust me it works.

Please check here:

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

BR, Greg

Thank you for this. Was driving me nuts, and tripped over it quite by accident.

JoelFrojmowicz commented 1 year ago

Hi,

If you are updating from 2.9.22 and using a Docker Compose file, the Persistent Volume for the MariaDB Aria Container must not be the same as the Persistent Volume for the NPM Container.

So you if you have a "mysql" folder in ./data folder from NPM, create a new Persistent Volume for MariaDB Aria Container, copy the "mysql" folder into it. Then recreate both containers with "latest" and you'll have no issues.

In Docker Compose it would look like this:

NPM Container

    volumes:
      - /etc/localtime:/etc/localtime:ro
      - /var/run/docker.sock:/var/run/docker.sock:ro
      - /var/docker/nginx-proxy-manager/data:/data
      - /var/docker/nginx-proxy-manager/letsencrypt:/etc/letsencrypt

MariaDB Aria Containter

    volumes:
#      - /var/docker/nginx-proxy-manager/data/mysql:/var/lib/mysql # OLD PATH THAT CAUSES ISSUES
      - /var/docker/mariadb-aria/data/mysql:/var/lib/mysql

Trust me it works.

Please check here:

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

BR, Greg

After 3 hours of trying to figure out what happened to my NPM instance, I just got to your post and magically everything is running normally. I cannot thank you enough for this solution!

THANKS !!!!!

baddate commented 1 year ago

Hi,

If you are updating from 2.9.22 and using a Docker Compose file, the Persistent Volume for the MariaDB Aria Container must not be the same as the Persistent Volume for the NPM Container.

So you if you have a "mysql" folder in ./data folder from NPM, create a new Persistent Volume for MariaDB Aria Container, copy the "mysql" folder into it. Then recreate both containers with "latest" and you'll have no issues.

In Docker Compose it would look like this:

NPM Container

    volumes:
      - /etc/localtime:/etc/localtime:ro
      - /var/run/docker.sock:/var/run/docker.sock:ro
      - /var/docker/nginx-proxy-manager/data:/data
      - /var/docker/nginx-proxy-manager/letsencrypt:/etc/letsencrypt

MariaDB Aria Containter

    volumes:
#      - /var/docker/nginx-proxy-manager/data/mysql:/var/lib/mysql # OLD PATH THAT CAUSES ISSUES
      - /var/docker/mariadb-aria/data/mysql:/var/lib/mysql

Trust me it works.

Please check here:

nginxproxymanager.com/setup/#using-mysql-mariadb-database

BR, Greg

it works when upgrading from 2.9.19 to 2.10.3.

msawyer91 commented 1 year ago

I got it working. Had to access the db-container and change the owner of the folder /var/lib/mysql/npm.

docker exec -it proxydatabase /bin/sh
cd /var/lib/mysql
chown -R mysql:mysql npm
exit

Thank you! Running the above commands (my container name is different) worked perfectly and immediately NPM started working. The issue was that I have Watchtower running in another container and it alerted me that mariadb had a newer version. I ran docker-compose pull to upgrade, and that caused the problem. Thankfully the above bash script fixed it.

Matt

samuellhwarren commented 10 months ago

I got it working. Had to access the db-container and change the owner of the folder /var/lib/mysql/npm.

docker exec -it proxydatabase /bin/sh
cd /var/lib/mysql
chown -R mysql:mysql npm
exit

Thank you! Running the above commands (my container name is different) worked perfectly and immediately NPM started working. The issue was that I have Watchtower running in another container and it alerted me that mariadb had a newer version. I ran docker-compose pull to upgrade, and that caused the problem. Thankfully the above bash script fixed it.

Matt

That worked for me!

tanushshukla commented 10 months ago

I got it working. Had to access the db-container and change the owner of the folder /var/lib/mysql/npm.

docker exec -it proxydatabase /bin/sh
cd /var/lib/mysql
chown -R mysql:mysql npm
exit

This worked for me, too! Thanks!

ektorasdj commented 9 months ago

Hello everyone, what about users with database.sqlite? Nothing works for that to my configuration.

Thank you

pnpninja commented 9 months ago

I got the same issue when trying to access NGINX Proxy Manager

Here is my Docker Compose File

version: "3"
volumes:
  nextcloud-data:
    driver: local
    driver_opts:
      type: none
      o: bind
      device: /portainer/nextcloud/data
  mysql-db:
    driver: local
    driver_opts:
      type: none
      o: bind
      device: /portainer/mysql
  npm-data:
    driver: local
    driver_opts:
      type: none
      o: bind
      device: /portainer/npm-data
  npm-ssl:
    driver: local
    driver_opts:
      type: none
      o: bind
      device: /portainer/npm-ssl
  npm-db:
    driver: local
    driver_opts:
      type: none
      o: bind
      device: /portainer/npm-db

networks:
  frontend:
    # add this if the network is already existing!
    # external: true
  backend:

services:
  nextcloud-app:
    image: nextcloud
    restart: always
    volumes:
      - nextcloud-data:/var/www/html
    environment:
      - MYSQL_PASSWORD=<redacted>
      - MYSQL_DATABASE=common
      - MYSQL_USER=prateek-nextcloud
      - MYSQL_HOST=mysql-db
    networks:
      - frontend
      - backend

  mysql-db:
    image: mysql
    restart: always
    command: --transaction-isolation=READ-COMMITTED --binlog-format=ROW
    volumes:
      - mysql-db:/var/lib/mysql
    environment:
      - MYSQL_ROOT_PASSWORD=<redacted>
      - MYSQL_PASSWORD=<redacted>
      - MYSQL_DATABASE=common
      - MYSQL_USER=prateek-nextcloud
    networks:
      - backend

  npm-app:
    image: jc21/nginx-proxy-manager:latest
    restart: always
    ports:
      - "80:80"
      - "81:81"
      - "443:443"
    environment:
      - DB_MYSQL_HOST=npm-db
      - DB_MYSQL_PORT=3306
      - DB_MYSQL_USER=npm
      - DB_MYSQL_PASSWORD=<redacted>
      - DB_MYSQL_NAME=npm
    volumes:
      - npm-data:/data
      - npm-ssl:/etc/letsencrypt
    networks:
      - frontend
      - backend

  npm-db:
    image: jc21/mariadb-aria:latest
    restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=<redacted>
      - MYSQL_DATABASE=npm
      - MYSQL_USER=npm
      - MYSQL_PASSWORD=<redacted>
    volumes:
      - npm-db:/var/lib/mysql
    networks:
      - backend

How do I fix it?

I tried the command - 

   docker exec -it <container ID of npm-db> /bin/sh
   cd /var/lib/mysql
  chown -R mysql:mysql npm
  exit
firefox7518 commented 7 months ago

Hi all I have a standalone mariadb server where I host the data of NPM. I DO NOT WANT to migrate to SQLITE as I have hundreds of DBs already on the standalone server and I don't want to manage multiple DB systems. So the solution above setting the folders right or using chown etc. does obviously not work as it is not in a container nor do I start a db container.

So how can I fix this now and get rid of the "Bad Gateway" message when trying to login?

samuellhwarren commented 7 months ago

Go to your server. Docker exec -it <> /bin/sh

Then type cd bar/lib/mysql Then CHOWN -R mysql:mysql npm

That did it for me.

On Mar 22, 2024, at 6:35 AM, Andreas Messerli @.***> wrote:



Hi all I have a standalone mariadb server where I host the data of NPM. I DO NOT WANT to migrate to SQLITE as I have hundreds of DBs already on the standalone server and I don't want to manage multiple DB systems. So the solution above setting the folders right or using chown etc. does obviously not work as it is not in a container nor do I start a db container.

So how can I fix this now and get rid of the "Bad Gateway" message when trying to login?

— Reply to this email directly, view it on GitHubhttps://github.com/NginxProxyManager/nginx-proxy-manager/issues/1499#issuecomment-2015111359, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGQILYC4IAZ524TENQUNHGDYZQXQFAVCNFSM5GJVTAW2U5DIOJSWCZC7NNSXTN2JONZXKZKDN5WW2ZLOOQ5TEMBRGUYTCMJTGU4Q. You are receiving this because you commented.Message ID: @.***>

cmcconomy commented 5 months ago

I was able to fix this with the following docker compose:

version: '3.8'
services:
  app:
    image: 'jc21/nginx-proxy-manager:latest'
    environment:
      PUID: 1000
      PGID: 1000
      DB_SQLITE_FILE: "/data/sqlite/database.sqlite"    
    restart: unless-stopped
    ports:
      - '80:80'
      - '81:81'
      - '443:443'
    volumes:
      - nginx-mgr-sqlite:/data/sqlite
      - /mnt/prox-share/docker/nginx-proxy-manager/data:/data
      - /mnt/prox-share/docker/nginx-proxy-manager/letsencrypt:/etc/letsencrypt

volumes:
    nginx-mgr-sqlite:
        external: false
simonhoellein commented 1 month ago

I also facing the same issue with my instance. A quick fix is to restart the MariaDB docker container:

docker restart <container-id-db>

This at least helps to clear the error and you can login to the webinterface again. Not a permanent solution but mabe helps if you are in a hurry ;)