Bug #39378 Backup: backup logs with default character set latin1 are not upgraded correctly

• Submitted: 10 Sep 2008 18:41
• Modified: 3 Sep 2010 8:42
• Reporter: Chuck Bell
• Status: Unsupported
• Category: MySQL Server: Backup
• Severity: S3 (Non-critical)
• Version: 6.0.7
• OS: Any
• Assigned to: Ingo Strüwing
• CPU Architecture: Any

[10 Sep 2008 18:41] Chuck Bell

Description:
The MySQL backup logs are not upgraded correctly when the mysql_upgrade application is run. The default character set is not updated to UTF8.

How to repeat:
Ensure the backup_history and backup_progress logs (tables) in mysql database use a default character set other than utf8. You can set this by issuing:

ALTER TABLE mysql.backup_history DEFAULT CHARSET=latin1;
ALTER TABLE mysql.backup_progress DEFAULT CHARSET=latin1;

Run the mysql_upgrade application and check the CREATE TABLE for the logs.

SHOW CREATE TABLE mysql.backup_history;
SHOW CREATE TABLE mysql.backup_progress;

They should each be set to utf8.

Suggested fix:
Apply this patch to the mysql_system_tables_fix.sql script: 

=== modified file 'scripts/mysql_system_tables_fix.sql'
--- scripts/mysql_system_tables_fix.sql 2008-08-07 03:05:33 +0000
+++ scripts/mysql_system_tables_fix.sql 2008-09-10 17:47:25 +0000
@@ -231,6 +231,8 @@ ALTER TABLE time_zone_leap_second CONVER
 ALTER TABLE time_zone_name CONVERT TO CHARACTER SET utf8;
 ALTER TABLE time_zone_transition CONVERT TO CHARACTER SET utf8;
 ALTER TABLE time_zone_transition_type CONVERT TO CHARACTER SET utf8;
+ALTER TABLE backup_history CONVERT TO CHARACTER SET utf8;
+ALTER TABLE backup_progress CONVERT TO CHARACTER SET utf8;

 # Bug#31291: Avoid conversion from text to mediumtext
 ALTER TABLE help_topic

[10 Sep 2008 19:05] Sveta Smirnova

Thank you for the report.

Verified as described.

[11 Sep 2008 14:37] Chuck Bell

The solution should also consider the name changes introduced in WL#4296. Specifically, the patch should remove the old tables and allow the new log files (tables) to be created.

ref:

online_backup became backup_history
online_backup_progress became backup_progress

[31 Mar 2010 10:28] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/104685

3115 Ingo Struewing	2010-03-31
      Bug#39378 - Backup: backup logs with default character set latin1
                  are not upgraded correctly
      
      The MySQL backup logs were not upgraded correctly when the mysql_upgrade
      application is run. The default character set was not updated to UTF8.
      
      Added ALTER statements to mysql_system_tables_fix.sql.
     @ mysql-test/suite/backup/r/backup_mysql_upgrade.result
        Bug#39378 - Backup: backup logs with default character set latin1 are not upgraded correctly
        Updated test result.
     @ mysql-test/suite/backup/t/backup_mysql_upgrade.test
        Bug#39378 - Backup: backup logs with default character set latin1 are not upgraded correctly
        Beautified test result.
        Added test.
     @ scripts/mysql_system_tables_fix.sql
        Bug#39378 - Backup: backup logs with default character set latin1 are not upgraded correctly
        Added character set conversion for backup tables.

[31 Mar 2010 15:08] Chuck Bell

Approved.

[1 Apr 2010 20:16] Anurag Shekhar

Approved

[12 Apr 2010 9:00] Ingo Strüwing

Approved by both reviewers.

[23 Apr 2010 14:55] Ingo Strüwing

Queued to mysql-next-mr-backup.

[3 Sep 2010 8:42] Ingo Strüwing

The feature will not be implemented.