~drizzle-trunk/drizzle/development

1 by brian
clean slate
1
#######################################################################
2
#                                                                     #
3
# Please, DO NOT TOUCH this file as well as the innodb.result file.   #
4
# These files are to be modified ONLY BY INNOBASE guys.               #
5
#                                                                     #
6
# Use innodb_mysql.[test|result] files instead.                       #
7
#                                                                     #
8
# If nevertheless you need to make some changes here, please, forward #
9
# your commit message To: dev@innodb.com Cc: dev-innodb@mysql.com     #
10
# (otherwise your changes may be erased).                             #
11
#                                                                     #
12
#######################################################################
13
14
-- source include/have_innodb.inc
15
16
#
17
# Small basic test with ignore
18
#
19
20
--disable_warnings
21
drop table if exists t1,t2,t3,t4;
22
drop database if exists mysqltest;
23
--enable_warnings
24
#set engine_condition_pushdown=0;
25
create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;
26
27
insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
28
select id, code, name from t1 order by id;
29
30
update ignore t1 set id = 8, name = 'Sinisa' where id < 3;
31
select id, code, name from t1 order by id;
32
update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;
33
select id, code, name from t1 order by id;
34
35
drop table t1;
36
37
#
38
# A bit bigger test
39
# The 'replace_column' statements are needed because the cardinality calculated
40
# by innodb is not always the same between runs
41
#
42
43
CREATE TABLE t1 (
44
  id int(11) NOT NULL auto_increment,
45
  parent_id int(11) DEFAULT '0' NOT NULL,
46
  level tinyint(4) DEFAULT '0' NOT NULL,
47
  PRIMARY KEY (id),
48
  KEY parent_id (parent_id),
49
  KEY level (level)
50
) engine=innodb;
51
INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2);
52
update t1 set parent_id=parent_id+100;
53
select * from t1 where parent_id=102;
54
update t1 set id=id+1000;
55
-- error ER_DUP_ENTRY,1022
56
update t1 set id=1024 where id=1009; 
57
select * from t1;
58
update ignore t1 set id=id+1; # This will change all rows
59
select * from t1;
60
update ignore t1 set id=1023 where id=1010;
61
select * from t1 where parent_id=102;
62
--replace_column 9 #
63
explain select level from t1 where level=1;
64
--replace_column 9 #
65
explain select level,id from t1 where level=1;
66
--replace_column 9 #
67
explain select level,id,parent_id from t1 where level=1;
68
select level,id from t1 where level=1;
69
select level,id,parent_id from t1 where level=1;
70
optimize table t1;
71
--replace_column 7 #
72
show keys from t1;
73
drop table t1;
74
75
#
76
# Test replace
77
#
78
79
CREATE TABLE t1 (
80
  gesuchnr int(11) DEFAULT '0' NOT NULL,
81
  benutzer_id int(11) DEFAULT '0' NOT NULL,
82
  PRIMARY KEY (gesuchnr,benutzer_id)
83
) engine=innodb;
84
85
replace into t1 (gesuchnr,benutzer_id) values (2,1);
86
replace into t1 (gesuchnr,benutzer_id) values (1,1);
87
replace into t1 (gesuchnr,benutzer_id) values (1,1);
88
select * from t1;
89
drop table t1;
90
91
#
92
# test delete using hidden_primary_key
93
#
94
95
create table t1 (a int) engine=innodb;
96
insert into t1 values (1), (2);
97
optimize table t1;
98
delete from t1 where a = 1;
99
select * from t1;
100
check table t1;
101
drop table t1;
102
103
create table t1 (a int,b varchar(20)) engine=innodb;
104
insert into t1 values (1,""), (2,"testing");
105
delete from t1 where a = 1;
106
select * from t1;
107
create index skr on t1 (a);
108
insert into t1 values (3,""), (4,"testing");
109
analyze table t1;
110
--replace_column 7 #
111
show keys from t1;
112
drop table t1;
113
114
115
# Test of reading on secondary key with may be null
116
117
create table t1 (a int,b varchar(20),key(a)) engine=innodb;
118
insert into t1 values (1,""), (2,"testing");
119
select * from t1 where a = 1;
120
drop table t1;
121
122
#
123
# Test rollback
124
#
125
126
create table t1 (n int not null primary key) engine=innodb;
127
set autocommit=0;
128
insert into t1 values (4);
129
rollback;
130
select n, "after rollback" from t1;
131
insert into t1 values (4);
132
commit;
133
select n, "after commit" from t1;
134
commit;
135
insert into t1 values (5);
136
-- error ER_DUP_ENTRY
137
insert into t1 values (4);
138
commit;
139
select n, "after commit" from t1;
140
set autocommit=1;
141
insert into t1 values (6);
142
-- error ER_DUP_ENTRY
143
insert into t1 values (4);
144
select n from t1;
145
set autocommit=0;
146
#
147
# savepoints
148
#
149
begin;
150
savepoint `my_savepoint`;
151
insert into t1 values (7);
152
savepoint `savept2`;
153
insert into t1 values (3);
154
select n from t1;
155
savepoint savept3;
156
rollback to savepoint savept2;
157
--error 1305
158
rollback to savepoint savept3;
159
rollback to savepoint savept2;
160
release savepoint `my_savepoint`;
161
select n from t1;
162
-- error 1305
163
rollback to savepoint `my_savepoint`;
164
--error 1305
165
rollback to savepoint savept2;
166
insert into t1 values (8);
167
savepoint sv;
168
commit;
169
savepoint sv;
170
set autocommit=1;
171
# nop
172
rollback;
173
drop table t1;
174
175
#
176
# Test for commit and FLUSH TABLES WITH READ LOCK
177
#
178
179
create table t1 (n int not null primary key) engine=innodb;
180
start transaction;
181
insert into t1 values (4);
182
flush tables with read lock;
183
#
184
# Current code can't handle a read lock in middle of transaction
185
#--error 1223;
186
commit;
187
unlock tables;
188
commit;
189
select * from t1;
190
drop table t1;
191
192
#
193
# Testing transactions
194
#
195
196
create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=innodb;
197
begin;
198
insert into t1 values(1,'hamdouni');
199
select id as afterbegin_id,nom as afterbegin_nom from t1;
200
rollback;
201
select id as afterrollback_id,nom as afterrollback_nom from t1;
202
set autocommit=0;
203
insert into t1 values(2,'mysql');
204
select id as afterautocommit0_id,nom as afterautocommit0_nom from t1;
205
rollback;
206
select id as afterrollback_id,nom as afterrollback_nom from t1;
207
set autocommit=1;
208
drop table t1;
209
210
#
211
# Simple not autocommit test
212
# 
213
214
CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=innodb;
215
insert into t1 values ('pippo', 12);
216
-- error ER_DUP_ENTRY
217
insert into t1 values ('pippo', 12); # Gives error
218
delete from t1;
219
delete from t1 where id = 'pippo';
220
select * from t1;
221
222
insert into t1 values ('pippo', 12);
223
set autocommit=0;
224
delete from t1;
225
rollback;
226
select * from t1;
227
delete from t1;
228
commit;
229
select * from t1;
230
drop table t1;
231
232
#
233
# Test of active transactions
234
#
235
236
create table t1 (a integer) engine=innodb;
237
start transaction;
238
rename table t1 to t2;
239
create table t1 (b integer) engine=innodb;
240
insert into t1 values (1);
241
rollback;
242
drop table t1;
243
rename table t2 to t1;
244
drop table t1;
245
set autocommit=1;
246
247
#
248
# The following simple tests failed at some point
249
#
250
251
CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=innodb;
252
INSERT INTO t1 VALUES (1, 'Jochen');
253
select * from t1;
254
drop table t1;
255
256
CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=innodb;
257
set autocommit=0;
258
INSERT INTO t1  SET _userid='marc@anyware.co.uk';
259
COMMIT;
260
SELECT * FROM t1;
261
SELECT _userid FROM t1 WHERE _userid='marc@anyware.co.uk';
262
drop table t1;
263
set autocommit=1;
264
265
#
266
# Test when reading on part of unique key
267
#
268
CREATE TABLE t1 (
269
  user_id int(10) DEFAULT '0' NOT NULL,
270
  name varchar(100),
271
  phone varchar(100),
272
  ref_email varchar(100) DEFAULT '' NOT NULL,
273
  detail varchar(200),
274
  PRIMARY KEY (user_id,ref_email)
275
)engine=innodb;
276
277
INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar');
278
select * from t1 where user_id=10292;
279
INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');
280
select * from t1 where user_id=10292;
281
select * from t1 where user_id>=10292;
282
select * from t1 where user_id>10292;
283
select * from t1 where user_id<10292;
284
drop table t1;
285
286
#
287
# Test that keys are created in right order
288
#
289
290
CREATE TABLE t1 (a int not null, b int not null,c int not null,
291
key(a),primary key(a,b), unique(c),key(a),unique(b));
292
--replace_column 7 #
293
show index from t1;
294
drop table t1;
295
296
#
297
# Test of ALTER TABLE and innodb tables
298
#
299
300
create table t1 (col1 int not null, col2 char(4) not null, primary key(col1));
301
alter table t1 engine=innodb;
302
insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');
303
select * from t1;
304
update t1 set col2='7' where col1='4';
305
select * from t1;
306
alter table t1 add co3 int not null;
307
select * from t1;
308
update t1 set col2='9' where col1='2';
309
select * from t1;
310
drop table t1;
311
312
#
313
# INSERT INTO innodb tables
314
#
315
316
create table t1 (a int not null , b int, primary key (a)) engine = innodb;
317
create table t2 (a int not null , b int, primary key (a)) engine = myisam;
318
insert into t1 VALUES (1,3) , (2,3), (3,3);
319
select * from t1;
320
insert into t2 select * from t1;
321
select * from t2;
322
delete from t1 where b = 3;
323
select * from t1;
324
insert into t1 select * from t2;
325
select * from t1;
326
select * from t2;
327
drop table t1,t2;
328
329
#
330
# ORDER BY on not primary key
331
#
332
333
CREATE TABLE t1 (
334
  user_name varchar(12),
335
  password text,
336
  subscribed char(1),
337
  user_id int(11) DEFAULT '0' NOT NULL,
338
  quota bigint(20),
339
  weight double,
340
  access_date date,
341
  access_time time,
342
  approved datetime,
343
  dummy_primary_key int(11) NOT NULL auto_increment,
344
  PRIMARY KEY (dummy_primary_key)
345
) ENGINE=innodb;
346
INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);
347
INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2);
348
INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3);
349
INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4);
350
INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5);
351
select  user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name;
352
drop table t1;
353
354
#
355
# Testing of tables without primary keys
356
#
357
358
CREATE TABLE t1 (
359
  id int(11) NOT NULL auto_increment,
360
  parent_id int(11) DEFAULT '0' NOT NULL,
361
  level tinyint(4) DEFAULT '0' NOT NULL,
362
  KEY (id),
363
  KEY parent_id (parent_id),
364
  KEY level (level)
365
) engine=innodb;
366
INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1);
367
INSERT INTO t1 values (179,5,2);
368
update t1 set parent_id=parent_id+100;
369
select * from t1 where parent_id=102;
370
update t1 set id=id+1000;
371
update t1 set id=1024 where id=1009; 
372
select * from t1;
373
update ignore t1 set id=id+1; # This will change all rows
374
select * from t1;
375
update ignore t1 set id=1023 where id=1010;
376
select * from t1 where parent_id=102;
377
--replace_column 9 #
378
explain select level from t1 where level=1;
379
select level,id from t1 where level=1;
380
select level,id,parent_id from t1 where level=1;
381
select level,id from t1 where level=1 order by id;
382
delete from t1 where level=1;
383
select * from t1;
384
drop table t1;
385
386
#
387
# Test of index only reads
388
#
389
CREATE TABLE t1 (
390
   sca_code char(6) NOT NULL,
391
   cat_code char(6) NOT NULL,
392
   sca_desc varchar(50),
393
   lan_code char(2) NOT NULL,
394
   sca_pic varchar(100),
395
   sca_sdesc varchar(50),
396
   sca_sch_desc varchar(16),
397
   PRIMARY KEY (sca_code, cat_code, lan_code),
398
   INDEX sca_pic (sca_pic)
399
) engine = innodb ;
400
401
INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'),( 'QQ', 'N', 'RING', 'EN', 'not null', NULL, 'RING');
402
select count(*) from t1 where sca_code = 'PD';
403
select count(*) from t1 where sca_code <= 'PD';
404
select count(*) from t1 where sca_pic is null;
405
alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);
406
select count(*) from t1 where sca_code='PD' and sca_pic is null;
407
select count(*) from t1 where cat_code='E';
408
alter table t1 drop index sca_pic, add index (sca_pic, cat_code);
409
select count(*) from t1 where sca_code='PD' and sca_pic is null;
410
select count(*) from t1 where sca_pic >= 'n';
411
select sca_pic from t1 where sca_pic is null;
412
update t1 set sca_pic="test" where sca_pic is null;
413
delete from t1 where sca_code='pd';
414
drop table t1;
415
416
#
417
# Test of opening table twice and timestamps
418
#
419
set @a:=now();
420
CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb;
421
insert into t1 (a) values(1),(2),(3);
422
select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;
423
select a from t1 natural join t1 as t2 where b >= @a order by a;
424
update t1 set a=5 where a=1;
425
select a from t1;
426
drop table t1;
427
428
#
429
# Test with variable length primary key
430
#
431
create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=innodb;
432
insert into t1 values("hello",1),("world",2);
433
select * from t1 order by b desc;
434
optimize table t1;
435
--replace_column 7 #
436
show keys from t1;
437
drop table t1;
438
439
#
440
# Test of create index with NULL columns
441
#
442
create table t1 (i int, j int ) ENGINE=innodb;
443
insert into t1 values (1,2);
444
select * from t1 where i=1 and j=2;
445
create index ax1 on t1 (i,j);
446
select * from t1 where i=1 and j=2;
447
drop table t1;
448
449
#
450
# Test min-max optimization
451
#
452
453
CREATE TABLE t1 (
454
  a int3 unsigned NOT NULL,
455
  b int1 unsigned NOT NULL,
456
  UNIQUE (a, b)
457
) ENGINE = innodb;
458
 
