#At file:///export/home/tmp/rpl/wl4227/mysql-6.0-backup/
2703 Hema Sridharan 2008-10-09
WL#4227: Changes made according to review comments of Chuck.
Currently 2 tests are included backup_datatypes and backup_partitions
backup_partitions is kept in backup_engines suite and will be executed
against all storage engines. backup_datatypes test is in backup suite.
There are seperate test cases for special characters and accented characters for this WL#
Currently these tests are failing because of Bug#38363, so attached them in them in the bug report.
added:
mysql-test/suite/backup/r/backup_datatypes.result
mysql-test/suite/backup/t/backup_datatypes.test
mysql-test/suite/backup_engines/r/backup_partitions.result
mysql-test/suite/backup_engines/t/backup_partitions.test
=== added file 'mysql-test/suite/backup/r/backup_datatypes.result'
--- a/mysql-test/suite/backup/r/backup_datatypes.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/r/backup_datatypes.result 2008-10-09 19:32:16 +0000
@@ -0,0 +1,221 @@
+
+starting the test for backup
+
+DROP DATABASE IF EXISTS `¥ü`;
+SET NAMES latin1;
+CREATE DATABASE `¥ü`;
+USE `¥ü`;
+Create table with all datatypes and load with data.
+CREATE TABLE `§Æ`(
+rint INT,
+tint TINYINT,
+sint SMALLINT,
+bint BIGINT,
+mint MEDIUMINT,
+name CHAR(100),
+city VARCHAR(100),
+fl FLOAT(7,4),
+pers DECIMAL(8,2),
+sal DOUBLE,
+colours SET('red','blue','yellow'),
+continent ENUM('Asia', 'Europe','Africa','Antartica'),
+ts TIMESTAMP DEFAULT 0,
+dt DATETIME NOT NULL,
+dob DATE,
+time TIME,
+y YEAR
+);
+creating table with blob and text columns
+CREATE TABLE `§Æ2`(
+region TEXT,
+summary LONGTEXT,
+data BLOB,
+details MEDIUMBLOB,
+queries TINYTEXT,
+query2 TINYBLOB,
+extract LONGBLOB,
+paras MEDIUMTEXT
+);
+CREATE TABLE `§¶œ`(b1 BINARY(3), b2 VARBINARY(2),bitvalue BIT(64));
+INSERT INTO `§¶œ` VALUES(0x61,0x2130,b'1111111111111111111111111111111111111111111111111111111111111111'), (0x6120,0x4100,b'101010101010101'), (0x612020, 0x4120,b'000000001');
+SELECT HEX(b1), HEX(b2), HEX(bitvalue) FROM `§¶œ`;
+HEX(b1) HEX(b2) HEX(bitvalue)
+610000 2130 FFFFFFFFFFFFFFFF
+612000 4100 5555
+612020 4120 1
+INSERT INTO `§Æ` VALUES
+(785,127,7288,278829899,3777,'testing1','sweden','678.299',200.23,829899.909,
+'red','Asia','2008-06-01 16:23:30','98/12/31 11*30*45','1984-09-08','7:05','1984');
+INSERT INTO `§Æ2` VALUES
+('xxxxxxxx','Testofonline backup','aaaaaaaaaa','bbbbbbbbbbb','hhhhhhhhhhh',
+'kkkkkkkkkkkkk','mmmmmmmmmmmm','onlinebackup1');
+UPDATE `§Æ2` SET extract=repeat('z',100);
+SELECT * FROM `§Æ`;;
+rint 785
+tint 127
+sint 7288
+bint 278829899
+mint 3777
+name testing1
+city sweden
+fl 678.2990
+pers 200.23
+sal 829899.909
+colours red
+continent Asia
+ts 2008-06-01 16:23:30
+dt 1998-12-31 11:30:45
+dob 1984-09-08
+time 07:05:00
+y 1984
+SELECT * FROM `§Æ2`;
+region summary data details queries query2 extract paras
+xxxxxxxx Testofonline backup aaaaaaaaaa bbbbbbbbbbb hhhhhhhhhhh kkkkkkkkkkkkk zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz onlinebackup1
+DESCRIBE `§Æ`;
+Field Type Null Key Default Extra
+rint int(11) YES NULL
+tint tinyint(4) YES NULL
+sint smallint(6) YES NULL
+bint bigint(20) YES NULL
+mint mediumint(9) YES NULL
+name char(100) YES NULL
+city varchar(100) YES NULL
+fl float(7,4) YES NULL
+pers decimal(8,2) YES NULL
+sal double YES NULL
+colours set('red','blue','yellow') YES NULL
+continent enum('Asia','Europe','Africa','Antartica') YES NULL
+ts timestamp NO 0000-00-00 00:00:00
+dt datetime NO NULL
+dob date YES NULL
+time time YES NULL
+y year(4) YES NULL
+DESCRIBE `§Æ2`;
+Field Type Null Key Default Extra
+region text YES NULL
+summary longtext YES NULL
+data blob YES NULL
+details mediumblob YES NULL
+queries tinytext YES NULL
+query2 tinyblob YES NULL
+extract longblob YES NULL
+paras mediumtext YES NULL
+DESCRIBE `§¶œ`;
+Field Type Null Key Default Extra
+b1 binary(3) YES NULL
+b2 varbinary(2) YES NULL
+bitvalue bit(64) YES NULL
+** Backup data **
+
+BACKUP DATABASE `¥ü` to 'bup_datatype.bak';
+backup_id
+#
+** dropping database**
+DROP DATABASE `¥ü`;
+**Restore**
+RESTORE FROM 'bup_datatype.bak';
+backup_id
+#
+** checking the character set **
+SELECT @@character_set_client;
+@@character_set_client
+latin1
+SELECT @@character_set_results;
+@@character_set_results
+latin1
+SELECT @@character_set_connection;
+@@character_set_connection
+latin1
+DESCRIBE `§Æ`;
+Field Type Null Key Default Extra
+rint int(11) YES NULL
+tint tinyint(4) YES NULL
+sint smallint(6) YES NULL
+bint bigint(20) YES NULL
+mint mediumint(9) YES NULL
+name char(100) YES NULL
+city varchar(100) YES NULL
+fl float(7,4) YES NULL
+pers decimal(8,2) YES NULL
+sal double YES NULL
+colours set('red','blue','yellow') YES NULL
+continent enum('Asia','Europe','Africa','Antartica') YES NULL
+ts timestamp NO 0000-00-00 00:00:00
+dt datetime NO NULL
+dob date YES NULL
+time time YES NULL
+y year(4) YES NULL
+DESCRIBE `§Æ2`;
+Field Type Null Key Default Extra
+region text YES NULL
+summary longtext YES NULL
+data blob YES NULL
+details mediumblob YES NULL
+queries tinytext YES NULL
+query2 tinyblob YES NULL
+extract longblob YES NULL
+paras mediumtext YES NULL
+DESCRIBE `§¶œ`;
+Field Type Null Key Default Extra
+b1 binary(3) YES NULL
+b2 varbinary(2) YES NULL
+bitvalue bit(64) YES NULL
+SELECT * FROM `§Æ`;;
+rint 785
+tint 127
+sint 7288
+bint 278829899
+mint 3777
+name testing1
+city sweden
+fl 678.2990
+pers 200.23
+sal 829899.909
+colours red
+continent Asia
+ts 2008-06-01 16:23:30
+dt 1998-12-31 11:30:45
+dob 1984-09-08
+time 07:05:00
+y 1984
+SELECT * FROM `§Æ2`;
+region summary data details queries query2 extract paras
+xxxxxxxx Testofonline backup aaaaaaaaaa bbbbbbbbbbb hhhhhhhhhhh kkkkkkkkkkkkk zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz onlinebackup1
+SELECT HEX(b1), HEX(b2),HEX(bitvalue) FROM `§¶œ`;
+HEX(b1) HEX(b2) HEX(bitvalue)
+610000 2130 FFFFFFFFFFFFFFFF
+612000 4100 5555
+612020 4120 1
+INSERT INTO `§¶œ` VALUES(0x7120,0x41,b'1010101010101010101010101010101010101010101010101010101010101010'), (0x5122, 0x6120,b'1000000000000000000000000000000000000000000000000000000000000000');
+SELECT HEX(b1), HEX(b2), HEX(bitvalue) FROM `§¶œ`;
+HEX(b1) HEX(b2) HEX(bitvalue)
+610000 2130 FFFFFFFFFFFFFFFF
+612000 4100 5555
+612020 4120 1
+712000 41 AAAAAAAAAAAAAAAA
+512200 6120 8000000000000000
+Perform restore again by changing the character set
+SET NAMES latin5;
+**Restore**
+RESTORE FROM 'bup_datatype.bak';
+backup_id
+#
+SHOW TABLES FROM `¥ü`;
+Tables_in_¥ü
+§¶�
+§�
+§�2
+** checking client character set **
+SELECT @@character_set_client;
+@@character_set_client
+latin5
+SELECT @@character_set_results;
+@@character_set_results
+latin5
+SELECT @@character_set_connection;
+@@character_set_connection
+latin5
+
+*** DROP `¥ü` DATABASE ****
+
+DROP DATABASE `¥ü`;
=== added file 'mysql-test/suite/backup/t/backup_datatypes.test'
--- a/mysql-test/suite/backup/t/backup_datatypes.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_datatypes.test 2008-10-09 19:32:16 +0000
@@ -0,0 +1,143 @@
+####################################################################
+# Author: Hema
+# Date: 2008-03-11
+# Purpose: To test the backup/restore of all datatypes for different storage
+# engines using accented letters.
+# We will ensure that backup stores identifiers properly in utf8 format and
+# is retreived during restore without changing the client character set.
+#########################################################################
+
+--source include/not_embedded.inc
+--source include/have_debug.inc
+
+connect (backup,localhost,root,,);
+connect (breakpoints,localhost,root,,);
+
+##############################################################
+--echo
+--echo starting the test for backup
+--echo
+##############################################################
+
+--error 0,1
+--remove_file $MYSQLTEST_VARDIR/master-data/bup_datatype.bak
+
+#Create Database and tables with different datatypes for this test.
+
+--disable_warnings
+DROP DATABASE IF EXISTS `¥ü`;
+--enable_warnings
+
+SET NAMES latin1;
+CREATE DATABASE `¥ü`;
+USE `¥ü`;
+
+--echo Create table with all datatypes and load with data.
+
+CREATE TABLE `§Æ`(
+rint INT,
+tint TINYINT,
+sint SMALLINT,
+bint BIGINT,
+mint MEDIUMINT,
+name CHAR(100),
+city VARCHAR(100),
+fl FLOAT(7,4),
+pers DECIMAL(8,2),
+sal DOUBLE,
+colours SET('red','blue','yellow'),
+continent ENUM('Asia', 'Europe','Africa','Antartica'),
+ts TIMESTAMP DEFAULT 0,
+dt DATETIME NOT NULL,
+dob DATE,
+time TIME,
+y YEAR
+);
+
+--echo creating table with blob and text columns
+
+CREATE TABLE `§Æ2`(
+region TEXT,
+summary LONGTEXT,
+data BLOB,
+details MEDIUMBLOB,
+queries TINYTEXT,
+query2 TINYBLOB,
+extract LONGBLOB,
+paras MEDIUMTEXT
+);
+
+CREATE TABLE `§¶œ`(b1 BINARY(3), b2 VARBINARY(2),bitvalue BIT(64));
+INSERT INTO `§¶œ` VALUES(0x61,0x2130,b'1111111111111111111111111111111111111111111111111111111111111111'), (0x6120,0x4100,b'101010101010101'), (0x612020, 0x4120,b'000000001');
+SELECT HEX(b1), HEX(b2), HEX(bitvalue) FROM `§¶œ`;
+
+INSERT INTO `§Æ` VALUES
+(785,127,7288,278829899,3777,'testing1','sweden','678.299',200.23,829899.909,
+'red','Asia','2008-06-01 16:23:30','98/12/31 11*30*45','1984-09-08','7:05','1984');
+
+INSERT INTO `§Æ2` VALUES
+('xxxxxxxx','Testofonline backup','aaaaaaaaaa','bbbbbbbbbbb','hhhhhhhhhhh',
+'kkkkkkkkkkkkk','mmmmmmmmmmmm','onlinebackup1');
+
+# Bug #37212 Restore crashes if table has longblob of size 1MB
+UPDATE `§Æ2` SET extract=repeat('z',100);
+--query_vertical SELECT * FROM `§Æ`;
+SELECT * FROM `§Æ2`;
+
+DESCRIBE `§Æ`;
+DESCRIBE `§Æ2`;
+DESCRIBE `§¶œ`;
+
+--echo ** Backup data **
+--echo
+--replace_column 1 #
+BACKUP DATABASE `¥ü` to 'bup_datatype.bak';
+
+--echo ** dropping database**
+DROP DATABASE `¥ü`;
+
+--echo **Restore**
+--replace_column 1 #
+RESTORE FROM 'bup_datatype.bak';
+
+--echo ** checking the character set **
+SELECT @@character_set_client;
+SELECT @@character_set_results;
+SELECT @@character_set_connection;
+
+#show data and table columns
+DESCRIBE `§Æ`;
+DESCRIBE `§Æ2`;
+DESCRIBE `§¶œ`;
+
+--query_vertical SELECT * FROM `§Æ`;
+SELECT * FROM `§Æ2`;
+SELECT HEX(b1), HEX(b2),HEX(bitvalue) FROM `§¶œ`;
+
+INSERT INTO `§¶œ` VALUES(0x7120,0x41,b'1010101010101010101010101010101010101010101010101010101010101010'), (0x5122, 0x6120,b'1000000000000000000000000000000000000000000000000000000000000000');
+SELECT HEX(b1), HEX(b2), HEX(bitvalue) FROM `§¶œ`;
+
+--echo Perform restore again by changing the character set
+SET NAMES latin5;
+
+--echo **Restore**
+--replace_column 1 #
+RESTORE FROM 'bup_datatype.bak';
+
+#show data and table columns
+SHOW TABLES FROM `¥ü`;
+
+--echo ** checking client character set **
+SELECT @@character_set_client;
+SELECT @@character_set_results;
+SELECT @@character_set_connection;
+
+# Test cleanup section
+--echo
+--echo *** DROP `¥ü` DATABASE ****
+--echo
+
+DROP DATABASE `¥ü`;
+
+--remove_file $MYSQLTEST_VARDIR/master-data/bup_datatype.bak
+
=== added file 'mysql-test/suite/backup_engines/r/backup_partitions.result'
--- a/mysql-test/suite/backup_engines/r/backup_partitions.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup_engines/r/backup_partitions.result 2008-10-09 19:32:16 +0000
@@ -0,0 +1,518 @@
+SHOW VARIABLES LIKE 'storage_engine';
+Variable_name Value
+storage_engine #
+
+starting the test for backup
+
+DROP DATABASE IF EXISTS `Ïœ±Ïœ±åå`;
+*** The following defects were reported while executing WL#4227 ***
+Bug #34391 Character sets: crash if char(), utf32, innodb
+Bug #33566 Backup: crash with partitions and Falcon
+Bug #37551 Junk detected in data contents sometimes when utf8mb3
+character set is used.
+Bug #37554 Use of character set and collate as 'filename' shows
+unusual behaviour.
+Bug #35499 View when created with swe7 character set fails.
+Bug #38784 Mysql server crash if table is altered by partition changes.
+SET NAMES latin5;
+CREATE DATABASE `Ïœ±Ïœ±åå`;
+USE `Ïœ±Ïœ±åå`;
+*****Creating table with range partition*********
+CREATE TABLE `¥€`(
+ID INT NOT NULL,
+NAME CHAR(20),
+AGE TINYINT,
+DOB DATE,
+SAL FLOAT,
+PRIMARY KEY(DOB)) PARTITION BY RANGE(Year(DOB))(
+PARTITION P0 values less than (1970),
+PARTITION P1 values less than (1980),
+PARTITION P2 values less than (1990),
+PARTITION P3 values less than (2000),
+PARTITION P4 values less than maxvalue);
+loading data
+INSERT INTO `¥€` VALUES
+(10,'aa','20','1988-12-19',2345.89),(11,'bb',31,'1977-10-19',6009.89),
+(12,'cc',78,'1945-09-12',3897),(13,'dd',12,'1994-09-18',7892.78),
+(14,'ee',45,'1960-12-23',7845.90),(15,'ff',25,'1984-02-07',3500.89);
+select data
+SELECT * FROM `¥€` ORDER BY DOB;
+ID NAME AGE DOB SAL
+12 cc 78 1945-09-12 3897
+14 ee 45 1960-12-23 7845.9
+11 bb 31 1977-10-19 6009.89
+15 ff 25 1984-02-07 3500.89
+10 aa 20 1988-12-19 2345.89
+13 dd 12 1994-09-18 7892.78
+SELECT COUNT(*) FROM `¥€`;
+COUNT(*)
+6
+
+******Creating table partition with List**************
+CREATE TABLE `ʯᴓ`(`numeric` INT, `char` CHAR(20))
+PARTITION BY LIST (`numeric`)
+(
+PARTITION p0 VALUES IN (1,3,5),
+PARTITION p1 VALUES IN (2,4,6)
+);
+insert data
+INSERT INTO `ʯᴓ` VALUES(1,'a'),(6,'c'),(5,'j'),(2,'l');
+selecting data
+SELECT * FROM `ʯᴓ` ORDER BY `numeric`;
+numeric char
+1 a
+2 l
+5 j
+6 c
+SELECT COUNT(*) FROM `ʯᴓ`;
+COUNT(*)
+4
+
+*******Creating table partition with Hash***********
+CREATE TABLE `µ±HASH (YEAR(col3))
+PARTITIONS 4;
+Insert data
+INSERT INTO `µ±¬` VALUES
+(1,'a','1942-10-09'),(2,'b','1950-08-07'),
+(3,'c','1952-10-10'),(4,'d','1954-10-04');
+selecting data
+SELECT * FROM `µ±¬` ORDER BY col3;
+col1 col2 col3
+1 a 1942-10-09
+2 b 1950-08-07
+3 c 1952-10-10
+4 d 1954-10-04
+SELECT COUNT(*) FROM `µ±¬`;
+COUNT(*)
+4
+********Creating table partition with keys*******
+CREATE TABLE `®§` (id INT)
+PARTITION BY KEY(id)
+PARTITIONS 2;
+insert data
+INSERT INTO `®§` VALUES(1),(2),(3),(4);
+selecting the data
+SELECT * FROM `®§`;
+id
+1
+3
+2
+4
+SELECT COUNT(*) FROM `®§`;
+COUNT(*)
+4
+***Creating views from partitoned Tables****
+CREATE VIEW `ïö` AS SELECT * FROM `¥€`;
+Obtaining information about Partitions
+SHOW FULL TABLES;
+Tables_in_Ïœ±Ïœ±åå Table_type
+¥€ BASE TABLE
+®§ BASE TABLE
+µ±¬ BASE TABLE
+ïö VIEW
+ʯᴓ BASE TABLE
+EXPLAIN PARTITIONS SELECT * FROM `¥€`;;
+id #
+select_type SIMPLE
+table ¥€
+partitions P0,P1,P2,P3,P4
+type ALL
+possible_keys NULL
+key NULL
+key_len NULL
+ref NULL
+rows #
+Extra
+EXPLAIN PARTITIONS SELECT * FROM `ʯᴓ`;;
+id #
+select_type SIMPLE
+table ʯᴓ
+partitions p0,p1
+type ALL
+possible_keys NULL
+key NULL
+key_len NULL
+ref NULL
+rows #
+Extra
+EXPLAIN PARTITIONS SELECT * FROM `µ±¬`;;
+id #
+select_type SIMPLE
+table µ±¬
+partitions p0,p1,p2,p3
+type ALL
+possible_keys NULL
+key NULL
+key_len NULL
+ref NULL
+rows #
+Extra
+EXPLAIN PARTITIONS SELECT * FROM `®§`;;
+id #
+select_type SIMPLE
+table ®§
+partitions p0,p1
+type ALL
+possible_keys NULL
+key NULL
+key_len NULL
+ref NULL
+rows #
+Extra
+backup data
+BACKUP DATABASE `Ïœ±Ïœ±åå` TO 'partitions.bak';
+backup_id
+#
+dropping database.
+DROP DATABASE `Ïœ±Ïœ±å#
+
+** RESULTS AFTER RESTORE **
+
+Obtaining Information about Partitions
+SHOW FULL TABLES;;
+Tables_in_Ïœ±Ïœ±åå ¥€
+Table_type BASE TABLE
+Tables_in_Ïœ±Ïœ±åå ®§
+Table_type BASE TABLE
+Tables_in_Ïœ±Ïœ±åå ïö
+Table_type VIEW
+Tables_in_Ïœ±Ïœ±å * FROM `¥€`;;
+id #
+select_type SIMPLE
+table ¥€
+partitions P0,P1,P2,P3,P4
+type ALL
+possible_keys NULL
+key NULL
+key_len NULL
+ref NULL
+rows #
+Extra
+EXPLAIN PARTITIONS SELECT * FROM `ʯᴓ`;;
+id #
+select_type SIMPLE
+table ʯᴓ
+partitions p0,p1
+type ALL
+possible_keys NULL
+key NULL
+key_len NULL
+ref NULL
+rows #
+Extra
+EXPLAIN PARTITIONS SELECT * FROM `µ±¬`;;
+id #
+select_type SIMPLE
+table µ±¬
+partitions p0,p1,p2,p3
+type ALL
+possible_keys NULL
+key NULL
+key_len NULL
+ref NULL
+rows #
+Extra
+EXPLAIN PARTITIONS SELECT * FROM `p0,p1
+type ALL
+possible_keys NULL
+key NULL
+key_len NULL
+ref NULL
+rows #
+Extra
+ALTER TABLE `¥€` PARTITION BY KEY(DOB) PARTITIONS 2;
+INSERT INTO `ïö` VALUES(17,'kk','90','2020-12-19',2000);
+SELECT COUNT(*) FROM `ïö`;
+COUNT(*)
+7
+SELECT COUNT(*) FROM `¥€`;
+COUNT(*)
+7
+
+**Creating table with subpartitions**
+CREATE TABLE `Ææ` (`int` INT)
+PARTITION BY range (`int`)
+SUBPARTITION BY key (`int`)
+(PARTITION p0 VALUES LESS THAN (2));
+INSERT INTO `Ææ` VALUES(0),(1);
+SHOW FULL TABLES;
+Tables_in_Ïœ±Ïœ±åå Table_type
+¥€ BASE TABLE
+®§ BASE TABLE
+µ±¬ BASE TABLE
+Ææ BASE TABLE
+ïM `¥€`;;
+id #
+select_type SIMPLE
+table ¥€
+partitions p0,p1
+type ALL
+possible_keys NULL
+key NULL
+key_len NULL
+ref NULL
+rows #
+Extra
+EXPLAIN PARTITIONS SELECT * FROM `ʯᴓ`;;
+id #
+select_type SIMPLE
+table ʯᴓ
+partitions p0,p1
+type ALL
+possible_keys NULL
+key NULL
+key_len NULL
+ref NULL
+rows #
+Extra
+EXPLAIN PARTITIONS SELECT * FROM `µ±¬`;;
+id #
+select_type SIMPLE
+table µ±¬
+partitions p0,p1,p2,p3
+type ALL
+possible_keys NULL
+key NULL
+key_len NULL
+ref NULL
+rows #
+Extra
+EXPLAIN PARTITIONS SELECT * FROM `®§`;;
+id #
+select_type SIMPLE
+table ®§
+partitions p0,p1
+type ALL
+possible_keys NULL
+key NULL
+key_len NULL
+ref NULL
+rows #
+Extra
+BACKUP DATABASE `Ïœ±Ïœ±åå` TO 'partitions.bak';
+backup_id
+#
+DROP DATABASE `Ïœ±Ïœ±ing objects and create statements
+EXPLAIN PARTITIONS SELECT * FROM `¥€`;;
+id #
+select_type SIMPLE
+table ¥ULL
+key_len NULL
+ref NULL
+rows #
+Extra
+EXPLAIN PARTITIONS SELECT * FROM `ʯᴓ`;;
+id #
+select_type SIMPLE
+table ʯᴓ
+partitions p0,p1
+type ALL
+possible_keys NULL
+key NULL
+key_len NULL
+ref NULL
+rows #
+Extra
+EXPLAIN PARTITIONS SELECT * FROM `µ±¬`;;
+id #
+select_type SIMPLE
+table µ±¬
+partitions p0,p1,p2,p3
+type ALL
+possible_keys NULL
+key NULL
+key_len NULL
+ref NULL
+rows #
+Extra
+EXPLAIN PARTITIONS SELECT * FROM `®§`;;
+id #
+select_type SIMPLE
+table ®§
+partitions p0,p1
+type ALL
+possible_keys NULL
+key NULL
+key_len NULL
+ref NULL
+rows #
+Extra
+SELECT * FROM `ʯᴓ` ORDER BY `numeric`;
+numeric char
+1 a
+2 l
+5 j
+6 c
+SELECT * FROM `®§`;
+id
+1
+3
+2
+4
+SELECT * FROM `µ±¬` ORDER BY col3;
+col1 col2 col3
+1 a 1942-10-09
+2 b 1950-08-07
+3 c 1952-10-10
+4 d 1954-10-04
+SELECT * FROM `¥€` ORDER BY DOB;
+ID NAME AGE DOB SAL
+12 cc 78 1945-09-12 3897
+14 ee 45 1960-12-23 7845.9
+11 bb 31 1977-10-19 6009.89
+15 ff 25 1984-02-07 3500.89
+10 aa 20 1988-12-19 2345.89
+13 dd 12 1994-09-18 7892.78
+17 kk 90 2020-12-19 2000
+SELECT * FROM `Ææ`;
+int
+0
+1
+**creating view from view***
+CREATE VIEW v2 AS SELECT * FROM `ïö`;
+INSERT INTO v2 VALUES(18,'pp',51,'1990-10-19',6009.89);
+SELECT COUNT(*) FROM `ïö`;
+COUNT(*)
+8
+SELECT COUNT(*) FROM `estore.
+EXPLAIN PARTITIONS SELECT * FROM `Ææ`;;
+id #
+select_type SIMPLE
+table Ææ
+partitions p0_p0sp0
+type ALL
+possible_keys NULL
+key NULL
+key_len NULL
+ref NULL
+rows #
+Extra
+SHOW FULL TABLES;
+Tables_in_Ïœ±Ïœ±åå Table_type
+¥€ BASE TABLE
+®§ BASE TABLE
+TABLE
+v2 VIEW
+ALTER TABLE `Ææ` REMOVE PARTITIONING;
+BACKUP DATABASE `Ïœ±Ïœ±åå` to 'partitions.bak';
+backup_id
+#
+DROP DATABASE `Ïœ±Ïœ±åå`;
+RESTORE FROM 'partitions.bak';
+backup_id
+#
+SHOW FULL TABLES;
+Tables_in_Ïœ±Ïœ±åå Table_type
+¥€ BASE TABLE
+®§ BASE TABLE
+µ±¬ BASE TABLE
+Ææ BASE TABLE
+ïö VIEW
+ʯᴓ BASE TABLE
+v2 VIEW
+SELECT * FROM `Ææ`;
+int
+0
+1
+EXPLAIN PARTITIONS SELECT * FROM `Ææ`;;
+id #
+select_type SIMPLE
+table Ææ
+partitions NULL
+type ALL
+possible_keys NULL
+key NULL
+key_len NULL
+ref NULL
+rows #
+Extra
+SELECT * FROM `ïö` ORDER BY DOB;
+ID NAME AGE DOB SAL
+12 cc 78 1945-09-12 3897
+14 ee 45 1960-12-23 7845.9
+11 bb 31 1977-10-19 6009.89
+15 ff 25 1984-02-07 3500.89
+10 aa 20 1988-12-19 2345.89
+18 pp 51 1990-10-19 6009.89
+13 dd 12 1994-09-18 7892.78
+17 kk 90 2020-12-19 2000
+SELECT COUNT(*) FROM `¥€`;
+COUNT(*)
+8
+Perform restore again by changing the character set
+SET NAMES latin7;
+**Restore**
+RESTORE FROM 'partitions.bak';
+backup_id
+#
+SHOW DATABASES;
+Database
+information_schema
+?�œ?�?�œ?�????
+mysql
+test
+SHOW FULL TABLES;
+Tables_in_?�œ?�?�œ?�???? Table_type
+???�� BASE TABLE
+?�?� BASE TABLE
+?�?�?� BASE TABLE
+?�?� BASE TABLE
+???� VIEW
+????� BASE TABLE
+v2 VIEW
+** checking client character set **
+SELECT @@character_set_client;
+@@character_set_client
+latin7
+SELECT @@character_set_results;
+@@character_set_results
+latin7
+SELECT @@character_set_connection;
+@@character_set_connection
+latin7
+SET NAMES latin5;
+RESTORE FROM 'partitions.bak';
+backup_id
+#
+SHOW DATABASES;
+Database
+information_schema
+Ïœ±Ïœ±åå
+mysql
+test
+
+*** DROP `Ïœ±Ïœ±åå` DATABASE ****
+
+DROP DATABASE `Ïœ±Ïœ±åå`;
=== added file 'mysql-test/suite/backup_engines/t/backup_partitions.test'
--- a/mysql-test/suite/backup_engines/t/backup_partitions.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup_engines/t/backup_partitions.test 2008-10-09 19:32:16 +0000
@@ -0,0 +1,314 @@
+###########################################################################
+# Author: Hema
+# Date: 2008-06-28
+# Purpose: To test the backup and Restore of different partitions using
+# accented letters. We will ensure that backup stores identifiers properly in
+# utf8 format and is retreived during restore without changing the client
+# character set.
+###############################################################################
+--source include/not_embedded.inc
+--source include/have_debug.inc
+--source suite/backup_engines/include/backup_engine.inc
+
+connect (backup,localhost,root,,);
+connect (breakpoints,localhost,root,,);
+
+##############################################################
+--echo
+--echo starting the test for backup
+--echo
+##############################################################
+
+--error 0,1
+--remove_file $MYSQLTEST_VARDIR/master-data/partitions.bak
+
+#Create Database and and tables with different types of partitions.
+
+--disable_warnings
+DROP DATABASE IF EXISTS `Ïœ±Ïœ±åå`;
+--enable_warnings
+
+--echo *** The following defects were reported while executing WL#4227 ***
+
+--echo Bug #34391 Character sets: crash if char(), utf32, innodb
+--echo Bug #33566 Backup: crash with partitions and Falcon
+--echo Bug #37551 Junk detected in data contents sometimes when utf8mb3
+--echo character set is used.
+--echo Bug #37554 Use of character set and collate as 'filename' shows
+--echo unusual behaviour.
+--echo Bug #35499 View when created with swe7 character set fails.
+--echo Bug #38784 Mysql server crash if table is altered by partition changes.
+
+SET NAMES latin5;
+
+CREATE DATABASE `Ïœ±Ïœ±åå`;
+USE `Ïœ±Ïœ±åå`;
+
+#Create table and load with data.
+
+--echo *****Creating table with range partition*********
+
+CREATE TABLE `¥€`(
+ ID INT NOT NULL,
+NAME CHAR(20),
+AGE TINYINT,
+DOB DATE,
+SAL FLOAT,
+PRIMARY KEY(DOB)) PARTITION BY RANGE(Year(DOB))(
+ PARTITION P0 values less than (1970),
+ PARTITION P1 values less than (1980),
+ PARTITION P2 values less than (1990),
+ PARTITION P3 values less than (2000),
+ PARTITION P4 values less than maxvalue);
+
+--echo loading data
+
+INSERT INTO `¥€` VALUES
+(10,'aa','20','1988-12-19',2345.89),(11,'bb',31,'1977-10-19',6009.89),
+(12,'cc',78,'1945-09-12',3897),(13,'dd',12,'1994-09-18',7892.78),
+(14,'ee',45,'1960-12-23',7845.90),(15,'ff',25,'1984-02-07',3500.89);
+
+--echo select data
+
+SELECT * FROM `¥€` ORDER BY DOB;
+SELECT COUNT(*) FROM `¥€`;
+
+--echo
+--echo ******Creating table partition with List**************
+
+CREATE TABLE `ʯᴓ`(`numeric` INT, `char` CHAR(20))
+PARTITION BY LIST (`numeric`)
+(
+ PARTITION p0 VALUES IN (1,3,5),
+ PARTITION p1 VALUES IN (2,4,6)
+);
+--echo insert data
+
+INSERT INTO `ʯᴓ` VALUES(1,'a'),(6,'c'),(5,'j'),(2,'l');
+
+--echo selecting data
+
+SELECT * FROM `ʯᴓ` ORDER BY `numeric`;
+SELECT COUNT(*) FROM `ʯth Hash***********
+
+CREATE TABLE `µ±¬`(
+col1 INT,
+col2 CHAR(20),
+col3 DATE)
+PARTITION BY HASH (YEAR(col3))
+PARTITIONS 4;
+
+--echo Insert data
+
+INSERT INTO `µ±¬` VALUES
+(1,'a','1942-10-09'),(2,'b','1950-08-07'),
+(3,'c','1952-10-10'),(4,'d','1954-10-04');
+
+--echo selecting data
+
+SELECT * FROM `µ±¬` ORDER BY col3;
+SELECT COUNT(*) FROM `µ±¬`;
+
+--echo ********Creating table partition with keys*******
+
+CREATE TABLE `®§` (id INT)
+PARTITION BY KEY(id)
+PARTITIONS 2;
+
+--echo insert data
+
+INSERT INTO `®§` VALUES(1),(2),(3),(4);
+
+--echo selecting the data
+
+SELECT * FROM `®§`;
+SELECT COUNT(*) FROM `®§`;
+
+--echo ***Creating views from partitoned Tables****
+
+CREATE VIEW `ïö` AS SELECT * FROM `¥€`;
+
+# We mask some of the columns in Explain Partitions command because of
+# Bug #37532 Explain command shows incorrect rows,
+# when table is partitioned and innodb.
+
+--echo Obtaining information about Partitions
+SHOW FULL TABLES;
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `¥€`;
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `ʯᴓ`;
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `µ±¬`;
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `®§`;
+
+--echo backup data
+--replace_column 1 #
+BACKUP DATABASE `Ïœ±Ïœ±åå` TO 'partitions.bak';
+
+--echo dropping database.
+
+DROP DATABASE `Ïœ±Ïœ±åå`;
+
+--echo Restore
+--replace_column 1 #
+RESTORE FROM 'partitions.bak';
+--remove_file $MYSQLTEST_VARDIR/master-data/partitions.bak
+
+--echo
+--echo ** RESULTS AFTER RESTORE **
+--echo
+
+#show data and create statements
+--echo Obtaining Information about Partitions
+--query_vertical SHOW FULL TABLES;
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `¥€`;
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `ʯᴓ`;
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `µ±¬`;
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `®§`;
+
+# Make some changes to existing partitions, take backup and then perform
+# Restore
+
+ALTER TABLE `¥€` PARTITION BY KEY(DOB) PARTITIONS 2;
+
+#All the maintenence operations of partitions like ANALYZE, OPTIMIZE
+#REPAIR are currently disabled in 6.0 and 5.0.
+#Bug#20129
+#We can include these operations in the test case once this bug is fixed.
+
+#Bug#38784 Mysql server crash if table is altered with partition changes.
+#This happens only in Windows OS. The # can be removed once the bug#38784
+#is fixed.
+
+#ALTER TABLE `ʯᴓ` ADD PARTITION (PARTITION p2 VALUES IN (10,20,30));
+#INSERT INTO `ʯᴓ` VALUES(10,'h'),(3,'b'),(10,'s'),(5,'l');
+#SELECT * FROM `ʯᴓ`;
+#SELECT COUNT(*) FROM `ʯᴓ`;
+
+INSERT INTO `ïö` VALUES(17,'kk','90','2020-12-19',2000);
+SELECT COUNT(*) FROM `ïö`;
+SELECT COUNT(*) FROM `¥€`;
+--echo
+--echo **Creating table with subpartitions**
+
+CREATE TABLE `Ææ` (`int` INT)
+PARTITION BY range (`int`)
+SUBPARTITION BY key (`int`)
+(PARTITION p0 VALUES LESS THAN (2));
+
+INSERT INTO `Ææ` VALUES(0),(1);
+
+#show data and create statements
+SHOW FULL TABLES;
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `¥€`;
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `ʯᴓ`;
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `µ±¬`;
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `®§`;
+
+--replace_column 1 #
+BACKUP DATABASE `Ïœ±Ïœ±åå` TO 'partitions.bak';
+DROP DATABASE `Ïœ±Ïœ±åå`;
+--replace_column 1 #
+RESTORE FROM 'partitions.bak';
+--remove_file $MYSQLTEST_VARDIR/master-data/partitions.bak
+
+#show data and create statements
+--echo showing objects and create statements
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `¥€`;
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `ʯᴓ`;
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `µ±¬`;
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `®§`;
+
+SELECT * FROM `ʯᴓ` ORDER BY `numeric`;
+SELECT * FROM `®§`;
+SELECT * FROM `µ±¬` ORDER BY col3;
+SELECT * FROM `¥€` ORDER BY DOB;
+SELECT * FROM `Ææ`;
+
+--echo **creating view from view***
+CREATE VIEW v2 AS SELECT * FROM `ïö`;
+INSERT INTO v2 VALUES(18,'pp',51,'1990-10-19',6009.89);
+
+SELECT COUNT(*) FROM `ïö`;
+SELECT COUNT(*) FROM `¥€`;
+
+--echo Make some changes and then perform Restore.
+
+#Bug#38784 Mysql server crash if table is altered with partition changes.
+#This happens only in Windows OS. The # can be removed once the bug#38784
+#is fixed.
+
+#ALTER TABLE `Ææ` ADD PARTITION (PARTITION p1 VALUES LESS THAN (4));
+#INSERT INTO `Ææ` VALUES(2),(3),(1);
+
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `Ææ`;
+SHOW FULL TABLES;
+ALTER TABLE `Ææ` REMOVE PARTITIONING;
+
+--replace_column 1 #
+BACKUP DATABASE `Ïœ±Ïœ±åå` to 'partitions.bak';
+
+DROP DATABASE `Ïœ±Ïœ±åå`;
+--replace_column 1 #
+RESTORE FROM 'partitions.bak';
+
+SHOW FULL TABLES;
+SELECT * FROM `Ææ`;
+--replace_column 1 # 10 #
+--query_vertical EXPLAIN PARTITIONS SELECT * FROM `Ææ`;
+SELECT * FROM `ïö` ORDER BY DOB;
+SELECT COUNT(*) FROM `¥€`;
+
+--echo Perform restore again by changing the character set
+
+SET NAMES latin7;
+
+--echo **Restore**
+--replace_column 1 #
+RESTORE FROM 'partitions.bak';
+
+#show data and table columns
+
+SHOW DATABASES;
+SHOW FULL TABLES;
+
+--echo ** checking client character set **
+#check if restore does not change the character set.
+SELECT @@character_set_client;
+SELECT @@character_set_results;
+SELECT @@character_set_connection;
+
+#Change the character set and restore again
+SET NAMES latin5;
+
+--replace_column 1 #
+RESTORE FROM 'partitions.bak';
+SHOW DATABASES;
+
+# Test cleanup section
+
+--echo
+--echo *** DROP `Ïœ±Ïœ±åå` DATABASE ****
+--echo
+DROP DATABASE `Ïœ±Ïœ±åå`;
+
+--remove_file $MYSQLTEST_VARDIR/master-data/partitions.bak
+
