MySQL 8: SQL Error on Database Migration, IF EXISTS not supported

[hulmgulm]

Updating from photoprism/photoprism 212018 docker image to 211127, the database migration fails with the following errors:

time="2021-11-28T05:10:21Z" level=error msg="migration 20211121-094727 failed: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS uix_places_place_label ON places' at line 1 [3.510832ms]"
time="2021-11-28T05:10:21Z" level=error msg="migration 20211124-120008 failed: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS idx_places_place_label ON places' at line 1 [2.024121ms]"

Database: MySQL 8.0.27

[lastzero]

Oh wow, MySQL supports IF EXISTS for about everything - but not for indexes? Even SQLite supports it.

Related to:

• Database: Add manual schema migrations #319

See also https://stackoverflow.com/questions/2480148/how-can-i-employ-if-exists-for-creating-or-dropping-an-index-in-mysql

As a workaround, you can manually run these statements without IF EXISTS or otherwise drop the index on the label column of the places table:

https://github.com/photoprism/photoprism/tree/develop/internal/migrate/mysql

Not happy we need to spend time writing complicated special migrations for MySQL 8. Indexes were created by the ORM in earlier versions, so we don't know 100% what the actual name is.

[lastzero]

Would it be an option to use MariaDB 10.6 instead? Any features only MySQL 8 has?

[lastzero]

Guess we could also ignore errors in the migration package if it's a drop statements, however that's dangerous as there can be other errors like invalid SQL too.

[hulmgulm]

Thank you for your help. Only one of these three indexes existed. As the only migration steps were index drops, I don't think this is critical. I was worried that other DB changes were missing and PhotoPrism might not work as intended.
I've chose MySQL 8 because I had it already running.

[lastzero]

It's critical in that the column is NOT UNIQUE anymore. So you will get INSERT / UPDATE errors when updating places.

[lastzero]

Should be "fixed" with this: 7e8974f

[lastzero]

Started a new Development Preview build for testing! Should be available within the next hour...

[lastzero]

Updated the System Requirements as we currently don't have time to run all tests on MySQL 8 before every release:
https://docs.photoprism.org/getting-started/#databases

Let us know when you run into issues and we do our best to help.

[lastzero]

New Development Preview has been uploaded to Docker Hub.

[lastzero]

Released the fix. Hope it works for everyone.

[lastzero]

Announcement: https://twitter.com/photoprism_app/status/1464983836836704263

[hulmgulm]

I've switched from MySQL to MariaDB for PhotoPrism.

Thank you for your help and timely response.

[polhaghverdian]

@lastzero I have this exactly same error with release photoprism:220527-jammy

time="2022-05-28T18:59:15Z" level=error msg="migrate: executing 20220329-061000 failed with Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INDEX idx_files_photo_id ON files (photo_id, file_primary)' at line 1 [24.516502ms]"

time="2022-05-28T18:59:15Z" level=error msg="migrate: executing 20220329-070000 failed with Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS photo_taken_at DATETIME AFTER photo_uid' at line 1 [25.199829ms]"

time="2022-05-28T18:59:15Z" level=error msg="migrate: executing 20220329-080000 failed with Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS media_id VARBINARY(32) AFTER photo_taken_at' at line 1 [24.620146ms]"

time="2022-05-28T18:59:15Z" level=error msg="migrate: executing 20220329-081000 failed with Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNIQUE INDEX idx_files_search_media ON files (media_id)' at line 1 [24.425709ms]"

time="2022-05-28T18:59:15Z" level=error msg="migrate: executing 20220329-090000 failed with Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS time_index VARBINARY(48) AFTER photo_taken_at' at line 1 [25.906621ms]"

time="2022-05-28T18:59:15Z" level=error msg="migrate: executing 20220329-091000 failed with Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNIQUE INDEX idx_files_search_timeline ON files (time_index)' at line 1 [23.6379ms]"

time="2022-05-28T18:59:15Z" level=error msg="migrate: executing 20220421-200000 failed with Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INDEX idx_files_missing_root ON files (file_missing, file_root)' at line 1 [24.419842ms]"

[lastzero]

With MySQL 8? It's not supported anymore, see docs.

[polhaghverdian]

With MySQL 8? It's not supported anymore, see docs.

Aha, didn't know that. Sorry!

[lastzero]

Requires too many resources to completely test every release and add workarounds for MySQL 8, especially since we noticed that only very few users are (still) using it and can't upgrade....