| 1 |
# restart |
| 2 |
CREATE SCHEMA IF NOT EXISTS xtest; |
| 3 |
USE xtest; |
| 4 |
CREATE TABLE xtable (phrase VARCHAR(30)); |
| 5 |
INSERT INTO xtable (phrase) VALUES ('Alice has a cat'); |
| 6 |
INSERT INTO xtable (phrase) VALUES ('Bob has a dog'); |
| 7 |
|
| 8 |
## 1. Verify prep-stat status variables that are set to zero |
| 9 |
## 2. Prepare statement |
| 10 |
# a. fails in case when invalid data are supplied |
| 11 |
# b. success for valid data with one placeholder |
| 12 |
## 3. Execute prepared statement |
| 13 |
# a. success for execute with one parameter |
| 14 |
# b. success for execute with two parameters (more than needed) |
| 15 |
# c. fails when no parameter was supplied |
| 16 |
# d. fails when parameter of invalid type was supplied |
| 17 |
# e. fails when using invalid ID for prepared-statmenent |
| 18 |
## 4. Deallocate prepared statement |
| 19 |
# a. success when using ID that was prepared |
| 20 |
# b. fails when using ID that was already deallocated |
| 21 |
# c. fails when using ID that was never prepared |
| 22 |
## 5. Mixed tests |
| 23 |
# a. prepare-deallocate, reuse the same ID in next prepare |
| 24 |
# b. prepare two times same statement, show that previous stmt was deallocated |
| 25 |
# c. prepare-execute-deallocate confirm that next execute with deallocated-id is going to fail |
| 26 |
# d. prepare-execute stmt that returns NULL |
| 27 |
## 6. Assert status variables for new session, they should be at zero |
| 28 |
## 7. Assert that prep-statment id is local in context of session |
| 29 |
## 8. Assert that prep-execute-statment id is local in context of session |
| 30 |
|
| 31 |
1. Prep-stmt variables are set to zero |
| 32 |
======================================= |
| 33 |
Verify [Status variable "Mysqlx_prep_prepare" needs to have a value of 0] |
| 34 |
Verify [Status variable "Mysqlx_prep_execute" needs to have a value of 0] |
| 35 |
Verify [Status variable "Mysqlx_prep_deallocate" needs to have a value of 0] |
| 36 |
Verify [Status variable "Prepared_stmt_count" needs to have a value of 0] |
| 37 |
Verify [Status variable "Com_stmt_prepare" needs to have a value of 0] |
| 38 |
Verify [Status variable "Com_stmt_execute" needs to have a value of 0] |
| 39 |
Verify [Status variable "Com_stmt_fetch" needs to have a value of 0] |
| 40 |
Verify [Status variable "Com_stmt_close" needs to have a value of 0] |
| 41 |
|
| 42 |
2.a. Malformed stmt for prepare |
| 43 |
================================ |
| 44 |
Got expected error: |
| 45 |
Mysqlx.Error { |
| 46 |
severity: ERROR |
| 47 |
code: 1049 |
| 48 |
msg: "Unknown database \'nonexistent\'" |
| 49 |
sql_state: "42000" |
| 50 |
} |
| 51 |
|
| 52 |
Verify [Status variable "Mysqlx_prep_prepare" needs to have a value of 1] |
| 53 |
Verify [Status variable "Prepared_stmt_count" needs to have a value of 0] |
| 54 |
|
| 55 |
2.b. Correct stmt |
| 56 |
================== |
| 57 |
RUN recvok |
| 58 |
Verify [Status variable "Mysqlx_prep_prepare" needs to have a value of 2] |
| 59 |
Verify [Status variable "Prepared_stmt_count" needs to have a value of 1] |
| 60 |
|
| 61 |
3.a. Correct stmt execute |
| 62 |
========================== |
| 63 |
count('*') |
| 64 |
2 |
| 65 |
command ok |
| 66 |
Verify [Status variable "Mysqlx_prep_execute" needs to have a value of 1] |
| 67 |
|
| 68 |
3.b. Correct stmt execute (more than one expected args) |
| 69 |
======================================================== |
| 70 |
count('*') |
| 71 |
1 |
| 72 |
command ok |
| 73 |
Verify [Status variable "Mysqlx_prep_execute" needs to have a value of 2] |
| 74 |
|
| 75 |
3.c. Wrong stmt execute (no arg) |
| 76 |
================================= |
| 77 |
Got expected error: There is no argument for statement placeholder at position: 0 (code 5134) |
| 78 |
Verify [Status variable "Mysqlx_prep_execute" needs to have a value of 3] |
| 79 |
|
| 80 |
3.d. Correct stmt executed, still argument type is invalid (only scalar is supported) |
| 81 |
====================================================================================== |
| 82 |
Got expected error: Argument at index '0' and of type 'Mysqlx.Datatypes.Any' is not supported for binding to prepared statement (code 5133) |
| 83 |
Verify [Status variable "Mysqlx_prep_execute" needs to have a value of 4] |
| 84 |
|
| 85 |
3.e. Wrong stmt execute (wrong id) |
| 86 |
=================================== |
| 87 |
Got expected error: Statement with ID=42 was not prepared (code 5110) |
| 88 |
Verify [Status variable "Mysqlx_prep_execute" needs to have a value of 5] |
| 89 |
|
| 90 |
4.a. Correct stmt close |
| 91 |
======================== |
| 92 |
RUN recvok |
| 93 |
Verify [Status variable "Mysqlx_prep_deallocate" needs to have a value of 1] |
| 94 |
Verify [Status variable "Prepared_stmt_count" needs to have a value of 0] |
| 95 |
|
| 96 |
4.b. Wrong stmt close (bad id) / try to close second time |
| 97 |
========================================================== |
| 98 |
Got expected error: |
| 99 |
Mysqlx.Error { |
| 100 |
severity: ERROR |
| 101 |
code: 5110 |
| 102 |
msg: "Statement with ID=1 was not prepared" |
| 103 |
sql_state: "HY000" |
| 104 |
} |
| 105 |
|
| 106 |
Verify [Status variable "Mysqlx_prep_deallocate" needs to have a value of 2] |
| 107 |
|
| 108 |
4.c. Wrong stmt close (bad id) |
| 109 |
=============================== |
| 110 |
Got expected error: |
| 111 |
Mysqlx.Error { |
| 112 |
severity: ERROR |
| 113 |
code: 5110 |
| 114 |
msg: "Statement with ID=77 was not prepared" |
| 115 |
sql_state: "HY000" |
| 116 |
} |
| 117 |
|
| 118 |
Verify [Status variable "Mysqlx_prep_deallocate" needs to have a value of 3] |
| 119 |
|
| 120 |
5.a. Reuse deallocated ID |
| 121 |
========================== |
| 122 |
RUN recvok |
| 123 |
Verify [Status variable "Prepared_stmt_count" needs to have a value of 1] |
| 124 |
RUN recvok |
| 125 |
RUN recvok |
| 126 |
Verify [Status variable "Prepared_stmt_count" needs to have a value of 1] |
| 127 |
RUN recvok |
| 128 |
Verify [Status variable "Prepared_stmt_count" needs to have a value of 0] |
| 129 |
|
| 130 |
5.b. Reuse ID which deallocates previous one |
| 131 |
============================================= |
| 132 |
RUN recvok |
| 133 |
Verify [Status variable "Prepared_stmt_count" needs to have a value of 1] |
| 134 |
RUN recvok |
| 135 |
Verify [Status variable "Prepared_stmt_count" needs to have a value of 1] |
| 136 |
RUN recvok |
| 137 |
|
| 138 |
5.c. prepare-execute-deallocate confirm that next execute with deallocated-id is going to fail |
| 139 |
=============================================================================================== |
| 140 |
Got expected error: |
| 141 |
Mysqlx.Error { |
| 142 |
severity: ERROR |
| 143 |
code: 5110 |
| 144 |
msg: "Statement with ID=3 was not prepared" |
| 145 |
sql_state: "HY000" |
| 146 |
} |
| 147 |
|
| 148 |
Verify [Status variable "Prepared_stmt_count" needs to have a value of 0] |
| 149 |
|
| 150 |
5.d. prepare-execute stmt that returns NULL |
| 151 |
============================================ |
| 152 |
RUN recvok |
| 153 |
NULL |
| 154 |
null |
| 155 |
0 rows affected |
| 156 |
RUN recvok |
| 157 |
Verify [Status variable "Prepared_stmt_count" needs to have a value of 0] |
| 158 |
Verify [Status variable "Com_stmt_close" needs to have a value of 6] |
| 159 |
Verify [Status variable "Com_stmt_prepare" needs to have a value of 7] |
| 160 |
Verify [Status variable "Com_stmt_execute" needs to have a value of 5] |
| 161 |
Verify [Status variable "Com_stmt_fetch" needs to have a value of 0] |
| 162 |
|
| 163 |
6. Assert status variables for new session, they should be at zero |
| 164 |
=================================================================== |
| 165 |
connecting... |
| 166 |
active session is now 's1' |
| 167 |
Verify [Status variable "Mysqlx_prep_prepare" needs to have a value of 0] |
| 168 |
Verify [Status variable "Mysqlx_prep_execute" needs to have a value of 0] |
| 169 |
Verify [Status variable "Mysqlx_prep_deallocate" needs to have a value of 0] |
| 170 |
|
| 171 |
7. Assert that prep-statment id is local in context of session |
| 172 |
=============================================================== |
| 173 |
RUN recvok |
| 174 |
closing session s1 |
| 175 |
Mysqlx.Ok { |
| 176 |
msg: "bye!" |
| 177 |
} |
| 178 |
switched to session default |
| 179 |
connecting... |
| 180 |
active session is now 's2' |
| 181 |
Got expected error: Statement with ID=1 was not prepared (code 5110) |
| 182 |
RUN recvok |
| 183 |
|
| 184 |
8. Assert that prep-execute-statment id is local in context of session |
| 185 |
======================================================================= |
| 186 |
RUN recvok |
| 187 |
1 |
| 188 |
1 |
| 189 |
0 rows affected |
| 190 |
closing session s2 |
| 191 |
Mysqlx.Ok { |
| 192 |
msg: "bye!" |
| 193 |
} |
| 194 |
switched to session default |
| 195 |
connecting... |
| 196 |
active session is now 's2' |
| 197 |
Got expected error: Statement with ID=1 was not prepared (code 5110) |
| 198 |
RUN recvok |
| 199 |
closing session s2 |
| 200 |
Mysqlx.Ok { |
| 201 |
msg: "bye!" |
| 202 |
} |
| 203 |
ok |
| 204 |
# Syncing with status [ 'Mysqlx_connections_closed' == 4 ] |
| 205 |
# Syncing with status [ 'Mysqlx_sessions' == 0 ] |
| 206 |
|
| 207 |
## Assert global X Plugin status variables |
| 208 |
include/assert.inc [Global plugin status Mysqlx_prep_prepare must match number of send Mysqlx.Prepare.Prepare messages] |
| 209 |
include/assert.inc [Global plugin status Mysqlx_prep_execute must match number of send Mysqlx.Prepare.Execute messages] |
| 210 |
include/assert.inc [Global plugin status Mysqlx_prep_deallocate must match number of send Mysqlx.Prepare.Deallocate messages] |
| 211 |
|
| 212 |
## Assert global server status variables |
| 213 |
include/assert.inc [Global system status Prepared_stmt_count must be set to zero, all prep-stmt must be deallocated.] |
| 214 |
include/assert.inc [Global system status Com_stmt_prepare must be set to zero, all prep-stmt must be deallocated.] |
| 215 |
include/assert.inc [Global system status Com_stmt_execute must be set to zero, all prep-stmt must be deallocated.] |
| 216 |
include/assert.inc [Global system status Com_stmt_fetch must be set to zero, all prep-stmt must be deallocated.] |
| 217 |
include/assert.inc [Global system status Com_stmt_close must be set to zero, all prep-stmt must be deallocated.] |