1install plugin mysqlx soname "mysqlx.so"; 2call mtr.add_suppression("Plugin mysqlx reported: .Failed at SSL configuration: .SSL context is not usable without certificate and private key.."); 3call mtr.add_suppression("Plugin mysqlx reported: .SSL_CTX_load_verify_locations failed."); 4RUN DROP DATABASE IF EXISTS xplugintest 5 60 rows affected 7RUN CREATE DATABASE xplugintest 8 91 rows affected 10RUN USE xplugintest 11 120 rows affected 13RUN CREATE TABLE t1 ( col1 INT , col2 FLOAT(7,4), col3 REAL(7,4) , col4 VARCHAR(20) , col5 BLOB ) 14 150 rows affected 16RUN CREATE TABLE t2 ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB , col6 JSON ) 17 180 rows affected 19RUN CREATE TEMPORARY TABLE t3 ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB , col6 JSON ) 20 210 rows affected 22RUN CREATE TABLE t6 (val INT , col2 JSON) PARTITION BY LIST(val)( PARTITION mypart1 VALUES IN (1,3,5), PARTITION MyPart2 VALUES IN (2,4,6) ) 23 240 rows affected 25RUN CREATE TABLE t7 ( col1 INT PRIMARY KEY , col2 DOUBLE(7,4) UNIQUE KEY, col3 DECIMAL(7,4) NOT NULL COMMENT 'not_null', col4 VARCHAR(20) DEFAULT 'xyz' , col5 BLOB , col6 JSON ) 26 270 rows affected 28RUN CREATE TABLE t8 ( col1 INT REFERENCES t7(col1), col6 JSON ) 29 300 rows affected 31RUN CREATE TABLE t9 ( col1 INT NOT NULL AUTO_INCREMENT, col2 DOUBLE(7,4) , col3 DECIMAL(7,4) COMMENT 'Test', col4 VARCHAR(20) DEFAULT 'xyz' , col5 BLOB , col6 JSON , PRIMARY KEY (col1), INDEX `idx1` (col2) , UNIQUE KEY `idx2` (col4) ) 32 330 rows affected 34RUN CREATE TABLE t10 ( col1 INT NOT NULL AUTO_INCREMENT, col2 DOUBLE(7,4) , col3 DECIMAL(7,4) COMMENT 'Test', col4 VARCHAR(20) DEFAULT 'xyz' , col5 BLOB , col6 JSON , PRIMARY KEY (col1), INDEX `idx1` (col2) , UNIQUE KEY `idx2` (col4) ) ENGINE = Innodb , MAX_ROWS=100 35 360 rows affected 37RUN SET @tabname = 't11' 38 390 rows affected 40RUN SET @stmt1 = CONCAT('CREATE TABLE ',@tabname ,' ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB , col6 JSON)') 41 420 rows affected 43RUN PREPARE stmt2 FROM @stmt1 44 450 rows affected 46Statement prepared 47RUN EXECUTE stmt2 48 490 rows affected 50RUN DROP TABLE t11 51 520 rows affected 53RUN EXECUTE stmt2 54 550 rows affected 56RUN DEALLOCATE PREPARE stmt2 57 580 rows affected 59RUN INSERT INTO t1 VALUES (1, 999.10009 , 999.10009 , 'Row one' , REPEAT('t1r1',512)) 60 611 rows affected 62RUN INSERT INTO t2 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t2r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' ) 63 641 rows affected 65RUN INSERT INTO t3 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t3r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' ) 66 671 rows affected 68RUN INSERT INTO t6 VALUES (1, '{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' ) 69 701 rows affected 71RUN INSERT INTO t7 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t7r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' ) 72 731 rows affected 74RUN INSERT INTO t8 VALUES (1, '{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' ) 75 761 rows affected 77RUN INSERT INTO t9 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t9r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' ) 78 791 rows affected 80last insert id: 1 81RUN INSERT INTO t10 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t10r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' ) 82 831 rows affected 84last insert id: 1 85RUN INSERT INTO t11 VALUES (1, 999.10009, 999.10009 , 'Row one' , REPEAT('t10r1',512) ,'{"a":"b", "c":"d","ab":"abc", "bc": ["x", "y"]}' ) 86 871 rows affected 88RUN CREATE TABLE t4 LIKE t2 89 900 rows affected 91RUN CREATE TABLE t5 SELECT col1,col6 FROM t2 92 931 rows affected 94Records: 1 Duplicates: 0 Warnings: 0 95RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10) FROM t1 96col1 col2 col3 col4 SUBSTR(col5,1,10) 971 999.1001 999.1001 Row one t1r1t1r1t1 980 rows affected 99RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t2 100col1 col2 col3 col4 SUBSTR(col5,1,10) col6 1011 999.1001 999.1001 Row one t2r1t2r1t2 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 1020 rows affected 103RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t3 104col1 col2 col3 col4 SUBSTR(col5,1,10) col6 1051 999.1001 999.1001 Row one t3r1t3r1t3 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 1060 rows affected 107RUN SELECT * FROM t4 108col1 col2 col3 col4 col5 col6 1090 rows affected 110RUN SELECT * FROM t5 111col1 col6 1121 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 1130 rows affected 114RUN SELECT * FROM t6 115val col2 1161 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 1170 rows affected 118RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t7 119col1 col2 col3 col4 SUBSTR(col5,1,10) col6 1201 999.1001 999.1001 Row one t7r1t7r1t7 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 1210 rows affected 122RUN SELECT * FROM t8 123col1 col6 1241 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 1250 rows affected 126RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t9 127col1 col2 col3 col4 SUBSTR(col5,1,10) col6 1281 999.1001 999.1001 Row one t9r1t9r1t9 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 1290 rows affected 130RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t10 131col1 col2 col3 col4 SUBSTR(col5,1,10) col6 1321 999.1001 999.1001 Row one t10r1t10r1 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 1330 rows affected 134RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6 FROM t11 135col1 col2 col3 col4 SUBSTR(col5,1,10) col6 1361 999.1001 999.1001 Row one t10r1t10r1 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 1370 rows affected 138RUN CREATE TABLE t1 ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB ) 139While executing CREATE TABLE t1 ( col1 INT , col2 DOUBLE(7,4), col3 DECIMAL(7,4) , col4 VARCHAR(20) , col5 BLOB ) : 140Got expected error: Table 't1' already exists (code 1050) 141RUN CREATE TABLE t1 ( col1 INVALID_DATA_TYPE , col2 JSON) 142While executing CREATE TABLE t1 ( col1 INVALID_DATA_TYPE , col2 JSON) : 143Got expected error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INVALID_DATA_TYPE , col2 JSON)' at line 1 (code 1064) 144RUN CREATE TABLE t1 ( col1 INT NULL PRIMARY KEY , col2 JSON) 145While executing CREATE TABLE t1 ( col1 INT NULL PRIMARY KEY , col2 JSON) : 146Got expected error: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead (code 1171) 147RUN CREATE TABLE t1 ( col1 INT NULL , col2 JSON,PRIMARY KEY(col3)) 148While executing CREATE TABLE t1 ( col1 INT NULL , col2 JSON,PRIMARY KEY(col3)) : 149Got expected error: Key column 'col3' doesn't exist in table (code 1072) 150RUN CREATE TABLE t1 ( col1 INT NULL , col2 JSON) ENGINE=INVALID 151While executing CREATE TABLE t1 ( col1 INT NULL , col2 JSON) ENGINE=INVALID: 152Got expected error: Unknown storage engine 'INVALID' (code 1286) 153RUN LOCK TABLES t1 READ 154 1550 rows affected 156RUN CREATE TABLE t2 ( col1 INT ) 157While executing CREATE TABLE t2 ( col1 INT ): 158Got expected error: Table 't2' was not locked with LOCK TABLES (code 1100) 159RUN UNLOCK TABLES 160 1610 rows affected 162RUN ALTER TABLE t2 ADD COLUMN (col7 JSON), 163 ADD COLUMN (col8 JSON DEFAULT NULL), 164 ADD COLUMN (col9 VARCHAR(100) DEFAULT 'testdata'), 165 ADD INDEX `idx9` (col9), 166 ADD FULLTEXT INDEX `idx_fulltext` (col9) 167 1680 rows affected 169Records: 0 Duplicates: 0 Warnings: 1 170RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6,col7,col8,col9 FROM t2 171col1 col2 col3 col4 SUBSTR(col5,1,10) col6 col7 col8 col9 1721 999.1001 999.1001 Row one t2r1t2r1t2 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} null null testdata 1730 rows affected 174RUN ALTER TABLE t2 DROP INDEX `idx_fulltext` , ALGORITHM=COPY 175 1761 rows affected 177Records: 1 Duplicates: 0 Warnings: 0 178RUN ALTER TABLE t2 DROP INDEX `idx9` , ALGORITHM=INPLACE 179 1800 rows affected 181Records: 0 Duplicates: 0 Warnings: 0 182RUN ALTER TABLE t2 DROP COLUMN col7 , ALGORITHM=INPLACE 183 1840 rows affected 185Records: 0 Duplicates: 0 Warnings: 0 186RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6,col8,col9 FROM t2 187col1 col2 col3 col4 SUBSTR(col5,1,10) col6 col8 col9 1881 999.1001 999.1001 Row one t2r1t2r1t2 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} null testdata 1890 rows affected 190RUN ALTER TABLE t6 REMOVE PARTITIONING 191 1921 rows affected 193Records: 1 Duplicates: 0 Warnings: 0 194RUN SHOW CREATE TABLE t2 195Table Create Table 196t2 CREATE TABLE `t2` ( 197 `col1` int(11) DEFAULT NULL, 198 `col2` double(7,4) DEFAULT NULL, 199 `col3` decimal(7,4) DEFAULT NULL, 200 `col4` varchar(20) DEFAULT NULL, 201 `col5` blob, 202 `col6` json DEFAULT NULL, 203 `col8` json DEFAULT NULL, 204 `col9` varchar(100) DEFAULT 'testdata' 205) ENGINE=InnoDB DEFAULT CHARSET=latin1 2060 rows affected 207RUN SELECT * FROM t6 208val col2 2091 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} 2100 rows affected 211RUN CREATE INDEX `idx9` ON t2 ( col9(50) ) USING BTREE ALGORITHM=DEFAULT LOCK=SHARED 212 2130 rows affected 214Records: 0 Duplicates: 0 Warnings: 0 215RUN CREATE UNIQUE INDEX `idx5` ON t2 (col9(50)) 216 2170 rows affected 218Records: 0 Duplicates: 0 Warnings: 0 219RUN CREATE FULLTEXT INDEX `idx_fulltext` ON t2 (col9) 220 2210 rows affected 222Records: 0 Duplicates: 0 Warnings: 1 223RUN SELECT col1,col2,col3,col4,SUBSTR(col5,1,10),col6,col8,col9 FROM t2 224col1 col2 col3 col4 SUBSTR(col5,1,10) col6 col8 col9 2251 999.1001 999.1001 Row one t2r1t2r1t2 {"a": "b", "c": "d", "ab": "abc", "bc": ["x", "y"]} null testdata 2260 rows affected 227RUN ALTER TABLE t2 ADD PRIMARY KEY (col1) 228 2291 rows affected 230Records: 1 Duplicates: 0 Warnings: 0 231RUN DROP INDEX `idx9` ON t2 ALGORITHM=DEFAULT LOCK=SHARED 232 2330 rows affected 234Records: 0 Duplicates: 0 Warnings: 0 235RUN DROP INDEX `idx5` ON t2 236 2370 rows affected 238Records: 0 Duplicates: 0 Warnings: 0 239RUN DROP INDEX `PRIMARY` ON t2 240 2411 rows affected 242Records: 1 Duplicates: 0 Warnings: 0 243RUN SHOW CREATE TABLE t2 244Table Create Table 245t2 CREATE TABLE `t2` ( 246 `col1` int(11) NOT NULL, 247 `col2` double(7,4) DEFAULT NULL, 248 `col3` decimal(7,4) DEFAULT NULL, 249 `col4` varchar(20) DEFAULT NULL, 250 `col5` blob, 251 `col6` json DEFAULT NULL, 252 `col8` json DEFAULT NULL, 253 `col9` varchar(100) DEFAULT 'testdata', 254 FULLTEXT KEY `idx_fulltext` (`col9`) 255) ENGINE=InnoDB DEFAULT CHARSET=latin1 2560 rows affected 257RUN DROP DATABASE IF EXISTS xplugintest 258 25910 rows affected 260Mysqlx.Ok { 261 msg: "bye!" 262} 263ok 264UNINSTALL PLUGIN mysqlx; 265