459
INSERT INTO t1 VALUES (1, 1);
460
SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;
461
drop table t1;
462
463
#
464
# Test INSERT DELAYED
465
#
466
467
CREATE TABLE t1 (a int unsigned NOT NULL) engine=innodb;
468
# Can't test this in 3.23
469
# INSERT DELAYED INTO t1 VALUES (1);
470
INSERT INTO t1 VALUES (1);
471
SELECT * FROM t1;
472
DROP TABLE t1;
473
474
475
#
476
# Crash when using many tables (Test case by Jeremy D Zawodny)
477
#
478
479
create table t1 (a int  primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = innodb;
480
insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
481
--replace_column 9 #
482
explain select * from t1 where a > 0 and a < 50;
483
drop table t1;
484
485
#
486
# Test lock tables
487
#
488
489
create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb;
490
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
491
LOCK TABLES t1 WRITE;
492
--error ER_DUP_ENTRY
493
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
494
select id from t1;
495
select id from t1;
496
UNLOCK TABLES;
497
DROP TABLE t1;
498
499
create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb;
500
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
501
LOCK TABLES t1 WRITE;
502
begin;
503
--error ER_DUP_ENTRY
504
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
505
select id from t1;
506
insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');
507
commit;
508
select id,id3 from t1;
509
UNLOCK TABLES;
510
DROP TABLE t1;
511
512
#
513
# Test prefix key
514
#
515
create table t1 (a char(20), unique (a(5))) engine=innodb;
516
drop table t1;
517
create table t1 (a char(20), index (a(5))) engine=innodb;
518
show create table t1;
519
drop table t1;
520
521
#
522
# Test using temporary table and auto_increment
523
#
524
525
create temporary table t1 (a int not null auto_increment, primary key(a)) engine=innodb;
526
insert into t1 values (NULL),(NULL),(NULL);
527
delete from t1 where a=3;
528
insert into t1 values (NULL);
529
select * from t1;
530
alter table t1 add b int;
531
select * from t1;
532
drop table t1;
533
534
#Slashdot bug
535
create table t1
536
 (
537
  id int auto_increment primary key,
538
  name varchar(32) not null,
539
  value text not null,
540
  uid int not null,
541
  unique key(name,uid)
542
 ) engine=innodb;
543
insert into t1 values (1,'one','one value',101),
544
 (2,'two','two value',102),(3,'three','three value',103);
545
set insert_id=5;
546
replace into t1 (value,name,uid) values ('other value','two',102);
547
delete from t1 where uid=102;
548
set insert_id=5;
549
replace into t1 (value,name,uid) values ('other value','two',102);
550
set insert_id=6;
551
replace into t1 (value,name,uid) values ('other value','two',102);
552
select * from t1;
553
drop table t1;
554
555
#
556
# Test DROP DATABASE
557
#
558
559
create database mysqltest;
560
create table mysqltest.t1 (a int not null) engine= innodb;
561
insert into mysqltest.t1 values(1);
562
create table mysqltest.t2 (a int not null) engine= myisam;
563
insert into mysqltest.t2 values(1);
564
create table mysqltest.t3 (a int not null) engine= heap;
565
insert into mysqltest.t3 values(1);
566
commit;
567
drop database mysqltest;
568
# Don't check error message
569
--error 1049
570
show tables from mysqltest;
571
572
#
573
# Test truncate table with and without auto_commit
574
#
575
576
set autocommit=0;
577
create table t1 (a int not null) engine= innodb;
578
insert into t1 values(1),(2);
579
truncate table t1;
580
commit;
581
truncate table t1;
582
truncate table t1;
583
select * from t1;
584
insert into t1 values(1),(2);
585
delete from t1;
586
select * from t1;
587
commit;
588
drop table t1;
589
set autocommit=1;
590
591
create table t1 (a int not null) engine= innodb;
592
insert into t1 values(1),(2);
593
truncate table t1;
594
insert into t1 values(1),(2);
595
select * from t1;
596
truncate table t1;
597
insert into t1 values(1),(2);
598
delete from t1;
599
select * from t1;
600
drop table t1;
601
602
#
603
# Test of how ORDER BY works when doing it on the whole table
604
#
605
606
create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb;
607
insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
608
--replace_column 9 #
609
explain select * from t1 order by a;
610
--replace_column 9 #
611
explain select * from t1 order by b;
612
--replace_column 9 #
613
explain select * from t1 order by c;
614
--replace_column 9 #
615
explain select a from t1 order by a;
616
--replace_column 9 #
617
explain select b from t1 order by b;
618
--replace_column 9 #
619
explain select a,b from t1 order by b;
620
--replace_column 9 #
621
explain select a,b from t1;
622
--replace_column 9 #
623
explain select a,b,c from t1;
624
drop table t1;
625
626
#
627
# Check describe
628
#
629
630
create table t1 (t int not null default 1, key (t)) engine=innodb;
631
desc t1;
632
drop table t1;
633
634
#
635
# Test of multi-table-delete
636
#
637
638
CREATE TABLE t1 (
639
  number bigint(20) NOT NULL default '0',
640
  cname char(15) NOT NULL default '',
641
  carrier_id smallint(6) NOT NULL default '0',
642
  privacy tinyint(4) NOT NULL default '0',
643
  last_mod_date timestamp NOT NULL,
644
  last_mod_id smallint(6) NOT NULL default '0',
645
  last_app_date timestamp NOT NULL,
646
  last_app_id smallint(6) default '-1',
647
  version smallint(6) NOT NULL default '0',
648
  assigned_scps int(11) default '0',
649
  status tinyint(4) default '0'
650
) ENGINE=InnoDB;
651
INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);
652
INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);
653
INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);
654
INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);
655
INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);
656
INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);
657
CREATE TABLE t2 (
658
  number bigint(20) NOT NULL default '0',
659
  cname char(15) NOT NULL default '',
660
  carrier_id smallint(6) NOT NULL default '0',
661
  privacy tinyint(4) NOT NULL default '0',
662
  last_mod_date timestamp NOT NULL,
663
  last_mod_id smallint(6) NOT NULL default '0',
664
  last_app_date timestamp NOT NULL,
665
  last_app_id smallint(6) default '-1',
666
  version smallint(6) NOT NULL default '0',
667
  assigned_scps int(11) default '0',
668
  status tinyint(4) default '0'
669
) ENGINE=InnoDB;
670
INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);
671
INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);
672
INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);
673
INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);
674
select * from t1;
675
select * from t2;
676
delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or  (t1.carrier_id=90 and t2.number is null);
677
select * from t1;
678
select * from t2; 
679
select * from t2;
680
drop table t1,t2;
681
682
#
683
# A simple test with some isolation levels
684
# TODO: Make this into a test using replication to really test how
685
# this works.
686
#
687
688
create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;
689
690
BEGIN;
691
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
692
SELECT @@tx_isolation,@@global.tx_isolation;
693
insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');
694
select id, code, name from t1 order by id;
695
COMMIT;
696
697
BEGIN;
698
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
699
insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');
700
select id, code, name from t1 order by id;
701
COMMIT;
702
703
BEGIN;
704
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
705
insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
706
select id, code, name from t1 order by id;
707
COMMIT;
708
DROP TABLE t1;
709
710
#
711
# Test of multi-table-update
712
#
713
create table t1 (n int(10), d int(10)) engine=innodb;
714
create table t2 (n int(10), d int(10)) engine=innodb;
715
insert into t1 values(1,1),(1,2);
716
insert into t2 values(1,10),(2,20);
717
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
718
select * from t1;
719
select * from t2;
720
drop table t1,t2;
721
722
#
723
# Bug #29136  	erred multi-delete on trans table does not rollback 
724
#
725
726
# prepare
727
--disable_warnings
728
drop table if exists t1, t2;
729
--enable_warnings
730
CREATE TABLE t1 (a int, PRIMARY KEY (a));
731
CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
732
create trigger trg_del_t2 after  delete on t2 for each row
733
       insert into t1 values (1);
