From 4eac93cd5eb87810bc01b333dbb2c5e70cae8a12 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 8 Aug 2005 17:46:06 +0400 Subject: Fix for bug #10055 "Using stored function with information_schema causes empty result set". To enable full access to contents of I_S tables from stored functions or statements that use them, we manipulate with thread's open tables state and ensure that we won't cause deadlock when we open tables by ignoring flushes and name-locks. Building of contents of I_S.TABLES no longer requires locking of tables since we use use handler::info() method with HA_STATUS_AUTO flag instead of handler::update_auto_increment() for obtaining information about auto-increment values. But this also means that handlers have to implement support for HA_STATUS_AUTO flag (particularly InnoDB needs it). mysql-test/r/alter_table.result: Updated test results. This change was caused by the fact that now when we build contents of I_S tables (and thus output of SHOW INDEX) we don't use instances of tables which may be already opened and locked by thread (we always use new instance). mysql-test/r/information_schema.result: Added test which checks how information about current auto-increment value for table is reported in INFORMATION_SCHEMA.TABLES view. mysql-test/r/sp.result: Added test for bug #10055 "Using stored function with information_schema causes empty result set". mysql-test/t/information_schema.test: Added test which checks how information about current auto-increment value for table is reported in INFORMATION_SCHEMA.TABLES view. mysql-test/t/sp.test: Added test for bug #10055 "Using stored function with information_schema causes empty result set". sql/mysql_priv.h: close_thread_tables(): Get rid of 'stopper' argument which is no longer used. Now when we need to open and then close some table without touching tables which are already opened we use THD::reset_n/restore_backup_open_tables_state() methods. open_tables()/open_normal_and_derived_tables(): Added 'flags' argument to be able open tables even if some has done a flush or hold namelock on them. sql/sp.cc: close_proc_table/open_proc_table_for_read/db_find_routine(): Replaced push_open_tables_state/pop_open_tables_state() methods which were saving/restoring current open tables state in/from THD::open_state_list with reset_n_backup_open_tables_state/restore_backup_open_tables_state() methods which assume that backup storage for this state is allocated on stack (or elsewhere) by their caller. open_proc_table_for_read(): Since now we can have several open tables states stacked up we can't rely rely on checking whether we have some tables open in previous state. Instead we always assume that some tables are open and we need to ignore flush while locking mysql.proc. We don't really need MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK in this case since we open mysql.proc table only for reading. sql/sp.h: Added declarations of open_proc_table_for_read()/close_proc_table() to be able to use them in sql_show.cc. sql/sql_base.cc: close_thread_tables(): Get rid of 'stopper' argument which is no longer used. Now when we need to open and then close some table without touching tables which are already opened we use THD::reset_n/restore_backup_open_tables_state() methods. open_tables()/open_normal_and_derived_tables(): Added 'flags' argument to be able open tables even if some has done a flush or hold namelock on them. sql/sql_class.cc: Open_tables_state, THD: Replaced push_open_tables_state/pop_open_tables_state() methods which were saving/restoring current open tables state in/from THD::open_state_list with reset_n_backup_open_tables_state/restore_backup_open_tables_state() methods which assume that backup storage for this state is allocated on stack (or elsewhere) by their caller. sql/sql_class.h: Open_tables_state, THD: Replaced push_open_tables_state/pop_open_tables_state() methods which were saving/restoring current open tables state in/from THD::open_state_list with reset_n_backup_open_tables_state/restore_backup_open_tables_state() methods which assume that backup storage for this state is allocated on stack (or elsewhere) by their caller. sql/sql_handler.cc: open_tables()/open_normal_and_derived_tables(): Added 'flags' argument to be able open tables even if some has done a flush or hold namelock on them. sql/sql_prepare.cc: open_tables()/open_normal_and_derived_tables(): Added 'flags' argument to be able open tables even if some has done a flush or hold namelock on them. sql/sql_show.cc: get_all_tables(): Now we use THD::reset_n_/restore_backup_open_tables_state() for saving/restoring open tables state instead of working with it directly (This also allows us to have proper content of I_S system tables in statements with stored functions and in stored functions). We also ignore possible flushes when opening tables (we may create deadlock otherwise). Also we do all needed manipulations with LEX in this function and not in get_schema_tables_result() now. get_schema_tables_record(): Let us use handler::info() method with HA_STATUS_AUTO flag for obtaining information about table's auto-increment value. This allows to avoid locking of tables which is needed when we use handler::update_auto_increment() method. fill_schema_proc(): Now we use open_proc_table_for_read/close_proc_table() for access to mysql.proc table (so we won't cause deadlock if we already have some tables open and locked, this also allows us to have proper content in ROUTINES system table in statements using stored functions/in stored functions). get_schema_tables_result(): Moved all manipulations with Open_tables_state and LEX needed for safe opening of tables to ST_SCHEMA_TABLE::fill_table functions (i.e. get_all_tables() and fill_schema_proc()). sql/sql_update.cc: open_tables()/open_normal_and_derived_tables(): Added 'flags' argument to be able open tables even if some has done a flush or hold namelock on them. --- mysql-test/t/information_schema.test | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) (limited to 'mysql-test/t/information_schema.test') diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 7c0624b67fd..6d5bfe79560 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -5,7 +5,7 @@ # show databases --disable_warnings -DROP TABLE IF EXISTS t0,t1,t2; +DROP TABLE IF EXISTS t0,t1,t2,t3,t5; --enable_warnings @@ -30,6 +30,8 @@ create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b)); create table test.t2(a int); create table t3(a int, KEY a_data (a)); create table mysqltest.t4(a int); +create table t5 (id int auto_increment primary key); +insert into t5 values (10); create view v1 (c) as select table_name from information_schema.TABLES; select * from v1; @@ -76,7 +78,7 @@ where table_schema = 'mysqltest' and table_name = 'v1'; connection default; drop view v1, mysqltest.v1; -drop tables mysqltest.t4, mysqltest.t1, t2, t3; +drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5; drop database mysqltest; # Test for information_schema.CHARACTER_SETS & -- cgit v1.2.1 From 4813facf3d1e0db3a38014dc5aefbd26584d38d1 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 15 Aug 2005 17:14:55 +0100 Subject: Bug#12518 "COLUMN_DEFAULT has wrong value if NOT NULL is set" Show NULL instead of empty string when no default value is set mysql-test/r/information_schema.result: test for bug 12518 mysql-test/t/information_schema.test: test for bug 12518 sql/sql_show.cc: report NULL for information schema result when there is no default value --- mysql-test/t/information_schema.test | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'mysql-test/t/information_schema.test') diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 93b200b8a7c..d6d9f34302b 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -639,3 +639,13 @@ select trigger_schema, trigger_name from triggers where trigger_name='tr1'; use test; drop table t1; + +# +# Bug#12518 COLUMN_DEFAULT has wrong value if NOT NULL is set +# +create table t1 (a int not null, b int); +use information_schema; +select column_name, column_default from columns + where table_schema='test' and table_name='t1'; +use test; +drop table t1; -- cgit v1.2.1 From 9a8ea2f975c560ffdf9de754ddb34a9854cffaf8 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 16 Aug 2005 23:11:04 +0400 Subject: information_schema.test: Added a comment line for a test case used for bug #12301. information_schema.result: Fixed some test cases results (bug #12301). sql_show.cc: Fixed bug #12301. The bug was due to a missing value setting for the NUMERIC_SCALE column in the get_schema_column_record() function (sql_show.cc). sql/sql_show.cc: Fixed bug #12301. The bug was due to a missing value setting for the NUMERIC_SCALE column in the get_schema_column_record() function (sql_show.cc). mysql-test/r/information_schema.result: Fixed some test cases results (bug #12301). mysql-test/t/information_schema.test: Added a comment line for a test case used for bug #12301. --- mysql-test/t/information_schema.test | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test/t/information_schema.test') diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index d6d9f34302b..03810447299 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -615,6 +615,7 @@ show create database information_schema; # # Bug #11057 information_schema: columns table has some questionable contents +# Bug #12301 information_schema: NUMERIC_SCALE must be 0 for integer columns # create table t1(f1 LONGBLOB, f2 LONGTEXT); select column_name,data_type,CHARACTER_OCTET_LENGTH, -- cgit v1.2.1 From 8cc25933e928acc3595040207b3e4bf76878c3b5 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 20 Aug 2005 11:00:00 +0300 Subject: Fixes during review of new pushed code Fixed new bug when running a SP without a default database mysql-test/r/information_schema.result: Added test to cover changes made in default handling mysql-test/r/sp-security.result: Added test when executing SP without a default database mysql-test/t/information_schema.test: Added test to cover changes made in default handling mysql-test/t/sp-security.test: Added test when executing SP without a default database sql/item_strfunc.cc: Removed wrong push sql/mysqld.cc: Indentation fix sql/sql_base.cc: Use share->db instead of share->table_cache_key Remove assert that can never fail (because of test in previous row) sql/sql_db.cc: Allow empty database name when called from SP (To allow on run without a default database) sql/sql_parse.cc: Added comment sql/sql_show.cc: Indentation fixes Simplified code by checking for 'wrong' condition first and doing continue instead of going down one level Simplified precision and decimal handling --- mysql-test/t/information_schema.test | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test/t/information_schema.test') diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 03810447299..04d53828035 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -649,4 +649,5 @@ use information_schema; select column_name, column_default from columns where table_schema='test' and table_name='t1'; use test; +show columns from t1; drop table t1; -- cgit v1.2.1 From ce9b8cea88649be60473c766d119e78baff3fe67 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 22 Aug 2005 17:32:02 -0700 Subject: sql_show.cc: Database name was set incorrectly for any show command that used sunqueries in its where condition. information_schema.test, information_schema.result: Added a test case for bug #12636. mysql-test/r/information_schema.result: Added a test case for bug #12636. mysql-test/t/information_schema.test: Added a test case for bug #12636. sql/sql_show.cc: Database name was set incorrectly for any show command that used sunqueries in its where condition. --- mysql-test/t/information_schema.test | 15 +++++++++++++++ 1 file changed, 15 insertions(+) (limited to 'mysql-test/t/information_schema.test') diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 03810447299..5410885a404 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -650,3 +650,18 @@ select column_name, column_default from columns where table_schema='test' and table_name='t1'; use test; drop table t1; + +# +# Bug #12636: SHOW TABLE STATUS with where condition containing a subquery +# over information schema +# + +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int); + +--replace_column 8 # 12 # 13 # +SHOW TABLE STATUS FROM test + WHERE name IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES + WHERE TABLE_SCHEMA='test' AND TABLE_TYPE='BASE TABLE'); + +DROP TABLE t1,t2 -- cgit v1.2.1 From 2f4a043ce75bc7c712aba9ece9cbe618835aa184 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 25 Aug 2005 12:03:19 +0300 Subject: Make test predictable mysql-test/r/information_schema.result: Ensure that rows are in given order mysql-test/t/information_schema.test: Ensure that rows are in given order --- mysql-test/t/information_schema.test | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/t/information_schema.test') diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index d6a47b88ac0..23f88b75576 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -138,7 +138,7 @@ information_schema.SCHEMATA b where a.ROUTINE_SCHEMA = b.SCHEMA_NAME; select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a, -mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8); +mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) order by 1; select count(*) from information_schema.ROUTINES; connect (user1,localhost,mysqltest_1,,); -- cgit v1.2.1