diff options
Diffstat (limited to 'mysql-test/t/myisam.test')
| -rw-r--r-- | mysql-test/t/myisam.test | 343 |
1 files changed, 298 insertions, 45 deletions
diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 254b0378aa8..f8d9f15fab1 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -453,9 +453,9 @@ create table t1 (a int not null auto_increment primary key, b text not null, uni insert into t1 (b) values ('a'),('b'),('c'); select concat(b,'.') from t1; update t1 set b='b ' where a=2; ---error 1062 +--error ER_DUP_ENTRY update t1 set b='b ' where a > 1; ---error 1062 +--error ER_DUP_ENTRY insert into t1 (b) values ('b'); select * from t1; delete from t1 where b='b'; @@ -576,32 +576,6 @@ select count(*) from t1 where a is null; drop table t1; # -# Bug #8306: TRUNCATE leads to index corruption -# -create table t1 (c1 int, index(c1)); -create table t2 (c1 int, index(c1)) engine=merge union=(t1); -insert into t1 values (1); -# Close all tables. -flush tables; -# Open t2 and (implicitly) t1. -select * from t2; -# Truncate after flush works (unless another threads reopens t2 in between). -flush tables; -truncate table t1; -insert into t1 values (1); -# Close all tables. -flush tables; -# Open t2 and (implicitly) t1. -select * from t2; -# Truncate t1, wich was not recognized as open without the bugfix. -# Now, it should fail with a table-in-use error message. ---error 1105 -truncate table t1; -# The insert used to fail on the crashed table. -insert into t1 values (1); -drop table t1,t2; - -# # bug9188 - Corruption Can't open file: 'table.MYI' (errno: 145) # create table t1 (c1 int, c2 varchar(4) not null default '', @@ -1048,23 +1022,6 @@ create table t1 (v varchar(65535)); eval set storage_engine=$default; # -# Test how DROP TABLE works if the index or data file doesn't exists - -create table t1 (a int) engine=myisam; -remove_file $MYSQLTEST_VARDIR/master-data/test/t1.MYI ; -drop table if exists t1; -create table t1 (a int) engine=myisam; -remove_file $MYSQLTEST_VARDIR/master-data/test/t1.MYI ; ---error 1051,6 -drop table t1; -create table t1 (a int) engine=myisam; -remove_file $MYSQLTEST_VARDIR/master-data/test/t1.MYD ; ---error 1105,6,29 -drop table t1; ---error 1051 -drop table t1; - -# # Test concurrent insert # First with static record length # @@ -1135,6 +1092,7 @@ show keys from t1; drop table t1; + # # Bug#10056 - PACK_KEYS option take values greater than 1 while creating table # @@ -1145,6 +1103,7 @@ create table t3 (c1 int) engine=myisam pack_keys=default; create table t4 (c1 int) engine=myisam pack_keys=2; drop table t1, t2, t3; + # # Bug#28476: force index on a disabled myisam index gives error 124 # @@ -1226,3 +1185,297 @@ CHECK TABLE t1; DROP TABLE t1; --echo End of 5.0 tests + + +# +# Test of key_block_size +# + +create table t1 (a int not null, key `a` (a) key_block_size=1024); +show create table t1; +drop table t1; + +create table t1 (a int not null, key `a` (a) key_block_size=2048); +show create table t1; +drop table t1; + +create table t1 (a varchar(2048), key `a` (a)); +show create table t1; +drop table t1; + +create table t1 (a varchar(2048), key `a` (a) key_block_size=1024); +show create table t1; +drop table t1; + +create table t1 (a int not null, b varchar(2048), key (a), key(b)) key_block_size=1024; +show create table t1; +alter table t1 key_block_size=2048; +show create table t1; +alter table t1 add c int, add key (c); +show create table t1; +alter table t1 key_block_size=0; +alter table t1 add d int, add key (d); +show create table t1; +drop table t1; + +create table t1 (a int not null, b varchar(2048), key (a), key(b)) key_block_size=8192; +show create table t1; +drop table t1; + +create table t1 (a int not null, b varchar(2048), key (a) key_block_size=1024, key(b)) key_block_size=8192; +show create table t1; +drop table t1; + +create table t1 (a int not null, b int, key (a) key_block_size=1024, key(b) key_block_size=8192) key_block_size=16384; +show create table t1; +drop table t1; + + +# Test limits and errors of key_block_size + +create table t1 (a int not null, key `a` (a) key_block_size=512); +show create table t1; +drop table t1; + +create table t1 (a varchar(2048), key `a` (a) key_block_size=1000000000000000000); +show create table t1; +drop table t1; + +create table t1 (a int not null, key `a` (a) key_block_size=1025); +show create table t1; +drop table t1; + +--error 1064 +create table t1 (a int not null, key key_block_size=1024 (a)); +--error 1064 +create table t1 (a int not null, key `a` key_block_size=1024 (a)); + +# +# Bug#22119 - Changing MI_KEY_BLOCK_LENGTH makes a wrong myisamchk +# +CREATE TABLE t1 ( + c1 INT, + c2 VARCHAR(300), + KEY (c1) KEY_BLOCK_SIZE 1024, + KEY (c2) KEY_BLOCK_SIZE 8192 + ); +INSERT INTO t1 VALUES (10, REPEAT('a', CEIL(RAND(10) * 300))), + (11, REPEAT('b', CEIL(RAND() * 300))), + (12, REPEAT('c', CEIL(RAND() * 300))), + (13, REPEAT('d', CEIL(RAND() * 300))), + (14, REPEAT('e', CEIL(RAND() * 300))), + (15, REPEAT('f', CEIL(RAND() * 300))), + (16, REPEAT('g', CEIL(RAND() * 300))), + (17, REPEAT('h', CEIL(RAND() * 300))), + (18, REPEAT('i', CEIL(RAND() * 300))), + (19, REPEAT('j', CEIL(RAND() * 300))), + (20, REPEAT('k', CEIL(RAND() * 300))), + (21, REPEAT('l', CEIL(RAND() * 300))), + (22, REPEAT('m', CEIL(RAND() * 300))), + (23, REPEAT('n', CEIL(RAND() * 300))), + (24, REPEAT('o', CEIL(RAND() * 300))), + (25, REPEAT('p', CEIL(RAND() * 300))), + (26, REPEAT('q', CEIL(RAND() * 300))), + (27, REPEAT('r', CEIL(RAND() * 300))), + (28, REPEAT('s', CEIL(RAND() * 300))), + (29, REPEAT('t', CEIL(RAND() * 300))), + (30, REPEAT('u', CEIL(RAND() * 300))), + (31, REPEAT('v', CEIL(RAND() * 300))), + (32, REPEAT('w', CEIL(RAND() * 300))), + (33, REPEAT('x', CEIL(RAND() * 300))), + (34, REPEAT('y', CEIL(RAND() * 300))), + (35, REPEAT('z', CEIL(RAND() * 300))); +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +CHECK TABLE t1; +REPAIR TABLE t1; +DELETE FROM t1 WHERE c1 >= 10; +CHECK TABLE t1; +DROP TABLE t1; + +# +# Bug#33222 - myisam-table drops rows when column is added +# and a char-field > 128 exists +# +# Test #1 - CHECK TABLE sees wrong record, REPAR TABLE deletes it. +# Using a CHAR column that can have > 127 characters. +# Using a VARCHAR to create a table with dynamic row format. +CREATE TABLE t1 ( + c1 CHAR(130), + c2 VARCHAR(1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); +SELECT COUNT(*) FROM t1; +CHECK TABLE t1; +REPAIR TABLE t1; +SELECT COUNT(*) FROM t1; +CHECK TABLE t1; +DROP TABLE t1; +# +# Test #2 - same as test #1, but using EXTENDED. +# Using a CHAR column that can have > 127 characters. +# Using a VARCHAR to create a table with dynamic row format. +CREATE TABLE t1 ( + c1 CHAR(130), + c2 VARCHAR(1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); +SELECT COUNT(*) FROM t1; +CHECK TABLE t1 EXTENDED; +REPAIR TABLE t1 EXTENDED; +SELECT COUNT(*) FROM t1; +CHECK TABLE t1 EXTENDED; +DROP TABLE t1; +# +# Test #3 - same as test #1, but using OPTIMIZE TABLE. +# Using a CHAR column that can have > 127 characters. +# Using a VARCHAR to create a table with dynamic row format. +CREATE TABLE t1 ( + c1 CHAR(130), + c2 VARCHAR(1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); +# Insert more rows and delete one in the middle to force optimize. +INSERT INTO t1 VALUES('b', 'b'); +INSERT INTO t1 VALUES('c', 'b'); +DELETE FROM t1 WHERE c1='b'; +SELECT COUNT(*) FROM t1; +OPTIMIZE TABLE t1; +SELECT COUNT(*) FROM t1; +DROP TABLE t1; +# +# Test #4 - ALTER TABLE deletes rows. +# Using a CHAR column that can have > 127 characters. +# Using a VARCHAR to create a table with dynamic row format. +# Using an index which can be disabled during bulk insert. +CREATE TABLE t1 ( + c1 CHAR(130), + c2 VARCHAR(1), + KEY (c1) +) ENGINE=MyISAM; +# +# Insert 100 rows. This turns bulk insert on during the copy phase of +# ALTER TABLE. Bulk insert disables keys before the insert and re-enables +# them by repair after the insert. +--disable_query_log +let $count= 100; +--echo # Insert $count rows. Query log disabled. +while ($count) +{ + INSERT INTO t1 VALUES ('a', 'b'); + dec $count; +} +--enable_query_log +# +# Change most of the rows into long character values with > 127 characters. +UPDATE t1 SET c1=REPEAT("a",128) LIMIT 90; +SELECT COUNT(*) FROM t1; +ALTER TABLE t1 ENGINE=MyISAM; +# +# With bug present, this shows that all long rows are gone. +SELECT COUNT(*) FROM t1; +CHECK TABLE t1; +CHECK TABLE t1 EXTENDED; +DROP TABLE t1; +# +# Test #5 - same as test #1 but UTF-8. +# Using a CHAR column that can have > 127 characters. +# Using a VARCHAR to create a table with dynamic row format. +CREATE TABLE t1 ( + c1 CHAR(50), + c2 VARCHAR(1) +) ENGINE=MyISAM DEFAULT CHARSET UTF8; +# Using Tamil Letter A, Unicode U+0B85 +INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); +SELECT COUNT(*) FROM t1; +CHECK TABLE t1; +REPAIR TABLE t1; +SELECT COUNT(*) FROM t1; +CHECK TABLE t1; +DROP TABLE t1; +# +# Test #6 - same as test #2, but UTF-8. +# Using a CHAR column that can have > 127 characters. +# Using a VARCHAR to create a table with dynamic row format. +CREATE TABLE t1 ( + c1 CHAR(50), + c2 VARCHAR(1) +) ENGINE=MyISAM DEFAULT CHARSET UTF8; +# Using Tamil Letter A, Unicode U+0B85 +INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); +SELECT COUNT(*) FROM t1; +CHECK TABLE t1 EXTENDED; +REPAIR TABLE t1 EXTENDED; +SELECT COUNT(*) FROM t1; +CHECK TABLE t1 EXTENDED; +DROP TABLE t1; +# +# Test #7 - same as test #3, but UTF-8. +# Using a CHAR column that can have > 127 characters. +# Using a VARCHAR to create a table with dynamic row format. +CREATE TABLE t1 ( + c1 CHAR(50), + c2 VARCHAR(1) +) ENGINE=MyISAM DEFAULT CHARSET UTF8; +# Using Tamil Letter A, Unicode U+0B85 +INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); +# Insert more rows and delete one in the middle to force optimize. +INSERT INTO t1 VALUES('b', 'b'); +INSERT INTO t1 VALUES('c', 'b'); +DELETE FROM t1 WHERE c1='b'; +SELECT COUNT(*) FROM t1; +OPTIMIZE TABLE t1; +SELECT COUNT(*) FROM t1; +DROP TABLE t1; +# +# Test #8 - same as test #4, but UTF-8. +# Using a CHAR column that can have > 42 UTF-8 characters. +# Using a VARCHAR to create a table with dynamic row format. +# Using an index which can be disabled during bulk insert. +CREATE TABLE t1 ( + c1 CHAR(50), + c2 VARCHAR(1), + KEY (c1) +) ENGINE=MyISAM DEFAULT CHARSET UTF8; +# +# Insert 100 rows. This turns bulk insert on during the copy phase of +# ALTER TABLE. Bulk insert disables keys before the insert and re-enables +# them by repair after the insert. +--disable_query_log +let $count= 100; +--echo # Insert $count rows. Query log disabled. +while ($count) +{ + INSERT INTO t1 VALUES ('a', 'b'); + dec $count; +} +--enable_query_log +# +# Change most of the rows into long character values with > 42 characters. +# Using Tamil Letter A, Unicode U+0B85 +UPDATE t1 SET c1=REPEAT(_utf8 x'e0ae85',43) LIMIT 90; +SELECT COUNT(*) FROM t1; +ALTER TABLE t1 ENGINE=MyISAM; +# +# With bug present, this shows that all long rows are gone. +SELECT COUNT(*) FROM t1; +CHECK TABLE t1; +CHECK TABLE t1 EXTENDED; +DROP TABLE t1; + +# +# Bug#29182 - MyISAMCHK reports wrong character set +# +CREATE TABLE t1 ( + c1 VARCHAR(10) NOT NULL, + c2 CHAR(10) DEFAULT NULL, + c3 VARCHAR(10) NOT NULL, + KEY (c1), + KEY (c2) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--exec $MYISAMCHK -d $MYSQLTEST_VARDIR/master-data/test/t1 +DROP TABLE t1; + +--echo End of 5.1 tests + |