Update tests for mysql_upgrade_view

Add bad checksum test
Validate REPAIR VIEW {table} add mariadb-version to the frm
Add tests for the binary logging of REPAIR VIEW

3 files changed, 242 insertions, 7 deletions

diff --git a/mysql-test/r/mysql_upgrade_view.result b/mysql-test/r/mysql_upgrade_view.result
index 3ce1bf51544..aa4841c213a 100644
--- a/mysql-test/r/mysql_upgrade_view.result
+++ b/mysql-test/r/mysql_upgrade_view.result
@@ -1,7 +1,109 @@
-drop table if exists t1,v1,v2,v3,v4;
-drop view if exists t1,v1,v2,v3,v4;
-flush tables;
+set sql_log_bin=0;
+drop table if exists t1,v1,v2,v3,v4,v1badcheck;
+drop view if exists t1,v1,v2,v3,v4,v1badcheck;
 create table t1(a int);
+create table kv(k varchar(30) NOT NULL PRIMARY KEY,v  varchar(50));
+flush tables;
+Phase 1/4: Fixing views - skipped - not required
+Phase 2/4: Fixing table and database names
+Phase 3/4: Checking and upgrading tables
+Processing databases
+information_schema
+mtr
+mtr.global_suppressions                            OK
+mtr.test_suppressions                              OK
+mysql
+mysql.columns_priv                                 OK
+mysql.db                                           OK
+mysql.event                                        OK
+mysql.func                                         OK
+mysql.help_category                                OK
+mysql.help_keyword                                 OK
+mysql.help_relation                                OK
+mysql.help_topic                                   OK
+mysql.host                                         OK
+mysql.ndb_binlog_index                             OK
+mysql.plugin                                       OK
+mysql.proc                                         OK
+mysql.procs_priv                                   OK
+mysql.proxies_priv                                 OK
+mysql.servers                                      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.user                                         OK
+performance_schema
+test
+test.kv                                            OK
+test.t1                                            OK
+Phase 4/4: Running 'mysql_fix_privilege_tables'...
+OK
+show create view v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=MERGE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1`	utf8	utf8_general_ci
+show create view v2;
+View	Create View	character_set_client	collation_connection
+v2	CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` AS `a` from `t1`	utf8	utf8_general_ci
+show create view v3;
+View	Create View	character_set_client	collation_connection
+v3	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`a` AS `a` from `t1`	utf8	utf8_general_ci
+set sql_log_bin=1;
+REPAIR VIEW v1,v2;
+Table	Op	Msg_type	Msg_text
+test.v1	repair	status	OK
+test.v2	repair	status	OK
+REPAIR VIEW v1badcheck;
+Table	Op	Msg_type	Msg_text
+test.v1badcheck	repair	status	OK
+REPAIR NO_WRITE_TO_BINLOG VIEW v3;
+Table	Op	Msg_type	Msg_text
+test.v3	repair	status	OK
+set sql_log_bin=0;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	use `test`; REPAIR VIEW v1,v2
+master-bin.000001	#	Query	#	#	use `test`; REPAIR VIEW v1badcheck
+LOAD DATA INFILE 'MYSQLD_DATADIR/test/v1.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '=';
+SELECT k,v from kv where k in ('md5','algorithm');
+k	v
+algorithm	1
+md5	5e6eaf216e7b016fcedfd4e1113517af
+SELECT k from kv where k ='mariadb-version';
+k
+mariadb-version
+truncate table kv;
+LOAD DATA INFILE 'MYSQLD_DATADIR/test/v2.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '=';
+SELECT k,v from kv where k in ('md5','algorithm');
+k	v
+algorithm	2
+md5	5e6eaf216e7b016fcedfd4e1113517af
+SELECT k from kv where k ='mariadb-version';
+k
+mariadb-version
+truncate table kv;
+LOAD DATA INFILE 'MYSQLD_DATADIR/test/v3.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '=';
+SELECT k,v from kv where k in ('md5','algorithm');
+k	v
+algorithm	0
+md5	5e6eaf216e7b016fcedfd4e1113517af
+SELECT k from kv where k ='mariadb-version';
+k
+mariadb-version
+truncate table kv;
+LOAD DATA INFILE 'MYSQLD_DATADIR/test/v1badcheck.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '=';
+SELECT k,v from kv where k in ('md5','algorithm');
+k	v
+algorithm	1
+md5	5e6eaf216e7b016fcedfd4e1113517af
+SELECT k from kv where k ='mariadb-version';
+k
+mariadb-version
+truncate table kv;
+drop view if exists v1,v2,v3,v1badcheck;
+flush tables;
 create algorithm=temptable view v4 as select a from t1;
 show create view v1;
 View	Create View	character_set_client	collation_connection
