Duplicate entry for typename in carddav_xsubtypes

[cl-parsons]

This happens when updating a new carddav addressbook, with the latest version of Roundcube (1.4.9) and RCMCardDav (from composer, 4.0.0)

Log

[03-Oct-2020 11:15:48 +0200]: <srbunpub> INSERT group_user (INSERT INTO carddav_group_user(contact_id,group_id) VALUES (?,?)) ->
[03-Oct-2020 11:15:48 +0200]: <srbunpub> INSERT contacts (INSERT INTO carddav_contacts(firstname,surname,email,name,etag,vcard,abook_id,uri,cuid) VALUES (?,?,?,?,?,?,?,?,?)) -> 76
[03-Oct-2020 11:15:48 +0200]: <srbunpub> INSERT group_user (INSERT INTO carddav_group_user(contact_id,group_id) VALUES (?,?)) ->
[03-Oct-2020 11:15:48 +0200]: <srbunpub> INSERT xsubtypes (INSERT INTO carddav_xsubtypes(typename,subtype,abook_id) VALUES (?,?,?)) -> 0
[03-Oct-2020 11:15:48 +0200]: <srbunpub> Database::insert (INSERT INTO carddav_xsubtypes(typename,subtype,abook_id) VALUES (?,?,?)) ERROR: [1062] Duplicate entry 'email-OLD-14' for key 'typename'
[03-Oct-2020 11:15:48 +0200]: <srbunpub> Errors occurred during the refresh of addressbook 14: Exception: [1062] Duplicate entry 'email-OLD-14' for key 'typename' in /home/www-git/roundcubemail-1.4.4/plugins/carddav/src/Database.php:440
#0 /home/www-git/roundcubemail-1.4.4/plugins/carddav/src/Addressbook.php(1316): MStilkerich\CardDavAddressbook4Roundcube\Database::insert('xsubtypes', Array, Array)
#1 /home/www-git/roundcubemail-1.4.4/plugins/carddav/src/Addressbook.php(1904): MStilkerich\CardDavAddressbook4Roundcube\Addressbook->storeextrasubtype('email', 'OLD')
#2 /home/www-git/roundcubemail-1.4.4/plugins/carddav/src/Addressbook.php(1167): MStilkerich\CardDavAddressbook4Roundcube\Addressbook->getAttrLabel(Object(Sabre\VObject\Component\VCard), Object(Sabre\VObject\Property\FlatText), 'email')
#3 /home/www-git/roundcubemail-1.4.4/plugins/carddav/src/SyncHandlerRoundcube.php(280): MStilkerich\CardDavAddressbook4Roundcube\Addressbook->convVCard2Rcube(Object(Sabre\VObject\Component\VCard))
#4 /home/www-git/roundcubemail-1.4.4/plugins/carddav/src/SyncHandlerRoundcube.php(88): MStilkerich\CardDavAddressbook4Roundcube\SyncHandlerRoundcube->updateContactCard('/remote.php/dav...', '"dedd65907d79bd...', Object(Sabre\VObject\Component\VCard))
#5 /home/www-git/roundcubemail-1.4.4/vendor/mstilkerich/carddavclient/src/Services/Sync.php(163): MStilkerich\CardDavAddressbook4Roundcube\SyncHandlerRoundcube->addressObjectChanged('/remote.php/dav...', '"dedd65907d79bd...', Object(Sabre\VObject\Component\VCard))
#6 /home/www-git/roundcubemail-1.4.4/vendor/mstilkerich/carddavclient/src/Services/Sync.php(59): MStilkerich\CardDavClient\Services\Sync->synchronizeOneBatch(Object(MStilkerich\CardDavClient\AddressbookCollection), Object(MStilkerich\CardDavAddressbook4Roundcube\SyncHandlerRoundcube), Array, '')
#7 /home/www-git/roundcubemail-1.4.4/plugins/carddav/src/Addressbook.php(1213): MStilkerich\CardDavClient\Services\Sync->synchronize(Object(MStilkerich\CardDavClient\AddressbookCollection), Object(MStilkerich\CardDavAddressbook4Roundcube\SyncHandlerRoundcube), Array, '')
#8 /home/www-git/roundcubemail-1.4.4/plugins/carddav/src/Addressbook.php(179): MStilkerich\CardDavAddressbook4Roundcube\Addressbook->resync(true)
#9 /home/www-git/roundcubemail-1.4.4/plugins/carddav/carddav.php(291): MStilkerich\CardDavAddressbook4Roundcube\Addressbook->__construct('14', Object(carddav), false, Array)
#10 /home/www-git/roundcubemail-1.4.4/program/lib/Roundcube/rcube_plugin_api.php(469): carddav->getAddressbook(Array)
#12 /home/www-git/roundcubemail-1.4.4/program/steps/addressbook/func.inc(320): rcmail->get_address_book('carddav_14')
[03-Oct-2020 11:15:48 +0200]: <srbunpub> carddav::getAddressbook(carddav_18)
[03-Oct-2020 11:15:48 +0200]: <srbunpub> UPDATE addressbooks (UPDATE carddav_addressbooks SET last_updated=? WHERE `id` = '18')

