Merge gleb.loc:/home/uchum/work/bk/5.0

into  gleb.loc:/home/uchum/work/bk/5.0-opt

12 files changed, 1172 insertions, 8 deletions

diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index 4947fd7aecc..9d0e41b341a 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -1013,7 +1013,7 @@ c	int(11)	YES		NULL
 drop view v1;
 drop table t1;
 alter database information_schema;
-ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
 drop database information_schema;
 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
 drop table information_schema.tables;
@@ -1326,3 +1326,8 @@ v2	YES
 delete from v1;
 drop view v1,v2;
 drop table t1,t2;
+alter database;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
+alter database test;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
+End of 5.0 tests.
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 4535710c905..f32878309b8 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -735,4 +735,8 @@ COUNT(*)
 3072
 set @@sort_buffer_size=default;
 DROP TABLE t1,t2;
+drop table if exists t1;
+create table t1 (a int) engine=innodb;
+alter table t1 alter a set default 1;
+drop table t1;
 End of 5.0 tests
diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
index b0f3fb77c0e..f1f99012910 100644
--- a/mysql-test/r/query_cache.result
+++ b/mysql-test/r/query_cache.result
@@ -1467,3 +1467,42 @@ insert into t1 values ('c');
 a
 drop table t1;
 set GLOBAL query_cache_size= default;
+Bug#28249 Query Cache returns wrong result with concurrent insert/ certain lock
+set GLOBAL query_cache_type=1;
+set GLOBAL query_cache_limit=10000;
+set GLOBAL query_cache_min_res_unit=0;
+set GLOBAL query_cache_size= 100000;
+flush tables;
+drop table if exists t1, t2;
+create table t1 (a int);
+create table t2 (a int);
+insert into t1 values (1),(2),(3);
+Locking table T2 with a write lock.
+lock table t2 write;
+Select blocked by write lock.
+select *, (select count(*) from t2) from t1;;
+Sleeing is ok, because selecting should be done very fast.
+Inserting into table T1.
+insert into t1 values (4);
+Unlocking the tables.
+unlock tables;
+Collecting result from previously blocked select.
+Next select should contain 4 rows, as the insert is long finished.
+select *, (select count(*) from t2) from t1;
+a	(select count(*) from t2)
+1	0
+2	0
+3	0
+4	0
+reset query cache;
+select *, (select count(*) from t2) from t1;
+a	(select count(*) from t2)
+1	0
+2	0
+3	0
+4	0
+drop table t1,t2;
+set GLOBAL query_cache_type=default;
+set GLOBAL query_cache_limit=default;
+set GLOBAL query_cache_min_res_unit=default;
+set GLOBAL query_cache_size=default;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index b411c65faee..7a4deb3ea5f 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -6176,4 +6176,29 @@ v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VI
 DROP VIEW v1;
 DROP FUNCTION metered;
 DROP TABLE t1;
+drop database if exists mysqltest_db1;
+create database mysqltest_db1;
+create procedure mysqltest_db1.sp_bug28551() begin end;
+call mysqltest_db1.sp_bug28551();
+show warnings;
+Level	Code	Message
+drop database mysqltest_db1;
+drop database if exists mysqltest_db1;
+drop table if exists test.t1;
+create database mysqltest_db1;
+use mysqltest_db1;
+drop database mysqltest_db1;
+create table test.t1 (id int);
+insert into test.t1 (id) values (1);
+create procedure test.sp_bug29050() begin select * from t1; end//
+show warnings;
+Level	Code	Message
+call test.sp_bug29050();
+id
+1
+show warnings;
+Level	Code	Message
+use test;
+drop procedure sp_bug29050;
+drop table t1;
 End of 5.0 tests
diff --git a/mysql-test/r/trigger-trans.result b/mysql-test/r/trigger-trans.result
index b56abf1f59a..cd5f629564f 100644
--- a/mysql-test/r/trigger-trans.result
+++ b/mysql-test/r/trigger-trans.result
@@ -82,3 +82,62 @@ ALICE	33	1	0
 THE CROWN	43	1	0
 THE PIE	53	1	1
 drop table t1;
