Once again: migration from SQLite to MariaDB

[sc911]

Like others, I'm now in the situation where I started with the default SQLite database and now need to switch to MariaDB for performance reasons.

I do know #495 and https://docs.photoprism.org/getting-started/advanced/databases/#sqlite-to-mariadb

But now I'm facing the issue, that database layout seems to have changed from the initial version where the SQLite DB was created (don't know witch version this was) and the current version I used to create the database layout.

What I did, step by step:

1. stopped current photoprism container running version 20210925
2. docker-compose up on this docker-compose.yml to create the database layout:

version: "3.9"
services:
  photoprism-db:
    image: mariadb:10.6
    security_opt:
      - seccomp:unconfined
      - apparmor:unconfined
    command: --transaction-isolation=READ-COMMITTED --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --innodb-read-only-compressed=OFF --max-connections=512 --innodb-rollback-on-timeout=OFF --innodb-lock-wait-timeout=120
    restart: "unless-stopped"
    environment:
      MYSQL_ROOT_PASSWORD: root_pw
      MYSQL_PASSWORD: pw
      MYSQL_DATABASE: photoprism
      MYSQL_USER: photoprism
    volumes:
      - "/share/Docker/photoprism-db:/var/lib/mysql"
  photoprism:
    image: photoprism/photoprism:20210925
    restart: "unless-stopped"
    depends_on:
      - photoprism-db
    environment:
      PHOTOPRISM_DATABASE_DRIVER: "mysql"
      PHOTOPRISM_DATABASE_SERVER: "photoprism-db:3306"
      PHOTOPRISM_DATABASE_NAME: "photoprism"
      PHOTOPRISM_DATABASE_USER: "photoprism"
      PHOTOPRISM_DATABASE_PASSWORD: "pw"

3. CTRL-C when the photoprism webserver was startet
4. docker-compose up with a modified version of the file where /share/Docker/photoprism/storage points to the storage of the old photoprism container:

version: "3.9"
services:
  convert:
    image: python:3
    depends_on:
      - photoprism-db
    entrypoint: ["/bin/sh", "-c", "pip install sqlite3-to-mysql && sqlite3mysql -f /storage/index.db -h photoprism-db -d photoprism -u root --mysql-password 'root_pw'"]
    volumes:
      - "/share/Docker/photoprism/storage:/storage"
  photoprism-db:
    image: mariadb:10.6
    security_opt:
      - seccomp:unconfined
      - apparmor:unconfined
    command: --transaction-isolation=READ-COMMITTED --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --innodb-read-only-compressed=OFF --max-connections=512 --innodb-rollback-on-timeout=OFF --innodb-lock-wait-timeout=120
    restart: "unless-stopped"
    environment:
      MYSQL_ROOT_PASSWORD: root_pw
      MYSQL_PASSWORD: pw
      MYSQL_DATABASE: photoprism
      MYSQL_USER: photoprism
    volumes:
      - "/share/Docker/photoprism-db:/var/lib/mysql"

The convert services gives me the following output:

Collecting sqlite3-to-mysql,
  Downloading sqlite3_to_mysql-1.4.8-py2.py3-none-any.whl (16 kB),
Collecting Unidecode>=1.2.0,
  Downloading Unidecode-1.3.2-py3-none-any.whl (235 kB),
Collecting tqdm>=4.35.0,
  Downloading tqdm-4.62.3-py2.py3-none-any.whl (76 kB),
Collecting Click>=7.0,
  Downloading click-8.0.3-py3-none-any.whl (97 kB),
Collecting packaging>=20.3,
  Downloading packaging-21.0-py3-none-any.whl (40 kB),
Collecting mysql-connector-python>=8.0.18,
  Downloading mysql_connector_python-8.0.26-py2.py3-none-any.whl (324 kB),
Collecting six>=1.12.0,
  Downloading six-1.16.0-py2.py3-none-any.whl (11 kB),
Collecting simplejson>=3.16.0,
  Downloading simplejson-3.17.5-cp310-cp310-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_12_x86_64.manylinux2010_x86_64.whl (136 kB),
