call mtr.add_suppression("Invalid .old.. table or database name"); 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; mtr.global_suppressions OK mtr.test_suppressions OK mysql.column_stats OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.gtid_slave_pos OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.roles_mapping OK mysql.servers OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.transaction_registry OK mysql.user OK mtr.global_suppressions Table is already up to date mtr.test_suppressions Table is already up to date mysql.column_stats OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.gtid_slave_pos OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.index_stats OK mysql.innodb_index_stats note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.innodb_table_stats note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.roles_mapping OK mysql.servers OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.transaction_registry note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.user OK mysql.column_stats OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.gtid_slave_pos OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.roles_mapping OK mysql.servers OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.transaction_registry OK mysql.user OK mysql.column_stats Table is already up to date mysql.columns_priv Table is already up to date mysql.db Table is already up to date mysql.event Table is already up to date mysql.func Table is already up to date mysql.gtid_slave_pos Table is already up to date mysql.help_category Table is already up to date mysql.help_keyword Table is already up to date mysql.help_relation Table is already up to date mysql.help_topic Table is already up to date mysql.host Table is already up to date mysql.index_stats Table is already up to date mysql.innodb_index_stats note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.innodb_table_stats note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.plugin Table is already up to date mysql.proc Table is already up to date mysql.procs_priv Table is already up to date mysql.proxies_priv Table is already up to date mysql.roles_mapping Table is already up to date mysql.servers Table is already up to date mysql.table_stats Table is already up to date mysql.tables_priv Table is already up to date mysql.time_zone Table is already up to date mysql.time_zone_leap_second Table is already up to date mysql.time_zone_name Table is already up to date mysql.time_zone_transition Table is already up to date mysql.time_zone_transition_type Table is already up to date mysql.transaction_registry note : Table does not support optimize, doing recreate + analyze instead status : OK mysql.user Table is already up to date create table t1 (a int) engine=myisam; create view v1 as select * from t1; test.t1 OK test.t1 Table is already up to date test.t1 OK test.t1 Table is already up to date drop view v1; drop table t1; create table `t``1`(a int) engine=myisam; create table `t 1`(a int) engine=myisam; test.t 1 OK test.t`1 OK drop table `t``1`, `t 1`; 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; removing and creating d_bug25347.t_bug25347 Error : Incorrect file format 't_bug25347' error : Corrupt insert into t_bug25347 values (4),(5),(6); ERROR HY000: Incorrect file format 't_bug25347' d_bug25347.t_bug25347 warning : Number of rows changed from 0 to 3 status : OK insert into t_bug25347 values (7),(8),(9); select * from t_bug25347; a 1 2 3 7 8 9 select * from v_bug25347; a 1 2 3 7 8 9 drop view v_bug25347; drop table t_bug25347; drop database d_bug25347; use test; create view v1 as select * from information_schema.routines; check table v1, information_schema.routines; Table Op Msg_type Msg_text test.v1 check status OK information_schema.routines check note The storage engine for the table doesn't support check drop view v1; call mtr.add_suppression("Error reading file './test/t1.frm'"); CREATE TABLE t1(a INT) engine=myisam; CREATE TABLE t2(a INT) engine=myisam; test.t1 Error : Incorrect information in file: './test/t1.frm' error : Corrupt test.t2 OK DROP TABLE t1, t2; End of 5.0 tests create table t1(a int) engine=myisam; create view v1 as select * from t1; show tables; Tables_in_test t1 v1 show tables; Tables_in_test #mysql50#v-1 t1 v1 test.t1 OK show tables; Tables_in_test t1 v-1 v1 drop view v1, `v-1`; drop table t1; SET NAMES utf8; CREATE TABLE `#mysql50#@` (a INT) engine=myisam; SHOW TABLES; Tables_in_test #mysql50#@ SET NAMES DEFAULT; mysqlcheck --fix-table-names --databases test SET NAMES utf8; SHOW TABLES; Tables_in_test @ DROP TABLE `@`; CREATE TABLE `я` (a INT) engine=myisam; SET NAMES DEFAULT; call mtr.add_suppression("@003f.frm' \\(errno: 22\\)"); mysqlcheck --default-character-set="latin1" --databases test call mtr.add_suppression("Can't find file: '..test.@003f.frm'"); test.? Error : Table doesn't exist status : Operation failed mysqlcheck --default-character-set="utf8" --databases test test.я OK SET NAMES utf8; DROP TABLE `я`; SET NAMES DEFAULT; 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; SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA="#mysql50#a@b" ORDER BY trigger_name; TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION def #mysql50#a@b tr1 INSERT def #mysql50#a@b #mysql50#c@d 1 NULL SET NEW.a = 10 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci def #mysql50#a@b tr2 INSERT def #mysql50#a@b t1 1 NULL SET NEW.a = 100 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci mysqlcheck --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; TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION def a@b tr1 INSERT def a@b c@d 1 NULL SET NEW.a = 10 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost utf8 utf8_general_ci latin1_swedish_ci def a@b tr2 INSERT def a@b t1 1 NULL SET NEW.a = 100 * NEW.a ROW BEFORE NULL NULL OLD NEW NULL root@localhost utf8 utf8_general_ci latin1_swedish_ci INSERT INTO `c@d` VALUES (2), (1); SELECT * FROM `c@d`; a 20 10 INSERT INTO t1 VALUES (3), (5); SELECT * FROM t1; a 300 500 DROP DATABASE `a@b`; USE test; # # Bug #31821: --all-in-1 and --fix-table-names don't work together # drop table if exists `#mysql50#t1-1`; create table `#mysql50#t1-1` (a int) engine=myisam; show tables like 't1-1'; Tables_in_test (t1-1) t1-1 drop table `t1-1`; create table `#mysql50#t1-1` (a int) engine=myisam; show tables like 't1-1'; Tables_in_test (t1-1) t1-1 drop table `t1-1`; End of 5.1 tests # # Bug #35269: mysqlcheck behaves different depending on order of parameters # # # Bug#11755431 47205: MAP 'REPAIR TABLE' TO RECREATE +ANALYZE FOR # ENGINES NOT SUPPORTING NATIVE # DROP TABLE IF EXISTS bug47205; # # 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; # Replace the FRM with a 5.0 FRM that will require upgrade # Should indicate that ALTER TABLE ... FORCE is needed CHECK TABLE bug47205 FOR UPGRADE; Table Op Msg_type Msg_text test.bug47205 check error Table rebuild required. Please do "ALTER TABLE `bug47205` FORCE" or dump/reload to fix it! # ALTER TABLE ... FORCE should rebuild the table ALTER TABLE bug47205 FORCE; # Table should now be ok CHECK TABLE bug47205 FOR UPGRADE; Table Op Msg_type Msg_text test.bug47205 check status OK DROP TABLE bug47205; # # 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; # Replace the FRM with a 5.0 FRM that will require upgrade # Should indicate that ALTER TABLE .. FORCE is needed CHECK TABLE bug47205 FOR UPGRADE; Table Op Msg_type Msg_text test.bug47205 check error Table rebuild required. Please do "ALTER TABLE `bug47205` FORCE" or dump/reload to fix it! # Running mysqlcheck to check and upgrade test.bug47205 error : Table rebuild required. Please do "ALTER TABLE `bug47205` FORCE" or dump/reload to fix it! Repairing tables # Table should now be ok CHECK TABLE bug47205 FOR UPGRADE; Table Op Msg_type Msg_text test.bug47205 check status OK DROP TABLE bug47205; # # Test 3: MyISAM - REPAIR supported # Use an old FRM that will require upgrade # Should indicate that REPAIR TABLE is needed CHECK TABLE bug47205 FOR UPGRADE; Table Op Msg_type Msg_text test.bug47205 check error Upgrade required. Please do "REPAIR TABLE `bug47205`" or dump/reload to fix it! # Running mysqlcheck to check and upgrade test.bug47205 Needs upgrade Repairing tables test.bug47205 OK # Table should now be ok CHECK TABLE bug47205 FOR UPGRADE; Table Op Msg_type Msg_text test.bug47205 check status OK DROP TABLE bug47205; # #MDEV-6128:[PATCH] mysqlcheck wrongly escapes '.' in table names # create table `t.1` (id int); create view `v.1` as select 1; mysqlcheck test t.1 test.t.1 OK mysqlcheck --all-in-1 test t.1 test.t.1 OK mysqlcheck --all-in-1 --databases --process-views test test.t.1 OK test.v.1 OK create table `t.2`(a varchar(20) primary key) default character set utf8 collate utf8_general_ci engine=innodb; flush table `t.2`; mysqlcheck --check-upgrade --auto-repair test test.t.1 OK test.t.2 error : Table rebuild required. Please do "ALTER TABLE `t.2` FORCE" or dump/reload to fix it! test.t.3 Needs upgrade Repairing tables test.t.3 OK check table `t.1`, `t.2`, `t.3`; Table Op Msg_type Msg_text test.t.1 check status OK test.t.2 check status OK test.t.3 check status OK check table `t.1`, `t.2`, `t.3` for upgrade; Table Op Msg_type Msg_text test.t.1 check status OK test.t.2 check status OK test.t.3 check status OK drop view `v.1`; drop table test.`t.1`, `t.2`, `t.3`; # # MDEV-8123 mysqlcheck: new --process-views option conflicts with --quick, --extended and such # create view v1 as select 1; mysqlcheck --process-views test test.v1 OK mysqlcheck --process-views --extended test test.v1 OK mysqlcheck --process-views --fast test mysqlcheck --process-views --quick test test.v1 OK mysqlcheck --process-views --check-only-changed test mysqlcheck --process-views --medium-check test test.v1 OK mysqlcheck --process-views --check-upgrade test test.v1 OK drop view v1; # # MDEV-8124 mysqlcheck: --auto-repair runs REPAIR TABLE instead of REPAIR VIEW on views # create table t1(a int); mysqlcheck --process-views --check-upgrade --auto-repair test test.t1 OK test.v1 Needs upgrade Repairing views test.v1 OK drop view v1; drop table t1; create table `#mysql50#t1``1` (a int) engine=myisam; show tables; Tables_in_test t1`1 drop table `t1``1`; 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); check table mysqltest1.t1; Table Op Msg_type Msg_text mysqltest1.t1 check warning Size of datafile is: 4 Should be: 0 mysqltest1.t1 check error got error: 0 when reading datafile at record: 0 mysqltest1.t1 check error Corrupt mtr.global_suppressions Table is already up to date mtr.test_suppressions Table is already up to date mysql.column_stats Table is already up to date mysql.columns_priv Table is already up to date mysql.db Table is already up to date mysql.event Table is already up to date mysql.func Table is already up to date mysql.gtid_slave_pos Table is already up to date mysql.help_category Table is already up to date mysql.help_keyword Table is already up to date mysql.help_relation Table is already up to date mysql.help_topic Table is already up to date mysql.host Table is already up to date mysql.index_stats Table is already up to date mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.plugin Table is already up to date mysql.proc Table is already up to date mysql.procs_priv Table is already up to date mysql.proxies_priv Table is already up to date mysql.roles_mapping Table is already up to date mysql.servers Table is already up to date mysql.table_stats Table is already up to date mysql.tables_priv Table is already up to date mysql.time_zone Table is already up to date mysql.time_zone_leap_second Table is already up to date mysql.time_zone_name Table is already up to date mysql.time_zone_transition Table is already up to date mysql.time_zone_transition_type Table is already up to date mysql.transaction_registry OK mysql.user Table is already up to date mysqltest1.t1 warning : Table is marked as crashed warning : Size of datafile is: 4 Should be: 0 error : got error: 0 when reading datafile at record: 0 error : Corrupt test.t2 Table is already up to date Repairing tables mysqltest1.t1 OK drop table t2; drop database mysqltest1; # #MDEV-7384 [PATCH] add PERSISENT FOR ALL option to mysqlanalyze/mysqlcheck # create table t1(a int); insert into t1 (a) values (1), (2), (3); select * from mysql.column_stats; db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram test.t1 Engine-independent statistics collected status : OK select * from mysql.column_stats where db_name = 'test' and table_name = 't1'; db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram test t1 a 1 3 0.0000 4.0000 1.0000 0 NULL NULL drop table t1;