300 lines
9.3 KiB
Plaintext
300 lines
9.3 KiB
Plaintext
# Tests for PERFORMANCE_SCHEMA
|
|
# Miscelaneous
|
|
|
|
--disable_query_log
|
|
SET DEFAULT_STORAGE_ENGINE=XENGINE;
|
|
--enable_query_log
|
|
# Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
|
|
--source include/no_protocol.inc
|
|
|
|
#
|
|
# Bug#12790483 OBJECTS_SUMMARY_GLOBAL_BY_TYPE AND RENAME TABLE
|
|
#
|
|
# Rename table leaves old tables names behind in
|
|
# performance_schema.objects_summary_global_by_type
|
|
#
|
|
SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
|
|
WHERE object_schema='test';
|
|
|
|
CREATE TABLE test.t_before(a INT);
|
|
INSERT INTO test.t_before VALUES (1);
|
|
|
|
# The new table should appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE.
|
|
|
|
SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
|
|
WHERE object_schema='test';
|
|
|
|
RENAME TABLE test.t_before TO test.t_after;
|
|
|
|
# The renamed table should appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE, but only
|
|
# after it is accessed.
|
|
|
|
SELECT COUNT(*) FROM test.t_after;
|
|
|
|
SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
|
|
WHERE object_schema='test';
|
|
|
|
DROP TABLE test.t_after;
|
|
|
|
# The renamed table should not appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE.
|
|
|
|
SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
|
|
WHERE object_schema='test';
|
|
|
|
#
|
|
# Verify table views are ignored by the table io instrumentation.
|
|
#
|
|
|
|
CREATE TABLE test.t1(a INT);
|
|
INSERT INTO test.t1 VALUES (1);
|
|
CREATE VIEW test.v1 AS SELECT * FROM test.t1;
|
|
SELECT COUNT(*) FROM test.v1;
|
|
|
|
# Verify that a PFS table share was not created for the view.
|
|
SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
|
|
WHERE object_schema='test';
|
|
|
|
DROP VIEW test.v1;
|
|
DROP TABLE test.t1;
|
|
|
|
SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
|
|
WHERE object_schema='test';
|
|
|
|
|
|
#
|
|
# Bug#45496 Performance schema: assertion fails in
|
|
# ha_perfschema::rnd_init:223
|
|
#
|
|
|
|
--disable_result_log
|
|
SELECT EVENT_ID FROM performance_schema.events_waits_current
|
|
WHERE THREAD_ID IN
|
|
(SELECT THREAD_ID FROM performance_schema.threads)
|
|
AND EVENT_NAME IN
|
|
(SELECT NAME FROM performance_schema.setup_instruments
|
|
WHERE NAME LIKE "wait/synch/%")
|
|
LIMIT 1;
|
|
--enable_result_log
|
|
|
|
#
|
|
# Bug#45088 Should not be able to create tables of engine PERFORMANCE_SCHEMA
|
|
#
|
|
|
|
--error ER_WRONG_PERFSCHEMA_USAGE
|
|
create table test.t1(a int) engine=performance_schema;
|
|
|
|
# The table should not appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE
|
|
|
|
SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
|
|
WHERE object_schema='test';
|
|
|
|
#
|
|
# Bug#44897 Performance Schema: can create a ghost table in another database
|
|
#
|
|
|
|
--error ER_WRONG_PERFSCHEMA_USAGE
|
|
create table test.t1 like performance_schema.events_waits_current;
|
|
|
|
# The table should not appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE
|
|
|
|
SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
|
|
WHERE object_schema='test';
|
|
|
|
#
|
|
# Bug#44898 PerformanceSchema: can create a table in db performance_schema, cannot insert
|
|
#
|
|
|
|
--error ER_DBACCESS_DENIED_ERROR
|
|
create table performance_schema.t1(a int);
|
|
|
|
# The table should not appear in OBJECTS_SUMMARY_GLOBAL_BY_TYPE
|
|
|
|
SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type
|
|
WHERE object_schema='test';
|
|
|
|
#
|
|
# Bug#51447 performance schema evil twin files
|
|
#
|
|
|
|
--disable_warnings
|
|
drop table if exists test.ghost;
|
|
--enable_warnings
|
|
|
|
create table test.ghost (a int, b int);
|
|
alter table test.ghost add index index_a(a);
|
|
alter table test.ghost add index index_b(b);
|
|
insert into test.ghost values (1, 3);
|
|
insert into test.ghost values (2, 4);
|
|
select * from test.ghost;
|
|
|
|
drop table test.ghost;
|
|
|
|
# Shoud return nothing
|
|
select * from performance_schema.file_instances
|
|
where file_name like "%ghost%";
|
|
|
|
#
|
|
# Bug#52586 Misleading error message on attempt to access
|
|
# a P_S table using a wrong name
|
|
|
|
--error ER_NO_SUCH_TABLE
|
|
select * from performance_schema.no_such_table;
|
|
|
|
#
|
|
# Bug#12370950 - 60905: TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE AGGREGATES NON-INSERT DML WRONGLY
|
|
#
|
|
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS t_60905;
|
|
--enable_warnings
|
|
CREATE TABLE t_60905 (i INT, j INT, KEY(i));
|
|
INSERT INTO t_60905 VALUES
|
|
(1,2), (3,4), (5,6), (7,8), (9,10);
|
|
|
|
# should delete with a "single" PRIMARY lookup (2 PRIMARY fetch, 1 PRIMARY delete)
|
|
DELETE FROM t_60905 WHERE i = 1;
|
|
|
|
# should delete with a full scan (5 NULL fetch, 1 NULL delete)
|
|
DELETE FROM t_60905 WHERE j = 8;
|
|
|
|
# show the instrument data.
|
|
SELECT object_schema,
|
|
object_name,
|
|
index_name,
|
|
count_fetch,
|
|
count_insert,
|
|
count_update,
|
|
count_delete
|
|
FROM performance_schema.table_io_waits_summary_by_index_usage
|
|
WHERE object_schema = 'test'
|
|
AND object_name = 't_60905';
|
|
|
|
DROP TABLE t_60905;
|
|
|
|
#
|
|
# Bug#71278 NUMBER OF ROWS IN PERFORMANCE SCHEMA TABLES
|
|
#
|
|
|
|
show global variables like "performance_schema_max_thread_instances";
|
|
explain select * from performance_schema.threads;
|
|
|
|
#
|
|
# Bug #73082 SHOW EVENTS in performance_schema gives Access Denied
|
|
#
|
|
|
|
use performance_schema;
|
|
show events;
|
|
show events from performance_schema;
|
|
show events where Db= 'performance_schema';
|
|
use test;
|
|
|
|
|
|
#
|
|
# Bug#11929832 - EVENTS_STATEMENTS_HISTORY HAS ERRORS=0 WHEN THERE ARE ERRORS
|
|
#
|
|
# Verify that SQL errors are properly counted.
|
|
|
|
use test;
|
|
truncate performance_schema.events_statements_history;
|
|
truncate performance_schema.events_statements_history_long;
|
|
|
|
--error ER_NO_SUCH_TABLE
|
|
select * from t1;
|
|
|
|
--echo
|
|
select mysql_errno, returned_sqlstate, message_text, errors, warnings
|
|
from performance_schema.events_statements_history where errors > 0;
|
|
|
|
--echo
|
|
select mysql_errno, returned_sqlstate, message_text, errors, warnings from
|
|
performance_schema.events_statements_history_long where errors > 0;
|
|
|
|
#
|
|
# Bug#20519832 - TRUNCATED SQL_TEXT values are not suffixed with '...'
|
|
#
|
|
# Verify that truncated SQL statements are suffixed with '...'
|
|
|
|
use performance_schema;
|
|
truncate performance_schema.events_statements_history;
|
|
|
|
set names latin1;
|
|
# Should truncate at 1024 bytes (1024 characters)
|
|
select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' AS A;
|
|
|
|
select count(*) from events_statements_history where sql_text like "%...";
|
|
|
|
#
|
|
# Bug#26162562 INDEX NAME IS NOT CORRECT AFTER DELETE OPERATION ON PFS
|
|
# TABLE
|
|
|
|
--disable_warnings
|
|
drop table if exists test.t1;
|
|
--enable_warnings
|
|
|
|
insert into performance_schema.setup_objects
|
|
values ('TABLE', 'test', 't1', 'YES', 'YES');
|
|
|
|
create table test.t1 (a int, b int, primary key (a), key my_index(b));
|
|
|
|
insert into test.t1 values (1, 2), (3, 4), (5, 6);
|
|
|
|
flush table test.t1;
|
|
|
|
select * from test.t1 where a = 3;
|
|
select * from test.t1 where b = 4;
|
|
|
|
select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR
|
|
from performance_schema.table_lock_waits_summary_by_table
|
|
where OBJECT_NAME = 't1';
|
|
|
|
select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR
|
|
from performance_schema.table_io_waits_summary_by_index_usage
|
|
where OBJECT_NAME = 't1';
|
|
|
|
update performance_schema.setup_objects
|
|
set timed = 'NO' where OBJECT_NAME = 't1';
|
|
|
|
# Lock stats should still be here
|
|
select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR
|
|
from performance_schema.table_lock_waits_summary_by_table
|
|
where OBJECT_NAME = 't1';
|
|
|
|
# Index stats should still be here
|
|
select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR
|
|
from performance_schema.table_io_waits_summary_by_index_usage
|
|
where OBJECT_NAME = 't1';
|
|
|
|
update performance_schema.setup_objects
|
|
set enabled = 'NO' where OBJECT_NAME = 't1';
|
|
|
|
# Lock stats not printed
|
|
select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR
|
|
from performance_schema.table_lock_waits_summary_by_table
|
|
where OBJECT_NAME = 't1';
|
|
|
|
# Index stats not printed
|
|
select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR
|
|
from performance_schema.table_io_waits_summary_by_index_usage
|
|
where OBJECT_NAME = 't1';
|
|
|
|
update performance_schema.setup_objects
|
|
set enabled = 'YES' where OBJECT_NAME = 't1';
|
|
|
|
# Lock stats available again
|
|
select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR
|
|
from performance_schema.table_lock_waits_summary_by_table
|
|
where OBJECT_NAME = 't1';
|
|
|
|
# Index stats available again
|
|
select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR
|
|
from performance_schema.table_io_waits_summary_by_index_usage
|
|
where OBJECT_NAME = 't1';
|
|
|
|
drop table test.t1;
|
|
|
|
delete from performance_schema.setup_objects
|
|
where (OBJECT_TYPE = 'TABLE')
|
|
and (OBJECT_SCHEMA = 'test')
|
|
and (OBJECT_NAME = 't1');
|
|
|