Bug 370744

Summary: Date viewer do not return photos [patch]
Product: [Applications] digikam Reporter: Jeff <jeffmarchant>
Component: Database-MysqlAssignee: Digikam Developers <digikam-bugs-null>
Status: RESOLVED FIXED    
Severity: normal CC: caulier.gilles, david.varnes, metzpinguin
Priority: NOR    
Version: 5.2.0   
Target Milestone: ---   
Platform: MacPorts   
OS: macOS   
Latest Commit: https://commits.kde.org/digikam/dc49ea70d39b70ae6244db354fc1b14f2153d2d6 Version Fixed In: 5.4.0
Sentry Crash Report:
Attachments: Patch to fix the query SQL to comply with new stricter constraints in MYSQL 5.7

Description Jeff 2016-10-14 14:53:31 UTC 
Browsing for photos using the date view control shows the tree structure for years and months ok. When you select a month no photos are displayed or dates highlighted in the calendar, even though there are photos for that date present in the album. The timeline browser works ok.

If I look in the console output when selecting a month in date browser I see that there is an SQL error raised.

" Prepare failed!
digikam.dbengine: Failure executing query:
 "SELECT DISTINCT Images.id, Images.name, Images.album,        Albums.albumRoot,        ImageInformation.rating, Images.category,        ImageInformation.format, ImageInformation.creationDate,        Images.modificationDate, Images.fileSize,        ImageInformation.width, ImageInformation.height  FROM Images        LEFT JOIN ImageInformation ON Images.id=ImageInformation.imageid        INNER JOIN Albums ON Albums.id=Images.album  WHERE Images.status=1    AND ImageInformation.creationDate < ?    AND ImageInformation.creationDate >= ?  ORDER BY Albums.id;" 
Error messages: "QMYSQL3: Unable to prepare statement" "Expression #1 of ORDER BY clause is not in SELECT list, references column 'digikam.Albums.id' which is not in SELECT list; this is incompatible with DISTINCT" 3065 2"

Reproducible: Always

Steps to Reproduce:
1.Select a month node from the tree in the date viewer
2.
3.

Actual Results:  
NO photo displayed in viewer window and no dates highlighted in calendar control 

Expected Results:  
Photo displayed in viewer window for selected month.

This is a fresh  install using the 5,2,0 pkg with external MySQL database.
Comment 1 Jeff 2016-10-14 16:23:40 UTC 
Found a workaround ...seems to do with new defaults in MYSQL 5.7 releases ... see https://github.com/Piwigo/Piwigo/issues/376 

Now changed SQL mode for MYSQL to be blank and date view is now working :)
Comment 2 caulier.gilles 2016-10-14 16:27:26 UTC 
Maik,

As i can see in a comment in link given :

"I set sql-mode="" in /etc/my.cnf and that resolved the issue for me."

So for internal Mysql, we need to set this option in config file.

Gilles
Comment 3 Maik Qualmann 2016-10-16 07:52:21 UTC 
Git commit a7e5b50b312c8cc561ed6f2196e0ec9b8a0951ee by Maik Qualmann.
Committed on 16/10/2016 at 07:51.
Pushed by mqualmann into branch 'master'.

fix for internal MySQL DB the new default ONLY_FULL_GROUP_BY setting in MySQL-5.7

M  +3    -0    data/database/mysql-global.conf

http://commits.kde.org/digikam/a7e5b50b312c8cc561ed6f2196e0ec9b8a0951ee
Comment 4 Maik Qualmann 2016-10-27 05:55:03 UTC 
*** Bug 371726 has been marked as a duplicate of this bug. ***
Comment 5 david.varnes 2016-10-27 08:09:10 UTC 
Created attachment 101823 [details]
Patch to fix the query SQL to comply with new stricter constraints in MYSQL 5.7

This patch was tested against MYSQL server 5.7.3 in Ubuntu 16.10 with vanilla settings.
Comment 6 david.varnes 2016-11-03 11:05:12 UTC 
bump - patch attached

:-)  thanks !   :-)
Comment 7 david.varnes 2016-11-03 11:21:38 UTC 
Just noticed that the title of this bug is missing a key word - NOT.

Should be:  Date viewer does not return photos.

Might look a little odd in the change log the way it is  :-)
Comment 8 caulier.gilles 2016-12-04 10:29:02 UTC 
Git commit dc49ea70d39b70ae6244db354fc1b14f2153d2d6 by Gilles Caulier.
Committed on 04/12/2016 at 10:28.
Pushed by cgilles into branch 'master'.

Apply patch #101823 to make listDateRange() MYSQL 5.7 compliant

MYSQL 5.7 with default configuration introduced stricter query structure constraints.
This patch changes the ORDER BY clause to use Images.album instead of Albums.id, which causes an error since it is not in the SELECT DISTINCT clause.
As can be seen from the INNER JOIN clause Albums.id=Images.album this change does not alter the logic of the query.
FIXED-IN: 5.4.0

M  +2    -1    NEWS
M  +1    -1    libs/database/item/imagelister.cpp

https://commits.kde.org/digikam/dc49ea70d39b70ae6244db354fc1b14f2153d2d6
Comment 9 Maik Qualmann 2016-12-04 19:13:59 UTC 
Gilles,

I think i can remove workaround commit from Comment 3

Maik
Comment 10 caulier.gilles 2016-12-04 19:18:24 UTC 
I don't see your previous commit.

If with the SQL patch, and without the configuration patch it work, weel i can say yes.

Gilles
Comment 11 Maik Qualmann 2016-12-04 20:17:55 UTC 
Git commit aa26535c9cfb5d09eb95a04f1e7c085a1a28bbbc by Maik Qualmann.
Committed on 04/12/2016 at 20:17.
Pushed by mqualmann into branch 'master'.

remove workaround for MySQL-5.7

M  +0    -3    data/database/mysql-global.conf

https://commits.kde.org/digikam/aa26535c9cfb5d09eb95a04f1e7c085a1a28bbbc