SOLVED mysql to sqlite roundcube script fails

Operating System & Version
CloudLinux 6.10
cPanel & WHM Version
110.0.5

bloatedstoat

Well-Known Member
Jun 14, 2012
216
33
78
Victoria, Australia
cPanel Access Level
Root Administrator
Hello,

We're on the verge of a migration using the live transfer tool.
On the destination server after invoking a transfer session the destination server scans the Source.
After scanning the source server a notification is shown:

Source: “sourceserver.com” Roundcube database type is “mysql” …
Target: “destination.com” Roundcube database type is “sqlite” …

The source server is not running the same Roundcube database type as the target server. cPanel highly recommends that the source server run the same Roundcube database type at least 24 hours to ensure that email accounts using Roundcube will transfer properly.


To address this the documentation suggests:
/usr/local/cpanel/scripts/convert_roundcube_mysql2sqlite

After running the script the following is returned and the conversion fails.
Seems the sql is referencing columns in tables that don't actually exist such as 'user_id'.


[2023-05-12 19:34:11 +1000] warn [convert_roundcube_mysql2sqlite] Attempt to find necessary data from calendar_id in ical_calendars failed: Table 'roundcube.ical_calendars' doesn't exist
[2023-05-12 19:34:11 +1000] warn [convert_roundcube_mysql2sqlite] Attempt to find necessary data from calendar_id in caldav_calendars failed: Unknown column 'user_id' in 'where clause'
[2023-05-12 19:34:17 +1000] warn [convert_roundcube_mysql2sqlite] Failed to load xml for caldav_calendars: Document requires an element [Ln: 1, Col: 0]
XML::Simple called at /usr/local/cpanel/scripts/convert_roundcube_mysql2sqlite line 486.
[2023-05-12 19:34:17 +1000] info [convert_roundcube_mysql2sqlite] Conversion of roundcube XML dump to sqlite database failed
[2023-05-12 19:34:17 +1000] warn [convert_roundcube_mysql2sqlite] Conversion was not successful for user 'redacted'.
[2023-05-12 19:35:13 +1000] warn [convert_roundcube_mysql2sqlite] Roundcube Mysql to sqlite conversion was not completely successful.
Please check /usr/local/cpanel/logs/roundcube_sqlite_convert_log for details.


I'd be keen to get this sorted before migration if it's actually possible.
Anyone else experienced this and have a solution?

Source server is an old CL6.10 box running MariaDB10.3, cpanel 110.0.5

Thank you.
 
Last edited by a moderator:

bloatedstoat

Well-Known Member
Jun 14, 2012
216
33
78
Victoria, Australia
cPanel Access Level
Root Administrator
Hey there! Can you try running this command on the system first?

/usr/local/cpanel/bin/update-roundcube-db --force --foreground

and then proceed with the conversion?

Thanks for this, --force option can be dropped as it's not a parameter but after running the command here is the output.

Roundcube is configured to use SQLite.

This update is for Roundcube using MySQL. Running /usr/local/cpanel/bin/update-roundcube-sqlite-db.
Roundcube update from 1.6.0.10-1.cp110~el8 to 1.6.0.10-1.cp110~el8 in progress.
info [update-roundcube-sqlite-db] Roundcube will be locked out during this process.
Roundcube updated to 1.6.0.10-1.cp110~el8

[root@server~]# /usr/local/cpanel/scripts/convert_roundcube_mysql2sqlite
info [convert_roundcube_mysql2sqlite] Roundcube conversion already occurred, bailing out.


The message on the destination server remainjs the same within the Transfer Tool after scanning the source.

Source: “source.server” Roundcube database type is “mysql” …
Target: “destination.server” Roundcube database type is “sqlite” …

The source server is not running the same Roundcube database type as the target server. cPanel highly recommends that the source server run the same Roundcube database type at least 24 hours to ensure that email accounts using Roundcube will transfer properly.


The question now is, which to believe.

Cheers.
 
Last edited:

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
16,651
2,636
363
cPanel Access Level
Root Administrator
Ah, I completely misread that when I checked earlier.

Since it said "already occurred, bailing out" that would indicate that the conversion didn't need to be run at all, but the Transfer Tool is having trouble detecting the version. One easy way to tell what is happening is to see if there is a Roundcube database in /var/lib/mysql. If so, it likely has not been converted to SQLite as that would be stored in the user's home directory.
 

bloatedstoat

Well-Known Member
Jun 14, 2012
216
33
78
Victoria, Australia
cPanel Access Level
Root Administrator
On the strength of it then the conversion failed as /var/lib/mysql/roundcube exists on the source server with a whole bunch of files still within it.
On the destination no roundcube directory exists in /var/lib/mysql.

Are you saying once the conversion runs successfully /var/lib/mysql/roundcube is removed?

Thank you.
 

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
16,651
2,636
363
cPanel Access Level
Root Administrator
I mentioned in an older post here that I've never let that message stop me from doing a migration:


I don't believe we instantly remove the older MySQL data just in case something does go wrong.

I'm wondering if the Transfer Tool is still seeing that older data on your machine and throwing that error. I would just do the transfer at this point since everything seems to be in place.
 
  • Like
Reactions: bloatedstoat