Debian Codegraph @

mysql-test/suite/innodb_fts/t/transaction.test

raw
1
2 # FTS index with transaction
3 # Important Note:
4 # 1) Uncommitted records are not visible using FTS index - this is by FTS design
5 # 2) records will be seen using FTS index ONLY when transaction completes
6 # 3) UNCOMMITTED RECORDS CAN BE SEEN WITH QURIES WHICH DO NOT USE FTS INDEX
7 # this behavior do not break integratity of tables and "select" which do not use FTS still can view them.
8
9
10 --disable_warnings
11 drop table if exists t1;
12 --enable_warnings
13
14 #------------------------------------------------------------------------------
15 # FTS with transaction
16 #------------------------------------------------------------------------------
17 # Create FTS table
18 EVAL CREATE TABLE t1 (
19 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
20 a VARCHAR(200),
21 b TEXT
22 ) ENGINE = InnoDB STATS_PERSISTENT=0;
23
24 # Create the FTS index again
25 CREATE FULLTEXT INDEX idx on t1 (a,b);
26
27
28 # Insert rows
29 INSERT INTO t1 (a,b) VALUES
30 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
31 ('when To Use MySQL Well','After that you went through a ...'),
32 ('where will Optimizing MySQL','what In this tutorial we will show ...');
33
34 START TRANSACTION;
35
36 # this record is NOT seen with queries using FTS index until commit
37 INSERT INTO t1 (a,b) VALUES
38 ('MySQL Tutorial','request docteam for oraclehelp.');
39
40 # first and third record expected
41 SELECT * FROM t1
42 WHERE MATCH (a,b)
43 AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
44
45 # no records expected
46 SELECT * FROM t1
47 WHERE MATCH (a,b)
48 AGAINST ('request' IN NATURAL LANGUAGE MODE);
49
50 # all records with MySQL expected but record with 'request' not
51 SELECT * FROM t1
52 WHERE MATCH (a,b)
53 AGAINST ('request MySQL' IN BOOLEAN MODE);
54
55 # all records with MySQL expected but record with 'request' not
56 SELECT * FROM t1
57 WHERE MATCH (a,b)
58 AGAINST ('-request +MySQL' IN BOOLEAN MODE);
59
60 # only 2nd record expected
61 SELECT * FROM t1
62 WHERE MATCH (a,b)
63 AGAINST ('+MySQL -(Tutorial Optimizing)' IN BOOLEAN MODE);
64
65 # one record expected
66 SELECT * FROM t1
67 WHERE MATCH (a,b)
68 AGAINST ('"stands Database"@11' IN BOOLEAN MODE) ORDER BY 1;
69
70 # all records expected except with 'request'
71 --sorted_result
72 SELECT * FROM t1
73 WHERE MATCH (a,b)
74 AGAINST ('tutorial' WITH QUERY EXPANSION);
75
76 # transaction commit ,now we will be able to see records with FTS index
77 COMMIT;
78
79 # records having tutorial word
80 SELECT * FROM t1
81 WHERE MATCH (a,b)
82 AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
83
84 # records having tutorial and MySQL word
85 --sorted_result
86 SELECT * FROM t1
87 WHERE MATCH (a,b)
88 AGAINST ('request MySQL' IN BOOLEAN MODE);
89
90 --sorted_result
91 SELECT * FROM t1
92 WHERE MATCH (a,b)
93 AGAINST ('tutorial' WITH QUERY EXPANSION);
94
95 START TRANSACTION;
96
97 # insert null values
98 INSERT INTO t1 (a,b) VALUES (NULL,NULL);
99
100 # one record expected
101 SELECT * FROM t1
102 WHERE MATCH (a,b)
103 AGAINST ('+request +MySQL' IN BOOLEAN MODE);
104
105 # update record which is visible with FTS index as transaction completed
106 UPDATE t1 SET a = 'Trial version' , b = 'query performace 1255 minute on 2.1Hz Memory 2GB...'
107 WHERE MATCH (a,b) AGAINST ('+request +MySQL' IN BOOLEAN MODE);
108
109 # no records expected as it is updated with new value
110 SELECT * FROM t1
111 WHERE MATCH (a,b)
112 AGAINST ('request');
113
114 # no records expected as update tnx is not committed yet.
115 SELECT * FROM t1
116 WHERE MATCH (a,b)
117 AGAINST ('query performace' IN BOOLEAN MODE);
118
119 # update will not able to find record as it uses FTS , so record will be updated
120 UPDATE t1 SET a = 'when To Use MySQL Well' , b = 'for free faq xyz.com ...'
121 WHERE MATCH (a,b) AGAINST ('+query +performace' IN BOOLEAN MODE);
122
123 # no records expected
124 SELECT * FROM t1
125 WHERE MATCH (a,b)
126 AGAINST ('performace');
127 # no records expected
128 SELECT * FROM t1
129 WHERE MATCH (a,b)
130 AGAINST ('+mail +MySQL' IN BOOLEAN MODE);
131 COMMIT;
132 DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('+MySQL' IN BOOLEAN MODE);
133 SELECT * FROM t1;
134
135 # insert record with @ character which is used in proximity search
136 INSERT INTO t1 (a,b) VALUES
137 ('Trial version','query performace 1255 minute on 2.1Hz Memory 2GB...') ,
138 ('when To Use MySQL Well','for free faq [email protected] ...');
139
140 SELECT * FROM t1;
141 DROP TABLE t1;
142
143
144 #------------------------------------------------------------------------------
145 # FTS with rollback
146 #------------------------------------------------------------------------------
147 # Create FTS table
148 EVAL CREATE TABLE t1 (
149 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
150 a VARCHAR(200),
151 b TEXT
152 ) ENGINE = InnoDB;
153
154 # Create the FTS index again
155 CREATE FULLTEXT INDEX idx on t1 (a,b);
156
157
158 # Insert rows
159 INSERT INTO t1 (a,b) VALUES
160 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
161 ('when To Use MySQL Well','After that you went through a ...'),
162 ('where will Optimizing MySQL','what In this tutorial we will show ...');
163
164 START TRANSACTION;
165
166 # insert record but it won't be visible using FTS index until tnx completes.
167 INSERT INTO t1 (a,b) VALUES
168 ('MySQL Tutorial','request docteam for oraclehelp.');
169 # two records expected
170 SELECT * FROM t1
171 WHERE MATCH (a,b)
172 AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
173 # no records expected
174 SELECT * FROM t1
175 WHERE MATCH (a,b)
176 AGAINST ('request' IN NATURAL LANGUAGE MODE);
177 # only records with MySQL expected as record with 'request' word not committed yet.
178 SELECT * FROM t1
179 WHERE MATCH (a,b)
180 AGAINST ('request MySQL' IN BOOLEAN MODE);
181
182 # records with MySQL word
183 SELECT * FROM t1
184 WHERE MATCH (a,b)
185 AGAINST ('-request +MySQL' IN BOOLEAN MODE);
186
187 SELECT * FROM t1
188 WHERE MATCH (a,b)
189 AGAINST ('+MySQL -(Tutorial Optimizing)' IN BOOLEAN MODE);
190
191 # query expansion mode
192 --sorted_result
193 SELECT * FROM t1
194 WHERE MATCH (a,b)
195 AGAINST ('tutorial' WITH QUERY EXPANSION);
196
197 # query expansion mode
198 --sorted_result
199 SELECT * FROM t1
200 WHERE MATCH (a,b)
201 AGAINST ('tutorial' WITH QUERY EXPANSION);
202
203 # rollback transaction , record with 'request' word rollbacked.
204 ROLLBACK;
205
206 SELECT * FROM t1
207 WHERE MATCH (a,b)
208 AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
209
210 SELECT * FROM t1
211 WHERE MATCH (a,b)
212 AGAINST ('request MySQL' IN BOOLEAN MODE);
213
214 --sorted_result
215 SELECT * FROM t1
216 WHERE MATCH (a,b)
217 AGAINST ('tutorial' WITH QUERY EXPANSION);
218
219 START TRANSACTION;
220 INSERT INTO t1 (a,b) VALUES (NULL,NULL);
221
222 # no update as record with where condition is not present
223 UPDATE t1 SET a = 'Trial version' , b = 'query performace 1255 minute on 2.1Hz Memory 2GB...'
224 WHERE MATCH (a,b) AGAINST ('+request +MySQL' IN BOOLEAN MODE);
225
226 # no records
227 SELECT * FROM t1
228 WHERE MATCH (a,b)
229 AGAINST ('request');
230
231 # no records
232 SELECT * FROM t1
233 WHERE MATCH (a,b)
234 AGAINST ('query performace' IN BOOLEAN MODE);
235
236 # no update as record with where condition is not present
237 UPDATE t1 SET a = 'when To Use MySQL Well' , b = 'for free faq xyz.com ...'
238 WHERE MATCH (a,b) AGAINST ('+query +performace' IN BOOLEAN MODE);
239
240 # no records
241 SELECT * FROM t1
242 WHERE MATCH (a,b)
243 AGAINST ('performace');
244
245 # no records
246 SELECT * FROM t1
247 WHERE MATCH (a,b)
248 AGAINST ('+mail +MySQL' IN BOOLEAN MODE);
249 ROLLBACK;
250 DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('+MySQL' IN BOOLEAN MODE);
251 SELECT * FROM t1;
252
253 # insert record with @ character which is used in proximity search
254 INSERT INTO t1 (a,b) VALUES
255 ('Trial version','query performace 1255 minute on 2.1Hz Memory 2GB...') ,
256 ('when To Use MySQL Well','for free faq [email protected] ...');
257
258 SELECT * FROM t1;
259 DROP TABLE t1;
260
261 #------------------------------------------------------------------------------
262 # FTS with transaction -
263 # Uncommitted records are visible using FTS index so try use normal query to
264 # update such records in active transaction
265 #------------------------------------------------------------------------------
266 # Create FTS table
267 EVAL CREATE TABLE t1 (
268 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
269 a VARCHAR(200),
270 b TEXT
271 ) ENGINE = InnoDB;
272
273 # Create the FTS index again
274 CREATE FULLTEXT INDEX idx on t1 (a,b);
275
276 # Insert rows
277 INSERT INTO t1 (a,b) VALUES
278 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
279 ('when To Use MySQL Well','After that you went through a ...'),
280 ('where will Optimizing MySQL','what In this tutorial we will show ...');
281
282 INSERT INTO t1 (a,b) VALUES
283 ('MySQL Tutorial','request docteam for oraclehelp.');
284
285 START TRANSACTION;
286 INSERT INTO t1 (a,b) VALUES (NULL,NULL);
287
288 # only one record expected
289 SELECT * FROM t1
290 WHERE MATCH (a,b)
291 AGAINST ('+request +MySQL' IN BOOLEAN MODE);
292
293 # update record
294 UPDATE t1 SET a = 'Trial version' , b = 'query performace 1255 minute on 2.1Hz Memory 2GB...' WHERE MATCH (a,b) AGAINST ('+request +MySQL' IN BOOLEAN MODE);
295
296 # updated record is visible as we do not use FTS index in following query
297 SELECT * from t1;
298
299 # no records expected as it is updated with new value
300 SELECT * FROM t1
301 WHERE MATCH (a,b)
302 AGAINST ('request');
303
304 # no record expected as transaction is not committed.
305 SELECT * FROM t1
306 WHERE MATCH (a,b)
307 AGAINST ('query performace' IN BOOLEAN MODE);
308
309 # following update will not succeed as it uses FTS index in where clause and the record matching condition is not committe yet.
310 UPDATE t1 SET a = 'when To Use MySQL Well' , b = 'for free faq mail xyz.com ...' WHERE MATCH (a,b) AGAINST ('+query +performace' IN BOOLEAN MODE);
311 # update will succeed as it uses non FTS condition in where clause. Record in where clause is visible as it is accessed by NON FTS condition.
312 UPDATE t1 SET a = 'when To Use MySQL Well' , b = 'for free faq mail xyz.com ...' WHERE b like '%query performace%';
313
314 # no record expected as it is updated wih new value
315 SELECT * FROM t1
316 WHERE MATCH (a,b)
317 AGAINST ('performace');
318
319 # not visible with FTS as transaction not committed.
320 SELECT * FROM t1
321 WHERE MATCH (a,b)
322 AGAINST ('+mail +MySQL' IN BOOLEAN MODE);
323
324 SELECT * from t1;
325 COMMIT;
326 SELECT * from t1;
327
328 DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('+MySQL' IN BOOLEAN MODE);
329 SELECT * FROM t1;
330
331 # insert record with @ character which is used in proximity search
332 INSERT INTO t1 (a,b) VALUES
333 ('Trial version','query performace 1255 minute on 2.1Hz Memory 2GB...') ,
334 ('when To Use MySQL Well','for free faq [email protected] ...');
335
336 SELECT * FROM t1;
337 DROP TABLE t1;
338
339 #------------------------------------------------------------------------------
340 # FTS with transaction - multiple connections
341 #------------------------------------------------------------------------------
342
343 #set names utf8;
344 SET NAMES UTF8;
345
346 # Create FTS table
347 EVAL CREATE TABLE t1 (
348 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
349 a VARCHAR(200),
350 b TEXT
351 ) CHARACTER SET = UTF8,ENGINE = InnoDB;
352
353
354 --echo "In connection 1"
355 --connect (con1,localhost,root,,)
356 SET NAMES UTF8;
357
358 # Insert rows
359 INSERT INTO t1 (a,b) VALUES
360 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
361 ('when To Use MySQL Well','After that you went through a ...'),
362 ('where will Optimizing MySQL','what In this tutorial we will show ...'),
363 ('Я могу есть стекло', 'оно мне Mне вредит'),
364 ('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
365 ('Sævör grét', 'áðan því úlpan var ónýt');
366
367
368 --echo "In connection 2"
369 --connect (con2,localhost,root,,)
370 SET NAMES UTF8;
371
372 # Create the FTS index again
373 CREATE FULLTEXT INDEX idx on t1 (a,b);
374
375
376 --echo "In connection 1"
377 --connection con1
378
379 # first and third record expected
380 SELECT * FROM t1
381 WHERE MATCH (a,b)
382 AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
383
384 INSERT INTO t1 (a,b) VALUES
385 ('adding record using session 1','for verifying multiple concurrent transactions'),
386 ('Мога да ям стъкло', 'то Mне ми вреди');
387
388 # one records expected
389 SELECT * FROM t1
390 WHERE MATCH (a,b)
391 AGAINST ('+γυαλιὰ +χωρὶ*' IN BOOLEAN MODE);
392
393 START TRANSACTION;
394
395 # this record is NOT seen with queries using FTS index until commit
396 INSERT INTO t1 (a,b) VALUES
397 ('MySQL Tutorial','request docteam for oraclehelp.'),
398 ('Příliš žluťoučký kůň', 'úpěl ďábelské kódy');
399
400 # no records expected as its not committed yet
401 SELECT * FROM t1
402 WHERE MATCH (a,b)
403 AGAINST ('request ďábelské' IN BOOLEAN MODE);
404
405 # 3 record expected
406 SELECT * FROM t1
407 WHERE MATCH (a,b)
408 AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY 1;
409
410 # one record expected proximity
411 SELECT * FROM t1
412 WHERE MATCH (a,b)
413 AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE);
414
415
416 --echo "In connection 2"
417 --connection con2
418
419 # one records expected
420 SELECT * FROM t1
421 WHERE MATCH (a,b)
422 AGAINST ('+record +multiple' IN BOOLEAN MODE);
423
424 # 3 rcords expected , two for tutor* and one for Sævö*
425 SELECT * FROM t1
426 WHERE MATCH (a,b)
427 AGAINST ('tutor* Sævö*' IN BOOLEAN MODE) ORDER BY 1;
428
429
430 # one record expected
431 SELECT * FROM t1
432 WHERE MATCH (a,b)
433 AGAINST ('usin* multipl*' IN BOOLEAN MODE);
434
435 # no record expected as this record is not committed
436 SELECT * FROM t1
437 WHERE MATCH (a,b)
438 AGAINST ('"request docteam"@08' IN BOOLEAN MODE);
439
440 # no records expected as this record is not committed
441 SELECT * FROM t1
442 WHERE MATCH (a,b)
443 AGAINST ('request' IN NATURAL LANGUAGE MODE);
444
445 # all records with MySQL expected but record with 'request' is not committed so 'request' string not visible
446 SELECT * FROM t1
447 WHERE MATCH (a,b)
448 AGAINST ('request MySQL' IN BOOLEAN MODE);
449
450 # all records with MySQL expected but record with 'request' not
451 SELECT * FROM t1
452 WHERE MATCH (a,b)
453 AGAINST ('-request +MySQL' IN BOOLEAN MODE);
454
455 # only one record expected
456 SELECT * FROM t1
457 WHERE MATCH (a,b)
458 AGAINST ('+MySQL -(Tutorial Optimizing)' IN BOOLEAN MODE);
459
460 # one record expected
461 SELECT * FROM t1
462 WHERE MATCH (a,b)
463 AGAINST ('"stands Database"@11' IN BOOLEAN MODE) ORDER BY 1;
464
465 # all records expected except with 'request'
466 SELECT * FROM t1
467 WHERE MATCH (a,b)
468 AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY 1;
469
470 --echo "In connection 1"
471 --connection con1
472 # transaction commit ,now we will be able to see records with FTS index
473 COMMIT;
474 # records with MySQL,request string expected
475 SELECT * FROM t1
476 WHERE MATCH (a,b)
477 AGAINST ('+request +MySQL' IN BOOLEAN MODE);
478
479 SELECT * FROM t1
480 WHERE MATCH (a,b)
481 AGAINST ('Příliš žluťoučký' IN BOOLEAN MODE);
482
483 SELECT * FROM t1
484 WHERE MATCH (a,b)
485 AGAINST ('Příliš');
486
487 # insert null values
488 INSERT INTO t1 (a,b) VALUES (NULL,NULL);
489 SELECT * FROM t1 WHERE a IS NULL AND b IS NULL;
490
491 --echo "In connection 2"
492 --connection con2
493
494 # one record expected
495 SELECT * FROM t1
496 WHERE MATCH (a,b)
497 AGAINST ('usin* multipl*' IN BOOLEAN MODE);
498
499 # one record expected , b/c record is committed in connection 1
500 SELECT * FROM t1
501 WHERE MATCH (a,b)
502 AGAINST ('+request +MySQL' IN BOOLEAN MODE);
503
504 SELECT * FROM t1 WHERE a IS NULL AND b IS NULL;
505
506
507
508 ALTER TABLE t1 DROP INDEX idx;
509 # Create the FTS index again
510 CREATE FULLTEXT INDEX idx on t1 (a,b);
511
512 START TRANSACTION;
513 # update record but do not commiit so latest upadted string not visible using FTS index but available with normal select
514 UPDATE t1 SET a = 'Trial version Příliš žluťoučký' , b = 'query performace 1255 minute on 2.1Hz Memory 2GB...'
515 WHERE MATCH (a,b) AGAINST ('+request +MySQL' IN BOOLEAN MODE);
516
517 # no records expected as it is updated with new value
518 SELECT * FROM t1
519 WHERE MATCH (a,b)
520 AGAINST ('request');
521
522 # no records expected as it is not committed
523 SELECT * FROM t1
524 WHERE MATCH (a,b)
525 AGAINST ('+query +performace' IN BOOLEAN MODE);
526
527 # only one record will be updated as other is not committed hence not seen using FTS
528 UPDATE t1 SET a = UPPER(a) WHERE MATCH (a,b) AGAINST ('+Příliš +žluťoučký' IN BOOLEAN MODE);
529
530 # no records expected as it is not committed
531 SELECT * FROM t1
532 WHERE MATCH (a,b)
533 AGAINST ('+Příliš +žluťoučký' IN BOOLEAN MODE);
534
535 UPDATE t1 SET a = UPPER(a) WHERE a LIKE '%version Příliš%';
536
537 # no records expected as it is not committed
538 SELECT * FROM t1
539 WHERE MATCH (a,b)
540 AGAINST ('+query +performace' IN BOOLEAN MODE);
541
542 SELECT * FROM t1 WHERE b LIKE '%query performace%';
543
544 # no records expected as it is updated with new value
545 SELECT * FROM t1
546 WHERE MATCH (a,b)
547 AGAINST ('request');
548
549 --echo "In connection 1"
550 --connection con1
551
552 # no records expected as update tnx is not committed yet.
553 SELECT * FROM t1
554 WHERE MATCH (a,b)
555 AGAINST ('query performace' IN BOOLEAN MODE);
556
557 # no records expected
558 SELECT * FROM t1
559 WHERE MATCH (a,b)
560 AGAINST ('performace');
561 # no records expected
562 SELECT * FROM t1
563 WHERE MATCH (a,b)
564 AGAINST ('+mail +MySQL' IN BOOLEAN MODE);
565
566 --echo "In connection 2"
567 --connection con2
568 COMMIT;
569 SELECT * FROM t1
570 WHERE MATCH (a,b)
571 AGAINST ('+query +performace' IN BOOLEAN MODE);
572
573
574
575 --echo "In connection 1"
576 --connection con1
577 SELECT * FROM t1
578 WHERE MATCH (a,b)
579 AGAINST ('+query +performace' IN BOOLEAN MODE);
580 # Update
581 UPDATE t1 SET a = 'when To Use MySQL Well' , b = 'for free faq xyz.com ...'
582 WHERE MATCH (a,b) AGAINST ('+πάθω +τίποτα' IN BOOLEAN MODE);
583 # one record expected
584 SELECT * FROM t1
585 WHERE MATCH (a,b)
586 AGAINST ('well free') ORDER BY 1;
587
588 --disconnect con1
589 --disconnect con2
590 --source include/wait_until_disconnected.inc
591
592 --connection default
593 DROP TABLE t1;
594
595
596 #------------------------------------------------------------------------------
597 # FTS with delete transaction with multiple session
598 #------------------------------------------------------------------------------
599
600 SET NAMES UTF8;
601
602 # Create FTS table
603 EVAL CREATE TABLE t1 (
604 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
605 a VARCHAR(200),
606 b TEXT
607 ) CHARACTER SET = UTF8,ENGINE = InnoDB;
608
609
610 --echo "In connection 1"
611 --connect (con1,localhost,root,,)
612 SET NAMES UTF8;
613
614 # Insert rows
615 INSERT INTO t1 (a,b) VALUES
616 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
617 ('when To Use MySQL Well','After that you went through a ...'),
618 ('where will Optimizing MySQL','what In this tutorial we will show ...'),
619 ('Я могу есть стекло', 'оно мне Mне вредит'),
620 ('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
621 ('Sævör grét', 'áðan því úlpan var ónýt');
622
623
624 --echo "In connection 2"
625 --connect (con2,localhost,root,,)
626 SET NAMES UTF8;
627 select @@session.transaction_isolation;
628
629 # Create the FTS index again
630 CREATE FULLTEXT INDEX idx on t1 (a,b);
631 INSERT INTO t1 (a,b) VALUES
632 ('adding record using session 1','for verifying multiple concurrent transactions'),
633 ('Мога да ям стъкло', 'то Mне ми вреди');
634
635
636 --echo "In connection 1"
637 --connection con1
638
639
640 SELECT * FROM t1;
641
642 START TRANSACTION;
643 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Мога +Mне' IN BOOLEAN MODE);
644 # Delete rows
645 DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('+Мога +Mне' IN BOOLEAN MODE);
646 DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE);
647
648 SELECT * FROM t1;
649
650 # 1 record expected
651 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
652 # 1 record expected
653 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стекло');
654 # No record expected
655 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE);
656
657 SELECT * FROM t1;
658
659 --echo "In connection 2"
660 --connection con2
661 # records expected due to repeatable read
662 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
663 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стекло');
664 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE);
665
666
667 SELECT * FROM t1;
668
669 --echo "In connection 1"
670 --connection con1
671 COMMIT;
672 # 1 record expected
673 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
674 # 1 record expected
675 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стекло');
676 # No record expected
677 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE);
678
679 --echo "In connection 2"
680 --connection con2
681 # 1 record expected
682 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
683 # 1 record expected
684 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стекло');
685 # No record expected
686 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE);
687
688 SELECT * FROM t1;
689
690
691 --disconnect con1
692 --disconnect con2
693 --source include/wait_until_disconnected.inc
694
695 --connection default
696 DROP TABLE t1;
697
698
699
700 #------------------------------------------------------------------------------
701 # FTS with update transaction with multiple session
702 #------------------------------------------------------------------------------
703
704 SET NAMES UTF8;
705
706 # Create FTS table
707 EVAL CREATE TABLE t1 (
708 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
709 a VARCHAR(200),
710 b TEXT
711 ) CHARACTER SET = UTF8,ENGINE = InnoDB;
712
713
714 --echo "In connection 1"
715 --connect (con1,localhost,root,,)
716 SET NAMES UTF8;
717
718 # Insert rows
719 INSERT INTO t1 (a,b) VALUES
720 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
721 ('when To Use MySQL Well','After that you went through a ...'),
722 ('where will Optimizing MySQL','what In this tutorial we will show ...'),
723 ('Я могу есть стекло', 'оно мне Mне вредит');
724
725
726 --echo "In connection 2"
727 --connect (con2,localhost,root,,)
728 SET NAMES UTF8;
729 select @@session.transaction_isolation;
730
731 # Create the FTS index again
732 CREATE FULLTEXT INDEX idx on t1 (a,b);
733 INSERT INTO t1 (a,b) VALUES
734 ('adding record using session 1','for verifying multiple concurrent transactions'),
735 ('Мога да ям стъкло', 'то Mне ми вреди');
736
737
738 --echo "In connection 1"
739 --connection con1
740
741
742 SELECT * FROM t1;
743
744 START TRANSACTION;
745 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Мога +Mне' IN BOOLEAN MODE);
746 # Update row
747 UPDATE t1 SET a = 'Μπορῶ νὰ φάω σπασμένα' , b = 'γυαλιὰ χωρὶς νὰ πάθω τίποτα' WHERE MATCH (a,b) AGAINST ('+могу +Mне' IN BOOLEAN MODE);
748 # Insert row
749 INSERT INTO t1(a,b) VALUES ('Sævör grét', 'áðan því úlpan var ónýt');
750
751 SELECT * FROM t1;
752
753 # 1 record expected - records not seen
754 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
755 # 1 record expected - records not seen
756 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стекло');
757 # 1 record expected
758 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE);
759
760 SELECT * FROM t1;
761
762 --echo "In connection 2"
763 --connection con2
764 # 2 records expected
765 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
766 # 1 records expected
767 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стекло');
768 # 1 records expected
769 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE);
770
771 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
772
773 # 1 records expected
774 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
775 # no records expected
776 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стекло');
777 # 1 records expected
778 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE);
779
780 SELECT * FROM t1;
781
782 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
783
784 # 2 records expected
785 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
786 # 1 records expected
787 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стекло');
788 # 1 records expected
789 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE);
790
791 SELECT * FROM t1;
792
793 --echo "In connection 1"
794 --connection con1
795 COMMIT;
796 # 1 record expected
797 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
798 # no record expected
799 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стекло');
800 # 1 record expected
801 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE);
802
803 --echo "In connection 2"
804 --connection con2
805 # 1 record expected
806 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
807 # Innodb:error no record expected
808 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стекло');
809 # 1 record expected
810 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE);
811
812 SELECT * FROM t1;
813
814
815 --disconnect con1
816 --disconnect con2
817 --source include/wait_until_disconnected.inc
818
819 --connection default
820 DROP TABLE t1;
821
822
823 #------------------------------------------------------------------------------
824 # FTS with delete/rollback transaction with multiple session
825 #------------------------------------------------------------------------------
826
827 SET NAMES UTF8;
828
829 # Create FTS table
830 EVAL CREATE TABLE t1 (
831 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
832 a VARCHAR(200),
833 b TEXT
834 ) CHARACTER SET = UTF8,ENGINE = InnoDB;
835
836
837 --echo "In connection 1"
838 --connect (con1,localhost,root,,)
839 SET NAMES UTF8;
840
841 # Insert rows
842 INSERT INTO t1 (a,b) VALUES
843 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
844 ('when To Use MySQL Well','After that you went through a ...'),
845 ('where will Optimizing MySQL','what In this tutorial we will show ...'),
846 ('Я могу есть стекло', 'оно мне Mне вредит'),
847 ('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
848 ('Sævör grét', 'áðan því úlpan var ónýt');
849
850
851 --echo "In connection 2"
852 --connect (con2,localhost,root,,)
853 SET NAMES UTF8;
854 select @@session.transaction_isolation;
855
856 # Create the FTS index again
857 CREATE FULLTEXT INDEX idx on t1 (a,b);
858 INSERT INTO t1 (a,b) VALUES
859 ('adding record using session 1','for verifying multiple concurrent transactions'),
860 ('Мога да ям стъкло', 'то Mне ми вреди');
861
862
863 --echo "In connection 1"
864 --connection con1
865
866
867 SELECT * FROM t1;
868
869 START TRANSACTION;
870 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Мога +Mне' IN BOOLEAN MODE);
871 # Delete rows
872 DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('+Мога +Mне' IN BOOLEAN MODE);
873 DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE);
874
875 SELECT * FROM t1;
876
877 # 1 record expected
878 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
879 # No record expected
880 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стъкло');
881 # No record expected
882 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE);
883
884 SELECT * FROM t1;
885
886 --echo "In connection 2"
887 --connection con2
888 # records expected due to repeatable read
889 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
890 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стъкло');
891 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE);
892
893
894 SELECT * FROM t1;
895
896 --echo "In connection 1"
897 --connection con1
898 ROLLBACK;
899 # 2 record expected
900 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
901 # 1 record expected
902 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стъкло');
903 # 1 record expected
904 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE);
905
906 --echo "In connection 2"
907 --connection con2
908 # 2 record expected
909 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
910 # 1 record expected
911 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стъкло');
912 # 1 record expected
913 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE);
914
915 SELECT * FROM t1;
916
917
918 --disconnect con1
919 --disconnect con2
920 --source include/wait_until_disconnected.inc
921
922 --connection default
923 DROP TABLE t1;
924
925
926
927 #------------------------------------------------------------------------------
928 # FTS with update/rollback transaction with multiple session
929 #------------------------------------------------------------------------------
930
931 SET NAMES UTF8;
932
933 # Create FTS table
934 EVAL CREATE TABLE t1 (
935 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
936 a VARCHAR(200),
937 b TEXT
938 ) CHARACTER SET = UTF8,ENGINE = InnoDB;
939
940
941 --echo "In connection 1"
942 --connect (con1,localhost,root,,)
943 SET NAMES UTF8;
944
945 # Insert rows
946 INSERT INTO t1 (a,b) VALUES
947 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
948 ('when To Use MySQL Well','After that you went through a ...'),
949 ('where will Optimizing MySQL','what In this tutorial we will show ...'),
950 ('Я могу есть стекло', 'оно мне Mне вредит');
951
952
953 --echo "In connection 2"
954 --connect (con2,localhost,root,,)
955 SET NAMES UTF8;
956 select @@session.transaction_isolation;
957
958 # Create the FTS index again
959 CREATE FULLTEXT INDEX idx on t1 (a,b);
960 INSERT INTO t1 (a,b) VALUES
961 ('adding record using session 1','for verifying multiple concurrent transactions'),
962 ('Мога да ям стъкло', 'то Mне ми вреди');
963
964
965 --echo "In connection 1"
966 --connection con1
967
968
969 SELECT * FROM t1;
970
971 START TRANSACTION;
972 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+могу +Mне' IN BOOLEAN MODE);
973 # Update row
974 UPDATE t1 SET a = 'Μπορῶ νὰ φάω σπασμένα' , b = 'γυαλιὰ χωρὶς νὰ πάθω τίποτα' WHERE MATCH (a,b) AGAINST ('+могу +Mне' IN BOOLEAN MODE);
975 # Insert row
976 INSERT INTO t1(a,b) VALUES ('Sævör grét', 'áðan því úlpan var ónýt');
977
978 SELECT * FROM t1;
979
980 # 1 record expected - records not seen
981 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
982 # 1 record expected
983 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стъкло');
984 # 1 record expected
985 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE);
986
987 SELECT * FROM t1;
988
989 --echo "In connection 2"
990 --connection con2
991 # 2 records expected
992 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
993 # 1 records expected
994 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стъкло');
995 # 1 records expected
996 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE);
997
998 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
999
1000 # 1 records expected
1001 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
1002 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стъкло');
1003 # 1 records expected
1004 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE);
1005
1006 SELECT * FROM t1;
1007
1008 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1009
1010 # 2 records expected
1011 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
1012 # 1 records expected
1013 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стъкло');
1014 # 1 records expected
1015 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE);
1016
1017 SELECT * FROM t1;
1018
1019 --echo "In connection 1"
1020 --connection con1
1021 ROLLBACK;
1022 # 2 record expected
1023 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
1024 # 1 record expected
1025 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стъкло');
1026 # no record expected
1027 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE);
1028
1029 --echo "In connection 2"
1030 --connection con2
1031 # 2 record expected
1032 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
1033 # 1 record expected
1034 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стъкло');
1035 # no record expected
1036 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE);
1037
1038 SELECT * FROM t1;
1039
1040
1041 --disconnect con1
1042 --disconnect con2
1043 --source include/wait_until_disconnected.inc
1044
1045
1046 --connection default
1047 DROP TABLE t1;