Debian Codegraph @

mysql-test/suite/versioning/r/data.result

raw
1 #
2 # MDEV-16355 Add option for mysqldump to read data as of specific timestamp from system-versioned tables
3 #
4 create or replace table t1 (x int) with system versioning;
5 set timestamp=unix_timestamp('1990-01-01 00:00');
6 insert t1 (x) values (1),(2),(3);
7 set timestamp=unix_timestamp('1990-08-03 00:00');
8 delete from t1 where x=1;
9 set timestamp=unix_timestamp('1991-01-02 00:00');
10 delete from t1 where x=2;
11 set timestamp=default;
12 #MYSQL_DUMP --compact test
13 /*M!999999\- enable the sandbox mode */
14 /*!40101 SET @saved_cs_client = @@character_set_client */;
15 /*!40101 SET character_set_client = utf8mb4 */;
16 CREATE TABLE `t1` (
17 `x` int(11) DEFAULT NULL
18 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING;
19 /*!40101 SET character_set_client = @saved_cs_client */;
20 INSERT INTO `t1` VALUES
21 (3);
22 #MYSQL_DUMP --compact --as-of="1990-01-02 00:00" test
23 /*M!999999\- enable the sandbox mode */
24 /*!40101 SET @saved_cs_client = @@character_set_client */;
25 /*!40101 SET character_set_client = utf8mb4 */;
26 CREATE TABLE `t1` (
27 `x` int(11) DEFAULT NULL
28 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING;
29 /*!40101 SET character_set_client = @saved_cs_client */;
30 INSERT INTO `t1` VALUES
31 (1),
32 (2),
33 (3);
34 #MYSQL_DUMP --compact --as-of="1990-08-02 00:00" --databases test
35 /*M!999999\- enable the sandbox mode */
36
37 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci */;
38
39 USE `test`;
40 /*!40101 SET @saved_cs_client = @@character_set_client */;
41 /*!40101 SET character_set_client = utf8mb4 */;
42 CREATE TABLE `t1` (
43 `x` int(11) DEFAULT NULL
44 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING;
45 /*!40101 SET character_set_client = @saved_cs_client */;
46 INSERT INTO `t1` VALUES
47 (1),
48 (2),
49 (3);
50 #MYSQL_DUMP --compact --as-of="1990-08-04 00:00" test t1
51 /*M!999999\- enable the sandbox mode */
52 /*!40101 SET @saved_cs_client = @@character_set_client */;
53 /*!40101 SET character_set_client = utf8mb4 */;
54 CREATE TABLE `t1` (
55 `x` int(11) DEFAULT NULL
56 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING;
57 /*!40101 SET character_set_client = @saved_cs_client */;
58 INSERT INTO `t1` VALUES
59 (2),
60 (3);
61 #MYSQL_DUMP --compact --as-of="1990-08-04 00:00' where 'abc" test 2>&1
62 mariadb-dump: Incorrect DATETIME value: '1990-08-04 00:00' where 'abc'
63 drop tables t1;
64 #
65 # MDEV-16029 mysqldump: dump and restore historical data
66 #
67 create or replace table t1 (x int) with system versioning;
68 set timestamp=unix_timestamp('2010-10-10 10:10:10.101010');
69 insert into t1 values (1), (2);
70 set timestamp=unix_timestamp('2011-11-11 11:11:11.111111');
71 delete from t1 where x = 1;
72 set timestamp=default;
73 select row_start, row_end into @s1, @e1 from t1 for system_time all where x = 1;
74 select row_start, row_end into @s2, @e2 from t1 for system_time all where x = 2;
75 create or replace table t2 (
76 x int,
77 row_start timestamp(6) as row start invisible,
78 row_end timestamp(6) as row end invisible,
79 period for system_time (row_start, row_end))
80 with system versioning;
81 create or replace function check_fields(x int, row_start timestamp(6), row_end timestamp(6))
82 returns char(50) deterministic
83 return if (x = 1,
84 if (row_start = @s1 and row_end = @e1, '[CORRECT]', '[WRONG]'),
85 if (x = 2 and row_start = @s2 and row_end = @e2, '[CORRECT]', '[WRONG]'));
86 set @@system_versioning_insert_history= 1;
87 insert into t2 (x, row_start, row_end) select x, row_start, row_end from t1 for system_time all;
88 set @@system_versioning_insert_history= 0;
89 # t2 has the same data as t1
90 select x, check_fields(x, row_start, row_end) from t2 for system_time all order by x;
91 x check_fields(x, row_start, row_end)
92 1 [CORRECT]
93 2 [CORRECT]
94 /*M!999999\- enable the sandbox mode */
95 /*!40101 SET @saved_cs_client = @@character_set_client */;
96 /*!40101 SET character_set_client = utf8mb4 */;
97 CREATE TABLE `t1` (
98 `x` int(11) DEFAULT NULL
99 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING;
100 /*!40101 SET character_set_client = @saved_cs_client */;
101 /*M!101100 SET @old_system_versioning_insert_history=@@session.system_versioning_insert_history, @@session.system_versioning_insert_history=1 */;
102 INSERT INTO `t1` (`x`, row_start, row_end) VALUES (1,'2010-10-10 10:10:10.101010','2011-11-11 11:11:11.111111'),
103 (2,'2010-10-10 10:10:10.101010','MAX_TIME');
104 /*M!101100 SET system_versioning_insert_history=@old_system_versioning_insert_history */;
105 /*!40101 SET @saved_cs_client = @@character_set_client */;
106 /*!40101 SET character_set_client = utf8mb4 */;
107 CREATE TABLE `t2` (
108 `x` int(11) DEFAULT NULL,
109 `row_start` timestamp(6) GENERATED ALWAYS AS ROW START INVISIBLE,
110 `row_end` timestamp(6) GENERATED ALWAYS AS ROW END INVISIBLE,
111 PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
112 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING;
113 /*!40101 SET character_set_client = @saved_cs_client */;
114 /*M!101100 SET @old_system_versioning_insert_history=@@session.system_versioning_insert_history, @@session.system_versioning_insert_history=1 */;
115 INSERT INTO `t2` (`x`, `row_start`, `row_end`) VALUES (1,'2010-10-10 10:10:10.101010','2011-11-11 11:11:11.111111'),
116 (2,'2010-10-10 10:10:10.101010','MAX_TIME');
117 /*M!101100 SET system_versioning_insert_history=@old_system_versioning_insert_history */;
118 mariadb-dump: --dump-history can't be used with --as-of.
119 mariadb-dump: --dump-history can't be used with --replace.
120 mariadb-dump: --xml can't be used with --dump-history.
121 # SQL dump with/without history
122 ## With history
123 drop tables t1, t2;
124 select x, check_fields(x, row_start, row_end) from t1 for system_time all order by x;
125 x check_fields(x, row_start, row_end)
126 1 [CORRECT]
127 2 [CORRECT]
128 select x, check_fields(x, row_start, row_end) from t2 for system_time all order by x;
129 x check_fields(x, row_start, row_end)
130 1 [CORRECT]
131 2 [CORRECT]
132 ## Without history
133 drop tables t1, t2;
134 select x, check_row_ts(row_start, row_end) from t1 for system_time all order by x;
135 x check_row_ts(row_start, row_end)
136 2 CURRENT ROW
137 select x, check_row_ts(row_start, row_end) from t2 for system_time all order by x;
138 x check_row_ts(row_start, row_end)
139 2 CURRENT ROW
140 ## History and --no-create-info --skip-comments
141 create or replace table t1 (x int) with system versioning;
142 delete from t2;
143 delete history from t2;
144 select x, check_fields(x, row_start, row_end) from t1 for system_time all order by x;
145 x check_fields(x, row_start, row_end)
146 1 [CORRECT]
147 2 [CORRECT]
148 select x, check_fields(x, row_start, row_end) from t2 for system_time all order by x;
149 x check_fields(x, row_start, row_end)
150 1 [CORRECT]
151 2 [CORRECT]
152 ## compact
153 select x, check_fields(x, row_start, row_end) from t1 for system_time all order by x;
154 x check_fields(x, row_start, row_end)
155 1 [CORRECT]
156 2 [CORRECT]
157 select x, check_fields(x, row_start, row_end) from t2 for system_time all order by x;
158 x check_fields(x, row_start, row_end)
159 1 [CORRECT]
160 2 [CORRECT]
161 create or replace table t1 (x int) with system versioning;
162 # --tab with history
163 drop tables t1, t2;
164 show create table t1;
165 Table Create Table
166 t1 CREATE TABLE `t1` (
167 `x` int(11) DEFAULT NULL
168 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
169 show create table t2;
170 Table Create Table
171 t2 CREATE TABLE `t2` (
172 `x` int(11) DEFAULT NULL,
173 `row_start` timestamp(6) GENERATED ALWAYS AS ROW START INVISIBLE,
174 `row_end` timestamp(6) GENERATED ALWAYS AS ROW END INVISIBLE,
175 PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
176 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
177 set @@system_versioning_insert_history= 1;
178 load data infile 'tmp/t1.txt' into table t1 (x, row_start, row_end);
179 load data infile 'tmp/t2.txt' into table t2 (x, row_start, row_end);
180 set @@system_versioning_insert_history= 0;
181 select *, check_row_ts(row_start, row_end) from t1 for system_time all;
182 x check_row_ts(row_start, row_end)
183 1 HISTORICAL ROW
184 2 CURRENT ROW
185 select *, check_row_ts(row_start, row_end) from t2 for system_time all;
186 x check_row_ts(row_start, row_end)
187 1 HISTORICAL ROW
188 2 CURRENT ROW
189 drop tables t1, t2;
190 drop function check_fields;
191 #
192 # MDEV-29730 mysqldump --dump-history creates broken dump if there are precision-versioned tables
193 #
194 create table t1 (x int,
195 rs BIGINT unsigned as row start, re BiGiNt unsigned as row end,
196 period for system_time (rs,re)) with system versioning engine=innodb;
197 insert t1 (x) values (1);
198 insert t1 (x) values (2);
199 delete from t1 where x=1;
200 mariadb-dump: Cannot use --dump-history for table `t1` with transaction-precise history
201 mariadb-dump: Cannot use --dump-history for table `t1` with transaction-precise history
202 /*M!999999\- enable the sandbox mode */
203 /*!40101 SET @saved_cs_client = @@character_set_client */;
204 /*!40101 SET character_set_client = utf8mb4 */;
205 CREATE TABLE `t1` (
206 `x` int(11) DEFAULT NULL,
207 `rs` bigint(20) unsigned GENERATED ALWAYS AS ROW START,
208 `re` bigint(20) unsigned GENERATED ALWAYS AS ROW END,
209 PERIOD FOR SYSTEM_TIME (`rs`, `re`)
210 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING;
211 /*!40101 SET character_set_client = @saved_cs_client */;
212 INSERT INTO `t1` VALUES
213 (2,XXX,18446744073709551615);
214 drop table t1;