Debian Codegraph @

mysql-test/suite/x/r/prep_stmt_sundries.result

raw
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.]