Collecting tabulate,
  Downloading tabulate-0.8.9-py3-none-any.whl (25 kB),
Collecting pytimeparse>=1.1.8,
  Downloading pytimeparse-1.1.8-py2.py3-none-any.whl (10.0 kB),
Collecting protobuf>=3.0.0,
  Downloading protobuf-3.18.1-py2.py3-none-any.whl (174 kB),
Collecting pyparsing>=2.0.2,
  Downloading pyparsing-2.4.7-py2.py3-none-any.whl (67 kB),
Installing collected packages: pyparsing, protobuf, Unidecode, tqdm, tabulate, six, simplejson, pytimeparse, packaging, mysql-connector-python, Click, sqlite3-to-mysql,
Successfully installed Click-8.0.3 Unidecode-1.3.2 mysql-connector-python-8.0.26 packaging-21.0 protobuf-3.18.1 pyparsing-2.4.7 pytimeparse-1.1.8 simplejson-3.17.5 six-1.16.0 sqlite3-to-mysql-1.4.8 tabulate-0.8.9 tqdm-4.62.3,
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv,
WARNING: You are using pip version 21.2.4; however, version 21.3 is available.,
You should consider upgrading via the '/usr/local/bin/python -m pip install --upgrade pip' command.,
2021-10-18 20:50:49 INFO     Transferring table details,

  0%|          | 0/8317 [00:00<?, ?it/s]
  1%|          | 79/8317 [00:00<00:10, 786.00it/s]
  3%|▎         | 218/8317 [00:00<00:07, 1138.34it/s]
  4%|▍         | 337/8317 [00:00<00:06, 1158.97it/s]
  5%|▌         | 453/8317 [00:00<00:07, 1084.63it/s]
  7%|▋         | 563/8317 [00:00<00:10, 764.14it/s] 
  8%|▊         | 667/8317 [00:00<00:09, 834.65it/s]
  9%|▉         | 783/8317 [00:00<00:08, 901.35it/s]
 11%|█         | 919/8317 [00:00<00:07, 967.15it/s]
 13%|█▎        | 1055/8317 [00:01<00:07, 984.51it/s]
 14%|█▍        | 1193/8317 [00:01<00:08, 862.38it/s]
 16%|█▌        | 1333/8317 [00:01<00:07, 909.16it/s]
 18%|█▊        | 1461/8317 [00:01<00:06, 994.02it/s]
 19%|█▉        | 1566/8317 [00:01<00:07, 898.37it/s]
 20%|██        | 1676/8317 [00:01<00:07, 945.28it/s]
 21%|██▏       | 1784/8317 [00:01<00:06, 979.47it/s]
 23%|██▎       | 1889/8317 [00:01<00:06, 996.56it/s]
 24%|██▍       | 2018/8317 [00:02<00:06, 1028.65it/s]
 26%|██▌       | 2153/8317 [00:02<00:05, 1058.11it/s]
 28%|██▊       | 2288/8317 [00:02<00:05, 1084.94it/s]
 29%|██▉       | 2423/8317 [00:02<00:05, 1102.81it/s]
 31%|███       | 2542/8317 [00:02<00:05, 1124.79it/s]
 32%|███▏      | 2656/8317 [00:02<00:05, 1110.19it/s]
 33%|███▎      | 2768/8317 [00:02<00:05, 1100.91it/s]
 35%|███▍      | 2879/8317 [00:02<00:05, 1078.58it/s]
 36%|███▌      | 2988/8317 [00:02<00:04, 1074.15it/s]
 37%|███▋      | 3096/8317 [00:03<00:04, 1065.38it/s]
 39%|███▉      | 3224/8317 [00:03<00:04, 1083.66it/s]
 40%|████      | 3364/8317 [00:03<00:04, 1103.13it/s]
 42%|████▏     | 3488/8317 [00:03<00:04, 1138.68it/s]
 43%|████▎     | 3603/8317 [00:03<00:04, 1065.09it/s]
 45%|████▍     | 3711/8317 [00:03<00:04, 1067.92it/s]
 46%|████▌     | 3819/8317 [00:03<00:04, 939.09it/s] 
 47%|████▋     | 3916/8317 [00:03<00:05, 864.99it/s]
 49%|████▊     | 4047/8317 [00:04<00:04, 941.16it/s]
 50%|█████     | 4177/8317 [00:04<00:04, 949.99it/s]
 52%|█████▏    | 4316/8317 [00:04<00:04, 907.13it/s]
 54%|█████▎    | 4450/8317 [00:04<00:03, 1008.50it/s]
 55%|█████▍    | 4555/8317 [00:05<00:10, 368.14it/s] 
 56%|█████▌    | 4648/8317 [00:05<00:08, 432.53it/s]
 57%|█████▋    | 4769/8317 [00:05<00:06, 542.24it/s]
 58%|█████▊    | 4863/8317 [00:05<00:05, 596.23it/s]
 60%|█████▉    | 4975/8317 [00:05<00:04, 694.87it/s]
 62%|██████▏   | 5115/8317 [00:05<00:03, 845.11it/s]
 63%|██████▎   | 5243/8317 [00:05<00:03, 946.26it/s]
 65%|██████▌   | 5422/8317 [00:05<00:02, 1156.68it/s]
 67%|██████▋   | 5592/8317 [00:06<00:02, 1299.12it/s]
 69%|██████▉   | 5737/8317 [00:06<00:01, 1326.74it/s]
 71%|███████   | 5881/8317 [00:06<00:01, 1332.99it/s]
 72%|███████▏  | 6022/8317 [00:06<00:01, 1323.29it/s]
 74%|███████▍  | 6160/8317 [00:06<00:01, 1297.99it/s]
 76%|███████▌  | 6294/8317 [00:06<00:01, 1267.18it/s]
 77%|███████▋  | 6435/8317 [00:06<00:01, 1304.93it/s]
 79%|███████▉  | 6576/8317 [00:06<00:01, 1333.51it/s]
 81%|████████  | 6717/8317 [00:06<00:01, 1353.83it/s]
 82%|████████▏ | 6854/8317 [00:07<00:01, 1357.45it/s]
 84%|████████▍ | 6991/8317 [00:07<00:00, 1356.45it/s]
 86%|████████▌ | 7133/8317 [00:07<00:00, 1373.27it/s]
 87%|████████▋ | 7274/8317 [00:07<00:00, 1382.81it/s]
 89%|████████▉ | 7413/8317 [00:07<00:00, 1383.23it/s]
 91%|█████████ | 7555/8317 [00:07<00:00, 1392.78it/s]
 93%|█████████▎| 7695/8317 [00:07<00:00, 1358.18it/s]
 94%|█████████▍| 7838/8317 [00:07<00:00, 1377.98it/s]
 96%|█████████▌| 7977/8317 [00:07<00:00, 1379.62it/s]
 98%|█████████▊| 8118/8317 [00:07<00:00, 1388.43it/s]
 99%|█████████▉| 8258/8317 [00:08<00:00, 1390.57it/s]