+
+Bug#26141 mixing table types in trigger causes full
+table lock on innodb table
+
+Ensure we do not open and lock tables for the triggers we do not
+fire.
+
+drop table if exists t1, t2, t3;
+drop trigger if exists trg_bug26141_au;
+drop trigger if exists trg_bug26141_ai;
+create table t1 (c int primary key) engine=innodb;
+create table t2 (c int) engine=myisam;
+create table t3 (c int) engine=myisam;
+insert into t1 (c) values (1);
+create trigger trg_bug26141_ai after insert on t1
+for each row
+begin
+insert into t2 (c) values (1);
+# We need the 'sync' lock to synchronously wait in connection 2 till 
+# the moment when the trigger acquired all the locks.
+select release_lock("lock_bug26141_sync") into @a;
+# 1000 is time in seconds of lock wait timeout -- this is a way
+# to cause a manageable sleep up to 1000 seconds
+select get_lock("lock_bug26141_wait", 1000) into @a;
+end|
+create trigger trg_bug26141_au after update on t1
+for each row
+begin
+insert into t3 (c) values (1);
+end|
+select get_lock("lock_bug26141_wait", 0);
+get_lock("lock_bug26141_wait", 0)
+1
+select get_lock("lock_bug26141_sync", /* must not be priorly locked */ 0);
+get_lock("lock_bug26141_sync", /* must not be priorly locked */ 0)
+1
+insert into t1 (c) values (2);
+select get_lock("lock_bug26141_sync", 1000);
+get_lock("lock_bug26141_sync", 1000)
+1
+update t1 set c=3 where c=1;
+select release_lock("lock_bug26141_sync");
+release_lock("lock_bug26141_sync")
+1
+select release_lock("lock_bug26141_wait");
+release_lock("lock_bug26141_wait")
+1
+select * from t1;
+c
+2
+3
+select * from t2;
+c
+1
+select * from t3;
+c
+1
+drop table t1, t2, t3;
+End of 5.0 tests
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index 5405a632aa4..4b18e525e62 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -351,7 +351,7 @@ create trigger trg1 before insert on mysqltest.t1 for each row set @a:= 1;
 ERROR HY000: Trigger in wrong schema
 use mysqltest;
 create trigger test.trg1 before insert on t1 for each row set @a:= 1;
-ERROR HY000: Trigger in wrong schema
+ERROR 42S02: Table 'test.t1' doesn't exist
 drop database mysqltest;
 use test;
 create table t1 (i int, j int default 10, k int not null, key (k));
@@ -842,7 +842,7 @@ drop table t1;
 create trigger t1_bi before insert on test.t1 for each row set @a:=0;
 ERROR 3D000: No database selected
 create trigger test.t1_bi before insert on t1 for each row set @a:=0;
-ERROR 3D000: No database selected
+ERROR 42S02: Table 'test.t1' doesn't exist
 drop trigger t1_bi;
 ERROR 3D000: No database selected
 create table t1 (id int);
@@ -1476,4 +1476,461 @@ DROP TRIGGER t1_test;
 DROP TABLE t1,t2;
 SET SESSION LOW_PRIORITY_UPDATES=DEFAULT;
 SET GLOBAL LOW_PRIORITY_UPDATES=DEFAULT;
