Query breaks in strict compilers, or Mariadb in only_full_group_by mode #1713

New issue

Open

opened 2026-02-20 00:19:40 -05:00 by deekerman · 1 comment

deekerman commented

2026-02-20 00:19:40 -05:00

Owner

Originally created by @dankasak on GitHub (Feb 20, 2023).

1. What is not working as documented?

Databases that are stricter than old MySQL / Mariadb throw an error on the query generated from:
https://github.com/dankasak/photoprism/blob/develop/internal/config/client_config.go#L613

2. How can we reproduce it?

Install into a MySQL compatible database in only_full_group_by mode and look at error log

Steps to reproduce the behavior:

Install into a MySQL compatible database in only_full_group_by mode and look at error log. Eg in TiDB:

[2023/02/15 21:42:01.175 +11:00] [WARN] [session.go:2188] ["compile SQL failed"] [conn=3747630407693107665] [error="[planner:1055]Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'photoprism.l.label_uid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"] [SQL="SELECT l.label_uid, l.custom_slug, l.label_name FROM categories JOIN labels l ON categories.category_id = l.id WHERE (l.deleted_at IS NULL) GROUP BY l.custom_slug ORDER BY l.custom_slug LIMIT 1000 OFFSET 0"]

3. What behavior do you expect?

No SQL error

4. What could be the cause of your problem?

SELECT l.label_uid, l.custom_slug, l.label_name FROM categories JOIN labels l ON categories.category_id = l.id WHERE (l.deleted_at IS NULL) GROUP BY l.custom_slug ORDER BY l.custom_slug LIMIT 1000 OFFSET 0

... should be:

SELECT l.label_uid, l.custom_slug, l.label_name FROM categories JOIN labels l ON categories.category_id = l.id WHERE (l.deleted_at IS NULL) GROUP BY l.custom_slug, l.label_uid, l.label_name ORDER BY l.custom_slug LIMIT 1000 OFFSET 0

5. Can you provide us with example files for testing, error logs, or screenshots?

6. Which software versions do you use?

(a) PhotoPrism Architecture: AMD64, Build Number: docker pull from today

(b) Database Type & Version: TiDB

(d) Browser Types & Versions: Firefox

(e) Ad Blockers, Browser Plugins, and/or Firewall Software? NA

7. On what kind of device is PhotoPrism installed?

(a) Device / Processor Type: Ryzen 5 7600X

(b) Physical Memory & Swap Space in GB. RAM: 32GB, Swap: 8GB

(d) Anything else that might be helpful to know? No

8. Do you use a Reverse Proxy, Firewall, VPN, or CDN? No

Originally created by @dankasak on GitHub (Feb 20, 2023). #### 1. What is not working as documented? Databases that are stricter than old MySQL / Mariadb throw an error on the query generated from: https://github.com/dankasak/photoprism/blob/develop/internal/config/client_config.go#L613 #### 2. How can we reproduce it? Install into a MySQL compatible database in only_full_group_by mode and look at error log Steps to reproduce the behavior: Install into a MySQL compatible database in only_full_group_by mode and look at error log. Eg in TiDB: [2023/02/15 21:42:01.175 +11:00] [WARN] [session.go:2188] ["compile SQL failed"] [conn=3747630407693107665] [error="[planner:1055]Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'photoprism.l.label_uid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"] [SQL="SELECT l.label_uid, l.custom_slug, l.label_name FROM `categories` JOIN labels l ON categories.category_id = l.id WHERE (l.deleted_at IS NULL) GROUP BY l.custom_slug ORDER BY l.custom_slug LIMIT 1000 OFFSET 0"] #### 3. What behavior do you expect? No SQL error #### 4. What could be the cause of your problem? SELECT l.label_uid, l.custom_slug, l.label_name FROM `categories` JOIN labels l ON categories.category_id = l.id WHERE (l.deleted_at IS NULL) GROUP BY l.custom_slug ORDER BY l.custom_slug LIMIT 1000 OFFSET 0 ... should be: SELECT l.label_uid, l.custom_slug, l.label_name FROM `categories` JOIN labels l ON categories.category_id = l.id WHERE (l.deleted_at IS NULL) GROUP BY l.custom_slug, l.label_uid, l.label_name ORDER BY l.custom_slug LIMIT 1000 OFFSET 0 #### 5. Can you provide us with example files for testing, error logs, or screenshots? NA #### 6. Which software versions do you use? (a) PhotoPrism Architecture: AMD64, Build Number: docker pull from today (b) Database Type & Version: TiDB (c) Operating System Types & Versions: Linux (d) Browser Types & Versions: Firefox (e) Ad Blockers, Browser Plugins, and/or Firewall Software? NA #### 7. On what kind of device is PhotoPrism installed? (a) Device / Processor Type: Ryzen 5 7600X (b) Physical Memory & Swap Space in GB. RAM: 32GB, Swap: 8GB (c) Storage Type: HDD, SSD, RAID, USB, Network Storage,... 2x2TB SSDs in RAID1 (d) Anything else that might be helpful to know? No #### 8. Do you use a Reverse Proxy, Firewall, VPN, or CDN? No