Roundcube login breaks because NOT NULL constraint in carddav login

[simonhunter1]

We noticed that users can’t login to roundcube after the update to v56. The error they’re getting was that on login, they get ‘Oops… something went wrong! an internal error has occurred etc etc.’

I noticed the following error in the Roundcube logs:

[20-Jan-2022 12:15:26 -0800]: <iotvchua> DB Error: [19] NOT NULL constraint failed: carddav_addressbooks2.last_updated (SQL Query: UPDATE carddav_addressbooks2 SET last_updated=strftime('%s', last_updated)) in /usr/local/lib/roundcubemail/program/lib/Roundcube/rcube_db.php on line 566 (POST /mail/?_task=login&_action=login)

[20-Jan-2022 12:15:27 America/Los_Angeles] PHP Fatal error: Uncaught Error: Wrong parameters for MStilkerich\CardDavAddressbook4Roundcube\Db\DatabaseException([string $message [, long $code [, Throwable $previous = NULL]]]) in /usr/local/lib/roundcubemail/plugins/carddav/src/Db/Database.php:112

Stack trace:
#0 /usr/local/lib/roundcubemail/plugins/carddav/src/Db/Database.php(112): Exception->__construct(NULL)
#1 /usr/local/lib/roundcubemail/plugins/carddav/carddav.php(1169): MStilkerich\CardDavAddressbook4Roundcube\Db\Database->startTransaction(false)
#2 /usr/local/lib/roundcubemail/plugins/carddav/carddav.php(339): carddav->deleteAddressbook('6')
#3 /usr/local/lib/roundcubemail/program/lib/Roundcube/rcube_plugin_api.php(513): carddav->initPresets(Array)
#4 /usr/local/lib/roundcubemail/index.php(154): rcube_plugin_api->exec_hook('login_after', Array)
#5 {main}
thrown in /usr/local/lib/roundcubemail/plugins/carddav/src/Db/Database.php on line 112

So it seems like a database error which cascades into an error building an exception. While I’m trying to figure out how to fix it I have made a workaround by disabling the carddav plugin.

To temporarily disable the carddav plugin I navigated to /usr/local/lib/roundcubemail/config and edited config.inc.php. There’s a line with an array of plugins, remove ‘carddav’ from that array. This temporarily fixed the problem, although if your users use carddav that won’t be available, and this will be overwritten by the next update probably.

[simonhunter1]

This is a mailinabox which has been upgraded many times from an installation a long time ago, so maybe it needs some additional db migration step which is missed on very old installations, just an idea.

[downtownallday]

carddav dbmigration 14 seems suspect to me, which attempts to convert from native database datetimes to big int (seconds from unix epoch)

the code is here:

https://github.com/mstilkerich/rcmcarddav/blob/master/dbmigrations/0014-unifytimestampdefaultvalue/sqlite3.sql

The existing logic for makes no sense, which does an INSERT INTO carddav_addressbooks2 from a SELECT of the old table (carddav_addressbooks) without any column transformation. Then, attempts a column transformation of carddav_addressbooks2 treating the new column (big int) as if it were a string (strftime).

[TheNomad11]

Tthe Roundcube upgrade failed in Yunohost as well, maybe related? https://github.com/YunoHost-Apps/roundcube_ynh/issues/135 and https://github.com/YunoHost-Apps/roundcube_ynh/issues/126

If this is a common issue it might be better to skip or wait with this upgrade?

[downtownallday]

You could examine your current migration state:

source /etc/mailinabox.conf; sqlite3 $STORAGE_ROOT/mail/roundcube/roundcube.sqlite ".schema carddav_*" ".dump carddav_migrations"

[cinergi2]

To clarify, does this issue affect upgrades from the previous version 55, or only from older versions? Is there a fix in the works?

[simonhunter1]

I only experienced this going from 54 to 56 and haven't tested 55-56 directly.

[cinergi2]

Thanks. I'll wait for the dev team to comment before doing the upgrade. I use carddav, so disabling it is not an option for me.

[JoshData]

It didn't affect my box and I imagine by now we would have heard from others if this was a general problem, but anything is possible.

[downtownallday]

For now, you could just nuke all the carddav tables in roundcube.sqlite, then re-enable the plugin. The carddav database is simply a cache of the remote server's (Nextcloud's) data.

[cinergi2]

Can confirm successful upgrade from v55 to v56 with Roundcube login still working fine.

[JJWTimmer]

so how did we solve this exactly?

[TheNomad11]

Upgrade went fine for me as well

[JJWTimmer]

I ended up downloading the sqllite db from ~user-data/mail/roundcube/roundcube.sqllite to my desktop, and use a SQL GUI to delete the carddav tables. Then reupload and run the installer again, then the Roundcube worked again.

[dries]

Had this issue going from 55 -> 56. Fixed as follows (ssh as root):

sqlite3 /home/user-data/mail/roundcube/roundcube.sqlite`
SELECT * FROM carddav_addressbooks;

and then see which entry has a last_updated timestamp in seconds since epoch instead of timestamp (use .schema carddav_addressbooks to make sure you're looking at the correct column)

Then convert the seconds since epoch to regular timestamp and update that entry, e.g.

UPDATE carddav_addressbooks SET last_updated = '2022-03-04 15:14:53' WHERE id = 1;

And then everything was fine.

[simonhunter1]

@dries I did as you suggested and restored the carddav plugin and it's working, thanks for the simple fix!