diff options
Diffstat (limited to 'mysql-test/main/mysqlcheck.test')
| -rw-r--r-- | mysql-test/main/mysqlcheck.test | 423 |
1 files changed, 423 insertions, 0 deletions
diff --git a/mysql-test/main/mysqlcheck.test b/mysql-test/main/mysqlcheck.test new file mode 100644 index 00000000..ce4ac7d3 --- /dev/null +++ b/mysql-test/main/mysqlcheck.test @@ -0,0 +1,423 @@ +call mtr.add_suppression("Invalid .old.. table or database name"); + +# Embedded server doesn't support external clients +--source include/not_embedded.inc +--source include/have_innodb.inc +--source include/default_charset.inc + +#remove this include after fix MDEV-27873 +--source include/no_view_protocol.inc + +set @save_character_set_client=@@character_set_client; +set @save_collation_connection=@@collation_connection; + +# check that CSV engine was compiled in, as the result of the test +# depends on the presence of the log tables (which are CSV-based). +--source include/have_csv.inc +let $MYSQLD_DATADIR= `select @@datadir`; + +# +# Clean up after previous tests +# + +--disable_warnings +DROP TABLE IF EXISTS t1, `t``1`, `t 1`, test.`t.1`, v1; +drop view if exists t1, `t``1`, `t 1`, test.`t.1`, v1; +drop database if exists client_test_db; +# Repair any tables in mysql, sometimes the slow_log is marked as crashed +# after server has been killed +--exec $MYSQL_CHECK --repair --databases mysql > /dev/null 2>&1 +--enable_warnings + +# +# Bug #13783 mysqlcheck tries to optimize and analyze information_schema +# +--replace_result 'Table is already up to date' OK +--exec $MYSQL_CHECK --all-databases --analyze +--exec $MYSQL_CHECK --all-databases --optimize +--replace_result 'Table is already up to date' OK +--exec $MYSQL_CHECK --analyze --databases test information_schema mysql +--exec $MYSQL_CHECK --optimize --databases test information_schema mysql +--exec $MYSQL_CHECK --analyze information_schema schemata +--exec $MYSQL_CHECK --optimize information_schema schemata + +# +# Bug #16502: mysqlcheck tries to check views +# +create table t1 (a int) engine=myisam; +create view v1 as select * from t1; +--replace_result 'Table is already up to date' OK +--exec $MYSQL_CHECK --analyze --databases test +--exec $MYSQL_CHECK --optimize --databases test +--replace_result 'Table is already up to date' OK +--exec $MYSQL_CHECK --all-in-1 --analyze --databases test +--exec $MYSQL_CHECK --all-in-1 --optimize --databases test +drop view v1; +drop table t1; + +# +# Bug #30654: mysqlcheck fails during upgrade of tables whose names include backticks +# +create table `t``1`(a int) engine=myisam; +create table `t 1`(a int) engine=myisam; +--replace_result 'Table is already up to date' OK +--exec $MYSQL_CHECK --databases test +drop table `t``1`, `t 1`; + +# +# Bug#25347: mysqlcheck -A -r doesn't repair table marked as crashed +# +create database d_bug25347; +use d_bug25347; +create table t_bug25347 (a int) engine=myisam; +create view v_bug25347 as select * from t_bug25347; +insert into t_bug25347 values (1),(2),(3); +flush tables; +--echo removing and creating +--remove_file $MYSQLD_DATADIR/d_bug25347/t_bug25347.MYI +--write_file $MYSQLD_DATADIR/d_bug25347/t_bug25347.MYI +EOF +--exec $MYSQL_CHECK --repair --databases d_bug25347 +--error 130 +insert into t_bug25347 values (4),(5),(6); +--exec $MYSQL_CHECK --repair --use-frm --databases d_bug25347 +insert into t_bug25347 values (7),(8),(9); +select * from t_bug25347; +select * from v_bug25347; +drop view v_bug25347; +drop table t_bug25347; +drop database d_bug25347; +use test; + +# +# Bug#39541 CHECK TABLE on information_schema myisam tables produces error +# +create view v1 as select * from information_schema.routines; +check table v1, information_schema.routines; +drop view v1; + +# +# Bug#37527: mysqlcheck fails to report entire database +# when frm file corruption +# +call mtr.add_suppression("Error reading file './test/t1.frm'"); +CREATE TABLE t1(a INT) engine=myisam; +CREATE TABLE t2(a INT) engine=myisam; +# backup then null t1.frm +--copy_file $MYSQLD_DATADIR/test/t1.frm $MYSQLD_DATADIR/test/t1.frm.bak +--remove_file $MYSQLD_DATADIR/test/t1.frm +--write_file $MYSQLD_DATADIR/test/t1.frm +EOF +--exec $MYSQL_CHECK test +# restore t1.frm +--remove_file $MYSQLD_DATADIR/test/t1.frm +--copy_file $MYSQLD_DATADIR/test/t1.frm.bak $MYSQLD_DATADIR/test/t1.frm +--remove_file $MYSQLD_DATADIR/test/t1.frm.bak +DROP TABLE t1, t2; + + +--echo End of 5.0 tests + + +# +# Bug #30679: 5.1 name encoding not performed for views during upgrade +# +create table t1(a int) engine=myisam; +create view v1 as select * from t1; +show tables; +--copy_file $MYSQLD_DATADIR/test/v1.frm $MYSQLD_DATADIR/test/v-1.frm +show tables; +--exec $MYSQL_CHECK --check-upgrade --databases test +--exec $MYSQL_CHECK --fix-table-names --databases test +show tables; +drop view v1, `v-1`; +drop table t1; + + +# +# Bug #33094: Error in upgrading from 5.0 to 5.1 when table contains triggers +# Bug #41385: Crash when attempting to repair a #mysql50# upgraded table with +# triggers +# +SET NAMES utf8; +CREATE TABLE `#mysql50#@` (a INT) engine=myisam; +SHOW TABLES; +# Safer version of SET NAMES DEFAULT: +set @@character_set_client=@save_character_set_client; +set @@character_set_results=@save_character_set_client; +set @@collation_connection=@save_collation_connection; +--echo mysqlcheck --fix-table-names --databases test +--exec $MYSQL_CHECK --fix-table-names --databases test +SET NAMES utf8; +SHOW TABLES; +DROP TABLE `@`; + +CREATE TABLE `я` (a INT) engine=myisam; +set @@character_set_client=@save_character_set_client; +set @@character_set_results=@save_character_set_client; +set @@collation_connection=@save_collation_connection; + +call mtr.add_suppression("@003f.frm' \\(errno: 22\\)"); +--echo mysqlcheck --default-character-set="latin1" --databases test +# Error returned depends on platform, replace it with "Table doesn't exist" +call mtr.add_suppression("Can't find file: '..test.@003f.frm'"); +--replace_result "Can't find file: './test/@003f.frm' (errno: 22)" "Table doesn't exist" "Table 'test.?' doesn't exist" "Table doesn't exist" +--exec $MYSQL_CHECK --default-character-set="latin1" --databases test +--echo mysqlcheck --default-character-set="utf8" --databases test +--exec $MYSQL_CHECK --default-character-set="utf8" --databases test +SET NAMES utf8; +DROP TABLE `я`; +set @@character_set_client=@save_character_set_client; +set @@character_set_results=@save_character_set_client; +set @@collation_connection=@save_collation_connection; + +CREATE DATABASE `#mysql50#a@b`; +USE `#mysql50#a@b`; +CREATE TABLE `#mysql50#c@d` (a INT) engine=myisam; +CREATE TABLE t1 (a INT) engine=myisam; + +# Create 5.0 like triggers +--write_file $MYSQLD_DATADIR/a@b/c@d.TRG +TYPE=TRIGGERS +triggers='CREATE DEFINER=`root`@`localhost` TRIGGER tr1 BEFORE INSERT ON `c@d` FOR EACH ROW SET NEW.a = 10 * NEW.a' +sql_modes=0 +definers='root@localhost' +EOF +--write_file $MYSQLD_DATADIR/a@b/tr1.TRN +TYPE=TRIGGERNAME +trigger_table=c@d +EOF +--write_file $MYSQLD_DATADIR/a@b/t1.TRG +TYPE=TRIGGERS +triggers='CREATE DEFINER=`root`@`localhost` TRIGGER tr2 BEFORE INSERT ON `a@b`.t1 FOR EACH ROW SET NEW.a = 100 * NEW.a' +sql_modes=0 +definers='root@localhost' +EOF +--write_file $MYSQLD_DATADIR/a@b/tr2.TRN +TYPE=TRIGGERNAME +trigger_table=t1 +EOF + +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS + WHERE TRIGGER_SCHEMA="#mysql50#a@b" ORDER BY trigger_name; + +--echo mysqlcheck --fix-db-names --fix-table-names --all-databases +--exec $MYSQL_CHECK --default-character-set=utf8 --fix-db-names --fix-table-names --all-databases + +USE `a@b`; +SELECT * FROM INFORMATION_SCHEMA.TRIGGERS + WHERE TRIGGER_SCHEMA="a@b" ORDER BY trigger_name; + +INSERT INTO `c@d` VALUES (2), (1); +SELECT * FROM `c@d`; +INSERT INTO t1 VALUES (3), (5); +SELECT * FROM t1; + +DROP DATABASE `a@b`; + +USE test; + +--echo # +--echo # Bug #31821: --all-in-1 and --fix-table-names don't work together +--echo # + +--disable_warnings +drop table if exists `#mysql50#t1-1`; +--enable_warnings + +create table `#mysql50#t1-1` (a int) engine=myisam; +--exec $MYSQL_CHECK --all-in-1 --fix-table-names --databases test +show tables like 't1-1'; +drop table `t1-1`; + +create table `#mysql50#t1-1` (a int) engine=myisam; +--exec $MYSQL_CHECK --all-in-1 --fix-table-names test "#mysql50#t1-1" +show tables like 't1-1'; +drop table `t1-1`; + +--echo End of 5.1 tests + + +--echo # +--echo # Bug #35269: mysqlcheck behaves different depending on order of parameters +--echo # + +--error 13 +--exec $MYSQL_CHECK -a --fix-table-names test "#mysql50#t1-1" +--error 1 +--exec $MYSQL_CHECK -aoc test "#mysql50#t1-1" + + +--echo # +--echo # Bug#11755431 47205: MAP 'REPAIR TABLE' TO RECREATE +ANALYZE FOR +--echo # ENGINES NOT SUPPORTING NATIVE +--echo # + +--disable_warnings +DROP TABLE IF EXISTS bug47205; +--enable_warnings + +--echo # +--echo # Test 1: Check that ALTER TABLE ... rebuilds the table + +CREATE TABLE bug47205(a VARCHAR(20) PRIMARY KEY) + DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci engine=innodb; + +INSERT INTO bug47205 VALUES ("foobar"); +FLUSH TABLE bug47205; + +--echo # Replace the FRM with a 5.0 FRM that will require upgrade +--remove_file $MYSQLD_DATADIR/test/bug47205.frm +--copy_file std_data/bug47205.frm $MYSQLD_DATADIR/test/bug47205.frm + +--echo # Should indicate that ALTER TABLE ... FORCE is needed +CHECK TABLE bug47205 FOR UPGRADE; + +--echo # ALTER TABLE ... FORCE should rebuild the table +ALTER TABLE bug47205 FORCE; + +--echo # Table should now be ok +CHECK TABLE bug47205 FOR UPGRADE; + +DROP TABLE bug47205; + +--echo # +--echo # Test 2: InnoDB - REPAIR not supported + +CREATE TABLE bug47205(a VARCHAR(20) PRIMARY KEY) + DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci engine=innodb; + +FLUSH TABLE bug47205; + +--echo # Replace the FRM with a 5.0 FRM that will require upgrade +--remove_file $MYSQLD_DATADIR/test/bug47205.frm +--copy_file std_data/bug47205.frm $MYSQLD_DATADIR/test/bug47205.frm + +--echo # Should indicate that ALTER TABLE .. FORCE is needed +CHECK TABLE bug47205 FOR UPGRADE; + +--echo # Running mysqlcheck to check and upgrade +--exec $MYSQL_CHECK --check-upgrade --auto-repair test + +--echo # Table should now be ok +CHECK TABLE bug47205 FOR UPGRADE; + +DROP TABLE bug47205; + +--echo # +--echo # Test 3: MyISAM - REPAIR supported + +--echo # Use an old FRM that will require upgrade +--copy_file std_data/bug36055.frm $MYSQLD_DATADIR/test/bug47205.frm +--copy_file std_data/bug36055.MYD $MYSQLD_DATADIR/test/bug47205.MYD +--copy_file std_data/bug36055.MYI $MYSQLD_DATADIR/test/bug47205.MYI + +--echo # Should indicate that REPAIR TABLE is needed +CHECK TABLE bug47205 FOR UPGRADE; + +--echo # Running mysqlcheck to check and upgrade +--exec $MYSQL_CHECK --check-upgrade --auto-repair test + +--echo # Table should now be ok +CHECK TABLE bug47205 FOR UPGRADE; + +DROP TABLE bug47205; + + +--echo # +--echo #MDEV-6128:[PATCH] mysqlcheck wrongly escapes '.' in table names +--echo # +create table `t.1` (id int); +create view `v.1` as select 1; + +--echo mysqlcheck test t.1 +--exec $MYSQL_CHECK test t.1 +--echo mysqlcheck --all-in-1 test t.1 +--exec $MYSQL_CHECK --all-in-1 test t.1 +--echo mysqlcheck --all-in-1 --databases --process-views test +--exec $MYSQL_CHECK --all-in-1 --databases --process-views test + +create table `t.2`(a varchar(20) primary key) default character set utf8 collate utf8_general_ci engine=innodb; +flush table `t.2`; +--remove_file $MYSQLD_DATADIR/test/t@002e2.frm +--copy_file std_data/bug47205.frm $MYSQLD_DATADIR/test/t@002e2.frm + +--copy_file std_data/bug36055.frm $MYSQLD_DATADIR/test/t@002e3.frm +--copy_file std_data/bug36055.MYD $MYSQLD_DATADIR/test/t@002e3.MYD +--copy_file std_data/bug36055.MYI $MYSQLD_DATADIR/test/t@002e3.MYI + +--echo mysqlcheck --check-upgrade --auto-repair test +--exec $MYSQL_CHECK --check-upgrade --auto-repair test + +check table `t.1`, `t.2`, `t.3`; +check table `t.1`, `t.2`, `t.3` for upgrade; +drop view `v.1`; +drop table test.`t.1`, `t.2`, `t.3`; + +--echo # +--echo # MDEV-8123 mysqlcheck: new --process-views option conflicts with --quick, --extended and such +--echo # +create view v1 as select 1; +--echo mysqlcheck --process-views test +--exec $MYSQL_CHECK --process-views test +--echo mysqlcheck --process-views --extended test +--exec $MYSQL_CHECK --process-views --extended test +--echo mysqlcheck --process-views --fast test +--exec $MYSQL_CHECK --process-views --fast test +--echo mysqlcheck --process-views --quick test +--exec $MYSQL_CHECK --process-views --quick test +--echo mysqlcheck --process-views --check-only-changed test +--exec $MYSQL_CHECK --process-views --check-only-changed test +--echo mysqlcheck --process-views --medium-check test +--exec $MYSQL_CHECK --process-views --medium-check test +--echo mysqlcheck --process-views --check-upgrade test +--exec $MYSQL_CHECK --process-views --check-upgrade test +drop view v1; + + +--echo # +--echo # MDEV-8124 mysqlcheck: --auto-repair runs REPAIR TABLE instead of REPAIR VIEW on views +--echo # +create table t1(a int); +--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v1.frm $MYSQLD_DATADIR/test/v1.frm +--echo mysqlcheck --process-views --check-upgrade --auto-repair test +--exec $MYSQL_CHECK --process-views --check-upgrade --auto-repair test +drop view v1; +drop table t1; + +create table `#mysql50#t1``1` (a int) engine=myisam; +--exec $MYSQL_CHECK --fix-table-names --databases test +show tables; +drop table `t1``1`; + +# +# MDEV-9440 mysqlcheck -A --auto-repair selects wrong database when trying to repair broken table +# +call mtr.add_suppression("ha_myisam"); +call mtr.add_suppression("Checking table"); +create database mysqltest1; +create table mysqltest1.t1 (a int) engine=myisam; +create table t2 (a int); + +let $datadir= `select @@datadir`; +remove_file $datadir/mysqltest1/t1.MYD; +write_file $datadir/mysqltest1/t1.MYD; +foo +EOF + +check table mysqltest1.t1; + +--exec $MYSQL_CHECK -A --auto-repair --fast + +drop table t2; +drop database mysqltest1; + +--echo # +--echo #MDEV-7384 [PATCH] add PERSISENT FOR ALL option to mysqlanalyze/mysqlcheck +--echo # +create table t1(a int); +insert into t1 (a) values (1), (2), (3); +select db_name,table_name,column_name,min_value,max_value,nulls_ratio,avg_length,avg_frequency from mysql.column_stats order by db_name,table_name; +--exec $MYSQL_CHECK --analyze test t1 --persistent +select db_name,table_name,column_name,min_value,max_value,nulls_ratio,avg_length,avg_frequency from mysql.column_stats where db_name = 'test' and table_name = 't1' order by db_name,table_name; +drop table t1; |