1
set sql_mode=no_engine_substitution;
2
1
set storage_engine = InnoDB;
4
3
drop table if exists t1;
5
4
drop table if exists t2;
6
5
drop table if exists t3;
7
drop function if exists f2;
8
drop procedure if exists bug12713_call;
9
drop procedure if exists bug12713_dump_spvars;
10
drop procedure if exists dummy;
11
6
create table t1 (a int);
12
7
create table t2 (a int unique);
13
8
create table t3 (a int);
15
9
insert into t1 (a) values (1), (2);
16
10
insert into t3 (a) values (1), (2);
17
create function f2(x int) returns int
19
insert into t2 (a) values (x);
20
insert into t2 (a) values (x);
25
11
insert into t2 (a) values (1001);
26
insert into t1 (a) values (f2(1));
27
ERROR 23000: Duplicate entry '1' for key 'a'
12
insert into t1 (a) values (1);
34
20
insert into t2 (a) values (1002);
35
insert into t3 (a) select f2(2) from t1;
36
ERROR 23000: Duplicate entry '2' for key 'a'
43
30
insert into t2 (a) values (1003);
44
update t1 set a= a + f2(3);
45
ERROR 23000: Duplicate entry '3' for key 'a'
31
update t1 set a= a + 3;
52
43
insert into t2 (a) values (1004);
53
update t1, t3 set t1.a = 0, t3.a = 0 where (f2(4) = 4) and (t1.a = t3.a);
54
ERROR 23000: Duplicate entry '4' for key 'a'
44
update t1, t3 set t1.a = 0, t3.a = 0 where (4 = 4) and (t1.a = t3.a);
61
58
insert into t2 (a) values (1005);
62
delete from t1 where (a = f2(5));
63
ERROR 23000: Duplicate entry '5' for key 'a'
59
delete from t1 where (a = 5);
70
75
insert into t2 (a) values (1006);
71
delete from t1, t3 using t1, t3 where (f2(6) = 6) ;
72
ERROR 23000: Duplicate entry '6' for key 'a'
76
delete from t1, t3 using t1, t3 where (6 = 6) ;
79
94
insert into t2 (a) values (1007);
80
replace t1 values (f2(7));
81
ERROR 23000: Duplicate entry '7' for key 'a'
95
replace t1 values (7);
88
115
insert into t2 (a) values (1008);
89
replace into t3 (a) select f2(8) from t1;
90
ERROR 23000: Duplicate entry '8' for key 'a'
116
replace into t3 (a) select 8 from t1;
97
138
insert into t2 (a) values (1009);
98
select f2(9) from t1 ;
99
ERROR 23000: Duplicate entry '9' for key 'a'
100
142
select * from t2;
104
154
select * from t2;
106
165
insert into t2 (a) values (1010);
107
show databases where (f2(10) = 10);
108
ERROR 23000: Duplicate entry '10' for key 'a'
109
166
select * from t2;
113
179
select * from t2;
115
191
insert into t2 (a) values (1011);
116
show tables where (f2(11) = 11);
117
ERROR 23000: Duplicate entry '11' for key 'a'
124
insert into t2 (a) values (1012);
125
show triggers where (f2(12) = 12);
126
ERROR 23000: Duplicate entry '12' for key 'a'
133
232
insert into t2 (a) values (1013);
134
show table status where (f2(13) = 13);
135
ERROR 23000: Duplicate entry '13' for key 'a'
136
233
select * from t2;
140
248
select * from t2;
142
262
insert into t2 (a) values (1014);
143
show open tables where (f2(14) = 14);
144
ERROR 23000: Duplicate entry '14' for key 'a'
264
Database Table In_use Name_locked
145
268
select * from t2;
149
284
select * from t2;
151
299
insert into t2 (a) values (1015);
152
show columns in mysql.proc where (f2(15) = 15);
153
ERROR 23000: Duplicate entry '15' for key 'a'
154
300
select * from t2;
158
317
select * from t2;
160
333
insert into t2 (a) values (1016);
161
show status where (f2(16) = 16);
162
ERROR 23000: Duplicate entry '16' for key 'a'
163
334
select * from t2;
167
352
select * from t2;
169
369
insert into t2 (a) values (1017);
170
show variables where (f2(17) = 17);
171
ERROR 23000: Duplicate entry '17' for key 'a'
172
370
select * from t2;
176
389
select * from t2;
178
407
insert into t2 (a) values (1018);
179
show charset where (f2(18) = 18);
180
ERROR 23000: Duplicate entry '18' for key 'a'
181
408
select * from t2;
185
428
select * from t2;
187
447
insert into t2 (a) values (1019);
188
show collation where (f2(19) = 19);
189
ERROR 23000: Duplicate entry '19' for key 'a'
196
# We need at least one procedure to make sure the WHERE clause is
198
create procedure dummy() begin end;
199
insert into t2 (a) values (1020);
200
show procedure status where (f2(20) = 20);
201
ERROR 23000: Duplicate entry '20' for key 'a'
208
drop procedure dummy;
209
530
insert into t2 (a) values (1021);
210
show function status where (f2(21) = 21);
211
ERROR 23000: Duplicate entry '21' for key 'a'
212
531
select * from t2;
216
553
select * from t2;
218
574
insert into t2 (a) values (1022);
219
prepare stmt from "insert into t1 (a) values (f2(22))";
221
ERROR 23000: Duplicate entry '22' for key 'a'
222
575
select * from t2;
226
598
select * from t2;
228
620
insert into t2 (a) values (1023);
231
Error 1062 Duplicate entry '23' for key 'a'
238
create procedure bug12713_call ()
240
insert into t2 (a) values (24);
241
insert into t2 (a) values (24);
243
insert into t2 (a) values (1024);
244
call bug12713_call();
245
ERROR 23000: Duplicate entry '24' for key 'a'
253
668
=======================================================================
254
669
Testing select_to_file
255
670
=======================================================================
256
671
insert into t2 (a) values (1025);
257
select f2(25) into outfile "../tmp/dml.out" from t1;
258
ERROR 23000: Duplicate entry '25' for key 'a'
672
select 25 into outfile "../tmp/dml.out" from t1;
259
673
select * from t2;
263
698
select * from t2;
265
722
insert into t2 (a) values (1026);
266
load data infile "../std_data_ln/words.dat" into table t1 (a) set a:=f2(26);
267
ERROR 23000: Duplicate entry '26' for key 'a'
723
load data infile "../std_data_ln/words.dat" into table t1 (a) set a:=26;
724
ERROR HY000: Incorrect integer value: 'Aarhus' for column 'a' at row 1
268
725
select * from t2;
272
751
select * from t2;
274
776
=======================================================================
275
777
Testing select_dumpvar
276
778
=======================================================================
277
779
insert into t2 (a) values (1027);
278
select f2(27) into @foo;
279
ERROR 23000: Duplicate entry '27' for key 'a'
286
=======================================================================
287
Testing Select_fetch_into_spvars
288
=======================================================================
289
create procedure bug12713_dump_spvars ()
292
declare continue handler for sqlexception
294
select "Exception trapped";
296
select f2(28) into foo;
299
insert into t2 (a) values (1028);
300
call bug12713_dump_spvars ();
308
834
=======================================================================
310
836
=======================================================================
329
852
set autocommit=0;
330
853
drop table if exists t1;
331
854
drop table if exists t2;
332
drop function if exists f1;
333
drop procedure if exists p_verify_status_increment;
334
set @binlog_format=@@global.binlog_format;
335
set sql_mode=no_engine_substitution;
336
create table t1 (a int unique);
337
create table t2 (a int) engine=myisam;
338
set sql_mode=default;
340
# An auxiliary procedure to track Handler_prepare and Handler_commit
344
p_verify_status_increment(commit_inc_mixed int, prepare_inc_mixed int,
345
commit_inc_row int, prepare_inc_row int)
347
declare commit_inc int;
348
declare prepare_inc int;
349
declare old_commit_count int default ifnull(@commit_count, 0);
350
declare old_prepare_count int default ifnull(@prepare_count, 0);
352
# Use a cursor to have just one access to I_S instead of 2, it is very slow
353
# and amounts for over 90% of test CPU time
355
select variable_value
356
from information_schema.session_status
357
where variable_name='Handler_commit' or variable_name='Handler_prepare'
358
order by variable_name;
359
if @binlog_format = 'ROW' then
360
set commit_inc= commit_inc_row;
361
set prepare_inc= prepare_inc_row;
363
set commit_inc= commit_inc_mixed;
364
set prepare_inc= prepare_inc_mixed;
368
set @commit_count=c_res;
370
set @prepare_count=c_res;
372
if old_commit_count + commit_inc <> @commit_count then
373
select concat("Expected commit increment: ", commit_inc,
374
" actual: ", @commit_count - old_commit_count)
376
elseif old_prepare_count + prepare_inc <> @prepare_count then
377
select concat("Expected prepare increment: ", prepare_inc,
378
" actual: ", @prepare_count - old_prepare_count)
381
select '' as 'SUCCESS';
384
# Reset Handler_commit and Handler_prepare counters
387
# 1. Read-only statement: SELECT
391
call p_verify_status_increment(1, 0, 1, 0);
395
call p_verify_status_increment(1, 0, 1, 0);
398
# 2. Read-write statement: INSERT, insert 1 row.
400
insert into t1 (a) values (1);
401
call p_verify_status_increment(2, 2, 2, 2);
405
call p_verify_status_increment(2, 2, 2, 2);
408
# 3. Read-write statement: UPDATE, update 1 row.
411
call p_verify_status_increment(2, 2, 2, 2);
415
call p_verify_status_increment(2, 2, 2, 2);
418
# 4. Read-write statement: UPDATE, update 0 rows, 1 row matches WHERE
421
call p_verify_status_increment(2, 2, 1, 0);
425
call p_verify_status_increment(2, 2, 1, 0);
428
# 5. Read-write statement: UPDATE, update 0 rows, 0 rows match WHERE
430
# In mixed replication mode, there is a read-only transaction
431
# in InnoDB and also the statement is written to the binary log.
432
# So we have two commits but no 2pc, since the first engine's
433
# transaction is read-only.
434
# In the row level replication mode, we only have the read-only
435
# transaction in InnoDB and nothing is written to the binary log.
437
update t1 set a=3 where a=1;
438
call p_verify_status_increment(2, 0, 1, 0);
442
call p_verify_status_increment(2, 0, 1, 0);
445
# 6. Read-write statement: DELETE, delete 0 rows.
447
delete from t1 where a=1;
448
call p_verify_status_increment(2, 0, 1, 0);
452
call p_verify_status_increment(2, 0, 1, 0);
455
# 7. Read-write statement: DELETE, delete 1 row.
457
delete from t1 where a=2;
458
call p_verify_status_increment(2, 2, 2, 2);
462
call p_verify_status_increment(2, 2, 2, 2);
465
# 8. Read-write statement: unqualified DELETE
467
# In statement or mixed replication mode, we call
468
# handler::ha_delete_all_rows() and write statement text
469
# to the binary log. This results in two read-write transactions.
470
# In row level replication mode, we do not call
471
# handler::ha_delete_all_rows(), but delete rows one by one.
472
# Since there are no rows, nothing is written to the binary log.
473
# Thus we have just one read-only transaction in InnoDB.
475
call p_verify_status_increment(2, 2, 1, 0);
479
call p_verify_status_increment(2, 2, 1, 0);
482
# 9. Read-write statement: REPLACE, change 1 row.
485
call p_verify_status_increment(2, 2, 2, 2);
489
call p_verify_status_increment(2, 2, 2, 2);
492
# 10. Read-write statement: REPLACE, change 0 rows.
495
call p_verify_status_increment(2, 2, 1, 0);
499
call p_verify_status_increment(2, 2, 1, 0);
502
# 11. Read-write statement: IODKU, change 1 row.
504
insert t1 set a=1 on duplicate key update a=a+1;
505
call p_verify_status_increment(2, 2, 2, 2);
511
call p_verify_status_increment(1, 0, 1, 0);
515
call p_verify_status_increment(2, 2, 2, 2);
518
# 12. Read-write statement: IODKU, change 0 rows.
520
insert t1 set a=2 on duplicate key update a=2;
521
call p_verify_status_increment(1, 0, 1, 0);
525
call p_verify_status_increment(1, 0, 1, 0);
528
# 13. Read-write statement: INSERT IGNORE, change 0 rows.
530
insert ignore t1 set a=2;
531
call p_verify_status_increment(1, 0, 1, 0);
535
call p_verify_status_increment(1, 0, 1, 0);
538
# 14. Read-write statement: INSERT IGNORE, change 1 row.
540
insert ignore t1 set a=1;
541
call p_verify_status_increment(2, 2, 2, 2);
545
call p_verify_status_increment(2, 2, 2, 2);
548
# 15. Read-write statement: UPDATE IGNORE, change 0 rows.
550
update ignore t1 set a=2 where a=1;
551
call p_verify_status_increment(2, 2, 1, 0);
555
call p_verify_status_increment(2, 2, 1, 0);
559
# Create a stored function that modifies a
560
# non-transactional table. Demonstrate that changes in
561
# non-transactional tables do not affect the two phase commit
564
create function f1() returns int
569
call p_verify_status_increment(0, 0, 0, 0);
572
# 16. A function changes non-trans-table.
574
# For row-based logging, there is an extra commit for the
575
# non-transactional changes saved in the transaction cache to
581
call p_verify_status_increment(0, 0, 1, 0);
585
call p_verify_status_increment(0, 0, 1, 0);
588
# 17. Read-only statement, a function changes non-trans-table.
590
# For row-based logging, there is an extra commit for the
591
# non-transactional changes saved in the transaction cache to
598
call p_verify_status_increment(1, 0, 2, 0);
602
call p_verify_status_increment(1, 0, 2, 0);
605
# 18. Read-write statement: UPDATE, change 0 (transactional) rows.
607
select count(*) from t2;
610
update t1 set a=2 where a=f1()+10;
611
select count(*) from t2;
614
call p_verify_status_increment(2, 0, 2, 0);
618
call p_verify_status_increment(2, 0, 2, 0);
622
# Replace the non-transactional table with a temporary
623
# transactional table. Demonstrate that a change to a temporary
624
# transactional table does not provoke 2-phase commit, although
625
# does trigger a commit and a binlog write (in statement mode).
628
set sql_mode=no_engine_substitution;
629
create temporary table t2 (a int);
630
call p_verify_status_increment(0, 0, 0, 0);
633
set sql_mode=default;
634
# 19. A function changes temp-trans-table.
639
# Two commits because a binary log record is written
640
call p_verify_status_increment(2, 0, 1, 0);
644
call p_verify_status_increment(2, 0, 1, 0);
647
# 20. Read-only statement, a function changes non-trans-table.
653
# Two commits because a binary log record is written
654
call p_verify_status_increment(2, 0, 1, 0);
658
call p_verify_status_increment(2, 0, 1, 0);
661
# 21. Read-write statement: UPDATE, change 0 (transactional) rows.
663
update t1 set a=2 where a=f1()+10;
664
call p_verify_status_increment(2, 0, 1, 0);
668
call p_verify_status_increment(2, 0, 1, 0);
671
# 22. DDL: ALTER TEMPORARY TABLE, should not cause a 2pc
673
alter table t2 add column b int default 5;
674
# A commit is done internally by ALTER.
675
call p_verify_status_increment(2, 0, 2, 0);
679
# There is nothing left to commit
680
call p_verify_status_increment(0, 0, 0, 0);
683
# 23. DDL: RENAME TEMPORARY TABLE, does not start a transaction
685
# No test because of Bug#8729 "rename table fails on temporary table"
686
# 24. DDL: TRUNCATE TEMPORARY TABLE, does not start a transaction
689
call p_verify_status_increment(2, 0, 2, 0);
693
# There is nothing left to commit
694
call p_verify_status_increment(0, 0, 0, 0);
697
# 25. Read-write statement: unqualified DELETE
700
call p_verify_status_increment(2, 0, 1, 0);
704
# There is nothing left to commit
705
call p_verify_status_increment(2, 0, 1, 0);
708
# 25. DDL: DROP TEMPORARY TABLE, does not start a transaction
710
drop temporary table t2;
711
call p_verify_status_increment(0, 0, 0, 0);
715
call p_verify_status_increment(0, 0, 0, 0);
718
# 26. Verify that SET AUTOCOMMIT issues an implicit commit
721
call p_verify_status_increment(2, 2, 2, 2);
725
call p_verify_status_increment(2, 2, 2, 2);
729
select a from t1 where a=3;
732
call p_verify_status_increment(1, 0, 1, 0);
735
delete from t1 where a=3;
736
call p_verify_status_increment(2, 2, 2, 2);
740
call p_verify_status_increment(0, 0, 0, 0);
744
call p_verify_status_increment(0, 0, 0, 0);
748
call p_verify_status_increment(2, 2, 2, 2);
751
# Sic: not actually changing the value of autocommit
753
call p_verify_status_increment(0, 0, 0, 0);
757
select a from t1 where a=3;
759
call p_verify_status_increment(1, 0, 1, 0);
762
# 27. Savepoint management
765
call p_verify_status_increment(2, 2, 2, 2);
769
call p_verify_status_increment(0, 0, 0, 0);
773
# Sic: a bug. Binlog did not register itself this time.
774
call p_verify_status_increment(1, 0, 1, 0);
779
call p_verify_status_increment(0, 0, 0, 0);
782
select a from t1 where a=3;
784
call p_verify_status_increment(1, 0, 1, 0);
788
call p_verify_status_increment(1, 0, 1, 0);
791
# 28. Read-write statement: DO
793
create table t2 (a int);
794
call p_verify_status_increment(0, 0, 0, 0);
797
do (select f1() from t1 where a=2);
798
call p_verify_status_increment(2, 2, 2, 2);
802
call p_verify_status_increment(2, 2, 2, 2);
805
# 29. Read-write statement: MULTI-DELETE
807
delete t1, t2 from t1 join t2 on (t1.a=t2.a) where t1.a=2;
809
call p_verify_status_increment(4, 4, 4, 4);
812
# 30. Read-write statement: INSERT-SELECT, MULTI-UPDATE, REPLACE-SELECT
814
insert into t2 select a from t1;
816
replace into t2 select a from t1;
818
call p_verify_status_increment(8, 8, 8, 8);
821
update t1, t2 set t1.a=4, t2.a=8 where t1.a=t2.a and t1.a=1;
823
call p_verify_status_increment(4, 4, 4, 4);
826
# 31. DDL: various DDL with transactional tables
828
# Sic: no table is created.
829
create table if not exists t2 (a int) select 6 union select 7;
831
Note 1050 Table 't2' already exists
832
# Sic: first commits the statement, and then the transaction.
833
call p_verify_status_increment(4, 4, 4, 4);
836
create table t3 select a from t2;
837
call p_verify_status_increment(4, 4, 4, 4);
840
alter table t3 add column (b int);
841
call p_verify_status_increment(2, 0, 2, 0);
844
alter table t3 rename t4;
845
call p_verify_status_increment(1, 0, 1, 0);
848
rename table t4 to t3;
849
call p_verify_status_increment(1, 0, 1, 0);
853
call p_verify_status_increment(2, 2, 2, 2);
856
create view v1 as select * from t2;
857
call p_verify_status_increment(1, 0, 1, 0);
861
Table Op Msg_type Msg_text
862
test.t1 check status OK
863
call p_verify_status_increment(3, 0, 3, 0);
866
# Sic: after this bug is fixed, CHECK leaves no pending transaction
868
call p_verify_status_increment(0, 0, 0, 0);
871
check table t1, t2, t3;
872
Table Op Msg_type Msg_text
873
test.t1 check status OK
874
test.t2 check status OK
875
test.t3 check status OK
876
call p_verify_status_increment(6, 0, 6, 0);
880
call p_verify_status_increment(0, 0, 0, 0);
884
call p_verify_status_increment(0, 0, 0, 0);
890
drop table t1, t2, t3;
891
drop procedure p_verify_status_increment;
855
drop table if exists t3;
added
removed
tests/r/commit_1innodb.result
