SOLVED mysql to sqlite roundcube script fails

[bloatedstoat]

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.

 

[cPRex]

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?

 

[bloatedstoat]

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.

 

[cPRex]

I'm glad that worked! It does look like we removed the "--force" option, so I just need to update my brain now.

 

[bloatedstoat]

I'm glad that worked! It does look like we removed the "--force" option, so I just need to update my brain now.

Read it again @cPRex, it didn't work, it says it already converted but still throws an error in the Transfer Tool as having not been converted.

 

[cPRex]

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]

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]

I mentioned in an older post here that I've never let that message stop me from doing a migration:

Roundcube database type

I need to transfer a single cPanel account from one VPS to another. Source is running cPanel 86.0.40 while target running 96.0.11. While I attempting to transfer using the Transfer tool, I get the following message: Source: “x.x.x.x” Roundcube database type is “mysql” … Target...

forums.cpanel.net

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.

 

[bloatedstoat]

I would just do the transfer at this point since everything seems to be in place.

I have completed the transfer of some accounts that use the old Horde system to test and at this stage it seems that all has gone to plan.

Thanks for the help on this.

Cheers.

 

[cPRex]

I'm glad to hear all went well!