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.
Cannot Connect¶
First, verify that you are using the correct port (default is 3306
) and hostname or IP address:
- in the Docker environment, the default hostname is
mariadb
(same as the service name) - use
localhost
on your host if the port has been exposed as described below - use your private host IP from inside your home network (see network settings)
To connect to MariaDB from your host or home network, you need to expose port 3306
in your 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", 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
Bad Performance¶
Version Upgrade¶
If MariaDB fails 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.
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 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 docker-compose.yml
as shown in our config example:
services:
...
mariadb:
image: mariadb:10.8
...
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.
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.
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 docker-compose.yml
. This will temporarily give full access
to all users after a restart:
services:
mariadb:
command: mysqld --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';
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.
Corrupted Files¶
If 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.
- 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)
Server Relocation¶
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
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 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 improvements. As a result, the testing effort required before each release is no longer feasible.