734
insert into t1 values (1);
735
insert into t2 values (1),(2);
736
737
738
# exec cases A, B - see multi_update.test
739
740
# A. send_error() w/o send_eof() branch
741
742
--error ER_DUP_ENTRY
743
delete t2 from t2;
744
745
# check
746
747
select count(*) from t2 /* must be 2 as restored after rollback caused by the error */;
748
749
# cleanup bug#29136
750
751
drop table t1, t2;
752
753
754
#
755
# Testing of IFNULL
756
#
757
create table t1 (a int, b int) engine=innodb;
758
insert into t1 values(20,null);
759
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
760
t2.b=t3.a;
761
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
762
t2.b=t3.a order by 1;
763
insert into t1 values(10,null);
764
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
765
t2.b=t3.a order by 1;
766
drop table t1;
767
768
#
769
# Test of read_through not existing const_table
770
#
771
772
create table t1 (a varchar(10) not null) engine=myisam;
773
create table t2 (b varchar(10) not null unique) engine=innodb;
774
select t1.a from t1,t2 where t1.a=t2.b;
775
drop table t1,t2;
776
create table t1 (a int not null, b int, primary key (a)) engine = innodb;
777
create table t2 (a int not null, b int, primary key (a)) engine = innodb;
778
insert into t1 values (10, 20);
779
insert into t2 values (10, 20);
780
update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;
781
drop table t1,t2;
782
783
#
784
# Test of multi-table-delete with foreign key constraints
785
#
786
787
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
788
CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (t1_id) REFERENCES t1(id)  ON DELETE CASCADE ) ENGINE=INNODB;
789
insert into t1 set id=1;
790
insert into t2 set id=1, t1_id=1;
791
delete t1,t2 from t1,t2 where t1.id=t2.t1_id;
792
select * from t1;
793
select * from t2;
794
drop table t2,t1;
795
CREATE TABLE t1(id INT NOT NULL,  PRIMARY KEY (id)) ENGINE=INNODB;
796
CREATE TABLE t2(id  INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id)  ) ENGINE=INNODB;
797
INSERT INTO t1 VALUES(1);
798
INSERT INTO t2 VALUES(1, 1);
799
SELECT * from t1;
800
UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;
801
SELECT * from t1;
802
UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;
803
SELECT * from t1;
804
DROP TABLE t1,t2;
805
806
#
807
# Test of range_optimizer
808
#
809
810
set autocommit=0;
811
812
CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
813
814
CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
815
816
CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=InnoDB;
817
818
INSERT INTO t3 VALUES("my-test-1", "my-test-2");
819
COMMIT;
820
821
INSERT INTO t1 VALUES("this-key", "will disappear");
822
INSERT INTO t2 VALUES("this-key", "will also disappear");
823
DELETE FROM t3 WHERE id1="my-test-1";
824
825
SELECT * FROM t1;
826
SELECT * FROM t2;
827
SELECT * FROM t3;
828
ROLLBACK;
829
830
SELECT * FROM t1;
831
SELECT * FROM t2;
832
SELECT * FROM t3;
833
SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;
834
COMMIT;
835
set autocommit=1;
836
DROP TABLE t1,t2,t3;
837
838
#
839
# Check update with conflicting key
840
#
841
842
CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=innodb;
843
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
844
# We need the a < 1000 test here to quard against the halloween problems
845
UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
846
SELECT * from t1;
847
drop table t1;
848
849
#
850
# Test multi update with different join methods
851
#
852
853
CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=innodb;
854
CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=innodb;
855
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12);
856
INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
857
858
# Full join, without key
859
update t1,t2 set t1.a=t1.a+100;
860
select * from t1;
861
862
# unique key
863
update t1,t2 set t1.a=t1.a+100 where t1.a=101;
864
select * from t1;
865
866
# ref key
867
update t1,t2 set t1.b=t1.b+10 where t1.b=2;
868
select * from t1;
869
870
# Range key (in t1)
871
update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100;
872
select * from t1;
873
select * from t2;
874
875
drop table t1,t2;
876
CREATE TABLE t2 (   NEXT_T         BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;
877
CREATE TABLE t1 (  B_ID           INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
878
SET AUTOCOMMIT=0;
879
INSERT INTO t1 ( B_ID ) VALUES ( 1 );
880
INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );
881
ROLLBACK;
882
SELECT * FROM t1;
883
drop table  t1,t2;
884
create table t1  ( pk         int primary key,    parent     int not null,    child      int not null,       index (parent)  ) engine = innodb;
885
insert into t1 values   (1,0,4),  (2,1,3),  (3,2,1),  (4,1,2);
886
select distinct  parent,child   from t1   order by parent;
887
drop table t1;
888
889
#
890
# Test that MySQL priorities clustered indexes
891
#
892
create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;
893
create table t2 (a int not null auto_increment primary key, b int);
894
insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);
895
insert into t2 (a) select b from t1;
896
insert into t1 (b) select b from t2;
897
insert into t2 (a) select b from t1;
898
insert into t1 (a) select b from t2;
899
insert into t2 (a) select b from t1;
900
insert into t1 (a) select b from t2;
901
insert into t2 (a) select b from t1;
902
insert into t1 (a) select b from t2;
903
insert into t2 (a) select b from t1;
904
insert into t1 (a) select b from t2;
905
select count(*) from t1;
906
--replace_column 9 #
907
explain select * from t1 where c between 1 and 2500;
908
update t1 set c=a;
909
--replace_column 9 #
910
explain select * from t1 where c between 1 and 2500;
911
drop table t1,t2;
912
913
#
914
# Test of UPDATE ... ORDER BY
915
#
916
917
create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb;
918
919
insert into t1 (id) values (null),(null),(null),(null),(null);
920
update t1 set fk=69 where fk is null order by id limit 1;
921
SELECT * from t1;
922
drop table t1;
923
924
create table t1 (a int not null, b int not null, key (a));
925
insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
926
SET @tmp=0;
927
update t1 set b=(@tmp:=@tmp+1) order by a;
928
update t1 set b=99 where a=1 order by b asc limit 1;
929
update t1 set b=100 where a=1 order by b desc limit 2;
930
update t1 set a=a+10+b where a=1 order by b;
931
select * from t1 order by a,b;
932
drop table t1;
933
934
#
935
# Test of multi-table-updates (bug #1980).
936
#
937
938
create table t1 ( c char(8) not null ) engine=innodb;
939
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
940
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
941
942
alter table t1 add b char(8) not null;
943
alter table t1 add a char(8) not null;
944
alter table t1 add primary key (a,b,c);
945
update t1 set a=c, b=c;
946
947
create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=innodb;
948
insert into t2 select * from t1;
949
950
delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
951
drop table t1,t2;
952
953
#
954
# test autoincrement with TRUNCATE
955
#
956
957
SET AUTOCOMMIT=1;
958
create table t1 (a integer auto_increment primary key) engine=innodb;
959
insert into t1 (a) values (NULL),(NULL);
960
truncate table t1;
961
insert into t1 (a) values (NULL),(NULL);
962
SELECT * from t1;
963
drop table t1;
964
965
#
966
# Test dictionary handling with spaceand quoting
967
#
968
969
CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB;
970
CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (`t1_id`) REFERENCES `t1`(`id 1`)  ON DELETE CASCADE ) ENGINE=INNODB;
971
#show create table t2;
972
drop table t2,t1;
973
974
#
975
# Test of multi updated and foreign keys
976
#
977
978
create table `t1` (`id` int( 11 ) not null  ,primary key ( `id` )) engine = innodb;
979
insert into `t1`values ( 1 ) ;
980
create table `t2` (`id` int( 11 ) not null default '0',unique key `id` ( `id` ) ,constraint `t1_id_fk` foreign key ( `id` ) references `t1` (`id` )) engine = innodb;
981
insert into `t2`values ( 1 ) ;
982
create table `t3` (`id` int( 11 ) not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = innodb;
983
insert into `t3`values ( 1 ) ;
984
--error 1451
985
delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
986
--error 1451
987
update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7  where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
988
--error 1054
989
update t3 set  t3.id=7  where t1.id =1 and t2.id = t1.id and t3.id = t2.id;
990
drop table t3,t2,t1;
991
992
#
993
# test for recursion depth limit
994
#
995
create table t1(
996
	id int primary key,
997
	pid int,
998
	index(pid),
999
	foreign key(pid) references t1(id) on delete cascade) engine=innodb;
1000
insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),
1001
	(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);
