CMS MADE SIMPLE FORGE

CGCalendar

 
Back Back to List

[#12244] CGCalendar returns no entries - MYSQL SELECT DISTINCT and ORDER BY columns causes error in cgcalendar_utils::get_query

avatar
Created By: Jon Sykes (jonsykes)
Date Submitted: Sun Jan 12 18:09:24 -0500 2020

Assigned To: Robert Campbell (calguy1000)
Version: 2.6.2
CMSMS Version: 2.2.13
Severity: Critical
Resolution: None
State: Open
Summary:
CGCalendar returns no entries - MYSQL SELECT DISTINCT and ORDER BY columns causes error in cgcalendar_utils::get_query
Detailed Description:
Symptom: CGCalendar returns no entries

Root cause was an error reported by MySQL which requires the columns used in
ORDER BY to also be in SELECT DISTINCT statement.

Modified cgcalendar_utils::get_query fixed my issue.

Line 307 was:

$sql = "SELECT DISTINCT E.event_id FROM $events_table_name E\n";

With fix:

$sql = "SELECT DISTINCT E.event_id, E.event_date_start FROM $events_table_name
E\n";

Thanks, Jon


History

Comments
avatar 
Date: 2020-05-03 10:25
Posted By: Ruud van der Velden (ruudvdvelden)

Confirmed. Problem started after moving to another server, so possibly MySQL
version is relevant.


MySQL info:
Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.7.29-0ubuntu0.16.04.1 - (Ubuntu)
Protocol version: 10
Server charset: cp1252 West European (latin1)
avatar 
Date: 2020-05-03 10:36
Posted By: Ruud van der Velden (ruudvdvelden)

additional information:

Database Error: EXECUTE(3065) - Expression #1 of ORDER BY clause is not in
SELECT list, references column '[REMOVED].E.event_date_start' which is not in
SELECT list; this is incompatible with DISTINCT

https://bugs.mysql.com/bug.php?id=81342
https://bugs.mysql.com/bug.php?id=83978