diff options
| author | Ingo Struewing <ingo.struewing@sun.com> | 2009-10-02 13:27:48 +0200 |
|---|---|---|
| committer | Ingo Struewing <ingo.struewing@sun.com> | 2009-10-02 13:27:48 +0200 |
| commit | 60819c6a652cb2077ab7c684adadbc63ac694517 (patch) | |
| tree | 2abe30b77ac2a2ce2e50173dc413ee879d722c3a | |
| parent | 2deaff4d3f7a16c0f5ccb37228a7114d926f2b45 (diff) | |
| parent | 7c5e6dd96a2651849be96fa18668433242314f89 (diff) | |
| download | mariadb-git-60819c6a652cb2077ab7c684adadbc63ac694517.tar.gz | |
auto-merge
58 files changed, 2865 insertions, 200 deletions
diff --git a/client/mysqlbinlog.cc b/client/mysqlbinlog.cc index 82af7ca65f6..ebe34231238 100644 --- a/client/mysqlbinlog.cc +++ b/client/mysqlbinlog.cc @@ -726,7 +726,10 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev, switch (ev_type) { case QUERY_EVENT: - if (shall_skip_database(((Query_log_event*)ev)->db)) + if (strncmp(((Query_log_event*)ev)->query, "BEGIN", 5) && + strncmp(((Query_log_event*)ev)->query, "COMMIT", 6) && + strncmp(((Query_log_event*)ev)->query, "ROLLBACK", 8) && + shall_skip_database(((Query_log_event*)ev)->db)) goto end; if (opt_base64_output_mode == BASE64_OUTPUT_ALWAYS) { diff --git a/libmysqld/lib_sql.cc b/libmysqld/lib_sql.cc index d4a200c07b2..778d4874ad4 100644 --- a/libmysqld/lib_sql.cc +++ b/libmysqld/lib_sql.cc @@ -142,6 +142,8 @@ emb_advanced_command(MYSQL *mysql, enum enum_server_command command, if (!skip_check) result= thd->is_error() ? -1 : 0; + thd->mysys_var= 0; + #if defined(ENABLED_PROFILING) && defined(COMMUNITY_SERVER) thd->profiling.finish_current_query(); #endif @@ -634,6 +636,7 @@ void *create_embedded_thd(int client_flag) thread_count++; threads.append(thd); + thd->mysys_var= 0; return thd; err: delete(thd); diff --git a/mysql-test/extra/rpl_tests/rpl_auto_increment_insert_view.test b/mysql-test/extra/rpl_tests/rpl_auto_increment_insert_view.test new file mode 100644 index 00000000000..0bfa46de113 --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_auto_increment_insert_view.test @@ -0,0 +1,44 @@ +# +# This test verifies if inserting data into view that invokes a +# trigger will make the autoinc values become inconsistent on +# master and slave. +# +connection master; +CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb; +CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb; +CREATE TABLE t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +eval create trigger tr16 $insert_action on t1 for each row insert into t3(a) values(new.c1); +eval create trigger tr17 $insert_action on t2 for each row insert into t3(a) values(new.c2); +begin; +INSERT INTO t1(c1) VALUES (11), (12); +INSERT INTO t2(c2) VALUES (13), (14); + +CREATE VIEW v16 AS SELECT c1, c2 FROM t1, t2; + +INSERT INTO v16(c1) VALUES (15),(16); +INSERT INTO v16(c2) VALUES (17),(18); + +connection master1; +INSERT INTO v16(c1) VALUES (19),(20); +INSERT INTO v16(c2) VALUES (21),(22); + +connection master; +INSERT INTO v16(c1) VALUES (23), (24); +INSERT INTO v16(c1) VALUES (25), (26); +commit; +sync_slave_with_master; +--echo #Test if the results are consistent on master and slave +--echo #for 'INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS' +let $diff_table_1=master:test.t3; +let $diff_table_2=slave:test.t3; +source include/diff_tables.inc; + +connection master; +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP VIEW v16; +sync_slave_with_master; + + + diff --git a/mysql-test/extra/rpl_tests/rpl_auto_increment_invoke_trigger.test b/mysql-test/extra/rpl_tests/rpl_auto_increment_invoke_trigger.test new file mode 100644 index 00000000000..614d79d9c2d --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_auto_increment_invoke_trigger.test @@ -0,0 +1,82 @@ +# +# This test verifies if concurrent transactions that invoke a +# trigger that inserts more than one values into one or more +# tables with an auto_increment column will make the autoinc +# values become inconsistent on master and slave. +# + +connection master; +create table t1(a int, b int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +eval create trigger tr1 $trigger_action on t1 for each row insert into t2(a) values(6); + +create table t3(a int, b int) engine=innodb; +create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t5(a int) engine=innodb; +delimiter |; +eval create trigger tr2 $trigger_action on t3 for each row begin + insert into t4(a) values(f1_insert_triggered()); + insert into t4(a) values(f1_insert_triggered()); + insert into t5(a) values(8); +end | +delimiter ;| + +create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +delimiter //; +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER +BEGIN + INSERT INTO t6(a) values(2),(3); + RETURN 1; +END// +delimiter ;// + +begin; +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +insert into t1(a,b) values(1,1),(2,1); +insert into t3(a,b) values(1,1),(2,1); +update t1 set a = a + 5 where b = 1; +update t3 set a = a + 5 where b = 1; +delete from t1 where b = 1; +delete from t3 where b = 1; + +connection master1; +#The default autocommit is set to 1, so the statement is auto committed +insert into t2(a) values(3); +insert into t4(a) values(3); + +connection master; +commit; +insert into t1(a,b) values(4,2); +insert into t3(a,b) values(4,2); +update t1 set a = a + 5 where b = 2; +update t3 set a = a + 5 where b = 2; +delete from t1 where b = 2; +delete from t3 where b = 2; +--echo # To verify if insert/update in an autoinc column causes statement to be logged in row format +source include/show_binlog_events.inc; +commit; + +connection master; +sync_slave_with_master; +--echo #Test if the results are consistent on master and slave +--echo #for 'INVOKES A TRIGGER with $trigger_action action' +let $diff_table_1=master:test.t2; +let $diff_table_2=slave:test.t2; +source include/diff_tables.inc; +let $diff_table_1=master:test.t4; +let $diff_table_2=slave:test.t4; +source include/diff_tables.inc; +let $diff_table_1=master:test.t6; +let $diff_table_2=slave:test.t6; +source include/diff_tables.inc; + +connection master; +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +DROP TABLE t5; +DROP TABLE t6; +DROP FUNCTION f1_insert_triggered; +sync_slave_with_master; + diff --git a/mysql-test/extra/rpl_tests/rpl_autoinc_func_invokes_trigger.test b/mysql-test/extra/rpl_tests/rpl_autoinc_func_invokes_trigger.test new file mode 100644 index 00000000000..fece19b397d --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_autoinc_func_invokes_trigger.test @@ -0,0 +1,57 @@ +# +# This test verifies if concurrent transactions that call a +# function which invokes a 'after/before insert action' trigger +# that inserts more than one values into a table with autoinc +# column will make the autoinc values become inconsistent on +# master and slave. +# + +connection master; +create table t1(a int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +delimiter |; +CREATE FUNCTION f1_two_inserts_trigger() RETURNS INTEGER +BEGIN + INSERT INTO t2(a) values(2),(3); + INSERT INTO t2(a) values(2),(3); + RETURN 1; +END | +eval create trigger tr11 $insert_action on t2 for each row begin + insert into t3(a) values(new.a); + insert into t3(a) values(new.a); +end | +delimiter ;| +begin; +let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1); +insert into t1(a) values(f1_two_inserts_trigger()); + +connection master1; +#The default autocommit is set to 1, so the statement is auto committed +insert into t2(a) values(4),(5); + +connection master; +commit; +insert into t1(a) values(f1_two_inserts_trigger()); +--echo # To verify if insert/update in an autoinc column causes statement to be logged in row format +source include/show_binlog_events.inc; +commit; + +connection master; +sync_slave_with_master; +--echo #Test if the results are consistent on master and slave +--echo #for 'CALLS A FUNCTION which INVOKES A TRIGGER with $insert_action action' +let $diff_table_1=master:test.t2; +let $diff_table_2=slave:test.t2; +source include/diff_tables.inc; +let $diff_table_1=master:test.t3; +let $diff_table_2=slave:test.t3; +source include/diff_tables.inc; + +connection master; +drop table t1; +drop table t2; +drop table t3; +drop function f1_two_inserts_trigger; +sync_slave_with_master; + diff --git a/mysql-test/include/concurrent.inc b/mysql-test/include/concurrent.inc index 66f8a65a102..0b7299a3c34 100644 --- a/mysql-test/include/concurrent.inc +++ b/mysql-test/include/concurrent.inc @@ -25,8 +25,6 @@ # new wrapper t/concurrent_innodb_safelog.test # ---source include/not_embedded.inc - connection default; # # Show prerequisites for this test. diff --git a/mysql-test/include/have_not_innodb_plugin.inc b/mysql-test/include/have_not_innodb_plugin.inc new file mode 100644 index 00000000000..aaefbaf661c --- /dev/null +++ b/mysql-test/include/have_not_innodb_plugin.inc @@ -0,0 +1,4 @@ +disable_query_log; +--require r/not_true.require +select (PLUGIN_LIBRARY LIKE 'ha_innodb_plugin%') as `TRUE` from information_schema.plugins where PLUGIN_NAME='InnoDB'; +enable_query_log; diff --git a/mysql-test/lib/mtr_cases.pm b/mysql-test/lib/mtr_cases.pm index 224babaaf32..27165d56b50 100644 --- a/mysql-test/lib/mtr_cases.pm +++ b/mysql-test/lib/mtr_cases.pm @@ -493,6 +493,7 @@ sub collect_one_suite($) $lib_innodb_plugin) { my @new_cases; + my $sep= (IS_WINDOWS) ? ';' : ':'; foreach my $test (@cases) { @@ -520,12 +521,13 @@ sub collect_one_suite($) } } my $plugin_filename= basename($lib_innodb_plugin); + my $plugin_list= "innodb=$plugin_filename" . $sep . "innodb_locks=$plugin_filename"; push(@{$new_test->{master_opt}}, '--ignore-builtin-innodb'); push(@{$new_test->{master_opt}}, '--plugin-dir=' . dirname($lib_innodb_plugin)); - push(@{$new_test->{master_opt}}, "--plugin_load=innodb=$plugin_filename;innodb_locks=$plugin_filename"); + push(@{$new_test->{master_opt}}, "--plugin_load=$plugin_list"); push(@{$new_test->{slave_opt}}, '--ignore-builtin-innodb'); push(@{$new_test->{slave_opt}}, '--plugin-dir=' . dirname($lib_innodb_plugin)); - push(@{$new_test->{slave_opt}}, "--plugin_load=innodb=$plugin_filename;innodb_locks=$plugin_filename"); + push(@{$new_test->{slave_opt}}, "--plugin_load=$plugin_list"); if ($new_test->{combination}) { $new_test->{combination}.= ' + InnoDB plugin'; diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index da94f02fb27..647f9bb646b 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -1814,7 +1814,7 @@ sub environment_setup { ($lib_example_plugin ? dirname($lib_example_plugin) : ""); $ENV{'HA_EXAMPLE_SO'}="'".$plugin_filename."'"; - $ENV{'EXAMPLE_PLUGIN_LOAD'}="--plugin_load=;EXAMPLE=".$plugin_filename.";"; + $ENV{'EXAMPLE_PLUGIN_LOAD'}="--plugin_load=EXAMPLE=".$plugin_filename; } # ---------------------------------------------------- diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 5a115e9ea99..db7173d0b47 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -1268,4 +1268,66 @@ a b 4 b 5 a DROP TABLE t1; +# +# Bug#45567: Fast ALTER TABLE broken for enum and set +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a ENUM('a1','a2')); +INSERT INTO t1 VALUES ('a1'),('a2'); +# No copy: No modification +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# No copy: Add new enumeration to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a3'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# Copy: Modify and add new to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx','a5'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# Copy: Remove from the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# Copy: Add new enumeration +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# No copy: Add new enumerations to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx','a5','a6'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +DROP TABLE t1; +CREATE TABLE t1 (a SET('a1','a2')); +INSERT INTO t1 VALUES ('a1'),('a2'); +# No copy: No modification +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# No copy: Add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a3'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# Copy: Modify and add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx','a5'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# Copy: Remove from the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# Copy: Add new member +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# No copy: Add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# Copy: Numerical incrase (pack lenght) +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6','a7','a8','a9','a10'); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index 6305f8cd47a..2e3fa7f4acd 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -139,7 +139,7 @@ show create view testdb_1.v7; View Create View character_set_client collation_connection v7 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v7` AS select `testdb_1`.`t2`.`f1` AS `f1` from `t2` latin1 latin1_swedish_ci Warnings: -Warning 1356 View 'testdb_1.v7' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist show fields from testdb_1.v7; Field Type Null Key Default Extra f1 char(4) YES NULL @@ -169,7 +169,7 @@ show create view testdb_1.v7; View Create View character_set_client collation_connection v7 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v7` AS select `testdb_1`.`t2`.`f1` AS `f1` from `t2` latin1 latin1_swedish_ci Warnings: -Warning 1356 View 'testdb_1.v7' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist revoke insert(f1) on v3 from testdb_2@localhost; revoke show view on v5 from testdb_2@localhost; use testdb_1; @@ -187,7 +187,8 @@ ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v7' show create view testdb_1.v7; ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v7' show create view v4; -ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +View Create View character_set_client collation_connection +v4 CREATE ALGORITHM=UNDEFINED DEFINER=`testdb_2`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS select `v3`.`f1` AS `f1`,`v3`.`f2` AS `f2` from `testdb_1`.`v3` latin1 latin1_swedish_ci show fields from v4; Field Type Null Key Default Extra f1 char(4) YES NULL diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index 295a2f41d40..5f32561798b 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -44,16 +44,16 @@ SET TIMESTAMP=1000000000/*!*/; insert into t2 values () /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; DELIMITER ; # End of log file @@ -144,16 +144,16 @@ SET TIMESTAMP=1000000000/*!*/; insert into t2 values () /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (word) /*!*/; DELIMITER ; # End of log file @@ -359,29 +359,29 @@ SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a varchar(64) character set utf8) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.collation_database=7/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.collation_database=DEFAULT/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-#-#' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.collation_database=7/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-a-0' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-a-0' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; SET @@session.collation_database=DEFAULT/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-b-0' INTO table t1 +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-b-0' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; -load data LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-c-0' INTO table t1 character set koi8r +LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/tmp/SQL_LOAD_MB-c-0' INTO TABLE `t1` CHARACTER SET koi8r FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) /*!*/; SET TIMESTAMP=1000000000/*!*/; drop table t1 @@ -473,5 +473,94 @@ IS NOT NULL SET @@global.server_id= 1; RESET MASTER; FLUSH LOGS; +RESET MASTER; +FLUSH LOGS; +# +# Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified is exist +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; +DELIMITER /*!*/; +ROLLBACK/*!*/; +use test/*!*/; +SET TIMESTAMP=1253783037/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; +SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; +/*!\C latin1 *//*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +create table t1(a int) engine= innodb +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +BEGIN +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +insert into t1 (a) values (1) +/*!*/; +COMMIT/*!*/; +SET TIMESTAMP=1253783037/*!*/; +create table t3(a int) engine= innodb +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +BEGIN +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +insert into t3 (a) values (2) +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +ROLLBACK +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +create table t5(a int) engine= NDB +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +BEGIN +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +insert into t5 (a) values (3) +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +COMMIT +/*!*/; +DELIMITER ; +# End of log file +ROLLBACK /* added by mysqlbinlog */; +/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; +# +# Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified is not exist +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; +DELIMITER /*!*/; +ROLLBACK/*!*/; +SET TIMESTAMP=1253783037/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; +SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; +/*!\C latin1 *//*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +BEGIN +/*!*/; +COMMIT/*!*/; +SET TIMESTAMP=1253783037/*!*/; +BEGIN +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +ROLLBACK +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +BEGIN +/*!*/; +SET TIMESTAMP=1253783037/*!*/; +COMMIT +/*!*/; +DELIMITER ; +# End of log file +ROLLBACK /* added by mysqlbinlog */; +/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; End of 5.0 tests End of 5.1 tests diff --git a/mysql-test/r/not_true.require b/mysql-test/r/not_true.require new file mode 100644 index 00000000000..0032832f3d1 --- /dev/null +++ b/mysql-test/r/not_true.require @@ -0,0 +1,2 @@ +TRUE +NULL diff --git a/mysql-test/r/partition_innodb_builtin.result b/mysql-test/r/partition_innodb_builtin.result new file mode 100644 index 00000000000..384ce0790a4 --- /dev/null +++ b/mysql-test/r/partition_innodb_builtin.result @@ -0,0 +1,39 @@ +SET NAMES utf8; +CREATE TABLE `t``\""e` (a INT, PRIMARY KEY (a)) +ENGINE=InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +(PARTITION `p0``\""e` VALUES LESS THAN (100) +(SUBPARTITION `sp0``\""e`, +SUBPARTITION `sp1``\""e`), +PARTITION `p1``\""e` VALUES LESS THAN (MAXVALUE) +(SUBPARTITION `sp2``\""e`, +SUBPARTITION `sp3``\""e`)); +INSERT INTO `t``\""e` VALUES (0), (2), (6), (10), (14), (18), (22); +START TRANSACTION; +# con1 +SET NAMES utf8; +START TRANSACTION; +# default connection +UPDATE `t``\""e` SET a = 16 WHERE a = 0; +# con1 +UPDATE `t``\""e` SET a = 8 WHERE a = 22; +UPDATE `t``\""e` SET a = 12 WHERE a = 0; +# default connection +UPDATE `t``\""e` SET a = 4 WHERE a = 22; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +# First table reported in 'SHOW ENGINE InnoDB STATUS' +SHOW ENGINE InnoDB STATUS; +Type Name Status +InnoDB index `PRIMARY` of table `test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */ +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +SHOW ENGINE InnoDB STATUS; +Type Name Status +InnoDB index `PRIMARY` of table `test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */ +set @@sql_mode = @old_sql_mode; +# con1 +ROLLBACK; +# default connection +DROP TABLE `t``\""e`; +SET NAMES DEFAULT; diff --git a/mysql-test/r/partition_innodb_plugin.result b/mysql-test/r/partition_innodb_plugin.result new file mode 100644 index 00000000000..dd91eee316a --- /dev/null +++ b/mysql-test/r/partition_innodb_plugin.result @@ -0,0 +1,50 @@ +SET NAMES utf8; +CREATE TABLE `t``\""e` (a INT, PRIMARY KEY (a)) +ENGINE=InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +(PARTITION `p0``\""e` VALUES LESS THAN (100) +(SUBPARTITION `sp0``\""e`, +SUBPARTITION `sp1``\""e`), +PARTITION `p1``\""e` VALUES LESS THAN (MAXVALUE) +(SUBPARTITION `sp2``\""e`, +SUBPARTITION `sp3``\""e`)); +INSERT INTO `t``\""e` VALUES (0), (2), (6), (10), (14), (18), (22); +START TRANSACTION; +# con1 +SET NAMES utf8; +START TRANSACTION; +# default connection +UPDATE `t``\""e` SET a = 16 WHERE a = 0; +# con1 +UPDATE `t``\""e` SET a = 8 WHERE a = 22; +UPDATE `t``\""e` SET a = 12 WHERE a = 0; +# default connection +SELECT lock_table, COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS +GROUP BY lock_table; +lock_table COUNT(*) +`test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */ 2 +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +SELECT lock_table, COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS +GROUP BY lock_table; +lock_table COUNT(*) +"test"."t`\""""e" /* Partition "p0`\""""e", Subpartition "sp0`\""""e" */ 2 +set @@sql_mode = @old_sql_mode; +UPDATE `t``\""e` SET a = 4 WHERE a = 22; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +# First table reported in 'SHOW ENGINE InnoDB STATUS' +SHOW ENGINE InnoDB STATUS; +Type Name Status +InnoDB index `PRIMARY` of table `test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */ +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +SHOW ENGINE InnoDB STATUS; +Type Name Status +InnoDB index `PRIMARY` of table `test`.`t``\""e` /* Partition `p0``\""e`, Subpartition `sp0``\""e` */ +set @@sql_mode = @old_sql_mode; +# con1 +ROLLBACK; +# default connection +DROP TABLE `t``\""e`; +SET NAMES DEFAULT; diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index 7e280fa2fe5..4e43f52d8d7 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -606,7 +606,7 @@ SHOW CREATE VIEW v; View Create View character_set_client collation_connection v CREATE ALGORITHM=UNDEFINED DEFINER=`no-such-user`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `test`.`t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci Warnings: -Warning 1356 View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +Note 1449 The user specified as a definer ('no-such-user'@'localhost') does not exist SELECT * FROM v; ERROR HY000: The user specified as a definer ('no-such-user'@'localhost') does not exist DROP VIEW v; @@ -963,7 +963,7 @@ SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such`@`user_1` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci Warnings: -Warning 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist ALTER ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; Warnings: Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist @@ -971,7 +971,7 @@ SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=MERGE DEFINER=`no_such`@`user_1` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci Warnings: -Warning 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +Note 1449 The user specified as a definer ('no_such'@'user_1') does not exist ALTER ALGORITHM=TEMPTABLE DEFINER=no_such@user_2 VIEW v1 AS SELECT * FROM t1; Warnings: Note 1449 The user specified as a definer ('no_such'@'user_2') does not exist @@ -979,7 +979,7 @@ SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=TEMPTABLE DEFINER=`no_such`@`user_2` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci Warnings: -Warning 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +Note 1449 The user specified as a definer ('no_such'@'user_2') does not exist DROP VIEW v1; DROP TABLE t1; CREATE USER mysqluser1@localhost; @@ -1044,3 +1044,177 @@ DROP DATABASE mysqltest1; DROP VIEW test.v3; DROP USER mysqluser1@localhost; USE test; +# +# Bug#35996: SELECT + SHOW VIEW should be enough to display view +# definition +# +CREATE USER mysqluser1@localhost; +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; +GRANT USAGE, SELECT, CREATE VIEW, SHOW VIEW +ON mysqltest2.* TO mysqluser1@localhost; +USE mysqltest1; +CREATE TABLE t1( a INT ); +CREATE TABLE t2( a INT, b INT ); +CREATE FUNCTION f1() RETURNS INT RETURN 1; +CREATE VIEW v1 AS SELECT 1 AS a; +CREATE VIEW v2 AS SELECT 1 AS a, 2 AS b; +GRANT SELECT ON TABLE t1 TO mysqluser1@localhost; +GRANT SELECT (a, b) ON TABLE t2 TO mysqluser1@localhost; +GRANT EXECUTE ON FUNCTION f1 TO mysqluser1@localhost; +GRANT SELECT ON TABLE v1 TO mysqluser1@localhost; +GRANT SELECT (a, b) ON TABLE v2 TO mysqluser1@localhost; +CREATE VIEW v_t1 AS SELECT * FROM t1; +CREATE VIEW v_t2 AS SELECT * FROM t2; +CREATE VIEW v_f1 AS SELECT f1() AS a; +CREATE VIEW v_v1 AS SELECT * FROM v1; +CREATE VIEW v_v2 AS SELECT * FROM v2; +GRANT SELECT, SHOW VIEW ON v_t1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_t2 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_f1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_v1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_v2 TO mysqluser1@localhost; +CREATE VIEW v_mysqluser1_t1 AS SELECT * FROM mysqltest1.t1; +CREATE VIEW v_mysqluser1_t2 AS SELECT * FROM mysqltest1.t2; +CREATE VIEW v_mysqluser1_f1 AS SELECT mysqltest1.f1() AS a; +CREATE VIEW v_mysqluser1_v1 AS SELECT * FROM mysqltest1.v1; +CREATE VIEW v_mysqluser1_v2 AS SELECT * FROM mysqltest1.v2; +SHOW CREATE VIEW mysqltest1.v_t1; +View Create View character_set_client collation_connection +v_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_t2; +View Create View character_set_client collation_connection +v_t2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t2` AS select `mysqltest1`.`t2`.`a` AS `a`,`mysqltest1`.`t2`.`b` AS `b` from `mysqltest1`.`t2` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_f1; +View Create View character_set_client collation_connection +v_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_f1` AS select `f1`() AS `a` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_v1; +View Create View character_set_client collation_connection +v_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_v2; +View Create View character_set_client collation_connection +v_v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v2` AS select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `mysqltest1`.`v2` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_t1; +View Create View character_set_client collation_connection +v_mysqluser1_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_t2; +View Create View character_set_client collation_connection +v_mysqluser1_t2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t2` AS select `mysqltest1`.`t2`.`a` AS `a`,`mysqltest1`.`t2`.`b` AS `b` from `mysqltest1`.`t2` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_f1; +View Create View character_set_client collation_connection +v_mysqluser1_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_f1` AS select `mysqltest1`.`f1`() AS `a` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_v1; +View Create View character_set_client collation_connection +v_mysqluser1_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_v2; +View Create View character_set_client collation_connection +v_mysqluser1_v2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v2` AS select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `mysqltest1`.`v2` latin1 latin1_swedish_ci +REVOKE SELECT ON TABLE t1 FROM mysqluser1@localhost; +REVOKE SELECT (a) ON TABLE t2 FROM mysqluser1@localhost; +REVOKE EXECUTE ON FUNCTION f1 FROM mysqluser1@localhost; +REVOKE SELECT ON TABLE v1 FROM mysqluser1@localhost; +SHOW CREATE VIEW mysqltest1.v_t1; +View Create View character_set_client collation_connection +v_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_t2; +View Create View character_set_client collation_connection +v_t2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t2` AS select `mysqltest1`.`t2`.`a` AS `a`,`mysqltest1`.`t2`.`b` AS `b` from `mysqltest1`.`t2` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_f1; +View Create View character_set_client collation_connection +v_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_f1` AS select `f1`() AS `a` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_v1; +View Create View character_set_client collation_connection +v_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +SHOW CREATE VIEW mysqltest1.v_v2; +View Create View character_set_client collation_connection +v_v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v2` AS select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `mysqltest1`.`v2` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_t1; +View Create View character_set_client collation_connection +v_mysqluser1_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_t2; +View Create View character_set_client collation_connection +v_mysqluser1_t2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t2` AS select `mysqltest1`.`t2`.`a` AS `a`,`mysqltest1`.`t2`.`b` AS `b` from `mysqltest1`.`t2` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_f1; +View Create View character_set_client collation_connection +v_mysqluser1_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_f1` AS select `mysqltest1`.`f1`() AS `a` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_v1; +View Create View character_set_client collation_connection +v_mysqluser1_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +SHOW CREATE VIEW v_mysqluser1_v2; +View Create View character_set_client collation_connection +v_mysqluser1_v2 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v2` AS select `v2`.`a` AS `a`,`v2`.`b` AS `b` from `mysqltest1`.`v2` latin1 latin1_swedish_ci +# Testing the case when the views reference missing objects. +# Obviously, there are no privileges to check for, so we +# need only each object type once. +DROP TABLE t1; +DROP FUNCTION f1; +DROP VIEW v1; +SHOW CREATE VIEW mysqltest1.v_t1; +View Create View character_set_client collation_connection +v_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest1.v_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW mysqltest1.v_f1; +View Create View character_set_client collation_connection +v_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_f1` AS select `f1`() AS `a` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest1.v_f1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW mysqltest1.v_v1; +View Create View character_set_client collation_connection +v_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest1`.`v_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest1.v_v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_t1; +View Create View character_set_client collation_connection +v_mysqluser1_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_f1; +View Create View character_set_client collation_connection +v_mysqluser1_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_f1` AS select `mysqltest1`.`f1`() AS `a` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_f1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_v1; +View Create View character_set_client collation_connection +v_mysqluser1_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +REVOKE SHOW VIEW ON v_t1 FROM mysqluser1@localhost; +REVOKE SHOW VIEW ON v_f1 FROM mysqluser1@localhost; +REVOKE SHOW VIEW ON v_v1 FROM mysqluser1@localhost; +SHOW CREATE VIEW mysqltest1.v_t1; +ERROR 42000: SHOW VIEW command denied to user 'mysqluser1'@'localhost' for table 'v_t1' +SHOW CREATE VIEW mysqltest1.v_f1; +ERROR 42000: SHOW VIEW command denied to user 'mysqluser1'@'localhost' for table 'v_f1' +SHOW CREATE VIEW mysqltest1.v_v1; +ERROR 42000: SHOW VIEW command denied to user 'mysqluser1'@'localhost' for table 'v_v1' +SHOW CREATE VIEW v_mysqluser1_t1; +View Create View character_set_client collation_connection +v_mysqluser1_t1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_t1` AS select `mysqltest1`.`t1`.`a` AS `a` from `mysqltest1`.`t1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_t1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_f1; +View Create View character_set_client collation_connection +v_mysqluser1_f1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_f1` AS select `mysqltest1`.`f1`() AS `a` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_f1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +SHOW CREATE VIEW v_mysqluser1_v1; +View Create View character_set_client collation_connection +v_mysqluser1_v1 CREATE ALGORITHM=UNDEFINED DEFINER=`mysqluser1`@`localhost` SQL SECURITY DEFINER VIEW `v_mysqluser1_v1` AS select `v1`.`a` AS `a` from `mysqltest1`.`v1` latin1 latin1_swedish_ci +Warnings: +Warning 1356 View 'mysqltest2.v_mysqluser1_v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +DROP USER mysqluser1@localhost; +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; +USE test; +CREATE TABLE t1( a INT ); +CREATE DEFINER = no_such_user@no_such_host VIEW v1 AS SELECT * FROM t1; +Warnings: +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci +Warnings: +Note 1449 The user specified as a definer ('no_such_user'@'no_such_host') does not exist +DROP TABLE t1; +DROP VIEW v1; diff --git a/mysql-test/std_data/binlog_transaction.000001 b/mysql-test/std_data/binlog_transaction.000001 Binary files differnew file mode 100644 index 00000000000..c1d0745d57c --- /dev/null +++ b/mysql-test/std_data/binlog_transaction.000001 diff --git a/mysql-test/suite/binlog/r/binlog_killed_simulate.result b/mysql-test/suite/binlog/r/binlog_killed_simulate.result index 634d3f62814..df04f5129cf 100644 --- a/mysql-test/suite/binlog/r/binlog_killed_simulate.result +++ b/mysql-test/suite/binlog/r/binlog_killed_simulate.result @@ -19,7 +19,7 @@ ERROR 70100: Query execution was interrupted show binlog events from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# -master-bin.000001 # Execute_load_query # # use `test`; load data infile '../../std_data/rpl_loaddata.dat' into table t2 /* will be "killed" in the middle */ ;file_id=# +master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t2` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a, b) ;file_id=# select (@a:=load_file("MYSQLTEST_VARDIR/tmp/binlog_killed_bug27571.binlog")) is not null; diff --git a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result index 9ae5121f618..2d11fec5787 100644 --- a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result @@ -917,7 +917,7 @@ master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# master-bin.000001 # Intvar # # INSERT_ID=10 master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci -master-bin.000001 # Execute_load_query # # use `test`; load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2) ;file_id=# +master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t4` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a, @b) SET b=((@b) + `bug27417`(2)) ;file_id=# master-bin.000001 # Query # # ROLLBACK drop trigger trg_del_t2; drop table t1,t2,t3,t4,t5; diff --git a/mysql-test/suite/binlog/r/binlog_stm_blackhole.result b/mysql-test/suite/binlog/r/binlog_stm_blackhole.result index f3a01f66fc2..434c1b0896f 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_blackhole.result +++ b/mysql-test/suite/binlog/r/binlog_stm_blackhole.result @@ -127,7 +127,7 @@ master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; create table t2 (a varchar(200)) engine=blackhole master-bin.000001 # Query # # BEGIN master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=581 -master-bin.000001 # Execute_load_query # # use `test`; load data infile '../../std_data/words.dat' into table t2 ;file_id=# +master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE `t2` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a) ;file_id=# master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # use `test`; alter table t1 add b int master-bin.000001 # Query # # use `test`; alter table t1 drop b diff --git a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result index c15374dc1c6..06c57fba2e7 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result +++ b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result @@ -625,7 +625,7 @@ master-bin.000001 # Query # # BEGIN master-bin.000001 # Intvar # # INSERT_ID=10 master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# master-bin.000001 # Intvar # # INSERT_ID=10 -master-bin.000001 # Execute_load_query # # use `test`; load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2) ;file_id=# +master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t4` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a, @b) SET b=((@b) + `bug27417`(2)) ;file_id=# master-bin.000001 # Query # # ROLLBACK /* the output must denote there is the query */; drop trigger trg_del_t2; @@ -863,7 +863,7 @@ master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# master-bin.000001 # Intvar # # INSERT_ID=10 master-bin.000001 # User var # # @`b`=_latin1 0x3135 COLLATE latin1_swedish_ci -master-bin.000001 # Execute_load_query # # use `test`; load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2) ;file_id=# +master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t4` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a, @b) SET b=((@b) + `bug27417`(2)) ;file_id=# master-bin.000001 # Query # # ROLLBACK drop trigger trg_del_t2; drop table t1,t2,t3,t4,t5; diff --git a/mysql-test/suite/binlog/t/binlog_row_mysqlbinlog_db_filter.test b/mysql-test/suite/binlog/t/binlog_row_mysqlbinlog_db_filter.test index 0422c204270..6682d84d9c9 100644 --- a/mysql-test/suite/binlog/t/binlog_row_mysqlbinlog_db_filter.test +++ b/mysql-test/suite/binlog/t/binlog_row_mysqlbinlog_db_filter.test @@ -112,17 +112,22 @@ while($i) # remove unecessary files -- remove_file $outfile.1 -- remove_file $outfile.2 - + + # + # The two tests are canceled since we introduced the patch of bug#46998, + # which will make mydsqlbinlog output the 'BEGIN', 'COMMIT' and 'ROLLBACK' + # in regardless of database filtering + # # assertion: events for database test are filtered - if (`SELECT INSTR(@b42941_output.1, 'test')`) - { - -- echo **** ERROR **** Database name 'test' FOUND in mysqlbinlog output ($flags $outfile.1). - } - - if (`SELECT INSTR(@b42941_output.2, 'test')`) - { - -- echo **** ERROR **** Database name 'test' FOUND in mysqlbinlog output ($flags $outfile.2). - } + #if (`SELECT INSTR(@b42941_output.1, 'test')`) + #{ + #-- echo **** ERROR **** Database name 'test' FOUND in mysqlbinlog output ($flags $outfile.1). + #} + + #if (`SELECT INSTR(@b42941_output.2, 'test')`) + #{ + #-- echo **** ERROR **** Database name 'test' FOUND in mysqlbinlog output ($flags $outfile.2). + #} # assertion: events for database b42941 are not filtered if (!`SELECT INSTR(@b42941_output.1, 'b42941')`) diff --git a/mysql-test/suite/federated/federated_debug-master.opt b/mysql-test/suite/federated/federated_debug-master.opt new file mode 100644 index 00000000000..ad9ba4795af --- /dev/null +++ b/mysql-test/suite/federated/federated_debug-master.opt @@ -0,0 +1 @@ +--loose-debug=d,simulate_detached_thread_refresh diff --git a/mysql-test/suite/federated/federated_debug.result b/mysql-test/suite/federated/federated_debug.result new file mode 100644 index 00000000000..81a2558d425 --- /dev/null +++ b/mysql-test/suite/federated/federated_debug.result @@ -0,0 +1,28 @@ +CREATE DATABASE federated; +CREATE DATABASE federated; +# +# Bug#47525: MySQL crashed (Federated) +# +# Switch to slave +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +# Switch to master +CREATE TABLE t1(a INT) ENGINE=FEDERATED +CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/test/t1'; +SELECT * FROM t1; +a +1 +# Start a asynchronous reload +# Wait for tables to be closed +# Ensure that the server didn't crash +SELECT * FROM t1; +a +1 +# Drop tables on master and slave +DROP TABLE t1; +DROP TABLE t1; +# Federated cleanup +DROP TABLE IF EXISTS federated.t1; +DROP DATABASE federated; +DROP TABLE IF EXISTS federated.t1; +DROP DATABASE federated; diff --git a/mysql-test/suite/federated/federated_debug.test b/mysql-test/suite/federated/federated_debug.test new file mode 100644 index 00000000000..4152d2975b3 --- /dev/null +++ b/mysql-test/suite/federated/federated_debug.test @@ -0,0 +1,39 @@ +--source include/have_debug.inc +--source federated.inc + +--echo # +--echo # Bug#47525: MySQL crashed (Federated) +--echo # + +connection slave; +--echo # Switch to slave +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); + +connection master; +--echo # Switch to master +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval CREATE TABLE t1(a INT) ENGINE=FEDERATED + CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/test/t1'; + +SELECT * FROM t1; + +--echo # Start a asynchronous reload +--exec $MYSQLADMIN --no-defaults -S $MASTER_MYSOCK -P $MASTER_MYPORT -u root --password= refresh 2>&1 + +--echo # Wait for tables to be closed +let $show_statement= SHOW STATUS LIKE 'Open_tables'; +let $field= Value; +let $condition= = '0'; +--source include/wait_show_condition.inc + +--echo # Ensure that the server didn't crash +SELECT * FROM t1; +--echo # Drop tables on master and slave +DROP TABLE t1; +connection slave; +DROP TABLE t1; + +connection default; +--echo # Federated cleanup +source federated_cleanup.inc; diff --git a/mysql-test/suite/federated/my.cnf b/mysql-test/suite/federated/my.cnf index 3e84845b945..82600949712 100644 --- a/mysql-test/suite/federated/my.cnf +++ b/mysql-test/suite/federated/my.cnf @@ -9,4 +9,7 @@ log-bin= master-bin [ENV] MASTER_MYPORT= @mysqld.1.port +MASTER_MYSOCK= @mysqld.1.socket + SLAVE_MYPORT= @mysqld.2.port +SLAVE_MYSOCK= @mysqld.2.socket diff --git a/mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result b/mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result new file mode 100644 index 00000000000..b2cc92491c3 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_auto_increment_update_failure.result @@ -0,0 +1,1041 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +# Test case1: INVOKES A TRIGGER with after insert action +create table t1(a int, b int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create trigger tr1 after insert on t1 for each row insert into t2(a) values(6); +create table t3(a int, b int) engine=innodb; +create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t5(a int) engine=innodb; +create trigger tr2 after insert on t3 for each row begin +insert into t4(a) values(f1_insert_triggered()); +insert into t4(a) values(f1_insert_triggered()); +insert into t5(a) values(8); +end | +create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER +BEGIN +INSERT INTO t6(a) values(2),(3); +RETURN 1; +END// +begin; +insert into t1(a,b) values(1,1),(2,1); +insert into t3(a,b) values(1,1),(2,1); +update t1 set a = a + 5 where b = 1; +update t3 set a = a + 5 where b = 1; +delete from t1 where b = 1; +delete from t3 where b = 1; +insert into t2(a) values(3); +insert into t4(a) values(3); +commit; +insert into t1(a,b) values(4,2); +insert into t3(a,b) values(4,2); +update t1 set a = a + 5 where b = 2; +update t3 set a = a + 5 where b = 2; +delete from t1 where b = 2; +delete from t3 where b = 2; +# To verify if insert/update in an autoinc column causes statement to be logged in row format +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1 +master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1 +master-bin.000001 # Query # # use `test`; delete from t1 where b = 1 +master-bin.000001 # Query # # use `test`; delete from t3 where b = 1 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; delete from t1 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; delete from t3 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +commit; +#Test if the results are consistent on master and slave +#for 'INVOKES A TRIGGER with after insert action' +Comparing tables master:test.t2 and slave:test.t2 +Comparing tables master:test.t4 and slave:test.t4 +Comparing tables master:test.t6 and slave:test.t6 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +DROP TABLE t5; +DROP TABLE t6; +DROP FUNCTION f1_insert_triggered; +# Test case2: INVOKES A TRIGGER with before insert action +create table t1(a int, b int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create trigger tr1 before insert on t1 for each row insert into t2(a) values(6); +create table t3(a int, b int) engine=innodb; +create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t5(a int) engine=innodb; +create trigger tr2 before insert on t3 for each row begin +insert into t4(a) values(f1_insert_triggered()); +insert into t4(a) values(f1_insert_triggered()); +insert into t5(a) values(8); +end | +create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER +BEGIN +INSERT INTO t6(a) values(2),(3); +RETURN 1; +END// +begin; +insert into t1(a,b) values(1,1),(2,1); +insert into t3(a,b) values(1,1),(2,1); +update t1 set a = a + 5 where b = 1; +update t3 set a = a + 5 where b = 1; +delete from t1 where b = 1; +delete from t3 where b = 1; +insert into t2(a) values(3); +insert into t4(a) values(3); +commit; +insert into t1(a,b) values(4,2); +insert into t3(a,b) values(4,2); +update t1 set a = a + 5 where b = 2; +update t3 set a = a + 5 where b = 2; +delete from t1 where b = 2; +delete from t3 where b = 2; +# To verify if insert/update in an autoinc column causes statement to be logged in row format +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1 +master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1 +master-bin.000001 # Query # # use `test`; delete from t1 where b = 1 +master-bin.000001 # Query # # use `test`; delete from t3 where b = 1 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; delete from t1 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; delete from t3 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +commit; +#Test if the results are consistent on master and slave +#for 'INVOKES A TRIGGER with before insert action' +Comparing tables master:test.t2 and slave:test.t2 +Comparing tables master:test.t4 and slave:test.t4 +Comparing tables master:test.t6 and slave:test.t6 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +DROP TABLE t5; +DROP TABLE t6; +DROP FUNCTION f1_insert_triggered; +# Test case3: INVOKES A TRIGGER with after update action +create table t1(a int, b int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create trigger tr1 after update on t1 for each row insert into t2(a) values(6); +create table t3(a int, b int) engine=innodb; +create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t5(a int) engine=innodb; +create trigger tr2 after update on t3 for each row begin +insert into t4(a) values(f1_insert_triggered()); +insert into t4(a) values(f1_insert_triggered()); +insert into t5(a) values(8); +end | +create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER +BEGIN +INSERT INTO t6(a) values(2),(3); +RETURN 1; +END// +begin; +insert into t1(a,b) values(1,1),(2,1); +insert into t3(a,b) values(1,1),(2,1); +update t1 set a = a + 5 where b = 1; +update t3 set a = a + 5 where b = 1; +delete from t1 where b = 1; +delete from t3 where b = 1; +insert into t2(a) values(3); +insert into t4(a) values(3); +commit; +insert into t1(a,b) values(4,2); +insert into t3(a,b) values(4,2); +update t1 set a = a + 5 where b = 2; +update t3 set a = a + 5 where b = 2; +delete from t1 where b = 2; +delete from t3 where b = 2; +# To verify if insert/update in an autoinc column causes statement to be logged in row format +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1) +master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Update_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Update_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Update_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Update_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; delete from t1 where b = 1 +master-bin.000001 # Query # # use `test`; delete from t3 where b = 1 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Update_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Update_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; delete from t1 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; delete from t3 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +commit; +#Test if the results are consistent on master and slave +#for 'INVOKES A TRIGGER with after update action' +Comparing tables master:test.t2 and slave:test.t2 +Comparing tables master:test.t4 and slave:test.t4 +Comparing tables master:test.t6 and slave:test.t6 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +DROP TABLE t5; +DROP TABLE t6; +DROP FUNCTION f1_insert_triggered; +# Test case4: INVOKES A TRIGGER with before update action +create table t1(a int, b int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create trigger tr1 before update on t1 for each row insert into t2(a) values(6); +create table t3(a int, b int) engine=innodb; +create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t5(a int) engine=innodb; +create trigger tr2 before update on t3 for each row begin +insert into t4(a) values(f1_insert_triggered()); +insert into t4(a) values(f1_insert_triggered()); +insert into t5(a) values(8); +end | +create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER +BEGIN +INSERT INTO t6(a) values(2),(3); +RETURN 1; +END// +begin; +insert into t1(a,b) values(1,1),(2,1); +insert into t3(a,b) values(1,1),(2,1); +update t1 set a = a + 5 where b = 1; +update t3 set a = a + 5 where b = 1; +delete from t1 where b = 1; +delete from t3 where b = 1; +insert into t2(a) values(3); +insert into t4(a) values(3); +commit; +insert into t1(a,b) values(4,2); +insert into t3(a,b) values(4,2); +update t1 set a = a + 5 where b = 2; +update t3 set a = a + 5 where b = 2; +delete from t1 where b = 2; +delete from t3 where b = 2; +# To verify if insert/update in an autoinc column causes statement to be logged in row format +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1) +master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1) +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Update_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Update_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # use `test`; delete from t1 where b = 1 +master-bin.000001 # Query # # use `test`; delete from t3 where b = 1 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Update_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; delete from t1 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; delete from t3 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +commit; +#Test if the results are consistent on master and slave +#for 'INVOKES A TRIGGER with before update action' +Comparing tables master:test.t2 and slave:test.t2 +Comparing tables master:test.t4 and slave:test.t4 +Comparing tables master:test.t6 and slave:test.t6 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +DROP TABLE t5; +DROP TABLE t6; +DROP FUNCTION f1_insert_triggered; +# Test case5: INVOKES A TRIGGER with after delete action +create table t1(a int, b int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create trigger tr1 after delete on t1 for each row insert into t2(a) values(6); +create table t3(a int, b int) engine=innodb; +create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t5(a int) engine=innodb; +create trigger tr2 after delete on t3 for each row begin +insert into t4(a) values(f1_insert_triggered()); +insert into t4(a) values(f1_insert_triggered()); +insert into t5(a) values(8); +end | +create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER +BEGIN +INSERT INTO t6(a) values(2),(3); +RETURN 1; +END// +begin; +insert into t1(a,b) values(1,1),(2,1); +insert into t3(a,b) values(1,1),(2,1); +update t1 set a = a + 5 where b = 1; +update t3 set a = a + 5 where b = 1; +delete from t1 where b = 1; +delete from t3 where b = 1; +insert into t2(a) values(3); +insert into t4(a) values(3); +commit; +insert into t1(a,b) values(4,2); +insert into t3(a,b) values(4,2); +update t1 set a = a + 5 where b = 2; +update t3 set a = a + 5 where b = 2; +delete from t1 where b = 2; +delete from t3 where b = 2; +# To verify if insert/update in an autoinc column causes statement to be logged in row format +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1) +master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1) +master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1 +master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1 +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Delete_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Delete_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Delete_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Delete_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Delete_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Delete_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +commit; +#Test if the results are consistent on master and slave +#for 'INVOKES A TRIGGER with after delete action' +Comparing tables master:test.t2 and slave:test.t2 +Comparing tables master:test.t4 and slave:test.t4 +Comparing tables master:test.t6 and slave:test.t6 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +DROP TABLE t5; +DROP TABLE t6; +DROP FUNCTION f1_insert_triggered; +# Test case6: INVOKES A TRIGGER with before delete action +create table t1(a int, b int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create trigger tr1 before delete on t1 for each row insert into t2(a) values(6); +create table t3(a int, b int) engine=innodb; +create table t4(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t5(a int) engine=innodb; +create trigger tr2 before delete on t3 for each row begin +insert into t4(a) values(f1_insert_triggered()); +insert into t4(a) values(f1_insert_triggered()); +insert into t5(a) values(8); +end | +create table t6(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_insert_triggered() RETURNS INTEGER +BEGIN +INSERT INTO t6(a) values(2),(3); +RETURN 1; +END// +begin; +insert into t1(a,b) values(1,1),(2,1); +insert into t3(a,b) values(1,1),(2,1); +update t1 set a = a + 5 where b = 1; +update t3 set a = a + 5 where b = 1; +delete from t1 where b = 1; +delete from t3 where b = 1; +insert into t2(a) values(3); +insert into t4(a) values(3); +commit; +insert into t1(a,b) values(4,2); +insert into t3(a,b) values(4,2); +update t1 set a = a + 5 where b = 2; +update t3 set a = a + 5 where b = 2; +delete from t1 where b = 2; +delete from t3 where b = 2; +# To verify if insert/update in an autoinc column causes statement to be logged in row format +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t2(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; insert into t4(a) values(3) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,1) +master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(1,1),(2,1) +master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 1 +master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 1 +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Delete_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Delete_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(4,2) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; insert into t3(a,b) values(4,2) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; update t1 set a = a + 5 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Query # # use `test`; update t3 set a = a + 5 where b = 2 +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Table_map # # table_id: # (test.t5) +master-bin.000001 # Table_map # # table_id: # (test.t4) +master-bin.000001 # Table_map # # table_id: # (test.t6) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +commit; +#Test if the results are consistent on master and slave +#for 'INVOKES A TRIGGER with before delete action' +Comparing tables master:test.t2 and slave:test.t2 +Comparing tables master:test.t4 and slave:test.t4 +Comparing tables master:test.t6 and slave:test.t6 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +DROP TABLE t5; +DROP TABLE t6; +DROP FUNCTION f1_insert_triggered; +# Test case7: CALLS A FUNCTION which INVOKES A TRIGGER with after insert action +create table t1(a int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_two_inserts_trigger() RETURNS INTEGER +BEGIN +INSERT INTO t2(a) values(2),(3); +INSERT INTO t2(a) values(2),(3); +RETURN 1; +END | +create trigger tr11 after insert on t2 for each row begin +insert into t3(a) values(new.a); +insert into t3(a) values(new.a); +end | +begin; +insert into t1(a) values(f1_two_inserts_trigger()); +insert into t2(a) values(4),(5); +commit; +insert into t1(a) values(f1_two_inserts_trigger()); +# To verify if insert/update in an autoinc column causes statement to be logged in row format +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +commit; +#Test if the results are consistent on master and slave +#for 'CALLS A FUNCTION which INVOKES A TRIGGER with after insert action' +Comparing tables master:test.t2 and slave:test.t2 +Comparing tables master:test.t3 and slave:test.t3 +drop table t1; +drop table t2; +drop table t3; +drop function f1_two_inserts_trigger; +# Test case8: CALLS A FUNCTION which INVOKES A TRIGGER with before insert action +create table t1(a int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create table t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_two_inserts_trigger() RETURNS INTEGER +BEGIN +INSERT INTO t2(a) values(2),(3); +INSERT INTO t2(a) values(2),(3); +RETURN 1; +END | +create trigger tr11 before insert on t2 for each row begin +insert into t3(a) values(new.a); +insert into t3(a) values(new.a); +end | +begin; +insert into t1(a) values(f1_two_inserts_trigger()); +insert into t2(a) values(4),(5); +commit; +insert into t1(a) values(f1_two_inserts_trigger()); +# To verify if insert/update in an autoinc column causes statement to be logged in row format +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Table_map # # table_id: # (test.t2) +master-bin.000001 # Table_map # # table_id: # (test.t3) +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F +master-bin.000001 # Xid # # COMMIT /* XID */ +commit; +#Test if the results are consistent on master and slave +#for 'CALLS A FUNCTION which INVOKES A TRIGGER with before insert action' +Comparing tables master:test.t2 and slave:test.t2 +Comparing tables master:test.t3 and slave:test.t3 +drop table t1; +drop table t2; +drop table t3; +drop function f1_two_inserts_trigger; +# Test case9: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with after insert action +CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb; +CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb; +CREATE TABLE t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create trigger tr16 after insert on t1 for each row insert into t3(a) values(new.c1); +create trigger tr17 after insert on t2 for each row insert into t3(a) values(new.c2); +begin; +INSERT INTO t1(c1) VALUES (11), (12); +INSERT INTO t2(c2) VALUES (13), (14); +CREATE VIEW v16 AS SELECT c1, c2 FROM t1, t2; +INSERT INTO v16(c1) VALUES (15),(16); +INSERT INTO v16(c2) VALUES (17),(18); +INSERT INTO v16(c1) VALUES (19),(20); +INSERT INTO v16(c2) VALUES (21),(22); +INSERT INTO v16(c1) VALUES (23), (24); +INSERT INTO v16(c1) VALUES (25), (26); +commit; +#Test if the results are consistent on master and slave +#for 'INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS' +Comparing tables master:test.t3 and slave:test.t3 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP VIEW v16; +# Test case10: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with before insert action +CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb; +CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb; +CREATE TABLE t3(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +create trigger tr16 before insert on t1 for each row insert into t3(a) values(new.c1); +create trigger tr17 before insert on t2 for each row insert into t3(a) values(new.c2); +begin; +INSERT INTO t1(c1) VALUES (11), (12); +INSERT INTO t2(c2) VALUES (13), (14); +CREATE VIEW v16 AS SELECT c1, c2 FROM t1, t2; +INSERT INTO v16(c1) VALUES (15),(16); +INSERT INTO v16(c2) VALUES (17),(18); +INSERT INTO v16(c1) VALUES (19),(20); +INSERT INTO v16(c2) VALUES (21),(22); +INSERT INTO v16(c1) VALUES (23), (24); +INSERT INTO v16(c1) VALUES (25), (26); +commit; +#Test if the results are consistent on master and slave +#for 'INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS' +Comparing tables master:test.t3 and slave:test.t3 +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP VIEW v16; +# Test case11: INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES INTO A TABLE WITH AUTOINC COLUMN +create table t1(a int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_two_inserts() RETURNS INTEGER +BEGIN +INSERT INTO t2(a) values(2); +INSERT INTO t2(a) values(2); +RETURN 1; +END// +begin; +insert into t1(a) values(f1_two_inserts()); +insert into t2(a) values(4),(5); +commit; +insert into t1(a) values(f1_two_inserts()); +commit; +#Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on master +select * from t2 ORDER BY i1; +i1 a +1 2 +2 2 +3 4 +4 5 +5 2 +6 2 +#Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on slave +select * from t2 ORDER BY i1; +i1 a +1 2 +2 2 +3 4 +4 5 +5 2 +6 2 +drop table t1; +drop table t2; +drop function f1_two_inserts; +# Test case12: INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES OF A TABLE WITH AUTOINC COLUMN +create table t1(a int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; +CREATE FUNCTION f1_two_updates() RETURNS INTEGER +BEGIN +update t2 set a = a + 5 where b = 1; +update t2 set a = a + 5 where b = 2; +update t2 set a = a + 5 where b = 3; +update t2 set a = a + 5 where b = 4; +RETURN 1; +END// +insert into t2(a,b) values(1,1); +insert into t2(a,b) values(2,2); +insert into t2(a,b) values(3,3); +insert into t2(a,b) values(4,4); +insert into t1(a) values(f1_two_updates()); +begin; +insert into t1(a) values(f1_two_updates()); +commit; +#Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on master +select * from t2 ORDER BY i1; +i1 a b +1 11 1 +2 12 2 +3 13 3 +4 14 4 +#Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on slave +select * from t2 ORDER BY i1; +i1 a b +1 11 1 +2 12 2 +3 13 3 +4 14 4 +drop table t1; +drop table t2; +drop function f1_two_updates; +# Test case13: UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT +create table t1(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; +create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; +begin; +insert into t1(a,b) values(1,1),(2,2); +insert into t2(a,b) values(1,1),(2,2); +update t1,t2 set t1.a=t1.a+5, t2.a=t2.a+5 where t1.b=t2.b; +insert into t1(a,b) values(3,3); +insert into t2(a,b) values(3,3); +commit; +# To verify if it works fine when these statements are not be marked as unsafe +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=1 +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(1,1),(2,2) +master-bin.000001 # Intvar # # INSERT_ID=1 +master-bin.000001 # Query # # use `test`; insert into t2(a,b) values(1,1),(2,2) +master-bin.000001 # Query # # use `test`; update t1,t2 set t1.a=t1.a+5, t2.a=t2.a+5 where t1.b=t2.b +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t1(a,b) values(3,3) +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; insert into t2(a,b) values(3,3) +master-bin.000001 # Xid # # COMMIT /* XID */ +#Test if the results are consistent on master and slave +#for 'UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT' +Comparing tables master:test.t1 and slave:test.t1 +Comparing tables master:test.t2 and slave:test.t2 +drop table t1; +drop table t2; +# Test case14: INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES +CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb; +CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb; +begin; +INSERT INTO t1(c1) VALUES (11), (12); +INSERT INTO t2(c2) VALUES (13), (14); +CREATE VIEW v15 AS SELECT c1, c2 FROM t1, t2; +INSERT INTO v15(c1) VALUES (15),(16); +INSERT INTO v15(c2) VALUES (17),(18); +INSERT INTO v15(c1) VALUES (19),(20); +INSERT INTO v15(c2) VALUES (21),(22); +INSERT INTO v15(c1) VALUES (23), (24); +INSERT INTO v15(c2) VALUES (25), (26); +commit; +# To verify if it works fine when these statements are not be marked as unsafe +show binlog events from <binlog_start>; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=1 +master-bin.000001 # Query # # use `test`; INSERT INTO t1(c1) VALUES (11), (12) +master-bin.000001 # Intvar # # INSERT_ID=1 +master-bin.000001 # Query # # use `test`; INSERT INTO t2(c2) VALUES (13), (14) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v15` AS SELECT c1, c2 FROM t1, t2 +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; INSERT INTO v15(c1) VALUES (15),(16) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=3 +master-bin.000001 # Query # # use `test`; INSERT INTO v15(c2) VALUES (17),(18) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; INSERT INTO v15(c1) VALUES (19),(20) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=5 +master-bin.000001 # Query # # use `test`; INSERT INTO v15(c2) VALUES (21),(22) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=7 +master-bin.000001 # Query # # use `test`; INSERT INTO v15(c1) VALUES (23), (24) +master-bin.000001 # Xid # # COMMIT /* XID */ +master-bin.000001 # Query # # BEGIN +master-bin.000001 # Intvar # # INSERT_ID=7 +master-bin.000001 # Query # # use `test`; INSERT INTO v15(c2) VALUES (25), (26) +master-bin.000001 # Xid # # COMMIT /* XID */ +#Test if the results are consistent on master and slave +#for 'INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES' +Comparing tables master:test.t1 and slave:test.t1 +Comparing tables master:test.t2 and slave:test.t2 +drop table t1; +drop table t2; +drop view v15; diff --git a/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result b/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result index 81c486cb43c..3a1c2b68b01 100644 --- a/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result +++ b/mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result @@ -885,7 +885,7 @@ master-bin.000001 # Query 1 # use `test_rpl`; DELETE FROM t2 master-bin.000001 # Xid 1 # # master-bin.000001 # Query 1 # BEGIN master-bin.000001 # Begin_load_query 1 # ;file_id=#;block_len=# -master-bin.000001 # Execute_load_query 1 # use `test_rpl`; LOAD DATA INFILE 'MYSQLTEST_VARDIR/std_data/rpl_mixed.dat' INTO TABLE t1 FIELDS TERMINATED BY '|' ;file_id=# +master-bin.000001 # Execute_load_query 1 # use `test_rpl`; LOAD DATA INFILE 'MYSQLTEST_VARDIR/std_data/rpl_mixed.dat' INTO TABLE `t1` FIELDS TERMINATED BY '|' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (a, b) ;file_id=# master-bin.000001 # Xid 1 # # master-bin.000001 # Query 1 # BEGIN master-bin.000001 # Query 1 # use `test_rpl`; DELETE FROM t1 diff --git a/mysql-test/suite/rpl/r/rpl_loaddata.result b/mysql-test/suite/rpl/r/rpl_loaddata.result index d7a02bc84a5..0653936f0ec 100644 --- a/mysql-test/suite/rpl/r/rpl_loaddata.result +++ b/mysql-test/suite/rpl/r/rpl_loaddata.result @@ -36,7 +36,7 @@ set global sql_slave_skip_counter=1; start slave; show slave status; Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error -# 127.0.0.1 root MASTER_PORT 1 master-bin.000001 1797 # # master-bin.000001 Yes Yes # 0 0 1797 # None 0 No # No 0 0 +# 127.0.0.1 root MASTER_PORT 1 master-bin.000001 2009 # # master-bin.000001 Yes Yes # 0 0 2009 # None 0 No # No 0 0 set sql_log_bin=0; delete from t1; set sql_log_bin=1; @@ -46,7 +46,7 @@ change master to master_user='test'; change master to master_user='root'; show slave status; Slave_IO_State Master_Host Master_User Master_Port Connect_Retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno Last_Error Skip_Counter Exec_Master_Log_Pos Relay_Log_Space Until_Condition Until_Log_File Until_Log_Pos Master_SSL_Allowed Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key Seconds_Behind_Master Master_SSL_Verify_Server_Cert Last_IO_Errno Last_IO_Error Last_SQL_Errno Last_SQL_Error -# 127.0.0.1 root MASTER_PORT 1 master-bin.000001 1832 # # master-bin.000001 No No # 0 0 1832 # None 0 No # No 0 0 +# 127.0.0.1 root MASTER_PORT 1 master-bin.000001 2044 # # master-bin.000001 No No # 0 0 2044 # None 0 No # No 0 0 set global sql_slave_skip_counter=1; start slave; set sql_log_bin=0; diff --git a/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result b/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result index 27fb8623e85..35696615b5a 100644 --- a/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result +++ b/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result @@ -53,7 +53,7 @@ Master_User root Master_Port MASTER_PORT Connect_Retry 1 Master_Log_File master-bin.000001 -Read_Master_Log_Pos 465 +Read_Master_Log_Pos 556 Relay_Log_File # Relay_Log_Pos # Relay_Master_Log_File master-bin.000001 diff --git a/mysql-test/suite/rpl/r/rpl_loaddata_map.result b/mysql-test/suite/rpl/r/rpl_loaddata_map.result index 901f3352b44..006f84043a4 100644 --- a/mysql-test/suite/rpl/r/rpl_loaddata_map.result +++ b/mysql-test/suite/rpl/r/rpl_loaddata_map.result @@ -20,7 +20,7 @@ master-bin.000001 # Query # # use `test`; create table t2 (id int not null prima master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# master-bin.000001 # Append_block # # ;file_id=#;block_len=# master-bin.000001 # Append_block # # ;file_id=#;block_len=# -master-bin.000001 # Execute_load_query # # use `test`; load data infile 'MYSQLTEST_VARDIR/tmp/bug30435_5k.txt' into table t2 ;file_id=# +master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug30435_5k.txt' INTO TABLE `t2` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (id) ;file_id=# ==== Verify results on slave ==== [on slave] select count(*) from t2 /* 5 000 */; diff --git a/mysql-test/suite/rpl/r/rpl_loaddatalocal.result b/mysql-test/suite/rpl/r/rpl_loaddatalocal.result index 93ef33f3fc0..6dccaa3d74c 100644 --- a/mysql-test/suite/rpl/r/rpl_loaddatalocal.result +++ b/mysql-test/suite/rpl/r/rpl_loaddatalocal.result @@ -54,3 +54,31 @@ a [on master] DROP TABLE t1; [on slave] + +Bug #43746: +"return wrong query string when parse 'load data infile' sql statement" + +[master] +SELECT @@SESSION.sql_mode INTO @old_mode; +SET sql_mode='ignore_space'; +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES (1), (2), (3), (4); +SELECT * INTO OUTFILE 'MYSQLD_DATADIR/bug43746.sql' FROM t1; +TRUNCATE TABLE t1; +LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; +LOAD/* look mum, with comments in weird places! */DATA/* oh hai */LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql'/* we are */INTO/* from the internets */TABLE t1; +LOAD DATA/*!10000 LOCAL */INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; +LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' /*!10000 INTO */ TABLE t1; +LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' /*!10000 INTO TABLE */ t1; +LOAD DATA /*!10000 LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO TABLE */ t1; +LOAD DATA/*!10000 LOCAL */INFILE 'MYSQLD_DATADIR/bug43746.sql'/*!10000 INTO*/TABLE t1; +LOAD DATA/*!10000 LOCAL */INFILE 'MYSQLD_DATADIR/bug43746.sql'/* empty */INTO TABLE t1; +LOAD DATA/*!10000 LOCAL */INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO/* empty */TABLE t1; +LOAD/*!99999 special comments that do not expand */DATA/*!99999 code from the future */LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql'/*!99999 have flux capacitor */INTO/*!99999 will travel */TABLE t1; +SET sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER'; +LOAD DATA LOCAL INFILE 'MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; +[slave] +[master] +DROP TABLE t1; +SET SESSION sql_mode=@old_mode; +[slave] diff --git a/mysql-test/suite/rpl/r/rpl_stm_log.result b/mysql-test/suite/rpl/r/rpl_stm_log.result index bcefc6f9d3d..d73b8990041 100644 --- a/mysql-test/suite/rpl/r/rpl_stm_log.result +++ b/mysql-test/suite/rpl/r/rpl_stm_log.result @@ -25,7 +25,7 @@ master-bin.000001 # Query 1 # use `test`; insert into t1 values (NULL) master-bin.000001 # Query 1 # use `test`; drop table t1 master-bin.000001 # Query 1 # use `test`; create table t1 (word char(20) not null)ENGINE=MyISAM master-bin.000001 # Begin_load_query 1 # ;file_id=1;block_len=581 -master-bin.000001 # Execute_load_query 1 # use `test`; load data infile '../../std_data/words.dat' into table t1 ignore 1 lines ;file_id=1 +master-bin.000001 # Execute_load_query 1 # use `test`; LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES (word) ;file_id=1 show binlog events from 106 limit 1; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query 1 # use `test`; create table t1(n int not null auto_increment primary key)ENGINE=MyISAM @@ -193,7 +193,7 @@ master-bin.000001 # Query # # use `test`; insert into t1 values (NULL) master-bin.000001 # Query # # use `test`; drop table t1 master-bin.000001 # Query # # use `test`; create table t1 (word char(20) not null)ENGINE=MyISAM master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=# -master-bin.000001 # Execute_load_query # # use `test`; load data infile '../../std_data/words.dat' into table t1 ignore 1 lines ;file_id=# +master-bin.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES (word) ;file_id=# master-bin.000001 # Rotate # # master-bin.000002;pos=4 show binlog events in 'master-bin.000002'; Log_name Pos Event_type Server_id End_log_pos Info @@ -218,7 +218,7 @@ slave-bin.000001 # Query 1 # use `test`; insert into t1 values (NULL) slave-bin.000001 # Query 1 # use `test`; drop table t1 slave-bin.000001 # Query 1 # use `test`; create table t1 (word char(20) not null)ENGINE=MyISAM slave-bin.000001 # Begin_load_query 1 # ;file_id=1;block_len=581 -slave-bin.000001 # Execute_load_query 1 # use `test`; load data INFILE '../../tmp/SQL_LOAD-2-1-1.data' INTO table t1 ignore 1 lines ;file_id=1 +slave-bin.000001 # Execute_load_query 1 # use `test`; LOAD DATA INFILE '../../tmp/SQL_LOAD-2-1-1.data' INTO TABLE `t1` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES (word) ;file_id=1 slave-bin.000001 # Query 1 # use `test`; create table t3 (a int)ENGINE=MyISAM slave-bin.000001 # Rotate 2 # slave-bin.000002;pos=4 show binlog events in 'slave-bin.000002' from 4; diff --git a/mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test b/mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test new file mode 100644 index 00000000000..f38d2151ab3 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_auto_increment_update_failure.test @@ -0,0 +1,214 @@ +# +# Bug45677 +# This test verifies the following two properties: +# P1) insert/update in an autoinc column causes statement to +# be logged in row format if binlog_format=mixed. +# P2) if binlog_format=mixed, and a trigger or function contains +# two or more inserts/updates in a table that has an autoinc +# column, then the slave should not go out of sync, even if +# there are concurrent transactions. +# +# Property (P1) is tested by executing an insert and an update on +# a table that has an autoinc column, and verifying that these +# statements result in row events in the binlog. +# Property (P2) is tested by setting up the test scenario and +# verifying that the tables are identical on master and slave. +# + +source include/have_binlog_format_mixed.inc; +source include/have_innodb.inc; +source include/master-slave.inc; + +--echo # Test case1: INVOKES A TRIGGER with after insert action +let $trigger_action = after insert; +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test; + +--echo # Test case2: INVOKES A TRIGGER with before insert action +let $trigger_action = before insert; +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test; + +--echo # Test case3: INVOKES A TRIGGER with after update action +let $trigger_action = after update; +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test; + +--echo # Test case4: INVOKES A TRIGGER with before update action +let $trigger_action = before update; +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test; + +--echo # Test case5: INVOKES A TRIGGER with after delete action +let $trigger_action = after delete; +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test; + +--echo # Test case6: INVOKES A TRIGGER with before delete action +let $trigger_action = before delete; +source extra/rpl_tests/rpl_auto_increment_invoke_trigger.test; + +--echo # Test case7: CALLS A FUNCTION which INVOKES A TRIGGER with after insert action +let $insert_action = after insert; +source extra/rpl_tests/rpl_autoinc_func_invokes_trigger.test; + +--echo # Test case8: CALLS A FUNCTION which INVOKES A TRIGGER with before insert action +let $insert_action = before insert; +source extra/rpl_tests/rpl_autoinc_func_invokes_trigger.test; + +--echo # Test case9: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with after insert action +let $insert_action = after insert; +source extra/rpl_tests/rpl_auto_increment_insert_view.test; + +--echo # Test case10: INSERT DATA INTO VIEW WHICH INVOKES TRIGGERS with before insert action +let $insert_action = before insert; +source extra/rpl_tests/rpl_auto_increment_insert_view.test; + +--echo # Test case11: INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES INTO A TABLE WITH AUTOINC COLUMN +connection master; +create table t1(a int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, primary key(i1)) engine=innodb; +delimiter //; +CREATE FUNCTION f1_two_inserts() RETURNS INTEGER +BEGIN + INSERT INTO t2(a) values(2); + INSERT INTO t2(a) values(2); + RETURN 1; +END// +delimiter ;// +begin; +insert into t1(a) values(f1_two_inserts()); + +connection master1; +#The default autocommit is set to 1, so the statement is auto committed +insert into t2(a) values(4),(5); + +connection master; +commit; +insert into t1(a) values(f1_two_inserts()); +commit; + +connection master; +--echo #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on master +select * from t2 ORDER BY i1; + +sync_slave_with_master; +connection slave; +--echo #Test result for INVOKES A FUNCTION TO INSERT TWO OR MORE VALUES on slave +select * from t2 ORDER BY i1; + +connection master; +drop table t1; +drop table t2; +drop function f1_two_inserts; +sync_slave_with_master; + +--echo # Test case12: INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES OF A TABLE WITH AUTOINC COLUMN +connection master; +create table t1(a int) engine=innodb; +create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; +delimiter //; +CREATE FUNCTION f1_two_updates() RETURNS INTEGER +BEGIN + update t2 set a = a + 5 where b = 1; + update t2 set a = a + 5 where b = 2; + update t2 set a = a + 5 where b = 3; + update t2 set a = a + 5 where b = 4; + RETURN 1; +END// +delimiter ;// + +connection master1; +#The default autocommit is set to 1, so the statement is auto committed +insert into t2(a,b) values(1,1); +insert into t2(a,b) values(2,2); +insert into t2(a,b) values(3,3); +insert into t2(a,b) values(4,4); +insert into t1(a) values(f1_two_updates()); + +connection master; +begin; +insert into t1(a) values(f1_two_updates()); +commit; + +connection master; +--echo #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on master +select * from t2 ORDER BY i1; + +sync_slave_with_master; +connection slave; +--echo #Test result for INVOKES A FUNCTION TO UPDATE TWO OR MORE VALUES on slave +select * from t2 ORDER BY i1; + +connection master; +drop table t1; +drop table t2; +drop function f1_two_updates; +sync_slave_with_master; + +--echo # Test case13: UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT +connection master; +create table t1(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; +create table t2(i1 int not null auto_increment, a int, b int, primary key(i1)) engine=innodb; +begin; +let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +insert into t1(a,b) values(1,1),(2,2); +insert into t2(a,b) values(1,1),(2,2); +update t1,t2 set t1.a=t1.a+5, t2.a=t2.a+5 where t1.b=t2.b; +insert into t1(a,b) values(3,3); +insert into t2(a,b) values(3,3); +commit; +--echo # To verify if it works fine when these statements are not be marked as unsafe +source include/show_binlog_events.inc; + +sync_slave_with_master; +--echo #Test if the results are consistent on master and slave +--echo #for 'UPDATE MORE THAN ONE TABLES ON TOP-STATEMENT' +let $diff_table_1=master:test.t1; +let $diff_table_2=slave:test.t1; +source include/diff_tables.inc; +let $diff_table_1=master:test.t2; +let $diff_table_2=slave:test.t2; +source include/diff_tables.inc; + +connection master; +drop table t1; +drop table t2; +sync_slave_with_master; + +--echo # Test case14: INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES +connection master; +CREATE TABLE t1(i1 int not null auto_increment, c1 INT, primary key(i1)) engine=innodb; +CREATE TABLE t2(i1 int not null auto_increment, c2 INT, primary key(i1)) engine=innodb; +let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +begin; +INSERT INTO t1(c1) VALUES (11), (12); +INSERT INTO t2(c2) VALUES (13), (14); + +CREATE VIEW v15 AS SELECT c1, c2 FROM t1, t2; + +INSERT INTO v15(c1) VALUES (15),(16); +INSERT INTO v15(c2) VALUES (17),(18); + +connection master1; +INSERT INTO v15(c1) VALUES (19),(20); +INSERT INTO v15(c2) VALUES (21),(22); + +connection master; +INSERT INTO v15(c1) VALUES (23), (24); +INSERT INTO v15(c2) VALUES (25), (26); +commit; +--echo # To verify if it works fine when these statements are not be marked as unsafe +source include/show_binlog_events.inc; + +sync_slave_with_master; +--echo #Test if the results are consistent on master and slave +--echo #for 'INSERT DATA INTO VIEW WHICH INVOLVED MORE THAN ONE TABLES' +let $diff_table_1=master:test.t1; +let $diff_table_2=slave:test.t1; +source include/diff_tables.inc; +let $diff_table_1=master:test.t2; +let $diff_table_2=slave:test.t2; +source include/diff_tables.inc; + +connection master; +drop table t1; +drop table t2; +drop view v15; +sync_slave_with_master; + diff --git a/mysql-test/suite/rpl/t/rpl_loaddatalocal.test b/mysql-test/suite/rpl/t/rpl_loaddatalocal.test index 23c802ab3de..a93a82d6d9f 100644 --- a/mysql-test/suite/rpl/t/rpl_loaddatalocal.test +++ b/mysql-test/suite/rpl/t/rpl_loaddatalocal.test @@ -98,3 +98,73 @@ DROP TABLE t1; --echo [on slave] sync_slave_with_master; +--echo +--echo Bug #43746: +--echo "return wrong query string when parse 'load data infile' sql statement" +--echo + +--echo [master] +connection master; +let $MYSQLD_DATADIR= `select @@datadir`; +SELECT @@SESSION.sql_mode INTO @old_mode; + +SET sql_mode='ignore_space'; + +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES (1), (2), (3), (4); + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval SELECT * INTO OUTFILE '$MYSQLD_DATADIR/bug43746.sql' FROM t1; +TRUNCATE TABLE t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD/* look mum, with comments in weird places! */DATA/* oh hai */LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql'/* we are */INTO/* from the internets */TABLE t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA/*!10000 LOCAL */INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' /*!10000 INTO */ TABLE t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' /*!10000 INTO TABLE */ t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA /*!10000 LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO TABLE */ t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA/*!10000 LOCAL */INFILE '$MYSQLD_DATADIR/bug43746.sql'/*!10000 INTO*/TABLE t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA/*!10000 LOCAL */INFILE '$MYSQLD_DATADIR/bug43746.sql'/* empty */INTO TABLE t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA/*!10000 LOCAL */INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO/* empty */TABLE t1; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD/*!99999 special comments that do not expand */DATA/*!99999 code from the future */LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql'/*!99999 have flux capacitor */INTO/*!99999 will travel */TABLE t1; + +SET sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER'; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1; + +--echo [slave] +sync_slave_with_master; + +# cleanup + +--remove_file $MYSQLD_DATADIR/bug43746.sql + +--echo [master] +connection master; +DROP TABLE t1; +SET SESSION sql_mode=@old_mode; + +--echo [slave] +sync_slave_with_master; + +connection master; diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index ae48d5a8736..17549745203 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1000,4 +1000,50 @@ ALTER TABLE t1 MODIFY b ENUM('a', 'z', 'b', 'c') NOT NULL; SELECT * FROM t1; DROP TABLE t1; +--echo # +--echo # Bug#45567: Fast ALTER TABLE broken for enum and set +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a ENUM('a1','a2')); +INSERT INTO t1 VALUES ('a1'),('a2'); +--enable_info +--echo # No copy: No modification +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2'); +--echo # No copy: Add new enumeration to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a3'); +--echo # Copy: Modify and add new to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx','a5'); +--echo # Copy: Remove from the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx'); +--echo # Copy: Add new enumeration +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx'); +--echo # No copy: Add new enumerations to the end +ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx','a5','a6'); +--disable_info +DROP TABLE t1; + +CREATE TABLE t1 (a SET('a1','a2')); +INSERT INTO t1 VALUES ('a1'),('a2'); +--enable_info +--echo # No copy: No modification +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2'); +--echo # No copy: Add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a3'); +--echo # Copy: Modify and add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx','a5'); +--echo # Copy: Remove from the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx'); +--echo # Copy: Add new member +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx'); +--echo # No copy: Add new to the end +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6'); +--echo # Copy: Numerical incrase (pack lenght) +ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6','a7','a8','a9','a10'); +--disable_info +DROP TABLE t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 6d8f0af0c28..6f0b1716d38 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -12,3 +12,5 @@ kill : Bug#37780 2008-12-03 HHunger need some changes to be robust enough for pushbuild. innodb_bug39438 : Bug#42383 2009-01-28 lsoares "This fails in embedded and on windows. Note that this test is not run on windows and on embedded in PB for main trees currently" query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically +partition_innodb_builtin : Bug#32430 2009-09-25 mattiasj Waiting for push of Innodb changes +partition_innodb_plugin : Bug#32430 2009-09-25 mattiasj Waiting for push of Innodb changes diff --git a/mysql-test/t/information_schema_db.test b/mysql-test/t/information_schema_db.test index 0ff1d05f364..2f651057e5c 100644 --- a/mysql-test/t/information_schema_db.test +++ b/mysql-test/t/information_schema_db.test @@ -184,7 +184,6 @@ show fields from testdb_1.v7; --error ER_TABLEACCESS_DENIED_ERROR show create view testdb_1.v7; ---error ER_VIEW_NO_EXPLAIN show create view v4; #--error ER_VIEW_NO_EXPLAIN show fields from v4; diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index 7767abe43d0..78661b1bbc4 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -71,7 +71,8 @@ select "--- --position --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --position=239 $MYSQLD_DATADIR/master-bin.000002 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --position=330 $MYSQLD_DATADIR/master-bin.000002 + # These are tests for remote binlog. # They should return the same as previous test. @@ -107,7 +108,7 @@ select "--- --position --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --position=239 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --position=330 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 # Bug#7853 mysqlbinlog does not accept input from stdin --disable_query_log @@ -377,6 +378,68 @@ FLUSH LOGS; # We do not need the results, just make sure that mysqlbinlog does not crash --exec $MYSQL_BINLOG --hexdump --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 >/dev/null +# +# #46998 +# This test verifies if the 'BEGIN', 'COMMIT' and 'ROLLBACK' are output +# in regardless of database filtering +# + +RESET MASTER; +FLUSH LOGS; + +# The following three test cases were wrtten into binlog_transaction.000001 +# Test case1: Test if the 'BEGIN' and 'COMMIT' are output for the 'test' database +# in transaction1 base on innodb engine tables +# use test; +# create table t1(a int) engine= innodb; +# use mysql; +# create table t2(a int) engine= innodb; +# Transaction1 begin +# begin; +# use test; +# insert into t1 (a) values (1); +# use mysql; +# insert into t2 (a) values (1); +# commit; +# Transaction1 end + +# Test case2: Test if the 'BEGIN' and 'ROLLBACK' are output for the 'test' database +# in transaction2 base on innodb and myisam engine tables +# use test; +# create table t3(a int) engine= innodb; +# use mysql; +# create table t4(a int) engine= myisam; +# Transaction2 begin +# begin; +# use test; +# insert into t3 (a) values (2); +# use mysql; +# insert into t4 (a) values (2); +# rollback; +# Transaction2 end + +# Test case3: Test if the 'BEGIN' and 'COMMIT' are output for the 'test' database +# in transaction3 base on NDB engine tables +# use test; +# create table t5(a int) engine= NDB; +# use mysql; +# create table t6(a int) engine= NDB; +# Transaction3 begin +# begin; +# use test; +# insert into t5 (a) values (3); +# use mysql; +# insert into t6 (a) values (3); +# commit; +# Transaction3 end + +--echo # +--echo # Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified is exist +--exec $MYSQL_BINLOG --database=test --short-form $MYSQLTEST_VARDIR/std_data/binlog_transaction.000001 +--echo # +--echo # Test if the 'BEGIN', 'ROLLBACK' and 'COMMIT' are output if the database specified is not exist +--exec $MYSQL_BINLOG --database=not_exist --short-form $MYSQLTEST_VARDIR/std_data/binlog_transaction.000001 + --echo End of 5.0 tests --echo End of 5.1 tests diff --git a/mysql-test/t/partition_innodb_builtin.test b/mysql-test/t/partition_innodb_builtin.test new file mode 100644 index 00000000000..a9be41c7455 --- /dev/null +++ b/mysql-test/t/partition_innodb_builtin.test @@ -0,0 +1,67 @@ +--source include/have_partition.inc +--source include/have_innodb.inc +--source include/have_not_innodb_plugin.inc + +# +# Bug#32430 - show engine innodb status causes errors +# +SET NAMES utf8; +CREATE TABLE `t``\""e` (a INT, PRIMARY KEY (a)) +ENGINE=InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +(PARTITION `p0``\""e` VALUES LESS THAN (100) + (SUBPARTITION `sp0``\""e`, + SUBPARTITION `sp1``\""e`), + PARTITION `p1``\""e` VALUES LESS THAN (MAXVALUE) + (SUBPARTITION `sp2``\""e`, + SUBPARTITION `sp3``\""e`)); +INSERT INTO `t``\""e` VALUES (0), (2), (6), (10), (14), (18), (22); +START TRANSACTION; +--echo # con1 +connect(con1,localhost,root,,); +SET NAMES utf8; +START TRANSACTION; +--echo # default connection +connection default; +UPDATE `t``\""e` SET a = 16 WHERE a = 0; +--echo # con1 +connection con1; +UPDATE `t``\""e` SET a = 8 WHERE a = 22; +let $id_1= `SELECT CONNECTION_ID()`; +SEND; +UPDATE `t``\""e` SET a = 12 WHERE a = 0; +--echo # default connection +connection default; +let $wait_timeout= 2; +let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID = $id_1 AND STATE = 'Searching rows for update'; +--source include/wait_condition.inc +#--echo # tested wait condition $wait_condition_reps times +--error ER_LOCK_DEADLOCK +UPDATE `t``\""e` SET a = 4 WHERE a = 22; +--echo # First table reported in 'SHOW ENGINE InnoDB STATUS' +# RECORD LOCKS space id 0 page no 50 n bits 80 index `PRIMARY` in \ +# Database `test`, Table `t1`, Partition `p0`, Subpartition `sp0` \ +# trx id 0 775 +# NOTE: replace_regex is very slow on match copy/past '(.*)' regex's +# on big texts, removing a lot of text before + after makes it much faster. +#/.*in (.*) trx.*/\1/ +--replace_regex /.*RECORD LOCKS space id [0-9]* page no [0-9]* n bits [0-9]* // / trx id .*// /.*index .* in // +SHOW ENGINE InnoDB STATUS; +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +# INNODB_LOCKS only exists in innodb_plugin +#SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; +--replace_regex /.*RECORD LOCKS space id [0-9]* page no [0-9]* n bits [0-9]* // / trx id .*// /.*index .* in // +SHOW ENGINE InnoDB STATUS; +set @@sql_mode = @old_sql_mode; +--echo # con1 +connection con1; +REAP; +ROLLBACK; +disconnect con1; +--echo # default connection +connection default; +DROP TABLE `t``\""e`; +SET NAMES DEFAULT; diff --git a/mysql-test/t/partition_innodb_plugin.test b/mysql-test/t/partition_innodb_plugin.test new file mode 100644 index 00000000000..fed8c96424a --- /dev/null +++ b/mysql-test/t/partition_innodb_plugin.test @@ -0,0 +1,75 @@ +--source include/have_partition.inc +--source include/have_innodb.inc +--source suite/innodb/include/have_innodb_plugin.inc + +# +# Bug#32430 - show engine innodb status causes errors +# +SET NAMES utf8; +CREATE TABLE `t``\""e` (a INT, PRIMARY KEY (a)) +ENGINE=InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH (a) +(PARTITION `p0``\""e` VALUES LESS THAN (100) + (SUBPARTITION `sp0``\""e`, + SUBPARTITION `sp1``\""e`), + PARTITION `p1``\""e` VALUES LESS THAN (MAXVALUE) + (SUBPARTITION `sp2``\""e`, + SUBPARTITION `sp3``\""e`)); +INSERT INTO `t``\""e` VALUES (0), (2), (6), (10), (14), (18), (22); +START TRANSACTION; +--echo # con1 +connect(con1,localhost,root,,); +SET NAMES utf8; +START TRANSACTION; +--echo # default connection +connection default; +UPDATE `t``\""e` SET a = 16 WHERE a = 0; +--echo # con1 +connection con1; +UPDATE `t``\""e` SET a = 8 WHERE a = 22; +let $id_1= `SELECT CONNECTION_ID()`; +SEND; +UPDATE `t``\""e` SET a = 12 WHERE a = 0; +--echo # default connection +connection default; +let $wait_timeout= 2; +let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST +WHERE ID = $id_1 AND STATE = 'Searching rows for update'; +--source include/wait_condition.inc +#--echo # tested wait condition $wait_condition_reps times +# INNODB_LOCKS only exists in innodb_plugin +--sorted_result +SELECT lock_table, COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS +GROUP BY lock_table; +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +--sorted_result +SELECT lock_table, COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS +GROUP BY lock_table; +set @@sql_mode = @old_sql_mode; +--error ER_LOCK_DEADLOCK +UPDATE `t``\""e` SET a = 4 WHERE a = 22; +--echo # First table reported in 'SHOW ENGINE InnoDB STATUS' +# RECORD LOCKS space id 0 page no 50 n bits 80 index `PRIMARY` in \ +# Database `test`, Table `t1`, Partition `p0`, Subpartition `sp0` \ +# trx id 0 775 +# NOTE: replace_regex is very slow on match copy/past '(.*)' regex's +# on big texts, removing a lot of text before + after makes it much faster. +#/.*in (.*) trx.*/\1/ +--replace_regex /.*RECORD LOCKS space id [0-9]* page no [0-9]* n bits [0-9]* // / trx id .*// /.*index .* in // +SHOW ENGINE InnoDB STATUS; +set @old_sql_mode = @@sql_mode; +set sql_mode = 'ANSI_QUOTES'; +--replace_regex /.*RECORD LOCKS space id [0-9]* page no [0-9]* n bits [0-9]* // / trx id .*// /.*index .* in // +SHOW ENGINE InnoDB STATUS; +set @@sql_mode = @old_sql_mode; +--echo # con1 +connection con1; +REAP; +ROLLBACK; +disconnect con1; +--echo # default connection +connection default; +DROP TABLE `t``\""e`; +SET NAMES DEFAULT; diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 824c67d867e..2ad488b7529 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -1382,6 +1382,127 @@ DROP VIEW test.v3; DROP USER mysqluser1@localhost; USE test; +--echo # +--echo # Bug#35996: SELECT + SHOW VIEW should be enough to display view +--echo # definition +--echo # +-- source include/not_embedded.inc +CREATE USER mysqluser1@localhost; +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; +GRANT USAGE, SELECT, CREATE VIEW, SHOW VIEW +ON mysqltest2.* TO mysqluser1@localhost; + +USE mysqltest1; + +CREATE TABLE t1( a INT ); +CREATE TABLE t2( a INT, b INT ); +CREATE FUNCTION f1() RETURNS INT RETURN 1; +CREATE VIEW v1 AS SELECT 1 AS a; +CREATE VIEW v2 AS SELECT 1 AS a, 2 AS b; + +GRANT SELECT ON TABLE t1 TO mysqluser1@localhost; +GRANT SELECT (a, b) ON TABLE t2 TO mysqluser1@localhost; +GRANT EXECUTE ON FUNCTION f1 TO mysqluser1@localhost; +GRANT SELECT ON TABLE v1 TO mysqluser1@localhost; +GRANT SELECT (a, b) ON TABLE v2 TO mysqluser1@localhost; + +CREATE VIEW v_t1 AS SELECT * FROM t1; +CREATE VIEW v_t2 AS SELECT * FROM t2; +CREATE VIEW v_f1 AS SELECT f1() AS a; +CREATE VIEW v_v1 AS SELECT * FROM v1; +CREATE VIEW v_v2 AS SELECT * FROM v2; + +GRANT SELECT, SHOW VIEW ON v_t1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_t2 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_f1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_v1 TO mysqluser1@localhost; +GRANT SELECT, SHOW VIEW ON v_v2 TO mysqluser1@localhost; + +--connect (connection1, localhost, mysqluser1,, mysqltest2) +CREATE VIEW v_mysqluser1_t1 AS SELECT * FROM mysqltest1.t1; +CREATE VIEW v_mysqluser1_t2 AS SELECT * FROM mysqltest1.t2; +CREATE VIEW v_mysqluser1_f1 AS SELECT mysqltest1.f1() AS a; +CREATE VIEW v_mysqluser1_v1 AS SELECT * FROM mysqltest1.v1; +CREATE VIEW v_mysqluser1_v2 AS SELECT * FROM mysqltest1.v2; + +SHOW CREATE VIEW mysqltest1.v_t1; +SHOW CREATE VIEW mysqltest1.v_t2; +SHOW CREATE VIEW mysqltest1.v_f1; +SHOW CREATE VIEW mysqltest1.v_v1; +SHOW CREATE VIEW mysqltest1.v_v2; + +SHOW CREATE VIEW v_mysqluser1_t1; +SHOW CREATE VIEW v_mysqluser1_t2; +SHOW CREATE VIEW v_mysqluser1_f1; +SHOW CREATE VIEW v_mysqluser1_v1; +SHOW CREATE VIEW v_mysqluser1_v2; + +--connection default +REVOKE SELECT ON TABLE t1 FROM mysqluser1@localhost; +REVOKE SELECT (a) ON TABLE t2 FROM mysqluser1@localhost; +REVOKE EXECUTE ON FUNCTION f1 FROM mysqluser1@localhost; +REVOKE SELECT ON TABLE v1 FROM mysqluser1@localhost; + +--connection connection1 +SHOW CREATE VIEW mysqltest1.v_t1; +SHOW CREATE VIEW mysqltest1.v_t2; +SHOW CREATE VIEW mysqltest1.v_f1; +SHOW CREATE VIEW mysqltest1.v_v1; +SHOW CREATE VIEW mysqltest1.v_v2; + +SHOW CREATE VIEW v_mysqluser1_t1; +SHOW CREATE VIEW v_mysqluser1_t2; +SHOW CREATE VIEW v_mysqluser1_f1; +SHOW CREATE VIEW v_mysqluser1_v1; +SHOW CREATE VIEW v_mysqluser1_v2; + +--connection default +--echo # Testing the case when the views reference missing objects. +--echo # Obviously, there are no privileges to check for, so we +--echo # need only each object type once. +DROP TABLE t1; +DROP FUNCTION f1; +DROP VIEW v1; + +--connection connection1 +SHOW CREATE VIEW mysqltest1.v_t1; +SHOW CREATE VIEW mysqltest1.v_f1; +SHOW CREATE VIEW mysqltest1.v_v1; + +SHOW CREATE VIEW v_mysqluser1_t1; +SHOW CREATE VIEW v_mysqluser1_f1; +SHOW CREATE VIEW v_mysqluser1_v1; + +--connection default +REVOKE SHOW VIEW ON v_t1 FROM mysqluser1@localhost; +REVOKE SHOW VIEW ON v_f1 FROM mysqluser1@localhost; +REVOKE SHOW VIEW ON v_v1 FROM mysqluser1@localhost; + +--connection connection1 +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE VIEW mysqltest1.v_t1; +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE VIEW mysqltest1.v_f1; +--error ER_TABLEACCESS_DENIED_ERROR +SHOW CREATE VIEW mysqltest1.v_v1; +SHOW CREATE VIEW v_mysqluser1_t1; +SHOW CREATE VIEW v_mysqluser1_f1; +SHOW CREATE VIEW v_mysqluser1_v1; + +--disconnect connection1 +--connection default +DROP USER mysqluser1@localhost; +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; +USE test; + +CREATE TABLE t1( a INT ); +CREATE DEFINER = no_such_user@no_such_host VIEW v1 AS SELECT * FROM t1; +SHOW CREATE VIEW v1; +DROP TABLE t1; +DROP VIEW v1; + # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc diff --git a/scripts/make_win_bin_dist b/scripts/make_win_bin_dist index fdb34f2f48d..c65439181ef 100755 --- a/scripts/make_win_bin_dist +++ b/scripts/make_win_bin_dist @@ -338,6 +338,7 @@ fi mkdir $DESTDIR/mysql-test cp mysql-test/mysql-test-run.pl $DESTDIR/mysql-test/ +cp mysql-test/mysql-stress-test.pl $DESTDIR/mysql-test/ cp mysql-test/README $DESTDIR/mysql-test/ cp -R mysql-test/{t,r,include,suite,std_data,lib} $DESTDIR/mysql-test/ diff --git a/sql/field.cc b/sql/field.cc index 9f46552d5bd..0df9b0fc2e4 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -8833,38 +8833,81 @@ bool Field::eq_def(Field *field) /** + Compare the first t1::count type names. + + @return TRUE if the type names of t1 match those of t2. FALSE otherwise. +*/ + +static bool compare_type_names(CHARSET_INFO *charset, TYPELIB *t1, TYPELIB *t2) +{ + for (uint i= 0; i < t1->count; i++) + if (my_strnncoll(charset, + (const uchar*) t1->type_names[i], + t1->type_lengths[i], + (const uchar*) t2->type_names[i], + t2->type_lengths[i])) + return FALSE; + return TRUE; +} + +/** @return returns 1 if the fields are equally defined */ bool Field_enum::eq_def(Field *field) { + TYPELIB *values; + if (!Field::eq_def(field)) - return 0; - return compare_enum_values(((Field_enum*) field)->typelib); -} + return FALSE; + values= ((Field_enum*) field)->typelib; -bool Field_enum::compare_enum_values(TYPELIB *values) -{ + /* Definition must be strictly equal. */ if (typelib->count != values->count) return FALSE; - for (uint i= 0; i < typelib->count; i++) - if (my_strnncoll(field_charset, - (const uchar*) typelib->type_names[i], - typelib->type_lengths[i], - (const uchar*) values->type_names[i], - values->type_lengths[i])) - return FALSE; - return TRUE; + + return compare_type_names(field_charset, typelib, values); } +/** + Check whether two fields can be considered 'equal' for table + alteration purposes. Fields are equal if they retain the same + pack length and if new members are added to the end of the list. + + @return IS_EQUAL_YES if fields are compatible. + IS_EQUAL_NO otherwise. +*/ + uint Field_enum::is_equal(Create_field *new_field) { - if (!Field_str::is_equal(new_field)) - return 0; - return compare_enum_values(new_field->interval); + TYPELIB *values= new_field->interval; + + /* + The fields are compatible if they have the same flags, + type, charset and have the same underlying length. + */ + if (compare_str_field_flags(new_field, flags) || + new_field->sql_type != real_type() || + new_field->charset != field_charset || + new_field->pack_length != pack_length()) + return IS_EQUAL_NO; + + /* + Changing the definition of an ENUM or SET column by adding a new + enumeration or set members to the end of the list of valid member + values only alters table metadata and not table data. + */ + if (typelib->count > values->count) + return IS_EQUAL_NO; + + /* Check whether there are modification before the end. */ + if (! compare_type_names(field_charset, typelib, new_field->interval)) + return IS_EQUAL_NO; + + return IS_EQUAL_YES; } diff --git a/sql/field.h b/sql/field.h index a9299256f88..7a9b69eff40 100644 --- a/sql/field.h +++ b/sql/field.h @@ -472,6 +472,13 @@ public: /* maximum possible display length */ virtual uint32 max_display_length()= 0; + /** + Whether a field being created is compatible with a existing one. + + Used by the ALTER TABLE code to evaluate whether the new definition + of a table is compatible with the old definition so that it can + determine if data needs to be copied over (table data change). + */ virtual uint is_equal(Create_field *new_field); /* convert decimal to longlong with overflow check */ longlong convert_decimal2longlong(const my_decimal *val, bool unsigned_flag, @@ -1862,7 +1869,6 @@ public: CHARSET_INFO *sort_charset(void) const { return &my_charset_bin; } private: int do_save_field_metadata(uchar *first_byte); - bool compare_enum_values(TYPELIB *values); uint is_equal(Create_field *new_field); }; diff --git a/sql/log_event.cc b/sql/log_event.cc index 3c584011bfb..d7921ad3c27 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -4010,7 +4010,7 @@ uint Load_log_event::get_query_buffer_length() } -void Load_log_event::print_query(bool need_db, char *buf, +void Load_log_event::print_query(bool need_db, const char *cs, char *buf, char **end, char **fn_start, char **fn_end) { char *pos= buf; @@ -4034,9 +4034,9 @@ void Load_log_event::print_query(bool need_db, char *buf, pos= strmov(pos+fname_len, "' "); if (sql_ex.opt_flags & REPLACE_FLAG) - pos= strmov(pos, " REPLACE "); + pos= strmov(pos, "REPLACE "); else if (sql_ex.opt_flags & IGNORE_FLAG) - pos= strmov(pos, " IGNORE "); + pos= strmov(pos, "IGNORE "); pos= strmov(pos ,"INTO"); @@ -4047,8 +4047,16 @@ void Load_log_event::print_query(bool need_db, char *buf, memcpy(pos, table_name, table_name_len); pos+= table_name_len; - /* We have to create all optinal fields as the default is not empty */ - pos= strmov(pos, "` FIELDS TERMINATED BY "); + if (cs != NULL) + { + pos= strmov(pos ,"` CHARACTER SET "); + pos= strmov(pos , cs); + } + else + pos= strmov(pos, "`"); + + /* We have to create all optional fields as the default is not empty */ + pos= strmov(pos, " FIELDS TERMINATED BY "); pos= pretty_print_str(pos, sql_ex.field_term, sql_ex.field_term_len); if (sql_ex.opt_flags & OPT_ENCLOSED_FLAG) pos= strmov(pos, " OPTIONALLY "); @@ -4102,7 +4110,7 @@ void Load_log_event::pack_info(Protocol *protocol) if (!(buf= (char*) my_malloc(get_query_buffer_length(), MYF(MY_WME)))) return; - print_query(TRUE, buf, &end, 0, 0); + print_query(TRUE, NULL, buf, &end, 0, 0); protocol->store(buf, end-buf, &my_charset_bin); my_free(buf, MYF(0)); } @@ -4367,9 +4375,9 @@ void Load_log_event::print(FILE* file_arg, PRINT_EVENT_INFO* print_event_info, my_b_printf(&cache, "INFILE '%-*s' ", fname_len, fname); if (sql_ex.opt_flags & REPLACE_FLAG) - my_b_printf(&cache," REPLACE "); + my_b_printf(&cache,"REPLACE "); else if (sql_ex.opt_flags & IGNORE_FLAG) - my_b_printf(&cache," IGNORE "); + my_b_printf(&cache,"IGNORE "); my_b_printf(&cache, "INTO TABLE `%s`", table_name); my_b_printf(&cache, " FIELDS TERMINATED BY "); @@ -4577,8 +4585,7 @@ int Load_log_event::do_apply_event(NET* net, Relay_log_info const *rli, goto error; } - print_query(FALSE, load_data_query, &end, (char **)&thd->lex->fname_start, - (char **)&thd->lex->fname_end); + print_query(FALSE, NULL, load_data_query, &end, NULL, NULL); *end= 0; thd->set_query(load_data_query, (uint) (end - load_data_query)); @@ -6732,7 +6739,7 @@ void Execute_load_query_log_event::print(FILE* file, my_b_printf(&cache, "\'"); if (dup_handling == LOAD_DUP_REPLACE) my_b_printf(&cache, " REPLACE"); - my_b_printf(&cache, " INTO "); + my_b_printf(&cache, " INTO"); my_b_write(&cache, (uchar*) query + fn_pos_end, q_len-fn_pos_end); my_b_printf(&cache, "\n%s\n", print_event_info->delimiter); } diff --git a/sql/log_event.h b/sql/log_event.h index 8202dddcc76..31d4a7480c2 100644 --- a/sql/log_event.h +++ b/sql/log_event.h @@ -1960,15 +1960,15 @@ private: class Load_log_event: public Log_event { private: - uint get_query_buffer_length(); - void print_query(bool need_db, char *buf, char **end, - char **fn_start, char **fn_end); protected: int copy_log_event(const char *buf, ulong event_len, int body_offset, const Format_description_log_event* description_event); public: + uint get_query_buffer_length(); + void print_query(bool need_db, const char *cs, char *buf, char **end, + char **fn_start, char **fn_end); ulong thread_id; ulong slave_proxy_id; uint32 table_name_len; diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 381a0313add..6fde16d3049 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -2277,10 +2277,9 @@ enum enum_explain_filename_mode { EXPLAIN_ALL_VERBOSE= 0, EXPLAIN_PARTITIONS_VERBOSE, - EXPLAIN_PARTITIONS_AS_COMMENT, - EXPLAIN_PARTITIONS_AS_COMMENT_NO_QUOTING + EXPLAIN_PARTITIONS_AS_COMMENT }; -uint explain_filename(const char *from, char *to, uint to_length, +uint explain_filename(THD* thd, const char *from, char *to, uint to_length, enum_explain_filename_mode explain_mode); uint filename_to_tablename(const char *from, char *to, uint to_length); uint tablename_to_filename(const char *from, char *to, uint to_length); diff --git a/sql/slave.cc b/sql/slave.cc index 66cbef1029a..82c9d035fd2 100644 --- a/sql/slave.cc +++ b/sql/slave.cc @@ -3761,7 +3761,7 @@ extern "C" void slave_io_thread_detach_vio() { #ifdef SIGNAL_WITH_VIO_CLOSE THD *thd= current_thd; - if (thd->slave_thread) + if (thd && thd->slave_thread) thd->clear_active_vio(); #endif } diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index ab18a2d1d04..d7d662f912d 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -4072,8 +4072,7 @@ bool check_column_grant_in_table_ref(THD *thd, TABLE_LIST * table_ref, db_name= table_ref->view_db.str; table_name= table_ref->view_name.str; if (table_ref->belong_to_view && - (thd->lex->sql_command == SQLCOM_SHOW_FIELDS || - thd->lex->sql_command == SQLCOM_SHOW_CREATE)) + thd->lex->sql_command == SQLCOM_SHOW_FIELDS) { view_privs= get_column_grant(thd, grant, db_name, table_name, name); if (view_privs & VIEW_ANY_ACL) diff --git a/sql/sql_base.cc b/sql/sql_base.cc index af3ae03424e..d1e96fcdbb3 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -107,7 +107,7 @@ static bool open_new_frm(THD *thd, TABLE_SHARE *share, const char *alias, static void close_old_data_files(THD *thd, TABLE *table, bool morph_locks, bool send_refresh); static bool -has_two_write_locked_tables_with_auto_increment(TABLE_LIST *tables); +has_write_table_with_auto_increment(TABLE_LIST *tables); extern "C" uchar *table_cache_key(const uchar *record, size_t *length, @@ -5279,15 +5279,17 @@ int lock_tables(THD *thd, TABLE_LIST *tables, uint count, bool *need_reopen) thd->in_lock_tables=1; thd->options|= OPTION_TABLE_LOCK; /* - If we have >= 2 different tables to update with auto_inc columns, - statement-based binlogging won't work. We can solve this problem in - mixed mode by switching to row-based binlogging: + A query that modifies autoinc column in sub-statement can make the + master and slave inconsistent. + We can solve these problems in mixed mode by switching to binlogging + if at least one updated table is used by sub-statement */ - if (thd->variables.binlog_format == BINLOG_FORMAT_MIXED && - has_two_write_locked_tables_with_auto_increment(tables)) + /* The BINLOG_FORMAT_MIXED judgement is saved for suppressing + warnings, but it will be removed by fixing bug#45827 */ + if (thd->variables.binlog_format == BINLOG_FORMAT_MIXED && tables && + has_write_table_with_auto_increment(thd->lex->first_not_own_table())) { thd->lex->set_stmt_unsafe(); - thd->set_current_stmt_binlog_row_based_if_mixed(); } } @@ -8819,47 +8821,31 @@ void mysql_wait_completed_table(ALTER_PARTITION_PARAM_TYPE *lpt, TABLE *my_table /* - Tells if two (or more) tables have auto_increment columns and we want to - lock those tables with a write lock. + Check if one (or more) write tables have auto_increment columns. - SYNOPSIS - has_two_write_locked_tables_with_auto_increment - tables Table list + @param[in] tables Table list + + @retval 0 if at least one write tables has an auto_increment column + @retval 1 otherwise NOTES: Call this function only when you have established the list of all tables which you'll want to update (including stored functions, triggers, views inside your statement). - - RETURN - 0 No - 1 Yes */ static bool -has_two_write_locked_tables_with_auto_increment(TABLE_LIST *tables) +has_write_table_with_auto_increment(TABLE_LIST *tables) { - char *first_table_name= NULL, *first_db; - LINT_INIT(first_db); - for (TABLE_LIST *table= tables; table; table= table->next_global) { /* we must do preliminary checks as table->table may be NULL */ if (!table->placeholder() && table->table->found_next_number_field && (table->lock_type >= TL_WRITE_ALLOW_WRITE)) - { - if (first_table_name == NULL) - { - first_table_name= table->table_name; - first_db= table->db; - DBUG_ASSERT(first_db); - } - else if (strcmp(first_db, table->db) || - strcmp(first_table_name, table->table_name)) - return 1; - } + return 1; } + return 0; } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 76fd5354c51..4e4794ef2cf 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1735,13 +1735,6 @@ typedef struct st_lex : public Query_tables_list const char *stmt_definition_end; - /* - Pointers to part of LOAD DATA statement that should be rewritten - during replication ("LOCAL 'filename' REPLACE INTO" part). - */ - const char *fname_start; - const char *fname_end; - /** During name resolution search only in the table list given by Name_resolution_context::first_name_resolution_table and diff --git a/sql/sql_load.cc b/sql/sql_load.cc index b7f33d51335..e830e29176b 100644 --- a/sql/sql_load.cc +++ b/sql/sql_load.cc @@ -83,10 +83,13 @@ static int read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, String &enclosed, ulong skip_lines, bool ignore_check_option_errors); #ifndef EMBEDDED_LIBRARY -static bool write_execute_load_query_log_event(THD *thd, - bool duplicates, bool ignore, - bool transactional_table, - int errcode); +static bool write_execute_load_query_log_event(THD *thd, sql_exchange* ex, + const char* db_arg, + const char* table_name_arg, + enum enum_duplicates duplicates, + bool ignore, + bool transactional_table, + int errocode); #endif /* EMBEDDED_LIBRARY */ /* @@ -497,8 +500,10 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, int errcode= query_error_code(thd, killed_status == THD::NOT_KILLED); if (thd->transaction.stmt.modified_non_trans_table) - write_execute_load_query_log_event(thd, handle_duplicates, - ignore, transactional_table, + write_execute_load_query_log_event(thd, ex, + tdb, table_list->table_name, + handle_duplicates, ignore, + transactional_table, errcode); else { @@ -542,8 +547,11 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, if (lf_info.wrote_create_file) { int errcode= query_error_code(thd, killed_status == THD::NOT_KILLED); - write_execute_load_query_log_event(thd, handle_duplicates, ignore, - transactional_table, errcode); + write_execute_load_query_log_event(thd, ex, + tdb, table_list->table_name, + handle_duplicates, ignore, + transactional_table, + errcode); } } } @@ -564,15 +572,95 @@ err: #ifndef EMBEDDED_LIBRARY /* Not a very useful function; just to avoid duplication of code */ -static bool write_execute_load_query_log_event(THD *thd, - bool duplicates, bool ignore, - bool transactional_table, +static bool write_execute_load_query_log_event(THD *thd, sql_exchange* ex, + const char* db_arg, + const char* table_name_arg, + enum enum_duplicates duplicates, + bool ignore, + bool transactional_table, int errcode) { + char *load_data_query, + *end, + *fname_start, + *fname_end, + *p= NULL; + size_t pl= 0; + List<Item> fv; + Item *item, *val; + String pfield, pfields; + int n; + + Load_log_event lle(thd, ex, db_arg, table_name_arg, fv, duplicates, + ignore, transactional_table); + + /* + force in a LOCAL if there was one in the original. + */ + if (thd->lex->local_file) + lle.set_fname_outside_temp_buf(ex->file_name, strlen(ex->file_name)); + + /* + prepare fields-list and SET if needed; print_query won't do that for us. + */ + if (!thd->lex->field_list.is_empty()) + { + List_iterator<Item> li(thd->lex->field_list); + + pfields.append(" ("); + n= 0; + + while ((item= li++)) + { + if (n++) + pfields.append(", "); + if (item->name) + pfields.append(item->name); + else + item->print(&pfields, QT_ORDINARY); + } + pfields.append(")"); + } + + if (!thd->lex->update_list.is_empty()) + { + List_iterator<Item> lu(thd->lex->update_list); + List_iterator<Item> lv(thd->lex->value_list); + + pfields.append(" SET "); + n= 0; + + while ((item= lu++)) + { + val= lv++; + if (n++) + pfields.append(", "); + pfields.append(item->name); + pfields.append("="); + val->print(&pfields, QT_ORDINARY); + } + } + + p= pfields.c_ptr_safe(); + pl= strlen(p); + + if (!(load_data_query= (char *)thd->alloc(lle.get_query_buffer_length() + 1 + pl))) + return TRUE; + + lle.print_query(FALSE, (const char *) ex->cs?ex->cs->csname:NULL, + load_data_query, &end, + (char **)&fname_start, (char **)&fname_end); + + strcpy(end, p); + end += pl; + + thd->query_length= end - load_data_query; + thd->query= load_data_query; + Execute_load_query_log_event e(thd, thd->query, thd->query_length, - (uint) ((char*)thd->lex->fname_start - (char*)thd->query), - (uint) ((char*)thd->lex->fname_end - (char*)thd->query), + (uint) ((char*)fname_start - (char*)thd->query - 1), + (uint) ((char*)fname_end - (char*)thd->query), (duplicates == DUP_REPLACE) ? LOAD_DUP_REPLACE : (ignore ? LOAD_DUP_IGNORE : LOAD_DUP_ERROR), transactional_table, FALSE, errcode); diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index a977740ebe2..83ef525e3eb 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -1428,7 +1428,28 @@ bool dispatch_command(enum enum_server_command command, THD *thd, if (check_global_access(thd,RELOAD_ACL)) break; general_log_print(thd, command, NullS); - if (!reload_acl_and_cache(thd, options, (TABLE_LIST*) 0, ¬_used)) +#ifndef DBUG_OFF + bool debug_simulate= FALSE; + DBUG_EXECUTE_IF("simulate_detached_thread_refresh", debug_simulate= TRUE;); + if (debug_simulate) + { + /* + Simulate a reload without a attached thread session. + Provides a environment similar to that of when the + server receives a SIGHUP signal and reloads caches + and flushes tables. + */ + bool res; + my_pthread_setspecific_ptr(THR_THD, NULL); + res= reload_acl_and_cache(NULL, options | REFRESH_FAST, + NULL, ¬_used); + my_pthread_setspecific_ptr(THR_THD, thd); + if (!res) + my_ok(thd); + break; + } +#endif + if (!reload_acl_and_cache(thd, options, NULL, ¬_used)) my_ok(thd); break; } diff --git a/sql/sql_show.cc b/sql/sql_show.cc index bb377e676e2..75c9ea6c524 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -581,6 +581,126 @@ find_files(THD *thd, List<LEX_STRING> *files, const char *db, } +/** + An Internal_error_handler that suppresses errors regarding views' + underlying tables that occur during privilege checking within SHOW CREATE + VIEW commands. This happens in the cases when + + - A view's underlying table (e.g. referenced in its SELECT list) does not + exist. There should not be an error as no attempt was made to access it + per se. + + - Access is denied for some table, column, function or stored procedure + such as mentioned above. This error gets raised automatically, since we + can't untangle its access checking from that of the view itself. + */ +class Show_create_error_handler : public Internal_error_handler { + + TABLE_LIST *m_top_view; + bool m_handling; + Security_context *m_sctx; + + char m_view_access_denied_message[MYSQL_ERRMSG_SIZE]; + char *m_view_access_denied_message_ptr; + +public: + + /** + Creates a new Show_create_error_handler for the particular security + context and view. + + @thd Thread context, used for security context information if needed. + @top_view The view. We do not verify at this point that top_view is in + fact a view since, alas, these things do not stay constant. + */ + explicit Show_create_error_handler(THD *thd, TABLE_LIST *top_view) : + m_top_view(top_view), m_handling(FALSE), + m_view_access_denied_message_ptr(NULL) + { + + m_sctx = test(m_top_view->security_ctx) ? + m_top_view->security_ctx : thd->security_ctx; + } + + /** + Lazy instantiation of 'view access denied' message. The purpose of the + Show_create_error_handler is to hide details of underlying tables for + which we have no privileges behind ER_VIEW_INVALID messages. But this + obviously does not apply if we lack privileges on the view itself. + Unfortunately the information about for which table privilege checking + failed is not available at this point. The only way for us to check is by + reconstructing the actual error message and see if it's the same. + */ + char* get_view_access_denied_message() + { + if (!m_view_access_denied_message_ptr) + { + m_view_access_denied_message_ptr= m_view_access_denied_message; + my_snprintf(m_view_access_denied_message, MYSQL_ERRMSG_SIZE, + ER(ER_TABLEACCESS_DENIED_ERROR), "SHOW VIEW", + m_sctx->priv_user, + m_sctx->host_or_ip, m_top_view->get_table_name()); + } + return m_view_access_denied_message_ptr; + } + + bool handle_error(uint sql_errno, const char *message, + MYSQL_ERROR::enum_warning_level level, THD *thd) { + /* + The handler does not handle the errors raised by itself. + At this point we know if top_view is really a view. + */ + if (m_handling || !m_top_view->view) + return FALSE; + + m_handling= TRUE; + + bool is_handled; + + switch (sql_errno) + { + case ER_TABLEACCESS_DENIED_ERROR: + if (!strcmp(get_view_access_denied_message(), message)) + { + /* Access to top view is not granted, don't interfere. */ + is_handled= FALSE; + break; + } + case ER_COLUMNACCESS_DENIED_ERROR: + case ER_VIEW_NO_EXPLAIN: /* Error was anonymized, ignore all the same. */ + case ER_PROCACCESS_DENIED_ERROR: + is_handled= TRUE; + break; + + case ER_NO_SUCH_TABLE: + /* Established behavior: warn if underlying tables are missing. */ + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_VIEW_INVALID, + ER(ER_VIEW_INVALID), + m_top_view->get_db_name(), + m_top_view->get_table_name()); + is_handled= TRUE; + break; + + case ER_SP_DOES_NOT_EXIST: + /* Established behavior: warn if underlying functions are missing. */ + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_VIEW_INVALID, + ER(ER_VIEW_INVALID), + m_top_view->get_db_name(), + m_top_view->get_table_name()); + is_handled= TRUE; + break; + default: + is_handled= FALSE; + } + + m_handling= FALSE; + return is_handled; + } +}; + + bool mysqld_show_create(THD *thd, TABLE_LIST *table_list) { @@ -594,26 +714,13 @@ mysqld_show_create(THD *thd, TABLE_LIST *table_list) /* We want to preserve the tree for views. */ thd->lex->view_prepare_mode= TRUE; - /* Only one table for now, but VIEW can involve several tables */ - if (open_normal_and_derived_tables(thd, table_list, 0)) { - if (!table_list->view || - (thd->is_error() && thd->main_da.sql_errno() != ER_VIEW_INVALID)) + Show_create_error_handler view_error_suppressor(thd, table_list); + thd->push_internal_handler(&view_error_suppressor); + bool error= open_normal_and_derived_tables(thd, table_list, 0); + thd->pop_internal_handler(); + if (error && thd->main_da.is_error()) DBUG_RETURN(TRUE); - - /* - Clear all messages with 'error' level status and - issue a warning with 'warning' level status in - case of invalid view and last error is ER_VIEW_INVALID - */ - mysql_reset_errors(thd, true); - thd->clear_error(); - - push_warning_printf(thd,MYSQL_ERROR::WARN_LEVEL_WARN, - ER_VIEW_INVALID, - ER(ER_VIEW_INVALID), - table_list->view_db.str, - table_list->view_name.str); } /* TODO: add environment variables show when it become possible */ @@ -1873,7 +1980,7 @@ int fill_schema_processlist(THD* thd, TABLE_LIST* tables, COND* cond) tmp->mysys_var->current_cond ? "Waiting on cond" : NullS); #else - val= (char *) "Writing to net"; + val= (char *) (tmp->proc_info ? tmp->proc_info : NullS); #endif if (val) { diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 6dba2f02071..1d147c54059 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -70,15 +70,21 @@ static void wait_for_kill_signal(THD *thd) /** @brief Helper function for explain_filename + @param thd Thread handle + @param to_p Explained name in system_charset_info + @param end_p End of the to_p buffer + @param name Name to be converted + @param name_len Length of the name, in bytes */ -static char* add_identifier(char *to_p, const char * end_p, - const char* name, uint name_len, bool add_quotes) +static char* add_identifier(THD* thd, char *to_p, const char * end_p, + const char* name, uint name_len) { uint res; uint errors; const char *conv_name; char tmp_name[FN_REFLEN]; char conv_string[FN_REFLEN]; + int quote; DBUG_ENTER("add_identifier"); if (!name[name_len]) @@ -102,19 +108,21 @@ static char* add_identifier(char *to_p, const char * end_p, conv_name= conv_string; } - if (add_quotes && (end_p - to_p > 2)) + quote = thd ? get_quote_char_for_identifier(thd, conv_name, res - 1) : '"'; + + if (quote != EOF && (end_p - to_p > 2)) { - *(to_p++)= '`'; + *(to_p++)= (char) quote; while (*conv_name && (end_p - to_p - 1) > 0) { uint length= my_mbcharlen(system_charset_info, *conv_name); if (!length) length= 1; - if (length == 1 && *conv_name == '`') + if (length == 1 && *conv_name == (char) quote) { if ((end_p - to_p) < 3) break; - *(to_p++)= '`'; + *(to_p++)= (char) quote; *(to_p++)= *(conv_name++); } else if (((long) length) < (end_p - to_p)) @@ -125,7 +133,11 @@ static char* add_identifier(char *to_p, const char * end_p, else break; /* string already filled */ } - to_p= strnmov(to_p, "`", end_p - to_p); + if (end_p > to_p) { + *(to_p++)= (char) quote; + if (end_p > to_p) + *to_p= 0; /* terminate by NUL, but do not include it in the count */ + } } else to_p= strnmov(to_p, conv_name, end_p - to_p); @@ -145,6 +157,7 @@ static char* add_identifier(char *to_p, const char * end_p, diagnostic, error etc. when it would be useful to know what a particular file [and directory] means. Such as SHOW ENGINE STATUS, error messages etc. + @param thd Thread handle @param from Path name in my_charset_filename Null terminated in my_charset_filename, normalized to use '/' as directory separation character. @@ -161,13 +174,12 @@ static char* add_identifier(char *to_p, const char * end_p, [,[ Temporary| Renamed] Partition `p` [, Subpartition `sp`]] *| (| is really a /, and it is all in one line) - EXPLAIN_PARTITIONS_AS_COMMENT_NO_QUOTING -> - same as above but no quotes are added. @retval Length of returned string */ -uint explain_filename(const char *from, +uint explain_filename(THD* thd, + const char *from, char *to, uint to_length, enum_explain_filename_mode explain_mode) @@ -281,14 +293,12 @@ uint explain_filename(const char *from, { to_p= strnmov(to_p, ER(ER_DATABASE_NAME), end_p - to_p); *(to_p++)= ' '; - to_p= add_identifier(to_p, end_p, db_name, db_name_len, 1); + to_p= add_identifier(thd, to_p, end_p, db_name, db_name_len); to_p= strnmov(to_p, ", ", end_p - to_p); } else { - to_p= add_identifier(to_p, end_p, db_name, db_name_len, - (explain_mode != - EXPLAIN_PARTITIONS_AS_COMMENT_NO_QUOTING)); + to_p= add_identifier(thd, to_p, end_p, db_name, db_name_len); to_p= strnmov(to_p, ".", end_p - to_p); } } @@ -296,16 +306,13 @@ uint explain_filename(const char *from, { to_p= strnmov(to_p, ER(ER_TABLE_NAME), end_p - to_p); *(to_p++)= ' '; - to_p= add_identifier(to_p, end_p, table_name, table_name_len, 1); + to_p= add_identifier(thd, to_p, end_p, table_name, table_name_len); } else - to_p= add_identifier(to_p, end_p, table_name, table_name_len, - (explain_mode != - EXPLAIN_PARTITIONS_AS_COMMENT_NO_QUOTING)); + to_p= add_identifier(thd, to_p, end_p, table_name, table_name_len); if (part_name) { - if (explain_mode == EXPLAIN_PARTITIONS_AS_COMMENT || - explain_mode == EXPLAIN_PARTITIONS_AS_COMMENT_NO_QUOTING) + if (explain_mode == EXPLAIN_PARTITIONS_AS_COMMENT) to_p= strnmov(to_p, " /* ", end_p - to_p); else if (explain_mode == EXPLAIN_PARTITIONS_VERBOSE) to_p= strnmov(to_p, " ", end_p - to_p); @@ -321,20 +328,15 @@ uint explain_filename(const char *from, } to_p= strnmov(to_p, ER(ER_PARTITION_NAME), end_p - to_p); *(to_p++)= ' '; - to_p= add_identifier(to_p, end_p, part_name, part_name_len, - (explain_mode != - EXPLAIN_PARTITIONS_AS_COMMENT_NO_QUOTING)); + to_p= add_identifier(thd, to_p, end_p, part_name, part_name_len); if (subpart_name) { to_p= strnmov(to_p, ", ", end_p - to_p); to_p= strnmov(to_p, ER(ER_SUBPARTITION_NAME), end_p - to_p); *(to_p++)= ' '; - to_p= add_identifier(to_p, end_p, subpart_name, subpart_name_len, - (explain_mode != - EXPLAIN_PARTITIONS_AS_COMMENT_NO_QUOTING)); + to_p= add_identifier(thd, to_p, end_p, subpart_name, subpart_name_len); } - if (explain_mode == EXPLAIN_PARTITIONS_AS_COMMENT || - explain_mode == EXPLAIN_PARTITIONS_AS_COMMENT_NO_QUOTING) + if (explain_mode == EXPLAIN_PARTITIONS_AS_COMMENT) to_p= strnmov(to_p, " */", end_p - to_p); } DBUG_PRINT("exit", ("to '%s'", to)); diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index db97e77bbd0..12e124230e5 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -10425,14 +10425,12 @@ load: { THD *thd= YYTHD; LEX *lex= thd->lex; - Lex_input_stream *lip= YYLIP; if (lex->sphead) { my_error(ER_SP_BADSTATEMENT, MYF(0), "LOAD DATA"); MYSQL_YYABORT; } - lex->fname_start= lip->get_ptr(); } load_data {} @@ -10464,14 +10462,10 @@ load_data: if (!(lex->exchange= new sql_exchange($4.str, 0))) MYSQL_YYABORT; } - opt_duplicate INTO - { - Lex->fname_end= YYLIP->get_ptr(); - } - TABLE_SYM table_ident + opt_duplicate INTO TABLE_SYM table_ident { LEX *lex=Lex; - if (!Select->add_table_to_list(YYTHD, $10, NULL, TL_OPTION_UPDATING, + if (!Select->add_table_to_list(YYTHD, $9, NULL, TL_OPTION_UPDATING, lex->lock_option)) MYSQL_YYABORT; lex->field_list.empty(); @@ -10479,7 +10473,7 @@ load_data: lex->value_list.empty(); } opt_load_data_charset - { Lex->exchange->cs= $12; } + { Lex->exchange->cs= $11; } opt_field_term opt_line_term opt_ignore_lines opt_field_or_var_spec opt_load_data_set_spec {} diff --git a/sql/table.cc b/sql/table.cc index 4f647618979..04f2a3fbcf8 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -3338,7 +3338,12 @@ bool TABLE_LIST::prep_check_option(THD *thd, uint8 check_opt_type) /** - Hide errors which show view underlying table information + Hide errors which show view underlying table information. + There are currently two mechanisms at work that handle errors for views, + this one and a more general mechanism based on an Internal_error_handler, + see Show_create_error_handler. The latter handles errors encountered during + execution of SHOW CREATE VIEW, while the machanism using this method is + handles SELECT from views. The two methods should not clash. @param[in,out] thd thread handler @@ -3347,6 +3352,8 @@ bool TABLE_LIST::prep_check_option(THD *thd, uint8 check_opt_type) void TABLE_LIST::hide_view_error(THD *thd) { + if (thd->get_internal_handler()) + return; /* Hide "Unknown column" or "Unknown function" error */ DBUG_ASSERT(thd->is_error()); |