Bug #56316 mysql.backup_history and progress logs have default charset as latin1 instead of

• Submitted: 26 Aug 2010 23:53
• Modified: 6 Sep 2010 12:15
• Reporter: Hema Sridharan
• Status: Closed
• Category: MySQL Enterprise Backup
• Severity: S3 (Non-critical)
• Version: MEB3.5
• OS: Linux
• Assigned to: Satya B
• CPU Architecture: Any

[26 Aug 2010 23:53] Hema Sridharan

Description:
Generally all the mysql tables have character set of utf8 but backup_history and progress logs does not follow this. They have default charset as latin1.

How to repeat:
Please apply the latest patch for generating innobackup logs that can be obtained from [e-mail] email list.

One patch is applied, execute innobackup and check the following:

SHOW TABLES FROM mysql;
Tables_in_mysql
backup_history
backup_progress
columns_priv
db
event
func
general_log
..
...

SELECT column_name, character_set_name
FROM information_schema.columns
WHERE table_name LIKE 'tables_priv'
  AND character_set_name LIKE 'utf8'
ORDER BY column_name;
column_name     character_set_name
Column_priv     utf8
Db      utf8
Grantor utf8
Host    utf8
Table_name      utf8
Table_priv      utf8
User    utf8

SELECT column_name, character_set_name
FROM information_schema.columns
WHERE table_name LIKE 'backup_history'
ORDER BY column_name;
column_name     character_set_name
backup_destination      latin1
backup_format   latin1
backup_id       NULL
backup_type     latin1
binlog_file     latin1
binlog_pos      NULL
compression_level       NULL
end_lsn NULL
end_time        NULL
engines latin1
exit_state      latin1
incremental_base_lsn    NULL
innodb_data_file_path   latin1
innodb_data_home_dir    latin1
innodb_file_format      latin1
innodb_log_files_in_group       latin1
innodb_log_file_size    latin1
innodb_log_group_home_dir       latin1
last_error      latin1
last_error_code NULL
lock_time       NULL
mysql_data_dir  latin1
start_lsn       NULL
start_time      NULL
tool_name       latin1

From the above table we can see that backup_history has all columns with latin 1 charset. Whereas this is not the case with other mysql tables. It will be ideal to create(and maintain) tables in mysql database with uniformity(utf8 charset)

Suggested fix:
Change the character set from latin1 to utf8

[31 Aug 2010 9:10] Satya B

I have fixed this for  the history, progress tables using the utf8 charset. will push this fix to meb-3.5.next

output after fixing the charset

meb.integrity_innobackup                 [ fail ]
        Test ended at 2010-08-31 14:37:47

CURRENT_TEST: meb.integrity_innobackup
--- /home/satya/WORK/mysql-5.1-meb-wl-5319/mysql-test/suite/meb/r/integrity_innobackup.result	2010-06-10 18:54:33.140568000 +0300
+++ /home/satya/WORK/mysql-5.1-meb-wl-5319/mysql-test/suite/meb/r/integrity_innobackup.reject	2010-08-31 12:07:46.711735014 +0300
@@ -1506,6 +1506,33 @@
 ...
 # Success
 
+SELECT column_name, character_set_name FROM information_schema.columns WHERE table_name LIKE 'backup_history' ORDER BY column_name;
+column_name	character_set_name
+backup_destination	utf8
+backup_format	utf8
+backup_id	NULL
+backup_type	utf8
+binlog_file	utf8
+binlog_pos	NULL
+compression_level	NULL
+end_lsn	NULL
+end_time	NULL
+engines	utf8
+exit_state	utf8
+incremental_base_lsn	NULL
+innodb_data_file_path	utf8
+innodb_data_home_dir	utf8
+innodb_file_format	utf8
+innodb_log_files_in_group	utf8
+innodb_log_file_size	utf8
+innodb_log_group_home_dir	utf8
+last_error	utf8
+last_error_code	NULL
+lock_time	NULL
+mysql_data_dir	utf8
+start_lsn	NULL
+start_time	NULL
+tool_name	utf8
 #
 # Stop server.

[6 Sep 2010 12:15] Satya B

Fixed in meb-3.5-next/meb-innobackup-c