| 1 |
# |
| 2 |
# ==== Purpose ==== |
| 3 |
# |
| 4 |
# The test checks if different operations on rows with generated fields as json, |
| 5 |
# geometry, double and json using json function are completed without errors or |
| 6 |
# memory leaks when slave_rows_search_algorithms uses hash scan. |
| 7 |
# |
| 8 |
# ==== References ==== |
| 9 |
# |
| 10 |
# BUG#26280724 ASSERT FAILURE WHEN REPLICATING TABLE WITH VIRTUAL JSON COLUMN |
| 11 |
# BUG#25873029 HEAP-USE-AFTER-FREE IN RPL.RPL_JSON, RPL.RPL_VIRTUAL_GCOL |
| 12 |
# |
| 13 |
|
| 14 |
--source include/have_binlog_format_row.inc |
| 15 |
--source include/master-slave.inc |
| 16 |
|
| 17 |
--source include/rpl_connection_slave.inc |
| 18 |
set @saved_slave_rows_search_algorithms= @@global.slave_rows_search_algorithms; |
| 19 |
SET GLOBAL slave_rows_search_algorithms= 'INDEX_SCAN,HASH_SCAN'; |
| 20 |
--source include/rpl_connection_master.inc |
| 21 |
|
| 22 |
# |
| 23 |
# Generated column as json |
| 24 |
# |
| 25 |
CREATE TABLE gtable (doc JSON); |
| 26 |
INSERT INTO gtable (doc) VALUES |
| 27 |
('{"id": 1, "name": "abcd efgh"}'), |
| 28 |
('{"id": 2, "name": "abcd ijk"}'); |
| 29 |
ALTER TABLE gtable ADD COLUMN gtext JSON |
| 30 |
GENERATED ALWAYS AS (JSON_EXTRACT(doc, '$.name')) VIRTUAL; |
| 31 |
--source include/sync_slave_sql_with_master.inc |
| 32 |
|
| 33 |
# Update one of the rows |
| 34 |
|
| 35 |
--source include/rpl_connection_master.inc |
| 36 |
UPDATE gtable SET doc = JSON_SET(doc, '$.name', 'abcd lmnopqrstuvxz') |
| 37 |
WHERE JSON_EXTRACT(doc, '$.id') = 2; |
| 38 |
--source include/sync_slave_sql_with_master.inc |
| 39 |
|
| 40 |
# Insert a null row and modify it |
| 41 |
|
| 42 |
--source include/rpl_connection_master.inc |
| 43 |
INSERT INTO gtable(doc) VALUES (NULL); |
| 44 |
--source include/sync_slave_sql_with_master.inc |
| 45 |
|
| 46 |
--source include/rpl_connection_master.inc |
| 47 |
UPDATE gtable SET doc = '{ "id": 4, "name": "abc aba" }' where doc IS NULL; |
| 48 |
--source include/sync_slave_sql_with_master.inc |
| 49 |
|
| 50 |
--source include/rpl_connection_master.inc |
| 51 |
UPDATE gtable SET doc = NULL WHERE JSON_EXTRACT(doc, '$.id') = 4; |
| 52 |
--source include/sync_slave_sql_with_master.inc |
| 53 |
|
| 54 |
# Cleanup |
| 55 |
--source include/rpl_connection_master.inc |
| 56 |
DROP TABLE gtable; |
| 57 |
--source include/sync_slave_sql_with_master.inc |
| 58 |
|
| 59 |
# |
| 60 |
# Generated column as a json (array) |
| 61 |
# |
| 62 |
--source include/rpl_connection_master.inc |
| 63 |
CREATE TABLE gtable(id INT, doc JSON, gcol JSON AS (JSON_ARRAY(id, doc))); |
| 64 |
INSERT INTO gtable(id, doc) VALUES(10, '{"a": 1}'); |
| 65 |
--source include/sync_slave_sql_with_master.inc |
| 66 |
|
| 67 |
--source include/rpl_connection_master.inc |
| 68 |
UPDATE gtable SET doc = JSON_REPLACE(gcol, '$[0].a',"a"); |
| 69 |
--source include/sync_slave_sql_with_master.inc |
| 70 |
|
| 71 |
# Cleanup |
| 72 |
--source include/rpl_connection_master.inc |
| 73 |
DROP TABLE gtable; |
| 74 |
--source include/sync_slave_sql_with_master.inc |
| 75 |
|
| 76 |
# |
| 77 |
# Generated as geometry |
| 78 |
# |
| 79 |
--source include/rpl_connection_master.inc |
| 80 |
CREATE TABLE gtable (doc TEXT); |
| 81 |
INSERT INTO gtable (doc) VALUES ('point(1 1)'), ('point(2 2)'); |
| 82 |
ALTER TABLE gtable ADD COLUMN gtext GEOMETRY |
| 83 |
GENERATED ALWAYS AS (ST_GEOMFROMTEXT(doc)) VIRTUAL; |
| 84 |
|
| 85 |
UPDATE gtable SET doc = 'POINT(3 3)' WHERE ST_X(ST_GEOMFROMTEXT(doc)) = 2; |
| 86 |
INSERT INTO gtable(doc) VALUES (NULL); |
| 87 |
UPDATE gtable SET doc = 'POINT(4 4)' where doc IS NULL; |
| 88 |
UPDATE gtable SET doc = NULL WHERE ST_X(ST_GEOMFROMTEXT(doc)) = 4; |
| 89 |
--source include/sync_slave_sql_with_master.inc |
| 90 |
|
| 91 |
# Cleanup |
| 92 |
--source include/rpl_connection_master.inc |
| 93 |
DROP TABLE gtable; |
| 94 |
--source include/sync_slave_sql_with_master.inc |
| 95 |
|
| 96 |
# |
| 97 |
# Generated column as double |
| 98 |
# |
| 99 |
--source include/rpl_connection_master.inc |
| 100 |
CREATE TABLE gtable(a DOUBLE, |
| 101 |
b DOUBLE AS (a * a)); |
| 102 |
INSERT INTO gtable (a) VALUES (1); |
| 103 |
--source include/sync_slave_sql_with_master.inc |
| 104 |
|
| 105 |
--source include/rpl_connection_master.inc |
| 106 |
UPDATE gtable SET a= 2; |
| 107 |
--source include/sync_slave_sql_with_master.inc |
| 108 |
|
| 109 |
# Cleanup |
| 110 |
--source include/rpl_connection_master.inc |
| 111 |
DROP TABLE gtable; |
| 112 |
--source include/sync_slave_sql_with_master.inc |
| 113 |
|
| 114 |
# |
| 115 |
# Generated column as geometry using a geometry function that reads from another |
| 116 |
# geometry object |
| 117 |
# |
| 118 |
--source include/rpl_connection_master.inc |
| 119 |
CREATE TABLE gtable(geo GEOMETRY, gcol GEOMETRY AS (GEOMETRYCOLLECTION(geo, geo))); |
| 120 |
INSERT INTO gtable (geo) VALUES (POINT(1, 1)); |
| 121 |
--source include/sync_slave_sql_with_master.inc |
| 122 |
|
| 123 |
--source include/rpl_connection_master.inc |
| 124 |
UPDATE gtable SET geo = LINESTRING(POINT(1, 1), POINT(2, 2)); |
| 125 |
--source include/sync_slave_sql_with_master.inc |
| 126 |
|
| 127 |
# Cleanup |
| 128 |
--source include/rpl_connection_master.inc |
| 129 |
DROP TABLE gtable; |
| 130 |
--source include/sync_slave_sql_with_master.inc |
| 131 |
|
| 132 |
# |
| 133 |
# Generated column as a JSON using JSON function that reads from another JSON |
| 134 |
# object |
| 135 |
# |
| 136 |
|
| 137 |
# Save defaults and setup mts |
| 138 |
SET @replica_parallel_type_saved= @@GLOBAL.REPLICA_PARALLEL_TYPE; |
| 139 |
SET @replica_parallel_workers_saved= @@GLOBAL.REPLICA_PARALLEL_WORKERS; |
| 140 |
SET @slave_preserver_commit_order_saved= @@GLOBAL.REPLICA_PRESERVE_COMMIT_ORDER; |
| 141 |
--source include/stop_slave.inc |
| 142 |
SET GLOBAL replica_parallel_type= 'LOGICAL_CLOCK'; |
| 143 |
SET GLOBAL replica_parallel_workers= 4; |
| 144 |
SET GLOBAL replica_preserve_commit_order= ON; |
| 145 |
--source include/start_slave.inc |
| 146 |
CALL mtr.add_suppression("This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'"); |
| 147 |
|
| 148 |
--source include/rpl_connection_master.inc |
| 149 |
CREATE TABLE gtable(id INT, jd JSON, td TEXT, |
| 150 |
gcol JSON AS (JSON_OBJECT(id,jd,JSON_KEYS(jd),td))); |
| 151 |
INSERT INTO gtable(id,jd,td) VALUES(100, '{"a":1}', 'characters'); |
| 152 |
--source include/sync_slave_sql_with_master.inc |
| 153 |
|
| 154 |
# Warnings are disabled so that the warning regarding the lack of support for |
| 155 |
# the sorting of non-scalar JSON values is not printed |
| 156 |
--disable_warnings |
| 157 |
--let diff_tables=master:gtable, slave:gtable |
| 158 |
--source include/diff_tables.inc |
| 159 |
--enable_warnings |
| 160 |
|
| 161 |
--source include/rpl_connection_master.inc |
| 162 |
UPDATE gtable SET jd = JSON_REPLACE(jd, '$[0].a',"a"); |
| 163 |
--source include/sync_slave_sql_with_master.inc |
| 164 |
|
| 165 |
--disable_warnings |
| 166 |
--let diff_tables=master:gtable, slave:gtable |
| 167 |
--source include/diff_tables.inc |
| 168 |
--enable_warnings |
| 169 |
|
| 170 |
--source include/rpl_connection_master.inc |
| 171 |
UPDATE gtable SET jd= JSON_SET(jd, '$.key', '["h","e","l","l","o"]'); |
| 172 |
--source include/sync_slave_sql_with_master.inc |
| 173 |
|
| 174 |
--disable_warnings |
| 175 |
--let diff_tables=master:gtable, slave:gtable |
| 176 |
--source include/diff_tables.inc |
| 177 |
--enable_warnings |
| 178 |
|
| 179 |
# Cleanup |
| 180 |
--source include/rpl_connection_master.inc |
| 181 |
DROP TABLE gtable; |
| 182 |
--source include/sync_slave_sql_with_master.inc |
| 183 |
SET @@global.slave_rows_search_algorithms= @saved_slave_rows_search_algorithms; |
| 184 |
--source include/stop_slave.inc |
| 185 |
SET @@GLOBAL.REPLICA_PARALLEL_TYPE= @replica_parallel_type_saved; |
| 186 |
--disable_warnings |
| 187 |
SET @@GLOBAL.REPLICA_PARALLEL_WORKERS= @replica_parallel_workers_saved; |
| 188 |
--enable_warnings |
| 189 |
SET @@GLOBAL.REPLICA_PRESERVE_COMMIT_ORDER= @slave_preserver_commit_order_saved; |
| 190 |
--source include/start_slave.inc |
| 191 |
--source include/rpl_end.inc |