From 10cdf90bd37fe5eb2cb22398520f3c6e8cb693ae Mon Sep 17 00:00:00 2001 From: "cmiller@zippy.cornsilk.net" <> Date: Wed, 3 Jan 2007 17:15:10 -0500 Subject: Bug#24795: Add SHOW PROFILE MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Patch contributed by Jeremy Cole. CLA received Oct 2006 by Kaj Arnö Add rudimentary query profiling support. --- mysql-test/r/profile.result | 30 ++++++++++++++++++++++++++++++ mysql-test/t/profile.test | 39 +++++++++++++++++++++++++++++++++++++++ 2 files changed, 69 insertions(+) create mode 100644 mysql-test/r/profile.result create mode 100644 mysql-test/t/profile.test (limited to 'mysql-test') diff --git a/mysql-test/r/profile.result b/mysql-test/r/profile.result new file mode 100644 index 00000000000..8a4db348445 --- /dev/null +++ b/mysql-test/r/profile.result @@ -0,0 +1,30 @@ +create table t1 ( +a int, +b int +); +insert into t1 values (1,1), (2,null), (3, 4); +insert into t1 values (5,1), (6,null), (7, 4); +insert into t1 values (1,1), (2,null), (3, 4); +insert into t1 values (5,1), (6,null), (7, 4); +insert into t1 values (5,1), (6,null), (7, 4); +select sum(a) from t1; +select sum(a) from t1 group by b; +select sum(a) + sum(b) from t1 group by b; +select max(x) from (select sum(a) as x from t1 group by b) as teeone; +show profiles; +show profile for query 8; +show profile cpu, block io for query 8; +show profile cpu for query 8; +show profile cpu for query 9 limit 2 offset 2; +show profile cpu for query 10 limit 0; +show profile cpu for query 65534; +show profile memory; +show profile block io; +show profile context switches; +show profile page faults; +show profile ipc; +show profile swaps limit 1 offset 2; +show profile source; +show profile all for query 0 limit 0; +drop table t1; +End of 5.0 tests diff --git a/mysql-test/t/profile.test b/mysql-test/t/profile.test new file mode 100644 index 00000000000..4cf2a3aa92f --- /dev/null +++ b/mysql-test/t/profile.test @@ -0,0 +1,39 @@ + +create table t1 ( + a int, + b int +); +--disable_result_log +insert into t1 values (1,1), (2,null), (3, 4); +insert into t1 values (5,1), (6,null), (7, 4); +insert into t1 values (1,1), (2,null), (3, 4); +insert into t1 values (5,1), (6,null), (7, 4); +insert into t1 values (5,1), (6,null), (7, 4); +select sum(a) from t1; +select sum(a) from t1 group by b; +select sum(a) + sum(b) from t1 group by b; +select max(x) from (select sum(a) as x from t1 group by b) as teeone; + +# Merely verify that commands work. Checking values is impossible, right? +show profiles; +show profile for query 8; +show profile cpu, block io for query 8; +show profile cpu for query 8; +show profile cpu for query 9 limit 2 offset 2; +show profile cpu for query 10 limit 0; +show profile cpu for query 65534; +show profile memory; +show profile block io; +show profile context switches; +show profile page faults; +show profile ipc; +show profile swaps limit 1 offset 2; +show profile source; +show profile all for query 0 limit 0; +--enable_result_log + +drop table t1; + + +## +--echo End of 5.0 tests -- cgit v1.2.1 From 205d6a35b3bf13783f4505386aa50c41405dcc64 Mon Sep 17 00:00:00 2001 From: "cmiller@zippy.cornsilk.net" <> Date: Thu, 18 Jan 2007 09:50:16 -0500 Subject: Bug#24822: Patch: uptime_since_flush_status Provide the number of seconds since flush as a new status variable named "Uptime_since_flush_status". --- Override the post-flush value, as a second could pass between the two statements. --- mysql-test/r/information_schema.result | 7 +++++++ mysql-test/t/information_schema.test | 13 ++++++++++--- 2 files changed, 17 insertions(+), 3 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 21d7bfb1b21..d4fd536003a 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1269,3 +1269,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY system NULL NULL NULL NULL 0 const row not found 2 DERIVED tables ALL NULL NULL NULL NULL 2 drop view v1; +show global status like "Uptime_%"; +Variable_name Value +Uptime_since_flush_status # +flush status; +show global status like "Uptime_%"; +Variable_name Value +Uptime_since_flush_status # diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 623ccee49e4..a4cf3b497be 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -971,9 +971,6 @@ SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT), COLUMN_DEFAULT= DROP TABLE bug23037; DROP FUNCTION get_value; - - - # # Bug#22413: EXPLAIN SELECT FROM view with ORDER BY yield server crash # @@ -987,4 +984,14 @@ explain select * from v1; explain select * from (select table_name from information_schema.tables) as a; drop view v1; +# +# Bug#24822: Patch: uptime_since_flush_status +# +--replace_column 2 # +show global status like "Uptime_%"; +flush status; +--replace_column 2 # +show global status like "Uptime_%"; # Almost certainly zero + + # End of 5.0 tests. -- cgit v1.2.1 From 6e096ee8d9f5aa2cbe3c2bba4aecde6fceaa888f Mon Sep 17 00:00:00 2001 From: "cmiller@zippy.cornsilk.net" <> Date: Thu, 22 Feb 2007 10:03:08 -0500 Subject: Prevent bugs by making DBUG_* expressions syntactically equivalent to a single statement. --- Bug#24795: SHOW PROFILE Profiling is only partially functional on some architectures. Where there is no getrusage() system call, presently Null values are returned where it would be required. Notably, Windows needs some love applied to make it as useful. Syntax this adds: SHOW PROFILES SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n] where "n" is an integer and "types" is zero or many (comma-separated) of "CPU" "MEMORY" (not presently supported) "BLOCK IO" "CONTEXT SWITCHES" "PAGE FAULTS" "IPC" "SWAPS" "SOURCE" "ALL" It also adds a session variable (boolean) "profiling", set to "no" by default, and (integer) profiling_history_size, set to 15 by default. This patch abstracts setting THDs' "proc_info" behind a macro that can be used as a hook into the profiling code when profiling support is compiled in. All future code in this line should use that mechanism for setting thd->proc_info. --- Tests are now set to omit the statistics. --- Adds an Information_schema table, "profiling" for access to "show profile" data. --- Merge zippy.cornsilk.net:/home/cmiller/work/mysql/mysql-5.0-community-3--bug24795 into zippy.cornsilk.net:/home/cmiller/work/mysql/mysql-5.0-community --- Fix merge problems. --- Fixed one bug in the query_source being NULL. Updated test results. --- Include more thorough profiling tests. Improve support for prepared statements. Use session-specific query IDs, starting at zero. --- Selecting from I_S.profiling is no longer quashed in profiling, as requested by Giuseppe. Limit the size of captured query text. No longer log queries that are zero length. --- mysql-test/r/information_schema.result | 11 +- mysql-test/r/information_schema_db.result | 1 + mysql-test/r/mysqlshow.result | 2 + mysql-test/r/profile.result | 30 --- mysql-test/r/profiling.result | 365 ++++++++++++++++++++++++++++++ mysql-test/t/profile.test | 39 ---- mysql-test/t/profiling.test | 195 ++++++++++++++++ 7 files changed, 570 insertions(+), 73 deletions(-) delete mode 100644 mysql-test/r/profile.result create mode 100644 mysql-test/r/profiling.result delete mode 100644 mysql-test/t/profile.test create mode 100644 mysql-test/t/profiling.test (limited to 'mysql-test') diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index d4fd536003a..742d35d4ce7 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -42,6 +42,7 @@ COLLATION_CHARACTER_SET_APPLICABILITY COLUMNS COLUMN_PRIVILEGES KEY_COLUMN_USAGE +PROFILING ROUTINES SCHEMATA SCHEMA_PRIVILEGES @@ -730,7 +731,7 @@ CREATE TABLE t_crashme ( f1 BIGINT); CREATE VIEW a1 (t_CRASHME) AS SELECT f1 FROM t_crashme GROUP BY f1; CREATE VIEW a2 AS SELECT t_CRASHME FROM a1; count(*) -101 +102 drop view a2, a1; drop table t_crashme; select table_schema,table_name, column_name from @@ -801,7 +802,7 @@ delete from mysql.db where user='mysqltest_4'; flush privileges; SELECT table_schema, count(*) FROM information_schema.TABLES GROUP BY TABLE_SCHEMA; table_schema count(*) -information_schema 16 +information_schema 17 mysql 17 create table t1 (i int, j int); create trigger trg1 before insert on t1 for each row @@ -1094,8 +1095,8 @@ table_schema='information_schema' and (column_type = 'varchar(7)' or column_type = 'varchar(20)') group by column_type order by num; column_type group_concat(table_schema, '.', table_name) num -varchar(20) information_schema.COLUMNS 1 -varchar(7) information_schema.ROUTINES,information_schema.VIEWS 2 +varchar(20) information_schema.COLUMNS,information_schema.PROFILING 2 +varchar(7) information_schema.PROFILING,information_schema.PROFILING,information_schema.PROFILING,information_schema.ROUTINES,information_schema.VIEWS 5 create table t1(f1 char(1) not null, f2 char(9) not null) default character set utf8; select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from @@ -1192,6 +1193,7 @@ COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME COLUMNS TABLE_SCHEMA COLUMN_PRIVILEGES TABLE_SCHEMA KEY_COLUMN_USAGE CONSTRAINT_SCHEMA +PROFILING Query_id ROUTINES ROUTINE_SCHEMA SCHEMATA SCHEMA_NAME SCHEMA_PRIVILEGES TABLE_SCHEMA @@ -1223,6 +1225,7 @@ COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME COLUMNS TABLE_SCHEMA COLUMN_PRIVILEGES TABLE_SCHEMA KEY_COLUMN_USAGE CONSTRAINT_SCHEMA +PROFILING Query_id ROUTINES ROUTINE_SCHEMA SCHEMATA SCHEMA_NAME SCHEMA_PRIVILEGES TABLE_SCHEMA diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index 40773a7afc1..dcd48d3aa46 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -11,6 +11,7 @@ COLLATION_CHARACTER_SET_APPLICABILITY COLUMNS COLUMN_PRIVILEGES KEY_COLUMN_USAGE +PROFILING ROUTINES SCHEMATA SCHEMA_PRIVILEGES diff --git a/mysql-test/r/mysqlshow.result b/mysql-test/r/mysqlshow.result index 2bf8a58de4e..31b4940ec53 100644 --- a/mysql-test/r/mysqlshow.result +++ b/mysql-test/r/mysqlshow.result @@ -85,6 +85,7 @@ Database: information_schema | COLUMNS | | COLUMN_PRIVILEGES | | KEY_COLUMN_USAGE | +| PROFILING | | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | @@ -106,6 +107,7 @@ Database: INFORMATION_SCHEMA | COLUMNS | | COLUMN_PRIVILEGES | | KEY_COLUMN_USAGE | +| PROFILING | | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | diff --git a/mysql-test/r/profile.result b/mysql-test/r/profile.result deleted file mode 100644 index 8a4db348445..00000000000 --- a/mysql-test/r/profile.result +++ /dev/null @@ -1,30 +0,0 @@ -create table t1 ( -a int, -b int -); -insert into t1 values (1,1), (2,null), (3, 4); -insert into t1 values (5,1), (6,null), (7, 4); -insert into t1 values (1,1), (2,null), (3, 4); -insert into t1 values (5,1), (6,null), (7, 4); -insert into t1 values (5,1), (6,null), (7, 4); -select sum(a) from t1; -select sum(a) from t1 group by b; -select sum(a) + sum(b) from t1 group by b; -select max(x) from (select sum(a) as x from t1 group by b) as teeone; -show profiles; -show profile for query 8; -show profile cpu, block io for query 8; -show profile cpu for query 8; -show profile cpu for query 9 limit 2 offset 2; -show profile cpu for query 10 limit 0; -show profile cpu for query 65534; -show profile memory; -show profile block io; -show profile context switches; -show profile page faults; -show profile ipc; -show profile swaps limit 1 offset 2; -show profile source; -show profile all for query 0 limit 0; -drop table t1; -End of 5.0 tests diff --git a/mysql-test/r/profiling.result b/mysql-test/r/profiling.result new file mode 100644 index 00000000000..f8aa060eee4 --- /dev/null +++ b/mysql-test/r/profiling.result @@ -0,0 +1,365 @@ +show session variables like 'profil%'; +Variable_name Value +profiling OFF +profiling_history_size 15 +select @@profiling; +@@profiling +0 +set global profiling = ON; +ERROR HY000: Variable 'profiling' is a SESSION variable and can't be used with SET GLOBAL +set global profiling_history_size=100; +show global variables like 'profil%'; +Variable_name Value +profiling OFF +profiling_history_size 100 +set session profiling = ON; +set session profiling_history_size=30; +show session variables like 'profil%'; +Variable_name Value +profiling ON +profiling_history_size 30 +select @@profiling; +@@profiling +1 +create table t1 ( +a int, +b int +); +insert into t1 values (1,1), (2,null), (3, 4); +insert into t1 values (5,1), (6,null), (7, 4); +insert into t1 values (1,1), (2,null), (3, 4); +insert into t1 values (5,1), (6,null), (7, 4); +select max(x) from (select sum(a) as x from t1 group by b) as teeone; +max(x) +20 +insert into t1 select * from t1; +select count(*) from t1; +count(*) +24 +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +select count(*) from t1; +count(*) +192 +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +select count(*) from t1; +count(*) +1536 +select sum(a) from t1; +sum(a) +6144 +select sum(a) from t1 group by b; +sum(a) +2048 +1536 +2560 +select sum(a) + sum(b) from t1 group by b; +sum(a) + sum(b) +NULL +2048 +4608 +select max(x) from (select sum(a) as x from t1 group by b) as teeone; +max(x) +2560 +select '012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890' as big_string; +big_string +012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890 +show profiles; +Query_ID Duration Query +0 # set session profiling = ON +1 # set session profiling_history_size=30 +2 # show session variables like 'profil%' +3 # select @@profiling +4 # create table t1 ( +a int, +b int +) +5 # insert into t1 values (1,1), (2,null), (3, 4) +6 # insert into t1 values (5,1), (6,null), (7, 4) +7 # insert into t1 values (1,1), (2,null), (3, 4) +8 # insert into t1 values (5,1), (6,null), (7, 4) +9 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +10 # insert into t1 select * from t1 +11 # select count(*) from t1 +12 # insert into t1 select * from t1 +13 # insert into t1 select * from t1 +14 # insert into t1 select * from t1 +15 # select count(*) from t1 +16 # insert into t1 select * from t1 +17 # insert into t1 select * from t1 +18 # insert into t1 select * from t1 +19 # select count(*) from t1 +20 # select sum(a) from t1 +21 # select sum(a) from t1 group by b +22 # select sum(a) + sum(b) from t1 group by b +23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 +show profile for query 15; +show profile cpu for query 15; +show profile cpu, block io for query 15; +show profile cpu for query 9 limit 2 offset 2; +show profile cpu for query 10 limit 0; +show profile cpu for query 65534; +show profile memory; +show profile block io; +show profile context switches; +show profile page faults; +show profile ipc; +show profile swaps limit 1 offset 2; +show profile source; +show profile all for query 0 limit 0; +show profile all for query 15; +select * from information_schema.profiling; +select query_id, state, duration from information_schema.profiling; +select query_id, sum(duration) from information_schema.profiling group by query_id; +select query_id, count(*) from information_schema.profiling group by query_id; +select sum(duration) from information_schema.profiling; +select query_id, count(*), sum(duration) from information_schema.profiling group by query_id; +select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling; +drop table if exists t1, t2, t3; +Warnings: +Note 1051 Unknown table 't2' +Note 1051 Unknown table 't3' +create table t1 (id int ); +create table t2 (id int not null); +create table t3 (id int not null primary key); +insert into t1 values (1), (2), (3); +insert into t2 values (1), (2), (3); +insert into t3 values (1), (2), (3); +show profiles; +Query_ID Duration Query +10 # insert into t1 select * from t1 +11 # select count(*) from t1 +12 # insert into t1 select * from t1 +13 # insert into t1 select * from t1 +14 # insert into t1 select * from t1 +15 # select count(*) from t1 +16 # insert into t1 select * from t1 +17 # insert into t1 select * from t1 +18 # insert into t1 select * from t1 +19 # select count(*) from t1 +20 # select sum(a) from t1 +21 # select sum(a) from t1 group by b +22 # select sum(a) + sum(b) from t1 group by b +23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 +25 # select * from information_schema.profiling +26 # select query_id, state, duration from information_schema.profiling +27 # select query_id, sum(duration) from information_schema.profiling group by query_id +28 # select query_id, count(*) from information_schema.profiling group by query_id +29 # select sum(duration) from information_schema.profiling +30 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id +31 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling +32 # drop table if exists t1, t2, t3 +33 # SHOW WARNINGS +34 # create table t1 (id int ) +35 # create table t2 (id int not null) +36 # create table t3 (id int not null primary key) +37 # insert into t1 values (1), (2), (3) +38 # insert into t2 values (1), (2), (3) +39 # insert into t3 values (1), (2), (3) +select * from t1; +id +1 +2 +3 +show profiles; +Query_ID Duration Query +11 # select count(*) from t1 +12 # insert into t1 select * from t1 +13 # insert into t1 select * from t1 +14 # insert into t1 select * from t1 +15 # select count(*) from t1 +16 # insert into t1 select * from t1 +17 # insert into t1 select * from t1 +18 # insert into t1 select * from t1 +19 # select count(*) from t1 +20 # select sum(a) from t1 +21 # select sum(a) from t1 group by b +22 # select sum(a) + sum(b) from t1 group by b +23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 +25 # select * from information_schema.profiling +26 # select query_id, state, duration from information_schema.profiling +27 # select query_id, sum(duration) from information_schema.profiling group by query_id +28 # select query_id, count(*) from information_schema.profiling group by query_id +29 # select sum(duration) from information_schema.profiling +30 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id +31 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling +32 # drop table if exists t1, t2, t3 +33 # SHOW WARNINGS +34 # create table t1 (id int ) +35 # create table t2 (id int not null) +36 # create table t3 (id int not null primary key) +37 # insert into t1 values (1), (2), (3) +38 # insert into t2 values (1), (2), (3) +39 # insert into t3 values (1), (2), (3) +40 # select * from t1 +This ^^ should end in "select * from t1;\" +delete from t1; +insert into t1 values (1), (2), (3); +insert into t1 values (1), (2), (3); +select * from t1; +id +1 +2 +3 +1 +2 +3 +show profiles; +Query_ID Duration Query +15 # select count(*) from t1 +16 # insert into t1 select * from t1 +17 # insert into t1 select * from t1 +18 # insert into t1 select * from t1 +19 # select count(*) from t1 +20 # select sum(a) from t1 +21 # select sum(a) from t1 group by b +22 # select sum(a) + sum(b) from t1 group by b +23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 +25 # select * from information_schema.profiling +26 # select query_id, state, duration from information_schema.profiling +27 # select query_id, sum(duration) from information_schema.profiling group by query_id +28 # select query_id, count(*) from information_schema.profiling group by query_id +29 # select sum(duration) from information_schema.profiling +30 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id +31 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling +32 # drop table if exists t1, t2, t3 +33 # SHOW WARNINGS +34 # create table t1 (id int ) +35 # create table t2 (id int not null) +36 # create table t3 (id int not null primary key) +37 # insert into t1 values (1), (2), (3) +38 # insert into t2 values (1), (2), (3) +39 # insert into t3 values (1), (2), (3) +40 # select * from t1 +41 # delete from t1 +42 # insert into t1 values (1), (2), (3) +43 # insert into t1 values (1), (2), (3) +44 # select * from t1 +set session profiling = OFF; +select sum(id) from t1; +sum(id) +12 +show profiles; +Query_ID Duration Query +16 # insert into t1 select * from t1 +17 # insert into t1 select * from t1 +18 # insert into t1 select * from t1 +19 # select count(*) from t1 +20 # select sum(a) from t1 +21 # select sum(a) from t1 group by b +22 # select sum(a) + sum(b) from t1 group by b +23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 +25 # select * from information_schema.profiling +26 # select query_id, state, duration from information_schema.profiling +27 # select query_id, sum(duration) from information_schema.profiling group by query_id +28 # select query_id, count(*) from information_schema.profiling group by query_id +29 # select sum(duration) from information_schema.profiling +30 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id +31 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling +32 # drop table if exists t1, t2, t3 +33 # SHOW WARNINGS +34 # create table t1 (id int ) +35 # create table t2 (id int not null) +36 # create table t3 (id int not null primary key) +37 # insert into t1 values (1), (2), (3) +38 # insert into t2 values (1), (2), (3) +39 # insert into t3 values (1), (2), (3) +40 # select * from t1 +41 # delete from t1 +42 # insert into t1 values (1), (2), (3) +43 # insert into t1 values (1), (2), (3) +44 # select * from t1 +45 # set session profiling = OFF +set session profiling = ON; +select @@profiling; +@@profiling +1 +create function f1() returns varchar(50) return 'hello'; +select @@profiling; +@@profiling +1 +select * from t1 where id <> f1(); +id +1 +2 +3 +1 +2 +3 +select @@profiling; +@@profiling +1 +set session profiling = ON; +drop table if exists t2; +create table t2 (id int not null); +create trigger t2_bi before insert on t2 for each row set @x=0; +select @@profiling; +@@profiling +1 +insert into t2 values (1), (2), (3); +select @@profiling; +@@profiling +1 +set session profiling = ON; +drop table if exists t1, t2; +create table t1 (id int not null primary key); +create table t2 (id int not null primary key, id1 int not null); +select @@profiling; +@@profiling +1 +alter table t2 add foreign key (id1) references t1 (id) on delete cascade; +select @@profiling; +@@profiling +1 +lock table t1 write; +select @@profiling; +@@profiling +1 +unlock table; +select @@profiling; +@@profiling +1 +set autocommit=0; +select @@profiling, @@autocommit; +@@profiling @@autocommit +1 0 +begin; +select @@profiling; +@@profiling +1 +insert into t1 values (1); +insert into t2 values (1,1); +testing referential integrity cascade +delete from t1 where id = 1; +select @@profiling; +@@profiling +1 +testing rollback +rollback; +select @@profiling; +@@profiling +1 +testing commit +begin; +select @@profiling; +@@profiling +1 +commit; +select @@profiling; +@@profiling +1 +drop table if exists t1, t2, t3; +drop view if exists v1; +Warnings: +Note 1051 Unknown table 'test.v1' +drop function if exists f1; +set session profiling = OFF; +End of 5.0 tests diff --git a/mysql-test/t/profile.test b/mysql-test/t/profile.test deleted file mode 100644 index 4cf2a3aa92f..00000000000 --- a/mysql-test/t/profile.test +++ /dev/null @@ -1,39 +0,0 @@ - -create table t1 ( - a int, - b int -); ---disable_result_log -insert into t1 values (1,1), (2,null), (3, 4); -insert into t1 values (5,1), (6,null), (7, 4); -insert into t1 values (1,1), (2,null), (3, 4); -insert into t1 values (5,1), (6,null), (7, 4); -insert into t1 values (5,1), (6,null), (7, 4); -select sum(a) from t1; -select sum(a) from t1 group by b; -select sum(a) + sum(b) from t1 group by b; -select max(x) from (select sum(a) as x from t1 group by b) as teeone; - -# Merely verify that commands work. Checking values is impossible, right? -show profiles; -show profile for query 8; -show profile cpu, block io for query 8; -show profile cpu for query 8; -show profile cpu for query 9 limit 2 offset 2; -show profile cpu for query 10 limit 0; -show profile cpu for query 65534; -show profile memory; -show profile block io; -show profile context switches; -show profile page faults; -show profile ipc; -show profile swaps limit 1 offset 2; -show profile source; -show profile all for query 0 limit 0; ---enable_result_log - -drop table t1; - - -## ---echo End of 5.0 tests diff --git a/mysql-test/t/profiling.test b/mysql-test/t/profiling.test new file mode 100644 index 00000000000..ebde8eeffd8 --- /dev/null +++ b/mysql-test/t/profiling.test @@ -0,0 +1,195 @@ +# default is OFF +show session variables like 'profil%'; +select @@profiling; + +# setting global variable is an error +--error ER_LOCAL_VARIABLE +set global profiling = ON; + +# But size is okay +set global profiling_history_size=100; +show global variables like 'profil%'; + +# turn on for testing +set session profiling = ON; +set session profiling_history_size=30; # small enough to overflow + +# verify it is active +show session variables like 'profil%'; +select @@profiling; + +# Profiling is a descriptive look into the way the server operated +# in retrospect. Chad doesn't think it's wise to include the result +# log, as this creates a proscriptive specification about how the +# server should work in the future -- or it forces everyone who +# changes the server significantly to record the test results again, +# and that violates the spirit of our tests. Please don't include +# execution-specific data here, as in all of the "show profile" and +# information_schema.profiling results. + +create table t1 ( + a int, + b int +); +insert into t1 values (1,1), (2,null), (3, 4); +insert into t1 values (5,1), (6,null), (7, 4); +insert into t1 values (1,1), (2,null), (3, 4); +insert into t1 values (5,1), (6,null), (7, 4); +select max(x) from (select sum(a) as x from t1 group by b) as teeone; +insert into t1 select * from t1; +select count(*) from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +select count(*) from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +select count(*) from t1; +select sum(a) from t1; +select sum(a) from t1 group by b; +select sum(a) + sum(b) from t1 group by b; +select max(x) from (select sum(a) as x from t1 group by b) as teeone; +select '012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890' as big_string; + +--enable_result_log +--replace_column 2 # +show profiles; + +--disable_result_log +###--replace_column 2 # 3 # 4 # +show profile for query 15; +###--replace_column 2 # 3 # 4 # +show profile cpu for query 15; +###--replace_column 2 # 3 # 4 # 5 # 6 # +show profile cpu, block io for query 15; +###--replace_column 2 # 3 # 4 # +show profile cpu for query 9 limit 2 offset 2; +show profile cpu for query 10 limit 0; +--error 0,ER_WRONG_ARGUMENTS +show profile cpu for query 65534; +###--replace_column 2 # +show profile memory; +###--replace_column 2 # 3 # 4 # +show profile block io; +###--replace_column 2 # 3 # 4 # +show profile context switches; +###--replace_column 2 # 3 # 4 # +show profile page faults; +###--replace_column 2 # 3 # 4 # +show profile ipc; +###--replace_column 2 # +show profile swaps limit 1 offset 2; +###--replace_column 2 # 5 # +show profile source; +show profile all for query 0 limit 0; +###--replace_column 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 11 # 12 # 13 # 16 # +show profile all for query 15; +###--replace_column 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 10 # 11 # 12 # 13 # 16 # + +select * from information_schema.profiling; +select query_id, state, duration from information_schema.profiling; +select query_id, sum(duration) from information_schema.profiling group by query_id; +select query_id, count(*) from information_schema.profiling group by query_id; +select sum(duration) from information_schema.profiling; + +# Broken down into number of stages and duration of each query. +select query_id, count(*), sum(duration) from information_schema.profiling group by query_id; +select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling; + + +--enable_result_log +drop table if exists t1, t2, t3; +create table t1 (id int ); +create table t2 (id int not null); +create table t3 (id int not null primary key); +insert into t1 values (1), (2), (3); +insert into t2 values (1), (2), (3); +insert into t3 values (1), (2), (3); + +--replace_column 2 # +show profiles; + +select * from t1; +--replace_column 2 # +show profiles; +--echo This ^^ should end in \"select * from t1;\" + +delete from t1; +insert into t1 values (1), (2), (3); +insert into t1 values (1), (2), (3); + +select * from t1; +--replace_column 2 # +show profiles; + +# Turning profiling off does freeze it +set session profiling = OFF; +select sum(id) from t1; +--replace_column 2 # +show profiles; + +## Verify that the various juggling of THD contexts doesn't affect profiling. + +## Functions +set session profiling = ON; +select @@profiling; +create function f1() returns varchar(50) return 'hello'; +select @@profiling; +select * from t1 where id <> f1(); +select @@profiling; + +## Triggers +set session profiling = ON; +drop table if exists t2; +create table t2 (id int not null); +create trigger t2_bi before insert on t2 for each row set @x=0; +select @@profiling; +insert into t2 values (1), (2), (3); +select @@profiling; + +## ALTER TABLE +set session profiling = ON; +drop table if exists t1, t2; +create table t1 (id int not null primary key); +create table t2 (id int not null primary key, id1 int not null); +select @@profiling; +alter table t2 add foreign key (id1) references t1 (id) on delete cascade; +select @@profiling; + +## Table LOCKing +lock table t1 write; +select @@profiling; +unlock table; +select @@profiling; + +## Transactions +set autocommit=0; +select @@profiling, @@autocommit; +begin; +select @@profiling; +insert into t1 values (1); +insert into t2 values (1,1); +--echo testing referential integrity cascade +delete from t1 where id = 1; +select @@profiling; +--echo testing rollback +--disable_warnings +rollback; +--enable_warnings +select @@profiling; +--echo testing commit +begin; +select @@profiling; +commit; +select @@profiling; + +drop table if exists t1, t2, t3; +drop view if exists v1; +drop function if exists f1; + +## last thing in the file +set session profiling = OFF; + +## +--echo End of 5.0 tests -- cgit v1.2.1 From cda57d8f707bd2a2ac71db20afa5b0ec6d150456 Mon Sep 17 00:00:00 2001 From: "cmiller@calliope.local.cmiller/calliope.local" <> Date: Tue, 27 Feb 2007 11:30:03 -0500 Subject: Updated column names of I_S table to conform to other I_S uppercase scheme. --- mysql-test/r/information_schema.result | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 742d35d4ce7..d89514c6060 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1193,7 +1193,7 @@ COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME COLUMNS TABLE_SCHEMA COLUMN_PRIVILEGES TABLE_SCHEMA KEY_COLUMN_USAGE CONSTRAINT_SCHEMA -PROFILING Query_id +PROFILING QUERY_ID ROUTINES ROUTINE_SCHEMA SCHEMATA SCHEMA_NAME SCHEMA_PRIVILEGES TABLE_SCHEMA @@ -1225,7 +1225,7 @@ COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME COLUMNS TABLE_SCHEMA COLUMN_PRIVILEGES TABLE_SCHEMA KEY_COLUMN_USAGE CONSTRAINT_SCHEMA -PROFILING Query_id +PROFILING QUERY_ID ROUTINES ROUTINE_SCHEMA SCHEMATA SCHEMA_NAME SCHEMA_PRIVILEGES TABLE_SCHEMA -- cgit v1.2.1 From fac19ef0e26f120025e3fda1b0cf6da4ba7e0457 Mon Sep 17 00:00:00 2001 From: "cmiller@zippy.cornsilk.net" <> Date: Tue, 27 Feb 2007 20:04:58 -0500 Subject: Update to I_S result that now contains PROFILING entry. --- mysql-test/r/information_schema.result | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test') diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 0371652b081..b01ed152e33 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1308,6 +1308,7 @@ COLLATION_CHARACTER_SET_APPLICABILITY information_schema.COLLATION_CHARACTER_SET COLUMNS information_schema.COLUMNS 1 COLUMN_PRIVILEGES information_schema.COLUMN_PRIVILEGES 1 KEY_COLUMN_USAGE information_schema.KEY_COLUMN_USAGE 1 +PROFILING information_schema.PROFILING 1 ROUTINES information_schema.ROUTINES 1 SCHEMATA information_schema.SCHEMATA 1 SCHEMA_PRIVILEGES information_schema.SCHEMA_PRIVILEGES 1 -- cgit v1.2.1 From 27720d8b2663f53248327f7210973820ab706e51 Mon Sep 17 00:00:00 2001 From: "cmiller@zippy.cornsilk.net" <> Date: Tue, 3 Apr 2007 13:26:19 -0400 Subject: Listing all columns of a particular type tells us nothing at all. The test fails, and it shouldn't. --- mysql-test/r/information_schema.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index b01ed152e33..97bb5e4e446 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1096,7 +1096,7 @@ table_schema='information_schema' and group by column_type order by num; column_type group_concat(table_schema, '.', table_name) num varchar(20) information_schema.COLUMNS,information_schema.PROFILING 2 -varchar(7) information_schema.PROFILING,information_schema.PROFILING,information_schema.PROFILING,information_schema.ROUTINES,information_schema.VIEWS 5 +varchar(7) information_schema.ROUTINES,information_schema.VIEWS 2 create table t1(f1 char(1) not null, f2 char(9) not null) default character set utf8; select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from -- cgit v1.2.1 From 9e71adc46fea09b1bfe51427121f6848da963eb7 Mon Sep 17 00:00:00 2001 From: "cmiller@zippy.cornsilk.net" <> Date: Tue, 3 Apr 2007 14:05:00 -0400 Subject: The test suite erroneously removes backslashes. --- mysql-test/r/profiling.result | 2 +- mysql-test/t/profiling.test | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/profiling.result b/mysql-test/r/profiling.result index f8aa060eee4..a1e994220b9 100644 --- a/mysql-test/r/profiling.result +++ b/mysql-test/r/profiling.result @@ -198,7 +198,7 @@ Query_ID Duration Query 38 # insert into t2 values (1), (2), (3) 39 # insert into t3 values (1), (2), (3) 40 # select * from t1 -This ^^ should end in "select * from t1;\" +This ^^ should end in "select * from t1;" delete from t1; insert into t1 values (1), (2), (3); insert into t1 values (1), (2), (3); diff --git a/mysql-test/t/profiling.test b/mysql-test/t/profiling.test index ebde8eeffd8..fd7562f6f16 100644 --- a/mysql-test/t/profiling.test +++ b/mysql-test/t/profiling.test @@ -113,7 +113,7 @@ show profiles; select * from t1; --replace_column 2 # show profiles; ---echo This ^^ should end in \"select * from t1;\" +--echo This ^^ should end in "select * from t1;" delete from t1; insert into t1 values (1), (2), (3); -- cgit v1.2.1 From 0b3dfea79ae0d65ac0a7d88d3154848a51e92802 Mon Sep 17 00:00:00 2001 From: "cmiller@zippy.cornsilk.net" <> Date: Tue, 3 Apr 2007 14:08:09 -0400 Subject: Backport of Igor's patch for Bug#27362, March 22 2007. Fixed bug #27362: crash at evaluation of IN predicate when one of its argument happened to be a decimal expression returning the NULL value. The crash was due to the fact the function in_decimal::set did not take into account that val_decimal() could return 0 if the decimal expression had been evaluated to NULL. --- mysql-test/r/func_in.result | 5 +++++ mysql-test/t/func_in.test | 11 +++++++++++ 2 files changed, 16 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index fad9a7157e1..87855091699 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -470,4 +470,9 @@ a Warnings: Warning 1292 Incorrect date value: '19772-07-29' for column 'a' at row 1 DROP TABLE t1,t2,t3,t4; +CREATE TABLE t1 (id int not null); +INSERT INTO t1 VALUES (1),(2); +SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) ); +id +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index f9749662ec1..77592d015eb 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -360,4 +360,15 @@ SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29'); DROP TABLE t1,t2,t3,t4; +# +# BUG#27362: IN with a decimal expression that may return NULL +# + +CREATE TABLE t1 (id int not null); +INSERT INTO t1 VALUES (1),(2); + +SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) ); + +DROP TABLE t1; + --echo End of 5.0 tests -- cgit v1.2.1 From 2e698f8c4e2cf51a82eb6a76d2e8d66f82862b5e Mon Sep 17 00:00:00 2001 From: "cmiller@zippy.cornsilk.net" <> Date: Tue, 3 Apr 2007 18:12:31 -0400 Subject: Backport: Patch contributed by Jocelyn Fournier. CLA received 2007-02-27. B-g#25347: mysqlcheck -A -r doesn't repair table marked as crashed mysqlcheck tests nullness of the engine type to know whether the "table" is a view or not. That also falsely catches tables that are severly damaged. Instead, use SHOW FULL TABLES to test whether a "table" is a view or not. (Don't add new function. Instead, get original data a smarter way.) Make it safe for use against databases before when views appeared. --- mysql-test/r/mysqlcheck.result | 10 ++++++++++ mysql-test/t/mysqlcheck.test | 16 ++++++++++++++++ 2 files changed, 26 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index b2820df8f4c..81a30267e04 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -41,4 +41,14 @@ test.t1 OK test.t1 OK drop view v1; drop table t1; +create database d_bug25347; +use d_bug25347; +create table t_bug25347 (a int); +create view v_bug25347 as select * from t_bug25347; +removing and creating +d_bug25347.t_bug25347 OK +drop view v_bug25347; +drop table t_bug25347; +drop database d_bug25347; +use test; End of 5.0 tests diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test index f4e18d4004f..eb732a05909 100644 --- a/mysql-test/t/mysqlcheck.test +++ b/mysql-test/t/mysqlcheck.test @@ -31,4 +31,20 @@ create view v1 as select * from t1; drop view v1; drop table t1; +# +# Bug#25347: mysqlcheck -A -r doesn't repair table marked as crashed +# +create database d_bug25347; +use d_bug25347; +create table t_bug25347 (a int); +create view v_bug25347 as select * from t_bug25347; +--echo removing and creating +--exec rm $MYSQLTEST_VARDIR/master-data/d_bug25347/t_bug25347.MYI; +--exec touch $MYSQLTEST_VARDIR/master-data/d_bug25347/t_bug25347.MYI; +--exec $MYSQL_CHECK --repair --databases d_bug25347 +drop view v_bug25347; +drop table t_bug25347; +drop database d_bug25347; +use test; + --echo End of 5.0 tests -- cgit v1.2.1 From cd28612e4b08c2e4b17d215f0eb70fa0e69120f3 Mon Sep 17 00:00:00 2001 From: "cmiller@zippy.cornsilk.net" <> Date: Tue, 3 Apr 2007 19:50:55 -0400 Subject: Backport: B-g#27501: 5.0 significantly more sys ("kernel") time than 4.1 \ due to getrusage() calls Even if profiling is turned off, the parser makes calls to reset the state at the beginning of each query. That would eventually instantiate a PROFILE_ENTRY, which does indeed capture resource usage. Instead, now check that profiling is active before progressing far into the storage/expiration of old entries in the history. This has the pleasant side-effect that queries to toggle profiling are not recorded in the history. --- mysql-test/r/profiling.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/profiling.result b/mysql-test/r/profiling.result index a1e994220b9..75977f7f2e8 100644 --- a/mysql-test/r/profiling.result +++ b/mysql-test/r/profiling.result @@ -248,6 +248,7 @@ sum(id) 12 show profiles; Query_ID Duration Query +15 # select count(*) from t1 16 # insert into t1 select * from t1 17 # insert into t1 select * from t1 18 # insert into t1 select * from t1 @@ -277,7 +278,6 @@ Query_ID Duration Query 42 # insert into t1 values (1), (2), (3) 43 # insert into t1 values (1), (2), (3) 44 # select * from t1 -45 # set session profiling = OFF set session profiling = ON; select @@profiling; @@profiling -- cgit v1.2.1 From 4078a7cccec3d5f7d3a84d1e98e4bc6ac6895475 Mon Sep 17 00:00:00 2001 From: "cmiller@zippy.cornsilk.net" <> Date: Tue, 3 Apr 2007 19:52:24 -0400 Subject: Unreported minor bug: We start numbering query IDs at zero, which is a special case in "SHOW PROFILE FOR QUERY n". No one can get the zero item (which is always the statement that turns on profiling), because zero represents the final item, internally. Now, order the queries starting at one. --- mysql-test/r/profiling.result | 254 +++++++++++++++++++++--------------------- 1 file changed, 127 insertions(+), 127 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/profiling.result b/mysql-test/r/profiling.result index 75977f7f2e8..50b6fff82b8 100644 --- a/mysql-test/r/profiling.result +++ b/mysql-test/r/profiling.result @@ -69,34 +69,34 @@ big_string 012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890 show profiles; Query_ID Duration Query -0 # set session profiling = ON -1 # set session profiling_history_size=30 -2 # show session variables like 'profil%' -3 # select @@profiling -4 # create table t1 ( +1 # set session profiling = ON +2 # set session profiling_history_size=30 +3 # show session variables like 'profil%' +4 # select @@profiling +5 # create table t1 ( a int, b int ) -5 # insert into t1 values (1,1), (2,null), (3, 4) -6 # insert into t1 values (5,1), (6,null), (7, 4) -7 # insert into t1 values (1,1), (2,null), (3, 4) -8 # insert into t1 values (5,1), (6,null), (7, 4) -9 # select max(x) from (select sum(a) as x from t1 group by b) as teeone -10 # insert into t1 select * from t1 -11 # select count(*) from t1 -12 # insert into t1 select * from t1 +6 # insert into t1 values (1,1), (2,null), (3, 4) +7 # insert into t1 values (5,1), (6,null), (7, 4) +8 # insert into t1 values (1,1), (2,null), (3, 4) +9 # insert into t1 values (5,1), (6,null), (7, 4) +10 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +11 # insert into t1 select * from t1 +12 # select count(*) from t1 13 # insert into t1 select * from t1 14 # insert into t1 select * from t1 -15 # select count(*) from t1 -16 # insert into t1 select * from t1 +15 # insert into t1 select * from t1 +16 # select count(*) from t1 17 # insert into t1 select * from t1 18 # insert into t1 select * from t1 -19 # select count(*) from t1 -20 # select sum(a) from t1 -21 # select sum(a) from t1 group by b -22 # select sum(a) + sum(b) from t1 group by b -23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone -24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 +19 # insert into t1 select * from t1 +20 # select count(*) from t1 +21 # select sum(a) from t1 +22 # select sum(a) from t1 group by b +23 # select sum(a) + sum(b) from t1 group by b +24 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +25 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 show profile for query 15; show profile cpu for query 15; show profile cpu, block io for query 15; @@ -131,36 +131,36 @@ insert into t2 values (1), (2), (3); insert into t3 values (1), (2), (3); show profiles; Query_ID Duration Query -10 # insert into t1 select * from t1 -11 # select count(*) from t1 -12 # insert into t1 select * from t1 +11 # insert into t1 select * from t1 +12 # select count(*) from t1 13 # insert into t1 select * from t1 14 # insert into t1 select * from t1 -15 # select count(*) from t1 -16 # insert into t1 select * from t1 +15 # insert into t1 select * from t1 +16 # select count(*) from t1 17 # insert into t1 select * from t1 18 # insert into t1 select * from t1 -19 # select count(*) from t1 -20 # select sum(a) from t1 -21 # select sum(a) from t1 group by b -22 # select sum(a) + sum(b) from t1 group by b -23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone -24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 -25 # select * from information_schema.profiling -26 # select query_id, state, duration from information_schema.profiling -27 # select query_id, sum(duration) from information_schema.profiling group by query_id -28 # select query_id, count(*) from information_schema.profiling group by query_id -29 # select sum(duration) from information_schema.profiling -30 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id -31 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling -32 # drop table if exists t1, t2, t3 -33 # SHOW WARNINGS -34 # create table t1 (id int ) -35 # create table t2 (id int not null) -36 # create table t3 (id int not null primary key) -37 # insert into t1 values (1), (2), (3) -38 # insert into t2 values (1), (2), (3) -39 # insert into t3 values (1), (2), (3) +19 # insert into t1 select * from t1 +20 # select count(*) from t1 +21 # select sum(a) from t1 +22 # select sum(a) from t1 group by b +23 # select sum(a) + sum(b) from t1 group by b +24 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +25 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 +26 # select * from information_schema.profiling +27 # select query_id, state, duration from information_schema.profiling +28 # select query_id, sum(duration) from information_schema.profiling group by query_id +29 # select query_id, count(*) from information_schema.profiling group by query_id +30 # select sum(duration) from information_schema.profiling +31 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id +32 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling +33 # drop table if exists t1, t2, t3 +34 # SHOW WARNINGS +35 # create table t1 (id int ) +36 # create table t2 (id int not null) +37 # create table t3 (id int not null primary key) +38 # insert into t1 values (1), (2), (3) +39 # insert into t2 values (1), (2), (3) +40 # insert into t3 values (1), (2), (3) select * from t1; id 1 @@ -168,36 +168,36 @@ id 3 show profiles; Query_ID Duration Query -11 # select count(*) from t1 -12 # insert into t1 select * from t1 +12 # select count(*) from t1 13 # insert into t1 select * from t1 14 # insert into t1 select * from t1 -15 # select count(*) from t1 -16 # insert into t1 select * from t1 +15 # insert into t1 select * from t1 +16 # select count(*) from t1 17 # insert into t1 select * from t1 18 # insert into t1 select * from t1 -19 # select count(*) from t1 -20 # select sum(a) from t1 -21 # select sum(a) from t1 group by b -22 # select sum(a) + sum(b) from t1 group by b -23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone -24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 -25 # select * from information_schema.profiling -26 # select query_id, state, duration from information_schema.profiling -27 # select query_id, sum(duration) from information_schema.profiling group by query_id -28 # select query_id, count(*) from information_schema.profiling group by query_id -29 # select sum(duration) from information_schema.profiling -30 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id -31 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling -32 # drop table if exists t1, t2, t3 -33 # SHOW WARNINGS -34 # create table t1 (id int ) -35 # create table t2 (id int not null) -36 # create table t3 (id int not null primary key) -37 # insert into t1 values (1), (2), (3) -38 # insert into t2 values (1), (2), (3) -39 # insert into t3 values (1), (2), (3) -40 # select * from t1 +19 # insert into t1 select * from t1 +20 # select count(*) from t1 +21 # select sum(a) from t1 +22 # select sum(a) from t1 group by b +23 # select sum(a) + sum(b) from t1 group by b +24 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +25 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 +26 # select * from information_schema.profiling +27 # select query_id, state, duration from information_schema.profiling +28 # select query_id, sum(duration) from information_schema.profiling group by query_id +29 # select query_id, count(*) from information_schema.profiling group by query_id +30 # select sum(duration) from information_schema.profiling +31 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id +32 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling +33 # drop table if exists t1, t2, t3 +34 # SHOW WARNINGS +35 # create table t1 (id int ) +36 # create table t2 (id int not null) +37 # create table t3 (id int not null primary key) +38 # insert into t1 values (1), (2), (3) +39 # insert into t2 values (1), (2), (3) +40 # insert into t3 values (1), (2), (3) +41 # select * from t1 This ^^ should end in "select * from t1;" delete from t1; insert into t1 values (1), (2), (3); @@ -212,72 +212,72 @@ id 3 show profiles; Query_ID Duration Query -15 # select count(*) from t1 -16 # insert into t1 select * from t1 +16 # select count(*) from t1 17 # insert into t1 select * from t1 18 # insert into t1 select * from t1 -19 # select count(*) from t1 -20 # select sum(a) from t1 -21 # select sum(a) from t1 group by b -22 # select sum(a) + sum(b) from t1 group by b -23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone -24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 -25 # select * from information_schema.profiling -26 # select query_id, state, duration from information_schema.profiling -27 # select query_id, sum(duration) from information_schema.profiling group by query_id -28 # select query_id, count(*) from information_schema.profiling group by query_id -29 # select sum(duration) from information_schema.profiling -30 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id -31 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling -32 # drop table if exists t1, t2, t3 -33 # SHOW WARNINGS -34 # create table t1 (id int ) -35 # create table t2 (id int not null) -36 # create table t3 (id int not null primary key) -37 # insert into t1 values (1), (2), (3) -38 # insert into t2 values (1), (2), (3) -39 # insert into t3 values (1), (2), (3) -40 # select * from t1 -41 # delete from t1 -42 # insert into t1 values (1), (2), (3) +19 # insert into t1 select * from t1 +20 # select count(*) from t1 +21 # select sum(a) from t1 +22 # select sum(a) from t1 group by b +23 # select sum(a) + sum(b) from t1 group by b +24 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +25 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 +26 # select * from information_schema.profiling +27 # select query_id, state, duration from information_schema.profiling +28 # select query_id, sum(duration) from information_schema.profiling group by query_id +29 # select query_id, count(*) from information_schema.profiling group by query_id +30 # select sum(duration) from information_schema.profiling +31 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id +32 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling +33 # drop table if exists t1, t2, t3 +34 # SHOW WARNINGS +35 # create table t1 (id int ) +36 # create table t2 (id int not null) +37 # create table t3 (id int not null primary key) +38 # insert into t1 values (1), (2), (3) +39 # insert into t2 values (1), (2), (3) +40 # insert into t3 values (1), (2), (3) +41 # select * from t1 +42 # delete from t1 43 # insert into t1 values (1), (2), (3) -44 # select * from t1 +44 # insert into t1 values (1), (2), (3) +45 # select * from t1 set session profiling = OFF; select sum(id) from t1; sum(id) 12 show profiles; Query_ID Duration Query -15 # select count(*) from t1 -16 # insert into t1 select * from t1 +16 # select count(*) from t1 17 # insert into t1 select * from t1 18 # insert into t1 select * from t1 -19 # select count(*) from t1 -20 # select sum(a) from t1 -21 # select sum(a) from t1 group by b -22 # select sum(a) + sum(b) from t1 group by b -23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone -24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 -25 # select * from information_schema.profiling -26 # select query_id, state, duration from information_schema.profiling -27 # select query_id, sum(duration) from information_schema.profiling group by query_id -28 # select query_id, count(*) from information_schema.profiling group by query_id -29 # select sum(duration) from information_schema.profiling -30 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id -31 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling -32 # drop table if exists t1, t2, t3 -33 # SHOW WARNINGS -34 # create table t1 (id int ) -35 # create table t2 (id int not null) -36 # create table t3 (id int not null primary key) -37 # insert into t1 values (1), (2), (3) -38 # insert into t2 values (1), (2), (3) -39 # insert into t3 values (1), (2), (3) -40 # select * from t1 -41 # delete from t1 -42 # insert into t1 values (1), (2), (3) +19 # insert into t1 select * from t1 +20 # select count(*) from t1 +21 # select sum(a) from t1 +22 # select sum(a) from t1 group by b +23 # select sum(a) + sum(b) from t1 group by b +24 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +25 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 +26 # select * from information_schema.profiling +27 # select query_id, state, duration from information_schema.profiling +28 # select query_id, sum(duration) from information_schema.profiling group by query_id +29 # select query_id, count(*) from information_schema.profiling group by query_id +30 # select sum(duration) from information_schema.profiling +31 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id +32 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling +33 # drop table if exists t1, t2, t3 +34 # SHOW WARNINGS +35 # create table t1 (id int ) +36 # create table t2 (id int not null) +37 # create table t3 (id int not null primary key) +38 # insert into t1 values (1), (2), (3) +39 # insert into t2 values (1), (2), (3) +40 # insert into t3 values (1), (2), (3) +41 # select * from t1 +42 # delete from t1 43 # insert into t1 values (1), (2), (3) -44 # select * from t1 +44 # insert into t1 values (1), (2), (3) +45 # select * from t1 set session profiling = ON; select @@profiling; @@profiling -- cgit v1.2.1 From 88455a047f7d542d3670c073b704cfa2848279d4 Mon Sep 17 00:00:00 2001 From: "cmiller@zippy.cornsilk.net" <> Date: Fri, 6 Apr 2007 09:15:18 -0400 Subject: Changing the state of whether we're recording profiling information halfway through a query (as happens in "SET SESSION PROFILING = ...") has a few side-effects, the worst of which is a memory leak for prepared statements, which poke directly from the parser into the profiling code (we don't have the query text when we need it) and that overwrites a pointer to heap-allocated memory when the previous statement turns on profiling. Instead, now set a flag when we begin a new statement that tracks whether profiling is on _at the start_ of the query. Use that to track whether we gather info. Additionally, use that AND use the state of the profiling variable after the end of a query to know whether to store information about the query that just finished. --- mysql-test/r/profiling.result | 253 +++++++++++++++++++++--------------------- 1 file changed, 126 insertions(+), 127 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/profiling.result b/mysql-test/r/profiling.result index 50b6fff82b8..9f478d68b8d 100644 --- a/mysql-test/r/profiling.result +++ b/mysql-test/r/profiling.result @@ -69,34 +69,33 @@ big_string 012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890 show profiles; Query_ID Duration Query -1 # set session profiling = ON -2 # set session profiling_history_size=30 -3 # show session variables like 'profil%' -4 # select @@profiling -5 # create table t1 ( +1 # set session profiling_history_size=30 +2 # show session variables like 'profil%' +3 # select @@profiling +4 # create table t1 ( a int, b int ) -6 # insert into t1 values (1,1), (2,null), (3, 4) -7 # insert into t1 values (5,1), (6,null), (7, 4) -8 # insert into t1 values (1,1), (2,null), (3, 4) -9 # insert into t1 values (5,1), (6,null), (7, 4) -10 # select max(x) from (select sum(a) as x from t1 group by b) as teeone -11 # insert into t1 select * from t1 -12 # select count(*) from t1 +5 # insert into t1 values (1,1), (2,null), (3, 4) +6 # insert into t1 values (5,1), (6,null), (7, 4) +7 # insert into t1 values (1,1), (2,null), (3, 4) +8 # insert into t1 values (5,1), (6,null), (7, 4) +9 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +10 # insert into t1 select * from t1 +11 # select count(*) from t1 +12 # insert into t1 select * from t1 13 # insert into t1 select * from t1 14 # insert into t1 select * from t1 -15 # insert into t1 select * from t1 -16 # select count(*) from t1 +15 # select count(*) from t1 +16 # insert into t1 select * from t1 17 # insert into t1 select * from t1 18 # insert into t1 select * from t1 -19 # insert into t1 select * from t1 -20 # select count(*) from t1 -21 # select sum(a) from t1 -22 # select sum(a) from t1 group by b -23 # select sum(a) + sum(b) from t1 group by b -24 # select max(x) from (select sum(a) as x from t1 group by b) as teeone -25 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 +19 # select count(*) from t1 +20 # select sum(a) from t1 +21 # select sum(a) from t1 group by b +22 # select sum(a) + sum(b) from t1 group by b +23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 show profile for query 15; show profile cpu for query 15; show profile cpu, block io for query 15; @@ -131,36 +130,36 @@ insert into t2 values (1), (2), (3); insert into t3 values (1), (2), (3); show profiles; Query_ID Duration Query -11 # insert into t1 select * from t1 -12 # select count(*) from t1 +10 # insert into t1 select * from t1 +11 # select count(*) from t1 +12 # insert into t1 select * from t1 13 # insert into t1 select * from t1 14 # insert into t1 select * from t1 -15 # insert into t1 select * from t1 -16 # select count(*) from t1 +15 # select count(*) from t1 +16 # insert into t1 select * from t1 17 # insert into t1 select * from t1 18 # insert into t1 select * from t1 -19 # insert into t1 select * from t1 -20 # select count(*) from t1 -21 # select sum(a) from t1 -22 # select sum(a) from t1 group by b -23 # select sum(a) + sum(b) from t1 group by b -24 # select max(x) from (select sum(a) as x from t1 group by b) as teeone -25 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 -26 # select * from information_schema.profiling -27 # select query_id, state, duration from information_schema.profiling -28 # select query_id, sum(duration) from information_schema.profiling group by query_id -29 # select query_id, count(*) from information_schema.profiling group by query_id -30 # select sum(duration) from information_schema.profiling -31 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id -32 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling -33 # drop table if exists t1, t2, t3 -34 # SHOW WARNINGS -35 # create table t1 (id int ) -36 # create table t2 (id int not null) -37 # create table t3 (id int not null primary key) -38 # insert into t1 values (1), (2), (3) -39 # insert into t2 values (1), (2), (3) -40 # insert into t3 values (1), (2), (3) +19 # select count(*) from t1 +20 # select sum(a) from t1 +21 # select sum(a) from t1 group by b +22 # select sum(a) + sum(b) from t1 group by b +23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 +25 # select * from information_schema.profiling +26 # select query_id, state, duration from information_schema.profiling +27 # select query_id, sum(duration) from information_schema.profiling group by query_id +28 # select query_id, count(*) from information_schema.profiling group by query_id +29 # select sum(duration) from information_schema.profiling +30 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id +31 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling +32 # drop table if exists t1, t2, t3 +33 # SHOW WARNINGS +34 # create table t1 (id int ) +35 # create table t2 (id int not null) +36 # create table t3 (id int not null primary key) +37 # insert into t1 values (1), (2), (3) +38 # insert into t2 values (1), (2), (3) +39 # insert into t3 values (1), (2), (3) select * from t1; id 1 @@ -168,36 +167,36 @@ id 3 show profiles; Query_ID Duration Query -12 # select count(*) from t1 +11 # select count(*) from t1 +12 # insert into t1 select * from t1 13 # insert into t1 select * from t1 14 # insert into t1 select * from t1 -15 # insert into t1 select * from t1 -16 # select count(*) from t1 +15 # select count(*) from t1 +16 # insert into t1 select * from t1 17 # insert into t1 select * from t1 18 # insert into t1 select * from t1 -19 # insert into t1 select * from t1 -20 # select count(*) from t1 -21 # select sum(a) from t1 -22 # select sum(a) from t1 group by b -23 # select sum(a) + sum(b) from t1 group by b -24 # select max(x) from (select sum(a) as x from t1 group by b) as teeone -25 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 -26 # select * from information_schema.profiling -27 # select query_id, state, duration from information_schema.profiling -28 # select query_id, sum(duration) from information_schema.profiling group by query_id -29 # select query_id, count(*) from information_schema.profiling group by query_id -30 # select sum(duration) from information_schema.profiling -31 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id -32 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling -33 # drop table if exists t1, t2, t3 -34 # SHOW WARNINGS -35 # create table t1 (id int ) -36 # create table t2 (id int not null) -37 # create table t3 (id int not null primary key) -38 # insert into t1 values (1), (2), (3) -39 # insert into t2 values (1), (2), (3) -40 # insert into t3 values (1), (2), (3) -41 # select * from t1 +19 # select count(*) from t1 +20 # select sum(a) from t1 +21 # select sum(a) from t1 group by b +22 # select sum(a) + sum(b) from t1 group by b +23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 +25 # select * from information_schema.profiling +26 # select query_id, state, duration from information_schema.profiling +27 # select query_id, sum(duration) from information_schema.profiling group by query_id +28 # select query_id, count(*) from information_schema.profiling group by query_id +29 # select sum(duration) from information_schema.profiling +30 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id +31 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling +32 # drop table if exists t1, t2, t3 +33 # SHOW WARNINGS +34 # create table t1 (id int ) +35 # create table t2 (id int not null) +36 # create table t3 (id int not null primary key) +37 # insert into t1 values (1), (2), (3) +38 # insert into t2 values (1), (2), (3) +39 # insert into t3 values (1), (2), (3) +40 # select * from t1 This ^^ should end in "select * from t1;" delete from t1; insert into t1 values (1), (2), (3); @@ -212,72 +211,72 @@ id 3 show profiles; Query_ID Duration Query -16 # select count(*) from t1 +15 # select count(*) from t1 +16 # insert into t1 select * from t1 17 # insert into t1 select * from t1 18 # insert into t1 select * from t1 -19 # insert into t1 select * from t1 -20 # select count(*) from t1 -21 # select sum(a) from t1 -22 # select sum(a) from t1 group by b -23 # select sum(a) + sum(b) from t1 group by b -24 # select max(x) from (select sum(a) as x from t1 group by b) as teeone -25 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 -26 # select * from information_schema.profiling -27 # select query_id, state, duration from information_schema.profiling -28 # select query_id, sum(duration) from information_schema.profiling group by query_id -29 # select query_id, count(*) from information_schema.profiling group by query_id -30 # select sum(duration) from information_schema.profiling -31 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id -32 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling -33 # drop table if exists t1, t2, t3 -34 # SHOW WARNINGS -35 # create table t1 (id int ) -36 # create table t2 (id int not null) -37 # create table t3 (id int not null primary key) -38 # insert into t1 values (1), (2), (3) -39 # insert into t2 values (1), (2), (3) -40 # insert into t3 values (1), (2), (3) -41 # select * from t1 -42 # delete from t1 +19 # select count(*) from t1 +20 # select sum(a) from t1 +21 # select sum(a) from t1 group by b +22 # select sum(a) + sum(b) from t1 group by b +23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 +25 # select * from information_schema.profiling +26 # select query_id, state, duration from information_schema.profiling +27 # select query_id, sum(duration) from information_schema.profiling group by query_id +28 # select query_id, count(*) from information_schema.profiling group by query_id +29 # select sum(duration) from information_schema.profiling +30 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id +31 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling +32 # drop table if exists t1, t2, t3 +33 # SHOW WARNINGS +34 # create table t1 (id int ) +35 # create table t2 (id int not null) +36 # create table t3 (id int not null primary key) +37 # insert into t1 values (1), (2), (3) +38 # insert into t2 values (1), (2), (3) +39 # insert into t3 values (1), (2), (3) +40 # select * from t1 +41 # delete from t1 +42 # insert into t1 values (1), (2), (3) 43 # insert into t1 values (1), (2), (3) -44 # insert into t1 values (1), (2), (3) -45 # select * from t1 +44 # select * from t1 set session profiling = OFF; select sum(id) from t1; sum(id) 12 show profiles; Query_ID Duration Query -16 # select count(*) from t1 +15 # select count(*) from t1 +16 # insert into t1 select * from t1 17 # insert into t1 select * from t1 18 # insert into t1 select * from t1 -19 # insert into t1 select * from t1 -20 # select count(*) from t1 -21 # select sum(a) from t1 -22 # select sum(a) from t1 group by b -23 # select sum(a) + sum(b) from t1 group by b -24 # select max(x) from (select sum(a) as x from t1 group by b) as teeone -25 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 -26 # select * from information_schema.profiling -27 # select query_id, state, duration from information_schema.profiling -28 # select query_id, sum(duration) from information_schema.profiling group by query_id -29 # select query_id, count(*) from information_schema.profiling group by query_id -30 # select sum(duration) from information_schema.profiling -31 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id -32 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling -33 # drop table if exists t1, t2, t3 -34 # SHOW WARNINGS -35 # create table t1 (id int ) -36 # create table t2 (id int not null) -37 # create table t3 (id int not null primary key) -38 # insert into t1 values (1), (2), (3) -39 # insert into t2 values (1), (2), (3) -40 # insert into t3 values (1), (2), (3) -41 # select * from t1 -42 # delete from t1 +19 # select count(*) from t1 +20 # select sum(a) from t1 +21 # select sum(a) from t1 group by b +22 # select sum(a) + sum(b) from t1 group by b +23 # select max(x) from (select sum(a) as x from t1 group by b) as teeone +24 # select '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345678900123456789001234567890012345 +25 # select * from information_schema.profiling +26 # select query_id, state, duration from information_schema.profiling +27 # select query_id, sum(duration) from information_schema.profiling group by query_id +28 # select query_id, count(*) from information_schema.profiling group by query_id +29 # select sum(duration) from information_schema.profiling +30 # select query_id, count(*), sum(duration) from information_schema.profiling group by query_id +31 # select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling +32 # drop table if exists t1, t2, t3 +33 # SHOW WARNINGS +34 # create table t1 (id int ) +35 # create table t2 (id int not null) +36 # create table t3 (id int not null primary key) +37 # insert into t1 values (1), (2), (3) +38 # insert into t2 values (1), (2), (3) +39 # insert into t3 values (1), (2), (3) +40 # select * from t1 +41 # delete from t1 +42 # insert into t1 values (1), (2), (3) 43 # insert into t1 values (1), (2), (3) -44 # insert into t1 values (1), (2), (3) -45 # select * from t1 +44 # select * from t1 set session profiling = ON; select @@profiling; @@profiling -- cgit v1.2.1 From 17a57e557cf9fc24c9457cdee32b16ff3154b4d5 Mon Sep 17 00:00:00 2001 From: "cmiller@zippy.cornsilk.net" <> Date: Tue, 10 Apr 2007 16:47:07 -0400 Subject: Use platform-independent mysqltest language. --- mysql-test/t/mysqlcheck.test | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test index eb732a05909..5efe6ade6bb 100644 --- a/mysql-test/t/mysqlcheck.test +++ b/mysql-test/t/mysqlcheck.test @@ -39,8 +39,9 @@ use d_bug25347; create table t_bug25347 (a int); create view v_bug25347 as select * from t_bug25347; --echo removing and creating ---exec rm $MYSQLTEST_VARDIR/master-data/d_bug25347/t_bug25347.MYI; ---exec touch $MYSQLTEST_VARDIR/master-data/d_bug25347/t_bug25347.MYI; +--remove_file $MYSQLTEST_VARDIR/master-data/d_bug25347/t_bug25347.MYI +--write_file $MYSQLTEST_VARDIR/master-data/d_bug25347/t_bug25347.MYI EOF +EOF --exec $MYSQL_CHECK --repair --databases d_bug25347 drop view v_bug25347; drop table t_bug25347; -- cgit v1.2.1 From bbd8b850cd61b1326849c45153a2a0c694cb12b1 Mon Sep 17 00:00:00 2001 From: "cmiller@zippy.cornsilk.net" <> Date: Mon, 16 Apr 2007 15:00:16 -0400 Subject: Bug#25347: mysqlcheck -A -r doesn't repair table marked as crashed Additional changes to test: "flush tables" so that Windows releases the files. --- mysql-test/r/mysqlcheck.result | 27 ++++++++++++++++++++++++++- mysql-test/t/mysqlcheck.test | 8 ++++++++ 2 files changed, 34 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index 81a30267e04..1c42b0060f7 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -45,8 +45,33 @@ create database d_bug25347; use d_bug25347; create table t_bug25347 (a int); create view v_bug25347 as select * from t_bug25347; +insert into t_bug25347 values (1),(2),(3); +flush tables; removing and creating -d_bug25347.t_bug25347 OK +d_bug25347.t_bug25347 +error : Incorrect file format 't_bug25347' +insert into t_bug25347 values (4),(5),(6); +ERROR HY000: Incorrect file format 't_bug25347' +d_bug25347.t_bug25347 +warning : Number of rows changed from 0 to 3 +status : OK +insert into t_bug25347 values (7),(8),(9); +select * from t_bug25347; +a +1 +2 +3 +7 +8 +9 +select * from v_bug25347; +a +1 +2 +3 +7 +8 +9 drop view v_bug25347; drop table t_bug25347; drop database d_bug25347; diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test index 5efe6ade6bb..655ff39001c 100644 --- a/mysql-test/t/mysqlcheck.test +++ b/mysql-test/t/mysqlcheck.test @@ -38,11 +38,19 @@ create database d_bug25347; use d_bug25347; create table t_bug25347 (a int); create view v_bug25347 as select * from t_bug25347; +insert into t_bug25347 values (1),(2),(3); +flush tables; --echo removing and creating --remove_file $MYSQLTEST_VARDIR/master-data/d_bug25347/t_bug25347.MYI --write_file $MYSQLTEST_VARDIR/master-data/d_bug25347/t_bug25347.MYI EOF EOF --exec $MYSQL_CHECK --repair --databases d_bug25347 +--error 130 +insert into t_bug25347 values (4),(5),(6); +--exec $MYSQL_CHECK --repair --use-frm --databases d_bug25347 +insert into t_bug25347 values (7),(8),(9); +select * from t_bug25347; +select * from v_bug25347; drop view v_bug25347; drop table t_bug25347; drop database d_bug25347; -- cgit v1.2.1 From 01f572052d0d50a06745d5b6d96e20db81530d7b Mon Sep 17 00:00:00 2001 From: "cmiller@zippy.cornsilk.net" <> Date: Mon, 2 Jul 2007 07:27:39 -0400 Subject: Unify profiling SHOW code and INFORMATION_SCHEMA code for profiling. Also, Bug#26938: profiling client hang if used before enabled In the SHOW command, not sending header data because we had no rows to send was a protocol violation. Porting the SHOW PROFILE command to use the Information Schema table avoids that problem. --- mysql-test/r/profiling.result | 4 ++++ mysql-test/t/profiling.test | 5 +++++ 2 files changed, 9 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/profiling.result b/mysql-test/r/profiling.result index 9f478d68b8d..b99870e3564 100644 --- a/mysql-test/r/profiling.result +++ b/mysql-test/r/profiling.result @@ -1,3 +1,7 @@ +show profiles; +Query_ID Duration Query +show profile all; +Status Duration CPU_user CPU_system Context_voluntary Context_involuntary Block_ops_in Block_ops_out Messages_sent Messages_received Page_faults_major Page_faults_minor Swaps Source_function Source_file Source_line show session variables like 'profil%'; Variable_name Value profiling OFF diff --git a/mysql-test/t/profiling.test b/mysql-test/t/profiling.test index fd7562f6f16..960aa496731 100644 --- a/mysql-test/t/profiling.test +++ b/mysql-test/t/profiling.test @@ -1,3 +1,8 @@ +# Verify that the protocol isn't violated if we ask for profiling info +# before profiling has recorded anything. +show profiles; +show profile all; + # default is OFF show session variables like 'profil%'; select @@profiling; -- cgit v1.2.1 From 22afbc0ab05e3184759b29112d6a4c04dc638509 Mon Sep 17 00:00:00 2001 From: "cmiller@zippy.cornsilk.net" <> Date: Mon, 2 Jul 2007 12:29:07 -0400 Subject: Merge fix-up. --- mysql-test/r/information_schema.result | 14 +++++++------- 1 file changed, 7 insertions(+), 7 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index aff0ba94f13..62b81ea260c 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1319,6 +1319,13 @@ TABLE_PRIVILEGES information_schema.TABLE_PRIVILEGES 1 TRIGGERS information_schema.TRIGGERS 1 USER_PRIVILEGES information_schema.USER_PRIVILEGES 1 VIEWS information_schema.VIEWS 1 +show global status like "Uptime_%"; +Variable_name Value +Uptime_since_flush_status # +flush status; +show global status like "Uptime_%"; +Variable_name Value +Uptime_since_flush_status # create table t1(f1 int); create view v1 as select f1+1 as a from t1; create table t2 (f1 int, f2 int); @@ -1330,10 +1337,3 @@ v2 YES delete from v1; drop view v1,v2; drop table t1,t2; -show global status like "Uptime_%"; -Variable_name Value -Uptime_since_flush_status # -flush status; -show global status like "Uptime_%"; -Variable_name Value -Uptime_since_flush_status # -- cgit v1.2.1 From ddb1443b074b65f955240bb345331f0adae92e3c Mon Sep 17 00:00:00 2001 From: "cmiller@zippy.cornsilk.net" <> Date: Wed, 14 Nov 2007 15:11:58 -0500 Subject: Push history-limiting code until after the code that adds new history entries. Lazy deletion isn't smart or useful here. Backport from 5.1 . --- mysql-test/r/profiling.result | 47 ++++++++++++++++++++++++++++++ mysql-test/t/profiling.test | 66 ++++++++++++++++++++++++++++++++++++++++++- 2 files changed, 112 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/profiling.result b/mysql-test/r/profiling.result index b99870e3564..a9ada576b4b 100644 --- a/mysql-test/r/profiling.result +++ b/mysql-test/r/profiling.result @@ -300,6 +300,53 @@ id select @@profiling; @@profiling 1 +set session profiling = OFF; +drop table if exists profile_log; +Warnings: +Note 1051 Unknown table 'profile_log' +create table profile_log (how_many int); +drop procedure if exists p1; +drop procedure if exists p2; +drop procedure if exists p3; +create procedure p1 () +modifies sql data +begin +set profiling = ON; +select 'This p1 should show up in profiling'; +insert into profile_log select count(*) from information_schema.profiling; +end// +create procedure p2() +deterministic +begin +set profiling = ON; +call p1(); +select 'This p2 should show up in profiling'; +end// +create procedure p3 () +reads sql data +begin +set profiling = ON; +select 'This p3 should show up in profiling'; +show profile; +end// +first call to p1 +call p1; +select * from profile_log; +second call to p1 +call p1; +select * from profile_log; +third call to p1 +call p1; +select * from profile_log; +set session profiling = OFF; +call p2; +set session profiling = OFF; +call p3; +show profiles; +drop procedure if exists p1; +drop procedure if exists p2; +drop procedure if exists p3; +drop table if exists profile_log; set session profiling = ON; drop table if exists t2; create table t2 (id int not null); diff --git a/mysql-test/t/profiling.test b/mysql-test/t/profiling.test index 960aa496731..ffd38caf781 100644 --- a/mysql-test/t/profiling.test +++ b/mysql-test/t/profiling.test @@ -136,7 +136,7 @@ show profiles; ## Verify that the various juggling of THD contexts doesn't affect profiling. -## Functions +## Functions and procedures set session profiling = ON; select @@profiling; create function f1() returns varchar(50) return 'hello'; @@ -144,6 +144,64 @@ select @@profiling; select * from t1 where id <> f1(); select @@profiling; +set session profiling = OFF; +drop table if exists profile_log; +create table profile_log (how_many int); + +--disable_warnings +drop procedure if exists p1; +drop procedure if exists p2; +drop procedure if exists p3; +--enable_warnings + +delimiter //; +create procedure p1 () + modifies sql data +begin + set profiling = ON; + select 'This p1 should show up in profiling'; + insert into profile_log select count(*) from information_schema.profiling; +end// +create procedure p2() + deterministic +begin + set profiling = ON; + call p1(); + select 'This p2 should show up in profiling'; +end// +create procedure p3 () + reads sql data +begin + set profiling = ON; + select 'This p3 should show up in profiling'; + show profile; +end// +delimiter ;// + +--disable_result_log +--echo first call to p1 +call p1; +select * from profile_log; +--echo second call to p1 +call p1; +select * from profile_log; +--echo third call to p1 +call p1; +select * from profile_log; +set session profiling = OFF; +call p2; +set session profiling = OFF; +call p3; + +--replace_column 1 # 2 # +show profiles; +--enable_result_log + +drop procedure if exists p1; +drop procedure if exists p2; +drop procedure if exists p3; +drop table if exists profile_log; + ## Triggers set session profiling = ON; drop table if exists t2; @@ -193,6 +251,12 @@ drop table if exists t1, t2, t3; drop view if exists v1; drop function if exists f1; +## Multiple queries in one packet. Combo statements don't work with ps-proto. +#--eval select 1; select 2; select 3; +## two continuations, one starting +#select state from information_schema.profiling where seq=1 order by query_id desc limit 3; + + ## last thing in the file set session profiling = OFF; -- cgit v1.2.1 From 73e7b036c13bcf7fac59054faca0027ae7e70374 Mon Sep 17 00:00:00 2001 From: "joerg/mysqldev@mysql.com/production.mysql.com" <> Date: Thu, 15 Nov 2007 10:55:47 +0100 Subject: BUG#32111 - Security Breach via DATA/INDEX DIRECORY and RENAME TABLE RENAME TABLE against a table with DATA/INDEX DIRECTORY overwrites the file to which the symlink points. This is security issue, because it is possible to create a table with some name in some non-system database and set DATA/INDEX DIRECTORY to mysql system database. Renaming this table to one of mysql system tables (e.g. user, host) would overwrite the system table. Return an error when the file to which the symlink points exist. (This is a copy of changeset 2007/11/06 18:09:33+04:00 svoj@mysql.com and its merge changesets on the way from 4.0 up to 5.0) --- mysql-test/r/symlink.result | 6 ++++++ mysql-test/t/symlink.test | 12 ++++++++++++ 2 files changed, 18 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 18299bf4298..833adbeb851 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -99,6 +99,12 @@ t1 CREATE TABLE `t1` ( `b` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +CREATE TABLE t1(a INT) +DATA DIRECTORY='TEST_DIR/master-data/mysql' +INDEX DIRECTORY='TEST_DIR/master-data/mysql'; +RENAME TABLE t1 TO user; +ERROR HY000: Can't create/write to file 'TEST_DIR/master-data/mysql/user.MYI' (Errcode: 17) +DROP TABLE t1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/mysql-test/t/symlink.test b/mysql-test/t/symlink.test index 8c67a4c1048..7eaeaa00c0a 100644 --- a/mysql-test/t/symlink.test +++ b/mysql-test/t/symlink.test @@ -124,6 +124,18 @@ enable_query_log; show create table t1; drop table t1; +# +# BUG#32111 - Security Breach via DATA/INDEX DIRECORY and RENAME TABLE +# +--replace_result $MYSQLTEST_VARDIR TEST_DIR +eval CREATE TABLE t1(a INT) +DATA DIRECTORY='$MYSQLTEST_VARDIR/master-data/mysql' +INDEX DIRECTORY='$MYSQLTEST_VARDIR/master-data/mysql'; +--replace_result $MYSQLTEST_VARDIR TEST_DIR +--error 1 +RENAME TABLE t1 TO user; +DROP TABLE t1; + # # Test specifying DATA DIRECTORY that is the same as what would normally # have been chosen. (Bug #8707) -- cgit v1.2.1 From 5e1d36e439a2855c5d84da40754457ea8760b85a Mon Sep 17 00:00:00 2001 From: "gluh@mysql.com/eagle.(none)" <> Date: Thu, 22 Nov 2007 14:10:18 +0400 Subject: Bug#32458 Test "mysqlcheck" fails, because "mysqlcheck --repair" changed output format update result file because of Bug#26976 changes --- mysql-test/r/mysqlcheck.result | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index 1c42b0060f7..207b6ec88df 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -49,7 +49,8 @@ insert into t_bug25347 values (1),(2),(3); flush tables; removing and creating d_bug25347.t_bug25347 -error : Incorrect file format 't_bug25347' +Error : Incorrect file format 't_bug25347' +error : Corrupt insert into t_bug25347 values (4),(5),(6); ERROR HY000: Incorrect file format 't_bug25347' d_bug25347.t_bug25347 -- cgit v1.2.1 From 5da0c9ae6a1baaf03a4bc4094dee4e984992f98a Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Thu, 17 Jul 2008 20:19:13 -0300 Subject: Bug#33812: mysql client incorrectly parsing DELIMITER Revert fix for this bug as it introduced a regression reported in Bug#38158. --- mysql-test/r/mysql.result | 2 -- mysql-test/t/mysql_delimiter.sql | 6 ------ 2 files changed, 8 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/mysql.result b/mysql-test/r/mysql.result index bc50c686ac6..eded1a3fc3b 100644 --- a/mysql-test/r/mysql.result +++ b/mysql-test/r/mysql.result @@ -38,8 +38,6 @@ t2 t3 Tables_in_test t1 -delimiter -1 _ Test delimiter : from command line a diff --git a/mysql-test/t/mysql_delimiter.sql b/mysql-test/t/mysql_delimiter.sql index 917401275a2..533ac2ce093 100644 --- a/mysql-test/t/mysql_delimiter.sql +++ b/mysql-test/t/mysql_delimiter.sql @@ -60,12 +60,6 @@ use test// show tables// delimiter ; # Reset delimiter -# -# Bug #33812: mysql client incorrectly parsing DELIMITER -# -select a as delimiter from t1 -delimiter ; # Reset delimiter - # # Bug #36244: MySQL CLI doesn't recognize standalone -- as comment # before DELIMITER statement -- cgit v1.2.1 From 598975ec966496e65130cb8ec68eaab9010bb562 Mon Sep 17 00:00:00 2001 From: Alexey Botchkov Date: Thu, 31 Jul 2008 14:42:44 +0500 Subject: Buq#32167 another privilege bypass with DATA/INDEX DIRECTORY. test_if_data_home_dir fixed to look into real path. Checks added to mi_open for symlinks into data home directory. per-file messages: include/my_sys.h Bug#32167 another privilege bypass with DATA/INDEX DIRECTORY. my_is_symlink interface added mysql-test/r/udf.result test result fixed (not related to #32167) mysys/my_symlink.c my_is_symlink() implementsd my_realpath() now returns the 'realpath' even if a file isn't a symlink --- mysql-test/r/udf.result | 6 ++++++ 1 file changed, 6 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result index a79be1c3189..92185962d1f 100644 --- a/mysql-test/r/udf.result +++ b/mysql-test/r/udf.result @@ -1,5 +1,7 @@ drop table if exists t1; CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; +Warnings: +Warning 1105 plugin_dir was not specified CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB"; CREATE FUNCTION myfunc_nonexist RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB"; ERROR HY000: Can't find function 'myfunc_nonexist' in library @@ -197,6 +199,8 @@ DROP FUNCTION avgcost; select * from mysql.func; name ret dl type CREATE FUNCTION is_const RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; +Warnings: +Warning 1105 plugin_dir was not specified select IS_const(3); IS_const(3) const @@ -206,6 +210,8 @@ name ret dl type select is_const(3); ERROR 42000: FUNCTION test.is_const does not exist CREATE FUNCTION is_const RETURNS STRING SONAME "UDF_EXAMPLE_LIB"; +Warnings: +Warning 1105 plugin_dir was not specified select is_const(3) as const, is_const(3.14) as const, -- cgit v1.2.1 From 9d58239dea8c615a10a0939e34d052b22808e75f Mon Sep 17 00:00:00 2001 From: Chad MILLER Date: Tue, 5 May 2009 17:03:23 -0400 Subject: Pull 5.1 treatment of community features into 5.0. --- mysql-test/include/have_community_features.inc | 4 ++++ mysql-test/include/have_profiling.inc | 4 ++++ mysql-test/r/have_community_features.require | 2 ++ mysql-test/r/have_profiling.require | 2 ++ mysql-test/t/profiling.test | 2 ++ 5 files changed, 14 insertions(+) create mode 100644 mysql-test/include/have_community_features.inc create mode 100644 mysql-test/include/have_profiling.inc create mode 100644 mysql-test/r/have_community_features.require create mode 100644 mysql-test/r/have_profiling.require (limited to 'mysql-test') diff --git a/mysql-test/include/have_community_features.inc b/mysql-test/include/have_community_features.inc new file mode 100644 index 00000000000..66697d8dd00 --- /dev/null +++ b/mysql-test/include/have_community_features.inc @@ -0,0 +1,4 @@ +--require r/have_community_features.require +--disable_query_log +show variables like 'have_community_features'; +--enable_query_log diff --git a/mysql-test/include/have_profiling.inc b/mysql-test/include/have_profiling.inc new file mode 100644 index 00000000000..48f6668ff92 --- /dev/null +++ b/mysql-test/include/have_profiling.inc @@ -0,0 +1,4 @@ +--require r/have_profiling.require +--disable_query_log +show variables like 'have_profiling'; +--enable_query_log diff --git a/mysql-test/r/have_community_features.require b/mysql-test/r/have_community_features.require new file mode 100644 index 00000000000..9233bba91e1 --- /dev/null +++ b/mysql-test/r/have_community_features.require @@ -0,0 +1,2 @@ +Variable_name Value +have_community_features YES diff --git a/mysql-test/r/have_profiling.require b/mysql-test/r/have_profiling.require new file mode 100644 index 00000000000..54caeba1dae --- /dev/null +++ b/mysql-test/r/have_profiling.require @@ -0,0 +1,2 @@ +Variable_name Value +have_profiling YES diff --git a/mysql-test/t/profiling.test b/mysql-test/t/profiling.test index ffd38caf781..29b694fa0bd 100644 --- a/mysql-test/t/profiling.test +++ b/mysql-test/t/profiling.test @@ -1,3 +1,5 @@ +--source include/have_profiling.inc + # Verify that the protocol isn't violated if we ask for profiling info # before profiling has recorded anything. show profiles; -- cgit v1.2.1