The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Horde collation errors now seen during backup

Discussion in 'Data Protection' started by Trane Francks, Mar 24, 2014.

  1. Trane Francks

    Trane Francks Well-Known Member

    Joined:
    Jun 19, 2012
    Messages:
    56
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Machida, Tokyo, Japan
    cPanel Access Level:
    Root Administrator
    With the recent update to WHM 11.42.0 (build 22), I'm seeing collation errors in our daily backups, e.g.:

    Code:
    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?
     
  2. biovolt

    biovolt Registered

    Joined:
    Aug 5, 2011
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    I get the same errors did you mange to find any info regarding this?
     
  3. Trane Francks

    Trane Francks Well-Known Member

    Joined:
    Jun 19, 2012
    Messages:
    56
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Machida, Tokyo, Japan
    cPanel Access Level:
    Root Administrator
    No, nothing yet.
     
  4. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,466
    Likes Received:
    196
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
  5. Trane Francks

    Trane Francks Well-Known Member

    Joined:
    Jun 19, 2012
    Messages:
    56
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Machida, Tokyo, Japan
    cPanel Access Level:
    Root Administrator
    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.
     
  6. Trane Francks

    Trane Francks Well-Known Member

    Joined:
    Jun 19, 2012
    Messages:
    56
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Machida, Tokyo, Japan
    cPanel Access Level:
    Root Administrator
    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:

    Code:
    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?
     
    #6 Trane Francks, Aug 25, 2014
    Last edited: Aug 25, 2014
  7. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,765
    Likes Received:
    662
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
  8. Trane Francks

    Trane Francks Well-Known Member

    Joined:
    Jun 19, 2012
    Messages:
    56
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Machida, Tokyo, Japan
    cPanel Access Level:
    Root Administrator
    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.
     
  9. Trane Francks

    Trane Francks Well-Known Member

    Joined:
    Jun 19, 2012
    Messages:
    56
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Machida, Tokyo, Japan
    cPanel Access Level:
    Root Administrator
    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
     
  10. Trane Francks

    Trane Francks Well-Known Member

    Joined:
    Jun 19, 2012
    Messages:
    56
    Likes Received:
    1
    Trophy Points:
    8
    Location:
    Machida, Tokyo, Japan
    cPanel Access Level:
    Root Administrator
    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.
     
  11. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,765
    Likes Received:
    662
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator

Share This Page