Debian Codegraph @

mysql-test/suite/x/t/create_drop_collection_crud.test

raw
1 ########### ../t/create_drop_collection_crud.test #########
2 ## #
3 ## This test runs aims to run CREATE/DROP statement #
4 ## with mysqlxtest client. #
5 ## Test covers #
6 ## - Create collection with crud calls #
7 ## - plain create table #
8 ## - create table in different database with same name #
9 ## - change parameters order of create collection #
10 ## - alter and verify table created using xmessage #
11 ## - Access denied for user has restricted access #
12 ## - insert rejected when docc column does not have _id key #
13 ## required for table table created using create_collection #
14 ## Negative case #
15 ## - xmessage with empty args #
16 ## - xmessage with more than required args , invalid args type #
17 ## - try create table with existing name #
18 ## - Drop collection #
19 # - drop table created using xmessage and sql #
20 # - negative : drop non existing table #
21 ## #
22 #######################################################################
23
24
25
26 --source include/xplugin_preamble.inc
27 --source include/xplugin_create_user.inc
28 call mtr.add_suppression("Error parsing message of type 12: args");
29 call mtr.add_suppression("Error handling message: args");
30
31 ## Test starts here
32 --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl
33 -->sql
34 #-- "Creating database for testcase"
35 DROP DATABASE IF EXISTS xplugintest;
36 DROP DATABASE IF EXISTS xplugintest_1;
37 CREATE DATABASE xplugintest;
38 CREATE DATABASE xplugintest_1;
39 USE xplugintest;
40 CREATE TABLE table1 ( _id INT , doc JSON ) ;
41 -->endsql
42
43 #-- "Create table table2 in xplugintest "
44
45 -->stmtadmin create_collection {"schema":"xplugintest","name":"table2"}
46 -->recvresult
47
48 #-- "Create same name table table2 in xplugintest1 "
49 -->stmtadmin create_collection {"schema":"xplugintest_1","name":"table2"}
50 -->recvresult
51
52 #-- "Verify created tables"
53 -->sql
54 SHOW TABLES;
55 SHOW CREATE TABLE table1;
56 SHOW CREATE TABLE table2;
57 SHOW CREATE TABLE xplugintest_1.table2;
58
59 INSERT INTO table2 (doc) VALUES ('{"a":1,"_id":[100,200]}');
60 SELECT * FROM table2;
61 #-- "Alter document table created with crud"
62 ALTER TABLE table2 ADD COLUMN col1 INT ;
63 SELECT * FROM table2;
64 SHOW CREATE TABLE table1;
65 SHOW CREATE TABLE table2;
66
67 #-- "_id column value generated from doc column"
68 INSERT INTO table2 (doc,col1) VALUES ('{"a":2,"_id":[300,400]}',300);
69 SELECT * FROM table2;
70 #-- "_id is null as _id is not provided as key in json doc"
71 #-- "Insert is rejected as _id can not be null"
72 -->endsql
73 -->expecterror ER_BAD_NULL_ERROR
74 -->sql
75 INSERT INTO table2 (doc,col1) VALUES ('{"a":3,"b":[400,400]}',400);
76 SELECT * FROM table2;
77 -->endsql
78 #-- "Try insert using insert crud"
79 #-- "Insert is rejected as _id can not be null"
80 Mysqlx.Crud.Insert {
81 collection {
82 name: "table2"
83 schema: "xplugintest"
84 }
85 data_model: DOCUMENT
86 row {
87 field {
88 type: LITERAL
89 literal {
90 type: V_STRING
91 v_string {
92 value: "{\"a\":3,\"b\":[500,500]}"
93 }
94 }
95 }
96 }
97 }
98 ## error
99 -->recvresult
100 -->sql
101 SELECT * FROM table2;
102 ALTER TABLE table2 DROP COLUMN _id;
103 INSERT INTO table2 (doc,col1) VALUES ('{"a":4,"b":[400,400]}',400);
104 -->endsql
105 #-- "Now insert go through as we dropped _id column "
106 Mysqlx.Crud.Insert {
107 collection {
108 name: "table2"
109 schema: "xplugintest"
110 }
111 data_model: DOCUMENT
112 row {
113 field {
114 type: LITERAL
115 literal {
116 type: V_STRING
117 v_string {
118 value: "{\"a\":5,\"b\":[500,500]}"
119 }
120 }
121 }
122 }
123 }
124 -->recvresult
125 -->sql
126 SELECT * FROM table2;
127 SHOW CREATE TABLE table2;
128
129 -->endsql
130
131 #-- "Drop table"
132 -->sql
133 DROP TABLE table2;
134 -->endsql
135
136 #-- "Change order namespace , stmt "
137 -->stmtadmin create_collection {"schema":"xplugintest","name":"table2"}
138 -->recvresult
139 #-- "Show table"
140 -->sql
141 SHOW CREATE TABLE table2;
142 CREATE TABLE table2_tmp ( col1 INT);
143 -->endsql
144 #-- "Drop table created using create_collection message"
145 -->stmtadmin drop_collection {"schema":"xplugintest","name":"table2"}
146 -->recvresult
147
148 #-- "Drop table created using sql"
149 -->stmtadmin drop_collection {"schema":"xplugintest","name":"table2_tmp"}
150 -->recvresult
151
152 -->expecterror ER_NO_SUCH_TABLE
153 -->sql
154 SHOW CREATE TABLE table2;
155 -->endsql
156 #-- "Try to drop same table again"
157 -->stmtadmin drop_collection {"schema":"xplugintest","name":"table2"}
158 -->expecterror ER_BAD_TABLE_ERROR
159 -->recvresult
160
161 #-- "Try drop table from unkown database"
162 -->stmtadmin drop_collection {"schema":"xplugintest_non_existing","name":"table2"}
163 -->expecterror ER_BAD_TABLE_ERROR
164 -->recvresult
165
166 #-- "Incorrect args to drop_collection"
167 -->stmtadmin drop_collection {"schema":"xplugintest"}
168 -->expecterror ER_X_CMD_NUM_ARGUMENTS
169 -->recvresult
170
171 #-- "Invalid arg type"
172 -->stmtadmin drop_collection {"schema":"xplugintest_non_existing","name":3.14}
173 -->expecterror ER_X_CMD_ARGUMENT_VALUE
174 -->recvresult
175
176 EOF
177
178 --replace_regex /([0-9a-f]{4})[0-9a-f]{8}([0-9a-f]{16})/\1XXXXXXXX\2/
179 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1
180 --remove_file $MYSQL_TMP_DIR/mysqlx-in.xpl
181
182
183 --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl
184 #-- "Create table using namespace=sql "
185 Mysqlx.Sql.StmtExecute {
186 namespace: "sql"
187 stmt: "create table xplugintest.table_sql ( doc JSON ) "
188 }
189 -->recvresult
190 #-- "show table & drop it"
191 -->sql
192 SHOW CREATE TABLE xplugintest.table_sql;
193 INSERT INTO xplugintest.table_sql (doc) VALUES ('{"_id":"200"}');
194 SELECT * FROM xplugintest.table_sql;
195 DROP TABLE xplugintest.table_sql;
196 -->endsql
197 EOF
198
199 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1
200 --remove_file $MYSQL_TMP_DIR/mysqlx-in.xpl
201
202
203 --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl
204 #-- "Create table when user has no priviledge"
205 #-- "step 1 - create user1 with limied access"
206 -->sql
207 CREATE USER 'user1'@'localhost' identified by '';
208 GRANT USAGE ON *.* TO 'user1'@'localhost';
209 CREATE DATABASE xplugintest_user_access;
210 -->endsql
211 EOF
212 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1
213 --remove_file $MYSQL_TMP_DIR/mysqlx-in.xpl
214
215 --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl
216 #-- "step 2 - try to create table using user1"
217 -->stmtadmin create_collection {"schema":"xplugintest_user_access","name":"table1"}
218 ## Error
219 -->expecterror ER_TABLEACCESS_DENIED_ERROR
220 -->recvresult
221
222 #-- "show table "
223 -->expecterror ER_TABLEACCESS_DENIED_ERROR
224 -->sql
225 SHOW CREATE TABLE xplugintest_user_access.table1;
226 -->endsql
227 EOF
228 --exec $MYSQLXTEST -u user1 --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1
229 --remove_file $MYSQL_TMP_DIR/mysqlx-in.xpl
230
231 --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl
232 #-- "step 3 - clean user and database"
233 -->expecterror ER_NO_SUCH_TABLE
234 -->sql
235 SHOW CREATE TABLE xplugintest_user_access.table1;
236 DROP USER 'user1'@'localhost';
237 DROP DATABASE xplugintest_user_access;
238 -->endsql
239 EOF
240 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1
241 --remove_file $MYSQL_TMP_DIR/mysqlx-in.xpl
242
243
244
245 --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl
246 #-- "Negative cases"
247 #-- "1 empty args"
248 -->stmtadmin create_collection {}
249 ## error
250 -->expecterror ER_X_CMD_NUM_ARGUMENTS
251 -->recvresult
252 EOF
253 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1
254 --remove_file $MYSQL_TMP_DIR/mysqlx-in.xpl
255
256 --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl
257 #-- "2 more than two args"
258 -->stmtadmin create_collection {"schema":"xplugintest", "name":"table3", "alias":"table4"}
259 -->expecterror ER_X_CMD_INVALID_ARGUMENT
260 -->recvresult
261 EOF
262
263 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1
264 --remove_file $MYSQL_TMP_DIR/mysqlx-in.xpl
265
266 --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl
267 #-- "3 invalid namespace "
268 Mysqlx.Sql.StmtExecute {
269 namespace: "Invalid"
270 stmt: "create_collection"
271 args {
272 type: OBJECT obj {
273 fld {
274 key:'schema'
275 value{type:SCALAR scalar{type:V_STRING v_string{value:'xplugintest'}}}
276 }
277 fld {
278 key:'name'
279 value{type:SCALAR scalar{type:V_STRING v_string{value:'table2'}}}
280 }
281 }
282 }
283 }
284
285 ## error
286 -->expecterror ER_X_INVALID_NAMESPACE
287 -->recvresult
288 EOF
289
290 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1
291 --remove_file $MYSQL_TMP_DIR/mysqlx-in.xpl
292
293
294 --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl
295 #-- "Non existing database "
296
297 -->stmtadmin create_collection {"schema":"non_existing","name":"table2"}
298 ## error
299 -->expecterror ER_BAD_DB_ERROR
300 -->recvresult
301 EOF
302
303 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1
304 --remove_file $MYSQL_TMP_DIR/mysqlx-in.xpl
305
306 --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl
307 #-- "NULL Table name "
308 -->stmtadmin create_collection {"schema":"xplugintest","name":null}
309 ## error
310 -->expecterror ER_X_CMD_ARGUMENT_VALUE
311 -->recvresult
312 EOF
313
314 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1
315 --remove_file $MYSQL_TMP_DIR/mysqlx-in.xpl
316
317
318
319 --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl
320 #-- "Invaild parameter in args"
321 -->stmtadmin create_collection {"schema":{"name":"xplugintest"} ,"name":"table3"}
322 ## error
323 -->expecterror ER_X_CMD_ARGUMENT_VALUE
324 -->recvresult
325 EOF
326
327 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1
328 --remove_file $MYSQL_TMP_DIR/mysqlx-in.xpl
329
330 --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl
331 #-- "Create table with existing name"
332 -->stmtadmin create_collection {"schema":"xplugintest" ,"name":"table1"}
333 ## error
334 -->expecterror ER_TABLE_EXISTS_ERROR
335 -->recvresult
336 EOF
337
338 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1
339 --remove_file $MYSQL_TMP_DIR/mysqlx-in.xpl
340
341
342
343 --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl
344 ## Cleanup
345 -->sql
346 DROP DATABASE IF EXISTS xplugintest;
347 DROP DATABASE IF EXISTS xplugintest_1;
348 -->endsql
349 EOF
350
351 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1
352 --remove_file $MYSQL_TMP_DIR/mysqlx-in.xpl
353
354
355 # Try run create in parallel from two mysqlxtest
356 # input file include xmessage and sql for create
357 perl;
358 my $dir = $ENV{'MYSQL_TMP_DIR'};
359 open ( OUTPUT, ">$dir/mysqlx-in.xpl") ;
360 $message = <<"END_MESSAGE";
361 -->stmtadmin create_collection {"schema":"xplugintest","name":"table_name"}
362 END_MESSAGE
363
364 print OUTPUT "-->sql\n";
365 print OUTPUT "CREATE DATABASE IF NOT EXISTS xplugintest;\n";
366 print OUTPUT "USE xplugintest;\n";
367 print OUTPUT "CREATE TABLE IF NOT EXISTS test_table ( col1 INT );\n";
368
369 print OUTPUT "-->endsql\n";
370 for ($i=1;$i<=30;$i++) {
371 if ($i%5 == 0) {
372 print OUTPUT "-->expecterror ER_SUCCESS,ER_TABLE_EXISTS_ERROR\n";
373 print OUTPUT "-->stmtadmin create_collection {\"schema\":\"xplugintest\",\"name\":\"tab_".$i."\"}\n";
374 print OUTPUT "-->recvresult\n";
375 }else {
376 $str = $message;
377 $replace = "tab_".$i;
378 $find = "table_name";
379 $str =~ s/$find/$replace/g;
380 print OUTPUT "$str\n";
381 print OUTPUT "-->expecterror ER_SUCCESS,ER_TABLE_EXISTS_ERROR\n";
382 print OUTPUT "-->recvresult\n";
383 }
384 }
385 print OUTPUT "-->sql\n";
386 print OUTPUT "INSERT INTO xplugintest.test_table VALUES (1);\n";
387 print OUTPUT "-->endsql\n";
388 close (OUTPUT);
389 EOF
390 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl >$MYSQL_TMP_DIR/mysqlx-out.xpl 2>&1 &
391 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl >$MYSQL_TMP_DIR/mysqlx-out2.xpl 2>&1
392 let $wait_condition= select count(*)=2 from xplugintest.test_table;
393 --source include/wait_condition.inc
394 DROP DATABASE xplugintest;
395 --remove_file $MYSQL_TMP_DIR/mysqlx-in.xpl
396 --remove_file $MYSQL_TMP_DIR/mysqlx-out2.xpl
397 --remove_file $MYSQL_TMP_DIR/mysqlx-out.xpl
398
399
400 perl;
401 my $dir = $ENV{'MYSQL_TMP_DIR'};
402 open ( OUTPUT, ">$dir/mysqlx-in.xpl") ;
403 $message = <<"END_MESSAGE";
404 -->stmtadmin ensure_collection {"schema":"xplugintest","name":"table_name"}
405 END_MESSAGE
406
407 print OUTPUT "-->sql\n";
408 print OUTPUT "CREATE DATABASE IF NOT EXISTS xplugintest;\n";
409 print OUTPUT "USE xplugintest;\n";
410 print OUTPUT "CREATE TABLE IF NOT EXISTS test_table ( col1 INT );\n";
411
412 print OUTPUT "-->endsql\n";
413 for ($i=1;$i<=30;$i++) {
414 if ($i%5 == 0) {
415 print OUTPUT "-->expecterror ER_SUCCESS,ER_TABLE_EXISTS_ERROR\n";
416 print OUTPUT "-->stmtadmin create_collection {\"schema\":\"xplugintest\",\"name\":\"tab_".$i."\"}\n";
417 print OUTPUT "-->recvresult\n";
418 }else {
419 $str = $message;
420 $replace = "tab_".$i;
421 $find = "table_name";
422 $str =~ s/$find/$replace/g;
423 print OUTPUT "$str\n";
424 print OUTPUT "-->expecterror ER_SUCCESS,ER_TABLE_EXISTS_ERROR\n";
425 print OUTPUT "-->recvresult\n";
426 }
427 }
428 print OUTPUT "-->sql\n";
429 print OUTPUT "INSERT INTO xplugintest.test_table VALUES (1);\n";
430 print OUTPUT "-->endsql\n";
431 close (OUTPUT);
432 EOF
433 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl >$MYSQL_TMP_DIR/mysqlx-out.xpl 2>&1 &
434 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl >$MYSQL_TMP_DIR/mysqlx-out2.xpl 2>&1
435 let $wait_condition= select count(*)=2 from xplugintest.test_table;
436 --source include/wait_condition.inc
437
438 ## Cleanup
439 DROP DATABASE xplugintest;
440 --source ../include/xplugin_cleanup.inc