Webcalender 1.9.1 not support two categories

[GemeinsamGemeinde]

Hi, an new issue, when we try to add two categories to on date (two global or also same with one global and one local categorie) we get the following error (Version 1.9.1, PHP 8.2, PHP 8.1) When I Use PHP 8.0 only the message Fehler: Fehler beim Ausführen des Query (Error: Error on execute the query) is coming up

Fatal error: Uncaught mysqli_sql_exception: Duplicate entry '194' for key 'webcal_entry_categories.PRIMARY' in /homepages/34/xxxx/htdocs/domain/domain/includes/dbi4php.php:382 Stack trace:

0 /homepages/34/xxxxx/htdocs/domain/domain/includes/dbi4php.php(382): mysqli->query('INSERT INTO web...')

1 /homepages/34/xxxx/htdocs/domain/churchtools/includes/dbi4php.php(786): dbi_query('INSERT INTO web...', true, true)

2 /homepages/34/xxxxx/htdocs/domain/churchtools/edit_entry_handler.php(734): dbi_execute('INSERT INTO web...', Array)

3 {main} thrown in /homepages/34/xxxxx/htdocs/domain/churchtools/includes/dbi4php.php on line 382

Whis Webcal 1.3.x, this function works perfect, but not with 1.9.1 anymore..... Any idea

[cyfrax]

I think I found the issue. The database table webcal_entry_categories was created wrong. The primary index is just on cal_id instead of cal_id, cat_id

I have now changed the primary index to ( cal_id, cat_id ) and created an additional index for cal_id.

-> works again

I have created a pull request for sqlite3 as a sample https://github.com/craigk5n/webcalendar/pull/342

but needs to be done for the other database categories as well

[GemeinsamGemeinde]

Oha, shame, blame on my side, I see your solution 8 month after...... I will try the update after an backup of my database... Thanks Stephan

[craigk5n]

This is fixed in the php8 branch and will be in master shortly (for databases, not just sqlite). The correct primary key is:

PRIMARY KEY ( cal_id, cat_id, cat_order, cat_owner )

But the cat_owner needs to no longer allow NULL:

cat_owner varchar(25) DEFAULT '' NOT NULL,

MySQL users can do the following:

UPDATE webcal_entry_categories SET cat_owner = '' WHERE cat_owner IS NULL;
ALTER TABLE webcal_entry_categories ADD PRIMARY KEY (cal_id, cat_id, cat_order, cat_owner);

You can upgrade to 1.9.6 (or later) when it is out, and it will do the two MySQL commands for you as part of the install upgrade.

If you have a really old database, you may have some junk in your WebCalendar database. This could result in errors about duplicate keys when you try to add the new primary key. To remove the duplicates, use the following MySQL commands (make a backup first with mysqldump!):

CREATE TABLE webcal_entry_categories_temp LIKE webcal_entry_categories;
INSERT INTO webcal_entry_categories_temp
SELECT DISTINCT cal_id, cat_id, cat_order, cat_owner
FROM webcal_entry_categories;
RENAME TABLE webcal_entry_categories TO webcal_entry_categories_old,
webcal_entry_categories_temp TO webcal_entry_categories;
DROP TABLE webcal_entry_categories_old;