Invoice Ninja Docker Container Not Connecting to Remote MySQL

  docker, invoice-ninja, mysql

I hope someone can help me with setting up Invoice Ninja for the first time. I am trying to startup Invoice Ninja using Docker Compose, but seem to have a MySQL connection problem:

SQLSTATE[HY000] [2002] No such file or directory

The MySQL instance is hosted remotely and is currently hosting various databases that are accessible by the Docker host and containers.

Using DBeaver from my local machine:

  • I can connect to the Invoice Ninja database directly via DBeaver and used the grant option when creating the MySQL user and database.

  • I can create a table as the user and do a select via DBeaver .

  • I can connect to the MySQL remote DB from within the container.

…yet I still get these MySQL errors.

The select command from DBeaver:

SHOW TABLES

Tables_in_ninja
NewTable

The connection from the container to MySQL:

~ $ mysql -h 13.13.13.13 -u ninja -pninja123 ninja
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MySQL connection id is 1235069
Server version: 8.0.21 Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

MySQL [ninja]> SHOW TABLES;
+-----------------+
| Tables_in_ninja |
+-----------------+
| NewTable        |
+-----------------+
1 row in set (0.003 sec)

MySQL [ninja]>

SQL Command to Create User and Database:

CREATE USER 'ninja'@'%' IDENTIFIED BY 'ninja123';
CREATE DATABASE IF NOT EXISTS ninja CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
GRANT ALL PRIVILEGES on ninja.* to 'ninja'@'%' WITH GRANT OPTION;
FLUSH privileges;

I use the following compose example:

version: '2'

services:         
  invoiceninja-app:
    container_name: invoiceninja-app
    image: "invoiceninja/invoiceninja"
    ports:
      - "61000:9000"
    hostname: "invoiceninja-app"
    environment:
     - IS_DOCKER=true
     - APP_ENV=production
     - APP_DEBUG=1
     - APP_URL=http://in-01.domain.com:61000
     - APP_KEY=base64:BZ5GN2QUAkiYEXAMPLEN9pq55jctXfAKX4=
     - APP_CIPHER=AES-256-CBC
     - DB_TYPE=mysql
     - DB_STRICT=false
     - DB_HOST=mysql-67.myhost.com
     - DB_DATABASE=ninja
     - DB_USERNAME=ninja
     - DB_PASSWORD=ninja123
    restart: "always"
    volumes:
     - /mnt/containers/invoiceninja-app-public/:/var/app/public/
     - /mnt/containers/invoiceninja-app-store/:/var/app/storage/
    networks:
      invoiceninja:
        ipv4_address: "172.37.0.4"     
    logging:
      driver: "json-file"
      options:
        max-file: "1"
        max-size: "1m"
        
networks:
  invoiceninja:
    ipam:
      config:
        - subnet: "172.37.0.0/16"  

Additional Debug Info:

Connections Inside Container

[email protected]:~$ sudo docker exec -it invoiceninja-app /bin/sh
~ $ netstat -tlpn
netstat: showing only processes with your user ID
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 127.0.0.11:36559        0.0.0.0:*               LISTEN      -
tcp        0      0 :::9000                 :::*                    LISTEN      78/php-fpm.conf)

Docker Host Networks

[email protected]:~$ sudo docker network ls

NETWORK ID          NAME                               DRIVER              SCOPE
5def5exc1fd2        invoiceninja_default               bridge              local
7433aurdc010        invoiceninja_invoiceninja          bridge              local

MySQL User Auth

SELECT user,authentication_string,plugin,host FROM mysql.user WHERE user="ninja";
+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| ninja            | ssssssss                                  | mysql_native_password | %         |

Environment Vars In Container

~ $ env
SNAPPDF_EXECUTABLE_PATH=/usr/bin/chromium-browser
DB_TYPE=mysql
PHP_EXTRA_CONFIGURE_ARGS=--enable-fpm --with-fpm-user=www-data --with-fpm-group=www-data --disable-cgi
APP_DEBUG=1
HOSTNAME=invoiceninja-app
APP_URL=http://in-01.domain.com:61000
PHP_INI_DIR=/usr/local/etc/php
SHLVL=1
HOME=/var/www/app
APP_CIPHER=AES-256-CBC
DB_DATABASE=ninja
PHP_LDFLAGS=-Wl,-O1 -pie
PHP_CFLAGS=-fstack-protector-strong -fpic -fpie -O2 -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64
PHP_VERSION=7.4.15
DB_USERNAME=ninja
GPG_KEYS=42670A7FE4D04C074A4EF02D 5A528B46F53EA312
PHP_CPPFLAGS=-fstack-protector-strong -fpic -fpie -O2 -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64
PHP_ASC_URL=https://www.php.net/distributions/php-7.4.15.tar.xz.asc
INVOICENINJA_VERSION=5.1.13
BAK_PUBLIC_PATH=/var/www/app/docker-backup-public/
PHP_URL=https://www.php.net/distributions/php-7.4.15.tar.xz
TERM=xterm
INVOICENINJA_USER=invoiceninja
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
BAK_STORAGE_PATH=/var/www/app/docker-backup-storage/
IS_DOCKER=true
APP_ENV=production
APP_KEY=base64:BZ5GNEXAMPLEjctXfAKX4=
DB_PASSWORD=ninja123
PHPIZE_DEPS=autoconf            dpkg-dev dpkg           file            g++             gcc             libc-dev                make            pkgconf                 re2c
PWD=/var/www/app
PHP_SHA256=9b859c65f0cf7b3efb3c20d874a79b5ec44d43cb8
DB_STRICT=false
DB_HOST=mysql-67.myhost.com
LOG=errorlog

Docker Container Log

Configuration cache cleared!
Configuration cached successfully!
Configuration cache cleared!
Configuration cached successfully!
Route cache cleared!
Routes cached successfully!
les cached successfully!

In Connection.php line 678:
  SQLSTATE[HY000] [2002] No such file or directory (SQL: select * from inform  
  ation_schema.tables where table_schema = ninja and table_name = accounts an  
  d table_type = 'BASE TABLE')                                                 

In Exception.php line 18:
  SQLSTATE[HY000] [2002] No such file or directory  

In PDOConnection.php line 38:

  SQLSTATE[HY000] [2002] No such file or directory  

[02-Mar-2021 09:21:58] NOTICE: [pool www] 'user' directive is ignored when FPM is not running as root
[02-Mar-2021 09:21:58] NOTICE: [pool www] 'user' directive is ignored when FPM is not running as root
[02-Mar-2021 09:21:58] NOTICE: [pool www] 'group' directive is ignored when FPM is not running as root
[02-Mar-2021 09:21:58] NOTICE: [pool www] 'group' directive is ignored when FPM is not running as root
[02-Mar-2021 09:21:58] NOTICE: fpm is running, pid 79
[02-Mar-2021 09:21:58] NOTICE: ready to handle connections

In Connection.php line 678:
SQLSTATE[HY000] [2002] No such file or directory (SQL: select * from inform  
ation_schema.tables where table_schema = ninja and table_name = accounts an  
 d table_type = 'BASE TABLE')                                                 

In Exception.php line 18:
 SQLSTATE[HY000] [2002] No such file or directory  

Source: Docker Questions

LEAVE A COMMENT