| 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; |