1002
-- error 1451
1003
delete from t1 where id=0;
1004
delete from t1 where id=15;
1005
delete from t1 where id=0;
1006
1007
drop table t1;
1008
1009
#
1010
# Test timestamps
1011
#
1012
1013
CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB;
1014
CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx
1015
(stamp))ENGINE=InnoDB;
1016
insert into t1 values (1),(2),(3);
1017
# Note that timestamp 3 is wrong
1018
insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);
1019
SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <
1020
'20020204120000' GROUP BY col1;
1021
drop table t1,t2;
1022
1023
#
1024
# Test by Francois MASUREL
1025
#
1026
1027
CREATE TABLE t1 (
1028
  `id` int(10) unsigned NOT NULL auto_increment,
1029
  `id_object` int(10) unsigned default '0',
1030
  `id_version` int(10) unsigned NOT NULL default '1',
1031
  `label` varchar(100) NOT NULL default '',
1032
  `description` text,
1033
  PRIMARY KEY  (`id`),
1034
  KEY `id_object` (`id_object`),
1035
  KEY `id_version` (`id_version`)
1036
) ENGINE=InnoDB;
1037
1038
INSERT INTO t1 VALUES("6", "3382", "9", "Test", NULL), ("7", "102", "5", "Le Pekin (Test)", NULL),("584", "1794", "4", "Test de resto", NULL),("837", "1822", "6", "Test 3", NULL),("1119", "3524", "1", "Societe Test", NULL),("1122", "3525", "1", "Fournisseur Test", NULL);
1039
1040
CREATE TABLE t2 (
1041
  `id` int(10) unsigned NOT NULL auto_increment,
1042
  `id_version` int(10) unsigned NOT NULL default '1',
1043
  PRIMARY KEY  (`id`),
1044
  KEY `id_version` (`id_version`)
1045
) ENGINE=InnoDB;
1046
1047
INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");
1048
1049
SELECT t2.id, t1.`label` FROM t2 INNER JOIN
1050
(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl 
1051
ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);
1052
drop table t1,t2;
1053
1054
create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=myisam;
1055
create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=innodb;
1056
create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb;
1057
insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
1058
insert t2 select * from t1;
1059
insert t3 select * from t1;
1060
checksum table t1, t2, t3, t4 quick;
1061
checksum table t1, t2, t3, t4;
1062
checksum table t1, t2, t3, t4 extended;
1063
#show table status;
1064
drop table t1,t2,t3;
1065
1066
#
1067
# Test problem with refering to different fields in same table in UNION
1068
# (Bug #2552)
1069
#
1070
create table t1 (id int,  name char(10) not null,  name2 char(10) not null) engine=innodb;
1071
insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');
1072
select trim(name2) from t1  union all  select trim(name) from t1 union all select trim(id) from t1;
1073
drop table t1;
1074
1075
#
1076
# Bug2160
1077
#
1078
create table t1 (a int) engine=innodb;
1079
create table t2 like t1;
1080
drop table t1,t2;
1081
1082
#
1083
# Test of automaticly created foreign keys
1084
#
1085
1086
create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=innodb;
1087
create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb;
1088
show create table t1;
1089
show create table t2;
1090
create index id on t2 (id);
1091
show create table t2;
1092
create index id2 on t2 (id);
1093
show create table t2;
1094
drop index id2 on t2;
1095
--error 1025,1025
1096
drop index id on t2;
1097
show create table t2;
1098
drop table t2;
1099
1100
create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id,id2) references t1 (id,id2)) engine = innodb;
1101
show create table t2;
1102
create unique index id on t2 (id,id2);
1103
show create table t2;
1104
drop table t2;
1105
1106
# Check foreign key columns created in different order than key columns
1107
create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb;
1108
show create table t2;
1109
drop table t2;
1110
1111
create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2), constraint t1_id_fk foreign key (id) references t1 (id)) engine = innodb;
1112
show create table t2;
1113
drop table t2;
1114
1115
create table t2 (id int(11) not null, id2 int(11) not null, unique (id,id2),constraint t1_id_fk foreign key (id2,id) references t1 (id,id2)) engine = innodb;
1116
show create table t2;
1117
drop table t2;
1118
1119
create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id), primary key (id), index (id,id2)) engine = innodb;
1120
show create table t2;
1121
drop table t2;
1122
1123
create table t2 (id int(11) not null auto_increment, id2 int(11) not null, constraint t1_id_fk foreign key (id) references t1 (id)) engine= innodb;
1124
show create table t2;
1125
alter table t2 add index id_test (id), add index id_test2 (id,id2);
1126
show create table t2;
1127
drop table t2;
1128
1129
# Test error handling
1130
1131
# Embedded server doesn't chdir to data directory
1132
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1133
--error ER_WRONG_FK_DEF
1134
create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb;
1135
1136
# bug#3749
1137
1138
create table t2 (a int auto_increment primary key, b int, index(b), foreign key (b) references t1(id), unique(b)) engine=innodb;
1139
show create table t2;
1140
drop table t2;
1141
create table t2 (a int auto_increment primary key, b int, foreign key (b) references t1(id), foreign key (b) references t1(id), unique(b)) engine=innodb;
1142
show create table t2;
1143
drop table t2, t1;
1144
1145
1146
#
1147
# Bug #6126: Duplicate columns in keys gives misleading error message
1148
#
1149
--error 1060
1150
create table t1 (c char(10), index (c,c)) engine=innodb;
1151
--error 1060
1152
create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=innodb;
1153
--error 1060
1154
create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=innodb;
1155
--error 1060
1156
create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=innodb;
1157
create table t1 (c1 char(10), c2 char(10)) engine=innodb;
1158
--error 1060
1159
alter table t1 add key (c1,c1);
1160
--error 1060
1161
alter table t1 add key (c2,c1,c1);
1162
--error 1060
1163
alter table t1 add key (c1,c2,c1);
1164
--error 1060
1165
alter table t1 add key (c1,c1,c2);
1166
drop table t1;
1167
1168
#
1169
# Bug #4082: integer truncation
1170
#
1171
1172
create table t1(a int(1) , b int(1)) engine=innodb;
1173
insert into t1 values ('1111', '3333');
1174
select distinct concat(a, b) from t1;
1175
drop table t1;
1176
1177
#
1178
# BUG#7709 test case - Boolean fulltext query against unsupported 
1179
#                      engines does not fail
1180
#
1181
1182
CREATE TABLE t1 ( a char(10) ) ENGINE=InnoDB;
1183
--error 1214
1184
SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE);
1185
DROP TABLE t1;
1186
1187
#
1188
# check null values #1
1189
#
1190
1191
--disable_warnings
1192
CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY  (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1193
INSERT INTO t1 VALUES (1),(2),(3);
1194
CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL default '0', PRIMARY KEY  (b_id), KEY  (b_a), 
1195
                CONSTRAINT fk_b_a FOREIGN KEY (b_a) REFERENCES t1 (a_id) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1196
--enable_warnings
1197
INSERT INTO t2 VALUES (1,1),(2,1),(3,1),(4,2),(5,2);
1198
SELECT * FROM (SELECT t1.*,GROUP_CONCAT(t2.b_id SEPARATOR ',') as b_list FROM (t1 LEFT JOIN (t2) on t1.a_id = t2.b_a) GROUP BY t1.a_id ) AS xyz;
1199
DROP TABLE t2;
1200
DROP TABLE t1;
1201
1202
#
1203
# Bug#11816 - Truncate table doesn't work with temporary innodb tables
1204
# This is not an innodb bug, but we test it using innodb.
1205
#
1206
create temporary table t1 (a int) engine=innodb;
1207
insert into t1 values (4711);
1208
truncate t1;
1209
insert into t1 values (42);
1210
select * from t1;
1211
drop table t1;
1212
# Show that it works with permanent tables too.
1213
create table t1 (a int) engine=innodb;
1214
insert into t1 values (4711);
1215
truncate t1;
1216
insert into t1 values (42);
1217
select * from t1;
1218
drop table t1;
1219
1220
#
1221
# Bug #13025  Server crash during filesort	
1222
#
1223
1224
create table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=innodb;
1225
insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);
1226
select * from t1 order by a,b,c,d;
1227
explain select * from t1 order by a,b,c,d;
1228
drop table t1;
1229
1230
#
1231
# BUG#11039,#13218 Wrong key length in min()
1232
#
1233
1234
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1235
insert into t1 values ('8', '6'), ('4', '7');
1236
select min(a) from t1;
1237
select min(b) from t1 where a='8';
1238
drop table t1;
1239
1240
# End of 4.1 tests
1241
1242
#
1243
# range optimizer problem
1244
#
1245
1246
create table t1 (x bigint unsigned not null primary key) engine=innodb;
1247
insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);
1248
select * from t1;
1249
select count(*) from t1 where x>0;
1250
select count(*) from t1 where x=0;
1251
select count(*) from t1 where x<0;
1252
select count(*) from t1 where x < -16;
1253
select count(*) from t1 where x = -16;
1254
explain select count(*) from t1 where x > -16;
1255
select count(*) from t1 where x > -16;
1256
select * from t1 where x > -16;
1257
select count(*) from t1 where x = 18446744073709551601;
1258
drop table t1;
1259
1260
1261
# Test for testable InnoDB status variables. This test
1262
# uses previous ones(pages_created, rows_deleted, ...).
1263
show status like "Innodb_buffer_pool_pages_total";
1264
show status like "Innodb_page_size";
1265
show status like "Innodb_rows_deleted";
1266
show status like "Innodb_rows_inserted";
1267
show status like "Innodb_rows_updated";
1268
1269
# Test for row locks InnoDB status variables.
1270
show status like "Innodb_row_lock_waits";
1271
show status like "Innodb_row_lock_current_waits";
1272
show status like "Innodb_row_lock_time";
1273
show status like "Innodb_row_lock_time_max";
1274
show status like "Innodb_row_lock_time_avg";
1275
1276
# Test for innodb_sync_spin_loops variable
1277
show variables like "innodb_sync_spin_loops";
1278
set global innodb_sync_spin_loops=1000;
1279
show variables like "innodb_sync_spin_loops";
1280
set global innodb_sync_spin_loops=0;
1281
show variables like "innodb_sync_spin_loops";
1282
set global innodb_sync_spin_loops=20;
1283
show variables like "innodb_sync_spin_loops";
1284
1285
# Test for innodb_thread_concurrency variable
1286
show variables like "innodb_thread_concurrency";
1287
set global innodb_thread_concurrency=1001;
1288
show variables like "innodb_thread_concurrency";
1289
set global innodb_thread_concurrency=0;
1290
show variables like "innodb_thread_concurrency";
1291
set global innodb_thread_concurrency=16;
1292
show variables like "innodb_thread_concurrency";
1293
1294
# Test for innodb_concurrency_tickets variable
1295
show variables like "innodb_concurrency_tickets";
1296
set global innodb_concurrency_tickets=1000;
1297
show variables like "innodb_concurrency_tickets";
1298
set global innodb_concurrency_tickets=0;
1299
show variables like "innodb_concurrency_tickets";
1300
set global innodb_concurrency_tickets=500;
1301
show variables like "innodb_concurrency_tickets";
1302
1303
# Test for innodb_thread_sleep_delay variable
1304
show variables like "innodb_thread_sleep_delay";
1305
set global innodb_thread_sleep_delay=100000;
1306
show variables like "innodb_thread_sleep_delay";
1307
set global innodb_thread_sleep_delay=0;
1308
show variables like "innodb_thread_sleep_delay";
1309
set global innodb_thread_sleep_delay=10000;
1310
show variables like "innodb_thread_sleep_delay";
1311
1312
#
1313
# Test varchar
1314
#
1315
1316
let $default=`select @@storage_engine`;
1317
set storage_engine=INNODB;
1318
source include/varchar.inc;
1319
1320
#
1321
# Some errors/warnings on create
1322
#
1323
1324
# Embedded server doesn't chdir to data directory
1325
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1326
create table t1 (v varchar(65530), key(v));
1327
drop table t1;
1328
create table t1 (v varchar(65536));
1329
show create table t1;
1330
drop table t1;
1331
create table t1 (v varchar(65530) character set utf8);
1332
show create table t1;
1333
drop table t1;
1334
1335
eval set storage_engine=$default;
1336
1337
# InnoDB specific varchar tests
1338
create table t1 (v varchar(16384)) engine=innodb;
1339
drop table t1;
1340
1341
#
1342
# BUG#11039 Wrong key length in min()
1343
#
1344
1345
create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
1346
insert into t1 values ('8', '6'), ('4', '7');
1347
select min(a) from t1;
1348
select min(b) from t1 where a='8';
1349
drop table t1;
1350
1351
#
1352
# Bug #11080 & #11005  Multi-row REPLACE fails on a duplicate key error
1353
#
1354
1355
CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY  (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
1356
insert into t1 (b) values (1);
1357
replace into t1 (b) values (2), (1), (3);
1358
select * from t1;
1359
truncate table t1;
1360
insert into t1 (b) values (1);
1361
replace into t1 (b) values (2);
1362
replace into t1 (b) values (1);
1363
replace into t1 (b) values (3);
1364
select * from t1;
1365
drop table t1;
1366
1367
create table t1 (rowid int not null auto_increment, val int not null,primary
1368
key (rowid), unique(val)) engine=innodb;
1369
replace into t1 (val) values ('1'),('2');
1370
replace into t1 (val) values ('1'),('2');
1371
--error ER_DUP_ENTRY
1372
insert into t1 (val) values ('1'),('2');
1373
select * from t1;
1374
drop table t1;
1375
1376
#
1377
# Test that update does not change internal auto-increment value
1378
#
1379
1380
create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
1381
insert into t1 (val) values (1);
1382
update t1 set a=2 where a=1;
1383
# We should get the following error because InnoDB does not update the counter
1384
--error ER_DUP_ENTRY
1385
insert into t1 (val) values (1);
1386
select * from t1;
1387
drop table t1;
1388
#
1389
# Bug #10465
1390
#
1391
1392
--disable_warnings
1393
CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
1394
--enable_warnings
1395
INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
1396
SELECT GRADE  FROM t1 WHERE GRADE > 160 AND GRADE < 300;
1397
SELECT GRADE  FROM t1 WHERE GRADE= 151;
1398
DROP TABLE t1;
1399
1400
#
1401
# Bug #12340 multitable delete deletes only one record
1402
#
1403
create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;
1404
create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;
1405
insert into t2 values ('aa','cc');
1406
insert into t1 values ('aa','bb'),('aa','cc');
1407
delete t1 from t1,t2 where f1=f3 and f4='cc';
1408
select * from t1;
1409
drop table t1,t2;
1410
1411
#
1412
# Test that the slow TRUNCATE implementation resets autoincrement columns
1413
# (bug #11946)
1414
#
1415
1416
CREATE TABLE t1 (
1417
id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
1418
) ENGINE=InnoDB;
1419
1420
CREATE TABLE t2 (
1421
id INTEGER NOT NULL,
1422
FOREIGN KEY (id) REFERENCES t1 (id)
1423
) ENGINE=InnoDB;
1424
1425
INSERT INTO t1 (id) VALUES (NULL);
1426
SELECT * FROM t1;
1427
TRUNCATE t1;
1428
INSERT INTO t1 (id) VALUES (NULL);
1429
SELECT * FROM t1;
1430
1431
# continued from above; test that doing a slow TRUNCATE on a table with 0
1432
# rows resets autoincrement columns
1433
DELETE FROM t1;
1434
TRUNCATE t1;
1435
INSERT INTO t1 (id) VALUES (NULL);
1436
SELECT * FROM t1;
1437
DROP TABLE t2, t1;
1438
1439
# Test that foreign keys in temporary tables are not accepted (bug #12084)
1440
CREATE TABLE t1
1441
(
1442
 id INT PRIMARY KEY
1443
) ENGINE=InnoDB;
1444
1445
--error 1005,1005
1446
CREATE TEMPORARY TABLE t2
1447
(
1448
 id INT NOT NULL PRIMARY KEY,
1449
 b INT,
1450
 FOREIGN KEY (b) REFERENCES test.t1(id)
1451
) ENGINE=InnoDB;
1452
DROP TABLE t1;
1453
1454
#
1455
# Test that index column max sizes are honored (bug #13315)
1456
#
1457
1458
# prefix index
1459
create table t1 (col1 varchar(2000), index (col1(767)))
1460
 character set = latin1 engine = innodb;
1461
1462
# normal indexes
1463
create table t2 (col1 char(255), index (col1))
1464
 character set = latin1 engine = innodb;
1465
create table t3 (col1 binary(255), index (col1))
1466
 character set = latin1 engine = innodb;
1467
create table t4 (col1 varchar(767), index (col1))
1468
 character set = latin1 engine = innodb;
1469
create table t5 (col1 varchar(767) primary key)
1470
 character set = latin1 engine = innodb;
1471
create table t6 (col1 varbinary(767) primary key)
1472
 character set = latin1 engine = innodb;
1473
create table t7 (col1 text, index(col1(767)))
1474
 character set = latin1 engine = innodb;
1475
create table t8 (col1 blob, index(col1(767)))
1476
 character set = latin1 engine = innodb;
1477
1478
# multi-column indexes are allowed to be longer
1479
create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))
1480
 character set = latin1 engine = innodb;
