Re: [Davical-general] SQL error after upgrade: ":collection_id" => "-1"

[Andrew R. <an...@et...>]

Hi Giuseppe,

It looks like DAViCal is failing to find the collection. Can you please try
running:

SELECT collection.*
FROM usr LEFT JOIN collection USING (user_no)
WHERE usr.username = 'giuseppe';

It certainly feels like things should be bailing out before it tries to use
-1 as a collection_id though!

Cheers,
Andrew

On Thu, 2023-06-15 at 01:30 +0200, Giuseppe Sacco via Davical-general wrote:
> Hello,
> I completed the upgrade to Debian 12 and davical 1.1.12, but I still have
> the
> same issue, so I traced all queries from the client, this is what it does:
> 
> SELECT usr.username
> FROM group_member
>   JOIN principal ON (principal_id=group_id)
>   JOIN usr USING(user_no)
> WHERE usr.active=true AND member_id = 1001
> UNION
> SELECT usr.username
> FROM group_member
>   LEFT JOIN grants ON (to_principal=group_id)
>   JOIN principal ON (principal_id=by_principal)
>   JOIN usr USING(user_no)
> WHERE usr.active=true AND member_id = 1001 and by_principal != member_id
> ORDER BY 1;
> 
> 0 records selected
> 
> SELECT collection.*, path_privs(1001::int8, collection.dav_name,2::int),
>   p.principal_id, p.type_id AS principal_type_id, p.displayname AS
> principal_displayname,
>   p.default_privileges AS principal_default_privileges,
> timezones.vtimezone
> FROM collection
>   LEFT JOIN principal p USING (user_no)
>   LEFT JOIN timezones ON (collection.timezone=timezones.tzid)
> WHERE collection.dav_name = '/'
> ORDER BY LENGTH(collection.dav_name) DESC
> LIMIT 1;
> 
> 0 records selected
> 
> SELECT property_name, property_value
> FROM property
> WHERE dav_name= '/';
> 
> 0 records selected
> 
> SELECT *, pprivs(1001::int8,principal_id,2::int) AS privileges
> FROM dav_principal
> WHERE lower(username)=lower(text('giuseppe'));
> 
> 1 record selected
> 
> SELECT usr.username
> FROM group_member
>   JOIN principal ON (principal_id=group_id)
>   JOIN usr USING(user_no)
> WHERE usr.active=true AND member_id = 1001
> UNION
> SELECT usr.username
> FROM group_member
>   LEFT JOIN grants ON (to_principal=group_id)
>   JOIN principal ON (principal_id=by_principal)
>   JOIN usr USING(user_no)
> WHERE usr.active=true AND member_id = 1001 and by_principal != member_id
> ORDER BY 1;
> 
> 0 records selected
> 
> SELECT new_sync_token( 0, -1) AS sync_token;
> 
> postgresql error: Key (collection_id)=(-1) not present in table
> "collection".
> 
> And, this is the error message in nginx log:
> 
> 2023/06/15 01:20:50 [error] 1443444#1443444: *32 FastCGI sent in stderr: "
> PHP message: davical: BUG: :DAViCal Fatal Error: [23503] SQLSTATE[23503]:
> Foreign key violation: 7 ERRORE:  la INSERT o l'UPDATE sulla tabella
> "sync_tokens" viola il vincolo di chiave esterna
> "sync_tokens_collection_id_fkey"
> DETTAGLI: La chiave (collection_id)=(-1) non è presente nella tabella
> "collection".
> CONTESTO: istruzione SQL "INSERT INTO sync_tokens(collection_id,
> sync_token) VALUES( in_collection_id, new_token )"
> funzione PL/pgSQL new_sync_token(bigint,bigint) riga 31 a istruzione SQL
> at /usr/share/awl/inc/AwlDatabase.php:94;
> PHP message: ================= Stack Trace ===================;
> PHP message: davical: LOG: :Response status 500 for PROPFIND
> /caldav.php/giuseppe/addresses/;
> PHP message: davical: LOG: :***************** Response Header
> ****************;
> PHP message: davical: LOG: headers:-->Server: 1.1;
> PHP message: davical: LOG: headers:-->DAV: 1, 2, 3, access-control,
> calendar-access, calendar-schedule;
> PHP message: davical: LOG: headers:-->DAV: extended-mkcol, bind,
> addressbook, calendar-auto-schedule, calendar-proxy;
> PHP message: davical: LOG: headers:-->X-DAViCal-Version: DAViCal/1.1.12;
> DB/1.3.5;
> PHP message: davical: LOG: headers:-->Content-type: text/plain;
> charset="utf-8";
> PHP message: davical: LOG: :******************** Response
> ********************;
> PHP message: davical: LOG: response:-->DAViCal Fatal Error" while reading
> response header from upstream, client: 192.168.222.54, server:
> cal.domain.tld, request: "PROPFIND /caldav.php/giuseppe/addresses/
> HTTP/2.0", upstream: "fastcgi://unix:/run/php/php8.2-fpm.sock:", host:
> "cal.domain.tld"
> 
> 
> Thank you,
> Giuseppe
> 
> 
> _______________________________________________
> Davical-general mailing list
> Dav...@li...
> https://lists.sourceforge.net/lists/listinfo/davical-general

-- 
Andrew Ruthven, Wellington, New Zealand
an...@et... |
Catalyst Cloud: | This space intentionally left blank
https://catalystcloud.nz |