Database Password other than default results in an error

aquilo-it · December 31, 2024, 2:52pm

Version

5.11.7-d

Environment

Docker (dockerfiles/debian)

Describe the bug

Hi,
it’s my first time trying to install InvoiceNinja on an Ubuntu Host in Docker.
When I use the default config of password ninja for user ninja everything works and I can use InvoiceNinja just fine:

DB_PASSWORD=ninja
MYSQL_PASSWORD=ninja

But as soon as I change the password to a custom one (keeping both db and mysql password the same), I get this error:

app-1    |   SQLSTATE[HY000] [1045] Access denied for user 'ninja'@'172.18.0.4' (using p
app-1    |   assword: YES) (Connection: mysql, SQL: select exists (select 1 from informa
app-1    |   tion_schema.tables where table_schema = 'ninja' and table_name = 'migration
app-1    |   s' and table_type in ('BASE TABLE', 'SYSTEM VERSIONED')) as `exists`)

Steps To Reproduce

git clone GitHub - invoiceninja/dockerfiles: Docker files for Invoice Ninja -b debian
cd dockerfiles/debian
Change the DB_ROOT_PASSWORD, DB_PASSWORD, MYSQL_PASSWORD and MYSQL_ROOT_PASSWORD to two custom but different passwords
docker compose up (I changed the passwords before the first start)

Expected Behavior

If I understood correctly, invoiceninja is creating the db and all the accounts with correct passwords. This does not seem to be the case.

Logs

app-1    | In Connection.php line 825:
app-1    |
app-1    |   SQLSTATE[HY000] [1045] Access denied for user 'ninja'@'172.18.0.4' (using p
app-1    |   assword: YES) (Connection: mysql, SQL: select exists (select 1 from informa
app-1    |   tion_schema.tables where table_schema = 'ninja' and table_name = 'migration
app-1    |   s' and table_type in ('BASE TABLE', 'SYSTEM VERSIONED')) as `exists`)
app-1    |
app-1    |
app-1    | In Connector.php line 66:
app-1    |
app-1    |   SQLSTATE[HY000] [1045] Access denied for user 'ninja'@'172.18.0.4' (using p
app-1    |   assword: YES)
app-1    |

Thank you very much for your help.

hillel · December 31, 2024, 2:53pm

Hi,

Is it possible the password includes a #

aquilo-it · December 31, 2024, 3:46pm

Hi, thanks for the quick reply.

Sadly the password does not contain a #. I even tried 12345678 for the ninja user and 123456789 for the root user.

This is the complete env file:

# IN application vars
APP_URL=http://<my_ip_in_vpn>
APP_KEY=base64:<redacted>
APP_ENV=production
APP_DEBUG=true
REQUIRE_HTTPS=false
PHANTOMJS_PDF_GENERATION=false
PDF_GENERATOR=snappdf
TRUSTED_PROXIES='*'


CACHE_DRIVER=redis
QUEUE_CONNECTION=redis
SESSION_DRIVER=redis

REDIS_HOST=redis
REDIS_PASSWORD=null
REDIS_PORT=6379

FILESYSTEM_DISK=debian_docker

# DB connection
DB_HOST=mysql
DB_PORT=3306
DB_DATABASE=ninja
DB_USERNAME=ninja
DB_PASSWORD=12345678
DB_ROOT_PASSWORD=123456789
DB_CONNECTION=mysql

# Create initial user
# Default to these values if empty
[email protected]
IN_PASSWORD=changeme!
# IN_USER_EMAIL=
# IN_PASSWORD=

# Mail options
MAIL_MAILER=log
MAIL_HOST=smtp.mailtrap.io
MAIL_PORT=2525
MAIL_USERNAME=null
MAIL_PASSWORD=null
MAIL_ENCRYPTION=null
MAIL_FROM_ADDRESS='[email protected]'
MAIL_FROM_NAME='Self Hosted User'

# MySQL
MYSQL_ROOT_PASSWORD=123456789
MYSQL_USER=ninja
MYSQL_PASSWORD=12345678
MYSQL_DATABASE=ninja

# GoCardless/Nordigen API key for banking integration
NORDIGEN_SECRET_ID=
NORDIGEN_SECRET_KEY=

IS_DOCKER=true
SCOUT_DRIVER=null
SNAPPDF_CHROMIUM_PATH=/usr/bin/google-chrome-stable

Still, I get the Access denied for user ... error.

hillel · December 31, 2024, 4:28pm

Maybe this will help:

github.com/invoiceninja/dockerfiles

DB credentials not being applied to db container

opened 02:42PM - 17 Oct 20 UTC

closed 01:18AM - 05 Jan 21 UTC

arcreigh

# Error log from db_1 ``` db_1 | 2020-10-16T14:45:55.813689Z 2 [Note] Acc…ess denied for user ''@'172.24.0.4' (using password: NO) db_1 | 2020-10-16T14:45:55.892297Z 3 [Note] Access denied for user ''@'172.24.0.4' (using password: NO) db_1 | 2020-10-16T14:45:57.420861Z 4 [Note] Access denied for user ''@'172.24.0.4' (using password: NO) db_1 | 2020-10-16T14:45:57.421919Z 5 [Note] Access denied for user ''@'172.24.0.4' (using password: NO) db_1 | 2020-10-16T14:47:17.480945Z 6 [Note] Access denied for user 'root'@'localhost' (using password: NO) db_1 | 2020-10-16T14:47:42.683459Z 7 [Note] Access denied for user 'ninja'@'localhost' (using password: NO) db_1 | 2020-10-16T14:47:49.687977Z 8 [Note] Access denied for user 'ninja'@'localhost' (using password: YES) db_1 | 2020-10-16T14:51:02.686555Z 9 [Note] Access denied for user 'root'@'localhost' (using password: NO) db_1 | 2020-10-16T14:51:11.295856Z 10 [Note] Access denied for user 'root'@'localhost' (using password: YES) db_1 | 2020-10-16T15:05:25.522614Z 11 [Note] Access denied for user ''@'172.24.0.4' (using password: NO) db_1 | 2020-10-16T15:05:25.524638Z 12 [Note] Access denied for user ''@'172.24.0.4' (using password: NO) db_1 | 2020-10-16T15:05:25.876386Z 13 [Note] Access denied for user ''@'172.24.0.4' (using password: NO) db_1 | 2020-10-16T15:05:25.877540Z 14 [Note] Access denied for user ''@'172.24.0.4' (using password: NO) db_1 | 2020-10-16T15:06:28.467799Z 15 [Note] Access denied for user 'ninja'@'172.24.0.4' (using password: YES) db_1 | 2020-10-16T16:06:40.035489Z 16 [Note] Access denied for user ''@'172.24.0.4' (using password: NO) db_1 | 2020-10-16T16:06:40.036427Z 17 [Note] Access denied for user ''@'172.24.0.4' (using password: NO) ``` # Environment information invoiceninja version 5 host OS ubuntu 18.04 LTS the host OS is a VM running in vSphere w/ 4gigs of ram 50 gigs of storage and 2vCPU's Running docker-compose version 1.27.4, build 40524192 Running Docker version 19.03.6, build 369ce74a3c # docker-compose.yml I left mostly defaults as I wanted to test out the image before going production. The hostname is only resolvable internal to my network. ``` version: '3.7' services: server: image: caddy:alpine restart: always environment: - APP_URL=http://ninja.arc.net volumes: # Vhost configuration - ./config/caddy/Caddyfile:/etc/caddy/Caddyfile # Configure your mounted directories, make sure the folder 'public' and 'storage' # exist, before mounting them - public:/var/www/app/public - storage:/var/www/app/storage # you may use a bind-mounted host directory instead, so that it is harder to accidentally remove the volume and lose all your data! # - ./docker/app/public:/var/www/app/public:rw,delegated # - ./docker/app/storage:/var/www/app/storage:rw,delegated depends_on: - app # Run webserver nginx on port 80 # Feel free to modify depending what port is already occupied ports: - "80:80" - "443:443" networks: - invoiceninja app: image: invoiceninja/invoiceninja:5 restart: always cap_add: - SYS_ADMIN environment: - APP_URL=http://ninja.arc.net - APP_KEY=base64:REDACTED_APP_KEY - MULTI_DB_ENABLED=false - DB_HOST1=db volumes: # Configure your mounted directories, make sure the folder 'public' and 'storage' # exist, before mounting them - public:/var/www/app/public - storage:/var/www/app/storage # you may use a bind-mounted host directory instead, so that it is harder to accidentally remove the volume and lose all your data! # - ./docker/app/public:/var/www/app/public:rw,delegated # - ./docker/app/storage:/var/www/app/storage:rw,delegated depends_on: - db networks: - invoiceninja db: image: mysql:5 restart: always environment: - MYSQL_ROOT_PASSWORD=ninjaAdm1nPassword - MYSQL_USER=ninja - MYSQL_PASSWORD=ninja - MYSQL_DATABASE=db-ninja-01 volumes: - mysql-data:/var/lib/mysql:rw # you may use a bind-mounted host directory instead, so that it is harder to accidentally remove the volume and lose all your data! # - ./docker/mysql/data:/var/lib/mysql:rw,delegated networks: - invoiceninja # THIS IS ONLY A VALID CONFIGURATION FOR IN 4. DO NOT USE FOR IN 5. # cron: # image: invoiceninja/invoiceninja:alpine-4 # volumes: # - storage:/var/www/app/storage # - logo:/var/www/app/public/logo # - public:/var/www/app/public # entrypoint: | # /bin/sh -c 'sh -s <<EOF # trap "break;exit" SIGHUP SIGINT SIGTERM # sleep 300s # while /bin/true; do # ./artisan ninja:send-invoices # ./artisan ninja:send-reminders # sleep 1d # done # EOF' # networks: # - invoiceninja volumes: mysql-data: public: storage: # This is needed for letting th cron run correctly # logo: networks: invoiceninja: ``` # Additional questions. - How would one go about building this image through portainer? Currently I am following the wiki instructions to get the web UI running. - If I change the public / storage locations and give it the correct permissions of 1000:www-data 1000 being the default user created on installation The UI breaks entirely as the javascript returns 500 errors when trying to load them. Additionally going to /var/invoiceninja/public or /storage (on the host OS not within the container) reveals that no data is saved there. I am fairly new to Docker so I am not sure if I am missing something vital here in order for a container to access host level directories. Any input here would be much appreciated.

aquilo-it · December 31, 2024, 4:40pm

Sadly this also didn’t help.

The storage:link command fails with the message ERROR The [public/storage] link already exists., so I think the storage link is already ok. The other .env variables didn’t change the error.

hillel · December 31, 2024, 4:45pm

It may help to run docker compose up -d

aquilo-it · December 31, 2024, 4:52pm

After every change I run

docker compose down
docker compose up -d

That should be enough right?

hillel · December 31, 2024, 4:54pm

@david do you have any suggestions, it looks like a few users are running into the same problem.

github.com/invoiceninja/dockerfiles

getting access denied after modifying docker-compose.yml

opened 03:51AM - 22 May 23 UTC

closed 09:01AM - 24 Nov 24 UTC

jorge198506

if i modify the docker-compose.yml file and re deploy it after invoice ninja ha…s been deployed i get acces denied to the DB [dockerfiles-app-1] SQLSTATE[HY000] [1045] Access denied for user 'ninja'@'172.19.0.3' (usi ng password: YES) (SQL: select * from information_schema.tables where table _schema = ninja and table_name = accounts and table_type = 'BASE TABLE') In Connector.php line 70: SQLSTATE[HY000] [1045] Access denied for user 'ninja'@'172.19.0.3' (usi ng password: YES) 2023-05-22T03:41:45Z [ERROR] [Entrypoint]: Error connecting to DB [dockerfiles-db-1] [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.33' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL. [dockerfiles-server-1] 2023/05/22 03:48:55 [notice] 1#1: OS: Linux 5.15.0-72-generic 2023/05/22 03:48:55 [notice] 1#1: getrlimit(RLIMIT_NOFILE): 1048576:1048576 2023/05/22 03:48:55 [notice] 1#1: start worker processes 2023/05/22 03:48:55 [notice] 1#1: start worker process 22 2023/05/22 03:48:55 [notice] 1#1: start worker process 23 if i delete everything and do a new deployment it works just fine. any ideas would be great. thanks

david · January 1, 2025, 7:43am

I believe that when you start the container for the first time, if the database db container has not been created yet, then the db credentials set at that point in time will be used to create the root db user and db user.

If you later change them to another value, that will not change anything. You will want to remove the database image completely. Set your preferred db credentials. And then start up the containers again.

aquilo-it · January 2, 2025, 10:13am

Thanks for your response.

I tried to provide a minimal environment on labs.play-with-docker.com where I could replicate the error. But there the issue didn’t exist

I needed to run all these commands to use the custom password. It seems as there was still some DB config that was being used:

docker compose down --volumes --remove-orphans
rm -rf ./.env ./nginx ./php ./supervisor
docker image prune -a
docker system prune -a --volumes
docker network rm `docker network ls -q`
cd ..
rm -rf dockerfiles/

hillel · January 2, 2025, 10:21am

Glad to hear it, thanks for sharing the solution!