+
+Bug#28502 Triggers that update another innodb table will block
+on X lock unnecessarily
+
+Ensure we do not open and lock tables for triggers we do not fire.
+
+drop table if exists t1, t2;
+drop trigger if exists trg_bug28502_au;
+create table t1 (id int, count int);
+create table t2 (id int);
+create trigger trg_bug28502_au before update on t2
+for each row
+begin
+if (new.id is not null) then
+update t1 set count= count + 1 where id = old.id;
+end if;
+end|
+insert into t1 (id, count) values (1, 0);
+lock table t1 write;
+insert into t2 set id=1;
+unlock tables;
+update t2 set id=1 where id=1;
+select * from t1;
+id	count
+1	1
+select * from t2;
+id
+1
+drop table t1, t2;
+
+Additionally, provide test coverage for triggers and 
+all MySQL data changing commands.
+
+drop table if exists t1, t2, t1_op_log;
+drop view if exists v1;
+drop trigger if exists trg_bug28502_bi;
+drop trigger if exists trg_bug28502_ai;
+drop trigger if exists trg_bug28502_bu;
+drop trigger if exists trg_bug28502_au;
+drop trigger if exists trg_bug28502_bd;
+drop trigger if exists trg_bug28502_ad;
+create table t1 (id int primary key auto_increment, operation varchar(255));
+create table t2 (id int primary key);
+create table t1_op_log(operation varchar(255));
+create view v1 as select * from t1;
+create trigger trg_bug28502_bi before insert on t1
+for each row
+insert into t1_op_log (operation)
+values (concat("Before INSERT, new=", new.operation));
+create trigger trg_bug28502_ai after insert on t1
+for each row
+insert into t1_op_log (operation)
+values (concat("After INSERT, new=", new.operation));
+create trigger trg_bug28502_bu before update on t1
+for each row
+insert into t1_op_log (operation)
+values (concat("Before UPDATE, new=", new.operation,
+", old=", old.operation));
+create trigger trg_bug28502_au after update on t1
+for each row
+insert into t1_op_log (operation)
+values (concat("After UPDATE, new=", new.operation,
+", old=", old.operation));
+create trigger trg_bug28502_bd before delete on t1
+for each row
+insert into t1_op_log (operation)
+values (concat("Before DELETE, old=", old.operation));
+create trigger trg_bug28502_ad after delete on t1
+for each row
+insert into t1_op_log (operation)
+values (concat("After DELETE, old=", old.operation));
+insert into t1 (operation) values ("INSERT");
+set @id=last_insert_id();
+select * from t1;
+id	operation
+1	INSERT
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT
+After INSERT, new=INSERT
+truncate t1_op_log;
+update t1 set operation="UPDATE" where id=@id;
+select * from t1;
+id	operation
+1	UPDATE
+select * from t1_op_log;
+operation
+Before UPDATE, new=UPDATE, old=INSERT
+After UPDATE, new=UPDATE, old=INSERT
+truncate t1_op_log;
+delete from t1 where id=@id;
+select * from t1;
+id	operation
+select * from t1_op_log;
+operation
+Before DELETE, old=UPDATE
+After DELETE, old=UPDATE
+truncate t1;
+truncate t1_op_log;
+insert into t1 (id, operation) values
+(NULL, "INSERT ON DUPLICATE KEY UPDATE, inserting a new key")
+on duplicate key update id=NULL, operation="Should never happen";
+set @id=last_insert_id();
+select * from t1;
+id	operation
+1	INSERT ON DUPLICATE KEY UPDATE, inserting a new key
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
+After INSERT, new=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
+truncate t1_op_log;
+insert into t1 (id, operation) values
+(@id, "INSERT ON DUPLICATE KEY UPDATE, the key value is the same")
+on duplicate key update id=NULL,
+operation="INSERT ON DUPLICATE KEY UPDATE, updating the duplicate";
+select * from t1;
+id	operation
+0	INSERT ON DUPLICATE KEY UPDATE, updating the duplicate
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT ON DUPLICATE KEY UPDATE, the key value is the same
+Before UPDATE, new=INSERT ON DUPLICATE KEY UPDATE, updating the duplicate, old=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
+After UPDATE, new=INSERT ON DUPLICATE KEY UPDATE, updating the duplicate, old=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
+truncate t1;
+truncate t1_op_log;
+replace into t1 values (NULL, "REPLACE, inserting a new key");
+set @id=last_insert_id();
+select * from t1;
+id	operation
+1	REPLACE, inserting a new key
+select * from t1_op_log;
+operation
+Before INSERT, new=REPLACE, inserting a new key
+After INSERT, new=REPLACE, inserting a new key
+truncate t1_op_log;
+replace into t1 values (@id, "REPLACE, deleting the duplicate");
+select * from t1;
+id	operation
+1	REPLACE, deleting the duplicate
+select * from t1_op_log;
+operation
+Before INSERT, new=REPLACE, deleting the duplicate
+Before DELETE, old=REPLACE, inserting a new key
+After DELETE, old=REPLACE, inserting a new key
+After INSERT, new=REPLACE, deleting the duplicate
+truncate t1;
+truncate t1_op_log;
+create table if not exists t1
+select NULL, "CREATE TABLE ... SELECT, inserting a new key";
+Warnings:
+Note	1050	Table 't1' already exists
+set @id=last_insert_id();
+select * from t1;
+id	operation
+1	CREATE TABLE ... SELECT, inserting a new key
+select * from t1_op_log;
+operation
+Before INSERT, new=CREATE TABLE ... SELECT, inserting a new key
+After INSERT, new=CREATE TABLE ... SELECT, inserting a new key
+truncate t1_op_log;
+create table if not exists t1 replace
+select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";
+Warnings:
+Note	1050	Table 't1' already exists
+select * from t1;
+id	operation
+1	CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
+select * from t1_op_log;
+operation
+Before INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
+Before DELETE, old=CREATE TABLE ... SELECT, inserting a new key
+After DELETE, old=CREATE TABLE ... SELECT, inserting a new key
+After INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
+truncate t1;
+truncate t1_op_log;
+insert into t1 (id, operation)
+select NULL, "INSERT ... SELECT, inserting a new key";
+set @id=last_insert_id();
+select * from t1;
+id	operation
+1	INSERT ... SELECT, inserting a new key
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT ... SELECT, inserting a new key
+After INSERT, new=INSERT ... SELECT, inserting a new key
+truncate t1_op_log;
+insert into t1 (id, operation)
+select @id,
+"INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate"
+on duplicate key update id=NULL,
+operation="INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate";
+select * from t1;
+id	operation
+0	INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate
+Before UPDATE, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate, old=INSERT ... SELECT, inserting a new key
+After UPDATE, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate, old=INSERT ... SELECT, inserting a new key
+truncate t1;
+truncate t1_op_log;
+replace into t1 (id, operation)
+select NULL, "REPLACE ... SELECT, inserting a new key";
+set @id=last_insert_id();
+select * from t1;
+id	operation
+1	REPLACE ... SELECT, inserting a new key
+select * from t1_op_log;
+operation
+Before INSERT, new=REPLACE ... SELECT, inserting a new key
+After INSERT, new=REPLACE ... SELECT, inserting a new key
+truncate t1_op_log;
+replace into t1 (id, operation)
+select @id, "REPLACE ... SELECT, deleting a duplicate";
+select * from t1;
+id	operation
+1	REPLACE ... SELECT, deleting a duplicate
+select * from t1_op_log;
+operation
+Before INSERT, new=REPLACE ... SELECT, deleting a duplicate
+Before DELETE, old=REPLACE ... SELECT, inserting a new key
+After DELETE, old=REPLACE ... SELECT, inserting a new key
+After INSERT, new=REPLACE ... SELECT, deleting a duplicate
+truncate t1;
+truncate t1_op_log;
+insert into t1 (id, operation) values (1, "INSERT for multi-DELETE");
+insert into t2 (id) values (1);
+delete t1.*, t2.* from t1, t2 where t1.id=1;
+select * from t1;
+id	operation
+select * from t2;
+id
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT for multi-DELETE
+After INSERT, new=INSERT for multi-DELETE
+Before DELETE, old=INSERT for multi-DELETE
+After DELETE, old=INSERT for multi-DELETE
+truncate t1;
+truncate t2;
+truncate t1_op_log;
+insert into t1 (id, operation) values (1, "INSERT for multi-UPDATE");
+insert into t2 (id) values (1);
+update t1, t2 set t1.id=2, operation="multi-UPDATE" where t1.id=1;
+update t1, t2
+set t2.id=3, operation="multi-UPDATE, SET for t2, but the trigger is fired" where t1.id=2;
+select * from t1;
+id	operation
+2	multi-UPDATE, SET for t2, but the trigger is fired
+select * from t2;
+id
+3
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT for multi-UPDATE
+After INSERT, new=INSERT for multi-UPDATE
+Before UPDATE, new=multi-UPDATE, old=INSERT for multi-UPDATE
+After UPDATE, new=multi-UPDATE, old=INSERT for multi-UPDATE
+Before UPDATE, new=multi-UPDATE, SET for t2, but the trigger is fired, old=multi-UPDATE
+After UPDATE, new=multi-UPDATE, SET for t2, but the trigger is fired, old=multi-UPDATE
+truncate table t1;
+truncate table t2;
+truncate table t1_op_log;
+
+Now do the same but use a view instead of the base table.
+
+insert into v1 (operation) values ("INSERT");
+set @id=last_insert_id();
+select * from t1;
+id	operation
+1	INSERT
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT
+After INSERT, new=INSERT
+truncate t1_op_log;
+update v1 set operation="UPDATE" where id=@id;
+select * from t1;
+id	operation
+1	UPDATE
+select * from t1_op_log;
+operation
+Before UPDATE, new=UPDATE, old=INSERT
+After UPDATE, new=UPDATE, old=INSERT
+truncate t1_op_log;
+delete from v1 where id=@id;
+select * from t1;
+id	operation
+select * from t1_op_log;
+operation
+Before DELETE, old=UPDATE
+After DELETE, old=UPDATE
+truncate t1;
+truncate t1_op_log;
+insert into v1 (id, operation) values
+(NULL, "INSERT ON DUPLICATE KEY UPDATE, inserting a new key")
+on duplicate key update id=NULL, operation="Should never happen";
+set @id=last_insert_id();
+select * from t1;
+id	operation
+1	INSERT ON DUPLICATE KEY UPDATE, inserting a new key
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
+After INSERT, new=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
+truncate t1_op_log;
+insert into v1 (id, operation) values
+(@id, "INSERT ON DUPLICATE KEY UPDATE, the key value is the same")
+on duplicate key update id=NULL,
+operation="INSERT ON DUPLICATE KEY UPDATE, updating the duplicate";
+select * from t1;
+id	operation
+0	INSERT ON DUPLICATE KEY UPDATE, updating the duplicate
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT ON DUPLICATE KEY UPDATE, the key value is the same
+Before UPDATE, new=INSERT ON DUPLICATE KEY UPDATE, updating the duplicate, old=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
+After UPDATE, new=INSERT ON DUPLICATE KEY UPDATE, updating the duplicate, old=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
+truncate t1;
+truncate t1_op_log;
+replace into v1 values (NULL, "REPLACE, inserting a new key");
+set @id=last_insert_id();
+select * from t1;
+id	operation
+1	REPLACE, inserting a new key
+select * from t1_op_log;
+operation
+Before INSERT, new=REPLACE, inserting a new key
+After INSERT, new=REPLACE, inserting a new key
+truncate t1_op_log;
+replace into v1 values (@id, "REPLACE, deleting the duplicate");
+select * from t1;
+id	operation
+1	REPLACE, deleting the duplicate
+select * from t1_op_log;
+operation
+Before INSERT, new=REPLACE, deleting the duplicate
+Before DELETE, old=REPLACE, inserting a new key
+After DELETE, old=REPLACE, inserting a new key
+After INSERT, new=REPLACE, deleting the duplicate
+truncate t1;
+truncate t1_op_log;
+create table if not exists v1
+select NULL, "CREATE TABLE ... SELECT, inserting a new key";
+Warnings:
+Note	1050	Table 'v1' already exists
+set @id=last_insert_id();
+select * from t1;
+id	operation
+1	CREATE TABLE ... SELECT, inserting a new key
+select * from t1_op_log;
+operation
+Before INSERT, new=CREATE TABLE ... SELECT, inserting a new key
+After INSERT, new=CREATE TABLE ... SELECT, inserting a new key
+truncate t1_op_log;
+create table if not exists v1 replace
+select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";
+Warnings:
+Note	1050	Table 'v1' already exists
+select * from t1;
+id	operation
+1	CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
+select * from t1_op_log;
+operation
+Before INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
+Before DELETE, old=CREATE TABLE ... SELECT, inserting a new key
+After DELETE, old=CREATE TABLE ... SELECT, inserting a new key
+After INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
+truncate t1;
+truncate t1_op_log;
+insert into v1 (id, operation)
+select NULL, "INSERT ... SELECT, inserting a new key";
+set @id=last_insert_id();
+select * from t1;
+id	operation
+1	INSERT ... SELECT, inserting a new key
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT ... SELECT, inserting a new key
+After INSERT, new=INSERT ... SELECT, inserting a new key
+truncate t1_op_log;
+insert into v1 (id, operation)
+select @id,
+"INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate"
+on duplicate key update id=NULL,
+operation="INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate";
+select * from t1;
+id	operation
+0	INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate
+Before UPDATE, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate, old=INSERT ... SELECT, inserting a new key
+After UPDATE, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate, old=INSERT ... SELECT, inserting a new key
+truncate t1;
+truncate t1_op_log;
+replace into v1 (id, operation)
+select NULL, "REPLACE ... SELECT, inserting a new key";
+set @id=last_insert_id();
+select * from t1;
+id	operation
+1	REPLACE ... SELECT, inserting a new key
+select * from t1_op_log;
+operation
+Before INSERT, new=REPLACE ... SELECT, inserting a new key
+After INSERT, new=REPLACE ... SELECT, inserting a new key
+truncate t1_op_log;
+replace into v1 (id, operation)
+select @id, "REPLACE ... SELECT, deleting a duplicate";
+select * from t1;
+id	operation
+1	REPLACE ... SELECT, deleting a duplicate
+select * from t1_op_log;
+operation
+Before INSERT, new=REPLACE ... SELECT, deleting a duplicate
+Before DELETE, old=REPLACE ... SELECT, inserting a new key
+After DELETE, old=REPLACE ... SELECT, inserting a new key
+After INSERT, new=REPLACE ... SELECT, deleting a duplicate
+truncate t1;
+truncate t1_op_log;
+insert into v1 (id, operation) values (1, "INSERT for multi-DELETE");
+insert into t2 (id) values (1);
+delete v1.*, t2.* from v1, t2 where v1.id=1;
+select * from t1;
+id	operation
+select * from t2;
+id
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT for multi-DELETE
+After INSERT, new=INSERT for multi-DELETE
+Before DELETE, old=INSERT for multi-DELETE
+After DELETE, old=INSERT for multi-DELETE
+truncate t1;
+truncate t2;
+truncate t1_op_log;
+insert into v1 (id, operation) values (1, "INSERT for multi-UPDATE");
+insert into t2 (id) values (1);
+update v1, t2 set v1.id=2, operation="multi-UPDATE" where v1.id=1;
+update v1, t2
+set t2.id=3, operation="multi-UPDATE, SET for t2, but the trigger is fired" where v1.id=2;
+select * from t1;
+id	operation
+2	multi-UPDATE, SET for t2, but the trigger is fired
+select * from t2;
+id
+3
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT for multi-UPDATE
+After INSERT, new=INSERT for multi-UPDATE
+Before UPDATE, new=multi-UPDATE, old=INSERT for multi-UPDATE
+After UPDATE, new=multi-UPDATE, old=INSERT for multi-UPDATE
+Before UPDATE, new=multi-UPDATE, SET for t2, but the trigger is fired, old=multi-UPDATE
+After UPDATE, new=multi-UPDATE, SET for t2, but the trigger is fired, old=multi-UPDATE
+drop view v1;
+drop table t1, t2, t1_op_log;
 End of 5.0 tests
diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test
index 1d368ac6075..6cf4ad8f576 100644
--- a/mysql-test/t/information_schema.test
+++ b/mysql-test/t/information_schema.test
@@ -697,7 +697,7 @@ drop table t1;
 #
 # Bug #9846 Inappropriate error displayed while dropping table from 'INFORMATION_SCHEMA'
 #
---error 1044
+--error ER_PARSE_ERROR
 alter database information_schema;
 --error 1044
 drop database information_schema;
@@ -1038,4 +1038,11 @@ delete from v1;
 drop view v1,v2;
 drop table t1,t2;
 
-# End of 5.0 tests.
+#
+# Bug#25859    ALTER DATABASE works w/o parameters
+#
+--error ER_PARSE_ERROR
+alter database;
+--error ER_PARSE_ERROR
+alter database test;
+--echo End of 5.0 tests.
diff --git a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
index d4ce997ddb1..0d43d13ec3a 100644
--- a/mysql-test/t/innodb_mysql.test
+++ b/mysql-test/t/innodb_mysql.test
@@ -740,5 +740,18 @@ SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
 set @@sort_buffer_size=default;
 
 DROP TABLE t1,t2;
+# Bug#27296 Assertion in ALTER TABLE SET DEFAULT in Linux Debug build
+# (possible deadlock).
+#
+# The bug is applicable only to a transactoinal table.
+# Cover with tests behavior that no longer causes an
+# assertion.
+#
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int) engine=innodb;
+alter table t1 alter a set default 1;
+drop table t1;
 
 --echo End of 5.0 tests
diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
index 965ebf5df62..962f53936a3 100644
--- a/mysql-test/t/query_cache.test
+++ b/mysql-test/t/query_cache.test
@@ -1028,4 +1028,77 @@ drop table t1;
 
 set GLOBAL query_cache_size= default;
 
+#
+# Bug #28249 Query Cache returns wrong result with concurrent insert / certain lock
+#
+--echo Bug#28249 Query Cache returns wrong result with concurrent insert/ certain lock
+connect (user1,localhost,root,,test,,);
+connect (user2,localhost,root,,test,,);
+connect (user3,localhost,root,,test,,);
+
+connection user1;
+
+set GLOBAL query_cache_type=1;
+set GLOBAL query_cache_limit=10000;
+set GLOBAL query_cache_min_res_unit=0;
+set GLOBAL query_cache_size= 100000;
+
+flush tables;
+--disable_warnings
+drop table if exists t1, t2;
+--enable_warnings
+create table t1 (a int);
+create table t2 (a int);
+insert into t1 values (1),(2),(3);
+connection user2;
+--echo Locking table T2 with a write lock.
+lock table t2 write;
+
+connection user1;
+--echo Select blocked by write lock.
+--send select *, (select count(*) from t2) from t1;
+--echo Sleeing is ok, because selecting should be done very fast.
+sleep 5;
+
+connection user3;
+--echo Inserting into table T1.
+insert into t1 values (4);
+
+connection user2;
+--echo Unlocking the tables.
+unlock tables;
+
+connection user1;
+--echo Collecting result from previously blocked select.
+# 
+# Since the lock ordering rule in thr_multi_lock depends on
+# pointer values, from execution to execution we might have
+# different lock order, and therefore, sometimes lock t1 and block
+# on t2, and sometimes block on t2 right away. In the second case,
+# the following insert succeeds, and only then this select can
+# proceed, and we actually test nothing, as the very first select
+# returns 4 rows right away.
+# It's fine to have a test case that covers the problematic area
+# at least once in a while.
+# We, however, need to disable the result log here to make the
+# test repeatable.
+--disable_result_log
+--reap
+--enable_result_log
+--echo Next select should contain 4 rows, as the insert is long finished.
+select *, (select count(*) from t2) from t1;
+reset query cache;
+select *, (select count(*) from t2) from t1;
+
+drop table t1,t2;
+
+connection default;
+disconnect user1;
+disconnect user2;
+disconnect user3;
+set GLOBAL query_cache_type=default;
+set GLOBAL query_cache_limit=default;
+set GLOBAL query_cache_min_res_unit=default;
+set GLOBAL query_cache_size=default;
 # End of 5.0 tests