100%|██████████| 8317/8317 [00:08<00:00, 1030.09it/s],
2021-10-18 20:50:57 INFO     Transferring table cells,

  0%|          | 0/816 [00:00<?, ?it/s]
 11%|█▏        | 93/816 [00:00<00:00, 802.61it/s]
 31%|███       | 253/816 [00:00<00:00, 1238.79it/s]
 48%|████▊     | 392/816 [00:00<00:00, 1303.25it/s]
 64%|██████▍   | 525/816 [00:00<00:00, 838.80it/s] 
 79%|███████▉  | 643/816 [00:00<00:00, 909.23it/s]
 94%|█████████▍| 768/816 [00:00<00:00, 970.60it/s]
100%|██████████| 816/816 [00:00<00:00, 1003.37it/s],
2021-10-18 20:50:58 INFO     Transferring table albums,
2021-10-18 20:50:58 ERROR    MySQL transfer failed inserting data into table albums: 1054 (42S22): Unknown column 'cover_uid' in 'field list',
1054 (42S22): Unknown column 'cover_uid' in 'field list',

  0%|          | 0/52 [00:00<?, ?it/s]
  0%|          | 0/52 [00:00<?, ?it/s]

Comparing SQLite (with DB Browser for SQLite) and MariaDB (phpmyadmin) databases shows the different in layout.

