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:
- Log in to WHM as a root user.
- Navigate to the ‘Server Configuration’ section.
- Select the ‘Basic WebHost Manager’ Setup.
The horde_mysqltosqlite script runs the Horde database conversion in the following order:
- 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.
- The script then checks for any Horde MySQL database schema problems and repairs it.
- Then the script converts the Horde MySQL single shared database to one-per-cPanel-user SQLite databases.
- 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:
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.