MySQL and MariaDB

InvokeAI keeps its boards, image records, workflows, projects, queue and accounts in a database. By default that is a SQLite file in db_dir, which needs no setup. On a server that hosts InvokeAI for several people, the database can be a MySQL or MariaDB database instead, run and backed up alongside the server’s other databases.

Requirements

• MySQL 8.4 or newer, or MariaDB 10.11 or newer, with the InnoDB storage engine.

• max_allowed_packet of at least 64 MiB on the server. MariaDB’s default is 16 MiB; InvokeAI refuses to start below 64 MiB, because a large project is stored in one statement. Set it in the server’s configuration and restart the server:

  [mysqld]
  max_allowed_packet=64M

• The mysql extra of the invokeai package, which installs the PyMySQL driver: pip install "invokeai[mysql]", or uv sync --extra mysql in a source checkout. The Docker image includes it.

Creating the database

Create an empty database and an account for InvokeAI. InvokeAI creates its tables, each with the character set and collations it needs, when it first starts.

MySQL:

CREATE DATABASE invokeai CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin;
CREATE USER 'invokeai'@'%' IDENTIFIED BY 'a-strong-password';
GRANT ALL PRIVILEGES ON invokeai.* TO 'invokeai'@'%';

MariaDB:

CREATE DATABASE invokeai CHARACTER SET utf8mb4 COLLATE utf8mb4_nopad_bin;
CREATE USER 'invokeai'@'%' IDENTIFIED BY 'a-strong-password';
GRANT ALL PRIVILEGES ON invokeai.* TO 'invokeai'@'%';

Configuring InvokeAI

Set db_url in invokeai.yaml, or the INVOKEAI_DB_URL environment variable:

db_url: mariadb+pymysql://invokeai:[email protected]:3306/invokeai

Use mysql+pymysql:// for MySQL and mariadb+pymysql:// for MariaDB. Characters in the password that have a meaning in a URL (@, :, /, %, #) must be percent-encoded, e.g. @ as %40. InvokeAI reads db_url when it starts, and shows it without the password in its settings. db_dir and db_synchronous apply to a SQLite database only.

On its first start InvokeAI creates the tables. On later starts it updates them to its version, as it does a SQLite database. It makes no backup of a server database before an update: back the database up yourself, e.g. with mysqldump or mariadb-dump, before upgrading InvokeAI.

Moving an existing install

invoke-db-copy copies an install’s SQLite database into a new, empty server database. It copies the database only: images, models and the other files stay where they are, in the root directory. The SQLite database is not changed.

1. Stop InvokeAI. What it writes during the copy is not copied.

2. Set db_url in invokeai.yaml (or INVOKEAI_DB_URL) to the new database. Do not start InvokeAI yet: it would start the new database empty.

3. Check the copy, which copies nothing yet:

   invoke-db-copy --check

4. Copy:

   invoke-db-copy

5. Start InvokeAI.

invoke-db-copy takes the install it runs in, or the one --root names, and copies into the database its db_url names. --target URL names another one; a password on the command line is visible to the other users of the computer and stays in the shell’s history.

It copies from a snapshot of the SQLite database, which it makes in the database’s directory and deletes afterwards: that needs free space as large as the database. It brings the snapshot to no newer version: if InvokeAI has not started since an upgrade, start it once first. The target must be empty, for the check too, and no InvokeAI process may use it.

It stops before copying anything when the server would refuse a row:

• Rows whose foreign keys name a row that is gone, which a SQLite database can hold and a server refuses. --orphans skip copies them without the reference where the database would clear it anyway (a board whose cover image is gone keeps no cover), and leaves out the others, together with what the database deletes with them. The report lists what it left out, per table.
• Values longer than the server’s column holds, and rows larger than half of max_allowed_packet.

Two kinds of value are copied in the form a server stores:

• Vocabulary terms of the image index that differ only in the case of a letter outside A-Z (Äpfel and äpfel), which the server compares as equal: it keeps one of them.
• A model’s file size stored with a fraction, which is written as a whole number of bytes.

After copying, invoke-db-copy compares every table of the copy with its source, row by row, and only then writes the records that make it a database InvokeAI takes. If the copy fails or does not match, InvokeAI refuses the target: drop the database, create it again empty, and run invoke-db-copy again.

Going back to SQLite

invoke-db-copy --to-sqlite FILE copies the server database back into a new SQLite file. Removing db_url alone is not a way back: InvokeAI then opens the SQLite database it used before the move, as it was then. The boards, image records, workflows and accounts made since are not in it, and the gallery no longer shows the images made since, although their files are in the outputs folder.

1. Stop InvokeAI. invoke-db-copy refuses to copy a database an InvokeAI process uses.

2. Copy into a new file:

   invoke-db-copy --to-sqlite invokeai-from-server.db

3. In the install’s database directory (db_dir, by default databases in the root), rename invokeai.db, which holds the state before the move, and put the new file in its place, named invokeai.db.

4. Remove db_url from invokeai.yaml, and INVOKEAI_DB_URL from the environment, and start InvokeAI.

The source is the database db_url names, unless --source URL names another; it is not changed. The new file appears only once every table matches its source, so a copy that fails leaves no file behind, and invoke-db-copy never overwrites an existing file. Ids continue as they did on the server: a queue item gets no id the server already gave out.

Limits

• One InvokeAI process per database. At startup a process cancels the queue items left running and updates its bundled workflows and style presets, which a second process would undo. The first process holds a lock on the database; a second one refuses to start. If a process ends without closing its connection, because its computer crashed or lost the network, the server releases its lock within ten minutes; the refusal names the query that finds the connection to end sooner with KILL.
• One database server. Setups with several writing servers (Galera, group replication with several primaries) are not supported.
• Busy database. When a request waits too long for a lock held by another, finds no free connection, or cannot reach the server, it fails with HTTP 503 and Retry-After: 1, and can be repeated. If this happens often, the server is overloaded or another program holds locks in InvokeAI’s database.
• Command-line tools. The account commands (invoke-useradd, invoke-userdel, invoke-usermod, invoke-userlist) and the maintenance scripts use the database invokeai.yaml names. They refuse a database InvokeAI has not updated to their version: start InvokeAI once after an upgrade before using them.