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

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

Author

Owner

@lastzero commented on GitHub (Nov 28, 2021):

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

Related to:

#319

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:

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 commented on GitHub (Nov 28, 2021): Oh wow, MySQL supports `IF EXISTS` for about everything - but not for indexes? Even SQLite supports it. Related to: - #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.

deekerman commented

Author

Owner

@lastzero commented on GitHub (Nov 28, 2021):

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

@lastzero commented on GitHub (Nov 28, 2021): Would it be an option to use MariaDB 10.6 instead? Any features only MySQL 8 has?

deekerman commented

Author

Owner

@lastzero commented on GitHub (Nov 28, 2021):

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.

@lastzero commented on GitHub (Nov 28, 2021): 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.

deekerman commented

Author

Owner

@hulmgulm commented on GitHub (Nov 28, 2021):

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.

@hulmgulm commented on GitHub (Nov 28, 2021): 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.

deekerman commented

Author

Owner

@lastzero commented on GitHub (Nov 28, 2021):

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

@lastzero commented on GitHub (Nov 28, 2021): It's critical in that the column is NOT UNIQUE anymore. So you will get INSERT / UPDATE errors when updating places.

deekerman commented

Author

Owner

@lastzero commented on GitHub (Nov 28, 2021):

Should be "fixed" with this: github.com/photoprism/photoprism@7e8974fd20

@lastzero commented on GitHub (Nov 28, 2021): Should be "fixed" with this: https://github.com/photoprism/photoprism/commit/7e8974fd20fcbf3ec403541125599deaeb1ee353

deekerman commented

Author

Owner

@lastzero commented on GitHub (Nov 28, 2021):

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

@lastzero commented on GitHub (Nov 28, 2021): Started a new Development Preview build for testing! Should be available within the next hour...

deekerman commented

Author

Owner

@lastzero commented on GitHub (Nov 28, 2021):

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 commented on GitHub (Nov 28, 2021): 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.

deekerman commented

Author

Owner

@lastzero commented on GitHub (Nov 28, 2021):

New Development Preview has been uploaded to Docker Hub.

@lastzero commented on GitHub (Nov 28, 2021): New Development Preview has been uploaded to Docker Hub.

deekerman commented

Author

Owner

@lastzero commented on GitHub (Nov 28, 2021):

Released the fix. Hope it works for everyone.

@lastzero commented on GitHub (Nov 28, 2021): Released the fix. Hope it works for everyone.

deekerman commented

Author

Owner

@lastzero commented on GitHub (Nov 28, 2021):

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

@lastzero commented on GitHub (Nov 28, 2021): Announcement: https://twitter.com/photoprism_app/status/1464983836836704263

deekerman commented

Author

Owner

@hulmgulm commented on GitHub (Nov 28, 2021):

I've switched from MySQL to MariaDB for PhotoPrism.

Thank you for your help and timely response.

@hulmgulm commented on GitHub (Nov 28, 2021): I've switched from MySQL to MariaDB for PhotoPrism. Thank you for your help and timely response.

deekerman commented

Author

Owner

@polhaghverdian commented on GitHub (May 28, 2022):

@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]"

@polhaghverdian commented on GitHub (May 28, 2022): @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]" ```

deekerman commented

Author

Owner

@lastzero commented on GitHub (May 28, 2022):

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

@lastzero commented on GitHub (May 28, 2022): With MySQL 8? It's not supported anymore, see docs.

deekerman commented

Author

Owner

@polhaghverdian commented on GitHub (May 28, 2022):

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

Aha, didn't know that. Sorry!

@polhaghverdian commented on GitHub (May 28, 2022): > With MySQL 8? It's not supported anymore, see docs. Aha, didn't know that. Sorry!

deekerman commented