Help: Mysql ->Connection refused

[danielpaul85]

Hello,

i have some problems to connect my local mysql server to photoprism. I use the default docker-compose.yml and start the server with the follow command:

sudo docker-compose logs --tail=25 -f

Both containers "photoprism" and "mariadb" starts an exit with code 0. Because the photoprism container gets an error to connect to the database. Thats the log:

Attaching to photoprism_photoprism_1, photoprism_mariadb_1
photoprism_1  | time="2021-01-26T08:03:11Z" level=debug msg="config: running on “Intel(R) Core(TM)2 Duo CPU     P7350  @ 2.00GHz”"
photoprism_1  | time="2021-01-26T08:03:11Z" level=debug msg="config: settings loaded from /photoprism/storage/config/settings.yml "
photoprism_1  | time="2021-01-26T08:04:11Z" level=fatal msg="dial tcp 127.0.1.1:3306: connect: connection refused"
mariadb_1     | 2021-01-26  8:03:08 0 [Note] InnoDB: 128 rollback segments are active.
mariadb_1     | 2021-01-26  8:03:08 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
mariadb_1     | 2021-01-26  8:03:08 0 [Note] InnoDB: Creating shared tablespace for temporary tables
mariadb_1     | 2021-01-26  8:03:08 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
mariadb_1     | 2021-01-26  8:03:08 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
mariadb_1     | 2021-01-26  8:03:08 0 [Note] InnoDB: 10.5.8 started; log sequence number 45238; transaction id 20
mariadb_1     | 2021-01-26  8:03:08 0 [Note] Plugin 'FEEDBACK' is disabled.
mariadb_1     | 2021-01-26  8:03:08 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
mariadb_1     | 2021-01-26  8:03:08 0 [Note] InnoDB: Buffer pool(s) load completed at 210126  8:03:08
mariadb_1     | 2021-01-26  8:03:08 0 [Note] Server socket created on IP: '::'.
mariadb_1     | 2021-01-26  8:03:08 0 [Warning] 'proxies_priv' entry '@% root@c99b7e7afbd1' ignored in --skip-name-resolve mode.
mariadb_1     | 2021-01-26  8:03:08 0 [Note] Reading of all Master_info entries succeeded
mariadb_1     | 2021-01-26  8:03:08 0 [Note] Added new Master_info '' to hash table
mariadb_1     | 2021-01-26  8:03:08 0 [Note] mysqld: ready for connections.
mariadb_1     | Version: '10.5.8-MariaDB-1:10.5.8+maria~focal'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
mariadb_1     | 2021-01-26  8:04:45 0 [Note] mysqld (initiated by: unknown): Normal shutdown
mariadb_1     | 2021-01-26  8:04:45 0 [Note] Event Scheduler: Purging the queue. 0 events
mariadb_1     | 2021-01-26  8:04:45 0 [Note] InnoDB: FTS optimize thread exiting.
mariadb_1     | 2021-01-26  8:04:45 0 [Note] InnoDB: Starting shutdown...
mariadb_1     | 2021-01-26  8:04:45 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
mariadb_1     | 2021-01-26  8:04:45 0 [Note] InnoDB: Buffer pool(s) dump completed at 210126  8:04:45
mariadb_1     | 2021-01-26  8:04:46 0 [Note] InnoDB: Shutdown completed; log sequence number 45250; transaction id 21
mariadb_1     | 2021-01-26  8:04:46 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
mariadb_1     | 2021-01-26  8:04:46 0 [Note] mysqld: Shutdown complete
mariadb_1     | 
photoprism_photoprism_1 exited with code 0
photoprism_mariadb_1 exited with code 0

Then i changed the docker-compose.yml config and set the databse server to localhost:3306 and set the user credentials. With this user credentials i can connect the databse called "photoprism" on my local mysql server. But after starts the photoprism container i get the same error.

Some ideas?

Thanks yor your help.

[kvalev]

You should take a look at the docker-compose example and follow it closely. In your case, the database hostname in the photoprism config should NOT be 127.0.0.1 or localhost, but the name of the database docker-compose service - from the log it seems to be mariadb.

[danielpaul85]

Thanks for your reply.
But it doesn't matter what hostname was set in the docker-compose.yml. The logs only show the host 127.0.1.1 as hostname to connect to the database and the same error. :(

[alexislefebvre]

Did you restarted the container after using another hostname than 127.0.0.1 or localhost? docker-compose up --detach photoprism should recreate the photoprism with the new parameters.