+
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 2f82482bdf7..ef66f1c98f0 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -7134,5 +7134,42 @@ DROP VIEW v1;
 DROP FUNCTION metered;
 DROP TABLE t1;
 
+#
+# Bug#28551 "The warning 'No database selected' is reported when calling
+# stored procedures"
+#
+--disable_warnings
+drop database if exists mysqltest_db1;
+--enable_warnings
+create database mysqltest_db1;
+create procedure mysqltest_db1.sp_bug28551() begin end;
+call mysqltest_db1.sp_bug28551();
+show warnings;
+drop database mysqltest_db1;
+#
+# Bug#29050 Creation of a legal stored procedure fails if a database is not
+# selected prior
+#
+--disable_warnings
+drop database if exists mysqltest_db1;
+drop table if exists test.t1;
+--enable_warnings
+create database mysqltest_db1;
+use mysqltest_db1;
+# For the sake of its side effect
+drop database mysqltest_db1; 
+# Now we have no current database selected.
+create table test.t1 (id int);
+insert into test.t1 (id) values (1);
+delimiter //;
+create procedure test.sp_bug29050() begin select * from t1; end//
+delimiter ;//
+show warnings;
+call test.sp_bug29050();
+show warnings;
+# Restore the old current database
+use test;
+drop procedure sp_bug29050;
+drop table t1;
 
 --echo End of 5.0 tests
diff --git a/mysql-test/t/trigger-trans.test b/mysql-test/t/trigger-trans.test
index 5c135d98878..8103a1ba0b1 100644
--- a/mysql-test/t/trigger-trans.test
+++ b/mysql-test/t/trigger-trans.test
@@ -49,4 +49,84 @@ insert into t1 values ('The Pie', 50, 1, 1);
 select * from t1;
 drop table t1;
 
