Skip to content

Troubleshooting MariaDB Problems

You are welcome to ask for help in our community chat. Sponsors receive direct technical support via email. Before submitting a support request, try to determine the cause of your problem.

Compatibility

PhotoPrism is compatible with SQLite 3 and MariaDB 10.5.12+. Official support for MySQL 8 is discontinued as Oracle seems to have stopped shipping new features and enhancements. As a result, the testing effort required before each release is no longer feasible.

Our configuration examples are generally based on the current stable version to take advantage of performance improvements. This does not mean that older versions are no longer supported and you must upgrade immediately. We recommend not using the :latest tag for the MariaDB Docker image and to upgrade manually by changing the tag once we had a chance to test a new major version, e.g.:

services:
  mariadb:
    image: mariadb:11
    ...

Cannot Connect

First, verify that you are using the correct port (default is 3306) and host:

  • in the internal Docker network, the default hostname is mariadb (same as the service)
  • avoid changing the default network configuration, unless you are experienced with this
  • avoid using IP addresses other than 127.0.0.1 (localhost) directly, as they can change
  • only use localhost or 127.0.0.1 if the database port has been exposed as described below and you are on the same computer (host)
  • we recommend configuring a local hostname to access other hosts on your network

To connect to MariaDB from your host or home network, you need to expose port 3306 in your compose.yaml or docker-compose.yml and restart the service for changes to take effect:

services:
  mariadb:
    ports:
      - "3306:3306"

Set strong passwords if the database is exposed to an external network. Never expose your database to the public Internet in this way, for example, if it is running on a cloud server.

If this doesn't help, check the Docker Logs for messages like disk full, disk quota exceeded, no space left on device, read-only file system, error creating path, wrong permissions, no route to host, connection failed, exec format error, no matching manifest, and killed:

  • Make sure that the database storage folder is readable and writable: Errors such as "read-only file system", "error creating path", "failed to create folder", "permission denied", or "wrong permissions" indicate a filesystem permission problem
  • If symbolic links are mounted or used within the storage folder, replace them with the actual paths and verify that they are accessible
  • If the MariaDB service has been "killed" or otherwise automatically terminated, this can point to a memory problem (add swap and/or memory; remove or increase usage limits)
  • In case the logs also show "disk full", "quota exceeded", or "no space left" errors, either the disk containing the storage folder is full (add storage) or a disk usage limit is configured (remove or increase it)
  • Log messages that contain "no route to host" may also indicate a general network configuration problem (follow our examples)
  • You have to resort to alternative Docker images to run MariaDB on ARMv7-based devices and those with a 32-bit operating system
  • You may find a solution in the official MariaDB Docker Image FAQ

Custom DSN

You may encounter errors similar to this when starting your instance or restoring a backup if you have manually configured a MariaDB Data Source Name (DSN), without adding the necessary parameters such as parseTime=true to the PHOTOPRISM_DATABASE_DSN environment variable or the --database-dsn command flag:

sql: Scan error on column index 5 unsupported Scan,
storing driver.Value type []uint8 into type *time.Time

To avoid this issue, we recommend using the following configuration options to specify the MariaDB database name, server, user, and password:

  • PHOTOPRISM_DATABASE_NAME
  • PHOTOPRISM_DATABASE_SERVER
  • PHOTOPRISM_DATABASE_USER
  • PHOTOPRISM_DATABASE_PASSWORD

This will automatically generate a compatible Data Source Name (DSN) for connecting to your MariaDB database server.

Since the connection parameters may change in future versions, manually setting the DSN is not recommended unless it is for testing purposes or to meet specific requirements. If you need to set it manually, the following parameters should be included at the end:

?charset=utf8mb4,utf8&collation=utf8mb4_unicode_ci&parseTime=true&timeout=60s

Note that it is not possible to set a custom DSN for MariaDB when a database server is configured at the same time, as the database server setting takes precedence.

When using SQLite, the DSN configuration option allows you to specify the database filename and custom parameters. Learn more ›

Wrong Password

If the password you are using was specified in a compose.yaml or docker-compose.yml file and contains one or more $ characters, these must be escaped with $$ (a double dollar sign) so that, for example, "compo$e" becomes "compo$$e":

services:
  mariadb:
    environment:
      # sets password to "compo$e"
      MARIADB_PASSWORD: "compo$$e"

Also note that you cannot change the database password with MARIADB_PASSWORD after MariaDB has been started for the first time.

In this case, you can either delete the database storage folder and restart the database service or follow the instructions under Lost Root Password.

Bad Performance

Many users reporting poor performance and high CPU usage have migrated from SQLite to MariaDB, so their database schema is no longer optimized for performance. For example, MariaDB cannot handle rows with text columns in memory and always uses temporary tables on disk if there are any.

The instructions for these migrations were provided by a contributor and are not part of the original software distribution. As such, they have not been officially released, recommended, or extensively tested by us.

If this is the case, please make sure that your migrated database schema matches that of a fresh, non-migrated installation. It may help to run the migrations manually in a terminal using the migrations subcommands. However, this does not guarantee that all issues such as missing indexes are resolved.

Get Performance Tips › View Database Schema ›

Version Upgrade

Should MariaDB fail to start after upgrading from an earlier version (or migrating from MySQL), the internal management schema may be outdated. With older versions, it could only be updated manually. However, newer MariaDB Docker images support automatic upgrades on startup, so you don't have to worry about that anymore.

