Re: bzr commit into mysql-6.0-backup branch (jorgen.loland:2675) Bug#33836

[Øystein Grøvlen]

I am not sure the proposed test has much value.  If this fix regresses 
is because someone edits the CREATE TABLE script and then he would 
probably just update the result file accordingly.  Isn't there a way to 
execute a backup that puts some non-latin1 characters (e.g. Chinese) 
into the progress or history table?

--
Øystein

Jorgen Loland wrote:
> #At file:///localhome/jl208045/mysql/mysql-6.0-backup-33836/
> 
>  2675 Jorgen Loland	2008-08-06
>       Bug#33836 - Backup: tables have default character set latin1
>       
>       Pre-fix: mysql.online_backup* tables have default character 
>       set latin1, while other mysql tables have utf8.
>       
>       Fix: change default character set of online_backup tables to
>       utf8.
> modified:
>   mysql-test/r/backup_progress.result
>   mysql-test/t/backup_progress.test
>   scripts/mysql_system_tables.sql
> 
> per-file comments:
>   mysql-test/r/backup_progress.result
>     Added expected table definition output for mysql.online_backup* tables
>   mysql-test/t/backup_progress.test
>     Added test that table definition for online_backup and online_backup_progress
> have not changed
>   scripts/mysql_system_tables.sql
>     Change create table script for online_backup and online_backup_progress to use
> default character set utf8
> === modified file 'mysql-test/r/backup_progress.result'
> --- a/mysql-test/r/backup_progress.result	2008-07-09 18:37:42 +0000
> +++ b/mysql-test/r/backup_progress.result	2008-08-06 13:58:21 +0000
> @@ -1,3 +1,44 @@
> +
> +Checking table definitions
> +TRUNCATE TABLE mysql.online_backup;
> +TRUNCATE TABLE mysql.online_backup_progress;
> +SHOW CREATE TABLE mysql.online_backup;
> +Table	Create Table
> +online_backup	CREATE TABLE `online_backup` (
> +  `backup_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Surrogate key for
> (near) duplicate entries',
> +  `process_id` int(10) unsigned NOT NULL COMMENT 'The process id that operation ran
> as',
> +  `binlog_pos` int(10) unsigned DEFAULT '0' COMMENT 'The recorded binlog position of
> backup/restore',
> +  `binlog_file` char(64) DEFAULT NULL COMMENT 'The recorded binlog filename at time
> of backup/restore',
> +  `backup_state` enum('complete','starting','validity
> point','running','error','cancel') NOT NULL COMMENT 'Status of current operation',
> +  `operation` enum('backup','restore') NOT NULL COMMENT 'Type of operation',
> +  `error_num` int(11) NOT NULL DEFAULT '0' COMMENT 'The error from this run 0 ==
> none',
> +  `num_objects` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The number of objects
> in the backup',
> +  `total_bytes` bigint(20) unsigned DEFAULT NULL COMMENT 'The size of the backup
> repository in bytes',
> +  `validity_point_time` datetime DEFAULT NULL COMMENT 'The time of the validity
> point.',
> +  `start_time` datetime DEFAULT NULL COMMENT 'The date/time of start of operation',
> +  `stop_time` datetime DEFAULT NULL COMMENT 'The date/time of end of operation',
> +  `host_or_server_name` char(30) DEFAULT NULL COMMENT 'The server name where
> operation ran',
> +  `username` char(30) DEFAULT NULL COMMENT 'The user name who ran the operation',
> +  `backup_file` char(100) DEFAULT NULL COMMENT 'The name of the file',
> +  `user_comment` varchar(200) DEFAULT NULL COMMENT 'The comment from user entered at
> command line',
> +  `command` varchar(512) DEFAULT NULL COMMENT 'The command used to issue
> operation',
> +  `drivers` varchar(100) DEFAULT NULL COMMENT 'The name of the storage engine backup
> drivers used in the operation',
> +  PRIMARY KEY (`backup_id`)
> +) ENGINE=MyISAM DEFAULT CHARSET=utf8
> +SHOW CREATE TABLE mysql.online_backup_progress;
> +Table	Create Table
> +online_backup_progress	CREATE TABLE `online_backup_progress` (
> +  `backup_id` bigint(20) unsigned NOT NULL COMMENT 'Key for online_backup table
> entries',
> +  `object` char(30) NOT NULL COMMENT 'The object being operated on',
> +  `start_time` datetime DEFAULT NULL COMMENT 'The date/time of start of operation',
> +  `stop_time` datetime DEFAULT NULL COMMENT 'The date/time of end of operation',
> +  `total_bytes` bigint(20) DEFAULT NULL COMMENT 'The size of the object in bytes',
> +  `progress` bigint(20) unsigned DEFAULT NULL COMMENT 'The number of bytes
> processed',
> +  `error_num` int(11) NOT NULL DEFAULT '0' COMMENT 'The error from this run 0 ==
> none',
> +  `notes` char(100) DEFAULT NULL COMMENT 'Commentary from the backup engine'
> +) ENGINE=MyISAM DEFAULT CHARSET=utf8
> +Now starting real tests
> +
>  SET DEBUG_SYNC= 'reset';
>  DROP DATABASE IF EXISTS backup_progress;
>  DROP TABLE IF EXISTS backup_progress.t1_res;
> 
> === modified file 'mysql-test/t/backup_progress.test'
> --- a/mysql-test/t/backup_progress.test	2008-06-25 13:39:04 +0000
> +++ b/mysql-test/t/backup_progress.test	2008-08-06 13:58:21 +0000
> @@ -8,6 +8,22 @@
>  --source include/have_innodb.inc
>  --source include/not_embedded.inc
>  
> +# First check that mysql.online_backup and
> +# mysql.online_backup_progress definitions have not changed
> +
> +--echo 
> +--echo Checking table definitions
> +
> +# truncate to remove any auto_increment info from the table definitions
> +TRUNCATE TABLE mysql.online_backup;
> +TRUNCATE TABLE mysql.online_backup_progress;
> +
> +SHOW CREATE TABLE mysql.online_backup;
> +SHOW CREATE TABLE mysql.online_backup_progress;
> +
> +--echo Now starting real tests
> +--echo 
> +
>  #
>  # Preparative cleanup.
>  #
> 
> === modified file 'scripts/mysql_system_tables.sql'
> --- a/scripts/mysql_system_tables.sql	2008-07-09 18:37:42 +0000
> +++ b/scripts/mysql_system_tables.sql	2008-08-06 13:58:21 +0000
> @@ -83,10 +83,10 @@ DROP PREPARE stmt;
>  CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin
> NOT NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob
> NOT NULL, definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
> execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field
> ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND')
> default NULL, created TIMESTAMP NOT NULL, modified TIMESTAMP NOT NULL, last_executed
> DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status
> ENUM('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL default 'ENABLED', on_completion
> ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode 
> set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE',
'N
> 
> OT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')
> DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default
> '', originator int(10) NOT NULL, time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT
> 'SYSTEM', character_set_client char(32) collate utf8_bin, collation_connection char(32)
> collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 longblob, PRIMARY KEY
> (db, name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events';
>  
>  
> -CREATE TABLE IF NOT EXISTS online_backup ( backup_id BIGINT UNSIGNED PRIMARY KEY
> AUTO_INCREMENT COMMENT 'Surrogate key for (near) duplicate entries', process_id INT
> UNSIGNED NOT NULL COMMENT 'The process id that operation ran as', binlog_pos INT UNSIGNED
> DEFAULT 0 COMMENT 'The recorded binlog position of backup/restore', binlog_file CHAR(64)
> COMMENT 'The recorded binlog filename at time of backup/restore', backup_state
> ENUM('complete', 'starting', 'validity point', 'running', 'error', 'cancel') NOT NULL
> COMMENT 'Status of current operation', operation ENUM('backup', 'restore') NOT NULL
> COMMENT 'Type of operation', error_num INT NOT NULL DEFAULT 0 COMMENT 'The error from this
> run 0 == none', num_objects INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'The number of objects
> in the backup', total_bytes BIGINT UNSIGNED COMMENT 'The size of the backup repository in
> bytes', validity_point_time datetime COMMENT 'The time of the validity point.', start_time
> datetime COMMENT 'The date/time
 o
>  f start of operation', stop_time datetime COMMENT 'The date/time of end of
> operation', host_or_server_name CHAR (30) COMMENT 'The server name where operation ran',
> username CHAR (30) COMMENT 'The user name who ran the operation', backup_file CHAR (100)
> COMMENT 'The name of the file', user_comment VARCHAR (200) DEFAULT NULL COMMENT 'The
> comment from user entered at command line', command VARCHAR (512) COMMENT 'The command
> used to issue operation', drivers VARCHAR (100) COMMENT 'The name of the storage engine
> backup drivers used in the operation') ENGINE=MYISAM;
> +CREATE TABLE IF NOT EXISTS online_backup ( backup_id BIGINT UNSIGNED PRIMARY KEY
> AUTO_INCREMENT COMMENT 'Surrogate key for (near) duplicate entries', process_id INT
> UNSIGNED NOT NULL COMMENT 'The process id that operation ran as', binlog_pos INT UNSIGNED
> DEFAULT 0 COMMENT 'The recorded binlog position of backup/restore', binlog_file CHAR(64)
> COMMENT 'The recorded binlog filename at time of backup/restore', backup_state
> ENUM('complete', 'starting', 'validity point', 'running', 'error', 'cancel') NOT NULL
> COMMENT 'Status of current operation', operation ENUM('backup', 'restore') NOT NULL
> COMMENT 'Type of operation', error_num INT NOT NULL DEFAULT 0 COMMENT 'The error from this
> run 0 == none', num_objects INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'The number of objects
> in the backup', total_bytes BIGINT UNSIGNED COMMENT 'The size of the backup repository in
> bytes', validity_point_time datetime COMMENT 'The time of the validity point.', start_time
> datetime COMMENT 'The date/time
 o
>  f start of operation', stop_time datetime COMMENT 'The date/time of end of
> operation', host_or_server_name CHAR (30) COMMENT 'The server name where operation ran',
> username CHAR (30) COMMENT 'The user name who ran the operation', backup_file CHAR (100)
> COMMENT 'The name of the file', user_comment VARCHAR (200) DEFAULT NULL COMMENT 'The
> comment from user entered at command line', command VARCHAR (512) COMMENT 'The command
> used to issue operation', drivers VARCHAR (100) COMMENT 'The name of the storage engine
> backup drivers used in the operation') ENGINE=MYISAM DEFAULT CHARACTER SET utf8;
>  
>  
> -CREATE TABLE IF NOT EXISTS online_backup_progress ( backup_id BIGINT UNSIGNED NOT
> NULL COMMENT 'Key for online_backup table entries', object CHAR (30) NOT NULL COMMENT 'The
> object being operated on', start_time datetime COMMENT 'The date/time of start of
> operation', stop_time datetime COMMENT 'The date/time of end of operation', total_bytes
> BIGINT COMMENT 'The size of the object in bytes', progress BIGINT UNSIGNED COMMENT 'The
> number of bytes processed', error_num INT NOT NULL DEFAULT 0 COMMENT 'The error from this
> run 0 == none', notes CHAR(100) COMMENT 'Commentary from the backup engine')
> ENGINE=MYISAM;
> +CREATE TABLE IF NOT EXISTS online_backup_progress ( backup_id BIGINT UNSIGNED NOT
> NULL COMMENT 'Key for online_backup table entries', object CHAR (30) NOT NULL COMMENT 'The
> object being operated on', start_time datetime COMMENT 'The date/time of start of
> operation', stop_time datetime COMMENT 'The date/time of end of operation', total_bytes
> BIGINT COMMENT 'The size of the object in bytes', progress BIGINT UNSIGNED COMMENT 'The
> number of bytes processed', error_num INT NOT NULL DEFAULT 0 COMMENT 'The error from this
> run 0 == none', notes CHAR(100) COMMENT 'Commentary from the backup engine') ENGINE=MYISAM
> DEFAULT CHARACTER SET utf8;
>  
>  
>  CREATE TABLE IF NOT EXISTS ndb_binlog_index (Position BIGINT UNSIGNED NOT NULL, File
> VARCHAR(255) NOT NULL, epoch BIGINT UNSIGNED NOT NULL, inserts INT UNSIGNED NOT NULL,
> updates INT UNSIGNED NOT NULL, deletes INT UNSIGNED NOT NULL, schemaops INT UNSIGNED NOT
> NULL, orig_server_id INT UNSIGNED NOT NULL, orig_epoch BIGINT UNSIGNED NOT NULL, gci INT
> UNSIGNED NOT NULL, PRIMARY KEY(epoch, orig_server_id, orig_epoch)) ENGINE=MYISAM;
> 
> 


-- 
Øystein Grøvlen, Senior Staff Engineer
Sun Microsystems, Database Group
Trondheim, Norway