1481
1482
show create table t9;
1483
1484
drop table t1, t2, t3, t4, t5, t6, t7, t8, t9;
1485
1486
# these should have their index length trimmed
1487
create table t1 (col1 varchar(768), index(col1))
1488
 character set = latin1 engine = innodb;
1489
create table t2 (col1 varbinary(768), index(col1))
1490
 character set = latin1 engine = innodb;
1491
create table t3 (col1 text, index(col1(768)))
1492
 character set = latin1 engine = innodb;
1493
create table t4 (col1 blob, index(col1(768)))
1494
 character set = latin1 engine = innodb;
1495
1496
show create table t1;
1497
1498
drop table t1, t2, t3, t4;
1499
1500
# these should be refused
1501
--error 1071
1502
create table t1 (col1 varchar(768) primary key)
1503
 character set = latin1 engine = innodb;
1504
--error 1071
1505
create table t2 (col1 varbinary(768) primary key)
1506
 character set = latin1 engine = innodb;
1507
--error 1071
1508
create table t3 (col1 text, primary key(col1(768)))
1509
 character set = latin1 engine = innodb;
1510
--error 1071
1511
create table t4 (col1 blob, primary key(col1(768)))
1512
 character set = latin1 engine = innodb;
1513
1514
#
1515
# Test improved foreign key error messages (bug #3443)
1516
#
1517
1518
CREATE TABLE t1
1519
(
1520
 id INT PRIMARY KEY
1521
) ENGINE=InnoDB;
1522
1523
CREATE TABLE t2
1524
(
1525
 v INT,
1526
 CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
1527
) ENGINE=InnoDB;
1528
1529
--error 1452
1530
INSERT INTO t2 VALUES(2);
1531
1532
INSERT INTO t1 VALUES(1);
1533
INSERT INTO t2 VALUES(1);
1534
1535
--error 1451
1536
DELETE FROM t1 WHERE id = 1;
1537
1538
--error 1217
1539
DROP TABLE t1;
1540
1541
SET FOREIGN_KEY_CHECKS=0;
1542
DROP TABLE t1;
1543
SET FOREIGN_KEY_CHECKS=1;
1544
1545
--error 1452
1546
INSERT INTO t2 VALUES(3);
1547
1548
DROP TABLE t2;
1549
#
1550
# Test that checksum table uses a consistent read Bug #12669
1551
#
1552
connect (a,localhost,root,,);
1553
connect (b,localhost,root,,);
1554
connection a;
1555
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
1556
insert into t1 values (1),(2);
1557
set autocommit=0;
1558
checksum table t1;
1559
connection b;
1560
insert into t1 values(3);
1561
connection a;
1562
#
1563
# Here checksum should not see insert
1564
#
1565
checksum table t1;
1566
connection a;
1567
commit;
1568
checksum table t1;
1569
commit;
1570
drop table t1;
1571
#
1572
# autocommit = 1
1573
#
1574
connection a;
1575
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
1576
insert into t1 values (1),(2);
1577
set autocommit=1;
1578
checksum table t1;
1579
connection b;
1580
set autocommit=1;
1581
insert into t1 values(3);
1582
connection a;
1583
#
1584
# Here checksum sees insert
1585
#
1586
checksum table t1;
1587
drop table t1;
1588
1589
connection default;
1590
disconnect a;
1591
disconnect b;
1592
1593
# tests for bugs #9802 and #13778
1594
1595
# test that FKs between invalid types are not accepted
1596
1597
set foreign_key_checks=0;
1598
create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
1599
# Embedded server doesn't chdir to data directory
1600
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1601
-- error 1005
1602
create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
1603
set foreign_key_checks=1;
1604
drop table t2;
1605
1606
# test that FKs between different charsets are not accepted in CREATE even
1607
# when f_k_c is 0
1608
1609
set foreign_key_checks=0;
1610
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
1611
# Embedded server doesn't chdir to data directory
1612
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1613
-- error 1005
1614
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8;
1615
set foreign_key_checks=1;
1616
drop table t1;
1617
1618
# test that invalid datatype conversions with ALTER are not allowed
1619
1620
set foreign_key_checks=0;
1621
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb;
1622
create table t1(a varchar(10) primary key) engine = innodb;
1623
-- error 1025,1025
1624
alter table t1 modify column a int;
1625
set foreign_key_checks=1;
1626
drop table t2,t1;
1627
1628
# test that charset conversions with ALTER are allowed when f_k_c is 0
1629
1630
set foreign_key_checks=0;
1631
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
1632
create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1;
1633
alter table t1 convert to character set utf8;
1634
set foreign_key_checks=1;
1635
drop table t2,t1;
1636
1637
# test that RENAME does not allow invalid charsets when f_k_c is 0
1638
1639
set foreign_key_checks=0;
1640
create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1;
1641
create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8;
1642
# Embedded server doesn't chdir to data directory
1643
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
1644
-- error 1025
1645
rename table t3 to t1;
1646
set foreign_key_checks=1;
1647
drop table t2,t3;
1648
1649
# test that foreign key errors are reported correctly (Bug #15550)
1650
1651
create table t1(a int primary key) row_format=redundant engine=innodb;
1652
create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb;
1653
create table t3(a int primary key) row_format=compact engine=innodb;
1654
create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb;
1655
1656
insert into t1 values(1);
1657
insert into t3 values(1);
1658
-- error 1452
1659
insert into t2 values(2);
1660
-- error 1452
1661
insert into t4 values(2);
1662
insert into t2 values(1);
1663
insert into t4 values(1);
1664
-- error 1451
1665
update t1 set a=2;
1666
-- error 1452
1667
update t2 set a=2;
1668
-- error 1451
1669
update t3 set a=2;
1670
-- error 1452
1671
update t4 set a=2;
1672
-- error 1451
1673
truncate t1;
1674
-- error 1451
1675
truncate t3;
1676
truncate t2;
1677
truncate t4;
1678
truncate t1;
1679
truncate t3;
1680
1681
drop table t4,t3,t2,t1;
1682
1683
1684
#
1685
# Test that we can create a large (>1K) key
1686
#
1687
create table t1 (a varchar(255) character set utf8,
1688
                 b varchar(255) character set utf8,
1689
                 c varchar(255) character set utf8,
1690
                 d varchar(255) character set utf8,
1691
                 key (a,b,c,d)) engine=innodb;
