cPanel Horde Data Conversion from MySQL to SQLite

Posted on January 2nd, 2020

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:

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 function
–no-drop To skip the MySQL database removal
–force-drop Forcefully remove the MySQL database. Only use this flag after you convert all Horde user data.
–replace-dest-db Option to move the existing SQLite database and create a new one.
–verbose To generate additional 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 acquires its own .cphorde directory. The .cphore directory includes 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 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:

Horde data conversion

 

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

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 given below.

Leave a Reply