1-- disable_warnings 2DROP TABLE IF EXISTS t1,t2,t3; 3-- enable_warnings 4# 5# BUG#31277 - myisamchk --unpack corrupts a table 6# 7CREATE TABLE t1(c1 DOUBLE, c2 DOUBLE, c3 DOUBLE, c4 DOUBLE, c5 DOUBLE, 8 c6 DOUBLE, c7 DOUBLE, c8 DOUBLE, c9 DOUBLE, a INT PRIMARY KEY); 9INSERT INTO t1 VALUES 10(-3.31168791059336e-06,-3.19054655887874e-06,-1.06528081684847e-05,-1.227278240089e-06,-1.66718069164799e-06,-2.59038972510885e-06,-2.83145227805303e-06,-4.09678491270648e-07,-2.22610091291797e-06,6), 11(0.0030743000272545,2.53222044316438e-05,2.78674650061845e-05,1.95914465544536e-05,1.7347572525984e-05,1.87513810069614e-05,1.69882826885005e-05,2.44449336987598e-05,1.89914629921774e-05,9), 12(2.85229319423495e-05,3.05970988282259e-05,3.77161100113133e-05,2.3055238978766e-05,2.08241267364615e-05,2.28009504270553e-05,2.12070165658947e-05,2.84350091565409e-05,2.3366822910704e-05,3), 13(0,0,0,0,0,0,0,0,0,12), 14(3.24544577570754e-05,3.44619021870993e-05,4.37561613201124e-05,2.57556808726748e-05,2.3195354640561e-05,2.58532400758869e-05,2.34934241667179e-05,3.1621640063232e-05,2.58229982746189e-05,19), 15(2.53222044316438e-05,0.00445071933455582,2.97447268116016e-05,2.12379514059868e-05,1.86777776502663e-05,2.0170058676712e-05,1.8946030385445e-05,2.66040037173511e-05,2.09161899668946e-05,20), 16(3.03462382611645e-05,3.26517930083994e-05,3.5242025468662e-05,2.53219745106391e-05,2.24384532945004e-05,2.4052346047657e-05,2.23865572957053e-05,3.1634313969082e-05,2.48285463481801e-05,21), 17(1.95914465544536e-05,2.12379514059868e-05,2.27808649037128e-05,0.000341724375366877,1.4512761275113e-05,1.56475828693953e-05,1.44372366441415e-05,2.07952121981765e-05,1.61488256935919e-05,28), 18(1.7347572525984e-05,1.86777776502663e-05,2.04116907052727e-05,1.4512761275113e-05,0.000432162526082388,1.38116514014465e-05,1.2712914948904e-05,1.82503165178506e-05,1.43043075345922e-05,30), 19(1.68339762136661e-05,1.77836497166611e-05,2.36328309295222e-05,1.30183423732016e-05,1.18674654241553e-05,1.32467273128652e-05,1.24581739117775e-05,1.55624190959406e-05,1.33010638508213e-05,31), 20(1.89643062824415e-05,2.06997140070717e-05,2.29045490159364e-05,1.57918175731019e-05,1.39864987449492e-05,1.50580274578455e-05,1.45908734129609e-05,1.95329296993327e-05,1.5814709481221e-05,32), 21(1.69882826885005e-05,1.8946030385445e-05,2.00820439721439e-05,1.44372366441415e-05,1.2712914948904e-05,1.35209686474184e-05,0.00261563314789896,1.78285095864627e-05,1.46699314500019e-05,34), 22(2.0278186540684e-05,2.18923409729654e-05,2.39981539939738e-05,1.71774589459438e-05,1.54654355357383e-05,1.62731485707636e-05,1.49253140625051e-05,2.18229800160297e-05,1.71923561673718e-05,35), 23(2.44449336987598e-05,2.66040037173511e-05,2.84860148925308e-05,2.07952121981765e-05,1.82503165178506e-05,1.97667730441441e-05,1.78285095864627e-05,0.00166478601822712,2.0299952103232e-05,36), 24(1.89914629921774e-05,2.09161899668946e-05,2.26026841007872e-05,1.61488256935919e-05,1.43043075345922e-05,1.52609063290127e-05,1.46699314500019e-05,2.0299952103232e-05,0.00306670170971682,39), 25(0,0,0,0,0,0,0,0,0,41), 26(0,0,0,0,0,0,0,0,0,17), 27(0,0,0,0,0,0,0,0,0,18), 28(2.51880677333017e-05,2.63051795435778e-05,2.79874748974906e-05,2.02888886670845e-05,1.8178636318197e-05,1.91308527003585e-05,1.83260023644133e-05,2.4422300558171e-05,1.96411467520551e-05,44), 29(2.22402118719591e-05,2.37546284320705e-05,2.58463051055541e-05,1.83391609130854e-05,1.6300720519646e-05,1.74559091886791e-05,1.63733785575587e-05,2.26616253279828e-05,1.79541237435621e-05,45), 30(3.01092775359837e-05,3.23865212934412e-05,4.09444584045994e-05,0,2.15470966302776e-05,2.39082636344032e-05,2.28296706429177e-05,2.9007671511595e-05,2.44201138973326e-05,46); 31FLUSH TABLES; 32let $MYSQLD_DATADIR= `select @@datadir`; 33--exec $MYISAMPACK -s $MYSQLD_DATADIR/test/t1 34--exec $MYISAMCHK -srq $MYSQLD_DATADIR/test/t1 35--exec $MYISAMCHK -s --unpack $MYSQLD_DATADIR/test/t1 36CHECK TABLE t1 EXTENDED; 37DROP TABLE t1; 38 39# 40# Bug#40949 Debug version of MySQL server crashes when run OPTIMIZE on compressed table. 41# expanded with testcase for 42# BUG#41574 - REPAIR TABLE: crashes for compressed tables 43# 44--disable_warnings 45drop table if exists t1; 46--enable_warnings 47create table t1(f1 int, f2 char(255)); 48insert into t1 values(1, 'foo'), (2, 'bar'); 49insert into t1 select * from t1; 50insert into t1 select * from t1; 51insert into t1 select * from t1; 52insert into t1 select * from t1; 53insert into t1 select * from t1; 54insert into t1 select * from t1; 55insert into t1 select * from t1; 56insert into t1 select * from t1; 57insert into t1 select * from t1; 58insert into t1 select * from t1; 59insert into t1 select * from t1; 60insert into t1 select * from t1; 61flush tables; 62let $MYSQLD_DATADIR= `select @@datadir`; 63--exec $MYISAMPACK $MYSQLD_DATADIR/test/t1 64optimize table t1; 65repair table t1; 66drop table t1; 67 68--echo # 69--echo # BUG#41541 - Valgrind warnings on packed MyISAM table 70--echo # 71CREATE TABLE t1(f1 VARCHAR(200), f2 TEXT); 72INSERT INTO t1 VALUES ('foo', 'foo1'), ('bar', 'bar1'); 73let $i=9; 74--disable_query_log 75begin; 76while ($i) 77{ 78 INSERT INTO t1 SELECT * FROM t1; 79 dec $i; 80} 81commit; 82--enable_query_log 83FLUSH TABLE t1; 84--echo # Compress the table using MYISAMPACK tool 85let $MYSQLD_DATADIR= `select @@datadir`; 86--exec $MYISAMPACK $MYSQLD_DATADIR/test/t1 87SELECT COUNT(*) FROM t1; 88DROP TABLE t1; 89 90--echo # 91--echo # Bug #43973 - backup_myisam.test fails on 6.0-bugteam 92--echo # 93CREATE DATABASE mysql_db1; 94CREATE TABLE mysql_db1.t1 (c1 VARCHAR(5), c2 int); 95CREATE INDEX i1 ON mysql_db1.t1 (c1, c2); 96INSERT INTO mysql_db1.t1 VALUES ('A',1); 97INSERT INTO mysql_db1.t1 SELECT * FROM mysql_db1.t1; 98INSERT INTO mysql_db1.t1 SELECT * FROM mysql_db1.t1; 99INSERT INTO mysql_db1.t1 SELECT * FROM mysql_db1.t1; 100INSERT INTO mysql_db1.t1 SELECT * FROM mysql_db1.t1; 101INSERT INTO mysql_db1.t1 SELECT * FROM mysql_db1.t1; 102INSERT INTO mysql_db1.t1 SELECT * FROM mysql_db1.t1; 103INSERT INTO mysql_db1.t1 SELECT * FROM mysql_db1.t1; 104FLUSH TABLE mysql_db1.t1; 105# 106--echo # Compress the table using MYISAMPACK tool 107let $MYSQLD_DATADIR= `select @@datadir`; 108--exec $MYISAMPACK -s $MYSQLD_DATADIR/mysql_db1/t1 109--echo # Run MYISAMCHK tool on the compressed table 110--exec $MYISAMCHK -srq $MYSQLD_DATADIR/mysql_db1/t1 111SELECT COUNT(*) FROM mysql_db1.t1 WHERE c2 < 5; 112# 113# Bug#36573 myisampack --join does not create destination table .frm file 114# 115############################################################################# 116# Testcase myisampack.1: Positive test for myisampack --join 117# To test myisampack --join operation creates .frm file 118# If it creates .frm file, we will be able to access from mysql 119# server 120############################################################################# 121--echo # ===== myisampack.1 ===== 122CREATE TABLE t1(a INT); 123INSERT INTO t1 VALUES(20); 124 125let $i=9; 126--disable_query_log 127while ($i) 128{ 129 INSERT INTO t1 SELECT a from t1; 130 dec $i; 131} 132--enable_query_log 133 134CREATE TABLE t2(a INT); 135INSERT INTO t2 VALUES(40); 136 137let $i=9; 138--disable_query_log 139while ($i) 140{ 141 INSERT INTO t2 SELECT a from t2; 142 dec $i; 143} 144--enable_query_log 145 146FLUSH TABLE t1,t2; 147--exec $MYISAMPACK --join=$MYSQLD_DATADIR/test/t3 $MYSQLD_DATADIR/test/t1 $MYSQLD_DATADIR/test/t2 2>&1 148 149--echo #If the myisampack --join operation is successful, we have table t3(.frm) 150--echo #so we should be able to query about the table from server. 151SELECT COUNT(a) FROM t3; 152 153############################################################################# 154# Testcase myisampack.2: 2nd Positive test for myisampack --join 155# Test myisampack join operation with an existing destination frm file. 156# It should finish the join operation successfully 157############################################################################# 158--echo # ===== myisampack.2 ===== 159FLUSH TABLE t3; 160--remove_file $MYSQLD_DATADIR/test/t3.MYI 161--remove_file $MYSQLD_DATADIR/test/t3.MYD 162--exec $MYISAMPACK --join=$MYSQLD_DATADIR/test/t3 $MYSQLD_DATADIR/test/t1 $MYSQLD_DATADIR/test/t2 2>&1 163--echo #Tests the myisampack join operation with an existing destination .frm file, 164--echo #the command should return correct exit status(0) and 165--echo #we should be able to query the table. 166 167SELECT COUNT(a) FROM t3; 168 169############################################################################# 170# Testcase myisampack.3: 3rd Positive test for myisampack --join 171# Test myisampack join operation without frm file for first table and second 172# table. It should finish the join operation successfully 173############################################################################# 174--echo # ===== myisampack.3 ===== 175--copy_file $MYSQLD_DATADIR/test/t1.frm $MYSQLTEST_VARDIR/tmp/bug36573.t1.frm 176--copy_file $MYSQLD_DATADIR/test/t2.frm $MYSQLTEST_VARDIR/tmp/bug36573.t2.frm 177--remove_file $MYSQLD_DATADIR/test/t1.frm 178--remove_file $MYSQLD_DATADIR/test/t2.frm 179 180DROP TABLE t3; 181--exec $MYISAMPACK --join=$MYSQLD_DATADIR/test/t3 $MYSQLD_DATADIR/test/t1 $MYSQLD_DATADIR/test/t2 2>&1 182--echo #Tests the myisampack join operation without frm file for the first and second table 183--echo #No frm file is generated in this and we shouldn't be able to access the newly 184--echo #created table 185 186--error ER_NO_SUCH_TABLE 187SELECT COUNT(a) FROM t3; 188 189--copy_file $MYSQLTEST_VARDIR/tmp/bug36573.t1.frm $MYSQLD_DATADIR/test/t1.frm 190--copy_file $MYSQLTEST_VARDIR/tmp/bug36573.t2.frm $MYSQLD_DATADIR/test/t2.frm 191--copy_file $MYSQLTEST_VARDIR/tmp/bug36573.t1.frm $MYSQLD_DATADIR/test/t3.frm 192--remove_file $MYSQLTEST_VARDIR/tmp/bug36573.t1.frm 193--remove_file $MYSQLTEST_VARDIR/tmp/bug36573.t2.frm 194 195############################################################################# 196# Testcase myisampack.4: Negative test for myisampack --join 197# Test myisampack join operation with an existing .MYI,.MDI,.frm files 198# the test should fail 199############################################################################# 200--echo # ===== myisampack.4 ===== 201--echo #Tests the myisampack join operation with an existing destination .frm,.MYI,.MDI 202--echo #the command should fail with exit status 2 203# 204# Note: Use of regular expressions in this file is for output printed in result file 205# The main purpose of this regular expression is to supress the filenames for 206# error messages produced so that we can create a generic result file 207# 208#1. /.*myisampack(\.exe)?: Can't create\/write to file .*\(/myisampack: Can't create\/write to file (/ 209# Replace everything before "myisampack" or "myisampack.exe" and followed by 210# ": Can't create\/write to file " until the first open paranthesis , with 211# "myisampack: Can't create\/write to file (" 212# 213#2. /Aborted: .*is/Aborted: file is/ 214# Replace everything after starting with "Aborted: " until ending with "is" with 215# "Aborted: file is/ 216# 217--replace_regex /.*myisampack(\.exe)?: Can't create\/write to file .*\(/myisampack: Can't create\/write to file (/ /Aborted: .*is/Aborted: file is/ 218--error 2 219--exec $MYISAMPACK --join=$MYSQLD_DATADIR/test/t3 $MYSQLD_DATADIR/test/t1 $MYSQLD_DATADIR/test/t2 2>&1 220 221DROP TABLE t1,t2,t3; 222 223DROP TABLE mysql_db1.t1; 224DROP DATABASE mysql_db1; 225 226--echo # 227--echo # BUG#11761180 - 53646: MYISAMPACK CORRUPTS TABLES WITH FULLTEXT INDEXES 228--echo # 229CREATE TABLE t1(a CHAR(4), FULLTEXT(a)); 230INSERT INTO t1 VALUES('aaaa'),('bbbb'),('cccc'); 231FLUSH TABLE t1; 232--exec $MYISAMPACK -sf $MYSQLD_DATADIR/test/t1 233--exec $MYISAMCHK -srq $MYSQLD_DATADIR/test/t1 234CHECK TABLE t1; 235SELECT * FROM t1 WHERE MATCH(a) AGAINST('aaaa' IN BOOLEAN MODE); 236SELECT * FROM t1 WHERE MATCH(a) AGAINST('aaaa'); 237DROP TABLE t1; 238 239--echo # Test table with key_reflength > rec_reflength 240CREATE TABLE t1(a CHAR(30), FULLTEXT(a)); 241--disable_query_log 242--echo # Populating a table, so it's index file exceeds 65K 243let $1=1700; 244while ($1) 245{ 246 eval INSERT INTO t1 VALUES('$1aaaaaaaaaaaaaaaaaaaaaaaaaa'); 247 dec $1; 248} 249 250--echo # Populating a table, so index file has second level fulltext tree 251let $1=60; 252while ($1) 253{ 254 eval INSERT INTO t1 VALUES('aaaa'),('aaaa'),('aaaa'),('aaaa'),('aaaa'); 255 dec $1; 256} 257--enable_query_log 258 259FLUSH TABLE t1; 260--echo # Compressing table 261--exec $MYISAMPACK -sf $MYSQLD_DATADIR/test/t1 262--echo # Fixing index (repair by sort) 263--exec $MYISAMCHK -srnq $MYSQLD_DATADIR/test/t1 264CHECK TABLE t1; 265FLUSH TABLE t1; 266--echo # Fixing index (repair with keycache) 267--exec $MYISAMCHK -soq $MYSQLD_DATADIR/test/t1 268CHECK TABLE t1; 269DROP TABLE t1; 270 271# 272# MDEV-6245 Certain compressed tables with myisampack are corrupted by 273# "CHECK TABLE" 274# 275# Issue was that checksum failed for tables with NULL and VARCHAR fields 276# 277 278create table `t1` (`id` varchar(15) DEFAULT NULL) ENGINE=MyISAM ROW_FORMAT=FIXED; 279insert into t1 values ('aaa'),('bbb'),('ccc'),('ddd'),('eee'); 280insert into t1 (select * from t1); 281insert into t1 (select * from t1); 282insert into t1 (select * from t1); 283insert into t1 (select * from t1); 284checksum table t1; 285insert into t1 values(NULL); 286checksum table t1; 287flush table t1; 288--exec $MYISAMPACK -sf $MYSQLD_DATADIR/test/t1 289--exec $MYISAMCHK -srnq $MYSQLD_DATADIR/test/t1 290check table t1; 291checksum table t1; 292alter table t1 checksum=1 row_format=fixed; 293checksum table t1; 294flush table t1; 295--exec $MYISAMPACK -sf $MYSQLD_DATADIR/test/t1 296--exec $MYISAMCHK -srnq $MYSQLD_DATADIR/test/t1 297check table t1; 298checksum table t1; 299 300# Testing with row_format=dynamic 301 302alter table t1 row_format=dynamic checksum=0; 303checksum table t1; 304flush table t1; 305--exec $MYISAMPACK -sf $MYSQLD_DATADIR/test/t1 306--exec $MYISAMCHK -srnq $MYSQLD_DATADIR/test/t1 307check table t1; 308checksum table t1; 309alter table t1 checksum=1 row_format=dynamic; 310checksum table t1; 311flush table t1; 312--exec $MYISAMPACK -sf $MYSQLD_DATADIR/test/t1 313--exec $MYISAMCHK -srnq $MYSQLD_DATADIR/test/t1 314check table t1; 315checksum table t1; 316drop table t1; 317 318--echo # 319--echo # BUG#11751736: DROP DATABASE STATEMENT SHOULD REMOVE .OLD SUFFIX FROM 320--echo # DATABASE DIRECTORY 321--echo # 322CREATE DATABASE db1; 323CREATE TABLE db1.t1(c1 INT) ENGINE=MyISAM; 324## Added -f to force pack db in any case regardless the size of database 325## being packed 326let $MYSQLD_DATADIR = `SELECT @@datadir`; 327--exec $MYISAMPACK -b -f $MYSQLD_DATADIR/db1/t1 328DROP DATABASE db1; 329