-# End of 5.0 tests
+--echo
+--echo Bug#26141 mixing table types in trigger causes full
+--echo table lock on innodb table
+--echo
+--echo Ensure we do not open and lock tables for the triggers we do not
+--echo fire.
+--echo
+--disable_warnings
+drop table if exists t1, t2, t3;
+drop trigger if exists trg_bug26141_au;
+drop trigger if exists trg_bug26141_ai;
+--enable_warnings
+# Note, for InnoDB to allow concurrent UPDATE and INSERT the
+# table must have a unique key.
+create table t1 (c int primary key) engine=innodb;
+create table t2 (c int) engine=myisam;
+create table t3 (c int) engine=myisam;
+insert into t1 (c) values (1);
+delimiter |;
+
+create trigger trg_bug26141_ai after insert on t1
+for each row
+begin
+  insert into t2 (c) values (1);
+# We need the 'sync' lock to synchronously wait in connection 2 till 
+# the moment when the trigger acquired all the locks.
+  select release_lock("lock_bug26141_sync") into @a;
+# 1000 is time in seconds of lock wait timeout -- this is a way
+# to cause a manageable sleep up to 1000 seconds
+  select get_lock("lock_bug26141_wait", 1000) into @a;
+end|
+
+create trigger trg_bug26141_au after update on t1
+for each row
+begin
+  insert into t3 (c) values (1);
+end|
+delimiter ;|
+
+# Establish an alternative connection.
+--connect (connection_aux,localhost,root,,test,,)
+--connect (connection_update,localhost,root,,test,,)
+
+connection connection_aux;
+# Lock the wait lock, it must not be locked, so specify zero timeout.
+select get_lock("lock_bug26141_wait", 0);
+
+#
+connection default;
+#
+# Run the trigger synchronously 
+#
+select get_lock("lock_bug26141_sync", /* must not be priorly locked */ 0);
+# Will acquire the table level locks, perform the insert into t2,
+# release the sync lock and block on the wait lock.
+send insert into t1 (c) values (2);
+
+connection connection_update;
+# Wait for the trigger to acquire its locks and unlock the sync lock.
+select get_lock("lock_bug26141_sync", 1000); 
+#
+# This must continue: after the fix for the bug, we do not
+# open tables for t2, and with c=4 innobase allows the update
+# to run concurrently with insert.
+update t1 set c=3 where c=1;
+select release_lock("lock_bug26141_sync"); 
+connection connection_aux;
+select release_lock("lock_bug26141_wait");
+connection default;
+reap;
+select * from t1;
+select * from t2;
+select * from t3;
+
+# Drops the trigger as well.
+drop table t1, t2, t3;
+disconnect connection_update;
+disconnect connection_aux;
+
+
+--echo End of 5.0 tests
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index 7158d02956e..a6390036322 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -406,7 +406,7 @@ create table mysqltest.t1 (i int);
 --error ER_TRG_IN_WRONG_SCHEMA
 create trigger trg1 before insert on mysqltest.t1 for each row set @a:= 1;
 use mysqltest;
---error ER_TRG_IN_WRONG_SCHEMA
+--error ER_NO_SUCH_TABLE
 create trigger test.trg1 before insert on t1 for each row set @a:= 1;
 drop database mysqltest;
 use test;
@@ -1040,7 +1040,7 @@ drop table t1;
 connection addconwithoutdb;
 --error ER_NO_DB_ERROR
 create trigger t1_bi before insert on test.t1 for each row set @a:=0;
---error ER_NO_DB_ERROR
+--error ER_NO_SUCH_TABLE
 create trigger test.t1_bi before insert on t1 for each row set @a:=0;
 --error ER_NO_DB_ERROR
 drop trigger t1_bi;
@@ -1828,5 +1828,370 @@ DROP TRIGGER t1_test;
 DROP TABLE t1,t2;
 SET SESSION LOW_PRIORITY_UPDATES=DEFAULT;
 SET GLOBAL LOW_PRIORITY_UPDATES=DEFAULT;