[alexislefebvre]

Please show your full configuration (without passwords).

[danielpaul85]

Hey,

here is my config:

version:` '3.5'

# Example Docker Compose config file for PhotoPrism (Linux / AMD64)
#
# Documentation : https://docs.photoprism.org/getting-started/docker-compose/
# Docker Hub URL: https://hub.docker.com/r/photoprism/photoprism/
#
# Please run behind a reverse proxy like Caddy, Traefik or Nginx if you need HTTPS / SSL support
# e.g. when running PhotoPrism on a public server outside your home network.
#
# ------------------------------------------------------------------
# DOCKER COMPOSE COMMAND REFERENCE
# ------------------------------------------------------------------
# Start    | docker-compose up -d
# Stop     | docker-compose stop
# Update   | docker-compose pull
# Logs     | docker-compose logs --tail=25 -f
# Terminal | docker-compose exec photoprism bash
# Help     | docker-compose exec photoprism photoprism help
# Config   | docker-compose exec photoprism photoprism config
# Reset    | docker-compose exec photoprism photoprism reset
# Backup   | docker-compose exec photoprism photoprism backup -a -i
# Restore  | docker-compose exec photoprism photoprism restore -a -i
# Index    | docker-compose exec photoprism photoprism index
# Reindex  | docker-compose exec photoprism photoprism index -a
# Import   | docker-compose exec photoprism photoprism import
# -------------------------------------------------------------------
# Note: All commands may have to be prefixed with "sudo" when not running as root.
#       This will change the home directory "~" to "/root" in your configuration.

services:
  photoprism:
    # Use photoprism/photoprism:preview instead for testing preview builds:
    image: photoprism/photoprism:latest
    # Only enable automatic restarts once your installation is properly
    # configured as it otherwise may get stuck in a restart loop:
    # https://docs.photoprism.org/getting-started/faq/#why-is-photoprism-getting-stuck-in-a-restart-loop
    # restart: unless-stopped
    security_opt:
      - seccomp:unconfined
      - apparmor:unconfined
    ports:
      - 2342:2342 # [local port]:[container port]
    environment:
      PHOTOPRISM_ADMIN_PASSWORD: "insecure"          # PLEASE CHANGE: Your initial admin password (min 4 characters)
      PHOTOPRISM_HTTP_PORT: 2342                     # Built-in Web server port
      PHOTOPRISM_HTTP_COMPRESSION: "gzip"            # Improves transfer speed and bandwidth utilization (none or gzip)
      PHOTOPRISM_DEBUG: "false"                      # Run in debug mode (shows additional log messages)
      PHOTOPRISM_PUBLIC: "false"                     # No authentication required (disables password protection)
      PHOTOPRISM_READONLY: "false"                   # Don't modify originals directory (reduced functionality)
      PHOTOPRISM_EXPERIMENTAL: "false"               # Enables experimental features
      PHOTOPRISM_DISABLE_WEBDAV: "false"             # Disables built-in WebDAV server
      PHOTOPRISM_DISABLE_SETTINGS: "false"           # Disables Settings in Web UI
      PHOTOPRISM_DISABLE_TENSORFLOW: "false"         # Disables using TensorFlow for image classification
      PHOTOPRISM_DARKTABLE_PRESETS: "false"          # Enables Darktable presets and disables concurrent RAW conversion
      PHOTOPRISM_DETECT_NSFW: "false"                # Flag photos as private that MAY be offensive (requires TensorFlow)
      PHOTOPRISM_UPLOAD_NSFW: "true"                 # Allow uploads that MAY be offensive
      #PHOTOPRISM_DATABASE_DRIVER: "sqlite"         # SQLite is an embedded database that doesn't require a server
      PHOTOPRISM_DATABASE_DRIVER: "mysql"            # Use MariaDB (or MySQL) instead of SQLite for improved performance
      PHOTOPRISM_DATABASE_SERVER: "localhost:3306"     # MariaDB database server (hostname:port)
      PHOTOPRISM_DATABASE_NAME: "photoprism"         # MariaDB database schema name
      PHOTOPRISM_DATABASE_USER: "photoprism"         # MariaDB database user name
      PHOTOPRISM_DATABASE_PASSWORD: ""       # MariaDB database user password
      PHOTOPRISM_SITE_URL: "http://localhost:2342/"  # Public PhotoPrism URL
      PHOTOPRISM_SITE_TITLE: "PhotoPrism"
      PHOTOPRISM_SITE_CAPTION: "Browse Your Life"
      PHOTOPRISM_SITE_DESCRIPTION: ""
      PHOTOPRISM_SITE_AUTHOR: ""
      # You may optionally set a user / group id using environment variables if your Docker version or NAS does not
      # support this natively (see next example):
      # UID: 1000
      # GID: 1000
      # UMASK: 0000
    # Uncomment and edit the following line to set a specific user / group id (native):
    # user: "1000:1000"
    volumes:
      # Your photo and video files ([local path]:[container path]):
      - "~/Pictures:/photoprism/originals"
      # Multiple folders can be indexed by mounting them as sub-folders of /photoprism/originals:
      # - "/mnt/Family:/photoprism/originals/Family"    # [folder_1]:/photoprism/originals/[folder_1]
      # - "/mnt/Friends:/photoprism/originals/Friends"  # [folder_2]:/photoprism/originals/[folder_2]
      # Mounting an import folder is optional (see docs):
      # - "~/Import:/photoprism/import"
      # Permanent storage for settings, index & sidecar files (DON'T REMOVE):
      - "./storage:/photoprism/storage"

  mariadb:
    image: mariadb:10.5
    restart: unless-stopped
    security_opt:
      - seccomp:unconfined
      - apparmor:unconfined
    command: mysqld --transaction-isolation=READ-COMMITTED --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --max-connections=512 --innodb-rollback-on-timeout=OFF --innodb-lock-wait-timeout=50
    volumes: # Don't remove permanent storage for index database files!
      - "./database:/var/lib/mysql"
    environment:
      MYSQL_ROOT_PASSWORD: please_change
      MYSQL_DATABASE: photoprism
      MYSQL_USER: photoprism
      MYSQL_PASSWORD: insecure

# Uncomment the following lines to upgrade automatically, whenever there is a new Docker image available:
#
#  watchtower:
#    image: containrrr/watchtower
#    restart: unless-stopped
#    volumes:
#      - "/var/run/docker.sock:/var/run/docker.sock"

[alexislefebvre]

So the database server and password are wrong.

Replace:

• PHOTOPRISM_DATABASE_SERVER: "localhost:3306" with PHOTOPRISM_DATABASE_SERVER: "mariadb:3306"
• PHOTOPRISM_DATABASE_PASSWORD: "" with PHOTOPRISM_DATABASE_PASSWORD: "insecure"

[danielpaul85]

I have change the config to the default one :

version: '3.5'

# Example Docker Compose config file for PhotoPrism (Linux / AMD64)
#
# Documentation : https://docs.photoprism.org/getting-started/docker-compose/
# Docker Hub URL: https://hub.docker.com/r/photoprism/photoprism/
#
# Please run behind a reverse proxy like Caddy, Traefik or Nginx if you need HTTPS / SSL support
# e.g. when running PhotoPrism on a public server outside your home network.
#
# ------------------------------------------------------------------
# DOCKER COMPOSE COMMAND REFERENCE
# ------------------------------------------------------------------
# Start    | docker-compose up -d
# Stop     | docker-compose stop
# Update   | docker-compose pull
# Logs     | docker-compose logs --tail=25 -f
# Terminal | docker-compose exec photoprism bash
# Help     | docker-compose exec photoprism photoprism help
# Config   | docker-compose exec photoprism photoprism config
# Reset    | docker-compose exec photoprism photoprism reset
# Backup   | docker-compose exec photoprism photoprism backup -a -i
# Restore  | docker-compose exec photoprism photoprism restore -a -i
# Index    | docker-compose exec photoprism photoprism index
# Reindex  | docker-compose exec photoprism photoprism index -a
# Import   | docker-compose exec photoprism photoprism import
# -------------------------------------------------------------------
# Note: All commands may have to be prefixed with "sudo" when not running as root.
#       This will change the home directory "~" to "/root" in your configuration.

services:
  photoprism:
    # Use photoprism/photoprism:preview instead for testing preview builds:
    image: photoprism/photoprism:latest
    # Only enable automatic restarts once your installation is properly
    # configured as it otherwise may get stuck in a restart loop:
    # https://docs.photoprism.org/getting-started/faq/#why-is-photoprism-getting-stuck-in-a-restart-loop
    # restart: unless-stopped
    security_opt:
      - seccomp:unconfined
      - apparmor:unconfined
    ports:
      - 2342:2342 # [local port]:[container port]
    environment:
      PHOTOPRISM_ADMIN_PASSWORD: "insecure"          # PLEASE CHANGE: Your initial admin password (min 4 characters)
      PHOTOPRISM_HTTP_PORT: 2342                     # Built-in Web server port
      PHOTOPRISM_HTTP_COMPRESSION: "gzip"            # Improves transfer speed and bandwidth utilization (none or gzip)
      PHOTOPRISM_DEBUG: "false"                      # Run in debug mode (shows additional log messages)
      PHOTOPRISM_PUBLIC: "false"                     # No authentication required (disables password protection)
      PHOTOPRISM_READONLY: "false"                   # Don't modify originals directory (reduced functionality)
      PHOTOPRISM_EXPERIMENTAL: "false"               # Enables experimental features
      PHOTOPRISM_DISABLE_WEBDAV: "false"             # Disables built-in WebDAV server
      PHOTOPRISM_DISABLE_SETTINGS: "false"           # Disables Settings in Web UI
      PHOTOPRISM_DISABLE_TENSORFLOW: "false"         # Disables using TensorFlow for image classification
      PHOTOPRISM_DARKTABLE_PRESETS: "false"          # Enables Darktable presets and disables concurrent RAW conversion
      PHOTOPRISM_DETECT_NSFW: "false"                # Flag photos as private that MAY be offensive (requires TensorFlow)
      PHOTOPRISM_UPLOAD_NSFW: "true"                 # Allow uploads that MAY be offensive
      #PHOTOPRISM_DATABASE_DRIVER: "sqlite"         # SQLite is an embedded database that doesn't require a server
      PHOTOPRISM_DATABASE_DRIVER: "mysql"            # Use MariaDB (or MySQL) instead of SQLite for improved performance
      PHOTOPRISM_DATABASE_SERVER: "mariadb:3306"     # MariaDB database server (hostname:port)
      PHOTOPRISM_DATABASE_NAME: "photoprism"         # MariaDB database schema name
      PHOTOPRISM_DATABASE_USER: "photoprism"         # MariaDB database user name
      PHOTOPRISM_DATABASE_PASSWORD: "insecure"       # MariaDB database user password
      PHOTOPRISM_SITE_URL: "http://localhost:2342/"  # Public PhotoPrism URL
      PHOTOPRISM_SITE_TITLE: "PhotoPrism"
      PHOTOPRISM_SITE_CAPTION: "Browse Your Life"
      PHOTOPRISM_SITE_DESCRIPTION: ""
      PHOTOPRISM_SITE_AUTHOR: ""
      # You may optionally set a user / group id using environment variables if your Docker version or NAS does not
      # support this natively (see next example):
      # UID: 1000
      # GID: 1000
      # UMASK: 0000
    # Uncomment and edit the following line to set a specific user / group id (native):
    # user: "1000:1000"
    volumes:
      # Your photo and video files ([local path]:[container path]):
      - "~/Pictures:/photoprism/originals"
      # Multiple folders can be indexed by mounting them as sub-folders of /photoprism/originals:
      # - "/mnt/Family:/photoprism/originals/Family"    # [folder_1]:/photoprism/originals/[folder_1]
      # - "/mnt/Friends:/photoprism/originals/Friends"  # [folder_2]:/photoprism/originals/[folder_2]
      # Mounting an import folder is optional (see docs):
      # - "~/Import:/photoprism/import"
      # Permanent storage for settings, index & sidecar files (DON'T REMOVE):
      - "./storage:/photoprism/storage"

  mariadb:
    image: mariadb:10.5
    restart: unless-stopped
    security_opt:
      - seccomp:unconfined
      - apparmor:unconfined
    command: mysqld --transaction-isolation=READ-COMMITTED --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --max-connections=512 --innodb-rollback-on-timeout=OFF --innodb-lock-wait-timeout=50
    volumes: # Don't remove permanent storage for index database files!
      - "./database:/var/lib/mysql"
    environment:
      MYSQL_ROOT_PASSWORD: please_change
      MYSQL_DATABASE: photoprism
      MYSQL_USER: photoprism
      MYSQL_PASSWORD: insecure

# Uncomment the following lines to upgrade automatically, whenever there is a new Docker image available:
#
#  watchtower:
#    image: containrrr/watchtower
#    restart: unless-stopped
#    volumes:
#      - "/var/run/docker.sock:/var/run/docker.sock"

Now i get another error:

Attaching to photoprism_photoprism_1, photoprism_mariadb_1
mariadb_1     | 2021-01-26  8:03:08 0 [Note] InnoDB: 128 rollback segments are active.
mariadb_1     | 2021-01-26  8:03:08 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
mariadb_1     | 2021-01-26  8:03:08 0 [Note] InnoDB: Creating shared tablespace for temporary tables
mariadb_1     | 2021-01-26  8:03:08 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
mariadb_1     | 2021-01-26  8:03:08 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
mariadb_1     | 2021-01-26  8:03:08 0 [Note] InnoDB: 10.5.8 started; log sequence number 45238; transaction id 20
mariadb_1     | 2021-01-26  8:03:08 0 [Note] Plugin 'FEEDBACK' is disabled.
mariadb_1     | 2021-01-26  8:03:08 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
mariadb_1     | 2021-01-26  8:03:08 0 [Note] InnoDB: Buffer pool(s) load completed at 210126  8:03:08
mariadb_1     | 2021-01-26  8:03:08 0 [Note] Server socket created on IP: '::'.
mariadb_1     | 2021-01-26  8:03:08 0 [Warning] 'proxies_priv' entry '@% root@c99b7e7afbd1' ignored in --skip-name-resolve mode.
mariadb_1     | 2021-01-26  8:03:08 0 [Note] Reading of all Master_info entries succeeded
mariadb_1     | 2021-01-26  8:03:08 0 [Note] Added new Master_info '' to hash table
mariadb_1     | 2021-01-26  8:03:08 0 [Note] mysqld: ready for connections.
mariadb_1     | Version: '10.5.8-MariaDB-1:10.5.8+maria~focal'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
mariadb_1     | 2021-01-26  8:04:45 0 [Note] mysqld (initiated by: unknown): Normal shutdown
mariadb_1     | 2021-01-26  8:04:45 0 [Note] Event Scheduler: Purging the queue. 0 events
mariadb_1     | 2021-01-26  8:04:45 0 [Note] InnoDB: FTS optimize thread exiting.
mariadb_1     | 2021-01-26  8:04:45 0 [Note] InnoDB: Starting shutdown...
mariadb_1     | 2021-01-26  8:04:45 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
mariadb_1     | 2021-01-26  8:04:45 0 [Note] InnoDB: Buffer pool(s) dump completed at 210126  8:04:45
mariadb_1     | 2021-01-26  8:04:46 0 [Note] InnoDB: Shutdown completed; log sequence number 45250; transaction id 21
mariadb_1     | 2021-01-26  8:04:46 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
mariadb_1     | 2021-01-26  8:04:46 0 [Note] mysqld: Shutdown complete
mariadb_1     | 
photoprism_mariadb_1 exited with code 0
photoprism_1  | time="2021-01-27T16:24:35Z" level=fatal msg="dial tcp: lookup mariadb: Temporary failure in name resolution"
photoprism_photoprism_1 exited with code 0

Some kind of idea from me. It is a problem that i start docker-compsoe with sudo?

[alexislefebvre]

Sorry I have no explanation for that line “mysqld (initiated by: unknown): Normal shutdown”.

[andrewlow]

I'm not using docker-compose - but I ran into a similar problem recently.

[edit] -- hmm.. I'm not sure this is related as I review the thread above. However, my suggestion here is to do some diagnosis by manually shelling into the containers and verifying that you can (or can't) access things.

The general problem people run into is password mismatch. However this wasn't my problem.

If I was to shell into the mariadb container

docker exec -it mariadb bash
# then execute a mysql login - my password was fine
mysql -u photoprism -p

I then shelled into the photoprism container to check my access to the db from there

docker exec -it photoprism
# and from inside the container
mysql -h mariadb -P 3306 -u photoprism

This also worked fine.

However, when I specified the database

mysql -h mariadb -P 3306 -u photoprism -p photoprism

It failed. This caused me to scratch my head a bunch -- then I decided to check inside of my mariadb to see if the photoprism db existed -- it did not.

I suspect - that there is some bug in the bootstrap, where the db is not being created?

I haven't tried with a normal docker-compose deploy - again, I'm using just docker.. but you may have a similar problem.

[danielpaul85]

Hey Guys,

sound a little bit crazy, but after a restart of my ubuntu machine, photoprism container starts with mysql support and anything is fine. :)

[andrewlow]

I suggest you mark this as 'answered' since you solved the problem with the classic 'turn it off and on again'