cPanel Horde Data Conversion from MySQL to SQLite

The horde_mysqltosqlite script in cPanel is used to transfer and convert Horde user data from a single shared MySQL database to one-per-cPanel-user SQLite databases. This script runs when you upgrade your cPanel & WHM Version from 11.48 to 11.50. If the system experiences any issue during the conversion process, it sends a notification to the user, saying that “You added in WHM’s Basic Webhost Manager Setup interface”. You can access the above Setup by following these steps:

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

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

1. Firstly, the script creates a backup of the Horde MySQL database. If the backup exists, then it is skipped. Always make sure to retain this backup until you verify that the script successfully converted the Horde user data.
2. The script then checks for any Horde MySQL database schema problems and repairs it.
3. Then the script converts the Horde MySQL single shared database to one-per-cPanel-user SQLite databases.
4. After the successful conversion of the database, the script removes the Horde MySQL database.

The root user can only execute the horde_mysqltosqlite script, and they can use the following formats for the desired actions.

1) Convert All the Users

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

$ /usr/local/cpanel/scripts/horde_mysqltosqlite

2) Convert a Single User

Run the below command as 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 MySQL database to SQLite database, then you can run the below command. You can mention multiple user IDs with space in between them so that this command converts the mentioned user’s data from MySQL to SQLite.

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

Some of the exhaustive argument used along with the horde_mysqltosqlite script is the following:

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 acquires its own .cphorde directory. The .cphore directory includes the following items:

Troubleshoot Horde Data Conversion from MySQL to SQLite

The standard-issue occurred 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 saying that the Horde database backup already exists. The entry of the same is made in the logs as shown in the below snippet:

We can resolve this problem by checking the integrity of the backup. You can follow the below step for the same.

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

$ awk ‘/cPanel dump:/{d++}END{printf(“%s bavkup\n”,d==2?"good":"bad”)}’ /var/cpanel/horde/horde.backup.sql.final

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 don’t want to use the existing one. Then you can move the backup file and then run the “/usr/local/cpanel/scripts/horde_mysqltosqlite” script. You can move the existing backup using the below command.

$ mv -v /var/cpanel/hore/horde.backup.sql.final{,.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 then you can see the below entry in the log:

BEGIN ERROR: Failed the next statement with error:

If you receive this error in the log, then the conversion script is not able to verify and repair the schema of the MySQL Horde database.

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

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

Horde SQLite Database Troubleshooting

If you delete a cPanel account’s Horde SQLite database, then you lose all of the account’s calendar and contact information. Some of the common problems in Horde SQLite database are:

1) RESET THE HORDE DATABASE PASSWORD

The SQLite database relies on filesystem permissions and not a password for access control. So, 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, then you can run the below command:

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

If you want to create or update the tables in the missing directory then you need to run the below 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 user, then you can run the below command:

# /usr/local/cpanel/bin/update_horde_config

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

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