@@ -54,6 +156,7 @@ mysql.time_zone_transition_type                    OK
 mysql.user                                         OK
 performance_schema
 test
+test.kv                                            OK
 test.t1                                            OK
 Phase 4/4: Running 'mysql_fix_privilege_tables'...
 OK
@@ -69,6 +172,30 @@ v3	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VI
 show create view v4;
 View	Create View	character_set_client	collation_connection
 v4	CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS select `t1`.`a` AS `a` from `t1`	latin1	latin1_swedish_ci
+LOAD DATA INFILE 'MYSQLD_DATADIR/test/v1.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '=';
+SELECT k,v from kv where k in ('md5','algorithm');
+k	v
+algorithm	2
+md5	5e6eaf216e7b016fcedfd4e1113517af
+SELECT k from kv where k ='mariadb-version';
+k
+mariadb-version
+truncate table kv;
+drop view if exists v1,v2,v3;
+flush tables;
+test.v1                                            OK
+test.v2                                            OK
+test.v3                                            OK
+test.v4                                            OK
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	use `test`; REPAIR VIEW v1,v2
+master-bin.000001	#	Query	#	#	use `test`; REPAIR VIEW v1badcheck
+master-bin.000001	#	Query	#	#	use `test`; REPAIR VIEW `v1`  FROM MYSQL
+master-bin.000001	#	Query	#	#	use `test`; REPAIR VIEW `v2`  FROM MYSQL
+master-bin.000001	#	Query	#	#	use `test`; REPAIR VIEW `v3`  FROM MYSQL
+master-bin.000001	#	Query	#	#	use `test`; REPAIR VIEW `v4`  FROM MYSQL
 flush tables;
+drop table if exists kv;
 drop view v1,v2,v3,v4;
 drop table t1;
diff --git a/mysql-test/std_data/mysql_upgrade/v1badcheck.frm b/mysql-test/std_data/mysql_upgrade/v1badcheck.frm
new file mode 100644
index 00000000000..a1e3bfe5cbc
--- /dev/null
+++ b/mysql-test/std_data/mysql_upgrade/v1badcheck.frm
@@ -0,0 +1,15 @@
+TYPE=VIEW
+query=select `test`.`t1`.`a` AS `a` from `test`.`t1`
+md5=00000000000000000000000000000000
+updatable=0
+algorithm=1
+definer_user=root
+definer_host=localhost
+suid=2
+with_check_option=0
+timestamp=2014-12-11 15:26:20
+create-version=1
+source=select a from t1
+client_cs_name=utf8
+connection_cl_name=utf8_general_ci
+view_body_utf8=select `test`.`t1`.`a` AS `a` from `test`.`t1`
diff --git a/mysql-test/t/mysql_upgrade_view.test b/mysql-test/t/mysql_upgrade_view.test
index 42c0a7f3c75..9bd86abd724 100644
--- a/mysql-test/t/mysql_upgrade_view.test
+++ b/mysql-test/t/mysql_upgrade_view.test
@@ -1,11 +1,72 @@
+-- source include/have_log_bin.inc
 
+set sql_log_bin=0;
 --disable_warnings
-drop table if exists t1,v1,v2,v3,v4;
-drop view if exists t1,v1,v2,v3,v4;
+drop table if exists t1,v1,v2,v3,v4,v1badcheck;
+drop view if exists t1,v1,v2,v3,v4,v1badcheck;
 --enable_warnings
 
+create table t1(a int);
+create table kv(k varchar(30) NOT NULL PRIMARY KEY,v  varchar(50));
+
 let $MYSQLD_DATADIR= `select @@datadir`;
 