My next hope was to you photoprism backup and restore but having a look at the output of backup it seems to be hardly tied to the specific database.

Is there any other way in migrating without starting from the scratch?

And if there is no other way, what is it I will loose? Just time? Or data?

Oh, did I mention that this software is awesome? Writing this issue to improve not to complain!

[lastzero]

Thank you! PhotoPrism migrates existing tables automatically when it starts. Also there is a photoprism migrate command to only run migrations.

[sc911]

And migration seems to be the "problem" here: it seems not to delete obsolete colmns, at least not when migrating from version 20210523 to 20210925.

I did some tests to verify:

1. Starting a brand new instance of 20210523 and then directly updating to 20210925 results in an albums-table containing cover_uid-column, even after running migrate manually.
2. Starting a brand new instance of 20210925 results in an albums-table NOT containing cover_uid-column

So for my inital problem, the solution is to initialize the maridb with 20210523 and then updating to 20210925 (so, repeating the steps I did with the productive SQLite-db). After that I should be able to copy the data using sqlite3-to-mysql (hat no time to test this).

The question is: shouldn't the layout after migration be like a brand new layout? Or is there any case where it is nessecary to keep old columns?

[lastzero]

No, it should not. See Gorm docs. The danger would be that you lose data that was in these columns. The only way is to manually run SQL migration queries which we currently don't have the resources to provide for two different databases every time we release, so weekly. It's a tradeoff and we decided to go for more frequent releases, more time we can spend with support (right now) and feature development.

[sc911]

OK, thanks for your fast answere!
Some points though:

1. Regarding migration: I do have a different view on this: your app, your code , your data model! If you decide that an entity looses a property the information stored in there either gets moved to a more appropriate place or is irrelevant. Either way there is no need to keep the data of an deleted property inaccessible (from a code view) in the database.
2. For me the problem is solved, I was able to rebuild my upgrade path, so I now (hopefully) can migrate my database backend using the script mentioned in the docs. But what if I couldn't remember? What if I started many versions ago? Wouldn't it be a nice feature to either have an buildin converter (like i.e. nextcloud) or to have a database driver independent backup and restore feature (for example dumping the data as JSON)? I'm not (yet) a go coder, so just suggesting without any possibility to help, sorry.
3. I'm not expecting support ( in a professional meaning), this is an open source project, I'm just trying to contribute.

Thanks again!

[lastzero]

You are right that we can improve this. We just don't have unlimited resources and we don't think doing it now will bring the most benefit to our users. Some of the unused columns may be from using development builds, as we try to avoid making changes to columns in our stable builds.

You're welcome to contribute docs or migration scripts for other, less experienced users! 👍

[sc911]

Ok, here is my contribution to the docs. I know there is already one but I hope this is more step by step and it's completely without any other requirements to the host than docker (+compose):

Database migration from SQLite to MariaDB

This guide will show you a way to convert the database of an existing photoprism installation from SQLite to MariaDB.

Prerequisites

To complete this guide successfully, you will need to know the complete upgrade history of your photoprism installation, as you will need to "replay" all database schema changes to the new and empty MariaDB database. Start with the latest available version before you initially installed photoprism and use every version since then as a step.

Let's assume that your current docker-file looks like this:

version: "3.9"
services:
  photoprism:
    image: photoprism/photoprism:20210925
    security_opt:
      - seccomp:unconfined
      - apparmor:unconfined
    ports:
      - "2342:2342"
    volumes:
      - "~/originals:/photoprism/originals"
      - "~/storage:/photoprism/storage"

Step 0

Backup all your data! So shut down your current instance and take a copy of at least the originals and the storage directory.

Step 1

Start by creating an additional docker-compose-file docker-compose.migrate.yml with the following content:

