cPanel Horde Data Conversion from MySQL to SQLite

The script used in cPanel to switch and convert Horde user data from a single shared MySQL database to individual SQLite databases per cPanel user is called horde_mysqltosqlite. This script will run automatically when you upgrade your cPanel & WHM Version from 11.48 to 11.50. If any issues occur during the conversion process, a notification will be sent to the user, informing them that they have added it in WHM’s Primary Webhost Manager Setup interface. To access the Setup mentioned above, follow these steps:

1. Log in to WHM as a root user.
2. Navigate to the ‘Server Configuration’ section.
3. Select the ‘Primary WebHost Manager’ Setup.

The horde_mysqltosqlite script executes the Horde database conversion in the following order:

1. Firstly, the script creates a backup of the Horde MySQL database. If the backup already exists, it will be skipped. Make sure to keep this backup until you confirm that the script has successfully converted the Horde user data.
2. The script then checks for any schema issues in the Horde MySQL database and repairs them.
3. Next, the script converts the single shared Horde MySQL database to individual SQLite databases per cPanel user.
4. After the successful conversion of the database, the script removes the Horde MySQL database.

Only the root user can execute the horde_mysqltosqlite script, and they can use the following formats for the specified actions.

1) Convert All Users

Log in to your server as the root user and run the following command to convert all Horde users from the MySQL database to the SQLite database.

$ /usr/local/cpanel/scripts/horde_mysqltosqlite

2) Convert a Single User

Run the following command as the root user from your server to convert a single user from the MySQL database to the SQLite database.

$ /usr/local/cpanel/scripts/horde_mysqltosqlite –user [user]

3) Convert Multiple Users

If you want to convert multiple users from the MySQL database to the SQLite database, you can run the following command. You can specify multiple user IDs with a space in between them so that this command converts the mentioned users’ data from MySQL to SQLite.

$ /usr/local/cpanel/scripts/horde_mysqltosqlite --user [user1] [user2] [user3]

Some of the exhaustive arguments used together with the horde_mysqltosqlite script are the following:

Option	Description
–user	To specify the users to convert.
–force-backup	To overwrite the MySQL database backup if it exists.
–no-backup	To skip the MySQL database backup during conversion.
–no-convert	Run the script without the conversion process.
–no-fix-schema	To skip the MySQL database schema repair operation.
–no-drop	To skip the MySQL database removal.
–force-drop	Forcefully remove the MySQL database. Only use this flag after you have transferred all Horde user data.
–replace-dest-db	Option to move the current SQLite database and create a new one.
–verbose	To generate more output details.

The SQLite Horde Database and The ~/.cphorde Directory

In cPanel and WHM version 11.50 and later, under the accounts home directory, each cPanel account has its own .cphorde directory. The .cphore directory contains the following items:

Name	Type	Description
Horde.sqlite	SQLite database	This database contains the Horde data for all the cPanel accounts.
meta	Directory	This directory is used by the backup services to create the Horde database backups.
vfsroot	Directory	The /usr/local/cpanel/base/horde/config/conf.php file uses this directory for the vfsroot setting.

Troubleshoot Horde Data Conversion from MySQL to SQLite

The most common issues encountered during the Horde data conversion from MySQL to SQLite are:

Horde Database Backup Already Exists

While converting the Horde data from MySQL to SQLite, the system throws an error stating that the Horde database backup already exists. The entry of the same is made in the logs as shown in the following snippet:

This problem can be resolved by checking the integrity of the backup. You can follow the steps below:

1) Check if the current backup is valid by running the following command, or you can manually check the same from the “/var/cpanel/horde/horde.backup.sql.last” file.

$ awk '/cPanel dump:/d++ENDprintf("%s bavkupn",d==2?"good":"unhealthy")' /var/cpanel/horde/horde.backup.sql.last

2) After validation, you can run the horde_mysqltosqlite script with the “no backup” option.

$ /usr/local/cpanel/scripts/horde_mysqltosqlite --no-backup

3) If you want a fresh backup and do not want to use the current one, you can move the backup file and then run the “/usr/local/cpanel/scripts/horde_mysqltosqlite” script. You can move the current backup using the following command.

$ mv -v /var/cpanel/hore/horde.backup.sql.last,.bakdate +%s

MySQL Horde Database Schema Fixer Failed

While converting the Horde database, there is a step to fix all the MySQL Horde database schema issues. If the Horde schema fixer step fails, you may see the following entry in the log:

BEGIN ERROR: Failed the following assertion with error:

If you encounter this error in the log, it means that the conversion script is unable to verify and repair the schema of the MySQL Horde database.

If you want to complete the conversion process without the schema repair, you can run the horde_mysqltosqlite script with the “no fix schema” option.

 $ /usr/local/cpanel/scripts/horde_mysqltosqlite --no-fix-schema

Horde SQLite Database Troubleshooting

If you delete a cPanel account’s Horde SQLite database, you will lose all the account’s calendar and contact information. Some common issues with the Horde SQLite database are:

1) Reset the Horde Database Password

The SQLite database relies on filesystem permissions and not a password for access control. Hence, this action cannot be performed.

2) Create a Missing Database for a cPanel User

If you want to create a missing database for a cPanel user, you can run the following command:

# /usr/local/cpanel/bin/update_horde_config --user=<username>

If you want to create or update the tables in the missing directory, you need to run the following command:

# /usr/local/cpanel/bin/update_horde_config --user=<username>  --full

3) Creating Missing Database for All cPanel Users on the System

If you want to create a missing database for all cPanel users, you can run the following command:

# /usr/local/cpanel/bin/update_horde_config

If you want to create or update the tables in the missing directory, you need to run the following command:

# /usr/local/cpanel/bin/update_horde_config --full

So, this is how you can convert MySQL to SQLite in Horde. If you have any questions regarding this tutorial or the process, please let us know in the comment section below.