Calendar Database error with latest git-release

[J_Roeleveld]

Hi,

I am using the git-release and after the most recent update, I get the following error when trying to edit/open a calendar entry:

> 2019/06/03 15:08:37 [error] 2415#2415: *10251 FastCGI sent in stderr: "PHP message: Database error (EGroupware\Api\Db\Exception\InvalidSql): Invalid SQL: SELECT egw_cal_repeats.recur_type,egw_cal_repeats.recur_interval,egw_cal_repeats.recur_data,CAST(egw_cal.cal_id AS varchar),cal_uid,cal_owner,cal_category,cal_modified,cal_priority,cal_public,cal_title,cal_description,cal_location,cal_reference,cal_modifier,cal_non_blocking,cal_special,cal_etag,cal_creator,cal_created,cal_recurrence,tz_id,cal_deleted,caldav_name,range_start,range_end,MIN(cal_start) AS cal_start,MIN(cal_end) AS cal_end,range_end-1 AS recur_enddate FROM egw_cal JOIN egw_cal_dates ON egw_cal.cal_id=egw_cal_dates.cal_id LEFT JOIN egw_cal_repeats ON egw_cal.cal_id=egw_cal_repeats.cal_id WHERE egw_cal.cal_id=2269 AND cal_start >= 1559599200 GROUP BY egw_cal_repeats.recur_type,egw_cal_repeats.recur_interval,egw_cal_repeats.recur_data,CAST(egw_cal.cal_id AS varchar),cal_uid,cal_owner,cal_category,cal_modified,cal_priority,cal_public,cal_title,cal_description,cal_location,cal_reference,cal_modifier,cal_non_blocking,...
> PHP message: #0 /var/www/egw.antarean.org/egroupware/api/src/Db/Deprecated.php(64): EGroupware\Api\Db->query('SELECT egw_cal_...', NULL, NULL, false, -1, false, 2, true)
> PHP message: #1 /var/www/egw.antarean.org/egroupware/api/src/Db.php(2122): EGroupware\Api\Db\Deprecated->query('SELECT egw_cal_...', 372, '/var/www/egw.an...', false, -1, false, 2)
> PHP message: #2 /var/www/egw.antarean.org/egroupware/calendar/inc/class.calendar_so.inc.php(372): EGroupware\Api\Db->select('egw_cal', 'egw_cal_repeats...', 'egw_cal.cal_id=...', 372, '/var/www/egw.an...', false, 'GROUP BY egw_ca...', 'calendar', 0, 'JOIN egw_cal_da...')
> PHP message: #3 /var/www/egw.antarean.org/egroupware/calendar/inc/class.calendar_bo.inc.php(1063): calendar_so->read(2269, 1559599200, false)
> PHP message: #4 /var/www/egw.antarean.org/egroupware/calendar/inc/class.calendar_uiforms.inc.php(1577): calendar_bo->read(2269, 1559599200, true)
> PHP message: #5 /var/www/egw.antarean.org

I am using Postgresql for the database.

[RalfBecker]

Problem is the message is truncated, so you don’t what the problem is:

Invalid SQL: SELECT egw_cal_repeats.recur_type,egw_cal_repeats.recur_interval,egw_cal_repeats.recur_data,CAST(egw_cal.cal_id AS varchar),cal_uid,cal_owner,cal_category,cal_modified,cal_priority,cal_public,cal_title,cal_description,cal_location,cal_reference,cal_modifier,cal_non_blocking,cal_special,cal_etag,cal_creator,cal_created,cal_recurrence,tz_id,cal_deleted,caldav_name,range_start,range_end,MIN(cal_start) AS cal_start,MIN(cal_end) AS cal_end,range_end-1 AS recur_enddate FROM egw_cal JOIN egw_cal_dates ON egw_cal.cal_id=egw_cal_dates.cal_id LEFT JOIN egw_cal_repeats ON egw_cal.cal_id=egw_cal_repeats.cal_id WHERE egw_cal.cal_id=2269 AND cal_start >= 1559599200 GROUP BY egw_cal_repeats.recur_type,egw_cal_repeats.recur_interval,egw_cal_repeats.recur_data,CAST(egw_cal.cal_id AS varchar),cal_uid,cal_owner,cal_category,cal_modified,cal_priority,cal_public,cal_title,cal_description,cal_location,cal_reference,cal_modifier,cal_non_blocking,...

Link to php.net on how to set a longer length. It would help, if we have the complete message.

Which EGroupware and PostgreSQL version is that?

Ralf

[J_Roeleveld]

Invalid SQL: SELECT egw_cal_repeats.recur_type,egw_cal_repeats.recur_interval,egw_cal_repeats.recur_data,CAST(egw_cal.cal_id AS varchar),cal_uid,cal_owner,cal_category,cal_modified,cal_priority,cal_public,cal_title,cal_description,cal_location,cal_reference,cal_modifier,cal_non_blocking,cal_special,cal_etag,cal_creator,cal_created,cal_recurrence,tz_id,cal_deleted,caldav_name,range_start,range_end,MIN(cal_start) AS cal_start,MIN(cal_end) AS cal_end,range_end-1 AS recur_enddate FROM egw_cal JOIN egw_cal_dates ON egw_cal.cal_id=egw_cal_dates.cal_id LEFT JOIN egw_cal_repeats ON egw_cal.cal_id=egw_cal_repeats.cal_id WHERE egw_cal.cal_id=2269 AND cal_start >= 1549926000 GROUP BY egw_cal_repeats.recur_type,egw_cal_repeats.recur_interval,egw_cal_repeats.recur_data,CAST(egw_cal.cal_id AS varchar),cal_uid,cal_owner,cal_category,cal_modified,cal_priority,cal_public,cal_title,cal_description,cal_location,cal_reference,cal_modifier,cal_non_blocking,cal_special,cal_etag,cal_creator,cal_created,cal_recurrence,tz_id,cal_deleted,caldav_name,range_start,range_end ORDER BY cal_deleted IS NOT NULL,egw_cal.cal_id DESC
ERROR:  column "egw_cal.cal_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ..._start,range_end ORDER BY cal_deleted IS NOT NULL,egw_cal.ca...

[J_Roeleveld]

Versions:

PHP: 7.2.16
Postgresql: 11.3
Nginx: 1.14.2
Egroupware: 17.1.20190529

[RalfBecker]

Hi,

can you try and verify the following fix:

Calendar - cast cal_id to varchar in sort too to avoid PostgreSQL error

It is not yet merged to 17.1, so the line number might be slightly different in 17.1.

Ralf

[J_Roeleveld]

Ralf,

After manually editing the file as per that change, the error has been fixed.
I had the change on the exact same line number.

Many thanks,

Joost

[RalfBecker]

Thanks a lot for reporting and testing the issue

It will be in the next maintenance release.

Ralf

[J_Roeleveld]

The product is supplied for free (as in beer) and I try to pay for the use by helping where I can.

I will leave the fix in on my end and will wait for the next release.