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

[Giuseppe S. <giu...@sg...>]

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