1692
drop table t1;
1693
--error ER_TOO_LONG_KEY
1694
create table t1 (a varchar(255) character set utf8,
1695
                 b varchar(255) character set utf8,
1696
                 c varchar(255) character set utf8,
1697
                 d varchar(255) character set utf8,
1698
                 e varchar(255) character set utf8,
1699
                 key (a,b,c,d,e)) engine=innodb;
1700
1701
1702
# test the padding of BINARY types and collations (Bug #14189)
1703
1704
create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
1705
create table t2 (s1 binary(2),primary key (s1)) engine=innodb;
1706
create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;
1707
create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb;
1708
1709
insert into t1 values (0x41),(0x4120),(0x4100);
1710
-- error ER_DUP_ENTRY
1711
insert into t2 values (0x41),(0x4120),(0x4100);
1712
insert into t2 values (0x41),(0x4120);
1713
-- error ER_DUP_ENTRY
1714
insert into t3 values (0x41),(0x4120),(0x4100);
1715
insert into t3 values (0x41),(0x4100);
1716
-- error ER_DUP_ENTRY
1717
insert into t4 values (0x41),(0x4120),(0x4100);
1718
insert into t4 values (0x41),(0x4100);
1719
select hex(s1) from t1;
1720
select hex(s1) from t2;
1721
select hex(s1) from t3;
1722
select hex(s1) from t4;
1723
drop table t1,t2,t3,t4;
1724
1725
create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb;
1726
create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
1727
1728
insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42);
1729
-- error 1452
1730
insert into t2 values(0x42);
1731
insert into t2 values(0x41);
1732
select hex(s1) from t2;
1733
update t1 set s1=0x123456 where a=2;
1734
select hex(s1) from t2;
1735
-- error 1451
1736
update t1 set s1=0x12 where a=1;
1737
-- error 1451
1738
update t1 set s1=0x12345678 where a=1;
1739
-- error 1451
1740
update t1 set s1=0x123457 where a=1;
1741
update t1 set s1=0x1220 where a=1;
1742
select hex(s1) from t2;
1743
update t1 set s1=0x1200 where a=1;
1744
select hex(s1) from t2;
1745
update t1 set s1=0x4200 where a=1;
1746
select hex(s1) from t2;
1747
-- error 1451
1748
delete from t1 where a=1;
1749
delete from t1 where a=2;
1750
update t2 set s1=0x4120;
1751
-- error 1451
1752
delete from t1;
1753
delete from t1 where a!=3;
1754
select a,hex(s1) from t1;
1755
select hex(s1) from t2;
1756
1757
drop table t2,t1;
1758
1759
create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb;
1760
create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb;
1761
1762
insert into t1 values(1,0x4100),(2,0x41);
1763
insert into t2 values(0x41);
1764
select hex(s1) from t2;
1765
update t1 set s1=0x1234 where a=1;
1766
select hex(s1) from t2;
1767
update t1 set s1=0x12 where a=2;
1768
select hex(s1) from t2;
1769
delete from t1 where a=1;
1770
-- error 1451
1771
delete from t1 where a=2;
1772
select a,hex(s1) from t1;
1773
select hex(s1) from t2;
1774
1775
drop table t2,t1;
1776
# Ensure that <tablename>_ibfk_0 is not mistreated as a
1777
# generated foreign key identifier.  (Bug #16387)
1778
1779
CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB;
1780
CREATE TABLE t2(a INT) ENGINE=InnoDB;
1781
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a);
1782
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1;
1783
ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a);
1784
ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0;
1785
SHOW CREATE TABLE t2;
1786
DROP TABLE t2,t1;
1787
1788
#
1789
# Test case for bug #16229: MySQL/InnoDB uses full explicit table locks in trigger processing
1790
#
1791
1792
connect (a,localhost,root,,);
1793
connect (b,localhost,root,,);
1794
connection a;
1795
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1796
insert into t1(a) values (1),(2),(3);
1797
commit;
1798
connection b;
1799
set autocommit = 0;
1800
update t1 set b = 5 where a = 2;
1801
connection a;
1802
delimiter |;
1803
create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |
1804
delimiter ;|
1805
set autocommit = 0;
1806
connection a;
1807
insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),
1808
(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),
1809
(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),
1810
(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),
1811
(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);
1812
connection b;
1813
commit;
1814
connection a;
1815
commit;
1816
drop trigger t1t;
1817
drop table t1;
1818
disconnect a;
1819
disconnect b;
1820
#
1821
# Another trigger test
1822
#
1823
connect (a,localhost,root,,);
1824
connect (b,localhost,root,,);
1825
connection a;
1826
create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1827
create table t2(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1828
create table t3(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1829
create table t4(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1830
create table t5(a int not null, b int, c int, d int, primary key(a)) engine=innodb;
1831
insert into t1(a) values (1),(2),(3);
1832
insert into t2(a) values (1),(2),(3);
1833
insert into t3(a) values (1),(2),(3);
1834
insert into t4(a) values (1),(2),(3);
1835
insert into t3(a) values (5),(7),(8);
1836
insert into t4(a) values (5),(7),(8);
1837
insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
1838
1839
delimiter |;
1840
create trigger t1t before insert on t1 for each row begin 
1841
    INSERT INTO t2 SET a = NEW.a;
1842
end |
1843
1844
create trigger t2t before insert on t2 for each row begin
1845
    DELETE FROM t3 WHERE a = NEW.a;
1846
end |
1847
1848
create trigger t3t before delete on t3 for each row begin  
1849
    UPDATE t4 SET b = b + 1 WHERE a = OLD.a;
1850
end |
1851
1852
create trigger t4t before update on t4 for each row begin
1853
    UPDATE t5 SET b = b + 1 where a = NEW.a;
1854
end |
1855
delimiter ;|
1856
commit;
1857
set autocommit = 0;
1858
update t1 set b = b + 5 where a = 1;
1859
update t2 set b = b + 5 where a = 1;
1860
update t3 set b = b + 5 where a = 1;
1861
update t4 set b = b + 5 where a = 1;
1862
insert into t5(a) values(20);
1863
connection b;
1864
set autocommit = 0;
1865
insert into t1(a) values(7);
1866
insert into t2(a) values(8);
1867
delete from t2 where a = 3;
1868
update t4 set b = b + 1 where a = 3;
1869
commit;
1870
drop trigger t1t;
1871
drop trigger t2t;
1872
drop trigger t3t;
1873
drop trigger t4t;
1874
drop table t1, t2, t3, t4, t5;
1875
connection default;
1876
disconnect a;
1877
disconnect b;
1878
1879
#
1880
# Test that cascading updates leading to duplicate keys give the correct
1881
# error message (bug #9680)
1882
#
1883
1884
CREATE TABLE t1 (
1885
  field1 varchar(8) NOT NULL DEFAULT '',
1886
  field2 varchar(8) NOT NULL DEFAULT '',
1887
  PRIMARY KEY  (field1, field2)
1888
) ENGINE=InnoDB;
1889
1890
CREATE TABLE t2 (
1891
  field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY,
1892
  FOREIGN KEY (field1) REFERENCES t1 (field1)
1893
    ON DELETE CASCADE ON UPDATE CASCADE
1894
) ENGINE=InnoDB;
1895
1896
INSERT INTO t1 VALUES ('old', 'somevalu');
1897
INSERT INTO t1 VALUES ('other', 'anyvalue');
1898
1899
INSERT INTO t2 VALUES ('old');
1900
INSERT INTO t2 VALUES ('other');
1901
1902
--error ER_FOREIGN_DUPLICATE_KEY
1903
UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu';
1904
1905
DROP TABLE t2;
1906
DROP TABLE t1;
1907
1908
#
1909
# Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE
1910
#
1911
create table t1 (
1912
  c1 bigint not null,
1913
  c2 bigint not null,
1914
  primary key (c1),
1915
  unique  key (c2)
1916
) engine=innodb;
1917
#
1918
create table t2 (
1919
  c1 bigint not null,
1920
  primary key (c1)
1921
) engine=innodb;
1922
#
1923
alter table t1 add constraint c2_fk foreign key (c2)
1924
  references t2(c1) on delete cascade;
1925
show create table t1;
1926
#
1927
alter table t1 drop foreign key c2_fk;
1928
show create table t1;
1929
#
1930
drop table t1, t2;
1931
1932
#
1933
# Bug #14360: problem with intervals
1934
#
1935
1936
create table t1(a date) engine=innodb;
1937
create table t2(a date, key(a)) engine=innodb;
1938
insert into t1 values('2005-10-01');
1939
insert into t2 values('2005-10-01');
1940
select * from t1, t2
1941
  where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;
1942
drop table t1, t2;
1943
1944
create table t1 (id int not null, f_id int not null, f int not null,
1945
primary key(f_id, id)) engine=innodb;
1946
create table t2 (id int not null,s_id int not null,s varchar(200),
1947
primary key(id)) engine=innodb;
1948
INSERT INTO t1 VALUES (8, 1, 3);
1949
INSERT INTO t1 VALUES (1, 2, 1);
1950
INSERT INTO t2 VALUES (1, 0, '');
1951
INSERT INTO t2 VALUES (8, 1, '');
1952
commit;
1953
DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)
1954
WHERE mm.id IS NULL;
1955
select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)
1956
where mm.id is null lock in share mode;
1957
drop table t1,t2;
1958
1959
#
1960
# Test case where X-locks on unused rows should be released in a
1961
# update (because READ COMMITTED isolation level)
1962
#
1963
1964
connect (a,localhost,root,,);
1965
connect (b,localhost,root,,);
1966
connection a;
1967
create table t1(a int not null, b int, primary key(a)) engine=innodb;
1968
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
1969
commit;
1970
set autocommit = 0; 
1971
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
1972
update t1 set b = 5 where b = 1;
1973
connection b;
1974
set autocommit = 0;
1975
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
1976
#
1977
# X-lock to record (7,3) should be released in a update 
1978
#
1979
select * from t1 where a = 7 and b = 3 for update;
1980
connection a;
1981
commit;
1982
connection b;
1983
commit;
1984
drop table t1;
1985
connection default;
1986
disconnect a;
1987
disconnect b;
1988
1989
#
1990
# Test case where no locks should be released (because we are not
1991
# using READ COMMITTED isolation level)
1992
#
1993
1994
connect (a,localhost,root,,);
1995
connect (b,localhost,root,,);
1996
connection a;
1997
create table t1(a int not null, b int, primary key(a)) engine=innodb;
1998
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2);
1999
commit;
2000
set autocommit = 0; 
2001
select * from t1 lock in share mode;
2002
update t1 set b = 5 where b = 1;
2003
connection b;
2004
set autocommit = 0;
2005
#
2006
# S-lock to records (2,2),(4,2), and (6,2) should not be released in a update
2007
#
2008
--error 1205
2009
select * from t1 where a = 2 and b = 2 for update;
2010
#
2011
# X-lock to record (1,1),(3,1),(5,1) should not be released in a update
2012
#
2013
--error 1205
2014
connection a;
2015
commit;
2016
connection b;
2017
commit;
2018
connection default;
2019
disconnect a;
2020
disconnect b;
2021
drop table t1;
2022
2023
#
2024
# Consistent read should be used in following selects
2025
#
2026
# 1) INSERT INTO ... SELECT
2027
# 2) UPDATE ... = ( SELECT ...)
2028
# 3) CREATE ... SELECT
2029
2030
connect (a,localhost,root,,);
2031
connect (b,localhost,root,,);
2032
connection a;
2033
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2034
insert into t1 values (1,2),(5,3),(4,2);
2035
create table t2(d int not null, e int, primary key(d)) engine=innodb;
2036
insert into t2 values (8,6),(12,1),(3,1);
2037
commit;
2038
set autocommit = 0;
2039
select * from t2 for update;
2040
connection b;
2041
set autocommit = 0;
2042
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2043
insert into t1 select * from t2;
2044
update t1 set b = (select e from t2 where a = d);
2045
create table t3(d int not null, e int, primary key(d)) engine=innodb
2046
select * from t2;
2047
commit;
2048
connection a;
2049
commit;
2050
connection default;
2051
disconnect a;
2052
disconnect b;
2053
drop table t1, t2, t3;
2054
2055
#
2056
# Consistent read should not be used if 
2057
#
2058
# (a) isolation level is serializable OR
2059
# (b) select ... lock in share mode OR
2060
# (c) select ... for update
2061
#
2062
# in following queries:
2063
#
2064
# 1) INSERT INTO ... SELECT
2065
# 2) UPDATE ... = ( SELECT ...)
2066
# 3) CREATE ... SELECT
2067
2068
connect (a,localhost,root,,);
2069
connect (b,localhost,root,,);
2070
connect (c,localhost,root,,);
2071
connect (d,localhost,root,,);
2072
connect (e,localhost,root,,);
2073
connect (f,localhost,root,,);
2074
connect (g,localhost,root,,);
2075
connect (h,localhost,root,,);
2076
connect (i,localhost,root,,);
2077
connect (j,localhost,root,,);
2078
connection a;
2079
create table t1(a int not null, b int, primary key(a)) engine=innodb;
2080
insert into t1 values (1,2),(5,3),(4,2);
2081
create table t2(a int not null, b int, primary key(a)) engine=innodb;
2082
insert into t2 values (8,6),(12,1),(3,1);
2083
create table t3(d int not null, b int, primary key(d)) engine=innodb;
2084
insert into t3 values (8,6),(12,1),(3,1);
2085
create table t5(a int not null, b int, primary key(a)) engine=innodb;
2086
insert into t5 values (1,2),(5,3),(4,2);
2087
create table t6(d int not null, e int, primary key(d)) engine=innodb;
2088
insert into t6 values (8,6),(12,1),(3,1);
2089
create table t8(a int not null, b int, primary key(a)) engine=innodb;
2090
insert into t8 values (1,2),(5,3),(4,2);
2091
create table t9(d int not null, e int, primary key(d)) engine=innodb;
2092
insert into t9 values (8,6),(12,1),(3,1);
2093
commit;
2094
set autocommit = 0;
2095
select * from t2 for update;
2096
connection b;
2097
set autocommit = 0;
2098
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2099
--send
2100
insert into t1 select * from t2;
2101
connection c;
2102
set autocommit = 0;
2103
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2104
--send
2105
update t3 set b = (select b from t2 where a = d);
2106
connection d;
2107
set autocommit = 0;
2108
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2109
--send
2110
create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
2111
connection e;
2112
set autocommit = 0;
2113
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2114
--send
2115
insert into t5 (select * from t2 lock in share mode);
2116
connection f;
2117
set autocommit = 0;
2118
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2119
--send
2120
update t6 set e = (select b from t2 where a = d lock in share mode);
2121
connection g;
2122
set autocommit = 0;
2123
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2124
--send
2125
create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
2126
connection h;
2127
set autocommit = 0;
2128
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2129
--send
2130
insert into t8 (select * from t2 for update);
2131
connection i;
2132
set autocommit = 0;
2133
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2134
--send
2135
update t9 set e = (select b from t2 where a = d for update);
2136
connection j;
2137
set autocommit = 0;
2138
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2139
--send
2140
create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
2141
2142
connection b;
2143
--error 1205
2144
reap;
2145
2146
connection c;
2147
--error 1205
2148
reap;
2149
2150
connection d;
2151
--error 1205
2152
reap;
2153
2154
connection e;
2155
--error 1205
2156
reap;
2157
2158
connection f;
2159
--error 1205
2160
reap;
2161
2162
connection g;
2163
--error 1205
2164
reap;
2165
2166
connection h;
2167
--error 1205
2168
reap;
2169
2170
connection i;
2171
--error 1205
2172
reap;
2173
2174
connection j;
2175
--error 1205
2176
reap;
2177
2178
connection a;
2179
commit;
2180
2181
connection default;
2182
disconnect a;
2183
disconnect b;
2184
disconnect c;
2185
disconnect d;
2186
disconnect e;
2187
disconnect f;
2188
disconnect g;
2189
disconnect h;
2190
disconnect i;
2191
disconnect j;
2192
drop table t1, t2, t3, t5, t6, t8, t9;
2193
2194
# bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID"
2195
--error 1005
2196
CREATE TABLE t1 (DB_ROW_ID int) engine=innodb;
2197
2198
#
2199
# Bug #17152: Wrong result with BINARY comparison on aliased column
2200
#
2201
2202
CREATE TABLE t1 (
2203
   a BIGINT(20) NOT NULL,
2204
    PRIMARY KEY  (a)
2205
 ) ENGINE=INNODB DEFAULT CHARSET=UTF8;
