330 lines
12 KiB
Plaintext
330 lines
12 KiB
Plaintext
--source include/have_binlog_format_row.inc
|
|
--source include/master-slave.inc
|
|
|
|
|
|
# Procedure to test metadata replication error against several types of data
|
|
#
|
|
--write_file $MYSQLTEST_VARDIR/tmp/metadata_replication_error.inc PROCEDURE
|
|
if ($cast_type == '') {
|
|
--die !!!ERROR IN TEST: you must set $cast_type
|
|
}
|
|
if ($array_on == '') {
|
|
--die !!!ERROR IN TEST: you must set $array_on
|
|
}
|
|
if ($insert_value == '') {
|
|
--die !!!ERROR IN TEST: you must set $insert_value
|
|
}
|
|
|
|
if ($array_on == master) {
|
|
--let $type_on_master = $cast_type array
|
|
--let $type_on_slave = $cast_type
|
|
}
|
|
|
|
if ($array_on == slave) {
|
|
--let $type_on_master = $cast_type
|
|
--let $type_on_slave = $cast_type array
|
|
}
|
|
|
|
--source include/rpl_connection_master.inc
|
|
set @@session.sql_log_bin = 0;
|
|
--eval create table t1(j json, key mvi((cast(j as $type_on_master))))
|
|
set @@session.sql_log_bin = 1;
|
|
|
|
--source include/rpl_connection_slave.inc
|
|
--eval create table t1(j json, key mvi((cast(j as $type_on_slave))))
|
|
--source include/rpl_connection_master.inc
|
|
--eval insert into t1 values ($insert_value)
|
|
|
|
--source include/rpl_connection_slave.inc
|
|
--let $slave_sql_errno = convert_error(ER_SERVER_SLAVE_CONVERSION_FAILED)
|
|
--source include/wait_for_slave_sql_error.inc
|
|
--let $rpl_only_running_threads= 1
|
|
--source include/rpl_reset.inc
|
|
--source include/rpl_connection_master.inc
|
|
drop table t1;
|
|
# END OF
|
|
PROCEDURE
|
|
|
|
# Procedure to test metadata replication success against several types of data
|
|
#
|
|
--write_file $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc PROCEDURE
|
|
if ($cast_type == '') {
|
|
--die !!!ERROR IN TEST: you must set $cast_type
|
|
}
|
|
if ($insert_value == '') {
|
|
--die !!!ERROR IN TEST: you must set $insert_value
|
|
}
|
|
|
|
--source include/rpl_connection_master.inc
|
|
DROP TABLE t1;
|
|
eval CREATE TABLE t1 (
|
|
id INT KEY AUTO_INCREMENT,
|
|
j4 JSON ,
|
|
KEY mv_idx_binary ((( CAST(j4->'$[*]' AS $cast_type ARRAY)))));
|
|
--eval INSERT INTO t1(j4) VALUES($insert_value)
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
# END OF
|
|
PROCEDURE
|
|
|
|
SET SESSION sql_log_bin= 0;
|
|
call mtr.add_suppression('.*Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*');
|
|
--let $initial_master_rows_search = `SELECT @@global.slave_rows_search_algorithms`
|
|
SET SESSION sql_log_bin= 1;
|
|
--source include/sync_slave_sql_with_master.inc
|
|
CALL mtr.add_suppression(".*Column 1 of table .* cannot be converted from type.*");
|
|
CALL mtr.add_suppression(".*The slave coordinator and worker threads are stopped.*");
|
|
--let $initial_slave_rows_search = `SELECT @@global.slave_rows_search_algorithms`
|
|
SET @@global.slave_rows_search_algorithms="TABLE_SCAN,INDEX_SCAN";
|
|
FLUSH LOGS;
|
|
--source include/rpl_connection_master.inc
|
|
|
|
--echo #
|
|
--echo # WL#8955: Add multi-valied index support
|
|
--echo #
|
|
CREATE TABLE t1(pl INT AUTO_INCREMENT KEY, f1 JSON, KEY i1((CAST(f1->'$[*]' AS UNSIGNED ARRAY))));
|
|
insert into t1(f1) values
|
|
(cast('[1,3]' as json)), (cast(2 as json)), (cast('[3,3,4,4,7]' as json)),
|
|
(cast('[5,7]' as json)),
|
|
(cast('[8,4,3,5]' as json)), (cast('[5,6,7]' as json)),
|
|
(cast('[9,2,7]' as json)), (cast('[1,3]' as json)),
|
|
(cast('[3,3,4,4,7]' as json)), (cast(4 as json)), (cast('[8,4,3,5]' as json)),
|
|
(cast('[9,2,7]' as json)), (cast('[9,2,7]' as json)),
|
|
(cast('[1,3]' as json)),
|
|
(cast('[3,3,4,4,7]' as json)), (cast(4 as json)),
|
|
(cast(7 as json)), (cast('[8,4,3,5]' as json)), (cast('[9,2,7]' as json)),
|
|
('[98,99]');
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
analyze table t1;
|
|
select * from t1 where 5 member of (f1->"$[*]");
|
|
explain select * from t1 where 5 member of (f1->"$[*]");
|
|
|
|
select * from t1 force index(i1) where json_contains(f1->"$[*]", "[4,3]");
|
|
explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[4,3]");
|
|
select * from t1 force index(i1) where json_contains(f1->"$[*]", "[5,7]");
|
|
explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[5,7]");
|
|
--source include/rpl_connection_master.inc
|
|
select * from t1 force index(i1) where 99 member of (f1->"$[*]");
|
|
update t1 set f1=cast('[100,99]' as json) where 99 member of (f1->"$[*]");
|
|
select * from t1 force index(i1) where json_contains(f1->"$[*]", "[100]");
|
|
explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[100]");
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
select * from t1 force index(i1) where json_contains(f1->"$[*]", "[100]");
|
|
explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[100]");
|
|
--source include/rpl_connection_master.inc
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test correctness of metadata replication
|
|
|
|
--let $cast_type = char(10)
|
|
--let $array_on = slave
|
|
--let $insert_value = '"asd"'
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_error.inc
|
|
--let $array_on = master
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_error.inc
|
|
|
|
--let $cast_type = decimal(64,1)
|
|
--let $array_on = slave
|
|
--let $insert_value = '123'
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_error.inc
|
|
|
|
--let $cast_type = decimal
|
|
--let $array_on = master
|
|
--let $insert_value = '123'
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_error.inc
|
|
|
|
--let $cast_type = unsigned
|
|
--let $array_on = slave
|
|
--let $insert_value = '123'
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_error.inc
|
|
|
|
--let $cast_type = signed
|
|
--let $array_on = master
|
|
--let $insert_value = '123'
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_error.inc
|
|
|
|
--let $cast_type = datetime(6)
|
|
--let $array_on = slave
|
|
--let $insert_value = '"01-01-01 01:01:01.0"'
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_error.inc
|
|
--let $array_on = master
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_error.inc
|
|
|
|
--let $cast_type = date
|
|
--let $array_on = slave
|
|
--let $insert_value = '"01-01-01"'
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_error.inc
|
|
--let $array_on = master
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_error.inc
|
|
|
|
--let $cast_type = time(6)
|
|
--let $array_on = slave
|
|
--let $insert_value = '"01:01:01.0"'
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_error.inc
|
|
--let $array_on = master
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_error.inc
|
|
|
|
SET @start_row_image_value = @@session.binlog_row_image;
|
|
SET @@session.binlog_row_image = MINIMAL;
|
|
|
|
SET @@global.transaction_write_set_extraction = 'XXHASH64';
|
|
SET @save_btdt= @@global.binlog_transaction_dependency_tracking;
|
|
SET @@global.binlog_transaction_dependency_tracking = 'WRITESET';
|
|
|
|
# Test transactions dependency is properly calculated
|
|
FLUSH LOGS;
|
|
--source include/save_binlog_position.inc
|
|
CREATE TABLE t1 (pk INT AUTO_INCREMENT KEY, data JSON NOT NULL,
|
|
KEY zips((CAST(CAST(data AS JSON) AS UNSIGNED ARRAY))));
|
|
INSERT INTO t1(data) VALUES ('[0,1,2]'), ('[10,11,12]');
|
|
UPDATE t1 SET data = '[3,4,5]' WHERE JSON_CONTAINS(CAST(data AS JSON),'1');
|
|
INSERT INTO t1(data) VALUES ('[33,44]');
|
|
INSERT INTO t1(data) VALUES ('[0,99]');
|
|
--let $logical_timestamps = 0 1;1 2;2 3;1 4;1 5
|
|
--source include/assert_logical_timestamps.inc
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
--echo [Connection Master]
|
|
--connection master
|
|
DROP TABLE t1;
|
|
|
|
FLUSH LOGS;
|
|
--source include/save_binlog_position.inc
|
|
CREATE TABLE t1 (pk INT AUTO_INCREMENT KEY, id INT DEFAULT 0,
|
|
data JSON NOT NULL,
|
|
UNIQUE KEY zips(id, (CAST(CAST(data AS JSON) AS UNSIGNED ARRAY))));
|
|
INSERT INTO t1(data) VALUES ('[0,1,2]'), ('[10,11,12]');
|
|
UPDATE t1 SET data = '[3,4,5]' WHERE JSON_CONTAINS(CAST(data AS JSON),'1');
|
|
INSERT INTO t1(data) VALUES ('[33,44]');
|
|
INSERT INTO t1(data) VALUES ('[0,99]');
|
|
--let $logical_timestamps = 0 1;1 2;2 3;1 4;3 5
|
|
--source include/assert_logical_timestamps.inc
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
--echo [Connection Master]
|
|
--connection master
|
|
DROP TABLE t1;
|
|
|
|
FLUSH LOGS;
|
|
--source include/save_binlog_position.inc
|
|
CREATE TABLE t1 (pk INT AUTO_INCREMENT KEY, id INT DEFAULT 0,
|
|
data JSON NOT NULL,
|
|
UNIQUE KEY zips((CAST(CAST(data AS JSON) AS UNSIGNED ARRAY)), id));
|
|
INSERT INTO t1(data) VALUES ('[0,1,2]'), ('[10,11,12]');
|
|
UPDATE t1 SET data = '[3,4,5]' WHERE JSON_CONTAINS(CAST(data AS JSON),'1');
|
|
INSERT INTO t1(data) VALUES ('[33,44]');
|
|
INSERT INTO t1(data) VALUES ('[0,99]');
|
|
--let $logical_timestamps = 0 1;1 2;2 3;1 4;3 5
|
|
--source include/assert_logical_timestamps.inc
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
--echo [Connection Master]
|
|
--connection master
|
|
DROP TABLE t1;
|
|
|
|
FLUSH LOGS;
|
|
--source include/save_binlog_position.inc
|
|
CREATE TABLE t1 (pk INT AUTO_INCREMENT KEY, data JSON NOT NULL,
|
|
UNIQUE KEY zips((CAST(CAST(data AS JSON) AS UNSIGNED ARRAY))));
|
|
INSERT INTO t1(data) VALUES ('[0,1,2]'), ('[10,11,12]');
|
|
UPDATE t1 SET data = '[3,4,5]' WHERE JSON_CONTAINS(CAST(data AS JSON),'1');
|
|
INSERT INTO t1(data) VALUES ('[]');
|
|
INSERT INTO t1(data) VALUES ('[0,99]');
|
|
--let $logical_timestamps = 0 1;1 2;2 3;1 4;3 5
|
|
--source include/assert_logical_timestamps.inc
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
--echo [Connection Master]
|
|
--connection master
|
|
DROP TABLE t1;
|
|
|
|
FLUSH LOGS;
|
|
# Test that non-unique index doesn''t generate collisions
|
|
--source include/save_binlog_position.inc
|
|
CREATE TABLE t1 (pk INT AUTO_INCREMENT KEY, data JSON,
|
|
KEY zips((CAST(CAST(data AS JSON) AS UNSIGNED ARRAY))));
|
|
INSERT INTO t1(data) VALUES ('[0,1,2]'), ('[10,11,12]');
|
|
UPDATE t1 SET data = '[3,4,5]' WHERE JSON_CONTAINS(CAST(data AS JSON),'1');
|
|
INSERT INTO t1(data) VALUES ('[33,44]');
|
|
INSERT INTO t1(data) VALUES (NULL);
|
|
INSERT INTO t1(data) VALUES ('[0,99]');
|
|
--let $logical_timestamps = 0 1;1 2;2 3;1 4;1 5;1 6
|
|
--source include/assert_logical_timestamps.inc
|
|
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
--let $diff_tables= master:t1, slave:t1
|
|
--source include/diff_tables.inc
|
|
|
|
--let $cast_type = CHAR(10)
|
|
--let $insert_value = '["foobar"]'
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
|
|
|
|
--let $cast_type = BINARY(10)
|
|
--let $insert_value = '["foobar"]'
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
|
|
|
|
--let $cast_type = DECIMAL
|
|
--let $insert_value = '[123]'
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
|
|
|
|
--let $cast_type = DECIMAL(4,2)
|
|
--let $insert_value = '[12.34]'
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
|
|
|
|
--let $cast_type = UNSIGNED
|
|
--let $insert_value = '[12]'
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
|
|
|
|
--let $cast_type = SIGNED
|
|
--let $insert_value = '[-1234]'
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
|
|
|
|
--let $cast_type = DATE
|
|
--let $insert_value = '["01-01-01"]'
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
|
|
|
|
--let $cast_type = TIME(6)
|
|
--let $insert_value = '["01:01:01.123456"]'
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
|
|
|
|
--let $cast_type = DATETIME(6)
|
|
--let $insert_value = '["01-01-01 01:01:01.123456"]'
|
|
--source $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
|
|
|
|
--source include/rpl_connection_master.inc
|
|
DROP TABLE t1;
|
|
|
|
SET @@global.binlog_transaction_dependency_tracking = @save_btdt;
|
|
--replace_result $initial_master_rows_search INTITAL_ROW_SEARCH_CONF
|
|
--eval SET @@global.slave_rows_search_algorithms= "$initial_master_rows_search"
|
|
FLUSH LOGS;
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--replace_result $initial_slave_rows_search INTITAL_ROW_SEARCH_CONF
|
|
--eval SET @@global.slave_rows_search_algorithms= "$initial_slave_rows_search"
|
|
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/metadata_replication_error.inc
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/metadata_replication_success.inc
|
|
--source include/rpl_end.inc
|
|
# End:
|