Hi Ralf,
thanks for your reply. Of course I like giving more detailed information.
:~ # mysqld --version
mysqld Ver 5.1.36-log for suse-linux-gnu on i686 (SUSE MySQL RPM)
:~ # more /etc/SuSE-release
openSUSE 11.2 (i586)
VERSION = 11.2
Database is type MyIsam. Every time after svn checkout I check for required table updates in egw/setup.
During my investigations I did a mysql-log, bringing this up
(there is only one client accessing the server via CalDAV):
/usr/sbin/mysqld, Version: 5.1.36-log (SUSE MySQL RPM). started with:
Tcp port: 3306 Unix socket: /var/run/mysql/mysql.sock
Time Id Command Argument
130418 15:03:59 1 Connect XXDB-NAMEXX@localhost on
1 Init DB XXDB-NAMEXX
1 Query SET NAMES 'utf8'
1 Query SELECT @@character_set_connection LIMIT 1
1 Query UPDATE egw_access_log SET session_dla=1366290239,session_action='/groupdav.php/calendar',lo=NULL WHERE sessionid=391
1 Query SELECT account_id,account_type FROM egw_accounts WHERE account_lid='calendar' AND account_id IS NOT NULL
1 Query SELECT account_id,account_type FROM egw_accounts WHERE account_lid='calendar' AND account_id IS NOT NULL
1 Query SELECT account_id,account_type FROM egw_accounts WHERE account_lid='calendar' AND account_id IS NOT NULL
1 Query SELECT account_id,account_type FROM egw_accounts WHERE account_lid='calendar' AND account_id IS NOT NULL
1 Query SELECT acl_account,acl_rights,acl_location FROM egw_acl WHERE acl_appname='calendar' AND acl_location IN ('-7','-1','-2','6')
1 Query SELECT MAX(cal_modified) FROM egw_cal_user JOIN egw_cal ON egw_cal.cal_id=egw_cal_user.cal_id WHERE (cal_user_type='u' AND cal_user_id IN ('6','-7
','-1','-2') OR cal_owner=6)
1 Query (SELECT egw_cal_repeats.recur_type,egw_cal_repeats.recur_interval,egw_cal_repeats.recur_data,range_end AS recur_enddate,egw_cal.cal_id,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_c
reated,cal_recurrence,tz_id,cal_deleted,caldav_name,range_start AS cal_start,(SELECT MIN(cal_end) FROM egw_cal_dates WHERE egw_cal.cal_id=egw_cal_dates.cal_id) AS cal_end,egw_cal
_user.cal_recur_date,NULL AS participants,NULL AS icons FROM egw_cal JOIN egw_cal_user ON egw_cal.cal_id=egw_cal_user.cal_id LEFT JOIN egw_cal_repeats ON egw_cal.cal_id=egw_cal_r
epeats.cal_id LEFT JOIN egw_cal_user rejected_by_user ON egw_cal.cal_id=rejected_by_user.cal_id AND rejected_by_user.cal_user_type='u' AND rejected_by_user.cal_user_id='6' AND re
jected_by_user.cal_recur_date=0 WHERE cal_reference=0 AND cal_deleted IS NULL AND egw_cal_user.cal_status!='X' AND (1360050239 < range_end OR range_end IS NULL) AND egw_cal_user.
cal_recur_date=0 AND 1389066239 > range_start AND (rejected_by_user.cal_status IS NULL OR rejected_by_user.cal_status NOT IN ('R','X') OR cal_owner=6) AND egw_cal_user.cal_user_t
ype='u' AND egw_cal_user.cal_user_id IN ('6','-7','-1','-2') )
UNION
(SELECT egw_cal_repeats.recur_type,egw_cal_repeats.recur_interval,egw_cal_repeats.recur_data,range_end AS recur_enddate,egw_cal.cal_id,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 AS cal_start,(SELECT MIN(cal_end) FROM egw_cal_dates WHERE egw_cal.cal_id=egw_cal_dates.cal_id) AS cal_end,egw_cal_user.cal_recur_date,NULL AS par
ticipants,NULL AS icons FROM egw_cal JOIN egw_cal_user ON egw_cal.cal_id=egw_cal_user.cal_id LEFT JOIN egw_cal_repeats ON egw_cal.cal_id=egw_cal_repeats.cal_id LEFT JOIN egw_cal_
user rejected_by_user ON egw_cal.cal_id=rejected_by_user.cal_id AND rejected_by_user.cal_user_type='u' AND rejected_by_user.cal_user_id='6' AND rejected_by_user.cal_recur_date=0
WHERE cal_reference=0 AND cal_deleted IS NULL AND egw_cal_user.cal_status!='X' AND (1360050239 < range_end OR range_end IS NULL) AND egw_cal_user.cal_recur_date=0 AND 1389066239
> range_start AND (rejected_by_user.cal_status IS NULL OR rejected_by_user.cal_status NOT IN ('R','X') OR cal_owner=6) AND cal_owner=6 )
ORDER BY range_start LIMIT 0,500
1 Quit
Putting the last query into phpmyadmin gives a syntax error, but with no helpful hint.
So I added some linebreaks to the query, trying to get it more human readable. Putting these lines into phpmyadmin I get the “#1054 - Unknown column ‘range_start’ in ‘order clause’” SQL -error. .
(SELECT
egw_cal_repeats.recur_type,
egw_cal_repeats.recur_interval,
egw_cal_repeats.recur_data,
range_end AS recur_enddate,
egw_cal.cal_id,
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 AS cal_start,
(SELECT MIN(cal_end) FROM egw_cal_dates WHERE egw_cal.cal_id=egw_cal_dates.cal_id) AS cal_end,
egw_cal_user.cal_recur_date,
NULL AS participants,
NULL AS icons
FROM egw_cal
JOIN egw_cal_user
ON egw_cal.cal_id=egw_cal_user.cal_id
LEFT JOIN egw_cal_repeats
ON egw_cal.cal_id=egw_cal_repeats.cal_id
LEFT JOIN egw_cal_user rejected_by_user
ON egw_cal.cal_id=rejected_by_user.cal_id
AND rejected_by_user.cal_user_type='u'
AND rejected_by_user.cal_user_id='6'
AND rejected_by_user.cal_recur_date=0
WHERE cal_reference=0
AND cal_deleted IS NULL
AND egw_cal_user.cal_status!='X'
AND (1360050239 < range_end OR range_end IS NULL)
AND egw_cal_user.cal_recur_date=0
AND 1389066239 > range_start
AND (rejected_by_user.cal_status IS NULL OR rejected_by_user.cal_status NOT IN ('R','X') OR cal_owner=6)
AND egw_cal_user.cal_user_type='u'
AND egw_cal_user.cal_user_id IN ('6','-7','-1','-2') )
UNION
(SELECT
egw_cal_repeats.recur_type,
egw_cal_repeats.recur_interval,
egw_cal_repeats.recur_data,range_end AS recur_enddate,
egw_cal.cal_id,
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 AS cal_start,
(SELECT MIN(cal_end) FROM egw_cal_dates WHERE egw_cal.cal_id=egw_cal_dates.cal_id) AS cal_end,
egw_cal_user.cal_recur_date,
NULL AS participants,
NULL AS icons
FROM egw_cal
JOIN egw_cal_user
ON egw_cal.cal_id=egw_cal_user.cal_id
LEFT JOIN egw_cal_repeats
ON egw_cal.cal_id=egw_cal_repeats.cal_id
LEFT JOIN egw_cal_user rejected_by_user
ON egw_cal.cal_id=rejected_by_user.cal_id
AND rejected_by_user.cal_user_type='u'
AND rejected_by_user.cal_user_id='6'
AND rejected_by_user.cal_recur_date=0
WHERE cal_reference=0
AND cal_deleted IS NULL
AND egw_cal_user.cal_status!='X'
AND (1360050239 < range_end OR range_end IS NULL)
AND egw_cal_user.cal_recur_date=0 AND 1389066239 > range_start
AND (rejected_by_user.cal_status IS NULL OR rejected_by_user.cal_status NOT IN ('R','X') OR cal_owner=6)
AND cal_owner=6 )
ORDER BY range_start LIMIT 0,500
My very first thaughts where that this is rather mysql-version related. But putting the query in a phpmyadmin on another machine, with a newer version of mysql (and a slightly older eGW) causing the same sql error!
:~$ mysqld --version
mysqld Ver 5.5.31-0ubuntu0.12.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))
I found out, the query was generated in /calendar/inc/class.calendar_so.inc.php and I then checked how the ORDER BY clause is set up.
As I said, since I switched the values for ORDER BY, I can use CalDAV as I was used to before doing an svn checkout.
Cheers,
Ulle
