From 5826a5c490df8540fbc2b5bed6efad38723619c3 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 7 Dec 2007 15:39:41 +0100 Subject: Bug #27440 read_only allows create and drop database When read_only option was enabled, a user without SUPER privilege could perform CREATE DATABASE and DROP DATABASE operations. This patch adds a check to make sure this isn't possible. It also attempts to simplify the logic used to determine if relevant tables are updated, making it more human readable. mysql-test/r/read_only.result: Updated result file mysql-test/t/read_only.test: A test case is added which shows that it is not possible to drop or create a database in read-only mode despite having the GRANT permissions to do so, SUPER user excepted. sql/sql_parse.cc: - Simplified complex predicate by grouping it in a read friendly way. - Added predicate to fail on database updates while running in read-only mode. --- mysql-test/r/read_only.result | 31 +++++++++++++++++++++++++++++++ mysql-test/t/read_only.test | 34 ++++++++++++++++++++++++++++++++++ 2 files changed, 65 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/read_only.result b/mysql-test/r/read_only.result index 69d25fbef6f..827a137f5b2 100644 --- a/mysql-test/r/read_only.result +++ b/mysql-test/r/read_only.result @@ -46,4 +46,35 @@ Warnings: Note 1051 Unknown table 'ttt' drop table t1,t2; drop user test@localhost; +# +# Bug #27440 read_only allows create and drop database +# +drop database if exists mysqltest_db1; +drop database if exists mysqltest_db2; +delete from mysql.user where User like 'mysqltest_%'; +delete from mysql.db where User like 'mysqltest_%'; +delete from mysql.tables_priv where User like 'mysqltest_%'; +delete from mysql.columns_priv where User like 'mysqltest_%'; +flush privileges; +grant all on mysqltest_db2.* to `mysqltest_u1`@`%`; +create database mysqltest_db1; +grant all on mysqltest_db1.* to `mysqltest_u1`@`%`; +flush privileges; +show grants for current_user(); +Grants for mysqltest_u1@% +GRANT USAGE ON *.* TO 'mysqltest_u1'@'%' +GRANT ALL PRIVILEGES ON `mysqltest_db2`.* TO 'mysqltest_u1'@'%' +GRANT ALL PRIVILEGES ON `mysqltest_db1`.* TO 'mysqltest_u1'@'%' +create database mysqltest_db2; +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +show databases like '%mysqltest_db2%'; +Database (%mysqltest_db2%) +drop database mysqltest_db1; +ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement +delete from mysql.user where User like 'mysqltest_%'; +delete from mysql.db where User like 'mysqltest_%'; +delete from mysql.tables_priv where User like 'mysqltest_%'; +delete from mysql.columns_priv where User like 'mysqltest_%'; +flush privileges; +drop database mysqltest_db1; set global read_only=0; diff --git a/mysql-test/t/read_only.test b/mysql-test/t/read_only.test index 8e14b310f4c..5ec062bc103 100644 --- a/mysql-test/t/read_only.test +++ b/mysql-test/t/read_only.test @@ -117,4 +117,38 @@ connection default; drop table t1,t2; drop user test@localhost; +--echo # +--echo # Bug #27440 read_only allows create and drop database +--echo # +--disable_warnings +drop database if exists mysqltest_db1; +drop database if exists mysqltest_db2; +--enable_warnings + +delete from mysql.user where User like 'mysqltest_%'; +delete from mysql.db where User like 'mysqltest_%'; +delete from mysql.tables_priv where User like 'mysqltest_%'; +delete from mysql.columns_priv where User like 'mysqltest_%'; +flush privileges; + +grant all on mysqltest_db2.* to `mysqltest_u1`@`%`; +create database mysqltest_db1; +grant all on mysqltest_db1.* to `mysqltest_u1`@`%`; +flush privileges; +connect (con_bug27440,127.0.0.1,mysqltest_u1,,test,$MASTER_MYPORT,); +connection con_bug27440; +show grants for current_user(); +--error ER_OPTION_PREVENTS_STATEMENT +create database mysqltest_db2; +show databases like '%mysqltest_db2%'; +--error ER_OPTION_PREVENTS_STATEMENT +drop database mysqltest_db1; +disconnect con_bug27440; +connection default; +delete from mysql.user where User like 'mysqltest_%'; +delete from mysql.db where User like 'mysqltest_%'; +delete from mysql.tables_priv where User like 'mysqltest_%'; +delete from mysql.columns_priv where User like 'mysqltest_%'; +flush privileges; +drop database mysqltest_db1; set global read_only=0; -- cgit v1.2.1 From dcc2e663916925c152c14599751ab107e0ae7999 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 10 Dec 2007 16:16:21 +0100 Subject: Test fails because of non deterministric function: show grants for current_user() mysql-test/r/read_only.result: Removing non deterministic test results from test. mysql-test/t/read_only.test: Removing non deterministic test results from test. --- mysql-test/r/read_only.result | 5 ----- mysql-test/t/read_only.test | 1 - 2 files changed, 6 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/read_only.result b/mysql-test/r/read_only.result index 827a137f5b2..1bf99a8ea07 100644 --- a/mysql-test/r/read_only.result +++ b/mysql-test/r/read_only.result @@ -60,11 +60,6 @@ grant all on mysqltest_db2.* to `mysqltest_u1`@`%`; create database mysqltest_db1; grant all on mysqltest_db1.* to `mysqltest_u1`@`%`; flush privileges; -show grants for current_user(); -Grants for mysqltest_u1@% -GRANT USAGE ON *.* TO 'mysqltest_u1'@'%' -GRANT ALL PRIVILEGES ON `mysqltest_db2`.* TO 'mysqltest_u1'@'%' -GRANT ALL PRIVILEGES ON `mysqltest_db1`.* TO 'mysqltest_u1'@'%' create database mysqltest_db2; ERROR HY000: The MySQL server is running with the --read-only option so it cannot execute this statement show databases like '%mysqltest_db2%'; diff --git a/mysql-test/t/read_only.test b/mysql-test/t/read_only.test index 5ec062bc103..cca9bbd6fde 100644 --- a/mysql-test/t/read_only.test +++ b/mysql-test/t/read_only.test @@ -137,7 +137,6 @@ grant all on mysqltest_db1.* to `mysqltest_u1`@`%`; flush privileges; connect (con_bug27440,127.0.0.1,mysqltest_u1,,test,$MASTER_MYPORT,); connection con_bug27440; -show grants for current_user(); --error ER_OPTION_PREVENTS_STATEMENT create database mysqltest_db2; show databases like '%mysqltest_db2%'; -- cgit v1.2.1 From 2db4748bf2675a1d0b33039c425175f4e2b39940 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 11 Dec 2007 17:30:42 -0500 Subject: Bug #30651 Problems with thread_handling system variable Changed thread_handling variable to a global only, read only variable, as it is currently used. --- mysql-test/r/no-threads.result | 4 ++++ mysql-test/t/no-threads.test | 10 ++++++++++ 2 files changed, 14 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/no-threads.result b/mysql-test/r/no-threads.result index aefecd0f7bc..9bc2dad6e2a 100644 --- a/mysql-test/r/no-threads.result +++ b/mysql-test/r/no-threads.result @@ -7,3 +7,7 @@ select 1+2; SHOW GLOBAL VARIABLES LIKE 'thread_handling'; Variable_name Value thread_handling no-threads +select @@session.thread_handling; +ERROR HY000: Variable 'thread_handling' is a GLOBAL variable +set GLOBAL thread_handling='one-thread'; +ERROR HY000: Variable 'thread_handling' is a read only variable diff --git a/mysql-test/t/no-threads.test b/mysql-test/t/no-threads.test index 31ea6406ee9..fd8365e5678 100644 --- a/mysql-test/t/no-threads.test +++ b/mysql-test/t/no-threads.test @@ -4,3 +4,13 @@ select 1+1; select 1+2; SHOW GLOBAL VARIABLES LIKE 'thread_handling'; + +# +# Bug #30651 Problems with thread_handling system variable +# + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +select @@session.thread_handling; + +--error ER_INCORRECT_GLOBAL_LOCAL_VAR +set GLOBAL thread_handling='one-thread'; -- cgit v1.2.1 From 2a0d2fef51219846e51eae8a56b2de45193edc37 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 12 Dec 2007 18:21:01 +0300 Subject: Bug#12713 "Error in a stored function called from a SELECT doesn't cause ROLLBACK of statement", part 1. Review fixes. Do not send OK/EOF packets to the client until we reached the end of the current statement. This is a consolidation, to keep the functionality that is shared by all SQL statements in one place in the server. Currently this functionality includes: - close_thread_tables() - log_slow_statement(). After this patch and the subsequent patch for Bug#12713, it shall also include: - ha_autocommit_or_rollback() - net_end_statement() - query_cache_end_of_result(). In future it may also include: - mysql_reset_thd_for_next_command(). include/mysql_com.h: Rename now unused members of NET: no_send_ok, no_send_error, report_error. These were server-specific variables related to the client/server protocol. They have been made obsolete by this patch. Previously the same members of NET were used to store the error message both on the client and on the server. The error message was stored in net.last_error (client: mysql->net.last_error, server: thd->net.last_error). The error code was stored in net.last_errno (client: mysql->net.last_errno, server: thd->net.last_errno). The server error code and message are now stored elsewhere (in the Diagnostics_area), thus NET members are no longer used by the server. Rename last_error to client_last_error, last_errno to client_last_errno to avoid potential bugs introduced by merges. include/mysql_h.ic: Update the ABI file to reflect a rename. Renames do not break the binary compatibility. libmysql/libmysql.c: Rename last_error to client_last_error, last_errno to client_last_errno. This is necessary to ensure no unnoticed bugs introduced by merged changesets. Remove net.report_error, net.no_send_ok, net.no_send_error. libmysql/manager.c: Rename net.last_errno to net.client_last_errno. libmysqld/lib_sql.cc: Rename net.last_errno to net.client_last_errno. Update the embedded implementation of the client-server protocol to reflect the refactoring of protocol.cc. libmysqld/libmysqld.c: Rename net.last_errno to net.client_last_errno. mysql-test/r/events.result: Update to reflect the change in mysql_rm_db(). Now we drop stored routines and events for a given database name only if there is a directory for this database name. ha_drop_database() and query_cache_invalidate() are called likewise. Previously we would attempt to drop routines/events even if database directory was not found (it worked, since routines and events are stored in tables). This fixes Bug 29958 "Weird message on DROP DATABASE if mysql.proc does not exist". The change was done because the previous code used to call send_ok() twice, which led to an assertion failure when asserts against it were added by this patch. mysql-test/r/grant.result: Fix the patch for Bug 16470, now FLUSH PRIVILEGES produces an error if mysql.procs_priv is missing. This fixes the assert that send_ok() must not called after send_error() (the original patch for Bug 16470 was prone to this). mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result: Produce a more detailed error message. mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result: Produce a more detailed error message. mysql-test/t/grant.test: Update the test, now FLUSH PRIVILEGES returns an error if mysql.procs_priv is missing. server-tools/instance-manager/mysql_connection.cc: Rename net.last_errno to net.client_last_errno. sql/ha_ndbcluster_binlog.cc: Add asserts. Use getters to access statement status information. Add a comment why run_query() is broken. Reset the diagnostics area in the end of run_query() to fulfill the invariant that the diagnostics_area is never assigned twice per statement (see the comment in the code when this can happen). We still do not clear thd->is_fatal_error and thd->is_slave_error, which may lead to bugs, I consider the whole affair as something to be dealt with separately. sql/ha_partition.cc: fatal_error() doesn't set an error by itself. Perhaps we should remove this method altogether and instead add a flag to my_error to set thd->is_fatal_error property. Meanwhile, this change is a part of inspection made to the entire source code with the goal to ensure that fatal_error() is always accompanied by my_error(). sql/item_func.cc: There is no net.last_error anymore. Remove the obsolete assignment. sql/log_event.cc: Use getters to access statement error status information. sql/log_event_old.cc: Use getters to access statement error status information. sql/mysqld.cc: Previously, if a continue handler for an error was found, my_message_sql() would not set an error in THD. Since the current statement must be aborted in any case, find_handler() had a hack to assign thd->net.report_error to 1. Remove this hack. Set an error in my_message_sql() even if the continue handler is found. The error will be cleared anyway when the handler is executed. This is one action among many in this patch to ensure the invariant that whenever thd->is_error() is TRUE, we have a message in thd->main_da.message(). sql/net_serv.cc: Use a full-blown my_error() in net_serv.cc to report an error, instead of just setting net->last_errno. This ensures the invariant that whenever thd->is_error() returns TRUE, we have a message in thd->main_da.message(). Remove initialization of removed NET members. sql/opt_range.cc: Use my_error() instead of just raising thd->net.report_error. This ensures the invariant that whenever thd->is_error() returns TRUE, there is a message in thd->main_da.message(). sql/opt_sum.cc: Move invocation of fatal_error() right next to the place where we set the error message. That makes it easier to track that whenever fatal_error() is called, there is a message in THD. sql/protocol.cc: Rename send_ok() and send_eof() to net_send_ok() and net_send_eof() respectively. These functions write directly to the network and are not for use anywhere outside the client/server protocol code. Remove the code that was responsible for cases when either there is no error code, or no error message, or both. Instead the calling code ensures that they are always present. Asserts are added to enforce the invariant. Instead of a direct access to thd->server_status and thd->total_warn_count use function parameters, since these from now on don't always come directly from THD. Introduce net_end_statement(), the single-entry-point replacement API for send_ok(), send_eof() and net_send_error(). Implement Protocol::end_partial_result_set to use in select_send::abort() when there is a continue handler. sql/protocol.h: Update declarations. sql/repl_failsafe.cc: Use getters to access statement status information in THD. Rename net.last_error to net.client_last_error. sql/rpl_record.cc: Set an error message in prepare_record() if there is no default value for the field -- later we do print this message to the client. sql/rpl_rli.cc: Use getters to access statement status information in THD. sql/slave.cc: In create_table_from_dump() (a common function that is used in LOAD MASTER TABLE SQL statement and COM_LOAD_MASTER_DATA), instead of hacks with no_send_ok, clear the diagnostics area when mysql_rm_table() succeeded. Update has_temporary_error() to work correctly when no error is set. This is the case when Incident_log_event is executed: it always returns an error but does not set an error message. Use getters to access error status information. sql/sp_head.cc: Instead of hacks with no_send_error, work through the diagnostics area interface to suppress sending of OK/ERROR packets to the client. Move query_cache_end_of_result before log_slow_statement(), similarly to how it's done in dispatch_command(). sql/sp_rcontext.cc: Remove hacks with assignment of thd->net.report_error, they are not necessary any more (see the changes in mysqld.cc). sql/sql_acl.cc: Use getters to access error status information in THD. sql/sql_base.cc: Access thd->main_da.sql_errno() only if there is an error. This fixes a bug when auto-discovery, that was effectively disabled under pre-locking. sql/sql_binlog.cc: Remove hacks with no_send_ok/no_send_error, they are not necessary anymore: the caller is responsible for network communication. sql/sql_cache.cc: Disable sending of OK/ERROR/EOF packet in the end of dispatch_command if the response has been served from the query cache. This raises the question whether we should store EOF packet in the query cache at all, or generate it anew for each statement (we should generate it anew), but this is to be addressed separately. sql/sql_class.cc: Implement class Diagnostics_area. Please see comments in sql_class.h for details. Fix a subtle coding mistake in select_send::send_data: when on slave, an error in Item::send() was ignored. The problem became visible due to asserts that the diagnostics area is never double assigned. Remove initialization of removed NET members. In select_send::abort() do not call select_send::send_eof(). This is not inheritance-safe. Even if a stored procedure continue handler is found, the current statement is aborted, not succeeded. Instead introduce a Protocol API to send the required response, Protocol::end_partial_result_set(). This simplifies implementation of select_send::send_eof(). No need to add more asserts that there is no error, there is an assert inside Diagnostics_area::set_ok_status() already. Leave no trace of no_send_* in the code. sql/sql_class.h: Declare class Diagnostics_area. Remove the hack with no_send_ok from Substatement_state. Provide inline implementations of send_ok/send_eof. Add commetns. sql/sql_connect.cc: Remove hacks with no_send_error. Since now an error in THD is always set if net->error, it's not necessary to check both net->error and thd->is_error() in the do_command loop. Use thd->main_da.message() instead of net->last_errno. Remove the hack with is_slave_error in sys_init_connect. Since now we do not reset the diagnostics area in net_send_error (it's reset at the beginning of the next statement), we can access it safely even after execute_init_command. sql/sql_db.cc: Update the code to satisfy the invariant that the diagnostics area is never assigned twice. Incidentally, this fixes Bug 29958 "Weird message on DROP DATABASE if mysql.proc does not exist". sql/sql_delete.cc: Change multi-delete to abort in abort(), as per select_send protocol. Fixes the merge error with the test for Bug 29136 sql/sql_derived.cc: Use getters to access error information. sql/sql_insert.cc: Use getters to access error information. sql-common/client.c: Rename last_error to client_last_error, last_errno to client_last_errno. sql/sql_parse.cc: Remove hacks with no_send_error. Deploy net_end_statement(). The story of COM_SHUTDOWN is interesting. Long story short, the server would become on its death's door, and only no_send_ok/no_send_error assigned by send_ok()/net_send_error() would hide its babbling from the client. First of all, COM_QUIT does not require a response. So, the comment saying "Let's send a response to possible COM_QUIT" is not only groundless (even mysqladmin shutdown/mysql_shutdown() doesn't send COM_QUIT after COM_SHUTDOWN), it's plainly incorrect. Secondly, besides this additional 'OK' packet to respond to a hypothetical COM_QUIT, there was the following code in dispatch_command(): if (thd->killed) thd->send_kill_message(); if (thd->is_error() net_send_error(thd); This worked out really funny for the thread through which COM_SHUTDOWN was delivered: we would get COM_SHUTDOWN, say okay, say okay again, kill everybody, get the kill signal ourselves, and then attempt to say "Server shutdown in progress" to the client that is very likely long gone. This all became visible when asserts were added that the Diagnostics_area is not assigned twice. Move query_cache_end_of_result() to the end of dispatch_command(), since net_send_eof() has been moved there. This is safe, query_cache_end_of_result() is a no-op if there is no started query in the cache. Consistently use select_send interface to call abort() or send_eof() depending on the operation result. Remove thd->fatal_error() from reset_master(), it was a no-op. in hacks with no_send_error woudl save us from complete breakage of the client/server protocol. Consistently use select_send::abort() whenever there is an error, and select_send::send_eof() in case of success. The issue became visible due to added asserts. sql/sql_partition.cc: Always set an error in THD whenever there is a call to fatal_error(). sql/sql_prepare.cc: Deploy class Diagnostics_area. Remove the unnecessary juggling with the protocol in Select_fetch_protocol_binary::send_eof(). EOF packet format is protocol-independent. sql/sql_select.cc: Call fatal_error() directly in opt_sum_query. Call my_error() whenever we call thd->fatal_error(). sql/sql_servers.cc: Use getters to access error information in THD. sql/sql_show.cc: Use getters to access error information in THD. Add comments. Call my_error() whenever we call fatal_error(). sql/sql_table.cc: Replace hacks with no_send_ok with the interface of the diagnostics area. Clear the error if ENOENT error in ha_delete_table(). sql/sql_update.cc: Introduce multi_update::abort(), which is the proper way to abort a multi-update. This fixes the merge conflict between this patch and the patch for Bug 29136. sql/table.cc: Use a getter to access error information in THD. sql/tztime.cc: Use a getter to access error information in THD. --- mysql-test/r/events.result | 3 ++- mysql-test/r/grant.result | 4 +--- mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result | 4 ++-- mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result | 4 ++-- mysql-test/t/grant.test | 1 + 5 files changed, 8 insertions(+), 8 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/events.result b/mysql-test/r/events.result index d1ee6d1c5a1..7b68914e219 100644 --- a/mysql-test/r/events.result +++ b/mysql-test/r/events.result @@ -403,9 +403,10 @@ ERROR 42S02: Table 'mysql.event' doesn't exist DROP DATABASE IF EXISTS mysqltest_no_such_database; Warnings: Note 1008 Can't drop database 'mysqltest_no_such_database'; database doesn't exist -Error 1146 Table 'mysql.event' doesn't exist CREATE DATABASE mysqltest_db2; DROP DATABASE mysqltest_db2; +Warnings: +Error 1146 Table 'mysql.event' doesn't exist OK, there is an unnecessary warning about the non-existent table but it's not easy to fix and no one complained about it. A similar warning is printed if mysql.proc is missing. diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 0d40778d5f2..b07f9c1e9e9 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -1229,9 +1229,7 @@ set names default; FLUSH PRIVILEGES without procs_priv table. RENAME TABLE mysql.procs_priv TO mysql.procs_gone; FLUSH PRIVILEGES; -Warnings: -Error 1146 Table 'mysql.procs_priv' doesn't exist -Error 1548 Cannot load from mysql.mysql.procs_priv. The table is probably corrupted +ERROR 42S02: Table 'mysql.procs_priv' doesn't exist Assigning privileges without procs_priv table. CREATE DATABASE mysqltest1; CREATE PROCEDURE mysqltest1.test() SQL SECURITY DEFINER diff --git a/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result b/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result index 06dc90f18aa..a8e1c8602e0 100644 --- a/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result +++ b/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result @@ -123,7 +123,7 @@ Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno 1364 -Last_Error Error in Write_rows event: error during transaction execution on table test.t1_nodef. +Last_Error Error in Write_rows event: error during transaction execution on table test.t1_nodef. Field 'x' doesn't have a default value Skip_Counter 0 Exec_Master_Log_Pos # Relay_Log_Space # @@ -141,7 +141,7 @@ Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error Last_SQL_Errno 1364 -Last_SQL_Error Error in Write_rows event: error during transaction execution on table test.t1_nodef. +Last_SQL_Error Error in Write_rows event: error during transaction execution on table test.t1_nodef. Field 'x' doesn't have a default value SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; INSERT INTO t9 VALUES (2); diff --git a/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result b/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result index 3911fe89b7f..12203379269 100644 --- a/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result +++ b/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result @@ -123,7 +123,7 @@ Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table Last_Errno 1364 -Last_Error Error in Write_rows event: error during transaction execution on table test.t1_nodef. +Last_Error Error in Write_rows event: error during transaction execution on table test.t1_nodef. Field 'x' doesn't have a default value Skip_Counter 0 Exec_Master_Log_Pos # Relay_Log_Space # @@ -141,7 +141,7 @@ Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error Last_SQL_Errno 1364 -Last_SQL_Error Error in Write_rows event: error during transaction execution on table test.t1_nodef. +Last_SQL_Error Error in Write_rows event: error during transaction execution on table test.t1_nodef. Field 'x' doesn't have a default value SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; INSERT INTO t9 VALUES (2); diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index 2a1940f4326..84660bbe66b 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -1284,6 +1284,7 @@ set names default; # --echo FLUSH PRIVILEGES without procs_priv table. RENAME TABLE mysql.procs_priv TO mysql.procs_gone; +--error ER_NO_SUCH_TABLE FLUSH PRIVILEGES; --echo Assigning privileges without procs_priv table. CREATE DATABASE mysqltest1; -- cgit v1.2.1 From e5a397e28f7db0b356d1eddd669591b76fa1c1f8 Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 12 Dec 2007 19:44:14 -0200 Subject: Bug#32395 Alter table under a impending global read lock causes a server crash The problem is that some DDL statements (ALTER TABLE, CREATE TRIGGER, FLUSH TABLES, ...) when under LOCK TABLES need to momentarily drop the lock, reopen the table and grab the write lock again (using reopen_tables). When grabbing the lock again, reopen_tables doesn't pass a flag to mysql_lock_tables in order to ignore the impending global read lock, which causes a assertion because LOCK_open is being hold. Also dropping the lock must not signal to any threads that the table has been relinquished (related to the locking/flushing protocol). The solution is to correct the way the table is reopenned and the locks grabbed. When reopening the table and under LOCK TABLES, the table version should be set to 0 so other threads have to wait for the table. When grabbing the lock, any other flush should be ignored because it's theoretically a atomic operation. The chosen solution also fixes a potential discrepancy between binlog and GRL (global read lock) because table placeholders were being ignored, now a FLUSH TABLES WITH READ LOCK will properly for table with open placeholders. It's also important to mention that this patch doesn't fix a potential deadlock if one uses two GRLs under LOCK TABLES concurrently. mysql-test/r/lock_multi.result: Add test case result for Bug#32395 mysql-test/r/trigger_notembedded.result: Add test case result for Bug#32395 mysql-test/t/lock_multi.test: Add test case for Bug#32395 mysql-test/t/trigger_notembedded.test: Enable test case for Bug#32395 sql/ha_ndbcluster.cc: Update close_cached_tables usage. sql/ha_ndbcluster_binlog.cc: Update close_cached_tables usage. sql/mysql_priv.h: Update close_cache_tables prototype. sql/set_var.cc: Update close_cached_tables usage and set flag to wait for tables with placeholders. This is one of the places where a GRL can be obtained. sql/sql_base.cc: Preserve old version for write locked tables and ignore pending flushes and update close_cache_tables to take into account name locked tables. sql/sql_parse.cc: Update close_cached_tables usage and pass flag so that name locked tables are waited for. sql/sql_table.cc: Protect the table against a impending GRL if under LOCK TABLES. --- mysql-test/r/lock_multi.result | 30 ++++++++++ mysql-test/r/trigger_notembedded.result | 14 +++++ mysql-test/t/lock_multi.test | 98 ++++++++++++++++++++++++++++++++- mysql-test/t/trigger_notembedded.test | 6 +- 4 files changed, 144 insertions(+), 4 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result index 4a0f70a7b88..9c4f1b17dcc 100644 --- a/mysql-test/r/lock_multi.result +++ b/mysql-test/r/lock_multi.result @@ -113,4 +113,34 @@ handler t1 open; ERROR HY000: Table storage engine for 't1' doesn't have this option --> client 1 drop table t1; +drop table if exists t1; +create table t1 (i int); +connection: default +lock tables t1 write; +connection: flush +flush tables with read lock;; +connection: default +alter table t1 add column j int; +connection: insert +insert into t1 values (1,2);; +connection: default +unlock tables; +connection: flush +select * from t1; +i j +unlock tables; +select * from t1; +i j +1 2 +drop table t1; +drop table if exists t1; +create table t1 (i int); +connection: default +lock tables t1 write; +connection: flush +flush tables with read lock;; +connection: default +flush tables; +unlock tables; +drop table t1; End of 5.1 tests diff --git a/mysql-test/r/trigger_notembedded.result b/mysql-test/r/trigger_notembedded.result index d56f83993a6..87e8f68da38 100644 --- a/mysql-test/r/trigger_notembedded.result +++ b/mysql-test/r/trigger_notembedded.result @@ -448,4 +448,18 @@ DROP TABLE t1; DROP DATABASE mysqltest_db1; USE test; End of 5.0 tests. +drop table if exists t1; +create table t1 (i int); +connection: default +lock tables t1 write; +connection: flush +flush tables with read lock;; +connection: default +create trigger t1_bi before insert on t1 for each row begin end; +unlock tables; +connection: flush +unlock tables; +select * from t1; +i +drop table t1; End of 5.1 tests. diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index b2266c9bff1..0d36b79df78 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -150,7 +150,7 @@ send SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1; connection locker; let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Locked" and info = + where state = "Waiting for table" and info = "SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1"; --source include/wait_condition.inc # Make test case independent from earlier grants. @@ -343,4 +343,100 @@ handler t1 open; connection default; drop table t1; +# +# Bug#32395 Alter table under a impending global read lock causes a server crash +# + +# +# Test ALTER TABLE under LOCK TABLES and FLUSH TABLES WITH READ LOCK +# + +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (i int); +connect (flush,localhost,root,,test,,); +connection default; +--echo connection: default +lock tables t1 write; +connection flush; +--echo connection: flush +--send flush tables with read lock; +connection default; +--echo connection: default +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Flushing tables"; +--source include/wait_condition.inc +alter table t1 add column j int; +connect (insert,localhost,root,,test,,); +connection insert; +--echo connection: insert +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Flushing tables"; +--source include/wait_condition.inc +--send insert into t1 values (1,2); +--echo connection: default +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for release of readlock"; +--source include/wait_condition.inc +unlock tables; +connection flush; +--echo connection: flush +--reap +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Waiting for release of readlock"; +--source include/wait_condition.inc +select * from t1; +unlock tables; +connection insert; +--reap +connection default; +select * from t1; +drop table t1; +disconnect flush; +disconnect insert; + +# +# Test that FLUSH TABLES under LOCK TABLES protects write locked tables +# from a impending FLUSH TABLES WITH READ LOCK +# + +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (i int); +connect (flush,localhost,root,,test,,); +connection default; +--echo connection: default +lock tables t1 write; +connection flush; +--echo connection: flush +--send flush tables with read lock; +connection default; +--echo connection: default +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Flushing tables"; +--source include/wait_condition.inc +flush tables; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Flushing tables"; +--source include/wait_condition.inc +unlock tables; +let $wait_condition= + select count(*) = 0 from information_schema.processlist + where state = "Flushing tables"; +--source include/wait_condition.inc +connection flush; +--reap +connection default; +disconnect flush; +drop table t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/trigger_notembedded.test b/mysql-test/t/trigger_notembedded.test index 748ae6e1c27..5d2ab84adaf 100644 --- a/mysql-test/t/trigger_notembedded.test +++ b/mysql-test/t/trigger_notembedded.test @@ -880,8 +880,9 @@ USE test; # Bug#23713 LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock # -# Test temporarily disable due to Bug#32395 ---disable_parsing +--disable_warnings +drop table if exists t1; +--enable_warnings create table t1 (i int); connect (flush,localhost,root,,test,,); connection default; @@ -906,6 +907,5 @@ connection default; select * from t1; drop table t1; disconnect flush; ---enable_parsing --echo End of 5.1 tests. -- cgit v1.2.1 From ec206b4caea4b031056822f43cfe0ecf0fab86ea Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 13 Dec 2007 04:33:54 +0300 Subject: Silence a new warning printed by rpl_row_tabledefs_3innodb test execution. --- mysql-test/lib/mtr_report.pl | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test') diff --git a/mysql-test/lib/mtr_report.pl b/mysql-test/lib/mtr_report.pl index a0a796dddf2..b1f15e5e187 100644 --- a/mysql-test/lib/mtr_report.pl +++ b/mysql-test/lib/mtr_report.pl @@ -304,6 +304,7 @@ sub mtr_report_stats ($) { /Slave: Error .*Unknown table/ or /Slave: Error in Write_rows event: / or /Slave: Field .* of table .* has no default value/ or + /Slave: Field .* doesn't have a default value/ or /Slave: Query caused different errors on master and slave/ or /Slave: Table .* doesn't exist/ or /Slave: Table width mismatch/ or -- cgit v1.2.1 From 0a578711a2e44dcb6bfe6c1491b3b8e799ae13b8 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 14 Dec 2007 17:46:24 -0700 Subject: WL#4165 (Prepared statements: validation) Adding the tests cases alone for WL#4165, disabled mysql-test/t/disabled.def: WL#4165 (Prepared statements: validation) mysql-test/r/ps_ddl.result: WL#4165 (Prepared statements: validation) mysql-test/t/ps_ddl.test: WL#4165 (Prepared statements: validation) --- mysql-test/r/ps_ddl.result | 2329 ++++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/disabled.def | 2 +- mysql-test/t/ps_ddl.test | 1851 +++++++++++++++++++++++++++++++++++ 3 files changed, 4181 insertions(+), 1 deletion(-) create mode 100644 mysql-test/r/ps_ddl.result create mode 100644 mysql-test/t/ps_ddl.test (limited to 'mysql-test') diff --git a/mysql-test/r/ps_ddl.result b/mysql-test/r/ps_ddl.result new file mode 100644 index 00000000000..0987e765265 --- /dev/null +++ b/mysql-test/r/ps_ddl.result @@ -0,0 +1,2329 @@ +===================================================================== +Testing 1: NOTHING -> TABLE transitions +===================================================================== +drop table if exists t1; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +prepare stmt from 'select * from t1'; +ERROR 42S02: Table 'test.t1' doesn't exist +===================================================================== +Testing 2: NOTHING -> TEMPORARY TABLE transitions +===================================================================== +===================================================================== +Testing 3: NOTHING -> VIEW transitions +===================================================================== +===================================================================== +Testing 4: TABLE -> NOTHING transitions +===================================================================== +drop table if exists t4; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t4(a int); +prepare stmt from 'select * from t4'; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop table t4; +execute stmt; +ERROR 42S02: Table 'test.t4' doesn't exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +ERROR 42S02: Table 'test.t4' doesn't exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +===================================================================== +Testing 5: TABLE -> TABLE (DDL) transitions +===================================================================== +drop table if exists t5; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t5(a int); +prepare stmt from 'select * from t5'; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +alter table t5 add column (b int); +execute stmt; +a b +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +execute stmt; +a b +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +drop table t5; +===================================================================== +Testing 6: TABLE -> TABLE (TRIGGER) transitions +===================================================================== +drop table if exists t6; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t6(a int); +prepare stmt from 'insert into t6(a) value (?)'; +set @val=1; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +set @val=2; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +create trigger t6_bi before insert on t6 for each row +begin +set @message= "t6_bi"; +end +$$ +set @message="none"; +set @val=3; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +select @message; +@message +t6_bi +set @val=4; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +select @message; +@message +t6_bi +prepare stmt from 'insert into t6(a) value (?)'; +set @message="none"; +set @val=5; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +select @message; +@message +t6_bi +set @message="none"; +set @val=6; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +select @message; +@message +t6_bi +create trigger t6_bd before delete on t6 for each row +begin +set @message= "t6_bd"; +end +$$ +set @message="none"; +set @val=7; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +select @message; +@message +t6_bi +set @message="none"; +set @val=8; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +select @message; +@message +t6_bi +prepare stmt from 'insert into t6(a) value (?)'; +set @message="none"; +set @val=9; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +select @message; +@message +t6_bi +set @message="none"; +set @val=10; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +select @message; +@message +t6_bi +drop trigger t6_bi; +create trigger t6_bi before insert on t6 for each row +begin +set @message= "t6_bi (2)"; +end +$$ +set @message="none"; +set @val=11; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +select @message; +@message +t6_bi (2) +set @val=12; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +select @message; +@message +t6_bi (2) +prepare stmt from 'insert into t6(a) value (?)'; +set @message="none"; +set @val=13; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +select @message; +@message +t6_bi (2) +set @message="none"; +set @val=14; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +select @message; +@message +t6_bi (2) +drop trigger t6_bd; +create trigger t6_bd before delete on t6 for each row +begin +set @message= "t6_bd (2)"; +end +$$ +set @message="none"; +set @val=15; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +select @message; +@message +t6_bi (2) +set @message="none"; +set @val=16; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +select @message; +@message +t6_bi (2) +prepare stmt from 'insert into t6(a) value (?)'; +set @message="none"; +set @val=17; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +select @message; +@message +t6_bi (2) +set @message="none"; +set @val=18; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +select @message; +@message +t6_bi (2) +drop trigger t6_bi; +set @message="none"; +set @val=19; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +select @message; +@message +none +set @val=20; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +select @message; +@message +none +prepare stmt from 'insert into t6(a) value (?)'; +set @message="none"; +set @val=21; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +select @message; +@message +none +set @val=22; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +select @message; +@message +none +drop trigger t6_bd; +set @val=23; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +select @message; +@message +none +set @val=24; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +select @message; +@message +none +select * from t6 order by a; +a +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +15 +16 +17 +18 +19 +20 +21 +22 +23 +24 +drop table t6; +===================================================================== +Testing 7: TABLE -> TABLE (TRIGGER dependencies) transitions +===================================================================== +drop table if exists t7_proc; +drop table if exists t7_func; +drop table if exists t7_view; +drop table if exists t7_table; +drop table if exists t7_dependent_table; +drop table if exists t7_table_trigger; +drop table if exists t7_audit; +drop procedure if exists audit_proc; +drop function if exists audit_func; +drop view if exists audit_view; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t7_proc(a int); +create table t7_func(a int); +create table t7_view(a int); +create table t7_table(a int); +create table t7_table_trigger(a int); +create table t7_audit(old_a int, new_a int, reason varchar(50)); +create table t7_dependent_table(old_a int, new_a int, reason varchar(50)); +create procedure audit_proc(a int) +insert into t7_audit values (NULL, a, "proc v1"); +create function audit_func() returns varchar(50) +return "func v1"; +create view audit_view as select "view v1" as reason from dual; +create trigger t7_proc_bi before insert on t7_proc for each row +call audit_proc(NEW.a); +create trigger t7_func_bi before insert on t7_func for each row +insert into t7_audit values (NULL, NEW.a, audit_func()); +create trigger t7_view_bi before insert on t7_view for each row +insert into t7_audit values (NULL, NEW.a, (select reason from audit_view)); +create trigger t7_table_bi before insert on t7_table for each row +insert into t7_dependent_table values (NULL, NEW.a, "dependent table"); +create trigger t7_table_trigger_bi before insert on t7_dependent_table +for each row set NEW.reason="trigger v1"; +prepare stmt_proc from 'insert into t7_proc(a) value (?)'; +set @val=101; +execute stmt_proc using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +set @val=102; +execute stmt_proc using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop procedure audit_proc; +create procedure audit_proc(a int) +insert into t7_audit values (NULL, a, "proc v2"); +set @val=103; +execute stmt_proc using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +set @val=104; +execute stmt_proc using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +prepare stmt_func from 'insert into t7_func(a) value (?)'; +set @val=201; +execute stmt_func using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +set @val=202; +execute stmt_func using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop function audit_func; +create function audit_func() returns varchar(50) +return "func v2"; +set @val=203; +execute stmt_func using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +set @val=204; +execute stmt_func using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +prepare stmt_view from 'insert into t7_view(a) value (?)'; +set @val=301; +execute stmt_view using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +set @val=302; +execute stmt_view using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop view audit_view; +create view audit_view as select "view v2" as reason from dual; +set @val=303; +execute stmt_view using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +set @val=304; +execute stmt_view using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +prepare stmt_table from 'insert into t7_table(a) value (?)'; +set @val=401; +execute stmt_table using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +set @val=402; +execute stmt_table using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +alter table t7_dependent_table add column comments varchar(100) default NULL; +set @val=403; +execute stmt_table using @val; +ERROR 21S01: Column count doesn't match value count at row 1 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +set @val=404; +execute stmt_table using @val; +ERROR 21S01: Column count doesn't match value count at row 1 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +alter table t7_dependent_table drop column comments; +set @val=405; +execute stmt_table using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +3 +set @val=406; +execute stmt_table using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +3 +prepare stmt_table_trigger from 'insert into t7_table(a) value (?)'; +set @val=501; +execute stmt_table_trigger using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +3 +set @val=502; +execute stmt_table_trigger using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +3 +drop trigger t7_table_trigger_bi; +create trigger t7_table_trigger_bi before insert on t7_dependent_table +for each row set NEW.reason="trigger v2"; +set @val=503; +execute stmt_table_trigger using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +4 +set @val=504; +execute stmt_table_trigger using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +4 +select * from t7_audit order by new_a; +old_a new_a reason +NULL 101 proc v1 +NULL 102 proc v1 +NULL 103 proc v2 +NULL 104 proc v2 +NULL 201 func v1 +NULL 202 func v1 +NULL 203 func v2 +NULL 204 func v2 +NULL 301 view v1 +NULL 302 view v1 +NULL 303 view v1 +NULL 304 view v1 +select * from t7_dependent_table order by new_a; +old_a new_a reason +NULL 401 trigger v1 +NULL 402 trigger v1 +NULL 405 trigger v1 +NULL 406 trigger v1 +NULL 501 trigger v1 +NULL 502 trigger v1 +NULL 503 trigger v2 +NULL 504 trigger v2 +drop table t7_proc; +drop table t7_func; +drop table t7_view; +drop table t7_table; +drop table t7_dependent_table; +drop table t7_table_trigger; +drop table t7_audit; +drop procedure audit_proc; +drop function audit_func; +drop view audit_view; +===================================================================== +Testing 8: TABLE -> TEMPORARY TABLE transitions +===================================================================== +drop table if exists t8; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t8(a int); +prepare stmt from 'select * from t8'; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop table t8; +create temporary table t8(a int); +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +drop table t8; +===================================================================== +Testing 9: TABLE -> VIEW transitions +===================================================================== +drop table if exists t9; +drop table if exists t9_b; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t9(a int); +create table t9_b(a int); +prepare stmt from 'select * from t9'; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop table t9; +create view t9 as select * from t9_b; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +drop view t9; +drop table t9_b; +===================================================================== +Testing 10: TEMPORARY TABLE -> NOTHING transitions +===================================================================== +drop temporary table if exists t10; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create temporary table t10(a int); +prepare stmt from 'select * from t10'; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop temporary table t10; +execute stmt; +ERROR 42S02: Table 'test.t10' doesn't exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +ERROR 42S02: Table 'test.t10' doesn't exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +===================================================================== +Testing 11: TEMPORARY TABLE -> TABLE transitions +===================================================================== +drop table if exists t11; +drop temporary table if exists t11; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t11(a int); +insert into t11(a) value (1); +create temporary table t11(a int); +prepare stmt from 'select * from t11'; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop temporary table t11; +execute stmt; +a +1 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +execute stmt; +a +1 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +select * from t11; +a +1 +drop table t11; +===================================================================== +Testing 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL) transitions +===================================================================== +drop temporary table if exists t12; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create temporary table t12(a int); +prepare stmt from 'select * from t12'; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop temporary table t12; +create temporary table t12(a int, b int); +execute stmt; +a b +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +execute stmt; +a b +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +select * from t12; +a b +drop table t12; +===================================================================== +Testing 13: TEMPORARY TABLE -> VIEW transitions +===================================================================== +drop temporary table if exists t13; +drop table if exists t13_b; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create temporary table t13(a int); +create table t13_b(a int); +prepare stmt from 'select * from t13'; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop temporary table t13; +create view t13 as select * from t13_b; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +drop view t13; +drop table t13_b; +===================================================================== +Testing 14: VIEW -> NOTHING transitions +===================================================================== +drop view if exists t14; +drop table if exists t14_b; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t14_b(a int); +create view t14 as select * from t14_b; +prepare stmt from 'select * from t14'; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop view t14; +execute stmt; +ERROR 42S02: Table 'test.t14' doesn't exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +execute stmt; +ERROR 42S02: Table 'test.t14' doesn't exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +drop table t14_b; +===================================================================== +Testing 15: VIEW -> TABLE transitions +===================================================================== +drop view if exists t15; +drop table if exists t15_b; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t15_b(a int); +create view t15 as select * from t15_b; +prepare stmt from 'select * from t15'; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop view t15; +create table t15(a int); +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +drop table t15_b; +drop table t15; +===================================================================== +Testing 16: VIEW -> TEMPORARY TABLE transitions +===================================================================== +drop view if exists t16; +drop table if exists t16_b; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t16_b(a int); +create view t16 as select * from t16_b; +prepare stmt from 'select * from t16'; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop view t16; +create temporary table t16(a int); +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +drop table t16_b; +drop temporary table t16; +===================================================================== +Testing 17: VIEW -> VIEW (DDL) transitions +===================================================================== +drop view if exists t17; +drop table if exists t17_b; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t17_b(a int); +insert into t17_b values (10), (20), (30); +create view t17 as select a, 2*a as b, 3*a as c from t17_b; +select * from t17; +a b c +10 20 30 +20 40 60 +30 60 90 +prepare stmt from 'select * from t17'; +execute stmt; +a b c +10 20 30 +20 40 60 +30 60 90 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a b c +10 20 30 +20 40 60 +30 60 90 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop view t17; +create view t17 as select a, 2*a as b, 10*a as c from t17_b; +select * from t17; +a b c +10 20 100 +20 40 200 +30 60 300 +execute stmt; +a b c +10 20 100 +20 40 200 +30 60 300 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +execute stmt; +a b c +10 20 100 +20 40 200 +30 60 300 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +drop table t17_b; +drop view t17; +===================================================================== +Testing 18: VIEW -> VIEW (VIEW dependencies) transitions +===================================================================== +drop table if exists t18; +drop table if exists t18_dependent_table; +drop view if exists t18_func; +drop view if exists t18_view; +drop view if exists t18_table; +drop function if exists view_func; +drop view if exists view_view; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t18(a int); +insert into t18 values (1), (2), (3); +create function view_func(x int) returns int +return x+1; +create view view_view as select "view v1" as reason from dual; +create table t18_dependent_table(a int); +create view t18_func as select a, view_func(a) as b from t18; +create view t18_view as select a, reason as b from t18, view_view; +create view t18_table as select * from t18; +prepare stmt_func from 'select * from t18_func'; +execute stmt_func; +a b +1 2 +2 3 +3 4 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt_func; +a b +1 2 +2 3 +3 4 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop function view_func; +create function view_func(x int) returns int +return x*x; +execute stmt_func; +a b +1 1 +2 4 +3 9 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt_func; +a b +1 1 +2 4 +3 9 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +prepare stmt_view from 'select * from t18_view'; +execute stmt_view; +a b +1 view v1 +2 view v1 +3 view v1 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt_view; +a b +1 view v1 +2 view v1 +3 view v1 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop view view_view; +create view view_view as select "view v2" as reason from dual; +execute stmt_view; +a b +1 view v2 +2 view v2 +3 view v2 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +execute stmt_view; +a b +1 view v2 +2 view v2 +3 view v2 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +prepare stmt_table from 'select * from t18_table'; +execute stmt_table; +a +1 +2 +3 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +execute stmt_table; +a +1 +2 +3 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +alter table t18 add column comments varchar(50) default NULL; +execute stmt_table; +a +1 +2 +3 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +execute stmt_table; +a +1 +2 +3 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +drop table t18; +drop table t18_dependent_table; +drop view t18_func; +drop view t18_view; +drop view t18_table; +drop function view_func; +drop view view_view; +===================================================================== +Testing 19: Special tables (INFORMATION_SCHEMA) +===================================================================== +drop procedure if exists proc_19; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +prepare stmt from +'select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE + from INFORMATION_SCHEMA.ROUTINES where + routine_name=\'proc_19\''; +create procedure proc_19() select "hi there"; +execute stmt; +ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE +test proc_19 PROCEDURE +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE +test proc_19 PROCEDURE +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop procedure proc_19; +create procedure proc_19() select "hi there, again"; +execute stmt; +ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE +test proc_19 PROCEDURE +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE +test proc_19 PROCEDURE +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop procedure proc_19; +===================================================================== +Testing 20: Special tables (log tables) +===================================================================== +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +prepare stmt from +'select * from mysql.general_log where argument=\'IMPOSSIBLE QUERY STRING\''; +execute stmt; +event_time user_host thread_id server_id command_type argument +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +event_time user_host thread_id server_id command_type argument +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +event_time user_host thread_id server_id command_type argument +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +event_time user_host thread_id server_id command_type argument +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +===================================================================== +Testing 21: Special tables (system tables) +===================================================================== +drop procedure if exists proc_21; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +prepare stmt from +'select type, db, name from mysql.proc where name=\'proc_21\''; +create procedure proc_21() select "hi there"; +execute stmt; +type db name +PROCEDURE test proc_21 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +type db name +PROCEDURE test proc_21 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop procedure proc_21; +create procedure proc_21() select "hi there, again"; +execute stmt; +type db name +PROCEDURE test proc_21 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +type db name +PROCEDURE test proc_21 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop procedure proc_21; +===================================================================== +Testing 22: Special tables (views temp tables) +===================================================================== +drop table if exists t22_b; +drop view if exists t22; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t22_b(a int); +create algorithm=temptable view t22 as select a*a as a2 from t22_b; +show create view t22; +View Create View character_set_client collation_connection +t22 CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t22` AS select (`t22_b`.`a` * `t22_b`.`a`) AS `a2` from `t22_b` latin1 latin1_swedish_ci +prepare stmt from 'select * from t22'; +insert into t22_b values (1), (2), (3); +execute stmt; +a2 +1 +4 +9 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a2 +1 +4 +9 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +insert into t22_b values (4), (5), (6); +execute stmt; +a2 +1 +4 +9 +16 +25 +36 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a2 +1 +4 +9 +16 +25 +36 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop table t22_b; +drop view t22; +===================================================================== +Testing 23: Special tables (internal join tables) +===================================================================== +drop table if exists t23_a; +drop table if exists t23_b; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t23_a(a int); +create table t23_b(b int); +prepare stmt from 'select * from t23_a join t23_b'; +insert into t23_a values (1), (2), (3); +insert into t23_b values (10), (20), (30); +execute stmt; +a b +1 10 +2 10 +3 10 +1 20 +2 20 +3 20 +1 30 +2 30 +3 30 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a b +1 10 +2 10 +3 10 +1 20 +2 20 +3 20 +1 30 +2 30 +3 30 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +insert into t23_a values (4); +insert into t23_b values (40); +execute stmt; +a b +1 10 +2 10 +3 10 +4 10 +1 20 +2 20 +3 20 +4 20 +1 30 +2 30 +3 30 +4 30 +1 40 +2 40 +3 40 +4 40 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a b +1 10 +2 10 +3 10 +4 10 +1 20 +2 20 +3 20 +4 20 +1 30 +2 30 +3 30 +4 30 +1 40 +2 40 +3 40 +4 40 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop table t23_a; +drop table t23_b; +===================================================================== +Testing 24: Special statements +===================================================================== +drop table if exists t24_alter; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t24_alter(a int); +prepare stmt from 'alter table t24_alter add column b int'; +execute stmt; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop table t24_alter; +create table t24_alter(a1 int, a2 int); +execute stmt; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +alter table t24_alter drop column b; +execute stmt; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +alter table t24_alter drop column b; +execute stmt; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop table t24_alter; +drop table if exists t24_repair; +create table t24_repair(a int); +insert into t24_repair values (1), (2), (3); +prepare stmt from 'repair table t24_repair'; +execute stmt; +Table Op Msg_type Msg_text +test.t24_repair repair status OK +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop table t24_repair; +create table t24_repair(a1 int, a2 int); +insert into t24_repair values (1, 10), (2, 20), (3, 30); +execute stmt; +Table Op Msg_type Msg_text +test.t24_repair repair status OK +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +alter table t24_repair add column b varchar(50) default NULL; +execute stmt; +Table Op Msg_type Msg_text +test.t24_repair repair status OK +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +alter table t24_repair drop column b; +execute stmt; +Table Op Msg_type Msg_text +test.t24_repair repair status OK +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop table t24_repair; +drop table if exists t24_analyze; +create table t24_analyze(a int); +insert into t24_analyze values (1), (2), (3); +prepare stmt from 'analyze table t24_analyze'; +execute stmt; +Table Op Msg_type Msg_text +test.t24_analyze analyze status OK +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop table t24_analyze; +create table t24_analyze(a1 int, a2 int); +insert into t24_analyze values (1, 10), (2, 20), (3, 30); +execute stmt; +Table Op Msg_type Msg_text +test.t24_analyze analyze status OK +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +alter table t24_analyze add column b varchar(50) default NULL; +execute stmt; +Table Op Msg_type Msg_text +test.t24_analyze analyze status OK +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +alter table t24_analyze drop column b; +execute stmt; +Table Op Msg_type Msg_text +test.t24_analyze analyze status OK +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop table t24_analyze; +drop table if exists t24_optimize; +create table t24_optimize(a int); +insert into t24_optimize values (1), (2), (3); +prepare stmt from 'optimize table t24_optimize'; +execute stmt; +Table Op Msg_type Msg_text +test.t24_optimize optimize status OK +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop table t24_optimize; +create table t24_optimize(a1 int, a2 int); +insert into t24_optimize values (1, 10), (2, 20), (3, 30); +execute stmt; +Table Op Msg_type Msg_text +test.t24_optimize optimize status OK +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +alter table t24_optimize add column b varchar(50) default NULL; +execute stmt; +Table Op Msg_type Msg_text +test.t24_optimize optimize status OK +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +alter table t24_optimize drop column b; +execute stmt; +Table Op Msg_type Msg_text +test.t24_optimize optimize status OK +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop table t24_optimize; +drop procedure if exists changing_proc; +prepare stmt from 'show create procedure changing_proc'; +execute stmt; +ERROR 42000: PROCEDURE changing_proc does not exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +ERROR 42000: PROCEDURE changing_proc does not exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +create procedure changing_proc() begin end; +execute stmt; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +changing_proc CREATE DEFINER=`root`@`localhost` PROCEDURE `changing_proc`() +begin end latin1 latin1_swedish_ci latin1_swedish_ci +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +changing_proc CREATE DEFINER=`root`@`localhost` PROCEDURE `changing_proc`() +begin end latin1 latin1_swedish_ci latin1_swedish_ci +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop procedure changing_proc; +create procedure changing_proc(x int, y int) begin end; +execute stmt; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +changing_proc CREATE DEFINER=`root`@`localhost` PROCEDURE `changing_proc`(x int, y int) +begin end latin1 latin1_swedish_ci latin1_swedish_ci +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +changing_proc CREATE DEFINER=`root`@`localhost` PROCEDURE `changing_proc`(x int, y int) +begin end latin1 latin1_swedish_ci latin1_swedish_ci +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop procedure changing_proc; +execute stmt; +ERROR 42000: PROCEDURE changing_proc does not exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +ERROR 42000: PROCEDURE changing_proc does not exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop function if exists changing_func; +prepare stmt from 'show create function changing_func'; +execute stmt; +ERROR 42000: FUNCTION changing_func does not exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +ERROR 42000: FUNCTION changing_func does not exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +create function changing_func() returns int return 0; +execute stmt; +Function sql_mode Create Function character_set_client collation_connection Database Collation +changing_func CREATE DEFINER=`root`@`localhost` FUNCTION `changing_func`() RETURNS int(11) +return 0 latin1 latin1_swedish_ci latin1_swedish_ci +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +Function sql_mode Create Function character_set_client collation_connection Database Collation +changing_func CREATE DEFINER=`root`@`localhost` FUNCTION `changing_func`() RETURNS int(11) +return 0 latin1 latin1_swedish_ci latin1_swedish_ci +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop function changing_func; +create function changing_func(x int, y int) returns int return x+y; +execute stmt; +Function sql_mode Create Function character_set_client collation_connection Database Collation +changing_func CREATE DEFINER=`root`@`localhost` FUNCTION `changing_func`(x int, y int) RETURNS int(11) +return x+y latin1 latin1_swedish_ci latin1_swedish_ci +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +Function sql_mode Create Function character_set_client collation_connection Database Collation +changing_func CREATE DEFINER=`root`@`localhost` FUNCTION `changing_func`(x int, y int) RETURNS int(11) +return x+y latin1 latin1_swedish_ci latin1_swedish_ci +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop function changing_func; +execute stmt; +ERROR 42000: FUNCTION changing_func does not exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +ERROR 42000: FUNCTION changing_func does not exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop table if exists t24_trigger; +create table t24_trigger(a int); +prepare stmt from 'show create trigger t24_bi;'; +execute stmt; +ERROR HY000: Trigger does not exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +ERROR HY000: Trigger does not exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +create trigger t24_bi before insert on t24_trigger for each row +begin +set @message= "t24_bi"; +end +$$ +execute stmt; +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +t24_bi CREATE DEFINER=`root`@`localhost` trigger t24_bi before insert on t24_trigger for each row +begin +set @message= "t24_bi"; +end latin1 latin1_swedish_ci latin1_swedish_ci +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +t24_bi CREATE DEFINER=`root`@`localhost` trigger t24_bi before insert on t24_trigger for each row +begin +set @message= "t24_bi"; +end latin1 latin1_swedish_ci latin1_swedish_ci +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop trigger t24_bi; +create trigger t24_bi before insert on t24_trigger for each row +begin +set @message= "t24_bi (2)"; +end +$$ +execute stmt; +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +t24_bi CREATE DEFINER=`root`@`localhost` trigger t24_bi before insert on t24_trigger for each row +begin +set @message= "t24_bi (2)"; +end latin1 latin1_swedish_ci latin1_swedish_ci +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +t24_bi CREATE DEFINER=`root`@`localhost` trigger t24_bi before insert on t24_trigger for each row +begin +set @message= "t24_bi (2)"; +end latin1 latin1_swedish_ci latin1_swedish_ci +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop trigger t24_bi; +execute stmt; +ERROR HY000: Trigger does not exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +ERROR HY000: Trigger does not exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop table t24_trigger; +===================================================================== +Testing 25: Testing the strength of TABLE_SHARE version +===================================================================== +drop table if exists t25_num_col; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t25_num_col(a int); +prepare stmt from 'select * from t25_num_col'; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +alter table t25_num_col add column b varchar(50) default NULL; +execute stmt; +a b +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +execute stmt; +a b +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +drop table t25_num_col; +drop table if exists t25_col_name; +create table t25_col_name(a int); +prepare stmt from 'select * from t25_col_name'; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +alter table t25_col_name change a b int; +execute stmt; +b +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +execute stmt; +b +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +drop table t25_col_name; +drop table if exists t25_col_type; +create table t25_col_type(a int); +prepare stmt from 'select * from t25_col_type'; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +alter table t25_col_type change a a varchar(10); +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +3 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +3 +drop table t25_col_type; +drop table if exists t25_col_type_length; +create table t25_col_type_length(a varchar(10)); +prepare stmt from 'select * from t25_col_type_length'; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +3 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +3 +alter table t25_col_type_length change a a varchar(20); +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +4 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +4 +drop table t25_col_type_length; +drop table if exists t25_col_null; +create table t25_col_null(a varchar(10)); +prepare stmt from 'select * from t25_col_null'; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +4 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +4 +alter table t25_col_null change a a varchar(10) NOT NULL; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +5 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +5 +drop table t25_col_null; +drop table if exists t25_col_default; +create table t25_col_default(a int, b int DEFAULT 10); +prepare stmt from 'insert into t25_col_default(a) values (?)'; +set @val=1; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +5 +set @val=2; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +5 +alter table t25_col_default change b b int DEFAULT 20; +set @val=3; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +5 +set @val=4; +execute stmt using @val; +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +5 +select * from t25_col_default; +a b +1 10 +2 10 +3 20 +4 20 +drop table t25_col_default; +drop table if exists t25_index; +create table t25_index(a varchar(10)); +prepare stmt from 'select * from t25_index'; +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +5 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +5 +create index i1 on t25_index(a); +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +6 +execute stmt; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +6 +drop table t25_index; +drop table if exists t25_index_unique; +create table t25_index_unique(a varchar(10), b varchar(10)); +create index i1 on t25_index_unique(a, b); +show create table t25_index_unique; +Table Create Table +t25_index_unique CREATE TABLE `t25_index_unique` ( + `a` varchar(10) DEFAULT NULL, + `b` varchar(10) DEFAULT NULL, + KEY `i1` (`a`,`b`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +prepare stmt from 'select * from t25_index_unique'; +execute stmt; +a b +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +6 +execute stmt; +a b +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +6 +alter table t25_index_unique drop index i1; +create unique index i1 on t25_index_unique(a, b); +show create table t25_index_unique; +Table Create Table +t25_index_unique CREATE TABLE `t25_index_unique` ( + `a` varchar(10) DEFAULT NULL, + `b` varchar(10) DEFAULT NULL, + UNIQUE KEY `i1` (`a`,`b`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +execute stmt; +a b +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +7 +execute stmt; +a b +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +7 +drop table t25_index_unique; +===================================================================== +Testing reported bugs +===================================================================== +drop table if exists table_12093; +drop function if exists func_12093; +drop function if exists func_12093_unrelated; +drop procedure if exists proc_12093; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table table_12093(a int); +create function func_12093() +returns int +begin +return (select count(*) from table_12093); +end// +create procedure proc_12093(a int) +begin +select * from table_12093; +end// +create function func_12093_unrelated() returns int return 2; +create procedure proc_12093_unrelated() begin end; +prepare stmt_sf from 'select func_12093();'; +prepare stmt_sp from 'call proc_12093(func_12093())'; +execute stmt_sf; +func_12093() +0 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt_sp; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop function func_12093_unrelated; +drop procedure proc_12093_unrelated; +execute stmt_sf; +func_12093() +0 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt_sp; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt_sf; +func_12093() +0 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt_sp; +a +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +deallocate prepare stmt_sf; +deallocate prepare stmt_sp; +drop table table_12093; +drop function func_12093; +drop procedure proc_12093; +drop function if exists func_21294; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create function func_21294() returns int return 10; +prepare stmt from "select func_21294()"; +execute stmt; +func_21294() +10 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop function func_21294; +create function func_21294() returns int return 10; +execute stmt; +func_21294() +10 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop function func_21294; +create function func_21294() returns int return 20; +execute stmt; +func_21294() +20 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +deallocate prepare stmt; +drop function func_21294; +drop table if exists t_27420_100; +drop table if exists t_27420_101; +drop view if exists v_27420; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t_27420_100(a int); +insert into t_27420_100 values (1), (2); +create table t_27420_101(a int); +insert into t_27420_101 values (1), (2); +create view v_27420 as select t_27420_100.a X, t_27420_101.a Y +from t_27420_100, t_27420_101 +where t_27420_100.a=t_27420_101.a; +prepare stmt from 'select * from v_27420'; +execute stmt; +X Y +1 1 +2 2 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop view v_27420; +create table v_27420(X int, Y int); +execute stmt; +X Y +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +drop table v_27420; +create table v_27420 (a int, b int, filler char(200)); +execute stmt; +a b filler +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +2 +deallocate prepare stmt; +drop table t_27420_100; +drop table t_27420_101; +drop table v_27420; +drop table if exists t_27430_1; +drop table if exists t_27430_2; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t_27430_1 (a int not null, oref int not null, key(a)); +insert into t_27430_1 values +(1, 1), +(1, 1234), +(2, 3), +(2, 1234), +(3, 1234); +create table t_27430_2 (a int not null, oref int not null); +insert into t_27430_2 values +(1, 1), +(2, 2), +(1234, 3), +(1234, 4); +prepare stmt from +'select oref, a, a in (select a from t_27430_1 where oref=t_27430_2.oref) Z from t_27430_2'; +execute stmt; +oref a Z +1 1 1 +2 2 0 +3 1234 0 +4 1234 0 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +oref a Z +1 1 1 +2 2 0 +3 1234 0 +4 1234 0 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop table t_27430_1, t_27430_2; +create table t_27430_1 (a int, oref int, key(a)); +insert into t_27430_1 values +(1, 1), +(1, NULL), +(2, 3), +(2, NULL), +(3, NULL); +create table t_27430_2 (a int, oref int); +insert into t_27430_2 values +(1, 1), +(2,2), +(NULL, 3), +(NULL, 4); +execute stmt; +oref a Z +1 1 1 +2 2 0 +3 NULL NULL +4 NULL 0 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +execute stmt; +oref a Z +1 1 1 +2 2 0 +3 NULL NULL +4 NULL 0 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +deallocate prepare stmt; +drop table t_27430_1; +drop table t_27430_2; +drop table if exists t_27690_1; +drop view if exists v_27690_1; +drop table if exists v_27690_2; +SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; +create table t_27690_1 (a int, b int); +insert into t_27690_1 values (1,1),(2,2); +create table v_27690_1 as select * from t_27690_1; +create table v_27690_2 as select * from t_27690_1; +prepare stmt from 'select * from v_27690_1, v_27690_2'; +execute stmt; +a b a b +1 1 1 1 +2 2 1 1 +1 1 2 2 +2 2 2 2 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +a b a b +1 1 1 1 +2 2 1 1 +1 1 2 2 +2 2 2 2 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +drop table v_27690_1; +execute stmt; +ERROR 42S02: Table 'test.v_27690_1' doesn't exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +execute stmt; +ERROR 42S02: Table 'test.v_27690_1' doesn't exist +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +0 +create view v_27690_1 as select A.a, A.b from t_27690_1 A, t_27690_1 B; +execute stmt; +a b a b +1 1 1 1 +2 2 1 1 +1 1 1 1 +2 2 1 1 +1 1 2 2 +2 2 2 2 +1 1 2 2 +2 2 2 2 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +execute stmt; +a b a b +1 1 1 1 +2 2 1 1 +1 1 1 1 +2 2 1 1 +1 1 2 2 +2 2 2 2 +1 1 2 2 +2 2 2 2 +SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; +REPREPARED +1 +deallocate prepare stmt; +drop table t_27690_1; +drop view v_27690_1; +drop table v_27690_2; diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 2c16017241c..3f9ec52ca36 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -20,6 +20,6 @@ lowercase_table3 : Bug#32667 lowercase_table3.test reports to error log kill : Bug#29149: Test "kill" fails on Windows innodb_mysql : Bug#32724: innodb_mysql.test fails randomly wait_timeout : Bug#32801 wait_timeout.test fails randomly -kill : Bug#29149 Test "kill" fails on Windows ctype_create : Bug#32965 main.ctype_create fails status : Bug#32966 main.status fails +ps_ddl : Bug#12093 2007-12-14 pending WL#4165 / WL#4166 diff --git a/mysql-test/t/ps_ddl.test b/mysql-test/t/ps_ddl.test new file mode 100644 index 00000000000..abb6563f052 --- /dev/null +++ b/mysql-test/t/ps_ddl.test @@ -0,0 +1,1851 @@ +# +# Testing the behavior of 'PREPARE', 'DDL', 'EXECUTE' scenarios +# +# Background: +# In a statement like "select * from t1", t1 can be: +# - nothing (the table does not exist) +# - a real table +# - a temporary table +# - a view +# +# Changing the nature of "t1" between a PREPARE and an EXECUTE +# can invalidate the internal state of a prepared statement, so that, +# during the execute, the server should: +# - detect state changes and fail to execute a statement, +# instead of crashing the server or returning wrong results +# - "RE-PREPARE" the statement to restore a valid internal state. +# +# Also, changing the physical structure of "t1", by: +# - changing the definition of t1 itself (DDL on tables, views) +# - changing TRIGGERs associated with a table +# - changing PROCEDURE, FUNCTION referenced by a TRIGGER body, +# - changing PROCEDURE, FUNCTION referenced by a VIEW body, +# impacts the internal structure of a prepared statement, and should +# cause the same verifications at execute time to be performed. +# +# This test provided in this file cover the different state transitions +# between a PREPARE and an EXECUTE, and are organized as follows: +# - Part 1: NOTHING -> TABLE +# - Part 2: NOTHING -> TEMPORARY TABLE +# - Part 3: NOTHING -> VIEW +# - Part 4: TABLE -> NOTHING +# - Part 5: TABLE -> TABLE (DDL) +# - Part 6: TABLE -> TABLE (TRIGGER) +# - Part 7: TABLE -> TABLE (TRIGGER dependencies) +# - Part 8: TABLE -> TEMPORARY TABLE +# - Part 9: TABLE -> VIEW +# - Part 10: TEMPORARY TABLE -> NOTHING +# - Part 11: TEMPORARY TABLE -> TABLE +# - Part 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL) +# - Part 13: TEMPORARY TABLE -> VIEW +# - Part 14: VIEW -> NOTHING +# - Part 15: VIEW -> TABLE +# - Part 16: VIEW -> TEMPORARY TABLE +# - Part 17: VIEW -> VIEW (DDL) +# - Part 18: VIEW -> VIEW (VIEW dependencies) +# - Part 19: Special tables (INFORMATION_SCHEMA) +# - Part 20: Special tables (log tables) +# - Part 21: Special tables (system tables) +# - Part 22: Special tables (views temp tables) +# - Part 23: Special tables (internal join tables) +# - Part 24: Special statements +# - Part 25: Testing the strength of TABLE_SHARE version + +let $base_count = SELECT VARIABLE_VALUE from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' +into @base_count ; + +let $reprepared = SELECT VARIABLE_VALUE - @base_count AS REPREPARED from +INFORMATION_SCHEMA.SESSION_STATUS where variable_name='COM_STMT_REPREPARE' ; + +--echo ===================================================================== +--echo Testing 1: NOTHING -> TABLE transitions +--echo ===================================================================== + +--disable_warnings +drop table if exists t1; +--enable_warnings + +eval $base_count; + +# can not be tested since prepare failed +--error ER_NO_SUCH_TABLE +prepare stmt from 'select * from t1'; + +--echo ===================================================================== +--echo Testing 2: NOTHING -> TEMPORARY TABLE transitions +--echo ===================================================================== + +# can not be tested + +--echo ===================================================================== +--echo Testing 3: NOTHING -> VIEW transitions +--echo ===================================================================== + +# can not be tested + +--echo ===================================================================== +--echo Testing 4: TABLE -> NOTHING transitions +--echo ===================================================================== + +--disable_warnings +drop table if exists t4; +--enable_warnings + +eval $base_count; + +create table t4(a int); + +prepare stmt from 'select * from t4'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t4; +--error ER_NO_SUCH_TABLE +execute stmt; +eval $reprepared; +--error ER_NO_SUCH_TABLE +execute stmt; +eval $reprepared; + +--echo ===================================================================== +--echo Testing 5: TABLE -> TABLE (DDL) transitions +--echo ===================================================================== + +--disable_warnings +drop table if exists t5; +--enable_warnings + +eval $base_count; + +create table t5(a int); + +prepare stmt from 'select * from t5'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +alter table t5 add column (b int); + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t5; + +--echo ===================================================================== +--echo Testing 6: TABLE -> TABLE (TRIGGER) transitions +--echo ===================================================================== + +# +# Test 6-a: adding a relevant trigger +# Test 6-b: adding an irrelevant trigger +# Test 6-c: changing a relevant trigger +# Test 6-d: changing an irrelevant trigger +# Test 6-e: removing a relevant trigger +# Test 6-f: removing an irrelevant trigger +# + +--disable_warnings +drop table if exists t6; +--enable_warnings + +eval $base_count; + +create table t6(a int); + +prepare stmt from 'insert into t6(a) value (?)'; +set @val=1; +execute stmt using @val; +eval $reprepared; +set @val=2; +execute stmt using @val; +eval $reprepared; + +# Relevant trigger: execute should reprepare +delimiter $$; +create trigger t6_bi before insert on t6 for each row + begin + set @message= "t6_bi"; + end +$$ +delimiter ;$$ + +set @message="none"; +set @val=3; +# REPREPARED +1 +execute stmt using @val; +eval $reprepared; +select @message; +set @val=4; +execute stmt using @val; +eval $reprepared; +select @message; + +prepare stmt from 'insert into t6(a) value (?)'; +set @message="none"; +set @val=5; +execute stmt using @val; +eval $reprepared; +select @message; +set @message="none"; +set @val=6; +execute stmt using @val; +eval $reprepared; +select @message; + +# Unrelated trigger: execute can pass of fail, implementation dependent +delimiter $$; +create trigger t6_bd before delete on t6 for each row + begin + set @message= "t6_bd"; + end +$$ +delimiter ;$$ + +set @message="none"; +set @val=7; +execute stmt using @val; +eval $reprepared; +select @message; +set @message="none"; +set @val=8; +execute stmt using @val; +eval $reprepared; +select @message; + +prepare stmt from 'insert into t6(a) value (?)'; +set @message="none"; +set @val=9; +execute stmt using @val; +eval $reprepared; +select @message; +set @message="none"; +set @val=10; +execute stmt using @val; +eval $reprepared; +select @message; + +# Relevant trigger: execute should reprepare +drop trigger t6_bi; +delimiter $$; +create trigger t6_bi before insert on t6 for each row + begin + set @message= "t6_bi (2)"; + end +$$ +delimiter ;$$ + +set @message="none"; +set @val=11; +# REPREPARED +1 +execute stmt using @val; +eval $reprepared; +select @message; +set @val=12; +execute stmt using @val; +eval $reprepared; +select @message; + +prepare stmt from 'insert into t6(a) value (?)'; +set @message="none"; +set @val=13; +execute stmt using @val; +eval $reprepared; +select @message; +set @message="none"; +set @val=14; +execute stmt using @val; +eval $reprepared; +select @message; + +# Unrelated trigger: execute can pass of fail, implementation dependent +drop trigger t6_bd; +delimiter $$; +create trigger t6_bd before delete on t6 for each row + begin + set @message= "t6_bd (2)"; + end +$$ +delimiter ;$$ + +set @message="none"; +set @val=15; +execute stmt using @val; +eval $reprepared; +select @message; +set @message="none"; +set @val=16; +execute stmt using @val; +eval $reprepared; +select @message; + +prepare stmt from 'insert into t6(a) value (?)'; +set @message="none"; +set @val=17; +execute stmt using @val; +eval $reprepared; +select @message; +set @message="none"; +set @val=18; +execute stmt using @val; +eval $reprepared; +select @message; + +drop trigger t6_bi; + +set @message="none"; +set @val=19; +# safe to re-execute +execute stmt using @val; +eval $reprepared; +select @message; +set @val=20; +# safe to re-execute +execute stmt using @val; +eval $reprepared; +select @message; + +prepare stmt from 'insert into t6(a) value (?)'; +set @message="none"; +set @val=21; +execute stmt using @val; +eval $reprepared; +select @message; +set @val=22; +execute stmt using @val; +eval $reprepared; +select @message; + +drop trigger t6_bd; + +set @val=23; +# safe to re-execute +execute stmt using @val; +eval $reprepared; +select @message; +set @val=24; +# safe to re-execute +execute stmt using @val; +eval $reprepared; +select @message; + +select * from t6 order by a; +drop table t6; + +--echo ===================================================================== +--echo Testing 7: TABLE -> TABLE (TRIGGER dependencies) transitions +--echo ===================================================================== + +# +# Test 7-a: dependent PROCEDURE has changed +# Test 7-b: dependent FUNCTION has changed +# Test 7-c: dependent VIEW has changed +# Test 7-d: dependent TABLE has changed +# Test 7-e: dependent TABLE TRIGGER has changed +# + +--disable_warnings +drop table if exists t7_proc; +drop table if exists t7_func; +drop table if exists t7_view; +drop table if exists t7_table; +drop table if exists t7_dependent_table; +drop table if exists t7_table_trigger; +drop table if exists t7_audit; +drop procedure if exists audit_proc; +drop function if exists audit_func; +drop view if exists audit_view; +--enable_warnings + +eval $base_count; + +create table t7_proc(a int); +create table t7_func(a int); +create table t7_view(a int); +create table t7_table(a int); +create table t7_table_trigger(a int); + +create table t7_audit(old_a int, new_a int, reason varchar(50)); +create table t7_dependent_table(old_a int, new_a int, reason varchar(50)); + +create procedure audit_proc(a int) + insert into t7_audit values (NULL, a, "proc v1"); + +create function audit_func() returns varchar(50) + return "func v1"; + +create view audit_view as select "view v1" as reason from dual; + +create trigger t7_proc_bi before insert on t7_proc for each row + call audit_proc(NEW.a); + +create trigger t7_func_bi before insert on t7_func for each row + insert into t7_audit values (NULL, NEW.a, audit_func()); + +create trigger t7_view_bi before insert on t7_view for each row + insert into t7_audit values (NULL, NEW.a, (select reason from audit_view)); + +create trigger t7_table_bi before insert on t7_table for each row + insert into t7_dependent_table values (NULL, NEW.a, "dependent table"); + +create trigger t7_table_trigger_bi before insert on t7_dependent_table + for each row set NEW.reason="trigger v1"; + +prepare stmt_proc from 'insert into t7_proc(a) value (?)'; +set @val=101; +execute stmt_proc using @val; +eval $reprepared; +set @val=102; +execute stmt_proc using @val; +eval $reprepared; + +drop procedure audit_proc; + +create procedure audit_proc(a int) + insert into t7_audit values (NULL, a, "proc v2"); + +set @val=103; +execute stmt_proc using @val; +eval $reprepared; +set @val=104; +execute stmt_proc using @val; +eval $reprepared; + + +prepare stmt_func from 'insert into t7_func(a) value (?)'; +set @val=201; +execute stmt_func using @val; +eval $reprepared; +set @val=202; +execute stmt_func using @val; +eval $reprepared; + +drop function audit_func; + +create function audit_func() returns varchar(50) + return "func v2"; + +set @val=203; +execute stmt_func using @val; +eval $reprepared; +set @val=204; +execute stmt_func using @val; +eval $reprepared; + +prepare stmt_view from 'insert into t7_view(a) value (?)'; +set @val=301; +execute stmt_view using @val; +eval $reprepared; +set @val=302; +execute stmt_view using @val; +eval $reprepared; + +drop view audit_view; + +create view audit_view as select "view v2" as reason from dual; + +# Because of Bug#33255, the wrong result is still produced for cases +# 303 and 304, even after re-preparing the statement. +# This is because the table trigger is cached and is not invalidated. + +set @val=303; +# REPREPARED +1 +execute stmt_view using @val; +eval $reprepared; +set @val=304; +execute stmt_view using @val; +eval $reprepared; + + +prepare stmt_table from 'insert into t7_table(a) value (?)'; +set @val=401; +execute stmt_table using @val; +eval $reprepared; +set @val=402; +execute stmt_table using @val; +eval $reprepared; + +alter table t7_dependent_table add column comments varchar(100) default NULL; + +set @val=403; +# REPREPARED +1 +--error ER_WRONG_VALUE_COUNT_ON_ROW +execute stmt_table using @val; +eval $reprepared; +set @val=404; +--error ER_WRONG_VALUE_COUNT_ON_ROW +execute stmt_table using @val; +eval $reprepared; + +alter table t7_dependent_table drop column comments; + +set @val=405; +# REPREPARED +1 +execute stmt_table using @val; +eval $reprepared; +set @val=406; +execute stmt_table using @val; +eval $reprepared; + + +prepare stmt_table_trigger from 'insert into t7_table(a) value (?)'; +set @val=501; +execute stmt_table_trigger using @val; +eval $reprepared; +set @val=502; +execute stmt_table_trigger using @val; +eval $reprepared; + +drop trigger t7_table_trigger_bi; + +create trigger t7_table_trigger_bi before insert on t7_dependent_table + for each row set NEW.reason="trigger v2"; + +set @val=503; +# REPREPARED +1 +execute stmt_table_trigger using @val; +eval $reprepared; +set @val=504; +execute stmt_table_trigger using @val; +eval $reprepared; + +select * from t7_audit order by new_a; + +select * from t7_dependent_table order by new_a; + +drop table t7_proc; +drop table t7_func; +drop table t7_view; +drop table t7_table; +drop table t7_dependent_table; +drop table t7_table_trigger; +drop table t7_audit; +drop procedure audit_proc; +drop function audit_func; +drop view audit_view; + +--echo ===================================================================== +--echo Testing 8: TABLE -> TEMPORARY TABLE transitions +--echo ===================================================================== + +--disable_warnings +drop table if exists t8; +--enable_warnings + +eval $base_count; + +create table t8(a int); + +prepare stmt from 'select * from t8'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t8; +create temporary table t8(a int); + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t8; + +--echo ===================================================================== +--echo Testing 9: TABLE -> VIEW transitions +--echo ===================================================================== + +--disable_warnings +drop table if exists t9; +drop table if exists t9_b; +--enable_warnings + +eval $base_count; + +create table t9(a int); +create table t9_b(a int); + +prepare stmt from 'select * from t9'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t9; +create view t9 as select * from t9_b; + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop view t9; +drop table t9_b; + +--echo ===================================================================== +--echo Testing 10: TEMPORARY TABLE -> NOTHING transitions +--echo ===================================================================== + +--disable_warnings +drop temporary table if exists t10; +--enable_warnings + +eval $base_count; + +create temporary table t10(a int); + +prepare stmt from 'select * from t10'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop temporary table t10; +--error ER_NO_SUCH_TABLE +execute stmt; +eval $reprepared; +--error ER_NO_SUCH_TABLE +execute stmt; +eval $reprepared; + +--echo ===================================================================== +--echo Testing 11: TEMPORARY TABLE -> TABLE transitions +--echo ===================================================================== + +--disable_warnings +drop table if exists t11; +drop temporary table if exists t11; +--enable_warnings + +eval $base_count; + +create table t11(a int); +insert into t11(a) value (1); +create temporary table t11(a int); + +prepare stmt from 'select * from t11'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop temporary table t11; + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +select * from t11; +drop table t11; + +--echo ===================================================================== +--echo Testing 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL) transitions +--echo ===================================================================== + +--disable_warnings +drop temporary table if exists t12; +--enable_warnings + +eval $base_count; + +create temporary table t12(a int); + +prepare stmt from 'select * from t12'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop temporary table t12; +create temporary table t12(a int, b int); + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +select * from t12; +drop table t12; + +--echo ===================================================================== +--echo Testing 13: TEMPORARY TABLE -> VIEW transitions +--echo ===================================================================== + +--disable_warnings +drop temporary table if exists t13; +drop table if exists t13_b; +--enable_warnings + +eval $base_count; + +create temporary table t13(a int); +create table t13_b(a int); + +prepare stmt from 'select * from t13'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop temporary table t13; +create view t13 as select * from t13_b; + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop view t13; +drop table t13_b; + +--echo ===================================================================== +--echo Testing 14: VIEW -> NOTHING transitions +--echo ===================================================================== + +--disable_warnings +drop view if exists t14; +drop table if exists t14_b; +--enable_warnings + +eval $base_count; + +create table t14_b(a int); +create view t14 as select * from t14_b; + +prepare stmt from 'select * from t14'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop view t14; + +# REPREPARED +1 +--error ER_NO_SUCH_TABLE +execute stmt; +eval $reprepared; +--error ER_NO_SUCH_TABLE +execute stmt; +eval $reprepared; + +drop table t14_b; + +--echo ===================================================================== +--echo Testing 15: VIEW -> TABLE transitions +--echo ===================================================================== + +--disable_warnings +drop view if exists t15; +drop table if exists t15_b; +--enable_warnings + +eval $base_count; + +create table t15_b(a int); +create view t15 as select * from t15_b; + +prepare stmt from 'select * from t15'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop view t15; +create table t15(a int); + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t15_b; +drop table t15; + +--echo ===================================================================== +--echo Testing 16: VIEW -> TEMPORARY TABLE transitions +--echo ===================================================================== + +--disable_warnings +drop view if exists t16; +drop table if exists t16_b; +--enable_warnings + +eval $base_count; + +create table t16_b(a int); +create view t16 as select * from t16_b; + +prepare stmt from 'select * from t16'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop view t16; +create temporary table t16(a int); + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t16_b; +drop temporary table t16; + +--echo ===================================================================== +--echo Testing 17: VIEW -> VIEW (DDL) transitions +--echo ===================================================================== + +--disable_warnings +drop view if exists t17; +drop table if exists t17_b; +--enable_warnings + +eval $base_count; + +create table t17_b(a int); +insert into t17_b values (10), (20), (30); + +create view t17 as select a, 2*a as b, 3*a as c from t17_b; +select * from t17; + +prepare stmt from 'select * from t17'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop view t17; +create view t17 as select a, 2*a as b, 10*a as c from t17_b; +select * from t17; + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t17_b; +drop view t17; + +--echo ===================================================================== +--echo Testing 18: VIEW -> VIEW (VIEW dependencies) transitions +--echo ===================================================================== + +# +# Test 18-a: dependent PROCEDURE has changed (via a trigger) +# Test 18-b: dependent FUNCTION has changed +# Test 18-c: dependent VIEW has changed +# Test 18-d: dependent TABLE has changed +# Test 18-e: dependent TABLE TRIGGER has changed +# + +--disable_warnings +drop table if exists t18; +drop table if exists t18_dependent_table; +drop view if exists t18_func; +drop view if exists t18_view; +drop view if exists t18_table; +drop function if exists view_func; +drop view if exists view_view; +--enable_warnings + +eval $base_count; + +# TODO: insertable view -> trigger +# TODO: insertable view -> trigger -> proc ? + +create table t18(a int); +insert into t18 values (1), (2), (3); + +create function view_func(x int) returns int + return x+1; + +create view view_view as select "view v1" as reason from dual; + +create table t18_dependent_table(a int); + +create view t18_func as select a, view_func(a) as b from t18; +create view t18_view as select a, reason as b from t18, view_view; +create view t18_table as select * from t18; + +prepare stmt_func from 'select * from t18_func'; +execute stmt_func; +eval $reprepared; +execute stmt_func; +eval $reprepared; + +drop function view_func; +create function view_func(x int) returns int + return x*x; + +execute stmt_func; +eval $reprepared; +execute stmt_func; +eval $reprepared; + +prepare stmt_view from 'select * from t18_view'; +execute stmt_view; +eval $reprepared; +execute stmt_view; +eval $reprepared; + +drop view view_view; +create view view_view as select "view v2" as reason from dual; + +# REPREPARED +1 +execute stmt_view; +eval $reprepared; +execute stmt_view; +eval $reprepared; + +prepare stmt_table from 'select * from t18_table'; +execute stmt_table; +eval $reprepared; +execute stmt_table; +eval $reprepared; + +alter table t18 add column comments varchar(50) default NULL; + +# REPREPARED +1 +execute stmt_table; +eval $reprepared; +execute stmt_table; +eval $reprepared; + +drop table t18; +drop table t18_dependent_table; +drop view t18_func; +drop view t18_view; +drop view t18_table; +drop function view_func; +drop view view_view; + +--echo ===================================================================== +--echo Testing 19: Special tables (INFORMATION_SCHEMA) +--echo ===================================================================== + +--disable_warnings +drop procedure if exists proc_19; +--enable_warnings + +eval $base_count; + +# Using a temporary table internally should not confuse the prepared +# statement code, and should not raise ER_PS_INVALIDATED errors +prepare stmt from + 'select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE + from INFORMATION_SCHEMA.ROUTINES where + routine_name=\'proc_19\''; + +create procedure proc_19() select "hi there"; + +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop procedure proc_19; +create procedure proc_19() select "hi there, again"; + +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop procedure proc_19; + +--echo ===================================================================== +--echo Testing 20: Special tables (log tables) +--echo ===================================================================== + +eval $base_count; + +prepare stmt from + 'select * from mysql.general_log where argument=\'IMPOSSIBLE QUERY STRING\''; + +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +--echo ===================================================================== +--echo Testing 21: Special tables (system tables) +--echo ===================================================================== + +--disable_warnings +drop procedure if exists proc_21; +--enable_warnings + +eval $base_count; + +prepare stmt from + 'select type, db, name from mysql.proc where name=\'proc_21\''; + +create procedure proc_21() select "hi there"; + +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop procedure proc_21; +create procedure proc_21() select "hi there, again"; + +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop procedure proc_21; + +--echo ===================================================================== +--echo Testing 22: Special tables (views temp tables) +--echo ===================================================================== + +--disable_warnings +drop table if exists t22_b; +drop view if exists t22; +--enable_warnings + +eval $base_count; + +create table t22_b(a int); + +create algorithm=temptable view t22 as select a*a as a2 from t22_b; + +# Using a temporary table internally should not confuse the prepared +# statement code, and should not raise ER_PS_INVALIDATED errors +show create view t22; + +prepare stmt from 'select * from t22'; + +insert into t22_b values (1), (2), (3); +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +insert into t22_b values (4), (5), (6); +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t22_b; +drop view t22; + +--echo ===================================================================== +--echo Testing 23: Special tables (internal join tables) +--echo ===================================================================== + +--disable_warnings +drop table if exists t23_a; +drop table if exists t23_b; +--enable_warnings + +eval $base_count; + +create table t23_a(a int); +create table t23_b(b int); + +# Using a temporary table internally should not confuse the prepared +# statement code, and should not raise ER_PS_INVALIDATED errors +prepare stmt from 'select * from t23_a join t23_b'; + +insert into t23_a values (1), (2), (3); +insert into t23_b values (10), (20), (30); +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +insert into t23_a values (4); +insert into t23_b values (40); +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t23_a; +drop table t23_b; + +--echo ===================================================================== +--echo Testing 24: Special statements +--echo ===================================================================== + +# SQLCOM_ALTER_TABLE: + +--disable_warnings +drop table if exists t24_alter; +--enable_warnings + +eval $base_count; + +create table t24_alter(a int); + +prepare stmt from 'alter table t24_alter add column b int'; +execute stmt; +eval $reprepared; + +drop table t24_alter; +create table t24_alter(a1 int, a2 int); + +# t24_alter has changed, and it's not a problem +execute stmt; +eval $reprepared; + +alter table t24_alter drop column b; +execute stmt; +eval $reprepared; + +alter table t24_alter drop column b; +execute stmt; +eval $reprepared; + +drop table t24_alter; + +# SQLCOM_REPAIR: + +--disable_warnings +drop table if exists t24_repair; +--enable_warnings + +create table t24_repair(a int); +insert into t24_repair values (1), (2), (3); + +prepare stmt from 'repair table t24_repair'; +execute stmt; +eval $reprepared; + +drop table t24_repair; +create table t24_repair(a1 int, a2 int); +insert into t24_repair values (1, 10), (2, 20), (3, 30); + +# t24_repair has changed, and it's not a problem +execute stmt; +eval $reprepared; + +alter table t24_repair add column b varchar(50) default NULL; +execute stmt; +eval $reprepared; + +alter table t24_repair drop column b; +execute stmt; +eval $reprepared; + +drop table t24_repair; + +# SQLCOM_ANALYZE: + +--disable_warnings +drop table if exists t24_analyze; +--enable_warnings + +create table t24_analyze(a int); +insert into t24_analyze values (1), (2), (3); + +prepare stmt from 'analyze table t24_analyze'; +execute stmt; +eval $reprepared; + +drop table t24_analyze; +create table t24_analyze(a1 int, a2 int); +insert into t24_analyze values (1, 10), (2, 20), (3, 30); + +# t24_analyze has changed, and it's not a problem +execute stmt; +eval $reprepared; + +alter table t24_analyze add column b varchar(50) default NULL; +execute stmt; +eval $reprepared; + +alter table t24_analyze drop column b; +execute stmt; +eval $reprepared; + +drop table t24_analyze; + +# SQLCOM_OPTIMIZE: + +--disable_warnings +drop table if exists t24_optimize; +--enable_warnings + +create table t24_optimize(a int); +insert into t24_optimize values (1), (2), (3); + +prepare stmt from 'optimize table t24_optimize'; +execute stmt; +eval $reprepared; + +drop table t24_optimize; +create table t24_optimize(a1 int, a2 int); +insert into t24_optimize values (1, 10), (2, 20), (3, 30); + +# t24_optimize has changed, and it's not a problem +execute stmt; +eval $reprepared; + +alter table t24_optimize add column b varchar(50) default NULL; +execute stmt; +eval $reprepared; + +alter table t24_optimize drop column b; +execute stmt; +eval $reprepared; + +drop table t24_optimize; + +# SQLCOM_SHOW_CREATE_PROC: + +--disable_warnings +drop procedure if exists changing_proc; +--enable_warnings + +prepare stmt from 'show create procedure changing_proc'; +--error ER_SP_DOES_NOT_EXIST +execute stmt; +eval $reprepared; +--error ER_SP_DOES_NOT_EXIST +execute stmt; +eval $reprepared; + +create procedure changing_proc() begin end; + +# changing_proc has changed, and it's not a problem +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop procedure changing_proc; +create procedure changing_proc(x int, y int) begin end; + +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop procedure changing_proc; + +--error ER_SP_DOES_NOT_EXIST +execute stmt; +eval $reprepared; +--error ER_SP_DOES_NOT_EXIST +execute stmt; +eval $reprepared; + +# SQLCOM_SHOW_CREATE_FUNC: + +--disable_warnings +drop function if exists changing_func; +--enable_warnings + +prepare stmt from 'show create function changing_func'; +--error ER_SP_DOES_NOT_EXIST +execute stmt; +eval $reprepared; +--error ER_SP_DOES_NOT_EXIST +execute stmt; +eval $reprepared; + +create function changing_func() returns int return 0; + +# changing_proc has changed, and it's not a problem +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop function changing_func; +create function changing_func(x int, y int) returns int return x+y; + +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop function changing_func; + +--error ER_SP_DOES_NOT_EXIST +execute stmt; +eval $reprepared; +--error ER_SP_DOES_NOT_EXIST +execute stmt; +eval $reprepared; + +# SQLCOM_SHOW_CREATE_TRIGGER: + +--disable_warnings +drop table if exists t24_trigger; +--enable_warnings + +create table t24_trigger(a int); + +prepare stmt from 'show create trigger t24_bi;'; +--error ER_TRG_DOES_NOT_EXIST +execute stmt; +eval $reprepared; +--error ER_TRG_DOES_NOT_EXIST +execute stmt; +eval $reprepared; + +delimiter $$; +create trigger t24_bi before insert on t24_trigger for each row + begin + set @message= "t24_bi"; + end +$$ +delimiter ;$$ + +# t24_bi has changed, and it's not a problem +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop trigger t24_bi; +delimiter $$; +create trigger t24_bi before insert on t24_trigger for each row + begin + set @message= "t24_bi (2)"; + end +$$ +delimiter ;$$ + +# t24_bi has changed, and it's not a problem +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop trigger t24_bi; + +--error ER_TRG_DOES_NOT_EXIST +execute stmt; +eval $reprepared; +--error ER_TRG_DOES_NOT_EXIST +execute stmt; +eval $reprepared; + +drop table t24_trigger; + +--echo ===================================================================== +--echo Testing 25: Testing the strength of TABLE_SHARE version +--echo ===================================================================== + +# Test 25-a: number of columns + +--disable_warnings +drop table if exists t25_num_col; +--enable_warnings + +eval $base_count; + +create table t25_num_col(a int); + +prepare stmt from 'select * from t25_num_col'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +alter table t25_num_col add column b varchar(50) default NULL; + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t25_num_col; + +# Test 25-b: column name + +--disable_warnings +drop table if exists t25_col_name; +--enable_warnings + +create table t25_col_name(a int); + +prepare stmt from 'select * from t25_col_name'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +alter table t25_col_name change a b int; + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t25_col_name; + +# Test 25-c: column type + +--disable_warnings +drop table if exists t25_col_type; +--enable_warnings + +create table t25_col_type(a int); + +prepare stmt from 'select * from t25_col_type'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +alter table t25_col_type change a a varchar(10); + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t25_col_type; + +# Test 25-d: column type length + +--disable_warnings +drop table if exists t25_col_type_length; +--enable_warnings + +create table t25_col_type_length(a varchar(10)); + +prepare stmt from 'select * from t25_col_type_length'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +alter table t25_col_type_length change a a varchar(20); + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t25_col_type_length; + +# Test 25-e: column NULL property + +--disable_warnings +drop table if exists t25_col_null; +--enable_warnings + +create table t25_col_null(a varchar(10)); + +prepare stmt from 'select * from t25_col_null'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +alter table t25_col_null change a a varchar(10) NOT NULL; + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t25_col_null; + +# Test 25-f: column DEFAULT + +--disable_warnings +drop table if exists t25_col_default; +--enable_warnings + +create table t25_col_default(a int, b int DEFAULT 10); + +prepare stmt from 'insert into t25_col_default(a) values (?)'; +set @val=1; +execute stmt using @val; +eval $reprepared; +set @val=2; +execute stmt using @val; +eval $reprepared; + +alter table t25_col_default change b b int DEFAULT 20; + +set @val=3; +# Must insert the correct default value for b +execute stmt using @val; +eval $reprepared; + +set @val=4; +# Must insert the correct default value for b +execute stmt using @val; +eval $reprepared; + +select * from t25_col_default; + +drop table t25_col_default; + +# Test 25-g: number of keys + +--disable_warnings +drop table if exists t25_index; +--enable_warnings + +create table t25_index(a varchar(10)); + +prepare stmt from 'select * from t25_index'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +create index i1 on t25_index(a); + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t25_index; + +# Test 25-h: changing index uniqueness + +--disable_warnings +drop table if exists t25_index_unique; +--enable_warnings + +create table t25_index_unique(a varchar(10), b varchar(10)); +create index i1 on t25_index_unique(a, b); + +show create table t25_index_unique; + +prepare stmt from 'select * from t25_index_unique'; +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +alter table t25_index_unique drop index i1; +create unique index i1 on t25_index_unique(a, b); + +show create table t25_index_unique; + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t25_index_unique; + +--echo ===================================================================== +--echo Testing reported bugs +--echo ===================================================================== + +# +# Bug#12093 (SP not found on second PS execution if another thread drops +# other SP in between) +# + +--disable_warnings +drop table if exists table_12093; +drop function if exists func_12093; +drop function if exists func_12093_unrelated; +drop procedure if exists proc_12093; +--enable_warnings + +eval $base_count; + +connect (con1,localhost,root,,); + +connection default; + +create table table_12093(a int); + +delimiter //; + +create function func_12093() +returns int +begin + return (select count(*) from table_12093); +end// + +create procedure proc_12093(a int) +begin + select * from table_12093; +end// + +delimiter ;// + +create function func_12093_unrelated() returns int return 2; +create procedure proc_12093_unrelated() begin end; + +prepare stmt_sf from 'select func_12093();'; +prepare stmt_sp from 'call proc_12093(func_12093())'; + +execute stmt_sf; +eval $reprepared; +execute stmt_sp; +eval $reprepared; + +connection con1; + +drop function func_12093_unrelated; +drop procedure proc_12093_unrelated; + +connection default; + +# previously, failed with --error 1305 +execute stmt_sf; +eval $reprepared; +# previously, failed with --error 1305 +execute stmt_sp; +eval $reprepared; + +# previously, failed with --error 1305 +execute stmt_sf; +eval $reprepared; +# previously, failed with --error 1305 +execute stmt_sp; +eval $reprepared; + +deallocate prepare stmt_sf; +deallocate prepare stmt_sp; + +disconnect con1; + +drop table table_12093; +drop function func_12093; +drop procedure proc_12093; + +# +# Bug#21294 (executing a prepared statement that executes a stored function +# which was recreat) +# + +--disable_warnings +drop function if exists func_21294; +--enable_warnings + +eval $base_count; + +create function func_21294() returns int return 10; + +prepare stmt from "select func_21294()"; +execute stmt; +eval $reprepared; + +drop function func_21294; +create function func_21294() returns int return 10; + +# might pass or fail, implementation dependent +execute stmt; +eval $reprepared; + +drop function func_21294; +create function func_21294() returns int return 20; + +execute stmt; +eval $reprepared; + +deallocate prepare stmt; +drop function func_21294; + +# +# Bug#27420 (A combination of PS and view operations cause error + assertion +# on shutdown) +# + +--disable_warnings +drop table if exists t_27420_100; +drop table if exists t_27420_101; +drop view if exists v_27420; +--enable_warnings + +eval $base_count; + +connect (con1,localhost,root,,); + +connection default; + +create table t_27420_100(a int); +insert into t_27420_100 values (1), (2); + +create table t_27420_101(a int); +insert into t_27420_101 values (1), (2); + +create view v_27420 as select t_27420_100.a X, t_27420_101.a Y + from t_27420_100, t_27420_101 + where t_27420_100.a=t_27420_101.a; + +prepare stmt from 'select * from v_27420'; + +execute stmt; +eval $reprepared; + +connection con1; + +drop view v_27420; +create table v_27420(X int, Y int); + +connection default; + +# REPREPARED +1 +execute stmt; +eval $reprepared; + +connection con1; + +drop table v_27420; +# passes in 5.0, fails in 5.1, should pass +create table v_27420 (a int, b int, filler char(200)); + +connection default; + +# REPREPARED +1 +execute stmt; +eval $reprepared; + +disconnect con1; + +deallocate prepare stmt; +drop table t_27420_100; +drop table t_27420_101; +drop table v_27420; + +# +# Bug#27430 (Crash in subquery code when in PS and table DDL changed after +# PREPARE) +# + +--disable_warnings +drop table if exists t_27430_1; +drop table if exists t_27430_2; +--enable_warnings + +eval $base_count; + +create table t_27430_1 (a int not null, oref int not null, key(a)); +insert into t_27430_1 values + (1, 1), + (1, 1234), + (2, 3), + (2, 1234), + (3, 1234); + +create table t_27430_2 (a int not null, oref int not null); +insert into t_27430_2 values + (1, 1), + (2, 2), + (1234, 3), + (1234, 4); + +prepare stmt from + 'select oref, a, a in (select a from t_27430_1 where oref=t_27430_2.oref) Z from t_27430_2'; + +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table t_27430_1, t_27430_2; + +create table t_27430_1 (a int, oref int, key(a)); +insert into t_27430_1 values + (1, 1), + (1, NULL), + (2, 3), + (2, NULL), + (3, NULL); + +create table t_27430_2 (a int, oref int); +insert into t_27430_2 values + (1, 1), + (2,2), + (NULL, 3), + (NULL, 4); + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +deallocate prepare stmt; +drop table t_27430_1; +drop table t_27430_2; + +# +# Bug#27690 (Re-execution of prepared statement after table was replaced +# with a view crashes) +# + +--disable_warnings +drop table if exists t_27690_1; +drop view if exists v_27690_1; +drop table if exists v_27690_2; +--enable_warnings + +eval $base_count; + +create table t_27690_1 (a int, b int); +insert into t_27690_1 values (1,1),(2,2); + +create table v_27690_1 as select * from t_27690_1; +create table v_27690_2 as select * from t_27690_1; + +prepare stmt from 'select * from v_27690_1, v_27690_2'; + +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +drop table v_27690_1; + +--error ER_NO_SUCH_TABLE +execute stmt; +eval $reprepared; + +--error ER_NO_SUCH_TABLE +execute stmt; +eval $reprepared; + +create view v_27690_1 as select A.a, A.b from t_27690_1 A, t_27690_1 B; + +# REPREPARED +1 +execute stmt; +eval $reprepared; +execute stmt; +eval $reprepared; + +deallocate prepare stmt; +drop table t_27690_1; +drop view v_27690_1; +drop table v_27690_2; + -- cgit v1.2.1