MariaDB: Make sure that a supported version is used #2381

ZoXx · 2022-05-30T11:37:36Z

Hello,

since the last update (docker image) we've got problems while uploading pictures to folders (albums).

Here is our log:
2022-05-30 12:45:18 ERRO file: Error 1292: Incorrect datetime value: '0000-00-00' for column 'photo_taken_at' at row 1 while saving

graciousgrey · 2022-05-30T12:00:52Z

Please follow our issue template for reporting potential bugs :)

ZoXx · 2022-05-30T12:08:52Z

Will do next time.
But your template is a lot to do and this bug exist since last image. I dont know how you can reproduce it but i cant be the only one with this behaviour. The other "bugs" doesnt fill in template too.

graciousgrey · 2022-05-30T12:39:24Z

But your template is a lot to do

This also applies to the development of this software and answering questions.

Uploading photos to albums works without problems on our public demo and private instances.
So it could be a problem with your setup or files.

graciousgrey · 2022-05-30T13:18:22Z

Might also be caused by a failed migration - could you run the following command

docker-compose exec photoprism photoprism migrations ls

to see if there have been errors

ZoXx · 2022-05-30T13:29:02Z

Here we go:
`
time="2022-05-30T13:27:57Z" time="2022-05-30T13:27:57Z" |-----------------| | ID |-----------------| | 20211121-094727 | mysql | 20211124-120008 | mysql | 20220329-030000 | mysql | 20220329-040000 | mysql | | | | | | | | 20220329-050000 | mysql | 20220329-060000 | mysql | 20220329-061000 | mysql | | | | | | | | 20220329-070000 | mysql | | | | | | | | 20220329-071000 | mysql | 20220329-080000 | mysql | | | | | | | | 20220329-081000 | mysql | | | | | | | | 20220329-083000 | mysql | 20220329-090000 | mysql | | | | | | | | 20220329-091000 | mysql | | | | | | | | 20220329-093000 | mysql | 20220421-200000 | mysql | | | | | | | | 20220521-000001 | mysql | 20220521-000002 | mysql | 20220521-000003 | mysql |-----------------| level=info msg="PhotoPrism® needs your support!"
level=info msg="Visit https://photoprism.app/membership to learn more."
---------|---------------------|---------------------|--------------------------------|
| Dialect | Started At | Finished At | Status |
---------|---------------------|---------------------|--------------------------------|
| 2022-02-26 22:16:19 | 2022-02-26 22:16:19 | OK |
| 2022-02-26 22:16:19 | 2022-02-26 22:16:19 | OK |
| 2022-05-17 03:02:35 | 2022-05-17 03:02:35 | OK |
| 2022-05-17 03:02:35 | - | 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_albums_album_filter ON |
| | | | albums' at line 1 |
| 2022-05-17 03:02:35 | 2022-05-17 03:02:37 | OK |
| 2022-05-17 03:02:37 | 2022-05-17 03:02:40 | OK |
| 2022-05-17 03:02:40 | - | 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 |
| 2022-05-17 03:02:40 | - | 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 |
| 2022-05-17 03:02:40 | 2022-05-17 03:02:41 | OK |
| 2022-05-17 03:02:41 | - | 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 |
| 2022-05-17 03:02:41 | - | 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 |
| 2022-05-17 03:02:41 | 2022-05-17 03:02:41 | OK |
| 2022-05-17 03:02:41 | - | 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 |
| 2022-05-17 03:02:41 | - | 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 |
| 2022-05-17 03:02:41 | 2022-05-17 03:02:42 | OK |
| 2022-05-17 03:02:42 | - | 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 |
| 2022-05-25 03:02:43 | 2022-05-25 03:02:44 | OK |
| 2022-05-25 03:02:44 | 2022-05-25 03:02:45 | OK |
| 2022-05-25 03:02:45 | 2022-05-25 03:02:45 | OK |
---------|---------------------|---------------------|--------------------------------|

time="2022-05-30T13:27:57Z" level=info msg="closed database connection"

ZoXx · 2022-05-30T15:28:26Z

So it's an official bug? The tag was set at least

graciousgrey · 2022-05-30T15:53:13Z

Possibly, @lastzero will have a look as soon as possible

gyto6 · 2022-05-30T22:12:24Z

Did you check this command in MariaDB? It stopped the trouble to me.

mysql_upgrade --user=root --password=<root_pwd>

lastzero · 2022-05-31T04:13:29Z

Please verify if you are using a supported mariadb version >= 10.5.12 and its service config includes the following environment variables as shown in our docker-compose.yml examples on dl.photoprism.app:

environment:
      MARIADB_AUTO_UPGRADE: "1"
      MARIADB_INITDB_SKIP_TZINFO: "1"

If you do not use auto upgrade, our Troubleshooting Guide includes a section about MariaDB that explains how to upgrade it manually.

ZoXx · 2022-05-31T07:24:40Z

Please verify if you are using a supported mariadb version >= 10.5.12 and its service config includes the following environment variables as shown in our docker-compose.yml examples on dl.photoprism.app:
environment:
      MARIADB_AUTO_UPGRADE: "1"
      MARIADB_INITDB_SKIP_TZINFO: "1"
If you do not use auto upgrade, our Troubleshooting Guide includes a section about MariaDB that explains how to upgrade it manually.

iam using my own mysql docker database.
Latest docker image with mysql version 8.0.29

lastzero · 2022-05-31T09:35:57Z

👉 https://docs.photoprism.app/getting-started/troubleshooting/#mysql-errors

ZoXx · 2022-05-31T09:38:10Z

👉 https://docs.photoprism.app/getting-started/troubleshooting/#mysql-errors

MySQL Errors¶
↪ Official support for MySQL 8 is discontinued

So not longer MYSQL Support and i have to switch to MariaDB?

ZoXx · 2022-05-31T10:42:08Z

We switched from MYSQL to MariaDB and imported our sql database.
Now we can upload photos again but we cant create albums? There is no error in logs.

ZoXx · 2022-05-31T12:36:33Z

The folders are created in the database, but they are not visible in Photoprism. In Photoprism there are no errors in the log. Also an indexing brings here no success.

There are no more problems with the images.

see photoprism/photoprism#2381 Signed-off-by: Michael Mayer <michael@photoprism.app>

lastzero · 2022-05-31T18:03:16Z

👉 https://docs.photoprism.app/getting-started/troubleshooting/mariadb/#incompatible-schema

ZoXx · 2022-06-01T11:57:50Z

👉 https://docs.photoprism.app/getting-started/troubleshooting/mariadb/#incompatible-schema

docker exec photoprism photoprism migrations ls
time="2022-06-01T06:32:10Z" level=info msg="PhotoPrism® needs your support!"
time="2022-06-01T06:32:10Z" level=info msg="Visit https://photoprism.app/membership to learn more."
|-----------------|---------|---------------------|---------------------|--------------------------------|
| ID | Dialect | Started At | Finished At | Status |
|-----------------|---------|---------------------|---------------------|--------------------------------|
| 20211121-094727 | mysql | 2022-02-26 22:16:19 | 2022-02-26 22:16:19 | OK |
| 20211124-120008 | mysql | 2022-02-26 22:16:19 | 2022-02-26 22:16:19 | OK |
| 20220329-030000 | mysql | 2022-05-17 03:02:35 | 2022-05-17 03:02:35 | OK |
| 20220329-040000 | mysql | 2022-05-17 03:02:35 | - | 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_albums_album_filter ON |
| | | | | albums' at line 1 |
| 20220329-050000 | mysql | 2022-05-17 03:02:35 | 2022-05-17 03:02:37 | OK |
| 20220329-060000 | mysql | 2022-05-17 03:02:37 | 2022-05-17 03:02:40 | OK |
| 20220329-061000 | mysql | 2022-05-17 03:02:40 | - | 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 |
| 20220329-070000 | mysql | 2022-05-17 03:02:40 | - | 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 |
| 20220329-071000 | mysql | 2022-05-17 03:02:40 | 2022-05-17 03:02:41 | OK |
| 20220329-080000 | mysql | 2022-05-17 03:02:41 | - | 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 |
| 20220329-081000 | mysql | 2022-05-17 03:02:41 | - | 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 |
| 20220329-083000 | mysql | 2022-05-17 03:02:41 | 2022-05-17 03:02:41 | OK |
| 20220329-090000 | mysql | 2022-05-17 03:02:41 | - | 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 |
| 20220329-091000 | mysql | 2022-05-17 03:02:41 | - | 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 |
| 20220329-093000 | mysql | 2022-05-17 03:02:41 | 2022-05-17 03:02:42 | OK |
| 20220421-200000 | mysql | 2022-05-17 03:02:42 | - | 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 |
| 20220521-000001 | mysql | 2022-05-25 03:02:43 | 2022-05-25 03:02:44 | OK |
| 20220521-000002 | mysql | 2022-05-25 03:02:44 | 2022-05-25 03:02:45 | OK |
| 20220521-000003 | mysql | 2022-05-25 03:02:45 | 2022-05-25 03:02:45 | OK |
|-----------------|---------|---------------------|---------------------|--------------------------------|

time="2022-06-01T06:32:10Z" level=info msg="closed database connection"

docker exec photoprism photoprism migrations run -f
time="2022-06-01T06:33:43Z" level=info msg="PhotoPrism® needs your support!"
time="2022-06-01T06:33:43Z" level=info msg="Visit https://photoprism.app/membership to learn more."
time="2022-06-01T06:33:43Z" level=info msg="migrate: running previously failed migrations"
time="2022-06-01T06:33:43Z" level=info msg="migrating database schema..."
time="2022-06-01T06:33:44Z" level=info msg="migrate: 20220329-040000 successful [47.839887ms]"
time="2022-06-01T06:33:44Z" level=info msg="migrate: 20220329-061000 successful [44.680234ms]"
time="2022-06-01T06:33:44Z" level=info msg="migrate: 20220329-070000 successful [33.164792ms]"
time="2022-06-01T06:33:44Z" level=info msg="migrate: 20220329-080000 successful [24.364695ms]"
time="2022-06-01T06:33:44Z" level=info msg="migrate: 20220329-081000 successful [32.74057ms]"
time="2022-06-01T06:33:44Z" level=info msg="migrate: 20220329-090000 successful [30.963455ms]"
time="2022-06-01T06:33:44Z" level=info msg="migrate: 20220329-091000 successful [33.414971ms]"
time="2022-06-01T06:33:44Z" level=info msg="migrate: 20220421-200000 successful [34.272154ms]"
time="2022-06-01T06:33:44Z" level=info msg="migration completed in 1.188280732s"
time="2022-06-01T06:33:44Z" level=info msg="closed database connection"

docker exec photoprism photoprism index -f
runs too. Updated a lot of and matched.

-> Now it is working. Thank you very much!!!

lastzero · 2022-06-01T12:00:17Z

You're welcome! 👉 https://photoprism.app/membership

Signed-off-by: Michael Mayer <michael@photoprism.app>

lastzero · 2022-10-08T16:53:34Z

After this happened again several times, we decided to display an error message with a link to the documentation:

$ photoprism --db-server mysql:4001 migrate
ERRO[2022-10-08T16:34:52Z] config: MySQL 8.0.27 is not supported, please upgrade
to MariaDB 10.5.12 or later (https://docs.photoprism.app/getting-started/#databases)

lastzero · 2022-11-04T11:44:23Z

Nobody complained so far, so I'll move ahead and close this as done!

graciousgrey added the incomplete Cannot be processed due to missing information label May 30, 2022

graciousgrey assigned lastzero May 30, 2022

graciousgrey added bug Something isn't working and removed incomplete Cannot be processed due to missing information labels May 30, 2022

graciousgrey added the needs-analysis Requires further investigation label May 30, 2022

graciousgrey added technical-support Support request and removed bug Something isn't working needs-analysis Requires further investigation labels May 31, 2022

graciousgrey closed this as completed May 31, 2022

lastzero added a commit to photoprism/photoprism-docs that referenced this issue May 31, 2022

Getting Started: Add migration commands for MariaDB

3decdac

see photoprism/photoprism#2381 Signed-off-by: Michael Mayer <michael@photoprism.app>

lastzero added a commit to photoprism/photoprism-docs that referenced this issue May 31, 2022

Getting Started: Add Complete Rescan to mariadb.md

0179331

see photoprism/photoprism#2381 Signed-off-by: Michael Mayer <michael@photoprism.app>

lastzero changed the title ~~Error 1292: Incorrect datetime value: '0000-00-00'~~ MariaDB: Make sure that a supported version is used Oct 8, 2022

lastzero added the enhancement Refactoring, improvement or maintenance task label Oct 8, 2022

lastzero added a commit that referenced this issue Oct 8, 2022

MariaDB: Show error if an unsupported server version is used #2381

ff8bc9f

Signed-off-by: Michael Mayer <michael@photoprism.app>

lastzero reopened this Oct 8, 2022

graciousgrey added the please-test Ready for acceptance test label Oct 23, 2022

lastzero mentioned this issue Nov 2, 2022

MariaDB: Exit with error if version is < 10.5.12 #2845

Closed

lastzero added released Available in the stable release and removed please-test Ready for acceptance test labels Nov 4, 2022

lastzero closed this as completed Nov 4, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

MariaDB: Make sure that a supported version is used #2381

MariaDB: Make sure that a supported version is used #2381

ZoXx commented May 30, 2022

graciousgrey commented May 30, 2022

ZoXx commented May 30, 2022

graciousgrey commented May 30, 2022

graciousgrey commented May 30, 2022

ZoXx commented May 30, 2022 •

edited

Loading

ZoXx commented May 30, 2022

graciousgrey commented May 30, 2022

gyto6 commented May 30, 2022

lastzero commented May 31, 2022 •

edited

Loading

ZoXx commented May 31, 2022 •

edited

Loading

lastzero commented May 31, 2022

ZoXx commented May 31, 2022

ZoXx commented May 31, 2022 •

edited

Loading

ZoXx commented May 31, 2022

lastzero commented May 31, 2022

ZoXx commented Jun 1, 2022

lastzero commented Jun 1, 2022

lastzero commented Oct 8, 2022

lastzero commented Nov 4, 2022

MariaDB: Make sure that a supported version is used #2381

MariaDB: Make sure that a supported version is used #2381

Comments

ZoXx commented May 30, 2022

graciousgrey commented May 30, 2022

ZoXx commented May 30, 2022

graciousgrey commented May 30, 2022

graciousgrey commented May 30, 2022

ZoXx commented May 30, 2022 • edited Loading

ZoXx commented May 30, 2022

graciousgrey commented May 30, 2022

gyto6 commented May 30, 2022

lastzero commented May 31, 2022 • edited Loading

ZoXx commented May 31, 2022 • edited Loading

lastzero commented May 31, 2022

ZoXx commented May 31, 2022

ZoXx commented May 31, 2022 • edited Loading

ZoXx commented May 31, 2022

lastzero commented May 31, 2022

ZoXx commented Jun 1, 2022

lastzero commented Jun 1, 2022

lastzero commented Oct 8, 2022

lastzero commented Nov 4, 2022

ZoXx commented May 30, 2022 •

edited

Loading

lastzero commented May 31, 2022 •

edited

Loading

ZoXx commented May 31, 2022 •

edited

Loading

ZoXx commented May 31, 2022 •

edited

Loading