Bug report: All DXCCs shown as not worked - MySQL 5.7.5+

Need help? - Post here and we will find a solution for you.
Locked
m0blf
New user
Posts: 2
Joined: 27 Dec 2017, 10:00

Bug report: All DXCCs shown as not worked - MySQL 5.7.5+

Post by m0blf »

Hi,
I hope I'm filing this bug report in the right place!

Environment: Client: Log4OM 1.30.0 running on Win 10 64-bit / Database server: MySQL 5.7.20 on Ubuntu 16.04.1 64-bit

Observed behaviour: After clicking Utilities > Statistics and Awards, all DXCCs are shown as red (not worked). Clicking on a DXCC entity name does show the QSOs in the log for that entity.
This also affects the cluster, where all spots are shown as in unworked DXCCs.

Expected behaviour: DXCCs with QSOs should be shown as Green (Confirmed) or Yellow (Worked). Cluster spots with worked DXCCs should not be coloured red on the Cluster window.

Cause:
1. When you open Statistics and Awards, the following SQL query runs:

Code: Select all

SELECT Band, Mode, QslRcvd, EqslQslRcvd, LotwQslRcvd, QslSent, EqslQslSent, LotwQslSent, Dxcc, SubmissionStatus from log group by Band, Mode, QslRcvd, EqslQslRcvd, LotwQslRcvd, QslSent, EqslQslSent, LotwQslSent, Dxcc, SubmissionStatus order by Country asc
2. On my MySQL installation, this SQL query returns
Error Code: 1055. Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'log4om_m0blf.log.Country' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by'
3. According to https://dev.mysql.com/doc/refman/5.7/en ... dling.html , this is due to a behaviour change in default installations of MySQL 5.7.5 and higher.

Workaround: I've worked around this by changing the sql_mode on my server to not include only_full_group_by. DXCCs are now shown with the correct status.

Fix: It looks like the correct fix would be to include the 'country' column (used for the 'order by' clause) in the 'group by' clause

Hope this helps someone else avoid so much head-scratching!

73, Dom M0BLF
User avatar
IW3HMH
Site Admin
Posts: 2925
Joined: 21 Jan 2013, 14:20
Location: Quarto d'Altino - Venezia (ITA)
Contact:

Re: Bug report: All DXCCs shown as not worked - MySQL 5.7.5+

Post by IW3HMH »

Wow Dom :)
what a great find! (if you enable debug mode you can see the query that is running).

I've added the fix to 1.31 release. Thanks!
Daniele Pistollato - IW3HMH
m0blf
New user
Posts: 2
Joined: 27 Dec 2017, 10:00

Re: Bug report: All DXCCs shown as not worked - MySQL 5.7.5+

Post by m0blf »

Thanks for the quick fix and for all your efforts with the great software. Happy New Year!
Locked