An error occurred fetching the project authors.
- 12 Feb, 2008 1 commit
-
-
unknown authored
The problem is that AFTER UPDATE triggers will fire only if the new data is different from the old data on the row. The trigger should fire regardless of whether there are changes to the data. The solution is to fire the trigger on UPDATE even if there are no changes to the value (because the value is the same). mysql-test/r/trigger.result: Add test case result for Bug#23771 mysql-test/t/trigger.test: Add test case for Bug#23771 sql/sql_update.cc: Move the invocation of the after update trigger so that the trigger will fire even if the records are the same.
-
- 29 Nov, 2007 1 commit
-
-
unknown authored
This bug is actually two bugs in one, one of which is CREATE TRIGGER under LOCK TABLES and the other is CREATE TRIGGER under LOCK TABLES simultaneous to a FLUSH TABLES WITH READ LOCK (global read lock). Both situations could lead to a server crash or deadlock. The first problem arises from the fact that when under LOCK TABLES, if the table is in the set of locked tables, the table is already open and it doesn't need to be reopened (not a placeholder). Also in this case, if the table is not write locked, a exclusive lock can't be acquired because of a possible deadlock with another thread also holding a (read) lock on the table. The second issue arises from the fact that one should never wait for a global read lock if it's holding any locked tables, because the global read lock is waiting for these tables and this leads to a circular wait deadlock. The solution for the first case is to check if the table is write locked and upgraded the write lock to a exclusive lock and fail otherwise for non write locked tables. Grabbin the exclusive lock in this case also means to ensure that the table is opened only by the calling thread. The second issue is partly fixed by not waiting for the global read lock if the thread is holding any locked tables. The second issue is only partly addressed in this patch because it turned out to be much wider and also affects other DDL statements. Reported as Bug#32395 mysql-test/r/trigger.result: Add test case result for Bug#23713 mysql-test/r/trigger_notembedded.result: Add test case result for Bug#23713 mysql-test/t/trigger.test: Add test case for Bug#23713 mysql-test/t/trigger_notembedded.test: Add test case for Bug#23713 sql/mysql_priv.h: Locally export wait_while_table_is_used and name_lock_locked_table and add flag to mysql_ha_rm_tables to signal that LOCK_open is locked. sql/sql_base.cc: Introduce name_lock_locked_table function and match close_old_data_files function declaration and definition. sql/sql_handler.cc: Add flag to mysql_ha_rm_tables to signal that LOCK_open is locked. sql/sql_rename.cc: Fix mysql_ha_rm_tables caller. sql/sql_table.cc: Export wait_while_table_is_used and assert that LOCK_open is locked and fix mysql_ha_rm_tables caller. sql/sql_trigger.cc: Upgrade write locked tables to a exclusive lock and fail if the table is not write locked. Also, don't wait for the global read lock if under LOCK TABLES.
-
- 16 Nov, 2007 1 commit
-
-
unknown authored
Wrong syntax for mysqltest commands was used in test file. Fixed test. mysql-test/r/trigger.result: Bug#32443 - trigger.test produces warnings files Fixed test result mysql-test/t/trigger.test: Bug#32443 - trigger.test produces warnings files Fixed test
-
- 01 Nov, 2007 1 commit
-
-
unknown authored
SHOW CREATE TRIGGER was not checking for detected errors opening/reading the trigger file. Fixed to return the already generated error. mysql-test/r/trigger.result: Bug #31866: test case mysql-test/t/trigger.test: Bug #31866: test case sql/sql_show.cc: Bug #31866: test for error and return it.
-
- 18 Jul, 2007 1 commit
-
-
unknown authored
table. The bug itself is yet another manifestation of Bug 26141. mysql-test/r/trigger.result: Update results. mysql-test/t/trigger.test: Add a test case for Bug#27248 Triggers: error if insert affects temporary table
-
- 12 Jul, 2007 1 commit
-
-
unknown authored
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).
-
- 05 Jul, 2007 1 commit
-
-
unknown authored
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.
-
- 28 Jun, 2007 1 commit
-
-
unknown authored
- BUG#11986: Stored routines and triggers can fail if the code has a non-ascii symbol - BUG#16291: mysqldump corrupts string-constants with non-ascii-chars - BUG#19443: INFORMATION_SCHEMA does not support charsets properly - BUG#21249: Character set of SP-var can be ignored - BUG#25212: Character set of string constant is ignored (stored routines) - BUG#25221: Character set of string constant is ignored (triggers) There were a few general problems that caused these bugs: 1. Character set information of the original (definition) query for views, triggers, stored routines and events was lost. 2. mysqldump output query in client character set, which can be inappropriate to encode definition-query. 3. INFORMATION_SCHEMA used strings with mixed encodings to display object definition; 1. No query-definition-character set. In order to compile query into execution code, some extra data (such as environment variables or the database character set) is used. The problem here was that this context was not preserved. So, on the next load it can differ from the original one, thus the result will be different. The context contains the following data: - client character set; - connection collation (character set and collation); - collation of the owner database; The fix is to store this context and use it each time we parse (compile) and execute the object (stored routine, trigger, ...). 2. Wrong mysqldump-output. The original query can contain several encodings (by means of character set introducers). The problem here was that we tried to convert original query to the mysqldump-client character set. Moreover, we stored queries in different character sets for different objects (views, for one, used UTF8, triggers used original character set). The solution is - to store definition queries in the original character set; - to change SHOW CREATE statement to output definition query in the binary character set (i.e. without any conversion); - introduce SHOW CREATE TRIGGER statement; - to dump special statements to switch the context to the original one before dumping and restore it afterwards. Note, in order to preserve the database collation at the creation time, additional ALTER DATABASE might be used (to temporary switch the database collation back to the original value). In this case, ALTER DATABASE privilege will be required. This is a backward-incompatible change. 3. INFORMATION_SCHEMA showed non-UTF8 strings The fix is to generate UTF8-query during the parsing, store it in the object and show it in the INFORMATION_SCHEMA. Basically, the idea is to create a copy of the original query convert it to UTF8. Character set introducers are removed and all text literals are converted to UTF8. This UTF8 query is intended to provide user-readable output. It must not be used to recreate the object. Specialized SHOW CREATE statements should be used for this. The reason for this limitation is the following: the original query can contain symbols from several character sets (by means of character set introducers). Example: - original query: CREATE VIEW v1 AS SELECT _cp1251 'Hello' AS c1; - UTF8 query (for INFORMATION_SCHEMA): CREATE VIEW v1 AS SELECT 'Hello' AS c1; client/mysqldump.c: Set original character set and collation before dumping definition query. include/my_sys.h: Move out-parameter to the end of list. mysql-test/lib/mtr_report.pl: Ignore server-warnings during the test case. mysql-test/r/create.result: Update result file. mysql-test/r/ctype_cp932_binlog_stm.result: Update result file. mysql-test/r/events.result: Update result file. mysql-test/r/events_bugs.result: Update result file. mysql-test/r/events_grant.result: Update result file. mysql-test/r/func_in.result: Update result file. mysql-test/r/gis.result: Update result file. mysql-test/r/grant.result: Update result file. mysql-test/r/information_schema.result: Update result file. mysql-test/r/information_schema_db.result: Update result file. mysql-test/r/lowercase_view.result: Update result file. mysql-test/r/mysqldump.result: Update result file. mysql-test/r/ndb_sp.result: Update result file. mysql-test/r/ps.result: Update result file. mysql-test/r/rpl_replicate_do.result: Update result file. mysql-test/r/rpl_sp.result: Update result file. mysql-test/r/rpl_trigger.result: Update result file. mysql-test/r/rpl_view.result: Update result file. mysql-test/r/show_check.result: Update result file. mysql-test/r/skip_grants.result: Update result file. mysql-test/r/sp-destruct.result: Update result file. mysql-test/r/sp-error.result: Update result file. mysql-test/r/sp-security.result: Update result file. mysql-test/r/sp.result: Update result file. mysql-test/r/sql_mode.result: Update result file. mysql-test/r/system_mysql_db.result: Update result file. mysql-test/r/temp_table.result: Update result file. mysql-test/r/trigger-compat.result: Update result file. mysql-test/r/trigger-grant.result: Update result file. mysql-test/r/trigger.result: Update result file. mysql-test/r/view.result: Update result file. mysql-test/r/view_grant.result: Update result file. mysql-test/t/events.test: Update test case (new columns added). mysql-test/t/information_schema.test: Update test case (new columns added). mysql-test/t/show_check.test: Test case for SHOW CREATE TRIGGER in prepared statements and stored routines. mysql-test/t/sp-destruct.test: Update test case (new columns added). mysql-test/t/sp.test: Update test case (new columns added). mysql-test/t/view.test: Update test. mysys/charset.c: Move out-parameter to the end of list. scripts/mysql_system_tables.sql: Add new columns to mysql.proc and mysql.event. scripts/mysql_system_tables_fix.sql: Add new columns to mysql.proc and mysql.event. sql/event_data_objects.cc: Support new attributes for events. sql/event_data_objects.h: Support new attributes for events. sql/event_db_repository.cc: Support new attributes for events. sql/event_db_repository.h: Support new attributes for events. sql/events.cc: Add new columns to SHOW CREATE event resultset. sql/mysql_priv.h: 1. Introduce Object_creation_ctx; 2. Introduce SHOW CREATE TRIGGER; 3. Introduce auxilary functions. sql/sp.cc: Add support for new store routines attributes. sql/sp_head.cc: Add support for new store routines attributes. sql/sp_head.h: Add support for new store routines attributes. sql/sql_lex.cc: Generate UTF8-body on parsing/lexing. sql/sql_lex.h: 1. Generate UTF8-body on parsing/lexing. 2. Introduce SHOW CREATE TRIGGER. sql/sql_parse.cc: Introduce SHOW CREATE TRIGGER. sql/sql_partition.cc: Update parse_sql(). sql/sql_prepare.cc: Update parse_sql(). sql/sql_show.cc: Support new attributes for views sql/sql_trigger.cc: Support new attributes for views sql/sql_trigger.h: Support new attributes for views sql/sql_view.cc: Support new attributes for views sql/sql_yacc.yy: 1. Add SHOW CREATE TRIGGER statement. 2. Generate UTF8-body for views, stored routines, triggers and events. sql/table.cc: Introduce Object_creation_ctx. sql/table.h: Introduce Object_creation_ctx. sql/share/errmsg.txt: Add new errors. mysql-test/include/ddl_i18n.check_events.inc: Aux file for test suite. mysql-test/include/ddl_i18n.check_sp.inc: Aux file for test suite. mysql-test/include/ddl_i18n.check_triggers.inc: Aux file for test suite. mysql-test/include/ddl_i18n.check_views.inc: Aux file for test suite. mysql-test/include/have_cp1251.inc: Aux file for test suite. mysql-test/include/have_cp866.inc: Aux file for test suite. mysql-test/include/have_koi8r.inc: Aux file for test suite. mysql-test/include/have_utf8.inc: Aux file for test suite. mysql-test/r/ddl_i18n_koi8r.result: Result file. mysql-test/r/ddl_i18n_utf8.result: Result file. mysql-test/r/have_cp1251.require: Aux file for test suite. mysql-test/r/have_cp866.require: Aux file for test suite. mysql-test/r/have_koi8r.require: Aux file for test suite. mysql-test/r/have_utf8.require: Aux file for test suite. mysql-test/t/ddl_i18n_koi8r.test: Complete koi8r test case for the CS patch. mysql-test/t/ddl_i18n_utf8.test: Complete utf8 test case for the CS patch.
-
- 12 Jun, 2007 2 commits
-
-
unknown authored
Bug 28127 (Some valid identifiers names are not parsed correctly) Bug 26302 (MySQL server cuts off trailing "*/" from comments in SP/func) This patch is the second part of a major cleanup, required to fix Bug 25411 (trigger code truncated). The root cause of the issue stems from the function skip_rear_comments, which was a work around to remove "extra" "*/" characters from the query text, when parsing a query and reusing the text fragments to represent a view, trigger, function or stored procedure. The reason for this work around is that "special comments", like /*!50002 XXX */, were not parsed properly, so that a query like: AAA /*!50002 BBB */ CCC would be seen by the parser as "AAA BBB */ CCC" when the current version is greater or equal to 5.0.2 The root cause of this stems from how special comments are parsed. Special comments are really out-of-bound text that appear inside a query, that affects how the parser behave. In nature, /*!50002 XXX */ in MySQL is similar to the C concept of preprocessing : #if VERSION >= 50002 XXX #endif Depending on the current VERSION of the server, either the special comment should be expanded or it should be ignored, but in all cases the "text" of the query should be re-written to strip the "/*!50002" and "*/" markers, which does not belong to the SQL language itself. Prior to this fix, these markers would leak into : - the storage format for VIEW, - the storage format for FUNCTION, - the storage format for FUNCTION parameters, in mysql.proc (param_list), - the storage format for PROCEDURE, - the storage format for PROCEDURE parameters, in mysql.proc (param_list), - the storage format for TRIGGER, - the binary log used for replication. In all cases, not only this cause format corruption, but also provide a vector for dormant security issues, by allowing to tunnel code that will be activated after an upgrade. The proper solution is to deal with special comments strictly during parsing, when accepting a query from the outside world. Once a query is parsed and an object is created with a persistant representation, this object should not arbitrarily mutate after an upgrade. In short, special comments are a useful but limited feature for MYSQLdump, when used at an *interface* level to facilitate import/export, but bloating the server *internal* storage format is *not* the proper way to deal with configuration management of the user logic. With this fix: - the Lex_input_stream class now acts as a comment pre-processor, and either expands or ignore special comments on the fly. - MYSQLlex and sql_yacc.yy have been cleaned up to strictly use the public interface of Lex_input_stream. In particular, how the input stream accepts or rejects a character is private to Lex_input_stream, and the internal buffer pointers of that class are strictly private, and should not be tempered with during parsing. This caused many changes mostly in sql_lex.cc. During the code cleanup in case MY_LEX_NUMBER_IDENT, Bug 28127 (Some valid identifiers names are not parsed correctly) was found and fixed. By parsing special comments properly, and removing the function 'skip_rear_comments' [sic], Bug 26302 (MySQL server cuts off trailing "*/" from comments in SP/func) has been fixed as well. sql/event_data_objects.cc: Cleanup of the code that extracts the query text sql/sp.cc: Cleanup of the code that extracts the query text sql/sp_head.cc: Cleanup of the code that extracts the query text sql/sql_trigger.cc: Cleanup of the code that extracts the query text sql/sql_view.cc: Cleanup of the code that extracts the query text mysql-test/r/comments.result: Bug#25411 (trigger code truncated) mysql-test/r/sp.result: Bug#25411 (trigger code truncated) Bug 26302 (MySQL server cuts off trailing "*/" from comments in SP/func) mysql-test/r/trigger.result: Bug#25411 (trigger code truncated) mysql-test/r/varbinary.result: Bug 28127 (Some valid identifiers names are not parsed correctly) mysql-test/t/comments.test: Bug#25411 (trigger code truncated) mysql-test/t/sp.test: Bug#25411 (trigger code truncated) Bug 26302 (MySQL server cuts off trailing "*/" from comments in SP/func) mysql-test/t/trigger.test: Bug#25411 (trigger code truncated) mysql-test/t/varbinary.test: Bug 28127 (Some valid identifiers names are not parsed correctly) sql/sql_lex.cc: Implemented comment pre-processing in Lex_input_stream, major cleanup of the lex/yacc code to not use Lex_input_stream private members. sql/sql_lex.h: Implemented comment pre-processing in Lex_input_stream, major cleanup of the lex/yacc code to not use Lex_input_stream private members. sql/sql_yacc.yy: post merge fix : view_check_options must be parsed before signaling the end of the query
-
unknown authored
- fixed wrong test case for bug 20903 - closed the dangling connections in trigger.test - GET_LOCK() and RELEASE_LOCK() now produce more detailed log - fixed an omission in GET_LOCK() : assign the thread_id when acquiring the lock. mysql-test/r/trigger.result: Bug #28992: test case updated mysql-test/t/trigger.test: Bug #28992: test case updated. dangling connections closed. sql/item_func.cc: Bug #28992: - GET_LOCK() and RELEASE_LOCK() now produce more detailed log - fixed an omission in GET_LOCK() : assign the thread_id when acquiring the lock.
-
- 03 Jun, 2007 1 commit
-
-
unknown authored
The value of "low-priority-updates" option and the LOW PRIORITY prefix was taken into account at parse time. This caused triggers (among others) to ignore this flag (if supplied for the DML statement). Moved reading of the LOW_PRIORITY flag at run time. Fixed an incosistency when handling SET GLOBAL LOW_PRIORITY_UPDATES : now it is in effect for delayed INSERTs. Tested by checking the effect of LOW_PRIORITY flag via a trigger. include/thr_lock.h: Bug #26162: moved reading of the LOW PRIORITY flag at run time mysql-test/r/trigger.result: Bug #26162: test case mysql-test/t/trigger.test: Bug #26162: test case sql/set_var.cc: Bug #26162: fixed the handling of the "low-priority-updates" option sql/sql_base.cc: Bug #26162: moved reading of the LOW PRIORITY flag at run time sql/sql_yacc.yy: Bug #26162: moved reading of the LOW PRIORITY flag at run time
-
- 18 May, 2007 1 commit
-
-
unknown authored
Adjust the check that defines the error message to be returned. mysql-test/r/sp-error.result: Update results (more accurate error code) mysql-test/r/sp-prelocking.result: Update results (more accurate error code) mysql-test/r/trigger.result: Update results (more accurate error code) mysql-test/t/sp-error.test: ER_NOT_LOCKED -> ER_NO_SUCH_TABLE mysql-test/t/sp-prelocking.test: Add a test case for Bug#27907 mysql-test/t/trigger.test: ER_NOT_LOCKED -> ER_NO_SUCH_TABLE sql/sql_base.cc: Adjust the check for where-we-are for a better error message.
-
- 11 May, 2007 2 commits
-
-
unknown authored
Bug #20662 "Infinite loop in CREATE TABLE IF NOT EXISTS ... SELECT with locked tables" Bug #20903 "Crash when using CREATE TABLE .. SELECT and triggers" Bug #24738 "CREATE TABLE ... SELECT is not isolated properly" Bug #24508 "Inconsistent results of CREATE TABLE ... SELECT when temporary table exists" Deadlock occured when one tried to execute CREATE TABLE IF NOT EXISTS ... SELECT statement under LOCK TABLES which held read lock on target table. Attempt to execute the same statement for already existing target table with triggers caused server crashes. Also concurrent execution of CREATE TABLE ... SELECT statement and other statements involving target table suffered from various races (some of which might've led to deadlocks). Finally, attempt to execute CREATE TABLE ... SELECT in case when a temporary table with same name was already present led to the insertion of data into this temporary table and creation of empty non-temporary table. All above problems stemmed from the old implementation of CREATE TABLE ... SELECT in which we created, opened and locked target table without any special protection in a separate step and not with the rest of tables used by this statement. This underminded deadlock-avoidance approach used in server and created window for races. It also excluded target table from prelocking causing problems with trigger execution. The patch solves these problems by implementing new approach to handling of CREATE TABLE ... SELECT for base tables. We try to open and lock table to be created at the same time as the rest of tables used by this statement. If such table does not exist at this moment we create and place in the table cache special placeholder for it which prevents its creation or any other usage by other threads. We still use old approach for creation of temporary tables. Note that we have separate fix for 5.0 since there we use slightly different less intrusive approach. mysql-test/r/create.result: Extended test coverage for CREATE TABLE ... SELECT. In particular added tests for bug #24508 "Inconsistent results of CREATE TABLE ... SELECT when temporary table exists" and bug #20662 "Infinite loop in CREATE TABLE IF NOT EXISTS ... SELECT with locked tables". mysql-test/r/trigger.result: Added test case for bug #20903 "Crash when using CREATE TABLE .. SELECT and triggers" mysql-test/t/create.test: Extended test coverage for CREATE TABLE ... SELECT. In particular added tests for bug #24508 "Inconsistent results of CREATE TABLE ... SELECT when temporary table exists" and bug #20662 "Infinite loop in CREATE TABLE IF NOT EXISTS ... SELECT with locked tables". mysql-test/t/trigger.test: Added test case for bug #20903 "Crash when using CREATE TABLE .. SELECT and triggers" sql/lock.cc: Now for creation of name-lock placeholder lock_table_name() uses auxiliary function table_cache_insert_placeholder(). sql/mysql_priv.h: Removed declaration of non-existing build_table_path() routine. The former mysql_create_table_internal() was renamed to mysql_create_table_no_lock() and now exposed to other modules to give them opportunity of creation of tables in cases when name-lock is already obtained. reopen_name_locked_table() now has 3rd argument which controls linking in of table being opened into THD::open_tables (this is useful in cases when placeholder used for name-locking is already linked into this list). Added declaration of auxiliary function table_cache_insert_placeholder() which is used for creation of table placeholders for name-locking. Added declaration of lock_table_name_if_not_cached() which can be used to take an exclusive name-lock on table if there are no records for it in table cache. Changed signature of unlink_open_table() function to simplify its use and make it useful for table placeholders and tables that are only open. Added auxiliary drop_open_table() routine. Moved declaration of refresh_version to table.h header to make it accessible from inline methods of TABLE class. MYSQL_OPEN_IGNORE_LOCKED_TABLES flag is no longer used. Instead MYSQL_OPEN_TEMPORARY_ONLY option was added. sql/sql_base.cc: Added support for the new approach to the handling of CREATE TABLE ... SELECT for base tables. Now we try to open and lock table to be created at the same time as the rest of tables used by this statement. If such table does not exist at this moment we create and place in the table cache special placeholder for it which prevents its creation or any other usage by other threads. Note significant distinctions of this placeholder from the placeholder used for normal name-lock: 1) It is treated like open table by other name-locks so it does not allow name-lock taking operations like DROP TABLE or RENAME TABLE to proceed. 2) it is linked into THD::open_tables list and automatically removed during close_thread_tables() call open_tables(): Implemented logic described above. To do this added auxiliary check_if_table_exists() function. Removed support for MYSQL_OPEN_IGNORE_LOCKED_TABLES option which is no longer used. Added MYSQL_OPEN_TEMPORARY_ONLY which is used to restrict search for temporary tables only. close_cached_tables()/close_thread_table()/reopen_tables()/ close_old_data_files()/table_is_used()/remove_table_from_cache(): Added support for open placeholders (note that we also use them when we need to re-open tables during flush). unlink_open_table(): Changed function signature to simplify its use and to make useful for open placeholders and tables which are only open and not locked. Added auxiliary drop_open_table() routine. reopen_name_locked_table(): Now has 3rd argument which controls linking in of table being opened into THD::open_tables (this is useful in cases when placeholder used for name-locking is already linked into this list). Added auxiliary table_cache_insert_placeholder() routine which simplifies creation of placeholders used for name-locking. Added lock_table_name_if_not_cached() which can be used to take an exclusive name-lock on table if there are no records for it in table cache. sql/sql_handler.cc: Adjusted mysql_ha_mark_tables_for_reopen() routine to properly handle placeholders which now can be linked into open tables list. sql/sql_insert.cc: Introduced new approach to handling of base tables in CREATE TABLE ... SELECT statement. Now we try to open and lock table to be created at the same time as the rest of tables used by this statement. If such table does not exist at this moment we create and place in the table cache special placeholder for it which prevents its creation or any other usage by other threads. By doing this we avoid races which existed with previous approach in which we created, opened and locked target in separate step without any special protection. This also allows properly calculate prelocking set in cases when target table already exists and has some on insert triggers. Note that we don't employ the same approach for temporary tables (this is okay as such tables are unaffected by other threads). Changed create_table_from_items() and methods of select_create class to implement this approach. sql/sql_parse.cc: The new approach to handling of CREATE TABLE ... SELECT for base tables assumes that all tables (including table to be created) are opened and (or) locked at the same time. So in cases when we create base table we have to pass to open_and_lock_tables() table list which includes target table. sql/sql_prepare.cc: The new approach to handling of CREATE TABLE ... SELECT for base tables assumes that all tables (including table to be created) are opened and (or) locked at the same time. So in cases when we create base table we have to pass to open_and_lock_tables() table list which includes target table. sql/sql_table.cc: Changed mysql_create_table(), mysql_create_like_table() and mysql_alter_table() (in rename case) to obtain exclusive name-lock on the non-temporary table which is going to be created (to which we going to rename). This ensures that not only destination table doesn't exist on disk but also that there are no placeholder in table cache for it (i.e. there is no CREATE TABLE ... SELECT operation in progress for it). Note that to avoid deadlocks while taking these name-locks this code assumes that existence of any record for table in table cache (even name-lock) means that table exists. Altough such check can lead to false positives these should occur only in case of highly concurrent DDL operations on the table and should not break binary logging. Renamed mysql_create_table_internal() to mysql_create_table_no_lock() and made it accessible from other files to give them ability to create table in situation when name-lock is already obtained or not relevant. Adjusted calls to reopen_name_locked_table(), which now takes extra argument, which controls linking of open table into THD::open_tables list. Removed redundant setting of table's 'version' field before calls to close_cached_table(). This function will set it to 0 itself anyway. sql/sql_trigger.cc: reopen_name_locked_tables() now has one more argument which controls linking of opened table into the THD::open_tables list. sql/sql_yacc.yy: The new approach to handling of CREATE TABLE ... SELECT statement for base tables assumes that all tables including table to be created are open and (or) locked at the same time. Therefore we need to set correct lock for target table. sql/table.h: Moved declaration of refresh_version variable from mysql_priv.h to make it accessible from inline methods of TABLE class. Renamed TABLE::locked_by_flush member to open_placeholder since now it is also used for taking exclusive name-lock and not only by flush. Introduced TABLE::is_name_opened() helper method which can be used to distinguish TABLE instances corresponding to open tables or placeholders for them from closed instances (e.g. due to their old version). Also introduced TABLE::needs_reopen_or_name_lock() helper which allows to check if TABLE instance corresponds to outdated version of table or to name-lock placeholder. Introduced TABLE_LIST::create member which marks elements of table list corresponds to the table to be created. Adjusted TABLE_LIST::placeholder() method to take into account name-lock placeholders for tables to be created (this, for example, allows to properly handle such placeholders in lock_tables()). Finally, moved currently unused TABLE::open_next/open_prev members under ifdef NOT_YET. mysql-test/r/create_select-big.result: New BitKeeper file ``mysql-test/r/create_select-big.result'' mysql-test/t/create_select-big.test: New BitKeeper file ``mysql-test/t/create_select-big.test''
-
unknown authored
Bug #20662 "Infinite loop in CREATE TABLE IF NOT EXISTS ... SELECT with locked tables" Bug #20903 "Crash when using CREATE TABLE .. SELECT and triggers" Bug #24738 "CREATE TABLE ... SELECT is not isolated properly" Bug #24508 "Inconsistent results of CREATE TABLE ... SELECT when temporary table exists" Deadlock occured when one tried to execute CREATE TABLE IF NOT EXISTS ... SELECT statement under LOCK TABLES which held read lock on target table. Attempt to execute the same statement for already existing target table with triggers caused server crashes. Also concurrent execution of CREATE TABLE ... SELECT statement and other statements involving target table suffered from various races (some of which might've led to deadlocks). Finally, attempt to execute CREATE TABLE ... SELECT in case when a temporary table with same name was already present led to the insertion of data into this temporary table and creation of empty non-temporary table. All above problems stemmed from the old implementation of CREATE TABLE ... SELECT in which we created, opened and locked target table without any special protection in a separate step and not with the rest of tables used by this statement. This underminded deadlock-avoidance approach used in server and created window for races. It also excluded target table from prelocking causing problems with trigger execution. The patch solves these problems by implementing new approach to handling of CREATE TABLE ... SELECT for base tables. We try to open and lock table to be created at the same time as the rest of tables used by this statement. If such table does not exist at this moment we create and place in the table cache special placeholder for it which prevents its creation or any other usage by other threads. We still use old approach for creation of temporary tables. Also note that we decided to postpone introduction of some tests for concurrent behaviour of CREATE TABLE ... SELECT till 5.1. The main reason for this is absence in 5.0 ability to set @@debug variable at runtime, which can be circumvented only by using several test files with individual .opt files. Since the latter is likely to slowdown test-suite unnecessary we chose not to push this tests into 5.0, but run them manually for this version and later push their optimized version into 5.1 mysql-test/r/create.result: Extended test coverage for CREATE TABLE ... SELECT. In particular added tests for bug #24508 "Inconsistent results of CREATE TABLE ... SELECT when temporary table exists" and bug #20662 "Infinite loop in CREATE TABLE IF NOT EXISTS ... SELECT with locked tables". mysql-test/r/trigger.result: Added test case for bug #20903 "Crash when using CREATE TABLE .. SELECT and triggers" mysql-test/t/create.test: Extended test coverage for CREATE TABLE ... SELECT. In particular added tests for bug #24508 "Inconsistent results of CREATE TABLE ... SELECT when temporary table exists" and bug #20662 "Infinite loop in CREATE TABLE IF NOT EXISTS ... SELECT with locked tables". mysql-test/t/trigger.test: Added test case for bug #20903 "Crash when using CREATE TABLE .. SELECT and triggers" sql/lock.cc: Now for creation of name-lock placeholder in lock_table_name() we use auxiliary function table_cache_insert_placeholder(). sql/mysql_priv.h: Made build_table_path() function available outside of sql_table.cc file. reopen_name_locked_table() now has 3rd argument which controls linking in of table being opened into THD::open_tables (this is useful in cases when placeholder used for name-locking is already linked into this list). Added declaration of auxiliary function table_cache_insert_placeholder() which is used for creation of table placeholders for name-locking. Added declaration of table_cache_has_open_placeholder() function which can be used for checking if table cache contains an open placeholder for the table and if this placeholder was created by another thread. (This function is needed only in 5.0 where we use it in various versions of CREATE TABLE in order to protect it from concurrent CREATE TABLE ... SELECT operations for the table. Starting from 5.1 we use different approach so it is going to be removed there). Made close_old_data_files() static within sql_base.cc file. Added auxiliary drop_open_table() routine. Moved declaration of refresh_version to table.h header to make it accessible from inline methods of TABLE class. MYSQL_OPEN_IGNORE_LOCKED_TABLES flag is no longer used. Instead MYSQL_OPEN_TEMPORARY_ONLY option was added. sql/sql_base.cc: Added support for the new approach to the handling of CREATE TABLE ... SELECT for base tables. Now we try to open and lock table to be created at the same time as the rest of tables used by this statement. If such table does not exist at this moment we create and place in the table cache special placeholder for it which prevents its creation or any other usage by other threads. Note significant distinctions of this placeholder from the placeholder used for normal name-lock: 1) It is treated like open table by other name-locks so it does not allow name-lock taking operations like DROP TABLE or RENAME TABLE to proceed. 2) it is linked into THD::open_tables list and automatically removed during close_thread_tables() call. open_tables(): Implemented logic described above. To do this added auxiliary check_if_table_exists() function. Removed support for MYSQL_OPEN_IGNORE_LOCKED_TABLES option which is no longer used. Added MYSQL_OPEN_TEMPORARY_ONLY which is used to restrict search for temporary tables only. close_cached_tables()/close_thread_table()/reopen_tables()/ close_old_data_files()/table_is_used()/remove_table_from_cache(): Added support for open placeholders (note that we also use them when we need to re-open tables during flush). Added auxiliary drop_open_table() routine. reopen_name_locked_table(): Now has 3rd argument which controls linking in of table being opened into THD::open_tables (this is useful in cases when placeholder used for name-locking is already linked into this list). Added auxiliary table_cache_insert_placeholder() routine which simplifies creation of placeholders used for name-locking. Added table_cache_has_open_placeholder() function which can be used for checking if table cache contains an open placeholder for the table and if this placeholder was created by another thread. (This function is needed only in 5.0 where we use it in various versions of CREATE TABLE in order to protect it from concurrent CREATE TABLE ... SELECT operations for the table. Starting from 5.1 we use different approach so it is going to be removed there). sql/sql_handler.cc: Adjusted mysql_ha_mark_tables_for_reopen() routine to properly handle placeholders which now can be linked into open tables list. sql/sql_insert.cc: Introduced new approach to handling of base tables in CREATE TABLE ... SELECT statement. Now we try to open and lock table to be created at the same time as the rest of tables used by this statement. If such table does not exist at this moment we create and place in the table cache special placeholder for it which prevents its creation or any other usage by other threads. By doing this we avoid races which existed with previous approach in which we created, opened and locked target in separate step without any special protection. This also allows properly calculate prelocking set in cases when target table already exists and has some on insert triggers. Note that we don't employ the same approach for temporary tables (this is okay as such tables are unaffected by other threads). Changed create_table_from_items() and select_create methods to implement this approach. sql/sql_parse.cc: The new approach to handling of CREATE TABLE ... SELECT for base tables assumes that all tables (including table to be created) are opened and (or) locked at the same time. So in cases when we create base table we have to pass to open_and_lock_tables() table list which includes target table. sql/sql_prepare.cc: The new approach to handling of CREATE TABLE ... SELECT for base tables assumes that all tables (including table to be created) are opened and (or) locked at the same time. So in cases when we create base table we have to pass to open_and_lock_tables() table list which includes target table. sql/sql_table.cc: Now mysql_create_table_internal(), mysql_create_like_table() and mysql_alter_table() not only check that destination table doesn't exist on disk but also check that there is no create placeholder in table cache for it (i.e. there is no CREATE TABLE ... SELECT operation in progress for it). Note that starting from 5.1 we use different approach in order to to protect CREATE TABLE ... SELECT from concurrent CREATE TABLE (ALTER TABLE ... RENAME) operations, the latter simply take name-locks on table before its creation (on target table name before renaming). Also made build_table_path() available from other files and asjusted calls to reopen_name_locked_table(), which now takes extra argument, which controls linking of open table into THD::open_tables list. sql/sql_trigger.cc: reopen_name_locked_tables() now has one more argument which controls linking of opened table into the THD::open_tables list. sql/sql_yacc.yy: The new approach to handling of CREATE TABLE ... SELECT statement for base tables assumes that all tables including table to be created are open and (or) locked at the same time. Therefore we need to set correct lock for target table. sql/table.h: Moved declaration of refresh_version variable from mysql_priv.h to make it accessible from inline methods of TABLE class. Renamed TABLE::locked_by_flush member to open_placeholder since now it is also used for taking exclusive name-lock and not only by flush. Introduced TABLE::is_name_opened() helper method which can be used to distinguish TABLE instances corresponding to open tables or placeholders for them from closed instances (e.g. due to their old version). Also introduced TABLE::needs_reopen_or_name_lock() helper which allows to check if TABLE instance corresponds to outdated version of table or to name-lock placeholder. Introduced TABLE_LIST::create member which marks elements of table list corresponds to the table to be created. Adjusted TABLE_LIST::placeholder() method to take into account name-lock placeholders for tables to be created (this, for example, allows to properly handle such placeholders in lock_tables()).
-
- 19 Mar, 2007 1 commit
-
-
unknown authored
Removed wrong fix for the bug#27006. The bug was added by the fix for the bug#19978 and fixed by Monty on 2007/02/21. trigger.test, trigger.result: Corrected test case for the bug#27006. sql/sql_insert.cc: Removed wrong fix for the bug#27006. The bug was added by the fix for the bug#19978 and fixed by Monty on 2007/02/21. mysql-test/t/trigger.test: Corrected test case for the bug#27006. mysql-test/r/trigger.result: Corrected test case for the bug#27006.
-
- 16 Mar, 2007 1 commit
-
-
unknown authored
UPDATE if the row wasn't actually changed. This bug was caused by fix for bug#19978. It causes AFTER UPDATE triggers not firing if a row wasn't actually changed by the update part of the INSERT .. ON DUPLICATE KEY UPDATE. Now triggers are always fired if a row is touched by the INSERT ... ON DUPLICATE KEY UPDATE. sql/sql_insert.cc: Bug#27006: AFTER UPDATE triggers not fired with INSERT ... ON DUPLICATE KEY UPDATE if the row wasn't actually changed. Now triggers are always fired if a row is touched by the INSERT ... ON DUPLICATE KEY UPDATE. mysql-test/r/trigger.result: Added a test case for the bug#27006: AFTER UPDATE triggers not fired with INSERT ... ON DUPLICATE KEY UPDATE if the row wasn't actually changed. mysql-test/t/trigger.test: Added a test case for the bug#27006: AFTER UPDATE triggers not fired with INSERT ... ON DUPLICATE KEY UPDATE if the row wasn't actually changed.
-
- 06 Mar, 2007 3 commits
-
-
unknown authored
-
unknown authored
-
unknown authored
Bug 18914 (Calling certain SPs from triggers fail) Bug 20713 (Functions will not not continue for SQLSTATE VALUE '42S02') Bug 21825 (Incorrect message error deleting records in a table with a trigger for inserting) Bug 22580 (DROP TABLE in nested stored procedure causes strange dependency error) Bug 25345 (Cursors from Functions) This fix resolves a long standing issue originally reported with bug 8407, which affect the behavior of Stored Procedures, Stored Functions and Trigger in many different ways, causing symptoms reported by all the bugs listed. In all cases, the root cause of the problem traces back to 8407 and how the server locks tables involved with sub statements. Prior to this fix, the implementation of stored routines would: - compute the transitive closure of all the tables referenced by a top level statement - open and lock all the tables involved - execute the top level statement "transitive closure of tables" means collecting: - all the tables, - all the stored functions, - all the views, - all the table triggers - all the stored procedures involved, and recursively inspect these objects definition to find more references to more objects, until the list of every object referenced does not grow any more. This mechanism is known as "pre-locking" tables before execution. The motivation for locking all the tables (possibly) used at once is to prevent dead locks. One problem with this approach is that, if the execution path the code really takes during runtime does not use a given table, and if the table is missing, the server would not execute the statement. This in particular has a major impact on triggers, since a missing table referenced by an update/delete trigger would prevent an insert trigger to run. Another problem is that stored routines might define SQL exception handlers to deal with missing tables, but the server implementation would never give user code a chance to execute this logic, since the routine is never executed when a missing table cause the pre-locking code to fail. With this fix, the internal implementation of the pre-locking code has been relaxed of some constraints, so that failure to open a table does not necessarily prevent execution of a stored routine. In particular, the pre-locking mechanism is now behaving as follows: 1) the first step, to compute the transitive closure of all the tables possibly referenced by a statement, is unchanged. 2) the next step, which is to open all the tables involved, only attempts to open the tables added by the pre-locking code, but silently fails without reporting any error or invoking any exception handler is the table is not present. This is achieved by trapping internal errors with Prelock_error_handler 3) the locking step only locks tables that were successfully opened. 4) when executing sub statements, the list of tables used by each statements is evaluated as before. The tables needed by the sub statement are expected to be already opened and locked. Statement referencing tables that were not opened in step 2) will fail to find the table in the open list, and only at this point will execution of the user code fail. 5) when a runtime exception is raised at 4), the instruction continuation destination (the next instruction to execute in case of SQL continue handlers) is evaluated. This is achieved with sp_instr::exec_open_and_lock_tables() 6) if a user exception handler is present in the stored routine, that handler is invoked as usual, so that ER_NO_SUCH_TABLE exceptions can be trapped by stored routines. If no handler exists, then the runtime execution will fail as expected. With all these changes, a side effect is that view security is impacted, in two different ways. First, a view defined as "select stored_function()", where the stored function references a table that may not exist, is considered valid. The rationale is that, because the stored function might trap exceptions during execution and still return a valid result, there is no way to decide when the view is created if a missing table really cause the view to be invalid. Secondly, testing for existence of tables is now done later during execution. View security, which consist of trapping errors and return a generic ER_VIEW_INVALID (to prevent disclosing information) was only implemented at very specific phases covering *opening* tables, but not covering the runtime execution. Because of this existing limitation, errors that were previously trapped and converted into ER_VIEW_INVALID are not trapped, causing table names to be reported to the user. This change is exposing an existing problem, which is independent and will be resolved separately. mysql-test/r/information_schema_db.result: Revised the pre-locking code implementation, aligned the tests. mysql-test/r/sp-error.result: Revised the pre-locking code implementation, aligned the tests. mysql-test/r/sp.result: Revised the pre-locking code implementation, aligned the tests. mysql-test/r/trigger.result: Revised the pre-locking code implementation, aligned the tests. mysql-test/r/view.result: Revised the pre-locking code implementation, aligned the tests. mysql-test/t/sp-error.test: Revised the pre-locking code implementation, aligned the tests. mysql-test/t/sp.test: Revised the pre-locking code implementation, aligned the tests. mysql-test/t/trigger.test: Revised the pre-locking code implementation, aligned the tests. sql/lock.cc: table->placeholder now checks for schema_table sql/mysqld.cc: my_message_sql(): invoke internal exception handlers sql/sp_head.cc: exec_open_and_lock_tables(): open and lock tables, or return the continuation destination of this instruction sql/sp_head.h: exec_open_and_lock_tables(): open and lock tables, or return the continuation destination of this instruction sql/sql_base.cc: Prelock_error_handler: delay open table errors until execution sql/sql_class.cc: THD: add internal error handler, as an exception mechanism. sql/sql_class.h: THD: add internal error handler, as an exception mechanism. sql/sql_update.cc: table->placeholder now checks for schema_table sql/table.cc: st_table_list::hide_view_error(): masked more errors for view security sql/table.h: table->placeholder now checks for schema_table, and unopened tables
-
- 01 Feb, 2007 1 commit
-
-
unknown authored
merge of the 5.0-opt tree mysql-test/r/trigger.result: merge of the 5.0-opt tree
-
- 12 Jan, 2007 1 commit
-
-
unknown authored
in a select list. The objects of the Item_trigger_field class inherited the implementations of the methods copy_or_same, get_tmp_table_item and get_tmp_table_field from the class Item_field while they rather should have used the default implementations defined for the base class Item. It could cause catastrophic problems for triggers that used SELECTs with select list containing trigger fields such as NEW.<table column> under DISTINCT. mysql-test/r/trigger.result: Added a test case for bug #25398. mysql-test/t/trigger.test: Added a test case for bug #25398.
-
- 21 Nov, 2006 1 commit
-
-
unknown authored
This is the 5.0 part of the fix. Currently TRUNCATE command will not call delete_all_rows() in the handler (that implements the "fast" TRUNCATE for InnoDB) when there are triggers on the table. As decided by the architecture team TRUNCATE must use "fast" TRUNCATE even when there are triggers. Thus it must ignore the triggers. Made TRUNCATE to ignore the triggers and call delete_all_rows() for all storage engines to maintain engine consistency. mysql-test/r/trigger.result: Bug#23556: TRUNCATE TABLE still maps to DELETE - test case mysql-test/t/trigger.test: Bug#23556: TRUNCATE TABLE still maps to DELETE - test case sql/sql_delete.cc: Bug#23556: TRUNCATE TABLE still maps to DELETE - We implemenent fast TRUNCATE for InnoDB even if triggers are present. - TRUNCATE ignores triggers.
-
- 13 Nov, 2006 2 commits
-
-
unknown authored
This change set implements the DROP TRIGGER IF EXISTS functionality. This fix is considered a bug and not a feature, because without it, there is no known method to write a database creation script that can create a trigger without failing, when executed on a database that may or may not contain already a trigger of the same name. Implementing this functionality closes an orthogonality gap between triggers and stored procedures / stored functions (which do support the DROP IF EXISTS syntax). In sql_trigger.cc, in mysql_create_or_drop_trigger, the code has been reordered to: - perform the tests that do not depend on the file system (access()), - get the locks (wait_if_global_read_lock, LOCK_open) - call access() - perform the operation - write to the binlog - unlock (LOCK_open, start_waiting_global_read_lock) This is to ensure that all the code that depends on the presence of the trigger file is executed in the same critical section, and prevents race conditions similar to the case fixed by Bug 14262 : - thread 1 executes DROP TRIGGER IF EXISTS, access() returns a failure - thread 2 executes CREATE TRIGGER - thread 2 logs CREATE TRIGGER - thread 1 logs DROP TRIGGER IF EXISTS The patch itself is based on code contributed by the MySQL community, under the terms of the Contributor License Agreement (See Bug 18161). mysql-test/r/rpl_trigger.result: DROP TRIGGER IF EXISTS mysql-test/r/trigger.result: DROP TRIGGER IF EXISTS mysql-test/t/rpl_trigger.test: DROP TRIGGER IF EXISTS mysql-test/t/trigger.test: DROP TRIGGER IF EXISTS sql/sql_trigger.cc: DROP TRIGGER IF EXISTS sql/sql_yacc.yy: DROP TRIGGER IF EXISTS
-
unknown authored
stored function invoked from different connections". Invocation of trigger which was using stored function from different connections caused server crashes (for non-debug server this happened in highly concurrent environment, but debug server failed on assertion in relatively simple scenario). Item_func_sp was not safe to use in triggers (in other words for re-execution from different threads) as artificial TABLE object pointed by Item_func_sp::dummy_table referenced incorrect THD object. To fix the problem we force re-initialization of this object for each re-execution of statement. mysql-test/r/trigger.result: Added test for bug#23651 "Server crashes when trigger which uses stored function invoked from different connections". mysql-test/t/trigger.test: Added test for bug#23651 "Server crashes when trigger which uses stored function invoked from different connections". sql/item_func.cc: To make Item_func_sp safe for usage in triggers (in other words safe for re-execution in different threads) we need to ensure that artificial TABLE object pointed by Item_func_sp::dummy_table references correct THD object. To achieve this we simply force its re-initialization for each re-execution of statement.
-
- 19 Oct, 2006 2 commits
-
-
unknown authored
This patch reverts a change introduced by Bug 6951, which incorrectly set thd->abort_on_warning for stored procedures. As per internal discussions about the SQL_MODE=TRADITIONAL, the correct behavior is to *not* abort on warnings even inside an INSERT/UPDATE trigger. Tests for Stored Procedures, Stored Functions, Triggers involving SQL_MODE have been included or revised, to reflect the intended behavior. (reposting approved patch, to work around source control issues, no review needed) mysql-test/include/sp-vars.inc: Tests for SQL_MODE='TRADITIONAL' mysql-test/r/sp-vars.result: Tests for SQL_MODE='TRADITIONAL' mysql-test/r/sp.result: Tests for SQL_MODE='TRADITIONAL' mysql-test/r/trigger.result: Tests for SQL_MODE='TRADITIONAL' mysql-test/t/sp-vars.test: Tests for SQL_MODE='TRADITIONAL' mysql-test/t/sp.test: Tests for SQL_MODE='TRADITIONAL' mysql-test/t/trigger.test: Tests for SQL_MODE='TRADITIONAL' sql/sp_head.cc: For SQL_MODE='TRADITIONAL', thd->abort_on_warning should be set only when assigning a *column*
-
unknown authored
mysql-test/r/sp.result: fix merge issues mysql-test/r/trigger.result: fix merge issues
-
- 17 Oct, 2006 1 commit
-
-
unknown authored
This patch reverts a change introduced by Bug 6951, which incorrectly set thd->abort_on_warning for stored procedures. As per internal discussions about the SQL_MODE=TRADITIONAL, the correct behavior is to *not* abort on warnings even inside an INSERT/UPDATE trigger. Tests for Stored Procedures, Stored Functions, Triggers involving SQL_MODE have been included or revised, to reflect the intended behavior. mysql-test/include/sp-vars.inc: Tests for SQL_MODE='TRADITIONAL' mysql-test/r/sp-vars.result: Tests for SQL_MODE='TRADITIONAL' mysql-test/r/sp.result: Tests for SQL_MODE='TRADITIONAL' mysql-test/r/trigger.result: Tests for SQL_MODE='TRADITIONAL' mysql-test/t/sp-vars.test: Tests for SQL_MODE='TRADITIONAL' mysql-test/t/sp.test: Tests for SQL_MODE='TRADITIONAL' mysql-test/t/trigger.test: Tests for SQL_MODE='TRADITIONAL'. Revised the expected behavior for triggers sql/sp_head.cc: For SQL_MODE='TRADITIONAL', thd->abort_on_warning should be set only when assigning a *column*
-
- 21 Sep, 2006 1 commit
-
-
unknown authored
this key does not stop" (version for 5.0 only). UPDATE statement which WHERE clause used key and which invoked trigger that modified field in this key worked indefinetely. This problem occured because in cases when UPDATE statement was executed in update-on-the-fly mode (in which row is updated right during evaluation of select for WHERE clause) the new version of the row became visible to select representing WHERE clause and was updated again and again. We already solve this problem for UPDATE statements which does not invoke triggers by detecting the fact that we are going to update field in key used for scanning and performing update in two steps, during the first step we gather information about the rows to be updated and then doing actual updates. We also do this for MULTI-UPDATE and in its case we even detect situation when such fields are updated in triggers (actually we simply assume that we always update fields used in key if we have before update trigger). The fix simply extends this check which is done in check_if_key_used()/ QUICK_SELECT_I::check_if_keys_used() routine/method in such way that it also detects cases when field used in key is updated in trigger. As nice side-effect we have more precise and thus more optimal perfomance-wise check for the MULTI-UPDATE. Also check_if_key_used()/QUICK_SELECT_I::check_if_keys_used() were renamed to is_key_used()/QUICK_SELECT_I::is_keys_used() in order to better reflect that boolean predicate. Note that this check is implemented in much more elegant way in 5.1 mysql-test/r/trigger.result: Added test case for bug#20670 "UPDATE using key and invoking trigger that modifies this key does not stop". mysql-test/t/trigger.test: Added test case for bug#20670 "UPDATE using key and invoking trigger that modifies this key does not stop". sql/key.cc: Renamed check_if_key_used() to is_key_used(). Also this routine checks if key uses field which can be updated by before update trigger defined on the table. As result we avoid using update-on-the-fly method in cases when trigger updates part of key which is used by select which filters rows to be updated and thus avoid infinite updates. By doing such check here we cover both UPDATE and MULTI-UPDATE cases. sql/mysql_priv.h: Renamed check_if_key_used() to is_key_used(). sql/opt_range.cc: Renamed check_if_key_used()/QUICK_SELECT_I::check_if_keys_used() to is_key_used()/QUICK_SELECT_I::is_keys_used(). sql/opt_range.h: Renamed QUICK_SELECT_I::check_if_keys_used() method to is_keys_used(), also updated comment describing it to reflect its extended semantics (this change was caused by change in check_if_key_used()/is_key_used() routine semantics). sql/sql_trigger.cc: Introduced Table_triggers_list::is_updated_in_before_update_triggers() method which is needed for checking if field of subject table can be changed in before update trigger. sql/sql_trigger.h: Table_triggers_list: Removed has_before_update_triggers() method which is not used any longer. Added declaration of is_updated_in_before_update_triggers() which is needed for checking if field of subject table can be changed by before update trigger. sql/sql_update.cc: safe_update_on_fly(): check_if_key_used() routine and check_if_keys_used() method were renamed to is_key_used()/is_keys_used(). Now cases when trigger updates fields which are part of key used for filtering rows for update are caught directly in is_key_used(). This also allows to cover both UPDATE and MULTI-UPDATE cases.
-
- 25 Aug, 2006 1 commit
-
-
unknown authored
erroneous check Problem: Actually there were two problems in the server code. The check for SQLCOM_FLUSH in SF/Triggers were not according to the existing architecture which uses sp_get_flags_for_command() from sp_head.cc . This function was also missing a check for SQLCOM_FLUSH which has a problem combined with prelocking. This changeset fixes both of these deficiencies as well as the erroneous check in sp_head::is_not_allowed_in_function() which was a copy&paste error. mysql-test/r/sp-error.result: update result mysql-test/r/trigger.result: update result mysql-test/t/sp-error.test: FLUSH can create a problem with prelocking, hence it's disabled. There is a better way to check this than a check in the parser. Now we use sp_get_flags_for_command() and the error returned is different. mysql-test/t/trigger.test: FLUSH can create a problem with prelocking, hence it's disabled. There is a better way to check this than a check in the parser. Now we use sp_get_flags_for_command() and the error returned is different. sql/sp_head.cc: FLUSH and RESET are not allowed inside a SF/Trigger. Because they don't imply a COMMIT sp_head::HAS_COMMIT_OR_ROLLBACK cannot be used. Two new flags were introduced for that reason. sql/sp_head.h: Don't check m_type as this check is erroneous. This is probably a copy and paste error when moving code from somewhere else. Another fact which supports this was prefixing the enum value with the name of class sp_head. Adding two new flags HAS_SQLCOM_RESET and HAS_SQLCOM_FLUSH. The values are 2048 and 4096 because in the 5.1 branch there are already new flags which are with values up-to 1024. sql/sql_parse.cc: FLUSH can cause a problem with prelocking in SF/Trigger and therefore is already disabled. RESET is also disabled because is handled by the same code as FLUSH. We won't allow RESET inside SF/Trigger at that stage without thorough analysis. The check for them is already done in the parser by calling is_not_allowed_in_function() sql/sql_yacc.yy: By listing SQLCOM_FLUSH as command which implies COMMIT in sp_get_flags_for_command() the check in sql_yacc.yy is obsolete.
-
- 24 Aug, 2006 1 commit
-
-
unknown authored
User name (host name) has limit on length. The server code relies on these limits when storing the names. The problem was that sometimes these limits were not checked properly, so that could lead to buffer overflow. The fix is to check length of user/host name in parser and if string is too long, throw an error. mysql-test/r/grant.result: Updated result file. mysql-test/r/sp.result: Updated result file. mysql-test/r/trigger.result: Updated result file. mysql-test/r/view.result: Updated result file. mysql-test/t/grant.test: Added test for BUG#16899. mysql-test/t/sp.test: Added test for BUG#16899. mysql-test/t/trigger.test: Added test for BUG#16899. mysql-test/t/view.test: Added test for BUG#16899. sql/mysql_priv.h: Added prototype for new function. sql/sql_acl.cc: Remove outdated checks. sql/sql_parse.cc: Add a new function for checking string length. sql/share/errmsg.txt: Added new resources. sql/sql_yacc.yy: Check length of user/host name.
-
- 23 Aug, 2006 1 commit
-
-
unknown authored
User name (host name) has limit on length. The server code relies on these limits when storing the names. The problem was that sometimes these limits were not checked properly, so that could lead to buffer overflow. The fix is to check length of user/host name in parser and if string is too long, throw an error. mysql-test/r/grant.result: Updated result file. mysql-test/r/sp.result: Updated result file. mysql-test/r/trigger.result: Updated result file. mysql-test/r/view.result: Updated result file. mysql-test/t/grant.test: Added test for BUG#16899. mysql-test/t/sp.test: Added test for BUG#16899. mysql-test/t/trigger.test: Added test for BUG#16899. mysql-test/t/view.test: Added test for BUG#16899. sql/mysql_priv.h: Added prototype for new function. sql/share/errmsg.txt: Added new resources. sql/sql_acl.cc: Remove outdated checks. sql/sql_parse.cc: Add a new function for checking string length. sql/sql_yacc.yy: Check length of user/host name.
-
- 28 Jun, 2006 2 commits
-
-
unknown authored
It was hard to distinguish case, when one was unable to create trigger on the table because trigger with same action time and event already existed for this table, from the case, when one tried to create trigger with name which was already occupied by some other trigger, since in both these cases we emitted ER_TRG_ALREADY_EXISTS error and message. Now we emit ER_NOT_SUPPORTED_YET error with appropriate additional message in the first case. There is no sense in introducing separate error for this situation since we plan to get rid of this limitation eventually. mysql-test/r/trigger.result: Update result for new error message. mysql-test/t/trigger.test: Update test for new error code. sql/sql_trigger.cc: If there is already a trigger with the same activation time, report an "Unsupported yet" error.
-
unknown authored
Bug #18361: Triggers on mysql.user table cause server crash Because they do not work, we do not allow creating triggers on tables within the 'mysql' schema. (They may be made to work and re-enabled at some later date, but not in 5.0 or 5.1.) mysql-test/r/trigger.result: Add new results mysql-test/t/trigger.test: Add new regression test for creating triggers on system schema sql/share/errmsg.txt: Add new error message sql/sql_trigger.cc: Disallow creating triggers on tables in the 'mysql' schema
-
- 16 Jun, 2006 1 commit
-
-
unknown authored
INSERT triggers". In cases when REPLACE was internally executed via update and table had on update (on delete) triggers defined we exposed the fact that such optimization used by callng on update (not calling on delete) triggers. Such behavior contradicts our documentation which describes REPLACE as INSERT with optional DELETE. This fix just disables this optimization for tables with on delete triggers. The optimization is still applied for tables which have on update but have no on delete triggers, we just don't invoke on update triggers in this case and thus don't expose information about optimization to user. Also added test coverage for values returned by ROW_COUNT() function (and thus for values returned by mysql_affected_rows()) for various forms of INSERT. mysql-test/r/insert.result: Added test for values returned by ROW_COUNT() function (and thus for values returned by mysql_affected_rows()) for various forms of INSERT. We didn't have coverage for this before and since this fix touches related code it is better to add it now. mysql-test/r/trigger.result: Adjusted test after fixing bug#13479 "REPLACE activates UPDATE trigger, not the DELETE and INSERT triggers". mysql-test/t/insert.test: Added test for values returned by ROW_COUNT() function (and thus for values returned by mysql_affected_rows()) for various forms of INSERT. We didn't have coverage for this before and since this fix touches related code it is better to add it now. mysql-test/t/trigger.test: Adjusted test after fixing bug#13479 "REPLACE activates UPDATE trigger, not the DELETE and INSERT triggers". sql/sql_insert.cc: write_record(): We should not expose that internally we sometimes execute REPLACE via UPDATE instead of documented INSERT + DELETE pair. So we should not use this optimization for tables with on delete triggers. OTOH it is ok to use it for tables which have on update but have no on delete triggers, we just should not invoke on update triggers in this case.
-
- 12 May, 2006 1 commit
-
-
unknown authored
from within triggers Add support for passing NEW.x as INOUT and OUT parameters to stored procedures. Passing NEW.x as INOUT parameter requires SELECT and UPDATE privileges on that column, and passing it as OUT parameter requires only UPDATE privilege. mysql-test/r/sp-error.result: Update the result for new message. mysql-test/r/trigger-grant.result: Add result for bug#14635. mysql-test/r/trigger.result: Add result for bug#14635. mysql-test/t/trigger-grant.test: Add test case for bug#14635. mysql-test/t/trigger.test: Add test case for bug#14635. sql/item.cc: Add implementations of set_value() and set_required_privilege() methods of Settable_routine_parameter interface. Use Item_trigger_field::want_privilege instead of Item_trigger_field::access_type. Reset privileges on Item_trigger_field::cleanup(). sql/item.h: Add interface class Settable_routine_parameter and interface query method to Item class. Item_splocal and Item_trigger_field implement this interface. For Item_trigger_field: - add read_only attribute and is_read_only() method. - remove access_type and add original_privilege and want_privilege instead. - add set_value() method. - add reset_privilege() method. sql/item_func.cc: Add implementations of set_value() method of Settable_routine_parameter interface. sql/item_func.h: Item_func_get_user_var implements Settable_routine_parameter interface. sql/share/errmsg.txt: Update english ER_SP_NOT_VAR_ARG message. sql/sp_head.cc: Use Settable_routine_parameter interface for parameter update. sql/sql_yacc.yy: Set read_only and want_privilege members in Item_trigger_field appropriately. For NEW.x trigger variable used in left-hand-side of SET statement the latter is set to UPDATE_ACL, otherwise it is set to SELECT_ACL (but see Item_trigger_field::set_required_privilege(), where it may be updated to different value).
-
- 10 May, 2006 1 commit
-
-
unknown authored
-
- 19 Apr, 2006 2 commits
-
-
unknown authored
which was caused by the same bulk insert optimization as bug #17764 but had slightly different symptoms.
-
unknown authored
While executing a trigger, we have to set thd->abort_on_warning to the value it had at trigger creation time. mysql-test/r/trigger.result: Add result for bug#6951. mysql-test/t/trigger.test: Add test case for bug#6951. sql/sp_head.cc: While executing a trigger, set thd->abort_on_warning to the value it had at trigger creation time.
-
- 18 Apr, 2006 1 commit
-
-
unknown authored
mysql-test/r/ctype_latin2_ch.result: Drot table at end of test mysql-test/r/grant2.result: Drop users mysql-test/r/openssl_1.result: Drop users mysql-test/r/rpl_openssl.result: Drop users mysql-test/r/sp-security.result: Drop users mysql-test/r/sp_notembedded.result: Drop table mysql-test/r/trigger.result: Drop table mysql-test/t/ctype_latin2_ch.test: Drop table mysql-test/t/grant2.test: Drop users mysql-test/t/openssl_1.test: Drop users mysql-test/t/rpl_openssl.test: Drop users mysql-test/t/sp-security.test: Drop users mysql-test/t/sp_notembedded.test: Drop table mysql-test/t/trigger.test: Drop table
-
- 12 Apr, 2006 1 commit
-
-
unknown authored
CONNECTION_ID() was implemented as a constant Item, i.e. an instance of Item_static_int_func class holding value computed at creation time. Since Items are created on parsing, and trigger statements are parsed on table open, the first connection to open a particular table would effectively set its own CONNECTION_ID() inside trigger statements for that table. Re-implement CONNECTION_ID() as a class derived from Item_int_func, and compute connection_id on every call to fix_fields(). mysql-test/r/trigger.result: Add result for bug#16461. mysql-test/t/trigger.test: Add test case for bug#16461. sql/item.cc: Remove now unused class Item_static_int_func. sql/item.h: Remove now unused class Item_static_int_func. sql/item_create.cc: Use new implementation of CONNECTION_ID(). sql/item_func.cc: Re-implement CONNECTION_ID() as Item_func_connection_id (was Item_static_int_func). Set max_length to 10, as it was before. Compute connection_id dynamically on every call to fix_fields(). sql/item_func.h: Re-implement CONNECTION_ID() as Item_func_connection_id (was Item_static_int_func).
-