2206
2207
CREATE TABLE t2 (
2208
  a BIGINT(20) NOT NULL,
2209
  b VARCHAR(128) NOT NULL,
2210
  c TEXT NOT NULL,
2211
  PRIMARY KEY  (a,b),
2212
  KEY idx_t2_b_c (b,c(200)),
2213
  CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a) 
2214
   ON DELETE CASCADE
2215
 ) ENGINE=INNODB DEFAULT CHARSET=UTF8;
2216
2217
INSERT INTO t1 VALUES (1);
2218
INSERT INTO t2 VALUES (1, 'bar', 'vbar');
2219
INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR');
2220
INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi');
2221
INSERT INTO t2 VALUES (1, 'customer_over', '1');
2222
2223
SELECT * FROM t2 WHERE b = 'customer_over';
2224
SELECT * FROM t2 WHERE BINARY b = 'customer_over';
2225
SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over';
2226
/* Bang: Empty result set, above was expected: */
2227
SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
2228
SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over';
2229
2230
drop table t2, t1;
2231
2232
#
2233
# Test optimize on table with open transaction
2234
#
2235
2236
CREATE TABLE t1 ( a int ) ENGINE=innodb;
2237
BEGIN;
2238
INSERT INTO t1 VALUES (1);
2239
OPTIMIZE TABLE t1;
2240
DROP TABLE t1;
2241
2242
#
2243
# Bug #24741 (existing cascade clauses disappear when adding foreign keys)
2244
#
2245
2246
CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB;
2247
2248
CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL,
2249
  CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id)
