How to Convert Roundcube to SQLite

Last modified: February 8, 2024

Overview

The convert_roundcube_mysql2sqlite script (located at /usr/local/cpanel/scripts/) facilitates the update of your Roundcube configuration to utilize individual SQLite databases instead of a single MySQL® database. This document provides a comprehensive guide on employing this script.

Important Note: Once Roundcube's database is converted to SQLite, reverting to a MySQL database is not supported.

Migrating to a SQLite database offers several advantages over a MySQL database:

• Simplified Data Restoration: Roundcube data for each email user is stored in a separate SQLite database. This granular approach allows for the restoration of data for a single user without impacting the entire system.
• Accurate Disk Usage Tracking: Roundcube data is stored within a cPanel user’s /home directory, enabling proper tracking of disk usage.
• Easier Identification of Problematic Accounts: Roundcube PHP processes run under the cPanel user, making it simpler to identify accounts experiencing issues.
• Enhanced Reliability: The system avoids a single point of failure; database corruptions will only affect an individual user, not the entire system.

Path and Filename for the SQLite Database

The SQLite database uses the following path and filename structure, where username refers to your cPanel account username and domain to your associated domain name:

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

Convert Roundcube to SQLite Database

To initiate the conversion of the Roundcube database from MySQL to SQLite across your entire system, execute the following command:

/usr/local/cpanel/scripts/convert_roundcube_mysql2sqlite

Once the system has been converted to a SQLite database, this script can also be utilized for troubleshooting issues related to individual users.

To convert Roundcube data for a specific cPanel user from MySQL to SQLite, append the user parameter to the command, replacing user with the relevant database username:

/usr/local/cpanel/scripts/convert_roundcube_mysql2sqlite user

Migrate a Roundcube MySQL Database to a SQLite Database

To migrate a Roundcube database between cPanel & WHM servers and simultaneously convert the data from MySQL to SQLite, follow these steps:

1. On the source cPanel & WHM server, create a backup of your Roundcube database. Execute the following command, where roundcube refers to the name of your Roundcube database:

   mysqldump roundcube > roundcube.dump

2. On the destination cPanel & WHM server, create a temporary MySQL database. Refer to our Manage My Databases documentation for detailed instructions.
3. On the destination cPanel & WHM server, open the /usr/local/cpanel/scripts/convert_roundcube_mysql2sqlite script and comment out the following lines of code:

                   unless ( defined $opt_dbname ) {
                       Cpanel::Email::RoundCube::archive_and_drop_mysql_roundcube($logger);
                   }

4. On the destination cPanel & WHM server, import the Roundcube data into the temporary database and proceed with the conversion to SQLite. Execute the following command, replacing user with the database username and exampledatabase with the name of the database where the Roundcube data will be imported:

   /usr/local/cpanel/scripts/convert_roundcube_mysql2sqlite user exampledatabase

5. The server will attempt to convert the Roundcube mailbox data. If any errors occur during the process, the conversion will be retried until it completes successfully.

Further Resources

• The Webmail Interface
• Webmail Clients
• How to Manually Migrate Horde Data to Roundcube
• How to Set Up Calendars and Contacts
• The export_horde_calendars_to_ics Script
• The cPanel Glossary