When upgrading from MariaDB 10.x to 11.0, you must replace command: mysqld with command: (followed by the command flags) in your compose.yaml or docker-compose.yml file, otherwise the database server may fail to start.

Manual Update

To manually upgrade the internal database schema, run this command in a terminal:

docker compose exec mariadb mariadb-upgrade -uroot -p

Enter the MariaDB "root" password specified in your compose.yaml or docker-compose.yml when prompted.

Alternatively, you can downgrade to the previous version, create a database backup using the photoprism backup command, start a new database instance based on the latest version, and then restore your index with the photoprism restore command.

Auto Upgrade

To enable automatic schema updates, set MARIADB_AUTO_UPGRADE to a non-empty value in your compose.yaml or docker-compose.yml as shown in our config example:

services:
  mariadb:
    image: mariadb:11
    ...
    environment:
      MARIADB_AUTO_UPGRADE: "1"
      MARIADB_INITDB_SKIP_TZINFO: "1"
      ...

Before starting MariaDB in production mode, the database image entrypoint script now runs mariadb-upgrade to update the internal management schema as needed. For example, when you pull a new major release and restart the service.

Since PhotoPrism does not require time zone support, you can also add MARIADB_INITDB_SKIP_TZINFO to your config as shown above. However, this is only a recommendation and optional.

Incompatible Schema

If your database does not seem to be compatible with the currently installed version of PhotoPrism, for example because search results are missing or incorrect, first make sure you are using a supported database and that its internal management schema is up-to-date. How to do that is explained in the previous section.

Once you have verified that neither is a problem, you can run the following command in a terminal to check the status of previous database schema migrations:

docker compose exec photoprism photoprism migrations ls

Omit the docker compose exec photoprism prefix if you are using an interactive terminal session or are running PhotoPrism directly on your computer without Docker.

Re-Run Migrations

Should the status of any migration not be OK, you can re-run failed migrations using this command in a terminal:

docker compose exec photoprism photoprism migrations run -f

The -f flag instructs the photoprism migrations run subcommand to re-run previously failed migrations. Use --help to see the command help.

Additional migration command examples can be found in the Developer Guide.

Complete Rescan

We recommend that you re-index your pictures after a schema migration, especially if problems persist. You can either start a rescan from the user interface by navigating to Library > Index, checking "Complete Rescan", and then clicking "Start", or by running this command in a terminal:

docker compose exec photoprism photoprism index -f

Be careful not to start multiple indexing processes at the same time, as this will lead to a high server load.

Server Migration

If you want to move your MariaDB database to another server or virtual machine:

To create a database backup:

On the new server:

Server Crashes

If the server crashes unexpectedly or your database files get corrupted frequently, it is usually because they are stored on an unreliable device such as a USB flash drive, an SD card, or a shared network folder mounted via NFS or CIFS. These may also have unexpected file size limitations, which is especially problematic for databases that do not split data into smaller files.

  • Never use the same database files with more than one server instance
  • To share a database over a network, run the database server directly on the remote server instead of sharing database files
  • To repair your tables after you have moved the files to a local disk, you can start MariaDB with --innodb-force-recovery=1 (otherwise the same procedure as for recovering a lost password, see above)
  • Make sure you are using the latest Docker version and read the release notes for the database server version you are using

Invalid Table Errors

If you are using macOS and see errors like Invalid (old?) table or database name '._column_stats', it may be because you are running MariaDB on a file system like ExFAT that does not support extended attributes. In this case, macOS automatically creates these files and MariaDB then reports them as invalid tables (which is technically correct). To remove extended attribute files, you can run the following in a terminal:

find . -type f -name '._*' -delete

Unless you open the storage folder again in macOS Finder, the errors should then be gone after restarting the database.

Corrupted Files

Most database table and/or index corruptions are hardware-related. Corrupted page writes can be caused by power failures or bad memory. The problem can also be caused by using network attached storage (NAS) and placing InnoDB databases on it.

↪ Server Crashes

Lost Root Password

In case you forgot the MariaDB "root" password and the one specified in your configuration does not work, you can start the server with the --skip-grant-tables flag added to the mysqld command in your compose.yaml or docker-compose.yml. This will temporarily give full access to all users after a restart:

services:
  mariadb:
    command: --skip-grant-tables

Restart the mariadb service for changes to take effect:

docker compose stop mariadb
docker compose up -d mariadb

Now open a database console:

docker compose exec mariadb mysql -uroot

Enter the following commands to change the password for "root":

FLUSH PRIVILEGES;
ALTER USER 'root'@'%' IDENTIFIED BY 'new_password';
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
exit

When you are done, remove the --skip-grant-tables flag again to restore the original command and restart the mariadb service as described above.

Unicode Support

If the logs show "incorrect string value" database errors and you are running a custom MariaDB or MySQL server that is not based on our default configuration:

  • Full Unicode support must be enabled, e.g. using the mysqld command parameters --character-set-server=utf8mb4 and --collation-server=utf8mb4_unicode_ci
  • Note that an existing database may use a different character set if you imported it from another server
  • Before submitting a support request, verify the problem still occurs with a newly created database based on our example

Run this command in a terminal to see the current values of the collation and character set variables (change the root password insecure and database name photoprism as specified in your compose.yaml or docker-compose.yml):

echo "SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';" | \
docker compose exec -T mariadb mysql -uroot -pinsecure photoprism

MySQL Errors

Official support for MySQL 8 is discontinued as Oracle seems to have stopped shipping new features and enhancements. As a result, the testing effort required before each release is no longer feasible.