diff options
Diffstat (limited to 'mysql-test/t')
76 files changed, 416 insertions, 408 deletions
diff --git a/mysql-test/t/alias.test b/mysql-test/t/alias.test index e0fa10d56d5..852b78dd04e 100644 --- a/mysql-test/t/alias.test +++ b/mysql-test/t/alias.test @@ -50,7 +50,7 @@ CREATE TABLE t1 ( KEY mcbs_aufnr(mcbs_aufnr), KEY kundentyp(kundentyp), KEY p_nr(p_nr,suffix) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES (3359356,405,3359356,'Mustermann Musterfrau',52500,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1485525,2122316,'+','','N',1909160,'MobilComSuper92000D2',NULL,NULL,'MS9ND2',3,24,'MobilCom Shop Koeln',52500,NULL,'auto',20010202105916,'Mobilfunk','PP','','',''); INSERT INTO t1 VALUES (3359357,468,3359357,'Mustermann Musterfrau',7001,'2000-05-20','workflow','Auftrag erledigt','Originalvertrag eingegangen und geprüft','','privat',1503580,2139699,'+','','P',1909171,'MobilComSuper9D1T10SFreisprech(Akquise)',NULL,NULL,'MS9NS1',327,24,'MobilCom Intern',7003,NULL,'auto',20010202105916,'Mobilfunk','PP','','',''); diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 11fe997e789..eab4fd7f5f0 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -61,7 +61,7 @@ CREATE TABLE t1 ( user_id int(11) unsigned NOT NULL default '0', status enum('new','old') NOT NULL default 'new', PRIMARY KEY (id) -) TYPE=MyISAM; +) ENGINE=MyISAM; ALTER TABLE t1 ORDER BY t1.id, t1.status, t1.type_id, t1.user_id, t1.body; DROP TABLE t1; @@ -70,7 +70,7 @@ DROP TABLE t1; # The following combination found a hang-bug in MyISAM # -CREATE TABLE t1 (AnamneseId int(10) unsigned NOT NULL auto_increment,B BLOB,PRIMARY KEY (AnamneseId)) type=myisam; +CREATE TABLE t1 (AnamneseId int(10) unsigned NOT NULL auto_increment,B BLOB,PRIMARY KEY (AnamneseId)) engine=myisam; insert into t1 values (null,"hello"); LOCK TABLES t1 WRITE; ALTER TABLE t1 ADD Column new_col int not null; @@ -191,7 +191,7 @@ CREATE TABLE t1 ( Host varchar(16) binary NOT NULL default '', User varchar(16) binary NOT NULL default '', PRIMARY KEY (Host,User) -) TYPE=MyISAM; +) ENGINE=MyISAM; ALTER TABLE t1 DISABLE KEYS; LOCK TABLES t1 WRITE; @@ -211,7 +211,7 @@ CREATE TABLE t1 ( User varchar(16) binary NOT NULL default '', PRIMARY KEY (Host,User), KEY (Host) -) TYPE=MyISAM; +) ENGINE=MyISAM; ALTER TABLE t1 DISABLE KEYS; SHOW INDEX FROM t1; @@ -238,7 +238,7 @@ CREATE TABLE t1 ( User varchar(16) binary NOT NULL default '', PRIMARY KEY (Host,User), KEY (Host) -) TYPE=MyISAM; +) ENGINE=MyISAM; LOCK TABLES t1 WRITE; ALTER TABLE t1 DISABLE KEYS; diff --git a/mysql-test/t/auto_increment.test b/mysql-test/t/auto_increment.test index d02850f6745..6cf286f86c0 100644 --- a/mysql-test/t/auto_increment.test +++ b/mysql-test/t/auto_increment.test @@ -6,7 +6,7 @@ drop table if exists t1; --enable_warnings SET SQL_WARNINGS=1; -create table t1 (a int not null auto_increment,b int, primary key (a)) type=myisam auto_increment=3; +create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3; insert into t1 values (1,1),(NULL,3),(NULL,4); delete from t1 where a=4; insert into t1 values (NULL,5),(NULL,6); diff --git a/mysql-test/t/bdb-alter-table-1.test b/mysql-test/t/bdb-alter-table-1.test index 7537cc358e8..18b6c70758f 100644 --- a/mysql-test/t/bdb-alter-table-1.test +++ b/mysql-test/t/bdb-alter-table-1.test @@ -6,7 +6,7 @@ drop table if exists t1; --enable_warnings -create table t1(objid BIGINT not null, tablename varchar(64), oid BIGINT not null, test BIGINT, PRIMARY KEY (objid), UNIQUE(tablename)) type=BDB; +create table t1(objid BIGINT not null, tablename varchar(64), oid BIGINT not null, test BIGINT, PRIMARY KEY (objid), UNIQUE(tablename)) engine=BDB; insert into t1 values(1, 't1',4,9); insert into t1 values(2, 'metatable',1,9); insert into t1 values(3, 'metaindex',1,9 ); diff --git a/mysql-test/t/bdb-crash.test b/mysql-test/t/bdb-crash.test index d77de901a30..fffed379e79 100644 --- a/mysql-test/t/bdb-crash.test +++ b/mysql-test/t/bdb-crash.test @@ -20,7 +20,7 @@ DEFAULT 'New' NOT NULL, PRIMARY KEY (ChargeID), KEY ServiceID (ServiceID), KEY ChargeDate (ChargeDate) -) type=BDB; +) engine=BDB; BEGIN; INSERT INTO t1 diff --git a/mysql-test/t/bdb-deadlock.test b/mysql-test/t/bdb-deadlock.test index 2453c42e49a..d86403fcffc 100644 --- a/mysql-test/t/bdb-deadlock.test +++ b/mysql-test/t/bdb-deadlock.test @@ -15,8 +15,8 @@ connect (con2,localhost,root,,); drop table if exists t1,t2; --enable_warnings connection con1; -create table t1 (id integer, x integer) type=BDB; -create table t2 (id integer, x integer) type=BDB; +create table t1 (id integer, x integer) engine=BDB; +create table t2 (id integer, x integer) engine=BDB; insert into t1 values(0, 0); insert into t2 values(0, 0); set autocommit=0; diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test index a0fbc56a9da..44bb753d6a1 100644 --- a/mysql-test/t/bdb.test +++ b/mysql-test/t/bdb.test @@ -7,7 +7,7 @@ --disable_warnings drop table if exists t1,t2,t3,t4,t5,t6,t7,t8; --enable_warnings -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)) type=bdb; +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=bdb; insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt'); select id, code, name from t1 order by id; @@ -30,7 +30,7 @@ CREATE TABLE t1 ( PRIMARY KEY (id), KEY parent_id (parent_id), KEY level (level) -) type=bdb; +) engine=bdb; 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); update t1 set parent_id=parent_id+100; select * from t1 where parent_id=102; @@ -59,7 +59,7 @@ CREATE TABLE t1 ( gesuchnr int(11) DEFAULT '0' NOT NULL, benutzer_id int(11) DEFAULT '0' NOT NULL, PRIMARY KEY (gesuchnr,benutzer_id) -) type=BDB; +) engine=BDB; replace into t1 (gesuchnr,benutzer_id) values (2,1); replace into t1 (gesuchnr,benutzer_id) values (1,1); @@ -68,7 +68,7 @@ select * from t1; drop table t1; # test for bug in replace with secondary key -create table t1 (id int not null primary key, x int not null, key (x)) type=bdb; +create table t1 (id int not null primary key, x int not null, key (x)) engine=bdb; insert into t1 (id, x) values (1, 1); replace into t1 (id, x) values (1, 2); select * from t1; @@ -78,7 +78,7 @@ drop table t1; # test delete using hidden_primary_key # -create table t1 (a int) type=bdb; +create table t1 (a int) engine=bdb; insert into t1 values (1), (2); optimize table t1; delete from t1 where a = 1; @@ -86,7 +86,7 @@ select * from t1; check table t1; drop table t1; -create table t1 (a int,b varchar(20)) type=bdb; +create table t1 (a int,b varchar(20)) engine=bdb; insert into t1 values (1,""), (2,"testing"); delete from t1 where a = 1; select * from t1; @@ -98,7 +98,7 @@ drop table t1; # Test of reading on secondary key with may be null -create table t1 (a int,b varchar(20),key(a)) type=bdb; +create table t1 (a int,b varchar(20),key(a)) engine=bdb; insert into t1 values (1,""), (2,"testing"); select * from t1 where a = 1; drop table t1; @@ -107,7 +107,7 @@ drop table t1; # Test auto_increment on sub key # -create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b)) type=BDB; +create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b)) engine=BDB; insert into t1 values ("a",1),("b",2),("a",2),("c",1); insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL); insert into t1 (a) values ("a"),("b"),("c"),("d"); @@ -123,7 +123,7 @@ drop table t1; # Test rollback # -create table t1 (n int not null primary key) type=bdb; +create table t1 (n int not null primary key) engine=bdb; set autocommit=0; insert into t1 values (4); rollback; @@ -150,7 +150,7 @@ drop table t1; # Testing transactions # -create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) type=BDB; +create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=BDB; begin; insert into t1 values(1,'hamdouni'); select id as afterbegin_id,nom as afterbegin_nom from t1; @@ -168,7 +168,7 @@ drop table t1; # Simple not autocommit test # -CREATE TABLE t1 (id char(8) not null primary key, val int not null) type=bdb; +CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=bdb; insert into t1 values ('pippo', 12); -- error 1062 insert into t1 values ('pippo', 12); # Gives error @@ -191,12 +191,12 @@ set autocommit=1; # The following simple tests failed at some point # -CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) TYPE=BDB; +CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=BDB; INSERT INTO t1 VALUES (1, 'Jochen'); select * from t1; drop table t1; -CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) TYPE=BDB; +CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=BDB; set autocommit=0; INSERT INTO t1 SET _userid='marc@anyware.co.uk'; COMMIT; @@ -215,7 +215,7 @@ CREATE TABLE t1 ( ref_email varchar(100) DEFAULT '' NOT NULL, detail varchar(200), PRIMARY KEY (user_id,ref_email) -)type=bdb; +)engine=bdb; 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'); select * from t1 where user_id=10292; @@ -240,7 +240,7 @@ drop table t1; # create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)); -alter table t1 type=BDB; +alter table t1 engine=BDB; insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4'); select * from t1; update t1 set col2='7' where col1='4'; @@ -255,8 +255,8 @@ drop table t1; # INSERT INTO BDB tables # -create table t1 (a int not null , b int, primary key (a)) type = BDB; -create table t2 (a int not null , b int, primary key (a)) type = myisam; +create table t1 (a int not null , b int, primary key (a)) engine = BDB; +create table t2 (a int not null , b int, primary key (a)) engine = myisam; insert into t1 VALUES (1,3) , (2,3), (3,3); select * from t1; insert into t2 select * from t1; @@ -279,7 +279,7 @@ CREATE TABLE t1 ( passwd varchar(32) binary DEFAULT '' NOT NULL, PRIMARY KEY (id), UNIQUE ggid (ggid) -) TYPE=BDB; +) ENGINE=BDB; insert into t1 (ggid,passwd) values ('test1','xxx'); insert into t1 (ggid,passwd) values ('test2','yyy'); @@ -317,7 +317,7 @@ CREATE TABLE t1 ( approved datetime, dummy_primary_key int(11) NOT NULL auto_increment, PRIMARY KEY (dummy_primary_key) -) TYPE=BDB; +) ENGINE=BDB; INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1); INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2); 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); @@ -337,7 +337,7 @@ CREATE TABLE t1 ( KEY (id), KEY parent_id (parent_id), KEY level (level) -) type=bdb; +) engine=bdb; 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); INSERT INTO t1 values (179,5,2); update t1 set parent_id=parent_id+100; @@ -370,7 +370,7 @@ CREATE TABLE t1 ( sca_sch_desc varchar(16), PRIMARY KEY (sca_code, cat_code, lan_code), INDEX sca_pic (sca_pic) -) type = bdb ; +) engine = bdb ; 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'); select count(*) from t1 where sca_code = 'PD'; @@ -392,7 +392,7 @@ drop table t1; # Test of opening table twice and timestamps # set @a:=now(); -CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) type=bdb; +CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=bdb; insert into t1 (a) values(1),(2),(3); select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a; update t1 set a=5 where a=1; @@ -407,7 +407,7 @@ flush logs; # # Test key on blob with null values # -create table t1 (b blob, i int, key (b(100)), key (i), key (i, b(20))) type=bdb; +create table t1 (b blob, i int, key (b(100)), key (i), key (i, b(20))) engine=bdb; insert into t1 values ('this is a blob', 1), (null, -1), (null, null),("",1),("",2),("",3); select b from t1 where b = 'this is a blob'; select * from t1 where b like 't%'; @@ -421,7 +421,7 @@ drop table t1; # # Test with variable length primary key # -create table t1 (a varchar(100) not null, primary key(a), b int not null) type=bdb; +create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=bdb; insert into t1 values("hello",1),("world",2); select * from t1 order by b desc; optimize table t1; @@ -431,7 +431,7 @@ drop table t1; # # Test of bug in create index with NULL columns # -create table t1 (i int, j int )TYPE=BDB; +create table t1 (i int, j int )ENGINE=BDB; insert into t1 values (1,2); select * from t1 where i=1 and j=2; create index ax1 on t1 (i,j); @@ -451,7 +451,7 @@ create table t1 unique branch_name(branch_name), index branch_active(branch_active) -) type=bdb; +) engine=bdb; create table t2 ( target_id int auto_increment primary key, @@ -460,7 +460,7 @@ create table t2 unique target_name(target_name), index target_active(target_active) -) type=bdb; +) engine=bdb; create table t3 ( platform_id int auto_increment primary key, @@ -469,7 +469,7 @@ create table t3 unique platform_name(platform_name), index platform_active(platform_active) -) type=bdb; +) engine=bdb; create table t4 ( product_id int auto_increment primary key, @@ -479,7 +479,7 @@ create table t4 unique product_name(product_name), index product_active(product_active) -) type=bdb; +) engine=bdb; create table t5 ( product_file_id int auto_increment primary key, @@ -492,7 +492,7 @@ create table t5 unique product_file(product_id,file_name), index file_included(file_included) -) type=bdb; +) engine=bdb; create table t6 ( file_platform_id int auto_increment primary key, @@ -505,7 +505,7 @@ create table t6 archive_filename varchar(255) not null, unique file_platform(product_file_id,platform_id,branch_id) -) type=bdb; +) engine=bdb; create table t8 ( archive_id int auto_increment primary key, @@ -517,7 +517,7 @@ create table t8 unique archive(branch_id,target_id,platform_id,product_id), index status_id(status_id) -) type=bdb; +) engine=bdb; create table t7 ( build_id int auto_increment primary key, @@ -531,7 +531,7 @@ create table t7 build_path text not null, unique build(branch_id,target_id,build_number) -) type=bdb; +) engine=bdb; insert into t1 (branch_name) values ('RealMedia'); @@ -659,7 +659,7 @@ CREATE TABLE t1 ( a tinytext NOT NULL, b tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (a(32),b) -) TYPE=BDB; +) ENGINE=BDB; INSERT INTO t1 VALUES ('a',1),('a',2); SELECT * FROM t1 WHERE a='a' AND b=2; SELECT * FROM t1 WHERE a='a' AND b in (2); @@ -674,7 +674,7 @@ CREATE TABLE t1 ( a int3 unsigned NOT NULL, b int1 unsigned NOT NULL, UNIQUE (a, b) -) TYPE = BDB; +) ENGINE = BDB; INSERT INTO t1 VALUES (1, 1); SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1; @@ -684,7 +684,7 @@ drop table t1; # Test problem with BDB and lock tables with duplicate write. # -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)) type=bdb; +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=bdb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); LOCK TABLES t1 WRITE; --error 1062 @@ -694,7 +694,7 @@ select id from t1; UNLOCK TABLES; DROP TABLE t1; -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)) type=bdb; +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=bdb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); LOCK TABLES t1 WRITE; begin; @@ -711,8 +711,8 @@ DROP TABLE t1; # Test with empty tables (crashed with lock error) # -CREATE TABLE t1 (SYAIN_NO char(5) NOT NULL default '', KINMU_DATE char(6) NOT NULL default '', PRIMARY KEY (SYAIN_NO,KINMU_DATE)) TYPE=BerkeleyDB; -CREATE TABLE t2 ( SYAIN_NO char(5) NOT NULL default '',STR_DATE char(8) NOT NULL default '',PRIMARY KEY (SYAIN_NO,STR_DATE) ) TYPE=BerkeleyDB; +CREATE TABLE t1 (SYAIN_NO char(5) NOT NULL default '', KINMU_DATE char(6) NOT NULL default '', PRIMARY KEY (SYAIN_NO,KINMU_DATE)) ENGINE=BerkeleyDB; +CREATE TABLE t2 ( SYAIN_NO char(5) NOT NULL default '',STR_DATE char(8) NOT NULL default '',PRIMARY KEY (SYAIN_NO,STR_DATE) ) ENGINE=BerkeleyDB; select T1.KINMU_DATE from t1 T1 ,t2 T2 where T1.SYAIN_NO = '12345' and T1.KINMU_DATE = '200106' and T2.SYAIN_NO = T1.SYAIN_NO; select T1.KINMU_DATE from t1 T1 ,t2 T2 where T1.SYAIN_NO = '12345' and T1.KINMU_DATE = '200106' and T2.SYAIN_NO = T1.SYAIN_NO; DROP TABLE t1,t2; @@ -721,7 +721,7 @@ DROP TABLE t1,t2; # Test problem with joining table to itself on a multi-part unique key # -create table t1 (a int(11) not null, b int(11) not null, unique (a,b)) type=bdb; +create table t1 (a int(11) not null, b int(11) not null, unique (a,b)) engine=bdb; insert into t1 values (1,1), (1,2); select * from t1 where a = 1; select t1.*, t2.* from t1, t1 t2 where t1.a = t2.a and t2.a = 1; @@ -732,9 +732,9 @@ drop table t1; # This caused a deadlock in BDB internal locks # -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)) type=bdb; +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=bdb; insert into t1 values (0,0,0,'ABCDEFGHIJ'); -create table t2 (id int NOT NULL,primary key (id)) type=bdb; +create table t2 (id int NOT NULL,primary key (id)) engine=bdb; LOCK TABLES t1 WRITE, t2 WRITE; insert into t2 values(1); SELECT t1.* FROM t1 WHERE id IN (1); @@ -746,7 +746,7 @@ drop table t1,t2; # Test problems with NULL # -CREATE TABLE t1 (i varchar(48) NOT NULL default '', p varchar(255) default NULL,s varchar(48) NOT NULL default '', PRIMARY KEY (i), UNIQUE(p,s)) TYPE=BDB; +CREATE TABLE t1 (i varchar(48) NOT NULL default '', p varchar(255) default NULL,s varchar(48) NOT NULL default '', PRIMARY KEY (i), UNIQUE(p,s)) ENGINE=BDB; INSERT INTO t1 VALUES ('00000000-e6c4ddeaa6-003b8-83458387','programs/xxxxxxxx.wmv','00000000-e6c4ddeb32-003bc-83458387'); SELECT * FROM t1 WHERE p='programs/xxxxxxxx.wmv'; drop table t1; @@ -755,7 +755,7 @@ drop table t1; # Test problem which gave error 'Can't find record in 't1'' # -CREATE TABLE t1 ( STR_DATE varchar(8) NOT NULL default '',INFO_NOTE varchar(200) default NULL,PRIMARY KEY (STR_DATE) ) TYPE=BerkeleyDB; +CREATE TABLE t1 ( STR_DATE varchar(8) NOT NULL default '',INFO_NOTE varchar(200) default NULL,PRIMARY KEY (STR_DATE) ) ENGINE=BerkeleyDB; select INFO_NOTE from t1 where STR_DATE = '20010610'; select INFO_NOTE from t1 where STR_DATE < '20010610'; select INFO_NOTE from t1 where STR_DATE > '20010610'; @@ -765,8 +765,8 @@ drop table t1; # Test problem with multi table delete which quickly shows up with bdb tables. # -create table t1 (a int not null, b int, primary key (a)) type =bdb; -create table t2 (a int not null, b int, primary key (a)) type =bdb; +create table t1 (a int not null, b int, primary key (a)) engine =bdb; +create table t2 (a int not null, b int, primary key (a)) engine =bdb; insert into t1 values (2, 3),(1, 7),(10, 7); insert into t2 values (2, 3),(1, 7),(10, 7); select * from t1; @@ -781,7 +781,7 @@ drop table t1,t2; # The bug #971 # -create table t1 (x int not null, index(x)) type=bdb; +create table t1 (x int not null, index(x)) engine=bdb; insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); select * from t1 where x <= 10 and x >= 7; select * from t1 where x <= 10 and x >= 7 order by x; diff --git a/mysql-test/t/bdb_cache.test b/mysql-test/t/bdb_cache.test index 0d7de75ccf6..0082c83faff 100644 --- a/mysql-test/t/bdb_cache.test +++ b/mysql-test/t/bdb_cache.test @@ -9,7 +9,7 @@ drop table if exists t1, t2, t3; --enable_warnings flush status; set autocommit=0; -create table t1 (a int not null) type=bdb; +create table t1 (a int not null) engine=bdb; insert into t1 values (1),(2),(3); select * from t1; show status like "Qcache_queries_in_cache"; @@ -17,15 +17,15 @@ drop table t1; commit; set autocommit=1; begin; -create table t1 (a int not null) type=bdb; +create table t1 (a int not null) engine=bdb; insert into t1 values (1),(2),(3); select * from t1; show status like "Qcache_queries_in_cache"; drop table t1; commit; -create table t1 (a int not null) type=bdb; -create table t2 (a int not null) type=bdb; -create table t3 (a int not null) type=bdb; +create table t1 (a int not null) engine=bdb; +create table t2 (a int not null) engine=bdb; +create table t3 (a int not null) engine=bdb; insert into t1 values (1),(2); insert into t2 values (1),(2); insert into t3 values (1),(2); diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 54166c3b0dc..32565c0e20b 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -18,7 +18,7 @@ insert into t1 values (""),(null); select * from t1; drop table t1; -create table t1 (a int not null auto_increment,primary key (a)) type=heap; +create table t1 (a int not null auto_increment,primary key (a)) engine=heap; drop table t1; # @@ -26,18 +26,18 @@ drop table t1; # --error 1146 -create table t2 type=heap select * from t1; +create table t2 engine=heap select * from t1; --error 1146 create table t2 select auto+1 from t1; drop table if exists t1,t2; --error 1167 create table t1 (b char(0) not null, index(b)); --error 1163 -create table t1 (a int not null,b text) type=heap; +create table t1 (a int not null,b text) engine=heap; drop table if exists t1; --error 1075 -create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) type=heap; +create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) engine=heap; -- error 1044,1 create table not_existing_database.test (a int); diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test index 0457c204e32..90b423cd1e0 100644 --- a/mysql-test/t/ctype_ucs.test +++ b/mysql-test/t/ctype_ucs.test @@ -84,7 +84,7 @@ DROP TABLE t1; # Bug 1181 # CREATE TABLE t1 (word varchar(64) NOT NULL, PRIMARY KEY (word)) -TYPE=MyISAM CHARACTER SET ucs2 COLLATE ucs2_general_ci; +ENGINE=MyISAM CHARACTER SET ucs2 COLLATE ucs2_general_ci; INSERT INTO t1 (word) VALUES ("cat"); SELECT * FROM t1 WHERE word LIKE "c%"; SELECT * FROM t1 WHERE word LIKE "ca_"; @@ -126,7 +126,7 @@ CREATE TABLE t1 ( word VARCHAR(64), bar INT(11) default 0, PRIMARY KEY (word)) - TYPE=MyISAM + ENGINE=MyISAM CHARSET ucs2 COLLATE ucs2_general_ci ; @@ -149,7 +149,7 @@ DROP TABLE t1; CREATE TABLE t1 ( word VARCHAR(64) , PRIMARY KEY (word)) - TYPE=MyISAM + ENGINE=MyISAM CHARSET ucs2 COLLATE ucs2_general_ci; @@ -172,7 +172,7 @@ CREATE TABLE t1 ( word TEXT, bar INT(11) AUTO_INCREMENT, PRIMARY KEY (bar)) - TYPE=MyISAM + ENGINE=MyISAM CHARSET ucs2 COLLATE ucs2_general_ci ; INSERT INTO t1 (word) VALUES ("aar"); diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index c529994f31b..bd5d9e5c0d9 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -56,7 +56,7 @@ CREATE TABLE t1 ( not_null varchar(20) binary NOT NULL default '', misc integer not null, PRIMARY KEY (not_null) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES (NULL,'a',4), (NULL,'b',5), (NULL,'c',6), (NULL,'d',7); diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index 56c00e7501c..3c1f18b7524 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -223,7 +223,7 @@ drop table t1; # Test problem with DISTINCT and ORDER BY DESC # -create table t1 (a char(1), key(a)) type=myisam; +create table t1 (a char(1), key(a)) engine=myisam; insert into t1 values('1'),('1'); select * from t1 where a >= '1'; select distinct a from t1 order by a desc; @@ -257,9 +257,9 @@ drop table t1,t2; # test with table.* in DISTINCT # -CREATE TABLE t1 (privatemessageid int(10) unsigned NOT NULL auto_increment, folderid smallint(6) NOT NULL default '0', userid int(10) unsigned NOT NULL default '0', touserid int(10) unsigned NOT NULL default '0', fromuserid int(10) unsigned NOT NULL default '0', title varchar(250) NOT NULL default '', message mediumtext NOT NULL, dateline int(10) unsigned NOT NULL default '0', showsignature smallint(6) NOT NULL default '0', iconid smallint(5) unsigned NOT NULL default '0', messageread smallint(6) NOT NULL default '0', readtime int(10) unsigned NOT NULL default '0', receipt smallint(6) unsigned NOT NULL default '0', deleteprompt smallint(6) unsigned NOT NULL default '0', multiplerecipients smallint(6) unsigned NOT NULL default '0', PRIMARY KEY (privatemessageid), KEY userid (userid)) TYPE=MyISAM; +CREATE TABLE t1 (privatemessageid int(10) unsigned NOT NULL auto_increment, folderid smallint(6) NOT NULL default '0', userid int(10) unsigned NOT NULL default '0', touserid int(10) unsigned NOT NULL default '0', fromuserid int(10) unsigned NOT NULL default '0', title varchar(250) NOT NULL default '', message mediumtext NOT NULL, dateline int(10) unsigned NOT NULL default '0', showsignature smallint(6) NOT NULL default '0', iconid smallint(5) unsigned NOT NULL default '0', messageread smallint(6) NOT NULL default '0', readtime int(10) unsigned NOT NULL default '0', receipt smallint(6) unsigned NOT NULL default '0', deleteprompt smallint(6) unsigned NOT NULL default '0', multiplerecipients smallint(6) unsigned NOT NULL default '0', PRIMARY KEY (privatemessageid), KEY userid (userid)) ENGINE=MyISAM; INSERT INTO t1 VALUES (128,0,33,33,8,':D','',996121863,1,0,2,996122850,2,0,0); -CREATE TABLE t2 (userid int(10) unsigned NOT NULL auto_increment, usergroupid smallint(5) unsigned NOT NULL default '0', username varchar(50) NOT NULL default '', password varchar(50) NOT NULL default '', email varchar(50) NOT NULL default '', styleid smallint(5) unsigned NOT NULL default '0', parentemail varchar(50) NOT NULL default '', coppauser smallint(6) NOT NULL default '0', homepage varchar(100) NOT NULL default '', icq varchar(20) NOT NULL default '', aim varchar(20) NOT NULL default '', yahoo varchar(20) NOT NULL default '', signature mediumtext NOT NULL, adminemail smallint(6) NOT NULL default '0', showemail smallint(6) NOT NULL default '0', invisible smallint(6) NOT NULL default '0', usertitle varchar(250) NOT NULL default '', customtitle smallint(6) NOT NULL default '0', joindate int(10) unsigned NOT NULL default '0', cookieuser smallint(6) NOT NULL default '0', daysprune smallint(6) NOT NULL default '0', lastvisit int(10) unsigned NOT NULL default '0', lastactivity int(10) unsigned NOT NULL default '0', lastpost int(10) unsigned NOT NULL default '0', posts smallint(5) unsigned NOT NULL default '0', timezoneoffset varchar(4) NOT NULL default '', emailnotification smallint(6) NOT NULL default '0', buddylist mediumtext NOT NULL, ignorelist mediumtext NOT NULL, pmfolders mediumtext NOT NULL, receivepm smallint(6) NOT NULL default '0', emailonpm smallint(6) NOT NULL default '0', pmpopup smallint(6) NOT NULL default '0', avatarid smallint(6) NOT NULL default '0', avatarrevision int(6) unsigned NOT NULL default '0', options smallint(6) NOT NULL default '15', birthday date NOT NULL default '0000-00-00', maxposts smallint(6) NOT NULL default '-1', startofweek smallint(6) NOT NULL default '1', ipaddress varchar(20) NOT NULL default '', referrerid int(10) unsigned NOT NULL default '0', nosessionhash smallint(6) NOT NULL default '0', autorefresh smallint(6) NOT NULL default '-1', messagepopup tinyint(2) NOT NULL default '0', inforum smallint(5) unsigned NOT NULL default '0', ratenum smallint(5) unsigned NOT NULL default '0', ratetotal smallint(5) unsigned NOT NULL default '0', allowrate smallint(5) unsigned NOT NULL default '1', PRIMARY KEY (userid), KEY usergroupid (usergroupid), KEY username (username), KEY inforum (inforum)) TYPE=MyISAM; +CREATE TABLE t2 (userid int(10) unsigned NOT NULL auto_increment, usergroupid smallint(5) unsigned NOT NULL default '0', username varchar(50) NOT NULL default '', password varchar(50) NOT NULL default '', email varchar(50) NOT NULL default '', styleid smallint(5) unsigned NOT NULL default '0', parentemail varchar(50) NOT NULL default '', coppauser smallint(6) NOT NULL default '0', homepage varchar(100) NOT NULL default '', icq varchar(20) NOT NULL default '', aim varchar(20) NOT NULL default '', yahoo varchar(20) NOT NULL default '', signature mediumtext NOT NULL, adminemail smallint(6) NOT NULL default '0', showemail smallint(6) NOT NULL default '0', invisible smallint(6) NOT NULL default '0', usertitle varchar(250) NOT NULL default '', customtitle smallint(6) NOT NULL default '0', joindate int(10) unsigned NOT NULL default '0', cookieuser smallint(6) NOT NULL default '0', daysprune smallint(6) NOT NULL default '0', lastvisit int(10) unsigned NOT NULL default '0', lastactivity int(10) unsigned NOT NULL default '0', lastpost int(10) unsigned NOT NULL default '0', posts smallint(5) unsigned NOT NULL default '0', timezoneoffset varchar(4) NOT NULL default '', emailnotification smallint(6) NOT NULL default '0', buddylist mediumtext NOT NULL, ignorelist mediumtext NOT NULL, pmfolders mediumtext NOT NULL, receivepm smallint(6) NOT NULL default '0', emailonpm smallint(6) NOT NULL default '0', pmpopup smallint(6) NOT NULL default '0', avatarid smallint(6) NOT NULL default '0', avatarrevision int(6) unsigned NOT NULL default '0', options smallint(6) NOT NULL default '15', birthday date NOT NULL default '0000-00-00', maxposts smallint(6) NOT NULL default '-1', startofweek smallint(6) NOT NULL default '1', ipaddress varchar(20) NOT NULL default '', referrerid int(10) unsigned NOT NULL default '0', nosessionhash smallint(6) NOT NULL default '0', autorefresh smallint(6) NOT NULL default '-1', messagepopup tinyint(2) NOT NULL default '0', inforum smallint(5) unsigned NOT NULL default '0', ratenum smallint(5) unsigned NOT NULL default '0', ratetotal smallint(5) unsigned NOT NULL default '0', allowrate smallint(5) unsigned NOT NULL default '1', PRIMARY KEY (userid), KEY usergroupid (usergroupid), KEY username (username), KEY inforum (inforum)) ENGINE=MyISAM; INSERT INTO t2 VALUES (33,6,'Kevin','0','kevin@stileproject.com',1,'',0,'http://www.stileproject.com','','','','',1,1,0,'Administrator',0,996120694,1,-1,1030996168,1031027028,1030599436,36,'-6',0,'','','',1,0,1,0,0,15,'0000-00-00',-1,1,'64.0.0.0',0,1,-1,0,0,4,19,1); SELECT DISTINCT t1.*, t2.* FROM t1 LEFT JOIN t2 ON (t2.userid = t1.touserid); DROP TABLE t1,t2; @@ -279,7 +279,7 @@ DROP TABLE t1,t2; # Test of LEFT() with distinct # -CREATE table t1 ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', PRIMARY KEY (`id`)) TYPE=MyISAM AUTO_INCREMENT=3 ; +CREATE table t1 ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 ; INSERT INTO t1 VALUES (1, 'aaaaa'); INSERT INTO t1 VALUES (3, 'aaaaa'); INSERT INTO t1 VALUES (2, 'eeeeeee'); @@ -327,7 +327,7 @@ CREATE TABLE t1 ( html varchar(5) default NULL, rin int(11) default '0', out int(11) default '0' -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES ('1',1,0); SELECT DISTINCT html,SUM(out)/(SUM(rin)+1) as 'prod' FROM t1 GROUP BY rin; diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 38c09682bac..02ada3dc8cb 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -176,7 +176,7 @@ CREATE TABLE t1 ( title varchar(100) default '', PRIMARY KEY (id), KEY ind5 (title) -) TYPE=MyISAM; +) ENGINE=MyISAM; CREATE FULLTEXT INDEX ft1 ON t1(title); insert into t1 (title) values ('this is a test'); @@ -191,7 +191,7 @@ drop table t1; # one more bug - const_table related -CREATE TABLE t1 (a int(11), b text, FULLTEXT KEY (b)) TYPE=MyISAM; +CREATE TABLE t1 (a int(11), b text, FULLTEXT KEY (b)) ENGINE=MyISAM; insert into t1 values (1,"I wonder why the fulltext index doesnt work?"); SELECT * from t1 where MATCH (b) AGAINST ('apples'); @@ -199,7 +199,7 @@ insert into t1 values (2,"fullaaa fullzzz"); select * from t1 where match b against ('full*' in boolean mode); drop table t1; -CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text NOT NULL, FULLTEXT KEY mytext (mytext)) TYPE=MyISAM; +CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text NOT NULL, FULLTEXT KEY mytext (mytext)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,'my small mouse'),(2,'la-la-la'),(3,'It is so funny'),(4,'MySQL Tutorial'); select 8 from t1; drop table t1; diff --git a/mysql-test/t/fulltext2.test b/mysql-test/t/fulltext2.test index a01ec3a59c9..cab1d096fe7 100644 --- a/mysql-test/t/fulltext2.test +++ b/mysql-test/t/fulltext2.test @@ -14,7 +14,7 @@ CREATE TABLE t1 ( i int(10) unsigned not null auto_increment primary key, a varchar(255) not null, FULLTEXT KEY (a) -) TYPE=MyISAM; +) ENGINE=MyISAM; # two-level entry, second-level tree with depth 2 --disable_query_log @@ -100,7 +100,7 @@ CREATE TABLE t1 ( i int(10) unsigned not null auto_increment primary key, a varchar(255) not null, FULLTEXT KEY (a) -) TYPE=MyISAM; +) ENGINE=MyISAM; # two-level entry, second-level tree with depth 2 --disable_query_log diff --git a/mysql-test/t/fulltext_distinct.test b/mysql-test/t/fulltext_distinct.test index 2ccfb94c819..1f4cb8cd7bc 100644 --- a/mysql-test/t/fulltext_distinct.test +++ b/mysql-test/t/fulltext_distinct.test @@ -15,14 +15,14 @@ CREATE TABLE t1 ( KEY kt(tag), KEY kv(value(15)), FULLTEXT KEY kvf(value) -) TYPE=MyISAM; +) ENGINE=MyISAM; CREATE TABLE t2 ( id_t2 mediumint unsigned NOT NULL default '0', id_t1 mediumint unsigned NOT NULL default '0', field_number tinyint unsigned NOT NULL default '0', PRIMARY KEY (id_t2,id_t1,field_number), KEY id_t1(id_t1) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 (tag,value) VALUES ('foo123','bar111'); INSERT INTO t1 (tag,value) VALUES ('foo123','bar222'); diff --git a/mysql-test/t/fulltext_left_join.test b/mysql-test/t/fulltext_left_join.test index e3083ddc4e1..f8dcfb2aad6 100644 --- a/mysql-test/t/fulltext_left_join.test +++ b/mysql-test/t/fulltext_left_join.test @@ -35,9 +35,9 @@ drop table t1, t2; # Bug #484, reported by Stephen Brandon <stephen@brandonitconsulting.co.uk> # -create table t1 (venue_id int(11) default null, venue_text varchar(255) default null, dt datetime default null) type=myisam; +create table t1 (venue_id int(11) default null, venue_text varchar(255) default null, dt datetime default null) engine=myisam; insert into t1 (venue_id, venue_text, dt) values (1, 'a1', '2003-05-23 19:30:00'),(null, 'a2', '2003-05-23 19:30:00'); -create table t2 (name varchar(255) not null default '', entity_id int(11) not null auto_increment, primary key (entity_id), fulltext key name (name)) type=myisam; +create table t2 (name varchar(255) not null default '', entity_id int(11) not null auto_increment, primary key (entity_id), fulltext key name (name)) engine=myisam; insert into t2 (name, entity_id) values ('aberdeen town hall', 1), ('glasgow royal concert hall', 2), ('queen\'s hall, edinburgh', 3); select * from t1 left join t2 on venue_id = entity_id where match(name) against('aberdeen' in boolean mode) and dt = '2003-05-23 19:30:00'; select * from t1 left join t2 on venue_id = entity_id where match(name) against('aberdeen') and dt = '2003-05-23 19:30:00'; diff --git a/mysql-test/t/func_compress.test b/mysql-test/t/func_compress.test index 06ebb388517..79de99276a6 100644 --- a/mysql-test/t/func_compress.test +++ b/mysql-test/t/func_compress.test @@ -13,7 +13,7 @@ explain extended select uncompressed_length(compress(@test_compress_string))=len select uncompressed_length(compress(@test_compress_string)); select length(compress(@test_compress_string))<length(@test_compress_string); -create table t1 (a text, b char(255), c char(4)) type=myisam; +create table t1 (a text, b char(255), c char(4)) engine=myisam; insert into t1 (a,b,c) values (compress(@test_compress_string),compress(@test_compress_string),'d '); select uncompress(a) from t1; select uncompress(b) from t1; diff --git a/mysql-test/t/func_date_add.test b/mysql-test/t/func_date_add.test index 35acfe84bee..93c77daf86e 100644 --- a/mysql-test/t/func_date_add.test +++ b/mysql-test/t/func_date_add.test @@ -13,7 +13,7 @@ CREATE TABLE t1 ( sessions int(10) unsigned DEFAULT '0' NOT NULL, ts timestamp(14), PRIMARY KEY (visitor_id,group_id) -)/*! type=MyISAM */; +)/*! engine=MyISAM */; INSERT INTO t1 VALUES (465931136,7,2,2,20000318160952); INSERT INTO t1 VALUES (173865424,2,2,2,20000318233615); INSERT INTO t1 VALUES (173865424,8,2,2,20000318233615); diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 9e0184d35ce..17ed4c03b5a 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -350,7 +350,7 @@ select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_an explain extended select SQL_BIG_RESULT a,count(b), sum(b), avg(b), std(b), min(b), max(b), bit_and(b), bit_or(b), bit_xor(b) from t1 group by a; drop table t1; --disable_warnings -create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) type=InnoDB; +create table t1 (USR_ID integer not null, MAX_REQ integer not null, constraint PK_SEA_USER primary key (USR_ID)) engine=InnoDB; --enable_warnings insert into t1 values (1, 3); select count(*) + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ + MAX_REQ - MAX_REQ from t1 group by MAX_REQ; diff --git a/mysql-test/t/func_if.test b/mysql-test/t/func_if.test index 7842df4fd0a..e44a7f6d6f9 100644 --- a/mysql-test/t/func_if.test +++ b/mysql-test/t/func_if.test @@ -14,7 +14,7 @@ select IF(0,"ERROR","this"),IF(1,"is","ERROR"),IF(NULL,"ERROR","a"),IF(1,2,3)|0, # # Test of IF and case-sensitiveness # -CREATE TABLE t1 (st varchar(255) NOT NULL, u int(11) NOT NULL) TYPE=MyISAM; +CREATE TABLE t1 (st varchar(255) NOT NULL, u int(11) NOT NULL) ENGINE=MyISAM; INSERT INTO t1 VALUES ('a',1),('A',1),('aa',1),('AA',1),('a',1),('aaa',0),('BBB',0); select if(1,st,st) s from t1 order by s; select if(u=1,st,st) s from t1 order by s; diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 4ae586256ae..0003f14c9ca 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -120,7 +120,7 @@ CREATE TABLE t1 ( modified timestamp(14) NOT NULL, bugstatus int(10) unsigned default NULL, submitter int(10) unsigned default NULL -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,'Link',1,1,1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','2001-02-28 08:40:16',20010228084016,0,4); SELECT CONCAT('"',CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified+0,bugstatus,submitter), '"') FROM t1; @@ -133,7 +133,7 @@ drop table t1; # Test bug in AES_DECRYPT() when called with wrong argument # -CREATE TABLE t1 (id int(11) NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) TYPE=MyISAM; +CREATE TABLE t1 (id int(11) NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf'); SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password"); DROP TABLE t1; @@ -334,7 +334,7 @@ DROP TABLE t1; # # test for #739 -CREATE TABLE t1 (title text) TYPE=MyISAM; +CREATE TABLE t1 (title text) ENGINE=MyISAM; INSERT INTO t1 VALUES ('Congress reconvenes in September to debate welfare and adult education'); INSERT INTO t1 VALUES ('House passes the CAREERS bill'); SELECT CONCAT("</a>",RPAD("",(55 - LENGTH(title)),".")) from t1; diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test index 86e0ee2637b..1559fef7e6e 100644 --- a/mysql-test/t/func_test.test +++ b/mysql-test/t/func_test.test @@ -79,9 +79,9 @@ select _koi8r'a' LIKE _latin1'A'; # # Test for LEAST() BUG in LEFT JOIN # -CREATE TABLE t1 ( faq_group_id int(11) NOT NULL default '0', faq_id int(11) NOT NULL default '0', title varchar(240) default NULL, keywords text, description longblob, solution longblob, status tinyint(4) NOT NULL default '0', access_id smallint(6) default NULL, lang_id smallint(6) NOT NULL default '0', created datetime NOT NULL default '0000-00-00 00:00:00', updated datetime default NULL, last_access datetime default NULL, last_notify datetime default NULL, solved_count int(11) NOT NULL default '0', static_solved int(11) default NULL, solved_1 int(11) default NULL, solved_2 int(11) default NULL, solved_3 int(11) default NULL, solved_4 int(11) default NULL, solved_5 int(11) default NULL, expires datetime default NULL, notes text, assigned_to smallint(6) default NULL, assigned_group smallint(6) default NULL, last_edited_by smallint(6) default NULL, orig_ref_no varchar(15) binary default NULL, c$fundstate smallint(6) default NULL, c$contributor smallint(6) default NULL, UNIQUE KEY t1$faq_id (faq_id), KEY t1$group_id$faq_id (faq_group_id,faq_id), KEY t1$c$fundstate (c$fundstate) ) TYPE=MyISAM; +CREATE TABLE t1 ( faq_group_id int(11) NOT NULL default '0', faq_id int(11) NOT NULL default '0', title varchar(240) default NULL, keywords text, description longblob, solution longblob, status tinyint(4) NOT NULL default '0', access_id smallint(6) default NULL, lang_id smallint(6) NOT NULL default '0', created datetime NOT NULL default '0000-00-00 00:00:00', updated datetime default NULL, last_access datetime default NULL, last_notify datetime default NULL, solved_count int(11) NOT NULL default '0', static_solved int(11) default NULL, solved_1 int(11) default NULL, solved_2 int(11) default NULL, solved_3 int(11) default NULL, solved_4 int(11) default NULL, solved_5 int(11) default NULL, expires datetime default NULL, notes text, assigned_to smallint(6) default NULL, assigned_group smallint(6) default NULL, last_edited_by smallint(6) default NULL, orig_ref_no varchar(15) binary default NULL, c$fundstate smallint(6) default NULL, c$contributor smallint(6) default NULL, UNIQUE KEY t1$faq_id (faq_id), KEY t1$group_id$faq_id (faq_group_id,faq_id), KEY t1$c$fundstate (c$fundstate) ) ENGINE=MyISAM; INSERT INTO t1 VALUES (82,82,'How to use the DynaVox Usage Counts Feature','usages count, number, corner, white, box, button','<as-html>\r\n<table width=\"100%\" border=\"0\">\r\n <tr>\r\n <td width=\"3%\"> </td>\r\n <td width=\"97%\">\r\n <h3><font face=\"Verdana, Arial, Helvetica, sans-serif\" color=\"#000000\">How \r\n To</font><!-- #BeginEditable \"CS_troubleshoot_question\" --><font face=\"Verdana, Arial, Helvetica, sans-serif\" color=\"#000099\"><font color=\"#000000\">: \r\n Display or Hide the Usage Counts to find out how many times each button is being selected. </font></font><!-- #EndEditable --></h3>\r\n </td>\r\n </tr>\r\n</table>','<as-html>\r\n <table width=\"100%\" border=\"0\">\r\n <tr>\r\n <td width=\"3%\"> </td>\r\n \r\n<td width=\"97%\"><!-- #BeginEditable \"CS_troubleshoot_answer\" --> \r\n \r\n<p><font color=\"#000000\" face=\"Verdana, Arial, Helvetica, sans-serif\">1. Select \r\n the <i>On/Setup</i> button to access the DynaVox Setup Menu.<br>\r\n 2. Select <b>Button Features.</b><br>\r\n 3. Below the <b>OK</b> button is the <b>Usage Counts</b> button.<br>\r\n a. If it says \"Hidden\" then the Usage Counts will not be displayed.<br>\r\n b. If it says \"Displayed\" then the Usage Counts will be shown.<br>\r\n c. Select the <b>Usage Counts</b> Option Ring once and it will toggle \r\n to the alternative option.<br>\r\n 4. Once the correct setting has been chosen, select <b>OK</b> to leave the <i>Button \r\n Features</i> menu.<br>\r\n 5. Select <b>OK</b> out of the <i>Setup</i> menu and return to the communication \r\n page.</font></p>\r\n <p><font color=\"#000000\" face=\"Verdana, Arial, Helvetica, sans-serif\">For \r\n further information on <i>Usage Counts,</i> see the <i>Button Features \r\n Menu Entry</i> in the DynaVox/DynaMyte Reference Manual.</font></p>\r\n<!-- #EndEditable --></td>\r\n </tr>\r\n</table>',4,1,1,'2001-11-16 16:43:34','2002-11-25 12:09:43','2003-07-24 01:04:48',NULL,11,NULL,0,0,0,0,0,NULL,NULL,NULL,NULL,11,NULL,NULL,NULL); -CREATE TABLE t2 ( access_id smallint(6) NOT NULL default '0', name varchar(20) binary default NULL, rank smallint(6) NOT NULL default '0', KEY t2$access_id (access_id) ) TYPE=MyISAM; +CREATE TABLE t2 ( access_id smallint(6) NOT NULL default '0', name varchar(20) binary default NULL, rank smallint(6) NOT NULL default '0', KEY t2$access_id (access_id) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,'Everyone',2),(2,'Help',3),(3,'Customer Support',1); SELECT f_acc.rank, a1.rank, a2.rank FROM t1 LEFT JOIN t1 f1 ON (f1.access_id=1 AND f1.faq_group_id = t1.faq_group_id) LEFT JOIN t2 a1 ON (a1.access_id = f1.access_id) LEFT JOIN t1 f2 ON (f2.access_id=3 AND f2.faq_group_id = t1.faq_group_id) LEFT JOIN t2 a2 ON (a2.access_id = f2.access_id), t2 f_acc WHERE LEAST(a1.rank,a2.rank) = f_acc.rank; DROP TABLE t1,t2; diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index f1a24a4c3cb..6f114fe18ae 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -151,7 +151,7 @@ drop table t1,t2; # # Test bug with month() and year() on text fields with wrong information -CREATE TABLE t1 (updated text) TYPE=MyISAM; +CREATE TABLE t1 (updated text) ENGINE=MyISAM; INSERT INTO t1 VALUES (''); SELECT month(updated) from t1; SELECT year(updated) from t1; diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test index 0368ddb41cb..08ba8329b48 100644 --- a/mysql-test/t/gis-rtree.test +++ b/mysql-test/t/gis-rtree.test @@ -9,7 +9,7 @@ CREATE TABLE t1 ( fid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, g GEOMETRY NOT NULL, SPATIAL KEY(g) -) TYPE=MyISAM; +) ENGINE=MyISAM; SHOW CREATE TABLE t1; @@ -31,7 +31,7 @@ DROP TABLE t1; CREATE TABLE t2 ( fid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, g GEOMETRY NOT NULL -) TYPE=MyISAM; +) ENGINE=MyISAM; let $1=10; while ($1) diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 705e32c82df..1e65bef19b7 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -352,7 +352,7 @@ CREATE TABLE t1 ( value int(11) unsigned NOT NULL default '0', UNIQUE KEY pid2 (pid,c1id,c2id), UNIQUE KEY pid (pid,value) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES (1, 1, NULL, 1),(1, 2, NULL, 2),(1, NULL, 3, 3),(1, 4, NULL, 4),(1, 5, NULL, 5); @@ -360,7 +360,7 @@ CREATE TABLE t2 ( id int(11) unsigned NOT NULL default '0', active enum('Yes','No') NOT NULL default 'Yes', PRIMARY KEY (id) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t2 VALUES (1, 'Yes'),(2, 'No'),(4, 'Yes'),(5, 'No'); diff --git a/mysql-test/t/handler.test b/mysql-test/t/handler.test index 99ca858990c..d734882591a 100644 --- a/mysql-test/t/handler.test +++ b/mysql-test/t/handler.test @@ -80,7 +80,7 @@ insert into t1 values (17); --error 1109 handler t2 read first; handler t1 open as t2; -alter table t1 type=MyISAM; +alter table t1 engine=MyISAM; --error 1109 handler t2 read first; drop table t1; diff --git a/mysql-test/t/heap.test b/mysql-test/t/heap.test index 797472e7769..d867d5f4323 100644 --- a/mysql-test/t/heap.test +++ b/mysql-test/t/heap.test @@ -6,7 +6,7 @@ drop table if exists t1; --enable_warnings -create table t1 (a int not null,b int not null, primary key (a)) type=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100; +create table t1 (a int not null,b int not null, primary key (a)) engine=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100; insert into t1 values(1,1),(2,2),(3,3),(4,4); delete from t1 where a=1 or a=0; #show table status like "t1"; @@ -20,20 +20,20 @@ select * from t1; alter table t1 add c int not null, add key (c,a); drop table t1; -create table t1 (a int not null,b int not null, primary key (a)) type=memory comment="testing heaps"; +create table t1 (a int not null,b int not null, primary key (a)) engine=memory comment="testing heaps"; insert into t1 values(1,1),(2,2),(3,3),(4,4); delete from t1 where a > 0; select * from t1; drop table t1; -create table t1 (a int not null,b int not null, primary key (a)) type=heap comment="testing heaps"; +create table t1 (a int not null,b int not null, primary key (a)) engine=heap comment="testing heaps"; insert into t1 values(1,1),(2,2),(3,3),(4,4); -alter table t1 modify a int not null auto_increment, type=myisam, comment="new myisam table"; +alter table t1 modify a int not null auto_increment, engine=myisam, comment="new myisam table"; #show table status like "t1"; select * from t1; drop table t1; -create table t1 (a int not null) type=heap; +create table t1 (a int not null) engine=heap; insert into t1 values (869751),(736494),(226312),(802616); select * from t1 where a > 736494; alter table t1 add unique uniq_id(a); @@ -41,31 +41,31 @@ select * from t1 where a > 736494; select * from t1 where a = 736494; select * from t1 where a=869751 or a=736494; select * from t1 where a in (869751,736494,226312,802616); -alter table t1 type=myisam; +alter table t1 engine=myisam; explain select * from t1 where a in (869751,736494,226312,802616); drop table t1; create table t1 (x int not null, y int not null, key x (x), unique y (y)) -type=heap; +engine=heap; insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6); select * from t1 where x=1; select * from t1,t1 as t2 where t1.x=t2.y; explain select * from t1,t1 as t2 where t1.x=t2.y; drop table t1; -create table t1 (a int) type=heap; +create table t1 (a int) engine=heap; insert into t1 values(1); select max(a) from t1; drop table t1; -CREATE TABLE t1 ( a int not null default 0, b int not null default 0, key(a), key(b) ) TYPE=HEAP; +CREATE TABLE t1 ( a int not null default 0, b int not null default 0, key(a), key(b) ) ENGINE=HEAP; insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); select * from t1 where a=1; insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); select * from t1 where a=1; drop table t1; -create table t1 (id int unsigned not null, primary key (id)) type=HEAP; +create table t1 (id int unsigned not null, primary key (id)) engine=HEAP; insert into t1 values(1); select max(id) from t1; insert into t1 values(2); @@ -73,16 +73,16 @@ select max(id) from t1; replace into t1 values(1); drop table t1; -create table t1 (n int) type=heap; +create table t1 (n int) engine=heap; drop table t1; -create table t1 (n int) type=heap; +create table t1 (n int) engine=heap; drop table if exists t1; # Test of non unique index CREATE table t1(f1 int not null,f2 char(20) not -null,index(f2)) type=heap; +null,index(f2)) engine=heap; INSERT into t1 set f1=12,f2="bill"; INSERT into t1 set f1=13,f2="bill"; INSERT into t1 set f1=14,f2="bill"; @@ -100,7 +100,7 @@ drop table t1; # Test when using part key searches # -create table t1 (btn char(10) not null, key(btn)) type=heap; +create table t1 (btn char(10) not null, key(btn)) engine=heap; insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i"); explain select * from t1 where btn like "q%"; select * from t1 where btn like "q%"; @@ -119,7 +119,7 @@ CREATE TABLE t1 ( b int default NULL, KEY a (a), UNIQUE b (b) -) type=heap; +) engine=heap; INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3); SELECT * FROM t1 WHERE a=NULL; explain SELECT * FROM t1 WHERE a IS NULL; @@ -135,7 +135,7 @@ DROP TABLE t1; CREATE TABLE t1 ( a int default NULL, key a (a) -) TYPE=HEAP; +) ENGINE=HEAP; INSERT INTO t1 VALUES (10), (10), (10); EXPLAIN SELECT * FROM t1 WHERE a=10; SELECT * FROM t1 WHERE a=10; @@ -145,7 +145,7 @@ DROP TABLE t1; # Test when deleting all rows # -CREATE TABLE t1 (a int not null, primary key(a)) type=heap; +CREATE TABLE t1 (a int not null, primary key(a)) engine=heap; INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11); DELETE from t1 where a < 100; SELECT * from t1; diff --git a/mysql-test/t/heap_auto_increment.test b/mysql-test/t/heap_auto_increment.test index 6f392f6830d..9d0fefa4d52 100644 --- a/mysql-test/t/heap_auto_increment.test +++ b/mysql-test/t/heap_auto_increment.test @@ -6,7 +6,7 @@ drop table if exists t1; --enable_warnings -create table t1 (a int not null auto_increment,b int, primary key (a)) type=heap auto_increment=3; +create table t1 (a int not null auto_increment,b int, primary key (a)) engine=heap auto_increment=3; insert into t1 values (1,1),(NULL,3),(NULL,4); delete from t1 where a=4; insert into t1 values (NULL,5),(NULL,6); @@ -25,7 +25,7 @@ drop table t1; create table t1 ( skey tinyint unsigned NOT NULL auto_increment PRIMARY KEY, sval char(20) -) type=heap; +) engine=heap; insert into t1 values (NULL, "hello"); insert into t1 values (NULL, "hey"); select * from t1; diff --git a/mysql-test/t/heap_btree.test b/mysql-test/t/heap_btree.test index 34aeeec0a11..a520065a8b3 100644 --- a/mysql-test/t/heap_btree.test +++ b/mysql-test/t/heap_btree.test @@ -6,7 +6,7 @@ drop table if exists t1; --enable_warnings -create table t1 (a int not null,b int not null, primary key using BTREE (a)) type=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100; +create table t1 (a int not null,b int not null, primary key using BTREE (a)) engine=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100; insert into t1 values(1,1),(2,2),(3,3),(4,4); delete from t1 where a=1 or a=0; #show table status like "t1"; @@ -20,20 +20,20 @@ select * from t1; alter table t1 add c int not null, add key using BTREE (c,a); drop table t1; -create table t1 (a int not null,b int not null, primary key using BTREE (a)) type=heap comment="testing heaps"; +create table t1 (a int not null,b int not null, primary key using BTREE (a)) engine=heap comment="testing heaps"; insert into t1 values(-2,-2),(-1,-1),(0,0),(1,1),(2,2),(3,3),(4,4); delete from t1 where a > -3; select * from t1; drop table t1; -create table t1 (a int not null,b int not null, primary key using BTREE (a)) type=heap comment="testing heaps"; +create table t1 (a int not null,b int not null, primary key using BTREE (a)) engine=heap comment="testing heaps"; insert into t1 values(1,1),(2,2),(3,3),(4,4); -alter table t1 modify a int not null auto_increment, type=myisam, comment="new myisam table"; +alter table t1 modify a int not null auto_increment, engine=myisam, comment="new myisam table"; #show table status like "t1"; select * from t1; drop table t1; -create table t1 (a int not null) type=heap; +create table t1 (a int not null) engine=heap; insert into t1 values (869751),(736494),(226312),(802616); select * from t1 where a > 736494; alter table t1 add unique uniq_id using BTREE (a); @@ -41,12 +41,12 @@ select * from t1 where a > 736494; select * from t1 where a = 736494; select * from t1 where a=869751 or a=736494; select * from t1 where a in (869751,736494,226312,802616); -alter table t1 type=myisam; +alter table t1 engine=myisam; explain select * from t1 where a in (869751,736494,226312,802616); drop table t1; create table t1 (x int not null, y int not null, key x using BTREE (x,y), unique y using BTREE (y)) -type=heap; +engine=heap; insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6); explain select * from t1 where x=1; select * from t1 where x=1; @@ -54,12 +54,12 @@ select * from t1,t1 as t2 where t1.x=t2.y; explain select * from t1,t1 as t2 where t1.x=t2.y; drop table t1; -create table t1 (a int) type=heap; +create table t1 (a int) engine=heap; insert into t1 values(1); select max(a) from t1; drop table t1; -CREATE TABLE t1 ( a int not null default 0, b int not null default 0, key using BTREE (a,b), key using BTREE (b) ) TYPE=HEAP; +CREATE TABLE t1 ( a int not null default 0, b int not null default 0, key using BTREE (a,b), key using BTREE (b) ) ENGINE=HEAP; insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); select * from t1 where a=1; insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); @@ -73,7 +73,7 @@ select * from t1 where b=1; explain select * from t1 where b=1; drop table t1; -create table t1 (id int unsigned not null, primary key using BTREE (id)) type=HEAP; +create table t1 (id int unsigned not null, primary key using BTREE (id)) engine=HEAP; insert into t1 values(1); select max(id) from t1; insert into t1 values(2); @@ -81,16 +81,16 @@ select max(id) from t1; replace into t1 values(1); drop table t1; -create table t1 (n int) type=heap; +create table t1 (n int) engine=heap; drop table t1; -create table t1 (n int) type=heap; +create table t1 (n int) engine=heap; drop table if exists t1; # Test of non unique index CREATE table t1(f1 int not null,f2 char(20) not -null,index(f2)) type=heap; +null,index(f2)) engine=heap; INSERT into t1 set f1=12,f2="bill"; INSERT into t1 set f1=13,f2="bill"; INSERT into t1 set f1=14,f2="bill"; @@ -108,7 +108,7 @@ drop table t1; # Test when using part key searches # -create table t1 (btn char(10) not null, key using BTREE (btn)) type=heap; +create table t1 (btn char(10) not null, key using BTREE (btn)) engine=heap; insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i"); explain select * from t1 where btn like "q%"; select * from t1 where btn like "q%"; @@ -127,7 +127,7 @@ CREATE TABLE t1 ( b int default NULL, KEY a using BTREE (a), UNIQUE b using BTREE (b) -) type=heap; +) engine=heap; INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3); SELECT * FROM t1 WHERE a=NULL; explain SELECT * FROM t1 WHERE a IS NULL; @@ -140,7 +140,7 @@ SELECT * FROM t1 WHERE b<=>NULL; INSERT INTO t1 VALUES (1,3); DROP TABLE t1; -CREATE TABLE t1 (a int, b int, c int, key using BTREE (a, b, c)) type=heap; +CREATE TABLE t1 (a int, b int, c int, key using BTREE (a, b, c)) engine=heap; INSERT INTO t1 VALUES (1, NULL, NULL), (1, 1, NULL), (1, NULL, 1); SELECT * FROM t1 WHERE a=1 and b IS NULL; SELECT * FROM t1 WHERE a=1 and c IS NULL; @@ -151,7 +151,7 @@ DROP TABLE t1; # Test when deleting all rows # -CREATE TABLE t1 (a int not null, primary key using BTREE (a)) type=heap; +CREATE TABLE t1 (a int not null, primary key using BTREE (a)) engine=heap; INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11); DELETE from t1 where a < 100; SELECT * from t1; diff --git a/mysql-test/t/heap_hash.test b/mysql-test/t/heap_hash.test index f339a10efc6..412b6f2e705 100644 --- a/mysql-test/t/heap_hash.test +++ b/mysql-test/t/heap_hash.test @@ -6,7 +6,7 @@ drop table if exists t1; --enable_warnings -create table t1 (a int not null,b int not null, primary key using HASH (a)) type=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100; +create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100; insert into t1 values(1,1),(2,2),(3,3),(4,4); delete from t1 where a=1 or a=0; #show table status like "t1"; @@ -20,20 +20,20 @@ select * from t1; alter table t1 add c int not null, add key using HASH (c,a); drop table t1; -create table t1 (a int not null,b int not null, primary key using HASH (a)) type=heap comment="testing heaps"; +create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps"; insert into t1 values(1,1),(2,2),(3,3),(4,4); delete from t1 where a > 0; select * from t1; drop table t1; -create table t1 (a int not null,b int not null, primary key using HASH (a)) type=heap comment="testing heaps"; +create table t1 (a int not null,b int not null, primary key using HASH (a)) engine=heap comment="testing heaps"; insert into t1 values(1,1),(2,2),(3,3),(4,4); -alter table t1 modify a int not null auto_increment, type=myisam, comment="new myisam table"; +alter table t1 modify a int not null auto_increment, engine=myisam, comment="new myisam table"; #show table status like "t1"; select * from t1; drop table t1; -create table t1 (a int not null) type=heap; +create table t1 (a int not null) engine=heap; insert into t1 values (869751),(736494),(226312),(802616); select * from t1 where a > 736494; alter table t1 add unique uniq_id using HASH (a); @@ -41,31 +41,31 @@ select * from t1 where a > 736494; select * from t1 where a = 736494; select * from t1 where a=869751 or a=736494; select * from t1 where a in (869751,736494,226312,802616); -alter table t1 type=myisam; +alter table t1 engine=myisam; explain select * from t1 where a in (869751,736494,226312,802616); drop table t1; create table t1 (x int not null, y int not null, key x using HASH (x), unique y using HASH (y)) -type=heap; +engine=heap; insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6); select * from t1 where x=1; select * from t1,t1 as t2 where t1.x=t2.y; explain select * from t1,t1 as t2 where t1.x=t2.y; drop table t1; -create table t1 (a int) type=heap; +create table t1 (a int) engine=heap; insert into t1 values(1); select max(a) from t1; drop table t1; -CREATE TABLE t1 ( a int not null default 0, b int not null default 0, key using HASH (a), key using HASH (b) ) TYPE=HEAP; +CREATE TABLE t1 ( a int not null default 0, b int not null default 0, key using HASH (a), key using HASH (b) ) ENGINE=HEAP; insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); select * from t1 where a=1; insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6); select * from t1 where a=1; drop table t1; -create table t1 (id int unsigned not null, primary key using HASH (id)) type=HEAP; +create table t1 (id int unsigned not null, primary key using HASH (id)) engine=HEAP; insert into t1 values(1); select max(id) from t1; insert into t1 values(2); @@ -73,16 +73,16 @@ select max(id) from t1; replace into t1 values(1); drop table t1; -create table t1 (n int) type=heap; +create table t1 (n int) engine=heap; drop table t1; -create table t1 (n int) type=heap; +create table t1 (n int) engine=heap; drop table if exists t1; # Test of non unique index CREATE table t1(f1 int not null,f2 char(20) not -null,index(f2)) type=heap; +null,index(f2)) engine=heap; INSERT into t1 set f1=12,f2="bill"; INSERT into t1 set f1=13,f2="bill"; INSERT into t1 set f1=14,f2="bill"; @@ -100,7 +100,7 @@ drop table t1; # Test when using part key searches # -create table t1 (btn char(10) not null, key using HASH (btn)) type=heap; +create table t1 (btn char(10) not null, key using HASH (btn)) engine=heap; insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i"); explain select * from t1 where btn like "q%"; select * from t1 where btn like "q%"; @@ -119,7 +119,7 @@ CREATE TABLE t1 ( b int default NULL, KEY a using HASH (a), UNIQUE b using HASH (b) -) type=heap; +) engine=heap; INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3); SELECT * FROM t1 WHERE a=NULL; explain SELECT * FROM t1 WHERE a IS NULL; @@ -136,7 +136,7 @@ DROP TABLE t1; # Test when deleting all rows # -CREATE TABLE t1 (a int not null, primary key using HASH (a)) type=heap; +CREATE TABLE t1 (a int not null, primary key using HASH (a)) engine=heap; INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11); DELETE from t1 where a < 100; SELECT * from t1; diff --git a/mysql-test/t/help.test b/mysql-test/t/help.test index 82312340336..54c551ccd7a 100644 --- a/mysql-test/t/help.test +++ b/mysql-test/t/help.test @@ -65,10 +65,10 @@ help 'impossible_function_1'; help 'impossible_category_1'; ############## -alter table mysql.help_relation type=innodb; -alter table mysql.help_keyword type=innodb; -alter table mysql.help_topic type=innodb; -alter table mysql.help_category type=innodb; +alter table mysql.help_relation engine=innodb; +alter table mysql.help_keyword engine=innodb; +alter table mysql.help_topic engine=innodb; +alter table mysql.help_category engine=innodb; ############## help 'function_of_my_dream'; @@ -90,10 +90,10 @@ help 'impossible_function_1'; help 'impossible_category_1'; ############## -alter table mysql.help_relation type=myisam; -alter table mysql.help_keyword type=myisam; -alter table mysql.help_topic type=myisam; -alter table mysql.help_category type=myisam; +alter table mysql.help_relation engine=myisam; +alter table mysql.help_keyword engine=myisam; +alter table mysql.help_topic engine=myisam; +alter table mysql.help_category engine=myisam; delete from mysql.help_topic where help_topic_id=@topic1_id; delete from mysql.help_topic where help_topic_id=@topic2_id; diff --git a/mysql-test/t/innodb-deadlock.test b/mysql-test/t/innodb-deadlock.test index ba4ec4a9ae4..8a36982b0c1 100644 --- a/mysql-test/t/innodb-deadlock.test +++ b/mysql-test/t/innodb-deadlock.test @@ -12,7 +12,7 @@ drop table if exists t1,t2; # connection con1; -create table t1 (id integer, x integer) type=INNODB; +create table t1 (id integer, x integer) engine=INNODB; insert into t1 values(0, 0); set autocommit=0; SELECT * from t1 where id = 0 FOR UPDATE; @@ -44,8 +44,8 @@ drop table t1; # connection con1; -create table t1 (id integer, x integer) type=INNODB; -create table t2 (b integer, a integer) type=INNODB; +create table t1 (id integer, x integer) engine=INNODB; +create table t2 (b integer, a integer) engine=INNODB; insert into t1 values(0, 0), (300, 300); insert into t2 values(0, 10), (1, 20), (2, 30); commit; @@ -77,8 +77,8 @@ select * from t1; commit; drop table t1, t2; -create table t1 (id integer, x integer) type=INNODB; -create table t2 (b integer, a integer) type=INNODB; +create table t1 (id integer, x integer) engine=INNODB; +create table t2 (b integer, a integer) engine=INNODB; insert into t1 values(0, 0), (300, 300); insert into t2 values(0, 0), (1, 20), (2, 30); commit; diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 3349ea21c45..6f7a0e100c3 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -9,7 +9,7 @@ drop table if exists t1,t2,t3,t4; drop database if exists mysqltest; --enable_warnings -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)) type=innodb; +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; insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt'); select id, code, name from t1 order by id; @@ -34,7 +34,7 @@ CREATE TABLE t1 ( PRIMARY KEY (id), KEY parent_id (parent_id), KEY level (level) -) type=innodb; +) engine=innodb; 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); update t1 set parent_id=parent_id+100; select * from t1 where parent_id=102; @@ -67,7 +67,7 @@ CREATE TABLE t1 ( gesuchnr int(11) DEFAULT '0' NOT NULL, benutzer_id int(11) DEFAULT '0' NOT NULL, PRIMARY KEY (gesuchnr,benutzer_id) -) type=innodb; +) engine=innodb; replace into t1 (gesuchnr,benutzer_id) values (2,1); replace into t1 (gesuchnr,benutzer_id) values (1,1); @@ -79,7 +79,7 @@ drop table t1; # test delete using hidden_primary_key # -create table t1 (a int) type=innodb; +create table t1 (a int) engine=innodb; insert into t1 values (1), (2); optimize table t1; delete from t1 where a = 1; @@ -87,7 +87,7 @@ select * from t1; check table t1; drop table t1; -create table t1 (a int,b varchar(20)) type=innodb; +create table t1 (a int,b varchar(20)) engine=innodb; insert into t1 values (1,""), (2,"testing"); delete from t1 where a = 1; select * from t1; @@ -101,7 +101,7 @@ drop table t1; # Test of reading on secondary key with may be null -create table t1 (a int,b varchar(20),key(a)) type=innodb; +create table t1 (a int,b varchar(20),key(a)) engine=innodb; insert into t1 values (1,""), (2,"testing"); select * from t1 where a = 1; drop table t1; @@ -110,7 +110,7 @@ drop table t1; # Test rollback # -create table t1 (n int not null primary key) type=innodb; +create table t1 (n int not null primary key) engine=innodb; set autocommit=0; insert into t1 values (4); rollback; @@ -137,7 +137,7 @@ drop table t1; # Test for commit and FLUSH TABLES WITH READ LOCK # -create table t1 (n int not null primary key) type=innodb; +create table t1 (n int not null primary key) engine=innodb; start transaction; insert into t1 values (4); flush tables with read lock; @@ -154,7 +154,7 @@ drop table t1; # Testing transactions # -create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) type=innodb; +create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=innodb; begin; insert into t1 values(1,'hamdouni'); select id as afterbegin_id,nom as afterbegin_nom from t1; @@ -172,7 +172,7 @@ drop table t1; # Simple not autocommit test # -CREATE TABLE t1 (id char(8) not null primary key, val int not null) type=innodb; +CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=innodb; insert into t1 values ('pippo', 12); -- error 1062 insert into t1 values ('pippo', 12); # Gives error @@ -194,10 +194,10 @@ drop table t1; # Test of active transactions # -create table t1 (a integer) type=innodb; +create table t1 (a integer) engine=innodb; start transaction; rename table t1 to t2; -create table t1 (b integer) type=innodb; +create table t1 (b integer) engine=innodb; insert into t1 values (1); rollback; drop table t1; @@ -209,12 +209,12 @@ set autocommit=1; # The following simple tests failed at some point # -CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) TYPE=innodb; +CREATE TABLE t1 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(64)) ENGINE=innodb; INSERT INTO t1 VALUES (1, 'Jochen'); select * from t1; drop table t1; -CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) TYPE=innodb; +CREATE TABLE t1 ( _userid VARCHAR(60) NOT NULL PRIMARY KEY) ENGINE=innodb; set autocommit=0; INSERT INTO t1 SET _userid='marc@anyware.co.uk'; COMMIT; @@ -233,7 +233,7 @@ CREATE TABLE t1 ( ref_email varchar(100) DEFAULT '' NOT NULL, detail varchar(200), PRIMARY KEY (user_id,ref_email) -)type=innodb; +)engine=innodb; 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'); select * from t1 where user_id=10292; @@ -259,7 +259,7 @@ drop table t1; # create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)); -alter table t1 type=innodb; +alter table t1 engine=innodb; insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4'); select * from t1; update t1 set col2='7' where col1='4'; @@ -274,8 +274,8 @@ drop table t1; # INSERT INTO innodb tables # -create table t1 (a int not null , b int, primary key (a)) type = innodb; -create table t2 (a int not null , b int, primary key (a)) type = myisam; +create table t1 (a int not null , b int, primary key (a)) engine = innodb; +create table t2 (a int not null , b int, primary key (a)) engine = myisam; insert into t1 VALUES (1,3) , (2,3), (3,3); select * from t1; insert into t2 select * from t1; @@ -298,7 +298,7 @@ CREATE TABLE t1 ( passwd varchar(32) binary DEFAULT '' NOT NULL, PRIMARY KEY (id), UNIQUE ggid (ggid) -) TYPE=innodb; +) ENGINE=innodb; insert into t1 (ggid,passwd) values ('test1','xxx'); insert into t1 (ggid,passwd) values ('test2','yyy'); @@ -336,7 +336,7 @@ CREATE TABLE t1 ( approved datetime, dummy_primary_key int(11) NOT NULL auto_increment, PRIMARY KEY (dummy_primary_key) -) TYPE=innodb; +) ENGINE=innodb; INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1); INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2); 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); @@ -356,7 +356,7 @@ CREATE TABLE t1 ( KEY (id), KEY parent_id (parent_id), KEY level (level) -) type=innodb; +) engine=innodb; 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); INSERT INTO t1 values (179,5,2); update t1 set parent_id=parent_id+100; @@ -390,7 +390,7 @@ CREATE TABLE t1 ( sca_sch_desc varchar(16), PRIMARY KEY (sca_code, cat_code, lan_code), INDEX sca_pic (sca_pic) -) type = innodb ; +) engine = innodb ; 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'); select count(*) from t1 where sca_code = 'PD'; @@ -412,7 +412,7 @@ drop table t1; # Test of opening table twice and timestamps # set @a:=now(); -CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) type=innodb; +CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb; insert into t1 (a) values(1),(2),(3); select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a; update t1 set a=5 where a=1; @@ -422,7 +422,7 @@ drop table t1; # # Test with variable length primary key # -create table t1 (a varchar(100) not null, primary key(a), b int not null) type=innodb; +create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=innodb; insert into t1 values("hello",1),("world",2); select * from t1 order by b desc; optimize table t1; @@ -433,7 +433,7 @@ drop table t1; # # Test of create index with NULL columns # -create table t1 (i int, j int ) TYPE=innodb; +create table t1 (i int, j int ) ENGINE=innodb; insert into t1 values (1,2); select * from t1 where i=1 and j=2; create index ax1 on t1 (i,j); @@ -448,7 +448,7 @@ CREATE TABLE t1 ( a int3 unsigned NOT NULL, b int1 unsigned NOT NULL, UNIQUE (a, b) -) TYPE = innodb; +) ENGINE = innodb; INSERT INTO t1 VALUES (1, 1); SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1; @@ -458,7 +458,7 @@ drop table t1; # Test INSERT DELAYED # -CREATE TABLE t1 (a int unsigned NOT NULL) type=innodb; +CREATE TABLE t1 (a int unsigned NOT NULL) engine=innodb; # Can't test this in 3.23 # INSERT DELAYED INTO t1 VALUES (1); INSERT INTO t1 VALUES (1); @@ -470,7 +470,7 @@ DROP TABLE t1; # Crash when using many tables (Test case by Jeremy D Zawodny) # -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) type = innodb; +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; 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); --replace_column 9 # explain select * from t1 where a > 0 and a < 50; @@ -480,7 +480,7 @@ drop table t1; # Test lock tables # -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)) type=innodb; +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; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); LOCK TABLES t1 WRITE; --error 1062 @@ -490,7 +490,7 @@ select id from t1; UNLOCK TABLES; DROP TABLE t1; -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)) type=innodb; +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; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); LOCK TABLES t1 WRITE; begin; @@ -506,9 +506,9 @@ DROP TABLE t1; # # Test prefix key # -create table t1 (a char(20), unique (a(5))) type=innodb; +create table t1 (a char(20), unique (a(5))) engine=innodb; drop table t1; -create table t1 (a char(20), index (a(5))) type=innodb; +create table t1 (a char(20), index (a(5))) engine=innodb; show create table t1; drop table t1; @@ -516,7 +516,7 @@ drop table t1; # Test using temporary table and auto_increment # -create temporary table t1 (a int not null auto_increment, primary key(a)) type=innodb; +create temporary table t1 (a int not null auto_increment, primary key(a)) engine=innodb; insert into t1 values (NULL),(NULL),(NULL); delete from t1 where a=3; insert into t1 values (NULL); @@ -533,7 +533,7 @@ create table t1 value text not null, uid int not null, unique key(name,uid) - ) type=innodb; + ) engine=innodb; insert into t1 values (1,'one','one value',101), (2,'two','two value',102),(3,'three','three value',103); set insert_id=5; @@ -551,11 +551,11 @@ drop table t1; # create database mysqltest; -create table mysqltest.t1 (a int not null) type= innodb; +create table mysqltest.t1 (a int not null) engine= innodb; insert into mysqltest.t1 values(1); -create table mysqltest.t2 (a int not null) type= myisam; +create table mysqltest.t2 (a int not null) engine= myisam; insert into mysqltest.t2 values(1); -create table mysqltest.t3 (a int not null) type= heap; +create table mysqltest.t3 (a int not null) engine= heap; insert into mysqltest.t3 values(1); commit; drop database mysqltest; @@ -568,7 +568,7 @@ show tables from mysqltest; # set autocommit=0; -create table t1 (a int not null) type= innodb; +create table t1 (a int not null) engine= innodb; insert into t1 values(1),(2); --error 1192 truncate table t1; @@ -582,7 +582,7 @@ commit; drop table t1; set autocommit=1; -create table t1 (a int not null) type= innodb; +create table t1 (a int not null) engine= innodb; insert into t1 values(1),(2); truncate table t1; insert into t1 values(1),(2); @@ -597,7 +597,7 @@ drop table t1; # Test of how ORDER BY works when doing it on the whole table # -create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) type=innodb; +create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=innodb; insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); --replace_column 9 # explain select * from t1 order by a; @@ -621,7 +621,7 @@ drop table t1; # Check describe # -create table t1 (t int not null default 1, key (t)) type=innodb; +create table t1 (t int not null default 1, key (t)) engine=innodb; desc t1; drop table t1; @@ -641,7 +641,7 @@ CREATE TABLE t1 ( version smallint(6) NOT NULL default '0', assigned_scps int(11) default '0', status tinyint(4) default '0' -) TYPE=InnoDB; +) ENGINE=InnoDB; INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1); INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0); INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1); @@ -660,7 +660,7 @@ CREATE TABLE t2 ( version smallint(6) NOT NULL default '0', assigned_scps int(11) default '0', status tinyint(4) default '0' -) TYPE=InnoDB; +) ENGINE=InnoDB; INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1); INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0); INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1); @@ -679,7 +679,7 @@ drop table t1,t2; # this works. # -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)) type=innodb; +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; BEGIN; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; @@ -704,8 +704,8 @@ DROP TABLE t1; # # Test of multi-table-update # -create table t1 (n int(10), d int(10)) type=innodb; -create table t2 (n int(10), d int(10)) type=innodb; +create table t1 (n int(10), d int(10)) engine=innodb; +create table t2 (n int(10), d int(10)) engine=innodb; insert into t1 values(1,1),(1,2); insert into t2 values(1,10),(2,20); UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n; @@ -716,7 +716,7 @@ drop table t1,t2; # # Testing of IFNULL # -create table t1 (a int, b int) type=innodb; +create table t1 (a int, b int) engine=innodb; insert into t1 values(20,null); select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on t2.b=t3.a; @@ -731,12 +731,12 @@ drop table t1; # Test of read_through not existing const_table # -create table t1 (a varchar(10) not null) type=myisam; -create table t2 (b varchar(10) not null unique) type=innodb; +create table t1 (a varchar(10) not null) engine=myisam; +create table t2 (b varchar(10) not null unique) engine=innodb; select t1.a from t1,t2 where t1.a=t2.b; drop table t1,t2; -create table t1 (a int not null, b int, primary key (a)) type = innodb; -create table t2 (a int not null, b int, primary key (a)) type = innodb; +create table t1 (a int not null, b int, primary key (a)) engine = innodb; +create table t2 (a int not null, b int, primary key (a)) engine = innodb; insert into t1 values (10, 20); insert into t2 values (10, 20); update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10; @@ -746,8 +746,8 @@ drop table t1,t2; # Test of multi-table-delete with foreign key constraints # -CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; -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 ) TYPE=INNODB; +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) ON DELETE CASCADE ) ENGINE=INNODB; insert into t1 set id=1; insert into t2 set id=1, t1_id=1; delete t1,t2 from t1,t2 where t1.id=t2.t1_id; @@ -755,8 +755,8 @@ select * from t1; select * from t2; drop table t1,t2; DROP TABLE IF EXISTS t1,t2; -CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; -CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) TYPE=INNODB; +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) ) ENGINE=INNODB; INSERT INTO t1 VALUES(1); INSERT INTO t2 VALUES(1, 1); SELECT * from t1; @@ -772,11 +772,11 @@ DROP TABLE t1,t2; set autocommit=0; -CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) TYPE=InnoDB; +CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; -CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) TYPE=InnoDB; +CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; -CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) TYPE=InnoDB; +CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) ENGINE=InnoDB; INSERT INTO t3 VALUES("my-test-1", "my-test-2"); COMMIT; @@ -802,7 +802,7 @@ DROP TABLE t1,t2,t3; # Check update with conflicting key # -CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) type=innodb; +CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=innodb; INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); # We need the a < 1000 test here to quard against the halloween problems UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000; @@ -813,8 +813,8 @@ drop table t1; # Test multi update with different join methods # -CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) type=innodb; -CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) type=innodb; +CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=innodb; +CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=innodb; 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); INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); @@ -836,15 +836,15 @@ select * from t1; select * from t2; drop table t1,t2; -CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) TYPE=MyISAM; -CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) TYPE=InnoDB; +CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM; +CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB; SET AUTOCOMMIT=0; INSERT INTO t1 ( B_ID ) VALUES ( 1 ); INSERT INTO t2 ( NEXT_T ) VALUES ( 1 ); ROLLBACK; SELECT * FROM t1; drop table t1,t2; -create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) type = innodb; +create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = innodb; insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2); select distinct parent,child from t1 order by parent; drop table t1; @@ -852,7 +852,7 @@ drop table t1; # # Test that MySQL priorities clustered indexes # -create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) type=innodb; +create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb; create table t2 (a int not null auto_increment primary key, b int); insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null); insert into t2 (a) select b from t1; @@ -885,7 +885,7 @@ drop table t1,t2; # Test of UPDATE ... ORDER BY # -create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) type=innodb; +create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=innodb; insert into t1 (id) values (null),(null),(null),(null),(null); update t1 set fk=69 where fk is null order by id limit 1; @@ -903,9 +903,9 @@ select * from t1 order by a,b; drop table t1; -CREATE TABLE t1 (col1 int(1))TYPE=InnoDB; +CREATE TABLE t1 (col1 int(1))ENGINE=InnoDB; CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx -(stamp))TYPE=InnoDB; +(stamp))ENGINE=InnoDB; insert into t1 values (1),(2),(3); insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ); SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp < @@ -925,7 +925,7 @@ CREATE TABLE t1 ( PRIMARY KEY (`id`), KEY `id_object` (`id_object`), KEY `id_version` (`id_version`) -) TYPE=InnoDB; +) ENGINE=InnoDB; 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); @@ -934,7 +934,7 @@ CREATE TABLE t2 ( `id_version` int(10) unsigned NOT NULL default '1', PRIMARY KEY (`id`), KEY `id_version` (`id_version`) -) TYPE=InnoDB; +) ENGINE=InnoDB; INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9"); @@ -946,7 +946,7 @@ drop table t1,t2; # # Bug #1078 # -create table t1 (c1 char(5) unique not null, c2 int, stamp timestamp) type=innodb; +create table t1 (c1 char(5) unique not null, c2 int, stamp timestamp) engine=innodb; select * from t1; --error 1031 replace delayed into t1 (c1, c2) values ( "text1","11"),( "text2","12"); @@ -956,9 +956,9 @@ replace delayed into t1 (c1, c2) values ( "text1","12"),( "text2","13"),( "text select * from t1; drop table t1; -create table t1 (a int, b varchar(200), c text not null) checksum=1 type=myisam; -create table t2 (a int, b varchar(200), c text not null) checksum=0 type=innodb; -create table t3 (a int, b varchar(200), c text not null) checksum=1 type=innodb; +create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=myisam; +create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=innodb; +create table t3 (a int, b varchar(200), c text not null) checksum=1 engine=innodb; insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, ""); insert t2 select * from t1; insert t3 select * from t1; diff --git a/mysql-test/t/innodb_cache.test b/mysql-test/t/innodb_cache.test index 8a08c19703d..8568fce7d97 100644 --- a/mysql-test/t/innodb_cache.test +++ b/mysql-test/t/innodb_cache.test @@ -11,7 +11,7 @@ drop table if exists t1,t2,t3; # flush status; set autocommit=0; -create table t1 (a int not null) type=innodb; +create table t1 (a int not null) engine=innodb; insert into t1 values (1),(2),(3); select * from t1; show status like "Qcache_queries_in_cache"; @@ -19,15 +19,15 @@ drop table t1; commit; set autocommit=1; begin; -create table t1 (a int not null) type=innodb; +create table t1 (a int not null) engine=innodb; insert into t1 values (1),(2),(3); select * from t1; show status like "Qcache_queries_in_cache"; drop table t1; commit; -create table t1 (a int not null) type=innodb; -create table t2 (a int not null) type=innodb; -create table t3 (a int not null) type=innodb; +create table t1 (a int not null) engine=innodb; +create table t2 (a int not null) engine=innodb; +create table t3 (a int not null) engine=innodb; insert into t1 values (1),(2); insert into t2 values (1),(2); insert into t3 values (1),(2); @@ -54,7 +54,7 @@ commit; show status like "Qcache_queries_in_cache"; drop table if exists t1; -CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) TYPE=InnoDB; +CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=InnoDB; select count(*) from t1; insert into t1 (id) values (0); select count(*) from t1; diff --git a/mysql-test/t/innodb_handler.test b/mysql-test/t/innodb_handler.test index cf6ac9870ce..e8c486caf66 100644 --- a/mysql-test/t/innodb_handler.test +++ b/mysql-test/t/innodb_handler.test @@ -8,7 +8,7 @@ drop table if exists t1,t2; --enable_warnings -create table t1 (a int, b char(10), key a(a), key b(a,b)) type=innodb; +create table t1 (a int, b char(10), key a(a), key b(a,b)) engine=innodb; insert into t1 values (17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), (14,"aaa"),(15,"bbb"),(16,"ccc"),(16,"xxx"), @@ -71,12 +71,12 @@ handler t2 close; handler t1 open as t2; handler t2 read first; -alter table t1 type=innodb; +alter table t1 engine=innodb; --error 1109 handler t2 read first; drop table t1; -CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY (no1,no2)) TYPE=InnoDB; +CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY (no1,no2)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2); HANDLER t1 OPEN; HANDLER t1 READ `primary` = (1, 1000); diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 4effed9b6de..e39e470cf31 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -35,7 +35,7 @@ CREATE TABLE `t1` ( KEY `date` (`date`), KEY `pseudo` (`pseudo`), KEY `numreponse` (`numreponse`) -) TYPE=MyISAM; +) ENGINE=MyISAM; CREATE TABLE `t2` ( `numeropost` bigint(20) unsigned NOT NULL default '0', @@ -51,7 +51,7 @@ CREATE TABLE `t2` ( KEY `date` (`date`), KEY `pseudo` (`pseudo`), KEY `numreponse` (`numreponse`) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t2 (numeropost,icone,numreponse,contenu,pseudo,date,ip,signature) VALUES diff --git a/mysql-test/t/isam.test b/mysql-test/t/isam.test index e35ed0dfd4a..f77d29fd20f 100644 --- a/mysql-test/t/isam.test +++ b/mysql-test/t/isam.test @@ -8,7 +8,7 @@ drop table if exists t1,t2; # Test possible problem with rows that are about 65535 bytes long # -create table t1 (a tinyint not null auto_increment, b blob not null, primary key (a)) type=isam; +create table t1 (a tinyint not null auto_increment, b blob not null, primary key (a)) engine=isam; let $1=100; disable_query_log; @@ -28,7 +28,7 @@ drop table t1; # Test of auto_increment; The test for BDB tables is in bdb.test # -create table t1 (a int not null auto_increment,b int, primary key (a)) type=isam; +create table t1 (a int not null auto_increment,b int, primary key (a)) engine=isam; insert into t1 values (1,1),(NULL,2),(3,3),(NULL,4); delete from t1 where a=4 or a=2; insert into t1 values (NULL,4),(NULL,5),(6,6); @@ -46,13 +46,13 @@ drop table t1; # Test of some CREATE TABLE's that should fail # --error 1121 -create table t1 (a int,b text, index(a)) type=isam; +create table t1 (a int,b text, index(a)) engine=isam; --error 1073 -create table t1 (a int,b text, index(b)) type=isam; +create table t1 (a int,b text, index(b)) engine=isam; --error 1075 -create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) type=isam; +create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) engine=isam; --error 1121 -create table t1 (ordid int(8), unique (ordid)) type=isam; +create table t1 (ordid int(8), unique (ordid)) engine=isam; drop table if exists t1; # @@ -61,7 +61,7 @@ drop table if exists t1; create table t1 (a int not null primary key, b int not null,c int not null, key(b,c)); insert into t1 values (1,2,2),(2,2,3),(3,2,4),(4,2,4); -create table t2 type=isam select * from t1; +create table t2 engine=isam select * from t1; optimize table t1; check table t1,t2; repair table t1,t2; diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 8cef925cc24..bba5cdeee58 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -126,13 +126,13 @@ CREATE TABLE t1 ( a int(11) NOT NULL, b int(11) NOT NULL, PRIMARY KEY (a,b) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(2,3); CREATE TABLE t2 ( a int(11) default NULL -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t2 VALUES (2),(3); SELECT t1.a,t2.a,b FROM t1,t2 WHERE t1.a=t2.a AND (t1.a=1 OR t1.a=2) AND b>=1 AND b<=3; DROP TABLE t1, t2; @@ -235,13 +235,13 @@ CREATE TABLE t1 ( max_value double default NULL, t3_id int(11) default NULL, item_id int(11) default NULL -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES (12,5,'Percent','Cost',-1,0,-1,-1),(14,4,'Percent','Cost',-1,0,-1,-1),(18,5,'Percent','Cost',-1,0,-1,-1),(19,4,'Percent','Cost',-1,0,-1,-1),(20,5,'Percent','Cost',100,-1,22,291),(21,5,'Percent','Cost',100,-1,18,291),(22,1,'Percent','Cost',100,-1,6,291),(23,1,'Percent','Cost',100,-1,21,291),(24,1,'Percent','Cost',100,-1,9,291),(25,1,'Percent','Cost',100,-1,4,291),(26,1,'Percent','Cost',100,-1,20,291),(27,4,'Percent','Cost',100,-1,7,202),(28,1,'Percent','Cost',50,-1,-1,137),(29,2,'Percent','Cost',100,-1,4,354),(30,2,'Percent','Cost',100,-1,9,137),(93,2,'Cost','Cost',-1,10000000,-1,-1); CREATE TABLE t2 ( id int(10) unsigned NOT NULL auto_increment, name varchar(255) default NULL, PRIMARY KEY (id) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,'s1'),(2,'s2'),(3,'s3'),(4,'s4'),(5,'s5'); select t1.*, t2.* from t1, t2 where t2.id=t1.t2_id limit 2; drop table t1,t2; @@ -256,7 +256,7 @@ CREATE TABLE t1 ( rate_code varchar(10) default NULL, UNIQUE KEY site_emp (siteid,emp_id), KEY siteid (siteid) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES ('rivercats','psmith','cust'), ('rivercats','KWalker','cust'); CREATE TABLE t2 ( siteid varchar(25) NOT NULL default '', @@ -264,7 +264,7 @@ CREATE TABLE t2 ( base_rate float NOT NULL default '0', PRIMARY KEY (siteid,rate_code), FULLTEXT KEY rate_code (rate_code) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t2 VALUES ('rivercats','cust',20); SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE emp.emp_id = 'psmith' AND lr.siteid = 'rivercats'; SELECT emp.rate_code, lr.base_rate FROM t1 AS emp LEFT JOIN t2 AS lr USING (siteid, rate_code) WHERE lr.siteid = 'rivercats' AND emp.emp_id = 'psmith'; diff --git a/mysql-test/t/join_crash.test b/mysql-test/t/join_crash.test index ada6e56e664..2d2222ad67a 100644 --- a/mysql-test/t/join_crash.test +++ b/mysql-test/t/join_crash.test @@ -17,7 +17,7 @@ CREATE TABLE t1 ( comments mediumtext, PRIMARY KEY (project_id), UNIQUE KEY project (client_ptr,project_name) -) TYPE=MyISAM PACK_KEYS=1; +) ENGINE=MyISAM PACK_KEYS=1; INSERT INTO t1 VALUES (1,0,'Rejected Time',1,NULL,NULL,NULL,NULL); INSERT INTO t1 VALUES (209,0,'MDGRAD Proposal/Investigation',97,NULL,NULL,NULL,''); @@ -40,7 +40,7 @@ CREATE TABLE t2 ( PRIMARY KEY (period_id), KEY period_index (period_type,period_key), KEY date_index (start_date,end_date) -) TYPE=MyISAM PACK_KEYS=1; +) ENGINE=MyISAM PACK_KEYS=1; INSERT INTO t2 VALUES (1,'user_table',98,'2000-01-01 00:00:00',NULL,NULL); INSERT INTO t2 VALUES (2,'user_table',99,'2000-01-01 00:00:00',NULL,NULL); @@ -68,7 +68,7 @@ CREATE TABLE t3 ( adjustment float(10,2) default NULL, PRIMARY KEY (budget_id), UNIQUE KEY po (project_ptr,po_number) -) TYPE=MyISAM PACK_KEYS=1; +) ENGINE=MyISAM PACK_KEYS=1; CREATE TABLE t4 ( client_id int(11) NOT NULL auto_increment, @@ -78,7 +78,7 @@ CREATE TABLE t4 ( comments mediumtext, PRIMARY KEY (client_id), UNIQUE KEY client_name (client_name) -) TYPE=MyISAM PACK_KEYS=1; +) ENGINE=MyISAM PACK_KEYS=1; INSERT INTO t4 VALUES (1,0,'CPS',NULL,NULL); diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index a63defff540..d048208a8ff 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -10,7 +10,7 @@ CREATE TABLE t1 ( grp int(11) default NULL, a bigint(20) unsigned default NULL, c char(10) NOT NULL default '' -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,1,'a'),(2,2,'b'),(2,3,'c'),(3,4,'E'),(3,5,'C'),(3,6,'D'),(NULL,NULL,''); create table t2 (id int, a bigint unsigned not null, c char(10), d int, primary key (a)); insert into t2 values (1,1,"a",1),(3,4,"A",4),(3,5,"B",5),(3,6,"C",6),(4,7,"D",7); diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test index 3b4cd7358cc..109cbb91e2f 100644 --- a/mysql-test/t/key.test +++ b/mysql-test/t/key.test @@ -158,7 +158,7 @@ CREATE TABLE t1 ( a tinytext NOT NULL, b tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (a(32),b) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES ('a',1),('a',2); SELECT * FROM t1 WHERE a='a' AND b=2; SELECT * FROM t1 WHERE a='a' AND b in (2); diff --git a/mysql-test/t/lock.test b/mysql-test/t/lock.test index 0868cf2c4b1..69c453bf60a 100644 --- a/mysql-test/t/lock.test +++ b/mysql-test/t/lock.test @@ -5,7 +5,7 @@ --disable_warnings drop table if exists t1,t2; --enable_warnings -CREATE TABLE t1 ( `id` int(11) NOT NULL default '0', `id2` int(11) NOT NULL default '0', `id3` int(11) NOT NULL default '0', `dummy1` char(30) default NULL, PRIMARY KEY (`id`,`id2`), KEY `index_id3` (`id3`)) TYPE=MyISAM; +CREATE TABLE t1 ( `id` int(11) NOT NULL default '0', `id2` int(11) NOT NULL default '0', `id3` int(11) NOT NULL default '0', `dummy1` char(30) default NULL, PRIMARY KEY (`id`,`id2`), KEY `index_id3` (`id3`)) ENGINE=MyISAM; insert into t1 (id,id2) values (1,1),(1,2),(1,3); LOCK TABLE t1 WRITE; select dummy1,count(distinct id) from t1 group by dummy1; @@ -33,12 +33,12 @@ CREATE TABLE t1 ( index1 smallint(6) default NULL, nr smallint(6) default NULL, KEY index1(index1) -) TYPE=MyISAM; +) ENGINE=MyISAM; CREATE TABLE t2 ( nr smallint(6) default NULL, name varchar(20) default NULL -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,'item1'); INSERT INTO t2 VALUES (2,'item2'); diff --git a/mysql-test/t/lock_tables_lost_commit.test b/mysql-test/t/lock_tables_lost_commit.test index d8301017801..22b666cbc86 100644 --- a/mysql-test/t/lock_tables_lost_commit.test +++ b/mysql-test/t/lock_tables_lost_commit.test @@ -6,7 +6,7 @@ connect (con2,localhost,root,,); connection con1; --disable_warnings drop table if exists t1; -create table t1(a int) type=innodb; +create table t1(a int) engine=innodb; --enable_warnings lock tables t1 write; insert into t1 values(10); diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index c3bffa4f2fb..1f61e200613 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -11,7 +11,7 @@ create table t1 (a int not null primary key auto_increment, message char(20)); create table t2 (a int not null primary key auto_increment, message char(20)); INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1"); INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2"); -create table t3 (a int not null, b char(20), key(a)) type=MERGE UNION=(t1,t2); +create table t3 (a int not null, b char(20), key(a)) engine=MERGE UNION=(t1,t2); select * from t3; select * from t3 order by a desc; drop table t3; @@ -26,7 +26,7 @@ insert into t2 select NULL,message from t1; insert into t1 select NULL,message from t2; insert into t2 select NULL,message from t1; insert into t1 select NULL,message from t2; -create table t3 (a int not null, b char(20), key(a)) type=MERGE UNION=(test.t1,test.t2); +create table t3 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,test.t2); explain select * from t3 where a < 10; explain select * from t3 where a > 10 and a < 20; select * from t3 where a = 10; @@ -46,7 +46,7 @@ select * from t3 where a < 10; show create table t3; # The following should give errors -create table t4 (a int not null, b char(10), key(a)) type=MERGE UNION=(t1,t2); +create table t4 (a int not null, b char(10), key(a)) engine=MERGE UNION=(t1,t2); --error 1016 select * from t4; --error 1016 @@ -57,18 +57,18 @@ alter table t4 add column c int; # create database mysqltest; create table mysqltest.t6 (a int not null primary key auto_increment, message char(20)); -create table t5 (a int not null, b char(20), key(a)) type=MERGE UNION=(test.t1,mysqltest.t6); +create table t5 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,mysqltest.t6); show create table t5; -alter table t5 type=myisam; +alter table t5 engine=myisam; drop table t5, mysqltest.t6; drop database mysqltest; # Because of windows, it's important that we drop the merge tables first! drop table t4,t3,t1,t2; -create table t1 (c char(10)) type=myisam; -create table t2 (c char(10)) type=myisam; -create table t3 (c char(10)) union=(t1,t2) type=merge; +create table t1 (c char(10)) engine=myisam; +create table t2 (c char(10)) engine=myisam; +create table t3 (c char(10)) union=(t1,t2) engine=merge; insert into t1 (c) values ('test1'); insert into t1 (c) values ('test1'); insert into t1 (c) values ('test1'); @@ -89,7 +89,7 @@ drop table t3,t2,t1; CREATE TABLE t1 (incr int not null, othr int not null, primary key(incr)); CREATE TABLE t2 (incr int not null, othr int not null, primary key(incr)); CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr)) -TYPE=MERGE UNION=(t1,t2); +ENGINE=MERGE UNION=(t1,t2); SELECT * from t3; @@ -103,14 +103,14 @@ alter table t3 UNION=(t1); select count(*) from t3; alter table t3 UNION=(t1,t2); select count(*) from t3; -alter table t3 TYPE=MYISAM; +alter table t3 ENGINE=MYISAM; select count(*) from t3; # Test that ALTER TABLE rembers the old UNION drop table t3; CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr)) -TYPE=MERGE UNION=(t1,t2); +ENGINE=MERGE UNION=(t1,t2); show create table t3; alter table t3 drop primary key; show create table t3; @@ -120,7 +120,7 @@ drop table t3,t2,t1; # # Test table without unions # -create table t1 (a int not null, key(a)) type=merge; +create table t1 (a int not null, key(a)) engine=merge; select * from t1; drop table t1; @@ -130,7 +130,7 @@ drop table t1; create table t1 (a int not null, b int not null, key(a,b)); create table t2 (a int not null, b int not null, key(a,b)); -create table t3 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2); +create table t3 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2); insert into t1 values (1,2),(2,1),(0,0),(4,4),(5,5),(6,6); insert into t2 values (1,1),(2,2),(0,0),(4,4),(5,5),(6,6); flush tables; @@ -145,9 +145,9 @@ drop table t3,t1,t2; create table t1 (a int not null, b int not null auto_increment, primary key(a,b)); create table t2 (a int not null, b int not null auto_increment, primary key(a,b)); create table t3 (a int not null, b int not null, key(a,b)) UNION=(t1,t2) INSERT_METHOD=NO; -create table t4 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=NO; -create table t5 (a int not null, b int not null auto_increment, primary key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; -create table t6 (a int not null, b int not null auto_increment, primary key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; +create table t4 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=NO; +create table t5 (a int not null, b int not null auto_increment, primary key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; +create table t6 (a int not null, b int not null auto_increment, primary key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; show create table t3; show create table t4; show create table t5; @@ -192,11 +192,11 @@ insert into t1 values (99,NULL); select * from t4 where a+0 > 90; drop table t6, t5, t4, t3, t2, t1; -CREATE TABLE t1 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) TYPE=MyISAM; +CREATE TABLE t1 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,1), (2,1); -CREATE TABLE t2 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) TYPE=MyISAM; +CREATE TABLE t2 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,2), (2,2); -CREATE TABLE t3 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', KEY a (a,b)) TYPE=MRG_MyISAM UNION=(t1,t2); +CREATE TABLE t3 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', KEY a (a,b)) ENGINE=MRG_MyISAM UNION=(t1,t2); select max(b) from t3 where a = 2; select max(b) from t1 where a = 2; drop table t3,t1,t2; @@ -208,13 +208,13 @@ create table t1 (a int not null); create table t2 (a int not null); insert into t1 values (1); insert into t2 values (2); -create temporary table t3 (a int not null) TYPE=MERGE UNION=(t1,t2); +create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2); select * from t3; create temporary table t4 (a int not null); create temporary table t5 (a int not null); insert into t4 values (1); insert into t5 values (2); -create temporary table t6 (a int not null) TYPE=MERGE UNION=(t4,t5); +create temporary table t6 (a int not null) ENGINE=MERGE UNION=(t4,t5); select * from t6; drop table t6, t3, t1, t2, t4, t5; @@ -228,7 +228,7 @@ CREATE TABLE t1 ( fileset_root_id tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (fileset_id,file_code), KEY files (fileset_id,fileset_root_id) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES (2, '0000000111', 1), (2, '0000000112', 1), (2, '0000000113', 1), (2, '0000000114', 1), (2, '0000000115', 1), (2, '0000000116', 1), (2, '0000000117', 1), (2, '0000000118', 1), (2, '0000000119', 1), (2, '0000000120', 1); @@ -238,7 +238,7 @@ CREATE TABLE t2 ( fileset_root_id tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (fileset_id,file_code), KEY files (fileset_id,fileset_root_id) -) TYPE=MRG_MyISAM UNION=(t1); +) ENGINE=MRG_MyISAM UNION=(t1); EXPLAIN SELECT * FROM t2 IGNORE INDEX (files) WHERE fileset_id = 2 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1; @@ -256,7 +256,7 @@ DROP TABLE t2, t1; create table t1 (x int, y int, index xy(x, y)); create table t2 (x int, y int, index xy(x, y)); -create table t3 (x int, y int, index xy(x, y)) type=merge union=(t1,t2); +create table t3 (x int, y int, index xy(x, y)) engine=merge union=(t1,t2); insert into t1 values(1, 2); insert into t2 values(1, 3); select * from t3 where x = 1 and y < 5 order by y; diff --git a/mysql-test/t/mix_innodb_myisam_binlog.test b/mysql-test/t/mix_innodb_myisam_binlog.test index 26f7cbf22a0..9c576b080ca 100644 --- a/mysql-test/t/mix_innodb_myisam_binlog.test +++ b/mysql-test/t/mix_innodb_myisam_binlog.test @@ -15,8 +15,8 @@ connect (con1,localhost,root,,); connect (con2,localhost,root,,); connection con1; -create table t1 (a int) type=innodb; -create table t2 (a int) type=myisam; +create table t1 (a int) engine=innodb; +create table t2 (a int) engine=myisam; reset master; @@ -118,7 +118,7 @@ show binlog events from 79; # Check that things work like before this BEGIN/ROLLBACK code was added, # when t2 is INNODB -alter table t2 type=INNODB; +alter table t2 engine=INNODB; delete from t1; delete from t2; diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index b3c742e0b30..115007d9e63 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -85,19 +85,19 @@ CREATE TABLE t1 ( id int(11) NOT NULL default '0', name varchar(10) default NULL, PRIMARY KEY (id) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,'aaa'),(2,'aaa'),(3,'aaa'); CREATE TABLE t2 ( id int(11) NOT NULL default '0', name varchar(10) default NULL, PRIMARY KEY (id) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb'); CREATE TABLE t3 ( id int(11) NOT NULL default '0', mydate datetime default NULL, PRIMARY KEY (id) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t3 VALUES (1,'2002-02-04 00:00:00'),(3,'2002-05-12 00:00:00'),(5,'2002-05-12 00:00:00'),(6,'2002-06-22 00:00:00'),(7,'2002-07-22 00:00:00'); delete t1,t2,t3 from t1,t2,t3 where to_days(now())-to_days(t3.mydate)>=30 and t3.id=t1.id and t3.id=t2.id; @@ -109,7 +109,7 @@ CREATE TABLE IF NOT EXISTS `t1` ( `tst` text, `tst1` text, PRIMARY KEY (`id`) -) TYPE=MyISAM; +) ENGINE=MyISAM; CREATE TABLE IF NOT EXISTS `t2` ( `ID` int(11) NOT NULL auto_increment, @@ -119,7 +119,7 @@ CREATE TABLE IF NOT EXISTS `t2` ( PRIMARY KEY (`ID`), KEY `IX_ParId_t2` (`ParId`), FOREIGN KEY (`ParId`) REFERENCES `t1` (`id`) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1(tst,tst1) VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE"); @@ -223,11 +223,11 @@ select * from t1; select * from t2; drop table t1,t2; -CREATE TABLE t1 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) TYPE=MyISAM; +CREATE TABLE t1 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a'),(10,''),(11,''),(12,''),(13,''); -CREATE TABLE t2 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) TYPE=MyISAM; +CREATE TABLE t2 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a'); -CREATE TABLE t3 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) TYPE=MyISAM; +CREATE TABLE t3 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM; INSERT INTO t3 VALUES (1,'jedan'),(2,'dva'); update t1,t2 set t1.naziv="aaaa" where t1.broj=t2.broj; update t1,t2,t3 set t1.naziv="bbbb", t2.naziv="aaaa" where t1.broj=t2.broj and t2.broj=t3.broj; @@ -260,7 +260,7 @@ select * from t1; select * from t2; drop table t1,t2; -CREATE TABLE t3 ( KEY1 varchar(50) NOT NULL default '', PARAM_CORR_DISTANCE_RUSH double default NULL, PARAM_CORR_DISTANCE_GEM double default NULL, PARAM_AVG_TARE double default NULL, PARAM_AVG_NB_DAYS double default NULL, PARAM_DEFAULT_PROP_GEM_SRVC varchar(50) default NULL, PARAM_DEFAULT_PROP_GEM_NO_ETIK varchar(50) default NULL, PARAM_SCENARIO_COSTS varchar(50) default NULL, PARAM_DEFAULT_WAGON_COST double default NULL, tmp int(11) default NULL, PRIMARY KEY (KEY1)) TYPE=MyISAM; +CREATE TABLE t3 ( KEY1 varchar(50) NOT NULL default '', PARAM_CORR_DISTANCE_RUSH double default NULL, PARAM_CORR_DISTANCE_GEM double default NULL, PARAM_AVG_TARE double default NULL, PARAM_AVG_NB_DAYS double default NULL, PARAM_DEFAULT_PROP_GEM_SRVC varchar(50) default NULL, PARAM_DEFAULT_PROP_GEM_NO_ETIK varchar(50) default NULL, PARAM_SCENARIO_COSTS varchar(50) default NULL, PARAM_DEFAULT_WAGON_COST double default NULL, tmp int(11) default NULL, PRIMARY KEY (KEY1)) ENGINE=MyISAM; INSERT INTO t3 VALUES ('A',1,1,22,3.2,'R','R','BASE2',0.24,NULL); create table t1 (A varchar(1)); insert into t1 values ("A") ,("B"),("C"),("D"); diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 5dceb2b4598..85e5c7a7751 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -15,7 +15,7 @@ SET SQL_WARNINGS=1; CREATE TABLE t1 ( STRING_DATA char(255) default NULL, KEY string_data (STRING_DATA) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'); INSERT INTO t1 VALUES ('DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD'); @@ -68,7 +68,7 @@ drop table t1; # Test of how ORDER BY works when doing it on the whole table # -create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) type=myisam; +create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=myisam; insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); explain select * from t1 order by a; explain select * from t1 order by b; @@ -324,7 +324,7 @@ CREATE TABLE `t1` ( KEY `poster_login` (`poster_login`), KEY `topic_id` (`topic_id`), FULLTEXT KEY `post_text` (`post_text`) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 (post_text) VALUES ('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'); @@ -402,7 +402,7 @@ drop table t1; # Test RTREE index # --error 1235 -CREATE TABLE t1 (`a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', UNIQUE KEY `a` USING RTREE (`a`,`b`)) TYPE=MyISAM; +CREATE TABLE t1 (`a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', UNIQUE KEY `a` USING RTREE (`a`,`b`)) ENGINE=MyISAM; # INSERT INTO t1 VALUES (1,1),(1,1); # DELETE FROM rt WHERE a<1; # DROP TABLE IF EXISTS t1; diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test index 5fa8f8a7651..c11ed78253b 100644 --- a/mysql-test/t/null.test +++ b/mysql-test/t/null.test @@ -45,7 +45,7 @@ DROP TABLE t1; # # Testing of IFNULL # -create table t1 (a int, b int) type=myisam; +create table t1 (a int, b int) engine=myisam; insert into t1 values(20,null); select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on t2.b=t3.a; diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test index 7d9500e90dd..9b346a181bf 100644 --- a/mysql-test/t/null_key.test +++ b/mysql-test/t/null_key.test @@ -5,7 +5,7 @@ drop table if exists t1,t2; --enable_warnings -create table t1 (a int, b int not null,unique key (a,b),index(b)) type=myisam; +create table t1 (a int, b int not null,unique key (a,b),index(b)) engine=myisam; insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); explain select * from t1 where a is null; explain select * from t1 where a is null and b = 2; @@ -92,13 +92,13 @@ CREATE TABLE t1 ( uniq_id int(10) unsigned default NULL, PRIMARY KEY (id), UNIQUE KEY idx1 (uniq_id) -) TYPE=MyISAM; +) ENGINE=MyISAM; CREATE TABLE t2 ( id int(10) unsigned NOT NULL auto_increment, uniq_id int(10) unsigned default NULL, PRIMARY KEY (id) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL); INSERT INTO t2 VALUES (1,NULL),(2,NULL),(3,1),(4,2),(5,NULL),(6,NULL),(7,3),(8,4),(9,NULL),(10,NULL); @@ -136,13 +136,13 @@ CREATE TABLE `t1` ( `product_id` char(32) NOT NULL default '', `product_type` int(11) NOT NULL default '0', PRIMARY KEY (`order_id`,`product_id`,`product_type`) -) TYPE=MyISAM; +) ENGINE=MyISAM; CREATE TABLE `t2` ( `order_id` char(32) NOT NULL default '', `product_id` char(32) NOT NULL default '', `product_type` int(11) NOT NULL default '0', PRIMARY KEY (`order_id`,`product_id`,`product_type`) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 (order_id, product_id, product_type) VALUES ('3d7ce39b5d4b3e3d22aaafe9b633de51',1206029, 3), ('3d7ce39b5d4b3e3d22aaafe9b633de51',5880836, 3), diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index ca4143fa1c9..bb72c5278f9 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -199,7 +199,7 @@ CREATE TABLE t1 ( info text NOT NULL, ipnr varchar(30) NOT NULL default '', PRIMARY KEY (member_id) -) TYPE=MyISAM PACK_KEYS=1; +) ENGINE=MyISAM PACK_KEYS=1; insert into t1 (member_id) values (1),(2),(3); select member_id, nickname, voornaam FROM t1 @@ -261,7 +261,7 @@ CREATE TABLE t1 ( cid smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (gid), KEY component_id (cid) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES (103853,108),(103867,108),(103962,108),(104505,108),(104619,108),(104620,108); ALTER TABLE t1 add skr int(10) not null; @@ -272,13 +272,13 @@ CREATE TABLE t2 ( PRIMARY KEY (gid), KEY uid (uid), KEY status_id (sid) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t2 VALUES (103853,250,5),(103867,27,5),(103962,27,5),(104505,117,5),(104619,75,5),(104620,15,5); CREATE TABLE t3 ( uid smallint(6) NOT NULL auto_increment, PRIMARY KEY (uid) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t3 VALUES (1),(15),(27),(75),(117),(250); ALTER TABLE t3 add skr int(10) not null; diff --git a/mysql-test/t/outfile.test b/mysql-test/t/outfile.test index a944df01051..a7b81d565e6 100644 --- a/mysql-test/t/outfile.test +++ b/mysql-test/t/outfile.test @@ -22,7 +22,7 @@ #select load_file("/tmp/select-test.not-exist"); #drop table t1; #drop table if exists t; -#CREATE TABLE t ( t timestamp NOT NULL, c char(200) character set latin1 NOT NULL default '', i int(11), v varchar(200), b blob, KEY t (t)) TYPE=MyISAM; +#CREATE TABLE t ( t timestamp NOT NULL, c char(200) character set latin1 NOT NULL default '', i int(11), v varchar(200), b blob, KEY t (t)) ENGINE=MyISAM; #INSERT INTO t VALUES ('2002-12-20 12:01:20','',1,"aaa","bbb"); #select * from t into outfile "check"; #drop table if exists t; diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index 14cbf4c906d..c93d616b379 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -45,7 +45,7 @@ create table t1 (a int not null); insert into t1 values (1),(2),(3); create table t2 (a int not null); insert into t2 values (4),(5),(6); -create table t3 (a int not null) type=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; +create table t3 (a int not null) engine=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; # insert select * from t3; select * from t3; diff --git a/mysql-test/t/query_cache_merge.test b/mysql-test/t/query_cache_merge.test index 02e316932a3..fef3f18df60 100644 --- a/mysql-test/t/query_cache_merge.test +++ b/mysql-test/t/query_cache_merge.test @@ -25,7 +25,7 @@ while ($1) } --enable_warnings -create table t00 (a int) type=MERGE UNION=(t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256,t257) INSERT_METHOD=FIRST; +create table t00 (a int) engine=MERGE UNION=(t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256,t257) INSERT_METHOD=FIRST; enable_query_log; select count(*) from t00; select count(*) from t00; diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index b1b846221e7..f01d8d4eacf 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -128,7 +128,7 @@ CREATE TABLE t1 ( KEY IdxArt (art), KEY IdxKnr (KNR), KEY IdxArtnr (ARTNR) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 (art) VALUES ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), @@ -214,7 +214,7 @@ CREATE TABLE t1 ( b int(11) default NULL, KEY a (a), KEY b (b) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES diff --git a/mysql-test/t/repair_part1.test b/mysql-test/t/repair_part1.test index 2657f91cd02..e82edb64713 100644 --- a/mysql-test/t/repair_part1.test +++ b/mysql-test/t/repair_part1.test @@ -8,7 +8,7 @@ drop table if exists t1; create table t1 SELECT 1,"table 1"; repair table t1 use_frm; -alter table t1 TYPE=HEAP; +alter table t1 ENGINE=HEAP; repair table t1 use_frm; drop table t1; @@ -19,4 +19,4 @@ repair table t1 use_frm; # Create test table for repair2 # The following must be last in this file -create table t1 type=myisam SELECT 1,"table 1"; +create table t1 engine=myisam SELECT 1,"table 1"; diff --git a/mysql-test/t/replace.test b/mysql-test/t/replace.test index 2afce2fcc84..8429d80a4ef 100644 --- a/mysql-test/t/replace.test +++ b/mysql-test/t/replace.test @@ -12,14 +12,14 @@ CREATE TABLE t1 ( gesuchnr int(11) DEFAULT '0' NOT NULL, benutzer_id int(11) DEFAULT '0' NOT NULL, PRIMARY KEY (gesuchnr,benutzer_id) -) type=ISAM; +) engine=ISAM; replace into t1 (gesuchnr,benutzer_id) values (2,1); replace into t1 (gesuchnr,benutzer_id) values (1,1); replace into t1 (gesuchnr,benutzer_id) values (1,1); -alter table t1 type=myisam; +alter table t1 engine=myisam; replace into t1 (gesuchnr,benutzer_id) values (1,1); -alter table t1 type=heap; +alter table t1 engine=heap; replace into t1 (gesuchnr,benutzer_id) values (1,1); drop table t1; diff --git a/mysql-test/t/rollback.test b/mysql-test/t/rollback.test index 1673f1648d3..87f59417d90 100644 --- a/mysql-test/t/rollback.test +++ b/mysql-test/t/rollback.test @@ -6,7 +6,7 @@ drop table if exists t1; --enable_warnings -create table t1 (n int not null primary key) type=myisam; +create table t1 (n int not null primary key) engine=myisam; begin work; insert into t1 values (4); insert into t1 values (5); diff --git a/mysql-test/t/rpl000006.test b/mysql-test/t/rpl000006.test index c6438062bb7..898ef309f50 100644 --- a/mysql-test/t/rpl000006.test +++ b/mysql-test/t/rpl000006.test @@ -30,7 +30,7 @@ set SQL_LOG_BIN=0; CREATE TABLE t1 ( a int not null -) TYPE=MyISAM MAX_ROWS=4000 CHECKSUM=1; +) ENGINE=MyISAM MAX_ROWS=4000 CHECKSUM=1; INSERT INTO t1 VALUES (1); save_master_pos; connection slave; diff --git a/mysql-test/t/rpl_flush_tables.test b/mysql-test/t/rpl_flush_tables.test index 67720343e83..b98235fbed8 100644 --- a/mysql-test/t/rpl_flush_tables.test +++ b/mysql-test/t/rpl_flush_tables.test @@ -8,7 +8,7 @@ source include/master-slave.inc; create table t1 (a int); insert into t1 values (10); create table t2 (a int); -create table t3 (a int) type=merge union(t1); +create table t3 (a int) engine=merge union(t1); create table t4 (a int); # We force the slave to open t3 (because we want to try confusing him) with this : insert into t4 select * from t3; diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test index b2b92dec7aa..8244c323a0d 100644 --- a/mysql-test/t/rpl_insert_id.test +++ b/mysql-test/t/rpl_insert_id.test @@ -23,8 +23,8 @@ connection master; drop table t1; drop table t2; --disable_warnings -create table t1(a int auto_increment, key(a)) type=innodb; -create table t2(b int auto_increment, c int, key(b), foreign key(b) references t1(a)) type=innodb; +create table t1(a int auto_increment, key(a)) engine=innodb; +create table t2(b int auto_increment, c int, key(b), foreign key(b) references t1(a)) engine=innodb; --enable_warnings SET FOREIGN_KEY_CHECKS=0; insert into t1 values (10); diff --git a/mysql-test/t/rpl_relayrotate.test b/mysql-test/t/rpl_relayrotate.test index 7a572740b3a..0e198d23517 100644 --- a/mysql-test/t/rpl_relayrotate.test +++ b/mysql-test/t/rpl_relayrotate.test @@ -15,7 +15,7 @@ connection slave; stop slave; connection master; --disable_warnings -create table t1 (a int) type=innodb; +create table t1 (a int) engine=innodb; --enable_warnings let $1=8000; disable_query_log; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index b817544bfb9..446c8768146 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -1381,7 +1381,7 @@ INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1009,"gems",447,2374 INSERT INTO t3 (period,name,companynr,price,price2) VALUES (1010,"clinker",512,786542,76234234); --enable_query_log -create temporary table tmp type = myisam select * from t3; +create temporary table tmp engine = myisam select * from t3; insert into t3 select * from tmp; insert into tmp select * from t3; @@ -1498,7 +1498,7 @@ create table t4 ( companyname char(30) NOT NULL default '', PRIMARY KEY (companynr), UNIQUE KEY companyname(companyname) -) TYPE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames'; +) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames'; --disable_query_log INSERT INTO t4 (companynr, companyname) VALUES (29,'company 1'); @@ -1767,9 +1767,9 @@ DROP TABLE t1; # Test of bug with SUM(CASE...) # -CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp(14) NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) TYPE=MyISAM; +CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp(14) NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM; INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL); -CREATE TABLE t2 ( hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, sampletid smallint(5) unsigned default NULL, sampletime datetime default NULL, samplevalue bigint(20) unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) TYPE=MyISAM; +CREATE TABLE t2 ( hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, sampletid smallint(5) unsigned default NULL, sampletime datetime default NULL, samplevalue bigint(20) unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= 'NULL' AND b.sampletime < 'NULL' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid; DROP TABLE t1,t2; @@ -1836,9 +1836,9 @@ select * from t1 natural join (t1 as t2 left join t1 as t3 using (a)); select * from (t1 as t2 left join t1 as t3 using (a)) natural join t1; drop table t1; -CREATE TABLE t1 ( aa char(2), id int(11) NOT NULL auto_increment, t2_id int(11) NOT NULL default '0', PRIMARY KEY (id), KEY replace_id (t2_id)) TYPE=MyISAM; +CREATE TABLE t1 ( aa char(2), id int(11) NOT NULL auto_increment, t2_id int(11) NOT NULL default '0', PRIMARY KEY (id), KEY replace_id (t2_id)) ENGINE=MyISAM; INSERT INTO t1 VALUES ("1",8264,2506),("2",8299,2517),("3",8301,2518),("4",8302,2519),("5",8303,2520),("6",8304,2521),("7",8305,2522); -CREATE TABLE t2 ( id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) TYPE=MyISAM; +CREATE TABLE t2 ( id int(11) NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM; INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522); select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5; drop table t1,t2; diff --git a/mysql-test/t/select_found.test b/mysql-test/t/select_found.test index e8be902606c..f787cec6533 100644 --- a/mysql-test/t/select_found.test +++ b/mysql-test/t/select_found.test @@ -81,7 +81,7 @@ CREATE TABLE `t1` ( `maxnumrep` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`numeropost`), KEY `maxnumrep` (`maxnumrep`) -) TYPE=MyISAM ROW_FORMAT=FIXED; +) ENGINE=MyISAM ROW_FORMAT=FIXED; INSERT INTO t1 (titre,maxnumrep) VALUES ('test1','1'),('test2','2'),('test3','3'); SELECT SQL_CALC_FOUND_ROWS titre,numeropost,maxnumrep FROM t1 WHERE numeropost IN (1,2) ORDER BY maxnumrep DESC LIMIT 0, 1; diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index 633826186be..4ab39e3ccbc 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -73,7 +73,7 @@ insert into t1 values (1); show open tables; drop table t1; -create table t1 (a int not null, b VARCHAR(10), INDEX (b) ) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" TYPE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed; +create table t1 (a int not null, b VARCHAR(10), INDEX (b) ) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" ENGINE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed; show create table t1; alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0; show create table t1; @@ -112,7 +112,7 @@ type_blob blob, type_medium_blob mediumblob, type_long_blob longblob, index(type_short) -) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" TYPE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed CHARSET=latin1; +) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" ENGINE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed CHARSET=latin1; # Not tested above: RAID_# UNION INSERT_METHOD DATA DIRECTORY INDEX DIRECTORY show create table t1; diff --git a/mysql-test/t/sql_mode.test b/mysql-test/t/sql_mode.test index d96fd030207..95e83b4b9e2 100644 --- a/mysql-test/t/sql_mode.test +++ b/mysql-test/t/sql_mode.test @@ -8,7 +8,7 @@ CREATE TABLE `t1` ( `email` varchar(60) character set latin2 NOT NULL default '', PRIMARY KEY (a), UNIQUE KEY `email` USING BTREE (`email`) -) TYPE=HEAP CHARSET=latin1 ROW_FORMAT DYNAMIC; +) ENGINE=HEAP CHARSET=latin1 ROW_FORMAT DYNAMIC; set @@sql_mode=""; show variables like 'sql_mode'; show create table t1; diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test index 30edcc79d9e..7be494bd0e7 100644 --- a/mysql-test/t/status.test +++ b/mysql-test/t/status.test @@ -17,7 +17,7 @@ SET SQL_LOG_BIN=0; drop table if exists t1; --enable_warnings -create table t1(n int) type=myisam; +create table t1(n int) engine=myisam; insert into t1 values(1); connection con2; lock tables t1 read; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index e53679b444e..95e4f022f2d 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -152,7 +152,7 @@ CREATE TABLE `t8` ( `email` varchar(60) character set latin1 NOT NULL default '', PRIMARY KEY (`pseudo`), UNIQUE KEY `email` (`email`) -) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC; +) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC; INSERT INTO t8 (pseudo,email) VALUES ('joce','test'); INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); @@ -177,7 +177,7 @@ CREATE TABLE `t1` ( `pseudo` varchar(35) character set latin1 NOT NULL default '', PRIMARY KEY (`pseudo`,`date`,`topic`), KEY `topic` (`topic`) -) TYPE=MyISAM ROW_FORMAT=DYNAMIC; +) ENGINE=MyISAM ROW_FORMAT=DYNAMIC; INSERT INTO t1 (topic,date,pseudo) VALUES ('43506','2002-10-02','joce'),('40143','2002-08-03','joce'); EXPLAIN EXTENDED SELECT DISTINCT date FROM t1 WHERE date='2002-08-03'; @@ -196,7 +196,7 @@ CREATE TABLE `t1` ( `maxnumrep` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`numeropost`), UNIQUE KEY `maxnumrep` (`maxnumrep`) -) TYPE=MyISAM ROW_FORMAT=FIXED; +) ENGINE=MyISAM ROW_FORMAT=FIXED; INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2); @@ -206,7 +206,7 @@ CREATE TABLE `t2` ( `date` date NOT NULL default '0000-00-00', `pseudo` varchar(35) NOT NULL default '', PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`) - ) TYPE=MyISAM ROW_FORMAT=DYNAMIC; + ) ENGINE=MyISAM ROW_FORMAT=DYNAMIC; INSERT INTO t2 (mot,topic,date,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce'); select numeropost as a FROM t1 GROUP BY (SELECT 1 FROM t1 HAVING a=1); @@ -239,7 +239,7 @@ CREATE TABLE `t1` ( `maxnumrep` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`numeropost`), UNIQUE KEY `maxnumrep` (`maxnumrep`) -) TYPE=MyISAM ROW_FORMAT=FIXED; +) ENGINE=MyISAM ROW_FORMAT=FIXED; INSERT INTO t1 (numeropost,maxnumrep) VALUES (1,0),(2,1); -- error 1241 @@ -268,7 +268,7 @@ CREATE TABLE `t1` ( PRIMARY KEY (`numeropost`,`numreponse`), UNIQUE KEY `numreponse` (`numreponse`), KEY `pseudo` (`pseudo`,`numeropost`) -) TYPE=MyISAM; +) ENGINE=MyISAM; -- error 1246 SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * FROM t1) as a; -- error 1054 @@ -392,7 +392,7 @@ drop table t1, t2, t3; -- error 1096 SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *); -CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1; +CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t2 VALUES (1),(2); SELECT * FROM t2 WHERE id IN (SELECT 1); EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1); @@ -407,7 +407,7 @@ INSERT INTO t2 VALUES ((SELECT * FROM t2)); -- error 1093 INSERT INTO t2 VALUES ((SELECT id FROM t2)); SELECT * FROM t2; -CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1; +CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 values (1),(1); -- error 1241 UPDATE t2 SET id=(SELECT * FROM t1); @@ -541,7 +541,7 @@ insert into t2 values (1,2),(1,3); select * from t1 where row(a,b) in (select a,b from t2); drop table t1, t2; -CREATE TABLE `t1` (`i` int(11) NOT NULL default '0',PRIMARY KEY (`i`)) TYPE=MyISAM CHARSET=latin1; +CREATE TABLE `t1` (`i` int(11) NOT NULL default '0',PRIMARY KEY (`i`)) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES (1); -- error 1111 UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i)); @@ -563,7 +563,7 @@ CREATE TABLE `t1` ( PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`), KEY `pseudo` (`pseudo`,`date`,`topic`), KEY `topic` (`topic`) -) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC; +) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC; CREATE TABLE `t2` ( `mot` varchar(30) character set latin1 NOT NULL default '', @@ -573,14 +573,14 @@ CREATE TABLE `t2` ( PRIMARY KEY (`mot`,`pseudo`,`date`,`topic`), KEY `pseudo` (`pseudo`,`date`,`topic`), KEY `topic` (`topic`) -) TYPE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC; +) ENGINE=MyISAM CHARSET=latin1 ROW_FORMAT=DYNAMIC; CREATE TABLE `t3` ( `numeropost` mediumint(8) unsigned NOT NULL auto_increment, `maxnumrep` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`numeropost`), UNIQUE KEY `maxnumrep` (`maxnumrep`) -) TYPE=MyISAM CHARSET=latin1; +) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES ('joce','1','','joce'),('test','2','','test'); INSERT INTO t2 VALUES ('joce','1','','joce'),('test','2','','test'); @@ -634,7 +634,7 @@ CREATE TABLE t1 ( District char(20) NOT NULL default '', Population int(11) NOT NULL default '0', PRIMARY KEY (ID) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES (130,'Sydney','AUS','New South Wales',3276207); INSERT INTO t1 VALUES (131,'Melbourne','AUS','Victoria',2865329); @@ -657,7 +657,7 @@ CREATE TABLE t2 ( Capital int(11) default NULL, Code2 char(2) NOT NULL default '', PRIMARY KEY (Code) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t2 VALUES ('AUS','Australia','Oceania','Australia and New Zealand',7741220.00,1901,18886000,79.8,351182.00,392911.00,'Australia','Constitutional Monarchy, Federation','Elisabeth II',135,'AU'); INSERT INTO t2 VALUES ('AZE','Azerbaijan','Asia','Middle East',86600.00,1991,7734000,62.9,4127.00,4100.00,'Azärbaycan','Federal Republic','Heydär Äliyev',144,'AZ'); @@ -674,7 +674,7 @@ CREATE TABLE `t1` ( `pseudo` varchar(35) character set latin1 NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `pseudo` (`pseudo`), -) TYPE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC; +) ENGINE=MyISAM PACK_KEYS=1 ROW_FORMAT=DYNAMIC; INSERT INTO t1 (pseudo) VALUES ('test'); SELECT 0 IN (SELECT 1 FROM t1 a); EXPLAIN EXTENDED SELECT 0 IN (SELECT 1 FROM t1 a); @@ -686,7 +686,7 @@ drop table t1; CREATE TABLE `t1` ( `i` int(11) NOT NULL default '0', PRIMARY KEY (`i`) -) TYPE=MyISAM CHARSET=latin1; +) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES (1); -- error 1111 @@ -702,12 +702,12 @@ drop table t1; # CREATE TABLE t1 ( id int(11) default NULL -) TYPE=MyISAM CHARSET=latin1; +) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES (1),(1),(2),(2),(1),(3); CREATE TABLE t2 ( id int(11) default NULL, name varchar(15) default NULL -) TYPE=MyISAM CHARSET=latin1; +) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t2 VALUES (4,'vita'), (1,'vita'), (2,'vita'), (1,'vita'); update t1, t2 set t2.name='lenka' where t2.id in (select id from t1); @@ -739,7 +739,7 @@ CREATE TABLE t1 ( UNIQUE KEY t1_PK (ID,SUB_ID), KEY t1_FK (REF_ID,REF_SUB), KEY t1_REFID (REF_ID) -) TYPE=MyISAM CHARSET=cp1251; +) ENGINE=MyISAM CHARSET=cp1251; INSERT INTO t1 VALUES (1,0,NULL,NULL),(2,0,NULL,NULL); SELECT DISTINCT REF_ID FROM t1 WHERE ID= (SELECT DISTINCT REF_ID FROM t1 WHERE ID=2); DROP TABLE t1; @@ -777,7 +777,7 @@ CREATE TABLE `t1` ( PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`), UNIQUE KEY `pseudo` (`pseudo`), -) TYPE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC; +) ENGINE=MyISAM CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=DYNAMIC; INSERT INTO t1 (id,pseudo,email) VALUES (1,'test','test'),(2,'test1','test1'); SELECT pseudo as a, pseudo as b FROM t1 GROUP BY (SELECT a) ORDER BY (SELECT id*1); drop table if exists t1; @@ -933,13 +933,13 @@ drop table t2, t3; # correct used_tables() # -CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ; +CREATE TABLE `t1` ( `id` mediumint(9) NOT NULL auto_increment, `taskid` bigint(20) NOT NULL default '0', `dbid` int(11) NOT NULL default '0', `create_date` datetime NOT NULL default '0000-00-00 00:00:00', `last_update` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `t1` (`id`, `taskid`, `dbid`, `create_date`,`last_update`) VALUES (1, 1, 15, '2003-09-29 10:31:36', '2003-09-29 10:31:36'), (2, 1, 21, now(), now()); -CREATE TABLE `t2` (`db_id` int(11) NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` smallint(6) NOT NULL default '0',`secondary_uid` smallint(6) NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`),FULLTEXT KEY `name` (`name`)) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647; +CREATE TABLE `t2` (`db_id` int(11) NOT NULL auto_increment,`name` varchar(200) NOT NULL default '',`primary_uid` smallint(6) NOT NULL default '0',`secondary_uid` smallint(6) NOT NULL default '0',PRIMARY KEY (`db_id`),UNIQUE KEY `name_2` (`name`),FULLTEXT KEY `name` (`name`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2147483647; INSERT INTO `t2` (`db_id`, `name`, `primary_uid`, `secondary_uid`) VALUES (18, 'Not Set 1', 0, 0),(19, 'Valid', 1, 2),(20, 'Valid 2', 1, 2),(21, 'Should Not Return', 1, 2),(26, 'Not Set 2', 0, 0),(-1, 'ALL DB\'S', 0, 0); -CREATE TABLE `t3` (`taskgenid` mediumint(9) NOT NULL auto_increment,`dbid` int(11) NOT NULL default '0',`taskid` int(11) NOT NULL default '0',`mon` tinyint(4) NOT NULL default '1',`tues` tinyint(4) NOT NULL default '1',`wed` tinyint(4) NOT NULL default '1',`thur` tinyint(4) NOT NULL default '1',`fri` tinyint(4) NOT NULL default '1',`sat` tinyint(4) NOT NULL default '0',`sun` tinyint(4) NOT NULL default '0',`how_often` smallint(6) NOT NULL default '1',`userid` smallint(6) NOT NULL default '0',`active` tinyint(4) NOT NULL default '1',PRIMARY KEY (`taskgenid`)) TYPE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ; +CREATE TABLE `t3` (`taskgenid` mediumint(9) NOT NULL auto_increment,`dbid` int(11) NOT NULL default '0',`taskid` int(11) NOT NULL default '0',`mon` tinyint(4) NOT NULL default '1',`tues` tinyint(4) NOT NULL default '1',`wed` tinyint(4) NOT NULL default '1',`thur` tinyint(4) NOT NULL default '1',`fri` tinyint(4) NOT NULL default '1',`sat` tinyint(4) NOT NULL default '0',`sun` tinyint(4) NOT NULL default '0',`how_often` smallint(6) NOT NULL default '1',`userid` smallint(6) NOT NULL default '0',`active` tinyint(4) NOT NULL default '1',PRIMARY KEY (`taskgenid`)) ENGINE=MyISAM CHARSET=latin1 AUTO_INCREMENT=2 ; INSERT INTO `t3` (`taskgenid`, `dbid`, `taskid`, `mon`, `tues`,`wed`, `thur`, `fri`, `sat`, `sun`, `how_often`, `userid`, `active`) VALUES (1,-1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 1); -CREATE TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') TYPE=MyISAM CHARSET=latin1; +CREATE TABLE `t4` (`task_id` smallint(6) NOT NULL default '0',`description` varchar(200) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; INSERT INTO `t4` (`task_id`, `description`) VALUES (1, 'Daily Check List'),(2, 'Weekly Status'); select dbid, name, (date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01') from t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND t4.task_id = taskid; SELECT dbid, name FROM t3 a, t2 b, t4 WHERE dbid = - 1 AND primary_uid = '1' AND ((date_format(now() , '%Y-%m-%d') - INTERVAL how_often DAY) >= ifnull((SELECT date_format(max(create_date),'%Y-%m-%d') FROM t1 WHERE dbid = b.db_id AND taskid = a.taskgenid), '1950-01-01')) AND t4.task_id = taskid; @@ -948,9 +948,9 @@ drop table t1,t2,t3,t4; # # cardinality check # -CREATE TABLE t1 (id int(11) default NULL) TYPE=MyISAM CHARSET=latin1; +CREATE TABLE t1 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES (1),(5); -CREATE TABLE t2 (id int(11) default NULL) TYPE=MyISAM CHARSET=latin1; +CREATE TABLE t2 (id int(11) default NULL) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t2 VALUES (2),(6); -- error 1240 select * from t1 where (1,2,6) in (select * from t2); @@ -980,9 +980,9 @@ drop table t1; # # filesort in subquery (restoring join_tab) # -CREATE TABLE t1 (number char(11) NOT NULL default '') TYPE=MyISAM CHARSET=latin1; +CREATE TABLE t1 (number char(11) NOT NULL default '') ENGINE=MyISAM CHARSET=latin1; INSERT INTO t1 VALUES ('69294728265'),('18621828126'),('89356874041'),('95895001874'); -CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) TYPE=MyISAM CHARSET=latin1; +CREATE TABLE t2 (code char(5) NOT NULL default '',UNIQUE KEY code (code)) ENGINE=MyISAM CHARSET=latin1; INSERT INTO t2 VALUES ('1'),('1226'),('1245'),('1862'),('18623'),('1874'),('1967'),('6'); select c.number as phone,(select p.code from t2 p where c.number like concat(p.code, '%') order by length(p.code) desc limit 1) as code from t1 c; drop table t1, t2; diff --git a/mysql-test/t/subselect2.test b/mysql-test/t/subselect2.test index 151e285e6d4..2d42320334a 100644 --- a/mysql-test/t/subselect2.test +++ b/mysql-test/t/subselect2.test @@ -22,7 +22,7 @@ DOCID VARCHAR(32)BINARY NOT NULL , ORIGINATOR INTEGER , PRIMARY KEY ( DOCID ) -) TYPE=InnoDB +) ENGINE=InnoDB ; CREATE TABLE t2 @@ -48,7 +48,7 @@ DOCID VARCHAR(32)BINARY NOT NULL , ORIGINATOR INTEGER , PRIMARY KEY ( DOCID ) -) TYPE=InnoDB +) ENGINE=InnoDB ; CREATE INDEX DDOCTYPEID_IDX ON t2 (DOCTYPEID); CREATE INDEX DFOLDERID_IDX ON t2 (FOLDERID); @@ -68,7 +68,7 @@ FOLDERID VARCHAR(32)BINARY NOT NULL , ORIGINATOR INTEGER , PRIMARY KEY ( FOLDERID ) -) TYPE=InnoDB; +) ENGINE=InnoDB; CREATE INDEX FFOLDERID_IDX ON t3 (FOLDERID); CREATE INDEX CMFLDRPARNT_IDX ON t3 (PARENTID); @@ -84,7 +84,7 @@ DOCTYPEID VARCHAR(32)BINARY NOT NULL , ORIGINATOR INTEGER , PRIMARY KEY ( DOCTYPEID ) -) TYPE=InnoDB; +) ENGINE=InnoDB; INSERT INTO t2 VALUES("c373e9f59cf15a6c3e57444553544200", "c373e9f59cf15a6c3e57444553544200", "340d243d45f111d497b00010a4ef934d", "2f6161e879db43c1a5b82c21ddc49089", NULL, "2003-06-06 07:48:42", NULL, NULL, NULL, "2003-06-06 07:48:42", "2003-06-06 07:48:42", NULL, NULL, NULL, "03eea05112b845949f3fd03278b5fe43", "2003-06-06 07:48:42", "admin", "0", NULL); diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index 5e274ecbd5c..9eb35ffc0fd 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -23,7 +23,7 @@ FOLDERID VARCHAR(32)BINARY NOT NULL , ORIGINATOR INTEGER , PRIMARY KEY ( FOLDERID ) -) TYPE=InnoDB; +) ENGINE=InnoDB; CREATE INDEX FFOLDERID_IDX ON t1 (FOLDERID); CREATE INDEX CMFLDRPARNT_IDX ON t1 (PARENTID); INSERT INTO t1 VALUES("0c9aab05b15048c59bc35c8461507deb", "System", "System", "2003-06-05 16:30:00", "The system content repository folder.", "3", "2003-06-05 16:30:00", "System", "0", NULL, "9c9aab05b15048c59bc35c8461507deb", "1"); @@ -35,9 +35,9 @@ drop table t1; # # UNION unlocking test # -create table t1 (a int) type=innodb; -create table t2 (a int) type=innodb; -create table t3 (a int) type=innodb; +create table t1 (a int) engine=innodb; +create table t2 (a int) engine=innodb; +create table t3 (a int) engine=innodb; insert into t1 values (1),(2),(3),(4); insert into t2 values (10),(20),(30),(40); insert into t3 values (1),(2),(10),(50); @@ -48,12 +48,12 @@ drop table t1,t2,t3; CREATE TABLE t1 ( processor_id INTEGER NOT NULL, PRIMARY KEY (processor_id) -) TYPE=InnoDB; +) ENGINE=InnoDB; CREATE TABLE t3 ( yod_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, login_processor INTEGER UNSIGNED , PRIMARY KEY (yod_id) -) TYPE=InnoDB; +) ENGINE=InnoDB; CREATE TABLE t2 ( processor_id INTEGER NOT NULL, yod_id BIGINT UNSIGNED NOT NULL, @@ -62,7 +62,7 @@ CREATE TABLE t2 ( INDEX (yod_id), FOREIGN KEY (processor_id) REFERENCES t1(processor_id), FOREIGN KEY (yod_id) REFERENCES t3(yod_id) -) TYPE=InnoDB; +) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2),(3); INSERT INTO t3 VALUES (1,1),(2,2),(3,3); INSERT INTO t2 VALUES (1,1),(2,2),(3,3); diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index f618c342936..716eed7abc0 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -41,7 +41,7 @@ drop table t2; # disable_query_log; -eval create table t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam data directory="$MYSQL_TEST_DIR/var/tmp" index directory="$MYSQL_TEST_DIR/var/run"; +eval create table t9 (a int not null auto_increment, b char(16) not null, primary key (a)) engine=myisam data directory="$MYSQL_TEST_DIR/var/tmp" index directory="$MYSQL_TEST_DIR/var/run"; enable_query_log; insert into t9 select * from t1; @@ -66,23 +66,23 @@ drop table t1; disable_query_log; --error 1103,1103 -create table t1 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam data directory="tmp"; +create table t1 (a int not null auto_increment, b char(16) not null, primary key (a)) engine=myisam data directory="tmp"; # Check that we cannot link over a table from another database. create database mysqltest; --error 1,1 -create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="/this-dir-does-not-exist"; +create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) engine=myisam index directory="/this-dir-does-not-exist"; --error 1103,1103 -create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="not-hard-path"; +create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) engine=myisam index directory="not-hard-path"; --error 1,1 -eval create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam index directory="$MYSQL_TEST_DIR/var/run"; +eval create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) engine=myisam index directory="$MYSQL_TEST_DIR/var/run"; --error 1,1 -eval create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) type=myisam data directory="$MYSQL_TEST_DIR/var/tmp"; +eval create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a)) engine=myisam data directory="$MYSQL_TEST_DIR/var/tmp"; enable_query_log; # Check moving table t9 from default database to mysqltest; diff --git a/mysql-test/t/temp_table.test b/mysql-test/t/temp_table.test index 03b6fa35bfa..2181d3eb74c 100644 --- a/mysql-test/t/temp_table.test +++ b/mysql-test/t/temp_table.test @@ -16,8 +16,8 @@ select * from t2; CREATE TABLE t2 (x int not null, y int not null); alter table t2 rename t1; select * from t1; -create TEMPORARY TABLE t2 type=heap select * from t1; -create TEMPORARY TABLE IF NOT EXISTS t2 (a int) type=heap; +create TEMPORARY TABLE t2 engine=heap select * from t1; +create TEMPORARY TABLE IF NOT EXISTS t2 (a int) engine=heap; # This should give errors !$1050 CREATE TEMPORARY TABLE t1 (a int not null, b char (10) not null); @@ -78,7 +78,7 @@ drop table t1; CREATE TABLE t1 ( d datetime default NULL -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES ('2002-10-24 14:50:32'),('2002-10-24 14:50:33'),('2002-10-24 14:50:34'),('2002-10-24 14:50:34'),('2002-10-24 14:50:34'),('2002-10-24 14:50:35'),('2002-10-24 14:50:35'),('2002-10-24 14:50:35'),('2002-10-24 14:50:35'),('2002-10-24 14:50:36'),('2002-10-24 14:50:36'),('2002-10-24 14:50:36'),('2002-10-24 14:50:36'),('2002-10-24 14:50:37'),('2002-10-24 14:50:37'),('2002-10-24 14:50:37'),('2002-10-24 14:50:37'),('2002-10-24 14:50:38'),('2002-10-24 14:50:38'),('2002-10-24 14:50:38'),('2002-10-24 14:50:39'),('2002-10-24 14:50:39'),('2002-10-24 14:50:39'),('2002-10-24 14:50:39'),('2002-10-24 14:50:40'),('2002-10-24 14:50:40'),('2002-10-24 14:50:40'); diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index d2611973101..7cedbc087e4 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -57,7 +57,7 @@ CREATE TABLE `t1` ( PRIMARY KEY (`numfacture`), KEY `date` (`date`), KEY `expedition` (`expedition`) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 (expedition) VALUES ('0001-00-00 00:00:00'); SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00'; diff --git a/mysql-test/t/type_set.test b/mysql-test/t/type_set.test index 6c0f162dc86..60aa8dcfcf2 100644 --- a/mysql-test/t/type_set.test +++ b/mysql-test/t/type_set.test @@ -12,5 +12,5 @@ drop table t1; create table t1 (a set (' ','a','b ') not null default 'b '); show create table t1; drop table t1; -CREATE TABLE t1 ( user varchar(64) NOT NULL default '', path varchar(255) NOT NULL default '', privilege set('select','RESERVED30','RESERVED29','RESERVED28','RESERVED27','RESERVED26', 'RESERVED25','RESERVED24','data.delete','RESERVED22','RESERVED21', 'RESERVED20','data.insert.none','data.insert.approve', 'data.insert.delete','data.insert.move','data.insert.propose', 'data.insert.reject','RESERVED13','RESERVED12','RESERVED11','RESERVED10', 'RESERVED09','data.update','RESERVED07','RESERVED06','RESERVED05', 'RESERVED04','metadata.delete','metadata.put','RESERVED01','RESERVED00') NOT NULL default '', KEY user (user) ) TYPE=MyISAM CHARSET=utf8; +CREATE TABLE t1 ( user varchar(64) NOT NULL default '', path varchar(255) NOT NULL default '', privilege set('select','RESERVED30','RESERVED29','RESERVED28','RESERVED27','RESERVED26', 'RESERVED25','RESERVED24','data.delete','RESERVED22','RESERVED21', 'RESERVED20','data.insert.none','data.insert.approve', 'data.insert.delete','data.insert.move','data.insert.propose', 'data.insert.reject','RESERVED13','RESERVED12','RESERVED11','RESERVED10', 'RESERVED09','data.update','RESERVED07','RESERVED06','RESERVED05', 'RESERVED04','metadata.delete','metadata.put','RESERVED01','RESERVED00') NOT NULL default '', KEY user (user) ) ENGINE=MyISAM CHARSET=utf8; DROP TABLE t1; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index a5cb1ce6b6e..6138bba5a31 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -97,7 +97,7 @@ CREATE TABLE t1 ( `same` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`pseudo1`), KEY `pseudo` (`pseudo`) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 (pseudo,pseudo1,same) VALUES ('joce', 'testtt', 1),('joce', 'tsestset', 1),('dekad', 'joce', 1); SELECT pseudo FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo FROM t1 WHERE pseudo='joce'; SELECT pseudo1 FROM t1 WHERE pseudo1='joce' UNION SELECT pseudo1 FROM t1 WHERE pseudo='joce'; @@ -252,9 +252,9 @@ drop table t1,t2; # # Test for another bug with UNION and LEFT JOIN # -CREATE TABLE t1 ( id int(3) unsigned default '0') TYPE=MyISAM; +CREATE TABLE t1 ( id int(3) unsigned default '0') ENGINE=MyISAM; INSERT INTO t1 (id) VALUES("1"); -CREATE TABLE t2 ( id int(3) unsigned default '0', id_master int(5) default '0', text1 varchar(5) default NULL, text2 varchar(5) default NULL) TYPE=MyISAM; +CREATE TABLE t2 ( id int(3) unsigned default '0', id_master int(5) default '0', text1 varchar(5) default NULL, text2 varchar(5) default NULL) ENGINE=MyISAM; INSERT INTO t2 (id, id_master, text1, text2) VALUES("1", "1", "foo1", "bar1"); INSERT INTO t2 (id, id_master, text1, text2) VALUES("2", "1", diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index 485af981fb4..8e50fee56fd 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -71,7 +71,7 @@ CREATE TABLE t1 ( KEY k3 (parent), KEY k4 (assignment), KEY ticket (ticket) -) TYPE=MyISAM; +) ENGINE=MyISAM; INSERT INTO t1 VALUES (773,773,'','','',980257344,20010318180652,0,'Open',10,0,0,0,1,'','','','',''); @@ -107,7 +107,7 @@ CREATE TABLE t1 ( `nom_option` char(40) NOT NULL default '', `valid` tinyint(1) NOT NULL default '0', KEY `id_param` (`id_param`,`nom_option`) - ) TYPE=MyISAM; + ) ENGINE=MyISAM; INSERT INTO t1 (id_param,nom_option,valid) VALUES (185,'600x1200',1); diff --git a/mysql-test/t/warnings.test b/mysql-test/t/warnings.test index 050449f0f5b..a94380db221 100644 --- a/mysql-test/t/warnings.test +++ b/mysql-test/t/warnings.test @@ -80,6 +80,14 @@ select @@warning_count; # Test for handler type # drop table t1; -create table t1 (id int) type=isam; -alter table t1 type=isam; +create table t1 (id int) engine=isam; +alter table t1 engine=isam; drop table t1; + +# +# Test for deprecated TYPE= syntax +# +create table t1 (id int) type=heap; +alter table t1 type=myisam; +drop table t1; + |