version: "3.9"
services:
  photoprism-db:
    restart: unless-stopped
    image: mariadb:10.6
    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=120
    environment:
      MYSQL_ROOT_PASSWORD: ROOTPW
      MYSQL_PASSWORD: USERPW
      MYSQL_DATABASE: photoprism
      MYSQL_USER: photoprism
    volumes:
      - "~/database:/var/lib/mysql"
  photoprism-migrate:
    image: photoprism/photoprism:YOURFIRSTVERSION
    command: photoprism migrate
    depends_on:
      - photoprism-db
    environment:
      PHOTOPRISM_DATABASE_DRIVER: "mysql"
      PHOTOPRISM_DATABASE_SERVER: "photoprism-db:3306"
      PHOTOPRISM_DATABASE_NAME: "photoprism"
      PHOTOPRISM_DATABASE_USER: "photoprism"
      PHOTOPRISM_DATABASE_PASSWORD: "USERPW"
  convert:
    image: python:3
    depends_on:
      - photoprism-db
    entrypoint: ["/bin/sh", "-c", "pip install sqlite3-to-mysql && sqlite3mysql -f /storage/index.db -h photoprism-db -d photoprism -u root --mysql-password 'UoZzB4i7GFTs6dog'"]
    volumes:
      - "~/storage:/storage"

Make sure that the host part of the volume of the service convert matches the one in your current photoprism instance.

Replace ROOTPW and USERPW by some random passwords.

Replace YOURFIRSTVERSION with the docker tag of the version photoprism was initially installed, for example 20210505.

Download all images by running:

docker-compose -f docker-compose.migrate.yml pull

Step 2

Now run the following commands to initialize MariaDB:

docker-compose -f docker-compose.migrate.yml up -d photoprism-db
docker-compose -f docker-compose.migrate.yml logs -f photoprism-db

If you see the message [Note] mysqld: ready for connections. press CTRL+C to exit the logs.

Step 3

Now setup the photoprism database scheme in the empty database. Run:

docker-compose -f docker-compose.migrate.yml up photoprism-migrate

It will stop after the database scheme is ready.

Step 4

Now replace the version tag in the migration docker-compose file with the next following version tag an re-run the command above. Do this for every version until your current version.

Step 5

Now that the scheme of both your current SQLite database and the future MariaDB are the same, you can migrate all data from the one to the other.

Before doing this, make sure your current instance of photoprism is shut down.

To copy your data, run the following command:

docker-compose -f docker-compose.migrate.yml up convert

This will copy each and every table and its contents and will tell you the progress. It stops when it's done.

If there is an error about a missing table you have probably missed one version in your upgrade path.

Step 6

Now stop your MariaDB by running:

docker-compose -f docker-compose.migrate.yml stop photoprism-db

Copy the complete service photoprism-db from docker-compose.migrate.yml to your actual docker-file and also add the environments starting with PHOTOPRISM_DATABASE_ from photoprism-migrate. Also add the depends_on block.

So it will look like this afterwards:

version: "3.9"
services:
  photoprism:
    image: photoprism/photoprism:20210925
    security_opt:
      - seccomp:unconfined
      - apparmor:unconfined
    ports:
      - "2342:2342"
    depends_on:
      - photoprism-db
    environment:
      PHOTOPRISM_DATABASE_DRIVER: "mysql"
      PHOTOPRISM_DATABASE_SERVER: "photoprism-db:3306"
      PHOTOPRISM_DATABASE_NAME: "photoprism"
      PHOTOPRISM_DATABASE_USER: "photoprism"
      PHOTOPRISM_DATABASE_PASSWORD: "USERPW"
    volumes:
      - "~/originals:/photoprism/originals"
      - "~/storage:/photoprism/storage"
  photoprism-db:
    restart: unless-stopped
    image: mariadb:10.6
    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=120
    environment:
      MYSQL_ROOT_PASSWORD: ROOTPW
      MYSQL_PASSWORD: USERPW
      MYSQL_DATABASE: photoprism
      MYSQL_USER: photoprism
    volumes:
      - "~/database:/var/lib/mysql"

Run:

docker-compose -f docker-compose.migrate.yml down

Afterwards delete your docker-compose.migrate.yml, as you wont need it any more.

Step 7

Now you can start up your photoprim instance using docker-compose and everything should be as before, but faster ;)