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 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 * from mysql.column_stats; --exec $MYSQL_CHECK --analyze test t1 --persistent --replace_column 9 # 10 # 11 # select * from mysql.column_stats where db_name = 'test' and table_name = 't1'; drop table t1;