diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/include/have_community_features.inc | 4 | ||||
| -rw-r--r-- | mysql-test/r/have_community_features.require | 2 | ||||
| -rw-r--r-- | mysql-test/r/information_schema.result | 8 | ||||
| -rw-r--r-- | mysql-test/r/information_schema_db.result | 1 | ||||
| -rw-r--r-- | mysql-test/r/mysqlcheck.result | 27 | ||||
| -rw-r--r-- | mysql-test/r/mysqlshow.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/not_embedded_server.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/profiling.result | 415 | ||||
| -rw-r--r-- | mysql-test/r/variables+c.result | 7 | ||||
| -rw-r--r-- | mysql-test/r/variables.result | 2 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/r/rpl_invoked_features.result | 24 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/t/rpl_invoked_features.test | 16 | ||||
| -rw-r--r-- | mysql-test/t/mysqlcheck.test | 9 | ||||
| -rw-r--r-- | mysql-test/t/profiling.test | 266 | ||||
| -rw-r--r-- | mysql-test/t/variables+c.test | 11 |
15 files changed, 770 insertions, 26 deletions
diff --git a/mysql-test/include/have_community_features.inc b/mysql-test/include/have_community_features.inc new file mode 100644 index 00000000000..01ed6142500 --- /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/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/information_schema.result b/mysql-test/r/information_schema.result index dc76a2693be..0416733255f 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -50,6 +50,7 @@ KEY_COLUMN_USAGE PARTITIONS PLUGINS PROCESSLIST +PROFILING REFERENTIAL_CONSTRAINTS ROUTINES SCHEMATA @@ -851,7 +852,7 @@ delete from mysql.db where user='mysqltest_4'; flush privileges; SELECT table_schema, count(*) FROM information_schema.TABLES where table_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status' GROUP BY TABLE_SCHEMA; table_schema count(*) -information_schema 27 +information_schema 28 mysql 22 create table t1 (i int, j int); create trigger trg1 before insert on t1 for each row @@ -1147,7 +1148,7 @@ group by column_type order by num; column_type group_concat(table_schema, '.', table_name) num varchar(27) information_schema.COLUMNS 1 varchar(7) information_schema.ROUTINES,information_schema.VIEWS 2 -varchar(20) information_schema.FILES,information_schema.FILES,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS 5 +varchar(20) information_schema.FILES,information_schema.FILES,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PROFILING 6 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 @@ -1252,6 +1253,7 @@ KEY_COLUMN_USAGE CONSTRAINT_SCHEMA PARTITIONS TABLE_SCHEMA PLUGINS PLUGIN_NAME PROCESSLIST ID +PROFILING QUERY_ID REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA ROUTINES ROUTINE_SCHEMA SCHEMATA SCHEMA_NAME @@ -1294,6 +1296,7 @@ KEY_COLUMN_USAGE CONSTRAINT_SCHEMA PARTITIONS TABLE_SCHEMA PLUGINS PLUGIN_NAME PROCESSLIST ID +PROFILING QUERY_ID REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA ROUTINES ROUTINE_SCHEMA SCHEMATA SCHEMA_NAME @@ -1387,6 +1390,7 @@ KEY_COLUMN_USAGE information_schema.KEY_COLUMN_USAGE 1 PARTITIONS information_schema.PARTITIONS 1 PLUGINS information_schema.PLUGINS 1 PROCESSLIST information_schema.PROCESSLIST 1 +PROFILING information_schema.PROFILING 1 REFERENTIAL_CONSTRAINTS information_schema.REFERENTIAL_CONSTRAINTS 1 ROUTINES information_schema.ROUTINES 1 SCHEMATA information_schema.SCHEMATA 1 diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index 2da4e305b01..cb56fb3d5cc 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -19,6 +19,7 @@ KEY_COLUMN_USAGE PARTITIONS PLUGINS PROCESSLIST +PROFILING REFERENTIAL_CONSTRAINTS ROUTINES SCHEMATA diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index 913e52f0249..b8fdd04d24a 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -66,9 +66,34 @@ 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' +error : Corrupt +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/r/mysqlshow.result b/mysql-test/r/mysqlshow.result index 14d8e4f464b..42081c309d0 100644 --- a/mysql-test/r/mysqlshow.result +++ b/mysql-test/r/mysqlshow.result @@ -93,6 +93,7 @@ Database: information_schema | PARTITIONS | | PLUGINS | | PROCESSLIST | +| PROFILING | | REFERENTIAL_CONSTRAINTS | | ROUTINES | | SCHEMATA | @@ -125,6 +126,7 @@ Database: INFORMATION_SCHEMA | PARTITIONS | | PLUGINS | | PROCESSLIST | +| PROFILING | | REFERENTIAL_CONSTRAINTS | | ROUTINES | | SCHEMATA | diff --git a/mysql-test/r/not_embedded_server.result b/mysql-test/r/not_embedded_server.result index 63351d187b6..f2ffe28895d 100644 --- a/mysql-test/r/not_embedded_server.result +++ b/mysql-test/r/not_embedded_server.result @@ -1,5 +1,5 @@ prepare stmt1 from ' SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND!=\'Daemon\' '; execute stmt1; ID USER HOST DB COMMAND TIME STATE INFO -number root localhost test Query time preparing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND!='Daemon' +number root localhost test Query time executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND!='Daemon' deallocate prepare stmt1; diff --git a/mysql-test/r/profiling.result b/mysql-test/r/profiling.result new file mode 100644 index 00000000000..a9ada576b4b --- /dev/null +++ b/mysql-test/r/profiling.result @@ -0,0 +1,415 @@ +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 +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 +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 +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 = 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 = 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); +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/r/variables+c.result b/mysql-test/r/variables+c.result new file mode 100644 index 00000000000..7a2976d1267 --- /dev/null +++ b/mysql-test/r/variables+c.result @@ -0,0 +1,7 @@ +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/r/variables.result b/mysql-test/r/variables.result index 658a3ffa7a1..e5c3c860c93 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -223,7 +223,7 @@ set GLOBAL myisam_max_sort_file_size=default; show global variables like 'myisam_max_sort_file_size'; Variable_name Value myisam_max_sort_file_size FILE_SIZE -select * from information_schema.session_variables where variable_name like 'myisam_max_sort_file_size'; +select * from information_schema.global_variables where variable_name like 'myisam_max_sort_file_size'; VARIABLE_NAME VARIABLE_VALUE MYISAM_MAX_SORT_FILE_SIZE FILE_SIZE set global net_retry_count=10, session net_retry_count=10; diff --git a/mysql-test/suite/rpl/r/rpl_invoked_features.result b/mysql-test/suite/rpl/r/rpl_invoked_features.result index 502bb040218..18434d8087e 100644 --- a/mysql-test/suite/rpl/r/rpl_invoked_features.result +++ b/mysql-test/suite/rpl/r/rpl_invoked_features.result @@ -116,23 +116,23 @@ t12 t13 t2 t3 -SELECT table_name FROM information_schema.views WHERE table_schema='test'; +SELECT table_name FROM information_schema.views WHERE table_schema='test' order by table_name; table_name v1 v11 -SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test'; +SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test' order by trigger_name; trigger_name event_manipulation event_object_table -t1_tr1 INSERT t1 -t1_tr2 UPDATE t1 t11_tr1 INSERT t11 t11_tr2 UPDATE t11 -SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test'; +t1_tr1 INSERT t1 +t1_tr2 UPDATE t1 +SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test' order by routine_name; routine_type routine_name FUNCTION f1 FUNCTION f2 PROCEDURE p1 PROCEDURE p11 -SELECT event_name, status FROM information_schema.events WHERE event_schema='test'; +SELECT event_name, status FROM information_schema.events WHERE event_schema='test' order by event_name; event_name status e1 DISABLED e11 DISABLED @@ -276,23 +276,23 @@ t12 t13 t2 t3 -SELECT table_name FROM information_schema.views WHERE table_schema='test'; +SELECT table_name FROM information_schema.views WHERE table_schema='test' order by table_name; table_name v1 v11 -SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test'; +SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test' order by trigger_name; trigger_name event_manipulation event_object_table -t1_tr1 INSERT t1 -t1_tr2 UPDATE t1 t11_tr1 INSERT t11 t11_tr2 UPDATE t11 -SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test'; +t1_tr1 INSERT t1 +t1_tr2 UPDATE t1 +SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test' order by routine_name; routine_type routine_name FUNCTION f1 FUNCTION f2 PROCEDURE p1 PROCEDURE p11 -SELECT event_name, status FROM information_schema.events WHERE event_schema='test'; +SELECT event_name, status FROM information_schema.events WHERE event_schema='test' order by event_name; event_name status e1 SLAVESIDE_DISABLED e11 SLAVESIDE_DISABLED diff --git a/mysql-test/suite/rpl/t/rpl_invoked_features.test b/mysql-test/suite/rpl/t/rpl_invoked_features.test index 2e69c0fabd9..ab996840e97 100644 --- a/mysql-test/suite/rpl/t/rpl_invoked_features.test +++ b/mysql-test/suite/rpl/t/rpl_invoked_features.test @@ -202,10 +202,10 @@ SET GLOBAL EVENT_SCHEDULER = off; # Check original objects --echo SHOW TABLES LIKE 't%'; -SELECT table_name FROM information_schema.views WHERE table_schema='test'; -SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test'; -SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test'; -SELECT event_name, status FROM information_schema.events WHERE event_schema='test'; +SELECT table_name FROM information_schema.views WHERE table_schema='test' order by table_name; +SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test' order by trigger_name; +SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test' order by routine_name; +SELECT event_name, status FROM information_schema.events WHERE event_schema='test' order by event_name; # Check original data --echo @@ -229,10 +229,10 @@ SELECT a,b FROM v11 ORDER BY a; # Check replicated objects --echo SHOW TABLES LIKE 't%'; -SELECT table_name FROM information_schema.views WHERE table_schema='test'; -SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test'; -SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test'; -SELECT event_name, status FROM information_schema.events WHERE event_schema='test'; +SELECT table_name FROM information_schema.views WHERE table_schema='test' order by table_name; +SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test' order by trigger_name; +SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test' order by routine_name; +SELECT event_name, status FROM information_schema.events WHERE event_schema='test' order by event_name; # Check replicated data --echo diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test index f1031c86c5a..ba13a315cb8 100644 --- a/mysql-test/t/mysqlcheck.test +++ b/mysql-test/t/mysqlcheck.test @@ -53,12 +53,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 ---exec $MYSQL_CHECK --repair --databases --use-frm d_bug25347 +--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; diff --git a/mysql-test/t/profiling.test b/mysql-test/t/profiling.test new file mode 100644 index 00000000000..5cb3f66aa2b --- /dev/null +++ b/mysql-test/t/profiling.test @@ -0,0 +1,266 @@ +--source include/have_community_features.inc + +# 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; + +# 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 and procedures +set session profiling = ON; +select @@profiling; +create function f1() returns varchar(50) return 'hello'; +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; +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; + +## 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; + +## +--echo End of 5.0 tests diff --git a/mysql-test/t/variables+c.test b/mysql-test/t/variables+c.test new file mode 100644 index 00000000000..0092d34133d --- /dev/null +++ b/mysql-test/t/variables+c.test @@ -0,0 +1,11 @@ +--source include/have_community_features.inc + +# +# 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 + |