Debian Codegraph @

mysql-test/suite/versioning/t/data.test

raw
1 --source include/not_embedded.inc
2 --source suite/versioning/common.inc
3
4 --echo #
5 --echo # MDEV-16355 Add option for mysqldump to read data as of specific timestamp from system-versioned tables
6 --echo #
7 create or replace table t1 (x int) with system versioning;
8 set timestamp=unix_timestamp('1990-01-01 00:00');
9 insert t1 (x) values (1),(2),(3);
10 set timestamp=unix_timestamp('1990-08-03 00:00');
11 delete from t1 where x=1;
12 set timestamp=unix_timestamp('1991-01-02 00:00');
13 delete from t1 where x=2;
14 set timestamp=default;
15
16 --echo #MYSQL_DUMP --compact test
17 --exec $MYSQL_DUMP --no-autocommit=0 --compact test
18 --echo #MYSQL_DUMP --compact --as-of="1990-01-02 00:00" test
19 --exec $MYSQL_DUMP --no-autocommit=0 --compact --as-of="1990-01-02 00:00" test
20 --echo #MYSQL_DUMP --compact --as-of="1990-08-02 00:00" --databases test
21 --exec $MYSQL_DUMP --no-autocommit=0 --compact --as-of="1990-08-02 00:00" --databases test
22 --echo #MYSQL_DUMP --compact --as-of="1990-08-04 00:00" test t1
23 --exec $MYSQL_DUMP --no-autocommit=0 --compact --as-of="1990-08-04 00:00" test t1
24 ## Forged query protection
25 --echo #MYSQL_DUMP --compact --as-of="1990-08-04 00:00' where 'abc" test 2>&1
26 --replace_result mariadb-dump.exe mariadb-dump
27 --error 1
28 --exec $MYSQL_DUMP --no-autocommit=0 --compact --as-of="1990-08-04 00:00' where 'abc" test 2>&1
29
30 drop tables t1;
31
32 --echo #
33 --echo # MDEV-16029 mysqldump: dump and restore historical data
34 --echo #
35 create or replace table t1 (x int) with system versioning;
36 set timestamp=unix_timestamp('2010-10-10 10:10:10.101010');
37 insert into t1 values (1), (2);
38 set timestamp=unix_timestamp('2011-11-11 11:11:11.111111');
39 delete from t1 where x = 1;
40 set timestamp=default;
41 --disable_cursor_protocol
42 select row_start, row_end into @s1, @e1 from t1 for system_time all where x = 1;
43 select row_start, row_end into @s2, @e2 from t1 for system_time all where x = 2;
44 --enable_cursor_protocol
45
46 create or replace table t2 (
47 x int,
48 row_start timestamp(6) as row start invisible,
49 row_end timestamp(6) as row end invisible,
50 period for system_time (row_start, row_end))
51 with system versioning;
52
53 eval
54 create or replace function check_fields(x int, row_start timestamp(6), row_end timestamp(6))
55 returns char(50) deterministic
56 return if (x = 1,
57 if (row_start = @s1 and row_end = @e1, '[CORRECT]', '[WRONG]'),
58 if (x = 2 and row_start = @s2 and row_end = @e2, '[CORRECT]', '[WRONG]'));
59
60 set @@system_versioning_insert_history= 1;
61 insert into t2 (x, row_start, row_end) select x, row_start, row_end from t1 for system_time all;
62 set @@system_versioning_insert_history= 0;
63 --echo # t2 has the same data as t1
64 select x, check_fields(x, row_start, row_end) from t2 for system_time all order by x;
65
66 --let TMP= $MYSQLTEST_VARDIR/tmp
67
68 --exec $MYSQL_DUMP --no-autocommit=0 --dump-history --databases test > $TMP/dump_history.sql
69 --exec $MYSQL_DUMP --no-autocommit=0 --databases test > $TMP/dump_no_history.sql
70 --exec $MYSQL_DUMP --no-autocommit=0 --dump-history --no-create-info --skip-comments --databases test > $TMP/dump_only_data.sql
71 --exec $MYSQL_DUMP --no-autocommit=0 --dump-history --compact test 2>&1 > $TMP/dump_history_compact.sql
72 --replace_result $sys_time_max_replace MAX_TIME
73 --cat_file $TMP/dump_history_compact.sql
74 --replace_result mariadb-dump.exe mariadb-dump
75 --error 1
76 --exec $MYSQL_DUMP --no-autocommit=0 --dump-history --as-of="1990-01-02 00:00" test 2>&1
77 --replace_result mariadb-dump.exe mariadb-dump
78 --error 1
79 --exec $MYSQL_DUMP --no-autocommit=0 --dump-history --replace test 2>&1
80 --replace_result mariadb-dump.exe mariadb-dump
81 --error 1
82 --exec $MYSQL_DUMP --dump-history --xml test 2>&1
83
84 --exec $MYSQL_DUMP --no-autocommit=0 --dump-history --tab=$TMP test
85
86 --echo # SQL dump with/without history
87 --echo ## With history
88 drop tables t1, t2;
89 --exec $MYSQL test < $TMP/dump_history.sql
90 select x, check_fields(x, row_start, row_end) from t1 for system_time all order by x;
91 select x, check_fields(x, row_start, row_end) from t2 for system_time all order by x;
92 --echo ## Without history
93 drop tables t1, t2;
94 --exec $MYSQL test < $TMP/dump_no_history.sql
95 select x, check_row_ts(row_start, row_end) from t1 for system_time all order by x;
96 select x, check_row_ts(row_start, row_end) from t2 for system_time all order by x;
97
98 --echo ## History and --no-create-info --skip-comments
99 create or replace table t1 (x int) with system versioning;
100 delete from t2; delete history from t2;
101 --exec $MYSQL test < $TMP/dump_only_data.sql
102 select x, check_fields(x, row_start, row_end) from t1 for system_time all order by x;
103 select x, check_fields(x, row_start, row_end) from t2 for system_time all order by x;
104
105 --echo ## compact
106 --exec $MYSQL test < $TMP/dump_history.sql
107 select x, check_fields(x, row_start, row_end) from t1 for system_time all order by x;
108 select x, check_fields(x, row_start, row_end) from t2 for system_time all order by x;
109
110 create or replace table t1 (x int) with system versioning;
111
112 # TODO: MDEV-16766 mysqldump: dump history in XML
113 if (0)
114 {
115 --echo # XML with history
116 drop table t1;
117 create or replace table t1 (x int) with system versioning;
118 delete from t2;
119 delete history from t2;
120 set @@system_versioning_insert_history= 1;
121 --replace_result $TMP TMP
122 eval load xml infile '$TMP/dump_history.xml' into table t1;
123 --exec cp $TMP/dump_history.xml /tmp
124 set @@system_versioning_insert_history= 0;
125 --echo ## History is now loaded as current data (TODO)
126 select *, check_row_ts(row_start, row_end) from t1 for system_time all;
127 # TODO: check mysqlimport
128 # --exec $MYSQL_IMPORT test $TMP/dump_history.xml
129 }
130
131 --echo # --tab with history
132 drop tables t1, t2;
133 --exec $MYSQL test < $TMP/t1.sql
134 --exec $MYSQL test < $TMP/t2.sql
135 show create table t1;
136 show create table t2;
137 set @@system_versioning_insert_history= 1;
138 --replace_result $TMP tmp
139 eval load data infile '$TMP/t1.txt' into table t1 (x, row_start, row_end);
140 --replace_result $TMP tmp
141 eval load data infile '$TMP/t2.txt' into table t2 (x, row_start, row_end);
142 set @@system_versioning_insert_history= 0;
143 select *, check_row_ts(row_start, row_end) from t1 for system_time all;
144 select *, check_row_ts(row_start, row_end) from t2 for system_time all;
145
146 # Cleanup
147 --remove_files_wildcard $TMP *.sql
148 --remove_files_wildcard $TMP *.txt
149 --remove_files_wildcard $TMP *.xml
150 drop tables t1, t2;
151 drop function check_fields;
152
153 --echo #
154 --echo # MDEV-29730 mysqldump --dump-history creates broken dump if there are precision-versioned tables
155 --echo #
156 create table t1 (x int,
157 rs BIGINT unsigned as row start, re BiGiNt unsigned as row end,
158 period for system_time (rs,re)) with system versioning engine=innodb;
159 insert t1 (x) values (1);
160 insert t1 (x) values (2);
161 delete from t1 where x=1;
162
163 --replace_result mariadb-dump.exe mariadb-dump
164 --error 6
165 --exec $MYSQL_DUMP --no-autocommit=0 --dump-history test 2>&1 >/dev/null
166 --replace_regex /2,\d+,/2,XXX,/ /mariadb-dump\.exe/mariadb-dump/
167 --error 6
168 --exec $MYSQL_DUMP --no-autocommit=0 --force --dump-history --compact test 2>&1
169
170 drop table t1;
171
172 --source suite/versioning/common_finish.inc