2250
  ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
2251
2252
ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON
2253
DELETE CASCADE ON UPDATE CASCADE;
2254
2255
SHOW CREATE TABLE t2;
2256
DROP TABLE t2, t1;
2257
2258
#
2259
# Bug #25927: Prevent ALTER TABLE ... MODIFY ... NOT NULL on columns
2260
# for which there is a foreign key constraint ON ... SET NULL.
2261
#
2262
2263
CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB;
2264
CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB;
2265
INSERT INTO t1 VALUES (1);
2266
INSERT INTO t2 VALUES (1);
2267
ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL;
2268
# mysqltest first does replace_regex, then replace_result
2269
--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/
2270
# Embedded server doesn't chdir to data directory
2271
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
2272
--error 1025
2273
ALTER TABLE t2 MODIFY a INT NOT NULL;
2274
DELETE FROM t1;
2275
DROP TABLE t2,t1;
2276
2277
#
2278
# Bug #26835: table corruption after delete+insert
2279
#
2280
2281
CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY)
2282
ENGINE=InnoDB;
2283
INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4);
2284
DELETE FROM t1;
2285
INSERT INTO t1 VALUES ('DDD');
2286
SELECT * FROM t1;
2287
DROP TABLE t1;
2288
2289
#
2290
# Bug #23313 (AUTO_INCREMENT=# not reported back for InnoDB tables)
2291
# Bug #21404 (AUTO_INCREMENT value reset when Adding FKEY (or ALTER?))
2292
#
2293
2294
CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB
2295
AUTO_INCREMENT=42;
2296
2297
INSERT INTO t1 VALUES (0),(347),(0);
2298
SELECT * FROM t1;
2299
2300
SHOW CREATE TABLE t1;
2301
2302
CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;
2303
INSERT INTO t2 VALUES(42),(347),(348);
2304
ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id);
2305
SHOW CREATE TABLE t1;
2306
2307
DROP TABLE t1,t2;
2308
2309
#
2310
# Bug #21101 (Prints wrong error message if max row size is too large)
2311
#
2312
--error 1118
2313
CREATE TABLE t1 (
2314
	c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255),
2315
	c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255),
2316
	c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255),
2317
	c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255),
2318
	c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255),
2319
	c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255),
2320
	c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255),
2321
	c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255)
2322
	) ENGINE = InnoDB;
2323
2324
#
2325
# Bug #31860 InnoDB assumes AUTOINC values can only be positive.
2326
#
2327
DROP TABLE IF EXISTS t1;
2328
CREATE TABLE t1(
2329
	id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY
2330
	) ENGINE=InnoDB;
2331
INSERT INTO t1 VALUES(-10);
2332
SELECT * FROM t1;
2333
#
2334
# NOTE: The server really needs to be restarted at this point
2335
# for the test to be useful.  
2336
#
2337
# Without the fix InnoDB would trip over an assertion here.
2338
INSERT INTO t1 VALUES(NULL);
2339
# The next value should be 1 and not -9 or a -ve number
2340
SELECT * FROM t1;
2341
DROP TABLE t1;
2342
2343
# 
2344
# Bug #21409 Incorrect result returned when in READ-COMMITTED with
2345
# query_cache ON
2346
#
2347
CONNECT (c1,localhost,root,,);
2348
CONNECT (c2,localhost,root,,);
2349
CONNECTION c1;
2350
SET TX_ISOLATION='read-committed';
2351
SET AUTOCOMMIT=0;
2352
DROP TABLE IF EXISTS t1, t2;
2353
CREATE TABLE t1 ( a int ) ENGINE=InnoDB;
2354
CREATE TABLE t2 LIKE t1;
2355
SELECT * FROM t2;
2356
CONNECTION c2;
2357
SET TX_ISOLATION='read-committed';
2358
SET AUTOCOMMIT=0;
2359
INSERT INTO t1 VALUES (1);
2360
COMMIT;
2361
CONNECTION c1;
2362
SELECT * FROM t1 WHERE a=1;
2363
DISCONNECT c1;
2364
DISCONNECT c2;
2365
CONNECT (c1,localhost,root,,);
2366
CONNECT (c2,localhost,root,,);
2367
CONNECTION c1;
2368
SET TX_ISOLATION='read-committed';
2369
SET AUTOCOMMIT=0;
2370
SELECT * FROM t2;
2371
CONNECTION c2;
2372
SET TX_ISOLATION='read-committed';
2373
SET AUTOCOMMIT=0;
2374
INSERT INTO t1 VALUES (2);
2375
COMMIT;
2376
CONNECTION c1;
2377
# The result set below should be the same for both selects
2378
SELECT * FROM t1 WHERE a=2;
2379
SELECT * FROM t1 WHERE a=2;
2380
DROP TABLE t1;
2381
DROP TABLE t2;
2382
DISCONNECT c1;
2383
DISCONNECT c2;
2384
2385
#######################################################################
2386
#                                                                     #
2387
# Please, DO NOT TOUCH this file as well as the innodb.result file.   #
2388
# These files are to be modified ONLY BY INNOBASE guys.               #
2389
#                                                                     #
2390
# Use innodb_mysql.[test|result] files instead.                       #
2391
#                                                                     #
2392
# If nevertheless you need to make some changes here, please, forward #
2393
# your commit message To: dev@innodb.com Cc: dev-innodb@mysql.com     #
2394
# (otherwise your changes may be erased).                             #
2395
#                                                                     #
2396
#######################################################################