Debian Codegraph @

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

raw
1 ## ===================================================================
2 ##
3 ## CRUD INSERT SCENARIOS
4 ##
5 ## ===================================================================
6 ## This test runs aims to run insert statementwith mysqlxtest client #
7 ## Test covers #
8 ## - Insert with crud calls #
9 ## - Insert in normal and document table #
10 ## - Insert NULL values #
11 ## - Insert into parent-child(PK-FK) table #
12 ## - Insert with transaction #
13 ## - Insert with lock table #
14 ## - Insert not allowed for user having read permission #
15 ## - Try insert when table lock is acquired by other session(wait) #
16 ## - Two session run insert in parallel #
17 ## - Error case #
18 ## - Try to Insert doc which do not have _id member #
19 ## - Try to insert null for doc column #
20 ## - Try non unique value for _id member #
21 ## - Try projection for DOCUMENT TABLE #
22 ## ===================================================================
23
24 # Insert xmessage
25 # Inserts a row or set of rows in the specified collection or table.
26 # Arguments
27 # schema and table
28 # data_model - relational or document
29 # projection - list of columns being inserted (for TABLE inserts)
30 # rows - 1 or more rows with the data to be inserted
31 # Results
32 # rows_affected - number of rows that were affected by the operation
33 # last_insert_id - last value used for the auto-increment field in
34 # a TABLE INSERT operation (not set for DOCUMENT inserts)
35 # Mapping
36 # When inserting into a document collection, the generated INSERT
37 # statement will look like:
38 # INSERT INTO schema.table (doc, _id) VALUES (?, JSN_UNQUOTE(JSN_EXTRACT(doc, '$._id')));
39 # When inserting into a relational table, the generated INSERT will be as:
40 # INSERT INTO schema.table (projection, ...) VALUES (?, ...);
41
42
43
44 ## Preamble
45 --source include/xplugin_preamble.inc
46 --source include/xplugin_create_user.inc
47 create user readuser@localhost identified by 'readuser';
48 grant select on *.* to readuser@localhost;
49
50 --write_file $MYSQL_TMP_DIR/mysqlx-insert_CRUD_1.xpl
51 #-- "Create relational TABLE "
52 -->sql
53 DROP SCHEMA if EXISTS xplugintest;
54 CREATE SCHEMA xplugintest DEFAULT CHARSET='utf8';
55 USE xplugintest;
56 CREATE TABLE categories (
57 categoryid int NOT NULL AUTO_INCREMENT,
58 categoryname varchar(100),
59 categorydescription varchar(200),
60 categoryimei tinyint,
61 categorydecimal decimal(5,2),
62 categorybool bit(1),
63 PRIMARY key (categoryid)
64 );
65 -->endsql
66
67 -->echo Insert multiple rows
68 Mysqlx.Crud.Insert {
69 collection {
70 name: "categories"
71 schema: "xplugintest"
72 }
73 data_model: TABLE
74 projection {
75 name: "categoryid"
76 }
77 projection {
78 name: "categoryname"
79 }
80 projection {
81 name: "categorydescription"
82 }
83 projection {
84 name: "categoryimei"
85 }
86 projection {
87 name: "categorydecimal"
88 }
89 projection {
90 name: "categorybool"
91 }
92 row {
93 field {
94 type : LITERAL
95 literal {
96 type: V_SINT
97 v_signed_int: 1
98 }}
99 field {
100 type: LITERAL
101 literal {
102 type: V_STRING
103 v_string {
104 value: "Sports"
105 }
106 }}
107 field {
108 type: LITERAL
109 literal {
110 type: V_STRING
111 v_string {
112 value: "Sports related category"
113 }
114 }}
115 field {
116 type : LITERAL
117 literal {
118 type: V_SINT
119 v_signed_int: 1
120 }}
121 field {
122 type: LITERAL
123 literal {
124 type: V_DOUBLE
125 v_double: 235.15
126 }}
127 field {
128 type : LITERAL
129 literal {
130 type: V_BOOL
131 v_bool: false
132 }}
133 }
134 row {
135 field {
136 type: LITERAL
137 literal {
138 type: V_SINT
139 v_signed_int: 2
140 }}
141 field {
142 type: LITERAL
143 literal {
144 type: V_STRING
145 v_string {
146 value: "Entertaiment"
147 }
148 }}
149 field {
150 type : LITERAL
151 literal {
152 type: V_STRING
153 v_string {
154 value: "Entertaiment related category"
155 }
156 }}
157 field {
158 type: LITERAL
159 literal {
160 type: V_SINT
161 v_signed_int: 2
162 }}
163 field {
164 type: LITERAL
165 literal {
166 type: V_DOUBLE
167 v_double: 156.17
168 }}
169 field {
170 type: LITERAL
171 literal {
172 type: V_BOOL
173 v_bool: true
174 }}
175 }
176 row {
177 field {
178 type: LITERAL
179 literal {
180 type: V_SINT
181 v_signed_int: 18
182 }}
183 field {
184 type: LITERAL
185 literal {
186 type: V_STRING
187 v_string {
188 value: "Cooking"
189 }
190 }}
191 field {
192 type: LITERAL
193 literal {
194 type: V_STRING
195 v_string {
196 value: "Cooking related category"
197 }
198 }}
199 field {
200 type: LITERAL
201 literal {
202 type: V_SINT
203 v_signed_int: 18
204 }}
205 field {
206 type: LITERAL
207 literal {
208 type: V_DOUBLE
209 v_double: 116.17
210 }}
211 field {
212 type: LITERAL
213 literal {
214 type: V_BOOL
215 v_bool: true
216 }}
217 }
218 }
219 #-- Mysqlx.Sql.StmtExecuteOk
220 -->recvresult
221 -->sql
222 SELECT * FROM categories;
223 -->endsql
224
225 -->echo "No auto increment column mentioned in projection"
226 Mysqlx.Crud.Insert {
227 collection {
228 name: "categories"
229 schema: "xplugintest"
230 }
231 data_model: TABLE
232 projection {
233 name: "categoryname"
234 }
235 row {
236 field {
237 type: LITERAL
238 literal {
239 type: V_STRING
240 v_string {
241 value: "GROUND"
242 }
243 }}
244 }
245 }
246 -->recvresult
247 -->sql
248 SELECT * FROM categories;
249 -->endsql
250
251 -->echo "Enter NULL values"
252 Mysqlx.Crud.Insert {
253 collection {
254 name: "categories"
255 schema: "xplugintest"
256 }
257 data_model: TABLE
258 projection {
259 name: "categoryname"
260 }
261 row {
262 field {
263 type: LITERAL
264 literal {
265 type: V_NULL
266 }}
267 }
268 }
269 -->recvresult
270 -->sql
271 SELECT * FROM categories;
272 -->endsql
273
274 -->sql
275 CREATE TABLE categories_child( col1 INT , col2 JSON ,
276 FOREIGN KEY (col1) REFERENCES categories(categoryid));
277 -->endsql
278 Mysqlx.Crud.Insert {
279 collection {
280 name: "categories_child"
281 schema: "xplugintest"
282 }
283 data_model: TABLE
284 projection {
285 name: "col1"
286 }
287 row {
288 field {
289 type: LITERAL
290 literal {
291 type: V_SINT
292 v_signed_int: 18
293 }}
294 }
295 }
296 -->recvresult
297
298 Mysqlx.Crud.Insert {
299 collection {
300 name: "categories_child"
301 schema: "xplugintest"
302 }
303 data_model: TABLE
304 projection {
305 name: "col1"
306 }
307 row {
308 field {
309 type: LITERAL
310 literal {
311 type: V_SINT
312 v_signed_int: 100
313 }}
314 }
315 }
316 -->echo "Insert fails as referencing value not present in parent"
317 -->expecterror ER_NO_REFERENCED_ROW_2
318 -->recvresult
319 -->sql
320 SELECT * FROM categories_child;
321 -->endsql
322
323 -->echo "Column mismatch"
324 Mysqlx.Crud.Insert {
325 collection {
326 name: "categories_child"
327 schema: "xplugintest"
328 }
329 data_model: TABLE
330 projection {
331 name: "col1"
332 }
333 projection {
334 name: "col2"
335 }
336 row {
337 field {
338 type: LITERAL
339 literal {
340 type: V_SINT
341 v_signed_int: 18
342 }}
343 }
344 }
345 -->expecterror ER_X_BAD_INSERT_DATA
346 -->recvresult
347 -->sql
348 SELECT * FROM categories_child;
349 DROP TABLE categories_child;
350 -->endsql
351
352 #-- "Create DOCUMENT table "
353 -->stmtadmin create_collection {"schema":"xplugintest","name":"maincollection"}
354 -->recvresult
355
356 -->echo Insert multiple rows into a Document column
357 Mysqlx.Crud.Insert {
358 collection {
359 name: "maincollection"
360 schema: "xplugintest"
361 }
362 data_model: DOCUMENT
363 row {
364 field {
365 type: LITERAL
366 literal {
367 type: V_STRING
368 v_string {
369 value: "\n{\n \"_id\": \"1\",\n \"name\": \"Omar Bras\", \"id\": \"1\"\n}"
370 }
371 }
372 }}
373 row {
374 field {
375 type: LITERAL
376 literal {
377 type: V_STRING
378 v_string {
379 value: "\n{\n \"_id\": \"2\",\n \"name\": \"Omar Mex\", \"id\": \"2\"\n}"
380 }
381 }}
382 }
383 }
384 -->recvresult
385 -->sql
386 SELECT doc FROM maincollection;
387 -->endsql
388
389 -->echo "Non unique value for _id member of `doc` "
390 Mysqlx.Crud.Insert {
391 collection {
392 name: "maincollection"
393 schema: "xplugintest"
394 }
395 data_model: DOCUMENT
396 row {
397 field {
398 type: LITERAL
399 literal {
400 type: V_STRING
401 v_string {
402 value: "\n{\n \"_id\": \"3\",\n \"name\": \"Omar Bras\", \"id\": \"3\"\n}"
403 }
404 }}
405 }
406 row {
407 field {
408 type: LITERAL
409 literal {
410 type: V_STRING
411 v_string {
412 value: "\n{\n \"_id\": \"2\",\n \"name\": \"Omar Mex\", \"id\": \"4\"\n}"
413 }
414 }}
415 }
416 }
417 -->expecterror ER_X_DUPLICATE_ENTRY
418 -->recvresult
419 -->echo "Error expected as non unique value for _id provided for member of JSON doc "
420 -->sql
421 SELECT doc FROM maincollection;
422 -->endsql
423
424 -->echo "No _id member for JSON doc"
425 Mysqlx.Crud.Insert {
426 collection {
427 name: "maincollection"
428 schema: "xplugintest"
429 }
430 data_model: DOCUMENT
431 row {
432 field {
433 type: LITERAL
434 literal {
435 type: V_STRING
436 v_string {
437 value: "\n{\n \"name\": \"Omar Bras\", \"id\": \"3\"\n}"
438 }
439 }}
440 }
441 }
442 -->recvresult
443 -->echo "Error expected as _id member is not provided for JSON doc "
444 -->sql
445 SELECT doc FROM maincollection;
446 -->endsql
447
448 -->echo "projection is not allowed for DOCUMENT table"
449 Mysqlx.Crud.Insert {
450 collection {
451 name: "maincollection"
452 schema: "xplugintest"
453 }
454 data_model: DOCUMENT
455 projection {
456 name: "doc"
457 }
458
459 row {
460 field {
461 type: LITERAL
462 literal {
463 type: V_STRING
464 v_string {
465 value: "\n{\n \"name\": \"Omar Bras\", \"id\": \"3\"\n}"
466 }
467 }}
468 }
469 }
470 -->expecterror ER_X_BAD_PROJECTION
471 -->recvresult
472 -->echo "Error expected as projection given for DOCUMENT table "
473 -->sql
474 SELECT doc FROM maincollection;
475 -->endsql
476
477 -->echo "Error when inserting doc as null"
478 Mysqlx.Crud.Insert {
479 collection {
480 name: "maincollection"
481 schema: "xplugintest"
482 }
483 data_model: DOCUMENT
484 row {
485 field {
486 type: LITERAL
487 literal {
488 type: V_NULL
489 }}
490 }
491 }
492 -->expecterror ER_X_BAD_PROJECTION,ER_X_DOC_ID_MISSING
493 -->recvresult
494 -->sql
495 SELECT doc FROM maincollection;
496 -->endsql
497
498
499 -->echo "enter null value in doc member"
500 Mysqlx.Crud.Insert {
501 collection {
502 name: "maincollection"
503 schema: "xplugintest"
504 }
505 data_model: DOCUMENT
506 row {
507 field {
508 type: LITERAL
509 literal {
510 type: V_STRING
511 v_string {
512 value: "\n{\n \"_id\": \"4\",\n \"name\": \"Jackson\", \"id\": null\n}"
513 }
514 }}
515 }
516 }
517 -->recvresult
518 -->sql
519 SELECT doc FROM maincollection;
520 -->endsql
521
522 -->echo "Insert using transaction"
523 -->sql
524 START TRANSACTION;
525 -->endsql
526 Mysqlx.Crud.Insert {
527 collection {
528 name: "maincollection"
529 schema: "xplugintest"
530 }
531 data_model: DOCUMENT
532 row {
533 field {
534 type: LITERAL
535 literal {
536 type: V_STRING
537 v_string {
538 value: "\n{\n \"_id\": \"5\",\n \"name\": \"Jimmy \", \"id\": null\n}"
539 }
540 }}
541 }
542 }
543 -->recvresult
544 -->sql
545 SAVEPOINT A;
546 -->endsql
547 Mysqlx.Crud.Insert {
548 collection {
549 name: "maincollection"
550 schema: "xplugintest"
551 }
552 data_model: DOCUMENT
553 row {
554 field {
555 type: LITERAL
556 literal {
557 type: V_STRING
558 v_string {
559 value: "\n{\n \"_id\": \"6\",\n \"name\": \"Joe\", \"id\": null\n}"
560 }
561 }
562 }}
563 }
564 -->recvresult
565 -->sql
566 SAVEPOINT B;
567 -->endsql
568 Mysqlx.Crud.Insert {
569 collection {
570 name: "maincollection"
571 schema: "xplugintest"
572 }
573 data_model: DOCUMENT
574 row {
575 field {
576 type: LITERAL
577 literal {
578 type: V_STRING
579 v_string {
580 value: "\n{\n \"_id\": \"7\",\n \"name\": \"Jacky\", \"id\": null\n}"
581 }
582 }}
583 }
584 }
585 -->recvresult
586 -->sql
587 ROLLBACK TO SAVEPOINT A;
588 COMMIT;
589 -->endsql
590
591 -->sql
592 SELECT doc FROM maincollection;
593 -->endsql
594
595
596 #-- "Insert by acquiring lock"
597 -->sql
598 LOCK TABLES maincollection READ;
599 -->endsql
600 Mysqlx.Crud.Insert {
601 collection {
602 name: "maincollection"
603 schema: "xplugintest"
604 }
605 data_model: DOCUMENT
606 row {
607 field {
608 type: LITERAL
609 literal {
610 type: V_STRING
611 v_string {
612 value: "\n{\n \"_id\": \"7\",\n \"name\": \"Jacky\", \"id\": null\n}"
613 }
614 }}
615 }
616 }
617 -->expecterror ER_TABLE_NOT_LOCKED_FOR_WRITE
618 -->recvresult
619 -->echo "Error as table is locked with read lock"
620 -->sql
621 SELECT doc FROM maincollection;
622 -->endsql
623
624 -->sql
625 UNLOCK TABLES;
626 LOCK TABLES maincollection WRITE;
627 -->endsql
628 Mysqlx.Crud.Insert {
629 collection {
630 name: "maincollection"
631 schema: "xplugintest"
632 }
633 data_model: DOCUMENT
634 row {
635 field {
636 type: LITERAL
637 literal {
638 type: V_STRING
639 v_string {
640 value: "\n{\n \"_id\": \"7\",\n \"name\": \"Jacky\", \"id\": null\n}"
641 }
642 }}
643 }
644 }
645 -->recvresult
646 -->sql
647 SELECT doc FROM maincollection;
648 UNLOCK TABLES;
649 -->endsql
650
651 EOF
652
653 --replace_regex /([0-9a-f]{4})[0-9a-f]{8}([0-9a-f]{16})/\1XXXXXXXX\2/
654 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-insert_CRUD_1.xpl 2>&1
655 --remove_file $MYSQL_TMP_DIR/mysqlx-insert_CRUD_1.xpl
656
657
658
659 # "Error : Insert when user has only select access "
660 --write_file $MYSQL_TMP_DIR/mysqlx-insert_CRUD_1.xpl
661 -->sql
662 SET SESSION lock_wait_timeout= 3;
663 -->endsql
664 Mysqlx.Crud.Insert {
665 collection {
666 name: "categories"
667 schema: "xplugintest"
668 }
669 data_model: TABLE
670 projection {
671 name: "categoryname"
672 }
673 row {
674 field {
675 type: LITERAL
676 literal {
677 type: V_STRING
678 v_string {
679 value: "Mountain"
680 }
681 }}
682 }
683 }
684 -->expecterror ER_TABLEACCESS_DENIED_ERROR,ER_LOCK_WAIT_TIMEOUT
685 -->recvresult
686 -->expecterror ER_SUCCESS,ER_LOCK_WAIT_TIMEOUT
687 -->sql
688 SELECT * FROM xplugintest.categories;
689 -->endsql
690 EOF
691
692 --exec $MYSQLXTEST -u readuser --password='readuser' --file=$MYSQL_TMP_DIR/mysqlx-insert_CRUD_1.xpl 2>&1
693
694 # Acquire lock on categories table from mysqltest client
695 # and try to run insert from mysqlxtest (Error : timeout expected from mysqlxtest)
696 LOCK TABLES xplugintest.categories WRITE;
697 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-insert_CRUD_1.xpl 2>&1
698 --remove_file $MYSQL_TMP_DIR/mysqlx-insert_CRUD_1.xpl
699 UNLOCK TABLES;
700
701
702
703 # Try run insert from 2 mysqlxtest sessions
704 DELETE FROM xplugintest.maincollection;
705 perl;
706 my $dir = $ENV{'MYSQL_TMP_DIR'};
707 open ( OUTPUT, ">$dir/mysqlx-in.xpl") ;
708 $message = <<"END_MESSAGE";
709 Mysqlx.Crud.Insert {
710 collection {
711 name: "maincollection"
712 schema: "xplugintest"
713 }
714 data_model: DOCUMENT
715 row {
716 field {
717 type: LITERAL
718 literal {
719 type: V_STRING
720 v_string {
721 value: "insert_json_doc"
722 }
723 }}
724 }
725 }
726
727 END_MESSAGE
728
729 print OUTPUT "-->sql\n";
730 print OUTPUT "USE xplugintest;\n";
731 print OUTPUT "CREATE TABLE IF NOT EXISTS xplugintest.test_table ( col1 INT );\n";
732
733 print OUTPUT "-->endsql\n";
734 for ($i=1001;$i<=1030;$i++) {
735 if ($i%5 == 0) {
736 print OUTPUT "-->expecterror ER_SUCCESS,ER_DUP_ENTRY\n";
737 print OUTPUT "-->sql\n";
738 print OUTPUT "INSERT INTO maincollection(doc) VALUES ('".'{\"name\":\"name_'.$i.'\"'.' , \"_id\":'.$i."}') \n";
739 print OUTPUT "-->endsql\n";
740 }else {
741 $str = $message;
742 $replace = '{\"name\":\"name_'.$i.'\"'.' , \"_id\":'.$i.'}';
743 $find = "insert_json_doc";
744 $str =~ s/$find/$replace/g;
745 print OUTPUT "$str\n";
746 print OUTPUT "-->expecterror ER_SUCCESS,ER_X_DUPLICATE_ENTRY\n";
747 print OUTPUT "-->recvresult\n";
748 }
749 }
750 print OUTPUT "-->sql\n";
751 print OUTPUT "INSERT INTO xplugintest.test_table VALUES (1);\n";
752 print OUTPUT "-->endsql\n";
753 close (OUTPUT);
754 EOF
755 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl >$MYSQL_TMP_DIR/mysqlx-out.xpl 2>&1 &
756 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl >$MYSQL_TMP_DIR/mysqlx-out2.xpl 2>&1
757 let $wait_condition= select count(*)=2 from xplugintest.test_table;
758 --source include/wait_condition.inc
759 SELECT COUNT(*) FROM xplugintest.maincollection;
760
761 ## Cleanup
762 DROP SCHEMA if EXISTS xplugintest;
763 DROP USER readuser@localhost;
764 --source ../include/xplugin_cleanup.inc