Horde collation errors now seen during backup

[Trane Francks]

With the recent update to WHM 11.42.0 (build 22), I'm seeing collation errors in our daily backups, e.g.:

DBD::mysql::db do failed: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' at /usr/local/cpanel/Cpanel/Horde/Utils.pm line 56.
Fail to backup horde data for user xxxxxxxx: DBD::mysql::db do failed: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' at /usr/local/cpanel/Cpanel/Horde/Utils.pm line 56.

These are brand new errors, not appearing prior to March 20th. How should I go about fixing these errors so that these user databases will be correctly backed up?

 

[biovolt]

I get the same errors did you mange to find any info regarding this?

 

[Trane Francks]

No, nothing yet.

 

[Infopro]

See if this post by cPanelMichael is useful:
Re: back up problem - cPanel Forums

 

[Trane Francks]

The tables aren't corrupted, the collation initially used by Horde is no longer supported. If we assume that forcing an update will recreate the database, there's no reason to assume that the collation will be any different in the new database.

 

[Trane Francks]

Last night, WHM got upgraded on Stable to 11.44.1.17. With the upgrade came a new version of MySQL and new and improved Horde backup errors:

Fail to backup horde data for user xxxxxxxx: DBD::mysql::db do failed: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
 at /usr/local/cpanel/Cpanel/Exception.pm line 96
	Cpanel::Exception::new('Cpanel::Exception::Database::Error', 'Cpanel::ExceptionMessage::Raw=SCALAR(0xb9f41e8)', 'HASH(0xe980288)') called at /usr/local/cpanel/Cpanel/Exception.pm line 47
	Cpanel::Exception::create('Database::Error', 'Cpanel::ExceptionMessage::Raw=SCALAR(0xb9f41e8)', 'HASH(0xe980288)') called at /usr/local/cpanel/Cpanel/Exception.pm line 55
	Cpanel::Exception::create_raw('Database::Error', 'DBD::mysql::db do failed: Illegal mix of collations (utf8_gen...', 'HASH(0xe980288)') called at /usr/local/cpanel/Cpanel/DBI.pm line 79
	Cpanel::DBI::_create_exception('Cpanel::DBI::Mysql::db=HASH(0xe979b38)', 'DBD::mysql::db do failed: Illegal mix of collations (utf8_gen...', undef) called at /usr/local/cpanel/Cpanel/DBI.pm line 70
	Cpanel::DBI::_error_handler('DBD::mysql::db do failed: Illegal mix of collations (utf8_gen...', 'Cpanel::DBI::Mysql::db=HASH(0xe979b38)', undef) called at /usr/local/cpanel/Cpanel/Horde/Utils.pm line 56
	Cpanel::Horde::Utils::build_backup('Cpanel::DBI::Mysql::db=HASH(0xe979b38)', 'ARRAY(0xd01ad00)', '/backup/cpbackup/daily/xxxxxxxx/horde', 5.1.4, 'horde') called at /usr/local/cpanel/Cpanel/Horde/Backup.pm line 44
	Cpanel::Horde::Backup::backup('Cpanel::Horde=HASH(0xe97ea30)', 'xxxxxxxx', '/backup/cpbackup/daily/xxxxxxxx/horde', 0) called at /usr/local/cpanel/scripts/pkgacct line 1092
	eval {...} called at /usr/local/cpanel/scripts/pkgacct line 1092
	Script::Pkgacct::script('Script::Pkgacct', 'xxxxxxxx', '/backup/cpbackup/daily', 'backup') called at /usr/local/cpanel/scripts/pkgacct line 116
…Done

These errors are visible for each user that has created Horde settings.

I repeat my original question: How do I fix this?

 

[cPanelMichael]

Please feel free to open a support ticket using the link in my signature so we can take a closer look. You can post the ticket number here so we can update this thread with the outcome.

Thank you.

 

[Trane Francks]

Hi, Michael.

In the last 24 hours, I dug deeper into the issue. In examining the horde database in phpMyAdmin, there were no fewer than three different collations. I considered changing them manually, but there is always a risk of corrupting tables when changing collations. There's just no guarantee that things will work properly after the fact. Because of this, I decided to be more drastic: Nuke-and-Pave.

I disabled Horde altogether, dropped the database and then re-enabled Horde to have the database created from scratch. This new database does, indeed, have the correct collations across all the tables, so I'm at a loss as to how the original problem happened in the first place. There are only two of us who admin this server and neither of us had ever mucked with the DB collations.

Of course, all user settings were lost in this process, but I highly doubt that anybody is actually using webmail on this server with any regularity anyway. There were no errors visible in the backup log, but also no Horde settings were being backed up. We'll revisit the topic should backups of user Horde settings present again.

Thanks for your patience with my whinging.

 

[Trane Francks]

So, a couple of users have used Horde and the backup errors have reappeared. Manually backing up the Horde database with 'mysqldump horde' works just fine, so the problem appears to not necessarily be with the horde database itself. It seems that this could be an issue with the script expecting particular settings.

I also note that another user has posted the identical problem here: http://forums.cpanel.net/f49/backup-error-message-422311.html

 

[Trane Francks]

Following up yet again with this, I checked /etc/my.cnf and noticed that the collation was set to utf8_unicode_ci. This indicates that Horde itself is expecting a different UNICODE collation to be used. When a user creates settings, Horde is adding new (or modifying existing) tables using utf8_general_ci. Then when the legacy backup script runs on our server, we see collation mismatches.

So.

I changed the collation in /etc/my.cnf as:

collation_server=utf8_general_ci

I've restarted MySQL. I disabled Horde in Tweak Settings. And then in phpMyAdmin, I changed the collation of all the tables to utf8_general_ci prior to enabling Horde again. We'll see whether that fixes things. If it does, I'll be both pleased and mildly annoyed. Horde should be able to work with the server's default collation and not require something different.

 

[cPanelMichael]

Please ensure you open a support ticket if the issue continues so we can take a closer look the next time this happens.

Thank you.