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