Skip to main content

roundcube 0014-unifytimestampdefaultvalue migration weirdness

Arkadiusz Miśkiewicz

How is roundcube carddav/dbmigrations/0014-unifytimestampdefaultvalue migration supposed to work?

 

CREATE TABLE IF NOT EXISTS TABLE_PREFIXcarddav_addressbooks2 (
[...]
last_updated BIGINT NOT NULL DEFAULT 0,  -- time stamp (seconds since epoch) of the last update of the local database
[...]
);

UPDATE TABLE_PREFIXcarddav_addressbooks2 SET last_updated='1970-01-01 00:00:00' WHERE last_updated=0;
UPDATE TABLE_PREFIXcarddav_addressbooks2 SET last_updated=strftime('%s', last_updated);


Table is created with last_updates being bigint .
Then string is going to be set there.
Then it's expected for last_updated to contain yyyy-mm-dd... date format that strftime is supposed to convert back to timestamp but bigint won't handle strings.

Huh?

This migration obviously fails:

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/cpanel/base/3rdparty/roundcube/program/lib/Roundcube/rcube_db.php

That failure triggers

[14-Jan-2026 11:42:45 Europe/Warsaw] PHP Fatal error: Uncaught TypeError: Exception::__construct(): Argument #1 ($message) must be of type string, null given in /usr/local/cpanel/base/3rdparty/roundcube/plugins/carddav/src/Db/Database.php:117 Stack trace: #0 /usr/local/cpanel/base/3rdparty/roundcube/plugins/carddav/src/Db/Database.php(117): Exception->__construct(NULL) #1 /usr/local/cpanel/base/3rdparty/roundcube/plugins/carddav/carddav.php(1184): MStilkerich\CardDavAddressbook4Roundcube\Db\Database->startTransaction(false) #2 /usr/local/cpanel/base/3rdparty/roundcube/plugins/carddav/carddav.php(348): carddav->deleteAddressbook('1') #3 /usr/local/cpanel/base/3rdparty/roundcube/program/lib/Roundcube/rcube_plugin_api.php(520): carddav->initPresets(Array) #4 /usr/local/cpanel/base/3rdparty/roundcube/index.php(151): rcube_plugin_api->exec_hook('login_after', Array) #5 {main} thrown in /usr/local/cpanel/base/3rdparty/roundcube/plugins/carddav/src/Db/Database.php on line 117 

which for user is visible as

"Ooops .... something went wrong"

when trying to access roundcube webmail from cpanel.

Comments

7 comments

Date Votes
  • cPRex Jurassic Moderator

    Hey there!  When this happens, the best option is typically to move the Roundcube database and then restart Roundcube on the system.

    You could try running

    /usr/local/cpanel/bin/update-roundcube-db

    to see if that takes care of things in general on the server, or you can move 

    /home/username/etc/domain/username.rcube.db

    out of the way to work with a specific user.

    Ultimately, it may be best to create a ticket on this issue.

    0
  • Arkadiusz MiÅ›kiewicz

    These are just (bad) workarounds.

    Anyway I changed 0014-unifytimestampdefaultvalue migration procedure here locally, so it succeeded for my users (and upgraded roundcube sqlite databases) but it would be nice if upstream cpanel also was fixed for other customers.

    I'm leaving these two bugs (one carddav migration and one null being passed in 117 line: throw new DatabaseException($dbh->is_error());) reported here since I cannot directly create ticket at support.cpanel.

    cpanel support form doesn't allow creating tickets for "Partner Supported License" (OVH in my case).

    I hope cpanel devs look at forum sometimes...

    0
  • cPRex Jurassic Moderator

    It's not so much that the devs do or don't look at the Forums (I contacted the devs before my earlier post) but we'd like to see a system where we can reproduce the issue.

    0
  • Arkadiusz MiÅ›kiewicz

    Reproducer below, should be easy to prepare system with such problem.

    % sqlite3 test.db
    SQLite version 3.51.0 2025-06-12 13:14:41
    Enter ".help" for usage hints.
    sqlite> -- Here you can drop column or rename column
    sqlite> CREATE TABLE IF NOT EXISTS TABLE_PREFIXcarddav_addressbooks2 (
    (x1...>         id           integer NOT NULL PRIMARY KEY,
    (x1...>         name         TEXT NOT NULL,
    (x1...>         username     TEXT NOT NULL,
    (x1...>         password     TEXT NOT NULL,
    (x1...>         url          TEXT NOT NULL,
    (x1...>         active       TINYINT UNSIGNED NOT NULL DEFAULT 1,
    (x1...>         user_id      integer NOT NULL,
    (x1...>         last_updated BIGINT NOT NULL DEFAULT 0,  -- time stamp (seconds since epoch) of the last update of the local database
    (x1...>         refresh_time INT NOT NULL DEFAULT 3600, -- time span (seconds) after that the local database will be refreshed, default 1h
    (x1...>         sync_token   TEXT NOT NULL DEFAULT '', -- sync-token the server sent us for the last sync
    (x1...>
    (x1...>         presetname   TEXT,                  -- presetname
    (x1...>
    (x1...>         use_categories TINYINT NOT NULL DEFAULT 0,
    (x1...>
    (x1...>         -- not enforced by sqlite < 3.6.19
    (x1...>         FOREIGN KEY(user_id) REFERENCES TABLE_PREFIXusers(user_id) ON DELETE CASCADE ON UPDATE CASCADE
    (x1...> );
    sqlite> INSERT INTO TABLE_PREFIXcarddav_addressbooks2
       ...> (name, username, password, url, active, user_id, last_updated, refresh_time, sync_token, presetname, use_categories)
       ...> VALUES
       ...> ('cPanel CardDAV','%l@%d','{BASE64}JXA=','https://jsomeserver:2080/addressbooks/__uids__/7DE7FC60EC81/addressbook/',1,1,1662728777,3600,'data:,1899_1311/6fd686e900e1f9d89abc33b0cf76281e','cPCardDAV',0);
    sqlite> select * from TABLE_PREFIXcarddav_addressbooks2;
    1|cPanel CardDAV|%l@%d|{BASE64}JXA=|https://jsomeserver:2080/addressbooks/__uids__/7DE7FC60EC81/addressbook/|1|1|1662728777|3600|data:,1899_1311/6fd686e900e1f9d89abc33b0cf76281e|cPCardDAV|0
    sqlite> UPDATE TABLE_PREFIXcarddav_addressbooks2 SET last_updated='1970-01-01 00:00:00' WHERE last_updated=0;
    sqlite> UPDATE TABLE_PREFIXcarddav_addressbooks2 SET last_updated=strftime('%s', last_updated);
    Runtime error: NOT NULL constraint failed: TABLE_PREFIXcarddav_addressbooks2.last_updated (19)
    sqlite>

    0
  • cPRex Jurassic Moderator

    Thanks for that - I'll do some testing in a bit and I'll let you know what I find!

    0
  • cPRex Jurassic Moderator

    I'm not able to make this table creation process work on a test machine.  When I run the second set of commands you listed I get an error:

    Error: table cptest_carddav_addressbooks2 has no column named use_categories

    It would be best to create a ticket with your hosting/license provider and then they can escalate the issue to us if necessary.

    0
  • Arkadiusz MiÅ›kiewicz

    But above CREATE TABLE contains use_categories definition, so it has to exist (unless you already had cptest...2 table from some earlier tests).

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post