Debian Codegraph @

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

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