95 lines
4.1 KiB
Plaintext
95 lines
4.1 KiB
Plaintext
--source include/have_binlog_format_row.inc
|
|
--source include/master-slave.inc
|
|
--source include/have_debug.inc
|
|
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";
|
|
--source include/rpl_connection_master.inc
|
|
|
|
FLUSH LOGS;
|
|
|
|
SET binlog_row_image = MINIMAL;
|
|
|
|
--echo # Test non-nullable MV index
|
|
CREATE TABLE t1 (id INT, c INT, j JSON NOT NULL,
|
|
UNIQUE INDEX i1((CAST(CAST(j AS JSON) AS UNSIGNED ARRAY))));
|
|
INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
|
|
UPDATE t1 SET j = '[2,9,10]' WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
SHOW STATUS LIKE 'Slave_rows_last_search_algorithm_used';
|
|
--source include/rpl_connection_master.inc
|
|
DELETE FROM t1 WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
SHOW STATUS LIKE 'Slave_rows_last_search_algorithm_used';
|
|
--source include/rpl_connection_master.inc
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test nullable MV index
|
|
CREATE TABLE t1 (id INT, c INT, j JSON,
|
|
UNIQUE INDEX i1((CAST(CAST(j AS JSON) AS UNSIGNED ARRAY))));
|
|
INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
|
|
INSERT INTO t1 VALUES (3, 3, NULL);
|
|
UPDATE t1 SET j = '[2,9,10]' WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
SHOW STATUS LIKE 'Slave_rows_last_search_algorithm_used';
|
|
--source include/rpl_connection_master.inc
|
|
DELETE FROM t1 WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
SHOW STATUS LIKE 'Slave_rows_last_search_algorithm_used';
|
|
--source include/rpl_connection_master.inc
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test non-unique MV index
|
|
CREATE TABLE t1 (id INT, c INT, j JSON NOT NULL,
|
|
INDEX i1((CAST(CAST(j AS JSON) AS UNSIGNED ARRAY))));
|
|
INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
|
|
INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
|
|
UPDATE t1 SET j = '[2,9,10]' WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
SHOW STATUS LIKE 'Slave_rows_last_search_algorithm_used';
|
|
--source include/rpl_connection_master.inc
|
|
DELETE FROM t1 WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
SHOW STATUS LIKE 'Slave_rows_last_search_algorithm_used';
|
|
--source include/rpl_connection_master.inc
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test composite MV index
|
|
CREATE TABLE t1 (id INT, c INT, j JSON NOT NULL,
|
|
INDEX i1((CAST(CAST(j AS JSON) AS UNSIGNED ARRAY)), id));
|
|
INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
|
|
INSERT INTO t1 VALUES (1, 1, '[1,2,3,4]'), (2, 2, '[5,6,7,8]');
|
|
UPDATE t1 SET j = '[2,9,10]' WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
SHOW STATUS LIKE 'Slave_rows_last_search_algorithm_used';
|
|
--source include/rpl_connection_master.inc
|
|
DELETE FROM t1 WHERE JSON_CONTAINS(CAST(j AS JSON),'2');
|
|
--source include/sync_slave_sql_with_master.inc
|
|
--echo [Connection Slave]
|
|
SHOW STATUS LIKE 'Slave_rows_last_search_algorithm_used';
|
|
--source include/rpl_connection_master.inc
|
|
DROP TABLE t1;
|
|
|
|
--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"
|
|
|
|
--source include/rpl_end.inc
|
|
|