Troubleshooting MariaDB Problems¶
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:10.11 ...
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
127.0.0.1if 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
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", 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
If the password you are using was specified in a
docker-compose.yml file and contains one or more
$ characters, these must be escaped with
$$ (a double dollar sign) so that, for example,
services: mariadb: environment: # sets password to "compo$e" MARIADB_PASSWORD: "compo$$e"
Also note that it is not possible to 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.
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.
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.
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
docker-compose.yml when prompted.
Alternatively, you can downgrade to the previous version, create a database backup using the
command, start a new database instance based on the latest version, and then restore your index with
photoprism restore command.
To enable automatic schema updates, set
MARIADB_AUTO_UPGRADE to a non-empty value in your
docker-compose.yml as shown in our config example:
services: mariadb: image: mariadb:10.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.
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
docker compose exec photoprism prefix if you are using an interactive terminal session or are running PhotoPrism directly on your computer without Docker.
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
-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.
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.
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.
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
added to the
mysqld command in your
docker-compose.yml. This will temporarily give full access
to all users after a restart:
services: mariadb: command: mysqld --skip-grant-tables
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'; UPDATE mysql.user SET authentication_string = '' WHERE user = 'root'; UPDATE mysql.user SET plugin = '' WHERE user = 'root'; 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.
When moving MariaDB to another computer, cloud server, or virtual machine:
- Move the complete storage folder along with it and preserve the file permissions
- or restore your index from an SQL dump (backup file)
- Perform a version upgrade if necessary
- Make sure that PhotoPrism can access the database on the new host
- Set strong passwords if the database is exposed to an external network
- Never expose your database to the public Internet
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
- 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
insecure and database name
photoprism as specified in your
echo "SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';" | \ docker compose exec -T mariadb mysql -uroot -pinsecure photoprism
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.