summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/mysqldump_restore.test
blob: 8d40a4f35f2614291a46de407070a39366d3ab6d (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
###############################################################################
# mysqldump_restore.test
#
# Purpose:  Tests if mysqldump output can be used to successfully restore
#           tables and data.  
#           We CREATE a table, mysqldump it to a file, ALTER the original
#           table's name, recreate the table from the mysqldump file, then
#           utilize include/diff_tables to compare the original and recreated
#           tables.
#
#           We use several examples from mysqldump.test here and include
#           the relevant bug numbers and headers from that test.
#
# NOTE:     This test is not currently complete and offers only basic
#           cases of mysqldump output being restored. 
#           Also, does NOT work with -X (xml) output!
#           
# Author:   pcrews
# Created:  2009-05-21
# Last Change:
# Change date:
###############################################################################

# Embedded server doesn't support external clients
--source include/not_embedded.inc
--source include/have_log_bin.inc

--echo # Set concurrent_insert = 0 to prevent random errors
--echo # will reset to original value at the end of the test
SET @old_concurrent_insert = @@global.concurrent_insert;
SET @@global.concurrent_insert = 0;

# Define mysqldumpfile here.  It is used to capture mysqldump output
# in order to test the output's ability to restore an exact copy of the table
let $mysqldumpfile = $MYSQLTEST_VARDIR/tmp/mysqldumpfile.sql;

--echo # Pre-test cleanup
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

--echo # Begin tests
--echo #
--echo # Bug#2005 Long decimal comparison bug.
--echo #
CREATE TABLE t1 (a DECIMAL(64, 20));
INSERT INTO t1 VALUES ("1234567890123456789012345678901234567890"),
("0987654321098765432109876543210987654321");
--exec $MYSQL_DUMP --compact test t1 > $mysqldumpfile
let $table_name = test.t1;
--source include/mysqldump.inc

--echo #
--echo # Bug#3361 mysqldump quotes DECIMAL values inconsistently
--echo #
CREATE TABLE t1 (a DECIMAL(10,5), b FLOAT);
# Check at first how mysql work with quoted decimal
INSERT INTO t1 VALUES (1.2345, 2.3456);
INSERT INTO t1 VALUES ('1.2345', 2.3456);
INSERT INTO t1 VALUES ("1.2345", 2.3456);
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI_QUOTES';
INSERT INTO t1 VALUES (1.2345, 2.3456);
INSERT INTO t1 VALUES ('1.2345', 2.3456);
--error ER_BAD_FIELD_ERROR
INSERT INTO t1 VALUES ("1.2345", 2.3456);
SET SQL_MODE=@OLD_SQL_MODE;

# check how mysqldump make quoting
--exec $MYSQL_DUMP --compact test t1 > $mysqldumpfile
let $table_name = test.t1;
--source include/mysqldump.inc

--echo #
--echo # Bug#1994 mysqldump does not correctly dump UCS2 data
--echo # Bug#4261 mysqldump 10.7 (mysql 4.1.2) --skip-extended-insert drops NULL from inserts
--echo #
CREATE TABLE t1 (a  VARCHAR(255)) DEFAULT CHARSET koi8r;
INSERT INTO t1  VALUES (_koi8r x'C1C2C3C4C5'), (NULL);
--exec $MYSQL_DUMP --skip-comments --skip-extended-insert test t1 > $mysqldumpfile
let $table_name = test.t1;
--source include/mysqldump.inc

--echo #
--echo # WL#2319 Exclude Tables from dump
--echo #
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (4),(5),(6);
--exec $MYSQL_DUMP --skip-comments --ignore-table=test.t1 test > $mysqldumpfile
let $table_name = test.t2;
--source include/mysqldump.inc
DROP TABLE t1;

--echo #
--echo # Bug#8830 mysqldump --skip-extended-insert causes --hex-blob to dump wrong values
--echo #
CREATE TABLE t1 (`b` blob);
INSERT INTO `t1` VALUES (0x602010000280100005E71A);
--exec $MYSQL_DUMP --skip-extended-insert --hex-blob test --skip-comments t1 > $mysqldumpfile
let $table_name = test.t1;
--source include/mysqldump.inc

--echo #
--echo # Start of 10.3 tests
--echo #

--echo #
--echo #  MDEV-17363 Compressed columns cannot be restored from dump
--echo #

CREATE TABLE t1 (a VARCHAR(1000) COMPRESSED CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL);
INSERT INTO `t1` VALUES (REPEAT('a', 256));
--exec $MYSQL_DUMP --skip-extended-insert test --skip-comments t1 > $mysqldumpfile
let $table_name = test.t1;
--source include/mysqldump.inc

CREATE TABLE t1 (a LONGTEXT COMPRESSED CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL);
INSERT INTO `t1` VALUES (REPEAT('a', 256));
--exec $MYSQL_DUMP --skip-extended-insert test --skip-comments t1 > $mysqldumpfile
let $table_name = test.t1;
--source include/mysqldump.inc

--echo #
--echo # End of 10.3 tests
--echo #

--echo # Cleanup
--echo # Reset concurrent_insert to its original value
SET @@global.concurrent_insert = @old_concurrent_insert;
--echo # remove mysqldumpfile
--error 0,1
--remove_file $mysqldumpfile