From b1ec3b534d530807995955c1c249496b0912a906 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 5 Jul 2007 02:20:32 +0400 Subject: A fix and a teset case for Bug#28551 The warning 'No database selected' is reported when calling stored procedures Remove the offending warning introduced by the fix for Bug 25082 This minimal patch relies on the intrinsic knowledge of the fact that mysql_change_db is never called with 'force_switch' set to TRUE when such a warning may be needed: * every stored routine belongs to a database (unlike, e.g., a user defined function, which does not), so if we're activating the database of a stored routine, it can never be NULL. Therefore, this branch is never called for activation. * if we're restoring the 'old' current database after routine execution is complete, we should not issue a warning, since it's OK to call a routine without having previously selected the current database. TODO: 'force_switch' is an ambiguous flag, since we do not actually have to 'force' the switch in case of stored routines at all. When we activate the routine's database, we should perform all the checks as in case of 'use db', and so we already do (in this case 'force_switch' is unused). When we load a routine into cache, we should not use mysql_change_db at all, since there it's enough to call thd->reset_db(). We do it this way for triggers, but code for routines is different (wrongly). TODO: bugs are lurking in replication, since it bypasses mysql_change_db and calls thd->[re_]set_db to set the current database. The latter does not change thd->db_charset, thd->sctx->db_access and thd->variables.collation_database (and this may have nasty side effects). These todo items are to be addressed in a separate patch, if at all. mysql-test/r/sp.result: Update results (Bug#28551) mysql-test/t/sp.test: Add a test case (Bug#28551) sql/sp.cc: Remove an obsolete comment. Replace a check with an assert. sql/sql_db.cc: Remove the offending warning introduced by the fix for Bug 25082 This minimal patch relies on the intrinsic knowledge of the fact that mysql_change_db is never called with 'force_switch' set to TRUE when such a warning may be needed. --- mysql-test/r/sp.result | 7 +++++++ 1 file changed, 7 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index b411c65faee..5d7371b0991 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -6176,4 +6176,11 @@ 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; End of 5.0 tests -- cgit v1.2.1 From e8966deecc0eda77c5b94c6f0004cc2159dbd158 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 5 Jul 2007 11:34:04 +0400 Subject: A fix and a test case for Bug#29050 Creation of a legal stored procedure fails if a database is not selected prior. The problem manifested itself when a user tried to create a routine that had non-fully-qualified identifiers in its bodies and there was no current database selected. This is a regression introduced by the fix for Bug 19022: The patch for Bug 19022 changes the code to always produce a warning if we can't resolve the current database in the parser. In this case this was not necessary, since even though the produced parsed tree was incorrect, we never re-use sphead that was obtained at first parsing of CREATE PROCEDURE. The sphead that is anyhow used is always obtained through db_load_routine, and there we change the current database to sphead->m_db before calling yyparse. The idea of the fix is to resolve the current database directly using lex->sphead->m_db member when parsing a stored routine body, when such is present. This patch removes the need to reset the current database when loading a trigger or routine definition into SP cache. The redundant code will be removed in 5.1. mysql-test/r/sp.result: Update test results (Bug#29050) mysql-test/r/trigger.result: Update results. mysql-test/t/sp.test: Add a test case for Bug#29050 mysql-test/t/trigger.test: Fix wrong behavior covered with tests. sql/sql_lex.cc: Implement st_lex::copy_db_to(). sql/sql_lex.h: Declare st_lex::copy_db_to(). sql/sql_parse.cc: Use st_lex::copy_db_to() in add_table_to_list, rather than THD::copy_db_to(). The former will use the database of the sphead, if we're parsing a stored routine, not the default database in THD. The default database is needed to initialize tables->db when the database part was not explicitly specified in the identifier. sql/sql_yacc.yy: Use st_lex::copy_db_to() in the parser, rather than THD::copy_db_to(). The former will use the database of the sphead, if we're parsing a stored routine, not the default database in THD. --- mysql-test/r/sp.result | 18 ++++++++++++++++++ mysql-test/r/trigger.result | 4 ++-- 2 files changed, 20 insertions(+), 2 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 5d7371b0991..7a4deb3ea5f 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -6183,4 +6183,22 @@ 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.result b/mysql-test/r/trigger.result index 5405a632aa4..290929d476d 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); -- cgit v1.2.1 From 0bc3e69f92497c04cdd48e7936888183fd8a21d5 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 12 Jul 2007 01:10:29 +0400 Subject: A fix and a test case for Bug#25859 ALTER DATABASE works w/o parameters. Fix the parser to make the database options not optional. mysql-test/r/information_schema.result: Update results (Bug#25859) mysql-test/t/information_schema.test: Add a test case for Bug#25859 "ALTER DATABASE works w/o parameters" sql/sql_yacc.yy: Fix Bug#25859 ALTER DATABASE works w/o parameters - require parameters in the parser. --- mysql-test/r/information_schema.result | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-) (limited to 'mysql-test/r') 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. -- cgit v1.2.1 From 6ba23b0ac9a4548de36386b4f892af9c7d471e97 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 12 Jul 2007 12:49:39 +0400 Subject: Fix for 5.1 for BUG#10491: Server returns data as charset binary SHOW CREATE TABLE or SELECT FROM I_S. This is the last patch for this bug, which depends on the big CS patch and was pending. The problem was that SHOW CREATE statements returned original queries in the binary character set. That could cause the query to be unreadable. The fix is to use original character_set_client when sending the original query to the client. In order to preserve the query in mysqldump, 'binary' character set results should be set when issuing SHOW CREATE statement. If either source or destination character set is 'binary' , no conversion is performed. The idea is that since the source character set is no longer 'binary', we fix the destination character set to still produce valid dumps. client/mysqldump.c: Switch character_set_results of mysqldump-connection before calling SHOW CREATE statements for the objects. mysql-test/r/show_check.result: Result file. mysql-test/t/show_check.test: Add test case for the part of BUG#10491. sql/events.cc: Send original query in the original character set. sql/sp_head.cc: Send original query in the original character set. sql/sql_show.cc: Send original query in the original character set. --- mysql-test/r/show_check.result | 39 +++++++++++++++++++++++++++++++++++++++ 1 file changed, 39 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index 9ef10865cd7..c89f386623b 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -1282,4 +1282,43 @@ t1_bi CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR E DROP TABLE t1; DROP PROCEDURE p1; DEALLOCATE PREPARE stmt1; +set names koi8r; +DROP VIEW IF EXISTS v1; +DROP PROCEDURE IF EXISTS p1; +DROP FUNCTION IF EXISTS f1; +DROP TABLE IF EXISTS t1; +DROP EVENT IF EXISTS ev1; +CREATE VIEW v1 AS SELECT 'ΤΕΣΤ' AS test; +CREATE PROCEDURE p1() SELECT 'ΤΕΣΤ' AS test; +CREATE FUNCTION f1() RETURNS CHAR(10) RETURN 'ΤΕΣΤ'; +CREATE TABLE t1(c1 CHAR(10)); +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 +FOR EACH ROW +SET NEW.c1 = 'ΤΕΣΤ'; +CREATE EVENT ev1 ON SCHEDULE AT '2030-01-01 00:00:00' DO SELECT 'ΤΕΣΤ' AS test; +set names utf8; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select _koi8r'тСст' AS `test` koi8r koi8r_general_ci +SHOW CREATE PROCEDURE p1; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +p1 CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() +SELECT 'тСст' AS test koi8r koi8r_general_ci latin1_swedish_ci +SHOW CREATE FUNCTION f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS char(10) CHARSET latin1 +RETURN 'тСст' koi8r koi8r_general_ci latin1_swedish_ci +SHOW CREATE TRIGGER t1_bi; +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +t1_bi CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 +FOR EACH ROW +SET NEW.c1 = 'тСст' koi8r koi8r_general_ci latin1_swedish_ci +SHOW CREATE EVENT ev1; +Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation +ev1 SYSTEM CREATE EVENT `ev1` ON SCHEDULE AT '2030-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO SELECT 'тСст' AS test koi8r koi8r_general_ci latin1_swedish_ci +DROP VIEW v1; +DROP PROCEDURE p1; +DROP FUNCTION f1; +DROP TABLE t1; +DROP EVENT ev1; End of 5.1 tests -- cgit v1.2.1 From 30810f80b1070cbfd4579835353bb6e84fd1b233 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 12 Jul 2007 13:29:51 +0200 Subject: Bug#28249 Query Cache returns wrong result with concurrent insert / certain lock A race condition in the integration between MyISAM and the query cache code caused the query cache to fail to invalidate itself on concurrently inserted data. This patch fix this problem by using the existing handler interface which, upon each statement cache attempt, compare the size of the table as viewed from the cache writing thread and with any snap shot of the global table state. If the two sizes are different the global table size is unknown and the current statement can't be cached. mysql-test/r/query_cache.result: Added test case mysql-test/t/query_cache.test: Added test case sql/ha_myisam.cc: - Implemented handler interface for ha_myisam class to dermine if the table belonging to the currently processed statement can be cached or not. sql/ha_myisam.h: - Implemented handler interface for ha_myisam class to dermine if the table belonging to the currently processed statement can be cached or not. sql/handler.h: - Documented register_query_cache_table method in the handler interface. --- mysql-test/r/query_cache.result | 39 +++++++++++++++++++++++++++++++++++++++ 1 file changed, 39 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 79471ee5c02..58b8aad6fc9 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -1409,3 +1409,42 @@ 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; +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; -- cgit v1.2.1 From 9dc3088f9e4cad7b6e33d4d1b35f11d4b5b5e372 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 12 Jul 2007 22:26:41 +0400 Subject: A fix and a test case for Bug#26141 mixing table types in trigger causes full table lock on innodb table. Also fixes Bug#28502 Triggers that update another innodb table will block on X lock unnecessarily (duplciate). Code review fixes. Both bugs' synopses are misleading: InnoDB table is not X locked. The statements, however, cannot proceed concurrently, but this happens due to lock conflicts for tables used in triggers, not for the InnoDB table. If a user had an InnoDB table, and two triggers, AFTER UPDATE and AFTER INSERT, competing for different resources (e.g. two distinct MyISAM tables), then these two triggers would not be able to execute concurrently. Moreover, INSERTS/UPDATES of the InnoDB table would not be able to run concurrently. The problem had other side-effects (see respective bug reports). This behavior was a consequence of a shortcoming of the pre-locking algorithm, which would not distinguish between different DML operations (e.g. INSERT and DELETE) and pre-lock all the tables that are used by any trigger defined on the subject table. The idea of the fix is to extend the pre-locking algorithm to keep track, for each table, what DML operation it is used for and not load triggers that are known to never be fired. mysql-test/r/trigger-trans.result: Update results (Bug#26141) mysql-test/r/trigger.result: Update results (Bug#28502) mysql-test/t/trigger-trans.test: Add a test case for Bug#26141 mixing table types in trigger causes full table lock on innodb table. mysql-test/t/trigger.test: Add a test case for Bug#28502 Triggers that update another innodb table will block echo on X lock unnecessarily. Add more test coverage for triggers. sql/item.h: enum trg_event_type is needed in table.h sql/sp.cc: Take into account table_list->trg_event_map when determining what tables to pre-lock. After this change, if we attempt to fire a trigger for which we had not pre-locked any tables, error 'Table was not locked with LOCK TABLES' will be printed. This, however, should never happen, provided the pre-locking algorithm has no programming bugs. Previously a trigger key in the sroutines hash was based on the name of the table the trigger belongs to. This was possible because we would always add to the pre-locking list all the triggers defined for a table when handling this table. Now the key is based on the name of the trigger, owing to the fact that a trigger name must be unique in the database it belongs to. sql/sp_head.cc: Generate sroutines hash key in init_spname(). This is a convenient place since there we have all the necessary information and can avoid an extra alloc. Maintain and merge trg_event_map when adding and merging elements of the pre-locking list. sql/sp_head.h: Add ,m_sroutines_key member, used when inserting the sphead for a trigger into the cache of routines used by a statement. Previously the key was based on the table name the trigger belonged to, since for a given table we would add to the sroutines list all the triggers defined on it. sql/sql_lex.cc: Introduce a new lex step: set_trg_event_type_for_tables(). It is called when we have finished parsing but before opening and locking tables. Now this step is used to evaluate for each TABLE_LIST instance which INSERT/UPDATE/DELETE operation, if any, it is used in. In future this method could be extended to aggregate other information that is hard to aggregate during parsing. sql/sql_lex.h: Add declaration for set_trg_event_type_for_tables(). sql/sql_parse.cc: Call set_trg_event_type_for_tables() after MYSQLparse(). Remove tabs. sql/sql_prepare.cc: Call set_trg_event_type_for_tables() after MYSQLparse(). sql/sql_trigger.cc: Call set_trg_event_type_for_tables() after MYSQLparse(). sql/sql_trigger.h: Remove an obsolete member. sql/sql_view.cc: Call set_trg_event_type_for_tables() after MYSQLparse(). sql/sql_yacc.yy: Move assignment of sp_head::m_type before calling sp_head::init_spname(), one is now used inside another. sql/table.cc: Implement TABLE_LIST::set_trg_event_map() - a method that calculates wh triggers may be fired on this table when executing a statement. sql/table.h: Add missing declarations. Move declaration of trg_event_type from item.h (it will be needed for trg_event_map bitmap when we start using Bitmap template instead of uint8). --- mysql-test/r/trigger-trans.result | 59 +++++ mysql-test/r/trigger.result | 457 ++++++++++++++++++++++++++++++++++++++ 2 files changed, 516 insertions(+) (limited to 'mysql-test/r') 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 290929d476d..4b18e525e62 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -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 -- cgit v1.2.1 From 7416224c60a876c5cde518e41469911d1b00b3ce Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 15 Jul 2007 13:25:38 +0400 Subject: A fix for Bug#27406 Events: failure only causes a warning. Update test results. When executing a CREATE EVENT statement with ON COMPLETION NOT PRESERVE clause (explicit or implicit) and completion date in the past, we do not create the event. Or, put it differently, we create it and then drop immediately. A warning is issued in this case, not an error -- we want to load successfully old database dumps, and such dumps may contain events that are no longer valid. Update the warning text to not imply an erroneous condition. mysql-test/r/events_bugs.result: Update the test results (Bug#27406) sql/share/errmsg.txt: Fix Bug#27406 "Events: failure only causes a warning" -- update the error message to not imply that there was a failure. --- mysql-test/r/events_bugs.result | 14 +++++++------- 1 file changed, 7 insertions(+), 7 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result index a01f098affb..14728bdcc2e 100644 --- a/mysql-test/r/events_bugs.result +++ b/mysql-test/r/events_bugs.result @@ -31,7 +31,7 @@ create event e_55 on schedule at 10000101000000 do drop table t; ERROR HY000: Incorrect AT value: '10000101000000' create event e_55 on schedule at 20000101000000 do drop table t; Warnings: -Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created +Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. show events; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation create event e_55 on schedule at 20200101000000 starts 10000101000000 do drop table t; @@ -447,32 +447,32 @@ e3 +00:00 CREATE EVENT `e3` ON SCHEDULE EVERY 1 DAY STARTS '2006-01-01 00:00:00 The following should fail, and nothing should be altered. ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00'; -ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been altered +ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' DISABLE; -ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been altered +ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. The following should give warnings, and nothing should be created. CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' DO SELECT 1; Warnings: -Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created +Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' DISABLE DO SELECT 1; Warnings: -Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created +Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DO SELECT 1; Warnings: -Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created +Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DISABLE DO SELECT 1; Warnings: -Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. Event has not been created +Note 1584 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. SHOW EVENTS; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation events_test e1 root@localhost +05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci -- cgit v1.2.1 From 8023d91929247f1b2e2f81ca10daca4dde4ab2e2 Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 15 Jul 2007 13:34:35 +0400 Subject: Add a teste case for Bug#27296 "Assertion in ALTER TABLE SET DEFAULT in Linux Debug build (possible deadlock)" The bug is not repeatable any more. mysql-test/r/innodb_mysql.result: Update test results (Bug#27296) mysql-test/t/innodb_mysql.test: Add a teste case for Bug#27296 "Assertion in ALTER TABLE SET DEFAULT in Linux Debug build (possible deadlock)" --- mysql-test/r/innodb_mysql.result | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 11c7c2aedc9..847805e7b6f 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -676,4 +676,8 @@ INSERT INTO t1 VALUES (1); switch to connection default SET AUTOCOMMIT=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 -- cgit v1.2.1 From bc642e113255417907d2759e5355c7b374b8d479 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 17 Jul 2007 00:59:21 +0400 Subject: Post-merge fixes (merge from the main). mysql-test/r/innodb_mysql.result: Update test results (merge from the main tree). mysql-test/r/query_cache.result: Update test results (merge from the main tree). mysql-test/r/sp.result: Update test results (merge from the main tree). mysql-test/t/query_cache.test: Use --echo End of to simplify future merges. sql/handler.h: st_table_list -> TABLE_LIST sql/item_create.cc: A post-merge fix (this code is in sql_yacc.yy in 5.0) sql/rpl_utility.h: st_table_list -> TABLE_LIST sql/sp.cc: A post-merge fix. sql/sp_head.cc: In 5.1 memdup_root returns void*. sql/sql_show.cc: st_table_list -> TABLE_LIST sql/sql_show.h: st_table_list -> TABLE_LIST sql/sql_yacc.yy: A post-merge fix. sql/table.cc: st_table_list -> TABLE_LIST sql/table.h: st_table_list -> TABLE_LIST --- mysql-test/r/innodb_mysql.result | 52 ++++------------------------------------ mysql-test/r/query_cache.result | 1 + mysql-test/r/sp.result | 50 +++++++++++++++++++------------------- 3 files changed, 30 insertions(+), 73 deletions(-) (limited to 'mysql-test/r') diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 3e4e10780d2..8a4749a478d 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -166,7 +166,6 @@ t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP TABLE t1; -End of 4.1 tests create table t1m (a int) engine = MEMORY; create table t1i (a int); create table t2m (a int) engine = MEMORY; @@ -362,22 +361,6 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL fkey 5 NULL 5 Using index 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where DROP TABLE t1,t2; -CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB; -INSERT INTO t1 VALUES ( 1 , 1 , 1); -INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1; -INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1; -INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1; -INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1; -INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1; -INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1; -INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1; -EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL b 5 NULL 128 -EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort -DROP TABLE t1; CREATE TABLE t1 ( id int NOT NULL, name varchar(20) NOT NULL, @@ -503,37 +486,6 @@ a 2 5 drop table t1; -set @save_qcache_size=@@global.query_cache_size; -set @save_qcache_type=@@global.query_cache_type; -set global query_cache_size=10*1024*1024; -set global query_cache_type=1; -drop table if exists `test`; -Warnings: -Note 1051 Unknown table 'test' -CREATE TABLE `test` (`test1` varchar(3) NOT NULL, -`test2` varchar(4) NOT NULL,PRIMARY KEY (`test1`)) -ENGINE=InnoDB DEFAULT CHARSET=latin1; -INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678'); -select * from test; -test1 test2 -tes 5678 -INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234') -ON DUPLICATE KEY UPDATE `test2` = '1234'; -select * from test; -test1 test2 -tes 1234 -flush tables; -select * from test; -test1 test2 -tes 1234 -drop table test; -set global query_cache_type=@save_qcache_type; -set global query_cache_size=@save_qcache_size; -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 create table t1( id int auto_increment, c char(1) not null, @@ -858,6 +810,10 @@ a 2 5 drop table t1; +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 CREATE TABLE `t2` ( `k` int(11) NOT NULL auto_increment, diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index db513902b2c..7a3cbf26a21 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -1301,6 +1301,7 @@ drop procedure f3; drop procedure f4; drop table t1; set GLOBAL query_cache_size=0; +End of 4.1 tests SET GLOBAL query_cache_size=102400; create table t1(a int); insert into t1 values(0), (1), (4), (5); diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 9c98dc8d027..273c60110b3 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -5659,31 +5659,6 @@ t3_id_1 t3_id_2 t4_id DROP PROCEDURE p1| DROP VIEW v1, v2| DROP TABLE t3, t4| -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 Begin of 5.1 tests drop function if exists pi; @@ -6306,6 +6281,31 @@ 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; drop procedure if exists proc_25411_a; drop procedure if exists proc_25411_b; drop procedure if exists proc_25411_c; -- cgit v1.2.1