Translation(s): none


Roundcube: Deprecation of SQLite v2

Important note: By now there is roundcube-sqlite3 in jessie and wheezy-backports, so instead of switching to MySQL you can also upgrade to sqlite3. See Option B at the end of this page.

SQLite v2 support in PHP has been deprecated since it a long time. It was unmaintained. It has been removed from PHP 5.4 which is the PHP version shipped with Debian Wheezy. Projects using SQLite were expected to migrate to SQLite v3. Roundcube does not currently supports SQLite v3. Therefore, you have to use it with another database, like MySQL and PostgreSQL.

If you have a running installation of Roundcube using SQLite v2, you will not be able to upgrade without manual steps. The upgrade process will fail to upgrade Roundcube related packages as along as you have roundcube-sqlite package. Here are the steps to ensure a proper upgrade of Roundcube. Feel free to complete this wiki with your experience.

We suppose here that you are upgrading from Roundcube 0.3.1-6, as it is available in Debian Squeeze. The following instructions also work for later versions but you will need to adapt. Moreover, if you are using SQLite as a backend but didn't install roundcube-sqlite package (which is just a metapackage), be sure to follow this guide before upgrading. If you have already upgraded, the procedure may be a little different. Notably, you will also have to handle database schema changes.

Option A, Switch to MySQL with these upgrade steps

During the upgrade, make sure that your Roundcube installation is not accessible to users. The upgrade is not seamless. We assume that you either did not start the upgrade to Wheezy or that the upgrade to Wheezy failed because roundcube-sqlite package was not available.

1. Backup the database

On upgrade, the previous database is automatically saved by dbconfig-common, the subsystem handling database tasks in Debian, in /var/cache/dbconfig-common/backups. However, since the upgrade process did not happen yet, it is better to do the backup now. Your database is in /var/lib/dbconfig-common/sqlite/roundcube/roundcube. Just copy this file to a safe place.

2. Install MySQL

While SQLite was convenient for small installations because it didn't require a dedicated database process, this is not the case for the other supported databases. We will assume that you want to use MySQL but this will also work fine for PostgreSQL. So you need to install MySQL server:

# apt-get install mysql-server mysql-client

You don't need to provide a password.

3. Change database

Reconfigure roundcube and select mysql as the database. This will create for you an empty database and configure Roundcube appropriately. You don't have to provide any password when prompted if you didn't provide one on the previous step.

# apt-get install roundcube-mysql
# dpkg-reconfigure roundcube-core

When a question is not related to the database, just keep the default choice. When you get a prompt about reinstalling the database, answer yes. Then, choose mysql. Other database related questions can be answered with the default choice.

4. Dump the content of the SQLite database

The next step is to dump the content of the SQLite database. We suppose you have the backup file roundcube in the current directory. We have three tables that we need to migrate to MySQL:

The other tables are session and cache stuff. You can lose their contents. You can dump each table with those commands:

output() {
    awk '/\|/ {
    split($0, col_name, "|");
    column_names[++n] = col_name[2];
}
/^BEGIN/ {
    printf("BEGIN;\n");
    sql = 1;
}
/^INSERT INTO/ {
    line=$0
    insert_part = match(line, /^INSERT INTO \"[A-Za-z].*\"/);
    if (insert_part == 0) {
        match(line, /^INSERT INTO [^ ]*/);
    }
    printf("%s ", substr($0, RSTART, RLENGTH));

    printf("(");
    for (i = 1; i <= n; i++) {
        if (i == 1) {
            printf("`%s`", column_names[i]);
        }
        else {
            printf(", `%s`", column_names[i]);
        }
    }
    printf(") ");

    values_part = substr($0, RLENGTH+1, length($0) - RSTART);
    printf("%s\n", values_part);
}
/^COMMIT/ {
    print;
}' > $1
}

for table in identities contacts users ; do
    cat <<EOF | sqlite roundcube | output $table.sql
pragma table_info($table);
.dump $table
EOF
done

You can copy/paste the above code in a file named convert.sh and execute it with sh convert.sh. You should get three files with SQL instructions.

If the generated .sql truncates multi-line contact vcard fields, try the following patch convert.sh:

--- convert.sh.orig     2014-08-24 00:37:23.733223772 +0300
+++ convert.sh  2014-08-24 00:37:59.901210893 +0300
@@ -6,6 +6,7 @@
 /^BEGIN/ {
     printf("BEGIN;\n");
     sql = 1;
+    RS=";\n"
 }
 /^INSERT INTO/ {
     line=$0
@@ -27,7 +28,7 @@
     printf(") ");
 
     values_part = substr($0, RLENGTH+1, length($0) - RSTART);
-    printf("%s\n", values_part);
+    printf("%s;\n", values_part);
 }
 /^COMMIT/ {
     print;

5. Transfer the content of the database to MySQL

This is the difficult step. Watch carefully for any error that may happen. You can always start from zero by emptying the tables (with TRUNCATE contacts for example). Just issue the following commands:

# mysql roundcube < users.sql
# mysql roundcube < contacts.sql
# mysql roundcube < identities.sql

You can then check if Roundcube works as expected.

6. Finish upgrade

You can then remove roundcube-sqlite package and resume (or start) upgrade.

Option B: switch to roundcube-sqlite3

  1. Stop roundcube/the webserver
  2. Upgrade to roundcube from jessie or wheezy-backports
  3. Install roundcube-sqlite3
  4. Backup your sqlite2 database:  cd /var/lib/dbconfig-common/sqlite/roundcube/ ; cp roundcube roundcube.bak 

  5. Upgrade this database to sqlite3:  sqlite roundcube .dump | sqlite3 new_roundcube ; mv new_roundcube roundcube 

  6. Start roundcube/the webserver


CategoryPermalink