1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
# t/innodb_mysql.test
#
# Last update:
# 2006-07-26 ML test refactored (MySQL 5.1)
# main testing code t/innodb_mysql.test -> include/mix1.inc
#
-- source include/have_innodb.inc
let $engine_type= InnoDB;
let $other_engine_type= MEMORY;
# InnoDB does support FOREIGN KEYFOREIGN KEYs
let $test_foreign_keys= 1;
set global innodb_support_xa=default;
set session innodb_support_xa=default;
--source include/mix1.inc
--disable_warnings
drop table if exists t1, t2, t3;
--enable_warnings
#
# BUG#35850: Performance regression in 5.1.23/5.1.24
#
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2 (a int, b int, pk int, key(a,b), primary key(pk)) engine=innodb;
insert into t2 select @a:=A.a+10*(B.a + 10*C.a),@a, @a from t1 A, t1 B, t1 C;
--echo this must use key 'a', not PRIMARY:
--replace_column 9 #
explain select a from t2 where a=b;
drop table t1, t2;
#
# Bug #40360: Binlog related errors with binlog off
#
# This bug is triggered when the binlog format is STATEMENT and the
# binary log is turned off. In this case, no error should be shown for
# the statement since there are no replication issues.
SET SESSION BINLOG_FORMAT=STATEMENT;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
query_vertical select @@session.sql_log_bin, @@session.binlog_format, @@session.tx_isolation;
CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
INSERT INTO t1 VALUES(1);
DROP TABLE t1;
#
# Bug#37284 Crash in Field_string::type()
#
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TABLE t1 (a char(50)) ENGINE=InnoDB;
CREATE INDEX i1 on t1 (a(3));
SELECT * FROM t1 WHERE a = 'abcde';
DROP TABLE t1;
#
# Bug #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of
# requested column
#
CREATE TABLE foo (a int, b int, c char(10),
PRIMARY KEY (c(3)),
KEY b (b)
) engine=innodb;
CREATE TABLE foo2 (a int, b int, c char(10),
PRIMARY KEY (c),
KEY b (b)
) engine=innodb;
CREATE TABLE bar (a int, b int, c char(10),
PRIMARY KEY (c(3)),
KEY b (b)
) engine=myisam;
INSERT INTO foo VALUES
(1,2,'abcdefghij'), (2,3,''), (3,4,'klmnopqrst'),
(4,5,'uvwxyz'), (5,6,'meotnsyglt'), (4,5,'asfdewe');
INSERT INTO bar SELECT * FROM foo;
INSERT INTO foo2 SELECT * FROM foo;
--query_vertical EXPLAIN SELECT c FROM bar WHERE b>2;
--query_vertical EXPLAIN SELECT c FROM foo WHERE b>2;
--query_vertical EXPLAIN SELECT c FROM foo2 WHERE b>2;
--query_vertical EXPLAIN SELECT c FROM bar WHERE c>2;
--query_vertical EXPLAIN SELECT c FROM foo WHERE c>2;
--query_vertical EXPLAIN SELECT c FROM foo2 WHERE c>2;
DROP TABLE foo, bar, foo2;
#
# Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites locking type of temp table
#
--disable_warnings
DROP TABLE IF EXISTS t1,t3,t2;
DROP FUNCTION IF EXISTS f1;
--enable_warnings
DELIMITER |;
CREATE FUNCTION f1() RETURNS VARCHAR(250)
BEGIN
return 'hhhhhhh' ;
END|
DELIMITER ;|
CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20), c VARCHAR(20)) ENGINE=INNODB;
BEGIN WORK;
CREATE TEMPORARY TABLE t2 (a VARCHAR(20), b VARCHAR(20), c varchar(20)) ENGINE=INNODB;
CREATE TEMPORARY TABLE t3 LIKE t2;
INSERT INTO t1 VALUES ('a','b',NULL),('c','d',NULL),('e','f',NULL);
SET @stmt := CONCAT('INSERT INTO t2 SELECT tbl.a, tbl.b, f1()',' FROM t1 tbl');
PREPARE stmt1 FROM @stmt;
SET @stmt := CONCAT('INSERT INTO t3', ' SELECT * FROM t2');
PREPARE stmt3 FROM @stmt;
EXECUTE stmt1;
COMMIT;
DEALLOCATE PREPARE stmt1;
DEALLOCATE PREPARE stmt3;
DROP TABLE t1,t3,t2;
DROP FUNCTION f1;
#
# Bug#37016: TRUNCATE TABLE removes some rows but not all
#
--disable_warnings
DROP TABLE IF EXISTS t1,t2;
--enable_warnings
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE t2 (id INT PRIMARY KEY,
t1_id INT, INDEX par_ind (t1_id),
FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB;
INSERT INTO t1 VALUES (1),(2);
INSERT INTO t2 VALUES (3,2);
SET AUTOCOMMIT = 0;
START TRANSACTION;
--error ER_ROW_IS_REFERENCED_2
TRUNCATE TABLE t1;
SELECT * FROM t1;
COMMIT;
SELECT * FROM t1;
START TRANSACTION;
--error ER_ROW_IS_REFERENCED_2
TRUNCATE TABLE t1;
SELECT * FROM t1;
ROLLBACK;
SELECT * FROM t1;
SET AUTOCOMMIT = 1;
START TRANSACTION;
SELECT * FROM t1;
COMMIT;
--error ER_ROW_IS_REFERENCED_2
TRUNCATE TABLE t1;
SELECT * FROM t1;
DELETE FROM t2 WHERE id = 3;
START TRANSACTION;
SELECT * FROM t1;
TRUNCATE TABLE t1;
ROLLBACK;
SELECT * FROM t1;
TRUNCATE TABLE t2;
DROP TABLE t2;
DROP TABLE t1;
--echo End of 5.1 tests