diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/include/profiling.inc | 4 | ||||
| -rw-r--r-- | mysql-test/r/information_schema.result | 15 | ||||
| -rw-r--r-- | mysql-test/r/information_schema_db.result | 1 | ||||
| -rw-r--r-- | mysql-test/r/mysqlcheck.result | 35 | ||||
| -rw-r--r-- | mysql-test/r/mysqlshow.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/profiling.require | 2 | ||||
| -rw-r--r-- | mysql-test/r/profiling.result | 364 | ||||
| -rw-r--r-- | mysql-test/t/information_schema.test | 8 | ||||
| -rw-r--r-- | mysql-test/t/mysqlcheck.test | 25 | ||||
| -rw-r--r-- | mysql-test/t/profiling.test | 197 |
10 files changed, 651 insertions, 2 deletions
diff --git a/mysql-test/include/profiling.inc b/mysql-test/include/profiling.inc new file mode 100644 index 00000000000..bc2228eaa10 --- /dev/null +++ b/mysql-test/include/profiling.inc @@ -0,0 +1,4 @@ +-- require r/profiling.require +disable_query_log; +show variables like "profiling"; +enable_query_log; diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 078946b4d81..d8b5c79c343 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 @@ -852,7 +853,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 @@ -1146,7 +1147,7 @@ table_schema='information_schema' and group by column_type order by num; column_type group_concat(table_schema, '.', table_name) num varchar(7) information_schema.ROUTINES,information_schema.VIEWS 2 -varchar(20) information_schema.COLUMNS,information_schema.FILES,information_schema.FILES,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS 6 +varchar(20) information_schema.COLUMNS,information_schema.FILES,information_schema.FILES,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PROFILING 7 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 @@ -1251,6 +1252,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 @@ -1293,6 +1295,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 @@ -1386,6 +1389,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 @@ -1399,6 +1403,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 # End of 5.0 tests. select * from information_schema.engines WHERE ENGINE="MyISAM"; ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index 94ebc213122..c0708694178 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 b8ada0adff9..1d7ea8590dd 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -57,4 +57,39 @@ 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; +insert into t_bug25347 values (1),(2),(3); +flush tables; +removing and creating +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; +use test; End of 5.0 tests 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/profiling.require b/mysql-test/r/profiling.require new file mode 100644 index 00000000000..a5f2b71f22a --- /dev/null +++ b/mysql-test/r/profiling.require @@ -0,0 +1,2 @@ +Variable_name Value +profiling OFF diff --git a/mysql-test/r/profiling.result b/mysql-test/r/profiling.result new file mode 100644 index 00000000000..9f478d68b8d --- /dev/null +++ b/mysql-test/r/profiling.result @@ -0,0 +1,364 @@ +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 = 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/information_schema.test b/mysql-test/t/information_schema.test index a49044e63c1..f9a2be499cb 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -1023,6 +1023,14 @@ where t.table_schema = 'information_schema' and (c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)') group by c2.column_type order by num limit 1) group by t.table_name order by num1, t.table_name; +# 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 + --echo End of 5.0 tests. # diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test index d233546f9e3..bb21d0b9ead 100644 --- a/mysql-test/t/mysqlcheck.test +++ b/mysql-test/t/mysqlcheck.test @@ -37,4 +37,29 @@ 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; +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; +use test; + --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..549d075d227 --- /dev/null +++ b/mysql-test/t/profiling.test @@ -0,0 +1,197 @@ +--source include/profiling.inc + +# 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 |