+--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v1.frm $MYSQLD_DATADIR/test/v1.frm
+--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v1badcheck.frm $MYSQLD_DATADIR/test/v1badcheck.frm
+--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v2.frm $MYSQLD_DATADIR/test/v2.frm
+--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v3.frm $MYSQLD_DATADIR/test/v3.frm
+
+flush tables;
+
+--replace_result $MYSQLTEST_VARDIR var
+--exec $MYSQL_UPGRADE --force 2>&1
+
+# "Phase 1/4: Fixing views - skipped - not required"  expected
+
+show create view v1;
+show create view v2;
+show create view v3;
+
+# Now force a mariadb version to be added
+
+set sql_log_bin=1;
+REPAIR VIEW v1,v2;
+REPAIR VIEW v1badcheck;
+REPAIR NO_WRITE_TO_BINLOG VIEW v3;
+set sql_log_bin=0;
+
+--source include/show_binlog_events.inc
+
+--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
+eval LOAD DATA INFILE '$MYSQLD_DATADIR/test/v1.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '=';
+SELECT k,v from kv where k in ('md5','algorithm');
+SELECT k from kv where k ='mariadb-version';
+truncate table kv;
+
+--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
+eval LOAD DATA INFILE '$MYSQLD_DATADIR/test/v2.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '=';
+SELECT k,v from kv where k in ('md5','algorithm');
+SELECT k from kv where k ='mariadb-version';
+truncate table kv;
+
+--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
+eval LOAD DATA INFILE '$MYSQLD_DATADIR/test/v3.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '=';
+SELECT k,v from kv where k in ('md5','algorithm');
+SELECT k from kv where k ='mariadb-version';
+truncate table kv;
+
+--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
+eval LOAD DATA INFILE '$MYSQLD_DATADIR/test/v1badcheck.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '=';
+SELECT k,v from kv where k in ('md5','algorithm');
+SELECT k from kv where k ='mariadb-version';
+truncate table kv;
+
+--disable_warnings
+drop view if exists v1,v2,v3,v1badcheck;
+--enable_warnings
+
+# Make it look like a MySQL directory now
+
 --copy_file $MYSQLD_DATADIR/mysql/event.MYI $MYSQLD_DATADIR/mysql/ev_bk.MYI
 --copy_file $MYSQLD_DATADIR/mysql/event.MYD $MYSQLD_DATADIR/mysql/ev_bk.MYD
 --copy_file $MYSQLD_DATADIR/mysql/event.frm $MYSQLD_DATADIR/mysql/ev_bk.frm
@@ -15,19 +76,20 @@ let $MYSQLD_DATADIR= `select @@datadir`;
 --copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/event.MYI $MYSQLD_DATADIR/mysql/event.MYI
 --copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/event.MYD $MYSQLD_DATADIR/mysql/event.MYD
 --copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/event.frm $MYSQLD_DATADIR/mysql/event.frm
+
 --copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v1.frm $MYSQLD_DATADIR/test/v1.frm
 --copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v2.frm $MYSQLD_DATADIR/test/v2.frm
 --copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v3.frm $MYSQLD_DATADIR/test/v3.frm
 
 flush tables;
 
-create table t1(a int);
 create algorithm=temptable view v4 as select a from t1;
 show create view v1;
 show create view v2;
 show create view v3;
 show create view v4;
 
+# here we test the fixing views does occur
 --replace_result $MYSQLTEST_VARDIR var
 --exec $MYSQL_UPGRADE --force 2>&1
 
@@ -36,6 +98,37 @@ show create view v2;
 show create view v3;
 show create view v4;
 
+--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
+eval LOAD DATA INFILE '$MYSQLD_DATADIR/test/v1.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '=';
+SELECT k,v from kv where k in ('md5','algorithm');
+SELECT k from kv where k ='mariadb-version';
+truncate table kv;
+
+--disable_warnings
+drop view if exists v1,v2,v3;
+--enable_warnings
+
+
+--remove_file $MYSQLD_DATADIR/mysql/event.MYI
+--remove_file $MYSQLD_DATADIR/mysql/event.MYD
+--remove_file $MYSQLD_DATADIR/mysql/event.frm
+
+--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/event.MYI $MYSQLD_DATADIR/mysql/event.MYI
+--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/event.MYD $MYSQLD_DATADIR/mysql/event.MYD
+--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/event.frm $MYSQLD_DATADIR/mysql/event.frm
+
+--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v1.frm $MYSQLD_DATADIR/test/v1.frm
+--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v2.frm $MYSQLD_DATADIR/test/v2.frm
+--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v3.frm $MYSQLD_DATADIR/test/v3.frm
+flush tables;
+
+# check of binlog
+--exec $MYSQL_CHECK --write-binlog --fix-view-algorithm --skip-fix-tables --all-databases 2>&1
+
+--source include/show_binlog_events.inc
+
+# back to mariadb default
+
 --remove_file $MYSQLD_DATADIR/mysql/event.MYI
 --remove_file $MYSQLD_DATADIR/mysql/event.MYD
 --remove_file $MYSQLD_DATADIR/mysql/event.frm
@@ -47,7 +140,7 @@ show create view v4;
 --remove_file $MYSQLD_DATADIR/mysql/ev_bk.frm
 flush tables;
 
+drop table if exists kv;
 drop view v1,v2,v3,v4;
 drop table t1;
 
-#select sleep(100);