+--echo
+--echo Bug#28502 Triggers that update another innodb table will block
+--echo on X lock unnecessarily
+--echo
+--echo Ensure we do not open and lock tables for triggers we do not fire.
+--echo
+--disable_warnings
+drop table if exists t1, t2;
+drop trigger if exists trg_bug28502_au;
+--enable_warnings
+
+create table t1 (id int, count int);
+create table t2 (id int);
+delimiter |;
+
+create trigger trg_bug28502_au before update on t2
+for each row
+begin
+  if (new.id is not null) then
+    update t1 set count= count + 1 where id = old.id;
+  end if;
+end|
+
+delimiter ;|
+insert into t1 (id, count) values (1, 0);
+
+lock table t1 write;
+
+--connect (connection_insert, localhost, root, , test, , )
+connection connection_insert;
+# Is expected to pass.
+insert into t2 set id=1;
+connection default;
+unlock tables;
+update t2 set id=1 where id=1;
+select * from t1;
+select * from t2;
+# Will drop the trigger
+drop table t1, t2;
+disconnect connection_insert;
+--echo
+--echo Additionally, provide test coverage for triggers and 
+--echo all MySQL data changing commands.
+--echo
+--disable_warnings
+drop table if exists t1, t2, t1_op_log;
+drop view if exists v1;
+drop trigger if exists trg_bug28502_bi;
+drop trigger if exists trg_bug28502_ai;
+drop trigger if exists trg_bug28502_bu;
+drop trigger if exists trg_bug28502_au;
+drop trigger if exists trg_bug28502_bd;
+drop trigger if exists trg_bug28502_ad;
+--enable_warnings
+create table t1 (id int primary key auto_increment, operation varchar(255));
+create table t2 (id int primary key);
+create table t1_op_log(operation varchar(255));
+create view v1 as select * from t1;
+create trigger trg_bug28502_bi before insert on t1
+for each row
+  insert into t1_op_log (operation)
+  values (concat("Before INSERT, new=", new.operation));
+
+create trigger trg_bug28502_ai after insert on t1
+for each row
+  insert into t1_op_log (operation)
+  values (concat("After INSERT, new=", new.operation));
+
+create trigger trg_bug28502_bu before update on t1
+for each row
+  insert into t1_op_log (operation)
+  values (concat("Before UPDATE, new=", new.operation,
+                 ", old=", old.operation));
+
+create trigger trg_bug28502_au after update on t1
+for each row
+  insert into t1_op_log (operation)
+  values (concat("After UPDATE, new=", new.operation,
+                 ", old=", old.operation));
+
+create trigger trg_bug28502_bd before delete on t1
+for each row
+  insert into t1_op_log (operation)
+  values (concat("Before DELETE, old=", old.operation));
+
+create trigger trg_bug28502_ad after delete on t1
+for each row
+  insert into t1_op_log (operation)
+  values (concat("After DELETE, old=", old.operation));
+
+insert into t1 (operation) values ("INSERT");
+
+set @id=last_insert_id();
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+update t1 set operation="UPDATE" where id=@id;
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+delete from t1 where id=@id;
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+insert into t1 (id, operation) values
+(NULL, "INSERT ON DUPLICATE KEY UPDATE, inserting a new key")
+on duplicate key update id=NULL, operation="Should never happen";
+
+set @id=last_insert_id();
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+insert into t1 (id, operation) values
+(@id, "INSERT ON DUPLICATE KEY UPDATE, the key value is the same")
+on duplicate key update id=NULL,
+operation="INSERT ON DUPLICATE KEY UPDATE, updating the duplicate";
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+replace into t1 values (NULL, "REPLACE, inserting a new key");
+
+set @id=last_insert_id();
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+replace into t1 values (@id, "REPLACE, deleting the duplicate");
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+create table if not exists t1
+select NULL, "CREATE TABLE ... SELECT, inserting a new key";
+
+set @id=last_insert_id();
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+create table if not exists t1 replace
+select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+insert into t1 (id, operation)
+select NULL, "INSERT ... SELECT, inserting a new key";
+
+set @id=last_insert_id();
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+insert into t1 (id, operation)
+select @id,
+"INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate"
+on duplicate key update id=NULL,
+operation="INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate";
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+replace into t1 (id, operation)
+select NULL, "REPLACE ... SELECT, inserting a new key";
+
+set @id=last_insert_id();
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+replace into t1 (id, operation)
+select @id, "REPLACE ... SELECT, deleting a duplicate";
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+insert into t1 (id, operation) values (1, "INSERT for multi-DELETE");
+insert into t2 (id) values (1);
+
+delete t1.*, t2.* from t1, t2 where t1.id=1;
+
+select * from t1;
+select * from t2;
+select * from t1_op_log;
+truncate t1;
+truncate t2;
+truncate t1_op_log;
+
+insert into t1 (id, operation) values (1, "INSERT for multi-UPDATE");
+insert into t2 (id) values (1);
+update t1, t2 set t1.id=2, operation="multi-UPDATE" where t1.id=1;
+update t1, t2
+set t2.id=3, operation="multi-UPDATE, SET for t2, but the trigger is fired" where t1.id=2;
+
+select * from t1;
+select * from t2;
+select * from t1_op_log;
+truncate table t1;
+truncate table t2;
+truncate table t1_op_log;
+
+--echo
+--echo Now do the same but use a view instead of the base table.
+--echo
+
+insert into v1 (operation) values ("INSERT");
+
+set @id=last_insert_id();
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+update v1 set operation="UPDATE" where id=@id;
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+delete from v1 where id=@id;
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+insert into v1 (id, operation) values
+(NULL, "INSERT ON DUPLICATE KEY UPDATE, inserting a new key")
+on duplicate key update id=NULL, operation="Should never happen";
+
+set @id=last_insert_id();
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+insert into v1 (id, operation) values
+(@id, "INSERT ON DUPLICATE KEY UPDATE, the key value is the same")
+on duplicate key update id=NULL,
+operation="INSERT ON DUPLICATE KEY UPDATE, updating the duplicate";
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+replace into v1 values (NULL, "REPLACE, inserting a new key");
+
+set @id=last_insert_id();
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+replace into v1 values (@id, "REPLACE, deleting the duplicate");
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+create table if not exists v1
+select NULL, "CREATE TABLE ... SELECT, inserting a new key";
+
+set @id=last_insert_id();
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+create table if not exists v1 replace
+select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+insert into v1 (id, operation)
+select NULL, "INSERT ... SELECT, inserting a new key";
+
+set @id=last_insert_id();
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+insert into v1 (id, operation)
+select @id,
+"INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate"
+on duplicate key update id=NULL,
+operation="INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate";
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+replace into v1 (id, operation)
+select NULL, "REPLACE ... SELECT, inserting a new key";
+
+set @id=last_insert_id();
 
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+replace into v1 (id, operation)
+select @id, "REPLACE ... SELECT, deleting a duplicate";
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+insert into v1 (id, operation) values (1, "INSERT for multi-DELETE");
+insert into t2 (id) values (1);
+
+delete v1.*, t2.* from v1, t2 where v1.id=1;
+
+select * from t1;
+select * from t2;
+select * from t1_op_log;
+truncate t1;
+truncate t2;
+truncate t1_op_log;
+
+insert into v1 (id, operation) values (1, "INSERT for multi-UPDATE");
+insert into t2 (id) values (1);
+update v1, t2 set v1.id=2, operation="multi-UPDATE" where v1.id=1;
+update v1, t2
+set t2.id=3, operation="multi-UPDATE, SET for t2, but the trigger is fired" where v1.id=2;
+
+select * from t1;
+select * from t2;
+select * from t1_op_log;
+
+drop view v1;
+drop table t1, t2, t1_op_log;
+
+#
+# TODO: test LOAD DATA INFILE
 --echo End of 5.0 tests