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."); 4CREATE USER user_127_0_0_1@127.0.0.1 IDENTIFIED WITH 'mysql_native_password' BY ''; 5CREATE USER user_8_8_8_8@8.8.8.8 IDENTIFIED WITH 'mysql_native_password' BY ''; 6CREATE USER user_all@'%' IDENTIFIED WITH 'mysql_native_password' BY ''; 7CREATE USER user_localhost@localhost IDENTIFIED WITH 'mysql_native_password' BY ''; 8CREATE USER user_localhost_8_8_8_8@localhost IDENTIFIED WITH 'mysql_native_password' BY ''; 9CREATE USER user_localhost_8_8_8_8@8.8.8.8 IDENTIFIED WITH 'mysql_native_password' BY ''; 10GRANT ALL ON *.* TO user_127_0_0_1@127.0.0.1; 11GRANT ALL ON *.* TO user_8_8_8_8@8.8.8.8; 12GRANT ALL ON *.* TO user_all@'%'; 13GRANT ALL ON *.* TO user_localhost@localhost; 14GRANT ALL ON *.* TO user_localhost_8_8_8_8@8.8.8.8; 15GRANT ALL ON *.* TO user_localhost_8_8_8_8@localhost; 16CREATE TABLE `city` ( 17`ID` int(11) NOT NULL auto_increment, 18`Name` char(35) NOT NULL default '', 19`CountryCode` char(3) NOT NULL default '', 20`District` char(20) NOT NULL default '', 21`Population` int(11) NOT NULL default '0', 22PRIMARY KEY (`ID`) 23); 24INSERT INTO `city` VALUES (1,'Kabul','AFG','Kabol',1780); 25INSERT INTO `city` VALUES (2,'Qandahar','AFG','Qandahar',237); 26INSERT INTO `city` VALUES (3,'Herat','AFG','Herat',186); 27INSERT INTO `city` VALUES (4,'Mazar-e-Sharif','AFG','Balkh',127); 28INSERT INTO `city` VALUES (5,'Amsterdam','NLD','Noord-Holland',731); 29INSERT INTO `city` VALUES (6,'Rotterdam','NLD','Zuid-Holland',593); 30call mtr.add_suppression("Plugin mysqlx reported: '1.1: Unsuccessful login attempt: Invalid user or password'"); 31call mtr.add_suppression("Plugin mysqlx reported: '2.1: Unsuccessful login attempt: Invalid user or password'"); 32Application terminated with expected error: Invalid user or password (code 1045) 33not ok 34Application terminated with expected error: Invalid user or password (code 1045) 35not ok 36send Mysqlx.Sql.StmtExecute { 37 stmt: "ping" 38 namespace: "mysqlx" 39} 40 41 42command ok 43RUN SELECT CONNECTION_TYPE from performance_schema.threads where processlist_command='Query'; 44CONNECTION_TYPE 45Socket 460 rows affected 47send Mysqlx.Crud.Find { 48 collection { 49 name: "city" 50 schema: "test" 51 } 52 data_model: TABLE 53 criteria { 54 type: OPERATOR 55 operator { 56 name: "<" 57 param { 58 type: IDENT 59 identifier { 60 name: "Population" 61 } 62 } 63 param { 64 type: LITERAL 65 literal { 66 type: V_SINT 67 v_signed_int: 250 68 } 69 } 70 } 71 } 72} 73 74ID Name CountryCode District Population 752 Qandahar AFG Qandahar 237 763 Herat AFG Herat 186 774 Mazar-e-Sharif AFG Balkh 127 78command ok 79Mysqlx.Ok { 80 msg: "bye!" 81} 82ok 83send Mysqlx.Sql.StmtExecute { 84 stmt: "ping" 85 namespace: "mysqlx" 86} 87 88 89command ok 90RUN SELECT CONNECTION_TYPE from performance_schema.threads where processlist_command='Query'; 91CONNECTION_TYPE 92Socket 930 rows affected 94send Mysqlx.Crud.Find { 95 collection { 96 name: "city" 97 schema: "test" 98 } 99 data_model: TABLE 100 criteria { 101 type: OPERATOR 102 operator { 103 name: "<" 104 param { 105 type: IDENT 106 identifier { 107 name: "Population" 108 } 109 } 110 param { 111 type: LITERAL 112 literal { 113 type: V_SINT 114 v_signed_int: 250 115 } 116 } 117 } 118 } 119} 120 121ID Name CountryCode District Population 1222 Qandahar AFG Qandahar 237 1233 Herat AFG Herat 186 1244 Mazar-e-Sharif AFG Balkh 127 125command ok 126Mysqlx.Ok { 127 msg: "bye!" 128} 129ok 130send Mysqlx.Sql.StmtExecute { 131 stmt: "ping" 132 namespace: "mysqlx" 133} 134 135 136command ok 137RUN SELECT CONNECTION_TYPE from performance_schema.threads where processlist_command='Query'; 138CONNECTION_TYPE 139Socket 1400 rows affected 141send Mysqlx.Crud.Find { 142 collection { 143 name: "city" 144 schema: "test" 145 } 146 data_model: TABLE 147 criteria { 148 type: OPERATOR 149 operator { 150 name: "<" 151 param { 152 type: IDENT 153 identifier { 154 name: "Population" 155 } 156 } 157 param { 158 type: LITERAL 159 literal { 160 type: V_SINT 161 v_signed_int: 250 162 } 163 } 164 } 165 } 166} 167 168ID Name CountryCode District Population 1692 Qandahar AFG Qandahar 237 1703 Herat AFG Herat 186 1714 Mazar-e-Sharif AFG Balkh 127 172command ok 173Mysqlx.Ok { 174 msg: "bye!" 175} 176ok 177Check if mysqlx_socket has correct value 178result 1791 180RUN SELECT CONNECTION_TYPE from performance_schema.threads where processlist_command='Query'; 181CONNECTION_TYPE 182Socket 1830 rows affected 184connecting... 185active session is now 'session2' 186RUN SELECT CONNECTION_TYPE from performance_schema.threads where processlist_command='Query'; 187CONNECTION_TYPE 188Socket 1890 rows affected 190connecting... 191active session is now 'session3' 192RUN SELECT CONNECTION_TYPE from performance_schema.threads where processlist_command='Query'; 193CONNECTION_TYPE 194Socket 1950 rows affected 196switched to session session3 197RUN select '3_1'; 198switched to session session2 199RUN select '2_1'; 200switched to session default 201RUN select '1_1'; 202switched to session session2 2032_1 2042_1 2050 rows affected 206switched to session session3 2073_1 2083_1 2090 rows affected 210switched to session default 2111_1 2121_1 2130 rows affected 214Mysqlx.Ok { 215 msg: "bye!" 216} 217ok 218# 219# Match capabilities with the result file, 220# must contain PLAIN and MYSQL41 221# 222Mysqlx.Connection.Capabilities(capabilities[0].value.array) = Mysqlx.Datatypes.Array { 223 value { 224 type: SCALAR 225 scalar { 226 type: V_STRING 227 v_string { 228 value: "MYSQL41" 229 } 230 } 231 } 232 value { 233 type: SCALAR 234 scalar { 235 type: V_STRING 236 v_string { 237 value: "PLAIN" 238 } 239 } 240 } 241} 242 243# 244# Do plain authentication (must be available) 245# 246Mysqlx.Session.AuthenticateOk { 247 auth_data: "" 248} 249 250ok 251# 252# Lets confirm that user can connect through UNIX socket connection is secure one 253# 254SET GLOBAL require_secure_transport:=1; 255send Mysqlx.Sql.StmtExecute { 256 stmt: "ping" 257 namespace: "mysqlx" 258} 259 260 261command ok 262RUN SELECT CONNECTION_TYPE from performance_schema.threads where processlist_command='Query'; 263CONNECTION_TYPE 264Socket 2650 rows affected 266send Mysqlx.Crud.Find { 267 collection { 268 name: "city" 269 schema: "test" 270 } 271 data_model: TABLE 272 criteria { 273 type: OPERATOR 274 operator { 275 name: "<" 276 param { 277 type: IDENT 278 identifier { 279 name: "Population" 280 } 281 } 282 param { 283 type: LITERAL 284 literal { 285 type: V_SINT 286 v_signed_int: 250 287 } 288 } 289 } 290 } 291} 292 293ID Name CountryCode District Population 2942 Qandahar AFG Qandahar 237 2953 Herat AFG Herat 186 2964 Mazar-e-Sharif AFG Balkh 127 297command ok 298Mysqlx.Ok { 299 msg: "bye!" 300} 301ok 302# 303# Match capabilities with the result file, 304# must contain PLAIN and MYSQL41 305# 306Mysqlx.Connection.Capabilities(capabilities[0].value.array) = Mysqlx.Datatypes.Array { 307 value { 308 type: SCALAR 309 scalar { 310 type: V_STRING 311 v_string { 312 value: "MYSQL41" 313 } 314 } 315 } 316 value { 317 type: SCALAR 318 scalar { 319 type: V_STRING 320 v_string { 321 value: "PLAIN" 322 } 323 } 324 } 325} 326 327# 328# Do plain authentication (must be available) 329# 330Mysqlx.Session.AuthenticateOk { 331 auth_data: "" 332} 333 334ok 335SET GLOBAL require_secure_transport:=0; 336# restart: --mysqlx-socket=SOCKET 337RUN SELECT CONNECTION_TYPE from performance_schema.threads where processlist_command='Query'; 338CONNECTION_TYPE 339Socket 3400 rows affected 341Mysqlx.Ok { 342 msg: "bye!" 343} 344ok 345DROP TABLE `city`; 346DROP USER user_127_0_0_1@127.0.0.1,user_8_8_8_8@8.8.8.8, user_all@'%', user_localhost@localhost, user_localhost_8_8_8_8@8.8.8.8, user_localhost_8_8_8_8@localhost; 347UNINSTALL PLUGIN mysqlx; 348