[mstilkerich]

Hello,

does this happen in a reproducible manner (e.g. every time you add the addressbook as a new one, or even on every sync)?

[cl-parsons]

@mstilkerich

Heh, it seems that it happens everytime I add the addressbook as a new one (I add several addressbooks at a time). I'll try with different carddav servers (baikal, nextcloud contacts, Sogo) to see what happens. The problem I described is with Nextcloud contacts, with 4 addressbooks. The fact is that in this case, I think that the SQL INSERT statement should be an UPSERT. I have to look into my DB to see what provokes the conflict.

[mstilkerich]

Normally this insert only happens when a new custom label (X-ABLabel extension) is encountered in an addressbook. When the label is already known, it should not attempt to insert it again. I know this conflict might happen when you have concurrent sync operations running on the same addressbook, but in this case it would be harmless / just a log entry.

Anyway, I will recheck the code concerning X-ABLabel again and try myself with an addressbook that has multiple uses of the same custom label to see if I can reproduce the issue.

[cl-parsons]

Thanks @mstilkerich , I forgot to mention that I miss a lot of contacts on the RC side, and I think it blocks always at the same contact. I have to investigate (when I'll have time :p) which one blocks ;)

[mstilkerich]

So I have taken a look, but I don't see how this situation could happen except for said concurrent synchronizations, which I don't think is happening in your case if the problem occurs during every sync. I have also tried myself with adressbooks that contain several cards with the same special label, and also cards that use the same special label on several email addresses. Unfortunately, I cannot reproduce the issue.

To give you a bit of context in case you cannot guess it from the log messages: In VCard, you can assign any multi-value attribute (in this case, EMAIL) a TYPE parameter. There is standard values such as home, work, internet for these attributes that are hardcoded into the plugin.

Then there is an extension to VCard (I believe originally defined by Apple from the name) called X-ABLabel, which allows to define user-defined labels for such multi-value properties. The VCard that causes the issue on your end contains an email address that has the special label OLD assigned using this extension.

Now the roundcube contact edit mask only allows to select the type of a multi-value attribute from a dropdown list, it is not possible to create new user-defined labels. To support user-defined labels, the rcmcarddav plugin stores any special label encountered in an addressbook in the database, and adds it to that dropdown list so it is possible to assign the label to other multi-value properties of the same time (email addresses in this case). This database store operation only happens for new labels, i. e. that are not already inside the database for the respective addressbook. In your case, the database store operation is attempted although the label is apparently already stored inside the DB for that addressbook. This fails because of a DB UNIQUE constraint.

However, I have no explanation as to why the plugin attempts to store a label that is already inside the DB.

If possible, can you provide the offending VCard? You should be able to find it in carddav_http.log when the loglevel for HTTP is set to debug by searching for X-ABLabel in the logfile.

I just came across a potential issue that I have to try: Are you using MySQL? In MySQL, we use case-insensitive collations, so the issue might occur if the label is used with different casing (for example OLD and old).

[mstilkerich]

Yep, special label with different case triggers the problem with MySQL because of the case insensitive collations. So I guess you have several different spellings of the OLD label for email addresses in your addressbook.

[cl-parsons]

Thanks a lot @mstilkerich , I removed the problematic X-label OLD and old types, and now everything is going well!