#At file:///devel/mysql/trunk-perfschema2/ based on revid:marc.alff@stripped
2959 Jorgen Austvik 2009-11-19
WL#4818: Tests for PERFORMANCE_SCHEMA
added:
mysql-test/suite/perfschema/include/table_helper.inc
mysql-test/suite/perfschema/r/func_file_io.result
mysql-test/suite/perfschema/r/func_mutex.result
mysql-test/suite/perfschema/r/func_storage_engine.result
mysql-test/suite/perfschema/r/selects.result
mysql-test/suite/perfschema/t/func_file_io.test
mysql-test/suite/perfschema/t/func_mutex.test
mysql-test/suite/perfschema/t/func_storage_engine.test
mysql-test/suite/perfschema/t/selects.test
mysql-test/suite/perfschema_stress/
mysql-test/suite/perfschema_stress/README
mysql-test/suite/perfschema_stress/include/
mysql-test/suite/perfschema_stress/include/settings.inc
mysql-test/suite/perfschema_stress/r/
mysql-test/suite/perfschema_stress/r/modify.result
mysql-test/suite/perfschema_stress/r/read.result
mysql-test/suite/perfschema_stress/r/work.result
mysql-test/suite/perfschema_stress/stress_init.txt
mysql-test/suite/perfschema_stress/stress_tests.txt
mysql-test/suite/perfschema_stress/t/
mysql-test/suite/perfschema_stress/t/modify.test
mysql-test/suite/perfschema_stress/t/read.test
mysql-test/suite/perfschema_stress/t/setup.test
mysql-test/suite/perfschema_stress/t/work.test
modified:
mysql-test/lib/v1/mtr_stress.pl
mysql-test/suite/perfschema/include/privilege.inc
mysql-test/suite/perfschema/r/ddl_cond_instances.result
mysql-test/suite/perfschema/r/ddl_events_waits_current.result
mysql-test/suite/perfschema/r/ddl_events_waits_history.result
mysql-test/suite/perfschema/r/ddl_events_waits_history_long.result
mysql-test/suite/perfschema/r/ddl_ews_by_event_name.result
mysql-test/suite/perfschema/r/ddl_ews_by_instance.result
mysql-test/suite/perfschema/r/ddl_ews_by_thread_by_event_name.result
mysql-test/suite/perfschema/r/ddl_file_instances.result
mysql-test/suite/perfschema/r/ddl_fs_by_event_name.result
mysql-test/suite/perfschema/r/ddl_fs_by_instance.result
mysql-test/suite/perfschema/r/ddl_mutex_instances.result
mysql-test/suite/perfschema/r/ddl_performance_timers.result
mysql-test/suite/perfschema/r/ddl_processlist.result
mysql-test/suite/perfschema/r/ddl_rwlock_instances.result
mysql-test/suite/perfschema/r/ddl_setup_consumers.result
mysql-test/suite/perfschema/r/ddl_setup_instruments.result
mysql-test/suite/perfschema/r/ddl_setup_objects.result
mysql-test/suite/perfschema/r/ddl_setup_timers.result
mysql-test/suite/perfschema/r/privilege.result
mysql-test/suite/perfschema/t/ddl_cond_instances.test
mysql-test/suite/perfschema/t/ddl_events_waits_current.test
mysql-test/suite/perfschema/t/ddl_events_waits_history.test
mysql-test/suite/perfschema/t/ddl_events_waits_history_long.test
mysql-test/suite/perfschema/t/ddl_ews_by_event_name.test
mysql-test/suite/perfschema/t/ddl_ews_by_instance.test
mysql-test/suite/perfschema/t/ddl_ews_by_thread_by_event_name.test
mysql-test/suite/perfschema/t/ddl_file_instances.test
mysql-test/suite/perfschema/t/ddl_fs_by_event_name.test
mysql-test/suite/perfschema/t/ddl_fs_by_instance.test
mysql-test/suite/perfschema/t/ddl_mutex_instances.test
mysql-test/suite/perfschema/t/ddl_performance_timers.test
mysql-test/suite/perfschema/t/ddl_processlist.test
mysql-test/suite/perfschema/t/ddl_rwlock_instances.test
mysql-test/suite/perfschema/t/ddl_setup_consumers.test
mysql-test/suite/perfschema/t/ddl_setup_instruments.test
mysql-test/suite/perfschema/t/ddl_setup_objects.test
mysql-test/suite/perfschema/t/ddl_setup_timers.test
mysql-test/suite/perfschema/t/disabled.def
mysql-test/suite/perfschema/t/privilege.test
=== modified file 'mysql-test/lib/v1/mtr_stress.pl'
--- a/mysql-test/lib/v1/mtr_stress.pl 2008-11-14 08:45:32 +0000
+++ b/mysql-test/lib/v1/mtr_stress.pl 2009-11-19 12:59:38 +0000
@@ -150,7 +150,7 @@ sub run_stress_test ()
mtr_add_arg($args, "--verbose");
mtr_add_arg($args, "--cleanup");
mtr_add_arg($args, "--log-error-details");
- mtr_add_arg($args, "--abort-on-error");
+ mtr_add_arg($args, "--abort-on-error=1");
if ( $::opt_stress_init_file )
{
=== modified file 'mysql-test/suite/perfschema/include/privilege.inc'
--- a/mysql-test/suite/perfschema/include/privilege.inc 2009-11-14 05:05:21 +0000
+++ b/mysql-test/suite/perfschema/include/privilege.inc 2009-11-19 12:59:38 +0000
@@ -165,3 +165,24 @@ unlock tables;
lock table performance_schema.FILE_INSTANCES write;
unlock tables;
+--echo #
+--echo # WL#4818, NFS2: Can use grants to give normal user access
+--echo # to view data from _CURRENT and _HISTORY tables
+--echo #
+--echo # Should work as pfs_user_1 and pfs_user_2, but not as pfs_user_3.
+--echo # (Except for EVENTS_WAITS_CURRENT, which is granted.)
+
+# Errors here will be cought by the diff afterwards
+--disable_abort_on_error
+
+--replace_column 1 [EVENT_ID]
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_HISTORY LIMIT 1;
+--replace_column 1 [EVENT_ID]
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_HISTORY_LONG LIMIT 1;
+--replace_column 1 [EVENT_ID]
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_CURRENT LIMIT 1;
+--replace_column 1 [EVENT_NAME]
+SELECT EVENT_NAME FROM performance_schema.EVENTS_WAITS_SUMMARY_BY_INSTANCE LIMIT 1;
+--replace_column 1 [EVENT_NAME]
+SELECT EVENT_NAME FROM performance_schema.FILE_SUMMARY_BY_INSTANCE LIMIT 1;
+--enable_abort_on_error
=== added file 'mysql-test/suite/perfschema/include/table_helper.inc'
--- a/mysql-test/suite/perfschema/include/table_helper.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/include/table_helper.inc 2009-11-19 12:59:38 +0000
@@ -0,0 +1,120 @@
+# Copyright (C) 2008-2009 Sun Microsystems, Inc
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+# Helper for table tests, use like this:
+#
+# --let $table_name= TABLE_NAME
+# --let $column_name= COLUMN_NAME
+# --source ../include/table_helper.inc
+
+#
+# From standard table tests
+#
+
+let $setup_table= 0;
+let $not_setup_table= 1;
+if (`SELECT SUBSTRING('$table_name', 1, 5) = 'SETUP'`)
+{
+ let $setup_table= 1;
+ let $not_setup_table= 0;
+}
+
+let $instances_table= 0;
+if (`SELECT SUBSTRING('$table_name', LENGTH('$table_name') - 8, 9) = 'INSTANCES'`)
+{
+ let $instances_table= 1;
+}
+
+let $info_table= 0;
+if (`SELECT '$table_name' = 'PERFORMANCE_TIMERS' OR '$table_name' = 'PROCESSLIST'`)
+{
+ let $info_table= 1;
+}
+
+let $normal_table= 1;
+let $special_table= 0;
+if (`SELECT $setup_table OR $instances_table OR $info_table`)
+{
+ let $normal_table= 0;
+ let $special_table= 1;
+}
+
+--error ER_DBACCESS_DENIED_ERROR
+eval ALTER TABLE performance_schema.$table_name ADD COLUMN test_foo INTEGER;
+
+# --echo "Normal: $normal_table, special: $special_table, setup: $setup_table, info: $info_table, instances: $instances_table"
+
+if ($normal_table)
+{
+ eval TRUNCATE TABLE performance_schema.$table_name;
+}
+if ($special_table)
+{
+ # TODO: Remove when SETUP_OBJECTS has implemented ACLs
+ if (`SELECT '$table_name' != 'SETUP_OBJECTS'`)
+ {
+ --error ER_WRONG_PERFSCHEMA_USAGE
+ eval TRUNCATE TABLE performance_schema.$table_name;
+ }
+}
+
+#
+# WL#4818, FSE1: PERFORMANCE_SCHEMA tables can not be indexed.
+#
+# Should not have access to adding indexes to tables in performance schema.
+#
+
+--error ER_DBACCESS_DENIED_ERROR
+eval ALTER TABLE performance_schema.$table_name ADD INDEX test_index ($column_name);
+
+--error ER_DBACCESS_DENIED_ERROR
+eval CREATE UNIQUE INDEX history_event ON performance_schema.$table_name ($column_name);
+
+#
+# WL#4818, FSE2: Write locks
+#
+# SETUP_* tables should be possible to write lock, but other tables should
+# not allow this
+#
+
+if ($setup_table)
+{
+ eval LOCK TABLES performance_schema.$table_name WRITE;
+}
+if ($not_setup_table)
+{
+ --error ER_TABLEACCESS_DENIED_ERROR
+ eval LOCK TABLES performance_schema.$table_name WRITE;
+}
+UNLOCK TABLES;
+
+if ($setup_table)
+{
+ eval LOCK TABLES performance_schema.$table_name READ;
+}
+if ($not_setup_table)
+{
+ --error ER_TABLEACCESS_DENIED_ERROR
+ eval LOCK TABLES performance_schema.$table_name READ;
+}
+ UNLOCK TABLES;
+
+#
+# FSE6: Drop on history tables should not drop them
+#
+
+# TODO: Commented out due to Bug#44213
+# --error ER_TABLEACCESS_DENIED_ERROR
+# eval DROP TABLE performance_schema.$table_name;
=== modified file 'mysql-test/suite/perfschema/r/ddl_cond_instances.result'
--- a/mysql-test/suite/perfschema/r/ddl_cond_instances.result 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_cond_instances.result 2009-11-19 12:59:38 +0000
@@ -1,4 +1,14 @@
-alter table performance_schema.COND_INSTANCES add column foo integer;
+ALTER TABLE performance_schema.COND_INSTANCES ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.COND_INSTANCES;
+TRUNCATE TABLE performance_schema.COND_INSTANCES;
ERROR HY000: Invalid performance_schema usage.
+ALTER TABLE performance_schema.COND_INSTANCES ADD INDEX test_index (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.COND_INSTANCES (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.COND_INSTANCES WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'COND_INSTANCES'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.COND_INSTANCES READ;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'COND_INSTANCES'
+UNLOCK TABLES;
=== modified file 'mysql-test/suite/perfschema/r/ddl_events_waits_current.result'
--- a/mysql-test/suite/perfschema/r/ddl_events_waits_current.result 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_events_waits_current.result 2009-11-19 12:59:38 +0000
@@ -1,3 +1,13 @@
-alter table performance_schema.EVENTS_WAITS_CURRENT add column foo integer;
+ALTER TABLE performance_schema.EVENTS_WAITS_CURRENT ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.EVENTS_WAITS_CURRENT;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_CURRENT;
+ALTER TABLE performance_schema.EVENTS_WAITS_CURRENT ADD INDEX test_index (EVENT_ID);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.EVENTS_WAITS_CURRENT (EVENT_ID);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.EVENTS_WAITS_CURRENT WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'EVENTS_WAITS_CURRENT'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.EVENTS_WAITS_CURRENT READ;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'EVENTS_WAITS_CURRENT'
+UNLOCK TABLES;
=== modified file 'mysql-test/suite/perfschema/r/ddl_events_waits_history.result'
--- a/mysql-test/suite/perfschema/r/ddl_events_waits_history.result 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_events_waits_history.result 2009-11-19 12:59:38 +0000
@@ -1,3 +1,13 @@
-alter table performance_schema.EVENTS_WAITS_HISTORY add column foo integer;
+ALTER TABLE performance_schema.EVENTS_WAITS_HISTORY ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.EVENTS_WAITS_HISTORY;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY;
+ALTER TABLE performance_schema.EVENTS_WAITS_HISTORY ADD INDEX test_index (EVENT_id);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.EVENTS_WAITS_HISTORY (EVENT_id);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.EVENTS_WAITS_HISTORY WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'EVENTS_WAITS_HISTORY'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.EVENTS_WAITS_HISTORY READ;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'EVENTS_WAITS_HISTORY'
+UNLOCK TABLES;
=== modified file 'mysql-test/suite/perfschema/r/ddl_events_waits_history_long.result'
--- a/mysql-test/suite/perfschema/r/ddl_events_waits_history_long.result 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_events_waits_history_long.result 2009-11-19 12:59:38 +0000
@@ -1,3 +1,13 @@
-alter table performance_schema.EVENTS_WAITS_HISTORY_LONG add column foo integer;
+ALTER TABLE performance_schema.EVENTS_WAITS_HISTORY_LONG ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.EVENTS_WAITS_HISTORY_LONG;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY_LONG;
+ALTER TABLE performance_schema.EVENTS_WAITS_HISTORY_LONG ADD INDEX test_index (EVENT_ID);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.EVENTS_WAITS_HISTORY_LONG (EVENT_ID);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.EVENTS_WAITS_HISTORY_LONG WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'EVENTS_WAITS_HISTORY_LONG'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.EVENTS_WAITS_HISTORY_LONG READ;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'EVENTS_WAITS_HISTORY_LONG'
+UNLOCK TABLES;
=== modified file 'mysql-test/suite/perfschema/r/ddl_ews_by_event_name.result'
--- a/mysql-test/suite/perfschema/r/ddl_ews_by_event_name.result 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_ews_by_event_name.result 2009-11-19 12:59:38 +0000
@@ -1,3 +1,13 @@
-alter table performance_schema.EVENTS_WAITS_SUMMARY_BY_EVENT_NAME add column foo integer;
+ALTER TABLE performance_schema.EVENTS_WAITS_SUMMARY_BY_EVENT_NAME ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.EVENTS_WAITS_SUMMARY_BY_EVENT_NAME;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_SUMMARY_BY_EVENT_NAME;
+ALTER TABLE performance_schema.EVENTS_WAITS_SUMMARY_BY_EVENT_NAME ADD INDEX test_index (EVENT_NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.EVENTS_WAITS_SUMMARY_BY_EVENT_NAME (EVENT_NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.EVENTS_WAITS_SUMMARY_BY_EVENT_NAME WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'EVENTS_WAITS_SUMMARY_BY_EVENT_NAME'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.EVENTS_WAITS_SUMMARY_BY_EVENT_NAME READ;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'EVENTS_WAITS_SUMMARY_BY_EVENT_NAME'
+UNLOCK TABLES;
=== modified file 'mysql-test/suite/perfschema/r/ddl_ews_by_instance.result'
--- a/mysql-test/suite/perfschema/r/ddl_ews_by_instance.result 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_ews_by_instance.result 2009-11-19 12:59:38 +0000
@@ -1,3 +1,13 @@
-alter table performance_schema.EVENTS_WAITS_SUMMARY_BY_INSTANCE add column foo integer;
+ALTER TABLE performance_schema.EVENTS_WAITS_SUMMARY_BY_INSTANCE ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.EVENTS_WAITS_SUMMARY_BY_INSTANCE;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_SUMMARY_BY_INSTANCE;
+ALTER TABLE performance_schema.EVENTS_WAITS_SUMMARY_BY_INSTANCE ADD INDEX test_index (EVENT_NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.EVENTS_WAITS_SUMMARY_BY_INSTANCE (EVENT_NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.EVENTS_WAITS_SUMMARY_BY_INSTANCE WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'EVENTS_WAITS_SUMMARY_BY_INSTANCE'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.EVENTS_WAITS_SUMMARY_BY_INSTANCE READ;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'EVENTS_WAITS_SUMMARY_BY_INSTANCE'
+UNLOCK TABLES;
=== modified file 'mysql-test/suite/perfschema/r/ddl_ews_by_thread_by_event_name.result'
--- a/mysql-test/suite/perfschema/r/ddl_ews_by_thread_by_event_name.result 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_ews_by_thread_by_event_name.result 2009-11-19 12:59:38 +0000
@@ -1,4 +1,13 @@
-alter table performance_schema.EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME
-add column foo integer;
+ALTER TABLE performance_schema.EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME;
+ALTER TABLE performance_schema.EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME ADD INDEX test_index (THREAD_ID);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME (THREAD_ID);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME READ;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME'
+UNLOCK TABLES;
=== modified file 'mysql-test/suite/perfschema/r/ddl_file_instances.result'
--- a/mysql-test/suite/perfschema/r/ddl_file_instances.result 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_file_instances.result 2009-11-19 12:59:38 +0000
@@ -1,4 +1,14 @@
-alter table performance_schema.FILE_INSTANCES add column foo integer;
+ALTER TABLE performance_schema.FILE_INSTANCES ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.FILE_INSTANCES;
+TRUNCATE TABLE performance_schema.FILE_INSTANCES;
ERROR HY000: Invalid performance_schema usage.
+ALTER TABLE performance_schema.FILE_INSTANCES ADD INDEX test_index (FILE_NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.FILE_INSTANCES (FILE_NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.FILE_INSTANCES WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'FILE_INSTANCES'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.FILE_INSTANCES READ;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'FILE_INSTANCES'
+UNLOCK TABLES;
=== modified file 'mysql-test/suite/perfschema/r/ddl_fs_by_event_name.result'
--- a/mysql-test/suite/perfschema/r/ddl_fs_by_event_name.result 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_fs_by_event_name.result 2009-11-19 12:59:38 +0000
@@ -1,3 +1,13 @@
-alter table performance_schema.FILE_SUMMARY_BY_EVENT_NAME add column foo integer;
+ALTER TABLE performance_schema.FILE_SUMMARY_BY_EVENT_NAME ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.FILE_SUMMARY_BY_EVENT_NAME;
+TRUNCATE TABLE performance_schema.FILE_SUMMARY_BY_EVENT_NAME;
+ALTER TABLE performance_schema.FILE_SUMMARY_BY_EVENT_NAME ADD INDEX test_index (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.FILE_SUMMARY_BY_EVENT_NAME (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.FILE_SUMMARY_BY_EVENT_NAME WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'FILE_SUMMARY_BY_EVENT_NAME'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.FILE_SUMMARY_BY_EVENT_NAME READ;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'FILE_SUMMARY_BY_EVENT_NAME'
+UNLOCK TABLES;
=== modified file 'mysql-test/suite/perfschema/r/ddl_fs_by_instance.result'
--- a/mysql-test/suite/perfschema/r/ddl_fs_by_instance.result 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_fs_by_instance.result 2009-11-19 12:59:38 +0000
@@ -1,3 +1,13 @@
-alter table performance_schema.FILE_SUMMARY_BY_INSTANCE add column foo integer;
+ALTER TABLE performance_schema.FILE_SUMMARY_BY_INSTANCE ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.FILE_SUMMARY_BY_INSTANCE;
+TRUNCATE TABLE performance_schema.FILE_SUMMARY_BY_INSTANCE;
+ALTER TABLE performance_schema.FILE_SUMMARY_BY_INSTANCE ADD INDEX test_index (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.FILE_SUMMARY_BY_INSTANCE (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.FILE_SUMMARY_BY_INSTANCE WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'FILE_SUMMARY_BY_INSTANCE'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.FILE_SUMMARY_BY_INSTANCE READ;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'FILE_SUMMARY_BY_INSTANCE'
+UNLOCK TABLES;
=== modified file 'mysql-test/suite/perfschema/r/ddl_mutex_instances.result'
--- a/mysql-test/suite/perfschema/r/ddl_mutex_instances.result 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_mutex_instances.result 2009-11-19 12:59:38 +0000
@@ -1,4 +1,14 @@
-alter table performance_schema.MUTEX_INSTANCES add column foo integer;
+ALTER TABLE performance_schema.MUTEX_INSTANCES ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.MUTEX_INSTANCES;
+TRUNCATE TABLE performance_schema.MUTEX_INSTANCES;
ERROR HY000: Invalid performance_schema usage.
+ALTER TABLE performance_schema.MUTEX_INSTANCES ADD INDEX test_index (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.MUTEX_INSTANCES (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.MUTEX_INSTANCES WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'MUTEX_INSTANCES'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.MUTEX_INSTANCES READ;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'MUTEX_INSTANCES'
+UNLOCK TABLES;
=== modified file 'mysql-test/suite/perfschema/r/ddl_performance_timers.result'
--- a/mysql-test/suite/perfschema/r/ddl_performance_timers.result 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_performance_timers.result 2009-11-19 12:59:38 +0000
@@ -1,4 +1,14 @@
-alter table performance_schema.PERFORMANCE_TIMERS add column foo integer;
+ALTER TABLE performance_schema.PERFORMANCE_TIMERS ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.PERFORMANCE_TIMERS;
+TRUNCATE TABLE performance_schema.PERFORMANCE_TIMERS;
ERROR HY000: Invalid performance_schema usage.
+ALTER TABLE performance_schema.PERFORMANCE_TIMERS ADD INDEX test_index (TIMER_NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.PERFORMANCE_TIMERS (TIMER_NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.PERFORMANCE_TIMERS WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'PERFORMANCE_TIMERS'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.PERFORMANCE_TIMERS READ;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'PERFORMANCE_TIMERS'
+UNLOCK TABLES;
=== modified file 'mysql-test/suite/perfschema/r/ddl_processlist.result'
--- a/mysql-test/suite/perfschema/r/ddl_processlist.result 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_processlist.result 2009-11-19 12:59:38 +0000
@@ -1,4 +1,14 @@
-alter table performance_schema.PROCESSLIST add column foo integer;
+ALTER TABLE performance_schema.PROCESSLIST ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.PROCESSLIST;
+TRUNCATE TABLE performance_schema.PROCESSLIST;
ERROR HY000: Invalid performance_schema usage.
+ALTER TABLE performance_schema.PROCESSLIST ADD INDEX test_index (ID);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.PROCESSLIST (ID);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.PROCESSLIST WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'PROCESSLIST'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.PROCESSLIST READ;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'PROCESSLIST'
+UNLOCK TABLES;
=== modified file 'mysql-test/suite/perfschema/r/ddl_rwlock_instances.result'
--- a/mysql-test/suite/perfschema/r/ddl_rwlock_instances.result 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_rwlock_instances.result 2009-11-19 12:59:38 +0000
@@ -1,4 +1,14 @@
-alter table performance_schema.RWLOCK_INSTANCES add column foo integer;
+ALTER TABLE performance_schema.RWLOCK_INSTANCES ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.RWLOCK_INSTANCES;
+TRUNCATE TABLE performance_schema.RWLOCK_INSTANCES;
ERROR HY000: Invalid performance_schema usage.
+ALTER TABLE performance_schema.RWLOCK_INSTANCES ADD INDEX test_index (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.RWLOCK_INSTANCES (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.RWLOCK_INSTANCES WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'RWLOCK_INSTANCES'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.RWLOCK_INSTANCES READ;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'RWLOCK_INSTANCES'
+UNLOCK TABLES;
=== modified file 'mysql-test/suite/perfschema/r/ddl_setup_consumers.result'
--- a/mysql-test/suite/perfschema/r/ddl_setup_consumers.result 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_setup_consumers.result 2009-11-19 12:59:38 +0000
@@ -1,4 +1,12 @@
-alter table performance_schema.SETUP_CONSUMERS add column foo integer;
+ALTER TABLE performance_schema.SETUP_CONSUMERS ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.SETUP_CONSUMERS;
+TRUNCATE TABLE performance_schema.SETUP_CONSUMERS;
ERROR HY000: Invalid performance_schema usage.
+ALTER TABLE performance_schema.SETUP_CONSUMERS ADD INDEX test_index (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.SETUP_CONSUMERS (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.SETUP_CONSUMERS WRITE;
+UNLOCK TABLES;
+LOCK TABLES performance_schema.SETUP_CONSUMERS READ;
+UNLOCK TABLES;
=== modified file 'mysql-test/suite/perfschema/r/ddl_setup_instruments.result'
--- a/mysql-test/suite/perfschema/r/ddl_setup_instruments.result 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_setup_instruments.result 2009-11-19 12:59:38 +0000
@@ -1,4 +1,12 @@
-alter table performance_schema.SETUP_INSTRUMENTS add column foo integer;
+ALTER TABLE performance_schema.SETUP_INSTRUMENTS ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.SETUP_INSTRUMENTS;
+TRUNCATE TABLE performance_schema.SETUP_INSTRUMENTS;
ERROR HY000: Invalid performance_schema usage.
+ALTER TABLE performance_schema.SETUP_INSTRUMENTS ADD INDEX test_index (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.SETUP_INSTRUMENTS (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.SETUP_INSTRUMENTS WRITE;
+UNLOCK TABLES;
+LOCK TABLES performance_schema.SETUP_INSTRUMENTS READ;
+UNLOCK TABLES;
=== modified file 'mysql-test/suite/perfschema/r/ddl_setup_objects.result'
--- a/mysql-test/suite/perfschema/r/ddl_setup_objects.result 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_setup_objects.result 2009-11-19 12:59:38 +0000
@@ -1,3 +1,10 @@
-alter table performance_schema.SETUP_OBJECTS add column foo integer;
+ALTER TABLE performance_schema.SETUP_OBJECTS ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.SETUP_OBJECTS;
+ALTER TABLE performance_schema.SETUP_OBJECTS ADD INDEX test_index (OBJECT_NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.SETUP_OBJECTS (OBJECT_NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.SETUP_OBJECTS WRITE;
+UNLOCK TABLES;
+LOCK TABLES performance_schema.SETUP_OBJECTS READ;
+UNLOCK TABLES;
=== modified file 'mysql-test/suite/perfschema/r/ddl_setup_timers.result'
--- a/mysql-test/suite/perfschema/r/ddl_setup_timers.result 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/r/ddl_setup_timers.result 2009-11-19 12:59:38 +0000
@@ -1,4 +1,12 @@
-alter table performance_schema.SETUP_TIMERS add column foo integer;
+ALTER TABLE performance_schema.SETUP_TIMERS ADD COLUMN test_foo INTEGER;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
-truncate table performance_schema.SETUP_TIMERS;
+TRUNCATE TABLE performance_schema.SETUP_TIMERS;
ERROR HY000: Invalid performance_schema usage.
+ALTER TABLE performance_schema.SETUP_TIMERS ADD INDEX test_index (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.SETUP_TIMERS (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.SETUP_TIMERS WRITE;
+UNLOCK TABLES;
+LOCK TABLES performance_schema.SETUP_TIMERS READ;
+UNLOCK TABLES;
=== added file 'mysql-test/suite/perfschema/r/func_file_io.result'
--- a/mysql-test/suite/perfschema/r/func_file_io.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/r/func_file_io.result 2009-11-19 12:59:38 +0000
@@ -0,0 +1,114 @@
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'NO', timed = 'YES';
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'YES'
+WHERE name LIKE 'wait/io/file/%';
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value')
+ENGINE=MyISAM;
+INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY_LONG;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_CURRENT;
+SELECT * FROM t1 WHERE id = 1;
+id b
+1 initial value
+SET @before_count = (SELECT SUM(TIMER_WAIT)
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile')
+AND (OBJECT_NAME LIKE '%/t1.MYD'));
+SELECT * FROM t1 WHERE id < 4;
+id b
+1 initial value
+2 initial value
+3 initial value
+SET @after_count = (SELECT SUM(TIMER_WAIT)
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile')
+AND (OBJECT_NAME LIKE '%/t1.MYD') AND (1 = 1));
+SELECT IF((@after_count - @before_count) > 0, 'Success', 'Failure') test_ff1_timed;
+test_ff1_timed
+Success
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled='NO';
+SET @before_count = (SELECT SUM(TIMER_WAIT)
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile')
+AND (OBJECT_NAME LIKE '%/t1.MYD') AND (2 = 2));
+SELECT * FROM t1 WHERE id < 6;
+id b
+1 initial value
+2 initial value
+3 initial value
+4 initial value
+5 initial value
+SET @after_count = (SELECT SUM(TIMER_WAIT)
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile')
+AND (OBJECT_NAME LIKE '%/t1.MYD') AND (3 = 3));
+SELECT IF((COALESCE(@after_count, 0) - COALESCE(@before_count, 0)) = 0, 'Success', 'Failure') test_ff2_timed;
+test_ff2_timed
+Success
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'YES'
+WHERE name LIKE 'wait/io/file/%';
+UPDATE performance_schema.SETUP_INSTRUMENTS SET timed = 'NO';
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY_LONG;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_CURRENT;
+SELECT * FROM t1 WHERE id > 4;
+id b
+5 initial value
+6 initial value
+7 initial value
+8 initial value
+SELECT * FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+WHERE TIMER_WAIT != NULL
+OR TIMER_START != NULL
+OR TIMER_END != NULL;
+THREAD_ID EVENT_ID EVENT_NAME SOURCE TIMER_START TIMER_END TIMER_WAIT SPINS OBJECT_SCHEMA OBJECT_NAME OBJECT_TYPE OBJECT_INSTANCE_BEGIN NESTING_EVENT_ID OPERATION NUMBER_OF_BYTES FLAGS
+SELECT * FROM performance_schema.EVENTS_WAITS_HISTORY
+WHERE TIMER_WAIT != NULL
+OR TIMER_START != NULL
+OR TIMER_END != NULL;
+THREAD_ID EVENT_ID EVENT_NAME SOURCE TIMER_START TIMER_END TIMER_WAIT SPINS OBJECT_SCHEMA OBJECT_NAME OBJECT_TYPE OBJECT_INSTANCE_BEGIN NESTING_EVENT_ID OPERATION NUMBER_OF_BYTES FLAGS
+SELECT * FROM performance_schema.EVENTS_WAITS_CURRENT
+WHERE TIMER_WAIT != NULL
+OR TIMER_START != NULL
+OR TIMER_END != NULL;
+THREAD_ID EVENT_ID EVENT_NAME SOURCE TIMER_START TIMER_END TIMER_WAIT SPINS OBJECT_SCHEMA OBJECT_NAME OBJECT_TYPE OBJECT_INSTANCE_BEGIN NESTING_EVENT_ID OPERATION NUMBER_OF_BYTES FLAGS
+UPDATE performance_schema.SETUP_INSTRUMENTS SET timed = 'YES';
+SELECT * FROM t1 WHERE id < 4;
+id b
+1 initial value
+2 initial value
+3 initial value
+DROP TABLE t1;
+SELECT SUM(COUNT_READ) AS sum_count_read,
+SUM(COUNT_WRITE) AS sum_count_write,
+SUM(SUM_NUMBER_OF_BYTES_READ) AS sum_num_bytes_read,
+SUM(SUM_NUMBER_OF_BYTES_WRITE) AS sum_num_bytes_write
+FROM performance_schema.FILE_SUMMARY_BY_INSTANCE
+WHERE FILE_NAME LIKE CONCAT('%', @@tmpdir, '%') ORDER BY NULL;
+SELECT EVENT_NAME, COUNT_STAR, AVG_TIMER_WAIT, SUM_TIMER_WAIT
+FROM performance_schema.EVENTS_WAITS_SUMMARY_BY_EVENT_NAME
+WHERE COUNT_STAR > 0
+ORDER BY SUM_TIMER_WAIT DESC
+LIMIT 10;
+SELECT i.user, SUM(TIMER_WAIT) SUM_WAIT
+# ((TIME_TO_SEC(TIMEDIFF(NOW(), i.startup_time)) * 1000) / SUM(TIMER_WAIT)) * 100 WAIT_PERCENTAGE
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG h
+INNER JOIN performance_schema.PROCESSLIST p USING (THREAD_ID)
+LEFT JOIN information_schema.PROCESSLIST i USING (ID)
+GROUP BY i.user
+ORDER BY SUM_WAIT DESC
+LIMIT 20;
+SELECT h.EVENT_NAME, SUM(h.TIMER_WAIT) TOTAL_WAIT
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG h
+INNER JOIN performance_schema.PROCESSLIST p USING (THREAD_ID)
+WHERE p.ID = 1
+GROUP BY h.EVENT_NAME
+HAVING TOTAL_WAIT > 0;
+SELECT i.user, h.operation, SUM(NUMBER_OF_BYTES) bytes
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG h
+INNER JOIN performance_schema.PROCESSLIST p USING (THREAD_ID)
+LEFT JOIN information_schema.PROCESSLIST i USING (ID)
+GROUP BY i.user, h.operation
+HAVING BYTES > 0
+ORDER BY i.user, h.operation;
=== added file 'mysql-test/suite/perfschema/r/func_mutex.result'
--- a/mysql-test/suite/perfschema/r/func_mutex.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/r/func_mutex.result 2009-11-19 12:59:38 +0000
@@ -0,0 +1,113 @@
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'NO', timed = 'YES';
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'YES'
+WHERE name LIKE 'wait/synch/mutex/%'
+ OR name LIKE 'wait/synch/rwlock/%';
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value')
+ENGINE=MyISAM;
+INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY_LONG;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_CURRENT;
+SELECT * FROM t1 WHERE id = 1;
+id b
+1 initial value
+SET @before_count = (SELECT SUM(TIMER_WAIT)
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+WHERE (EVENT_NAME = 'wait/synch/mutex/sql/LOCK_open'));
+SELECT * FROM t1;
+id b
+1 initial value
+2 initial value
+3 initial value
+4 initial value
+5 initial value
+6 initial value
+7 initial value
+8 initial value
+SET @after_count = (SELECT SUM(TIMER_WAIT)
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+WHERE (EVENT_NAME = 'wait/synch/mutex/sql/LOCK_open'));
+SELECT IF((@after_count - @before_count) > 0, 'Success', 'Failure') test_fm1_timed;
+test_fm1_timed
+Success
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'NO'
+WHERE NAME = 'wait/synch/mutex/sql/LOCK_open';
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY_LONG;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_CURRENT;
+SELECT * FROM t1 WHERE id = 1;
+id b
+1 initial value
+SET @before_count = (SELECT SUM(TIMER_WAIT)
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+WHERE (EVENT_NAME = 'wait/synch/mutex/sql/LOCK_open'));
+SELECT * FROM t1;
+id b
+1 initial value
+2 initial value
+3 initial value
+4 initial value
+5 initial value
+6 initial value
+7 initial value
+8 initial value
+SET @after_count = (SELECT SUM(TIMER_WAIT)
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+WHERE (EVENT_NAME = 'wait/synch/mutex/sql/LOCK_open'));
+SELECT IF((COALESCE(@after_count, 0) - COALESCE(@before_count, 0)) = 0, 'Success', 'Failure') test_fm2_timed;
+test_fm2_timed
+Success
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY_LONG;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_CURRENT;
+SELECT * FROM t1 WHERE id = 1;
+id b
+1 initial value
+SET @before_count = (SELECT SUM(TIMER_WAIT)
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+WHERE (EVENT_NAME = 'wait/synch/rwlock/sql/LOCK_grant'));
+SELECT * FROM t1;
+id b
+1 initial value
+2 initial value
+3 initial value
+4 initial value
+5 initial value
+6 initial value
+7 initial value
+8 initial value
+SET @after_count = (SELECT SUM(TIMER_WAIT)
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+WHERE (EVENT_NAME = 'wait/synch/rwlock/sql/LOCK_grant'));
+SELECT IF((@after_count - @before_count) > 0, 'Success', 'Failure') test_fm1_rw_timed;
+test_fm1_rw_timed
+Success
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'NO'
+WHERE NAME = 'wait/synch/rwlock/sql/LOCK_grant';
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY_LONG;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_CURRENT;
+SELECT * FROM t1 WHERE id = 1;
+id b
+1 initial value
+SET @before_count = (SELECT SUM(TIMER_WAIT)
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+WHERE (EVENT_NAME = 'wait/synch/rwlock/sql/LOCK_grant'));
+SELECT * FROM t1;
+id b
+1 initial value
+2 initial value
+3 initial value
+4 initial value
+5 initial value
+6 initial value
+7 initial value
+8 initial value
+SET @after_count = (SELECT SUM(TIMER_WAIT)
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+WHERE (EVENT_NAME = 'wait/synch/rwlock/sql/LOCK_grant'));
+SELECT IF((COALESCE(@after_count, 0) - COALESCE(@before_count, 0)) = 0, 'Success', 'Failure') test_fm2_rw_timed;
+test_fm2_rw_timed
+Success
+DROP TABLE t1;
=== added file 'mysql-test/suite/perfschema/r/func_storage_engine.result'
--- a/mysql-test/suite/perfschema/r/func_storage_engine.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/r/func_storage_engine.result 2009-11-19 12:59:38 +0000
@@ -0,0 +1,90 @@
+ALTER TABLE performance_schema.COND_INSTANCES ADD INDEX name_index (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX history_event ON performance_schema.EVENTS_WAITS_HISTORY (EVENT_ID);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX instances_name ON performance_schema.MUTEX_INSTANCES (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+CREATE UNIQUE INDEX setup_name ON performance_schema.SETUP_INSTRUMENTS (NAME);
+ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema'
+LOCK TABLES performance_schema.MUTEX_INSTANCES READ;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'MUTEX_INSTANCES'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.MUTEX_INSTANCES WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'MUTEX_INSTANCES'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.EVENTS_WAITS_CURRENT WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'EVENTS_WAITS_CURRENT'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.EVENTS_WAITS_HISTORY_LONG WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'EVENTS_WAITS_HISTORY_LONG'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.PROCESSLIST WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'PROCESSLIST'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.PERFORMANCE_TIMERS WRITE;
+ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'PERFORMANCE_TIMERS'
+UNLOCK TABLES;
+LOCK TABLES performance_schema.SETUP_CONSUMERS READ;
+UNLOCK TABLES;
+LOCK TABLES performance_schema.SETUP_CONSUMERS WRITE;
+UNLOCK TABLES;
+LOCK TABLES performance_schema.SETUP_INSTRUMENTS WRITE;
+UNLOCK TABLES;
+LOCK TABLES performance_schema.SETUP_OBJECTS WRITE;
+UNLOCK TABLES;
+LOCK TABLES performance_schema.SETUP_TIMERS WRITE;
+UNLOCK TABLES;
+CREATE TABLE t1 (id INT) ENGINE='PERFORMANCE_SCHEMA';
+ERROR HY000: Can't create table 'test.t1' (errno: 131)
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'YES', timed = 'YES';
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value')
+ENGINE=MyISAM;
+INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+SELECT OPERATION, SUM(NUMBER_OF_BYTES) AS TOTAL
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+GROUP BY OPERATION
+HAVING TOTAL IS NOT NULL
+ORDER BY OPERATION
+LIMIT 1;
+OPERATION TOTAL
+chsize [NUM_BYTES]
+SELECT DISTINCT EVENT_ID
+FROM performance_schema.EVENTS_WAITS_CURRENT
+JOIN performance_schema.EVENTS_WAITS_HISTORY USING (EVENT_ID)
+JOIN performance_schema.EVENTS_WAITS_HISTORY_LONG USING (EVENT_ID)
+ORDER BY EVENT_ID
+LIMIT 1;
+EVENT_ID
+[EVENT_ID]
+SELECT t1.THREAD_ID, t2.EVENT_ID, t3.EVENT_NAME, t4.TIMER_WAIT
+FROM performance_schema.EVENTS_WAITS_HISTORY t1
+JOIN performance_schema.EVENTS_WAITS_HISTORY t2 USING (EVENT_ID)
+JOIN performance_schema.EVENTS_WAITS_HISTORY t3 ON (t2.THREAD_ID = t3.THREAD_ID)
+JOIN performance_schema.EVENTS_WAITS_HISTORY t4 ON (t3.EVENT_NAME = t4.EVENT_NAME)
+ORDER BY t1.EVENT_ID, t2.EVENT_ID
+LIMIT 5;
+THREAD_ID EVENT_ID EVENT_NAME TIMER_WAIT
+[THREAD_ID] [EVENT_ID] [EVENT_NAME] [TIMER_WAIT]
+[THREAD_ID] [EVENT_ID] [EVENT_NAME] [TIMER_WAIT]
+[THREAD_ID] [EVENT_ID] [EVENT_NAME] [TIMER_WAIT]
+[THREAD_ID] [EVENT_ID] [EVENT_NAME] [TIMER_WAIT]
+[THREAD_ID] [EVENT_ID] [EVENT_NAME] [TIMER_WAIT]
+SELECT THREAD_ID, EVENT_ID FROM (
+SELECT THREAD_ID, EVENT_ID FROM performance_schema.EVENTS_WAITS_CURRENT
+UNION
+SELECT THREAD_ID, EVENT_ID FROM performance_schema.EVENTS_WAITS_HISTORY
+UNION
+SELECT THREAD_ID, EVENT_ID FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+) t1 ORDER BY THREAD_ID, EVENT_ID
+LIMIT 5;
+THREAD_ID EVENT_ID
+[THREAD_ID] [EVENT_ID]
+[THREAD_ID] [EVENT_ID]
+[THREAD_ID] [EVENT_ID]
+[THREAD_ID] [EVENT_ID]
+[THREAD_ID] [EVENT_ID]
+DROP TABLE t1;
=== modified file 'mysql-test/suite/perfschema/r/privilege.result'
--- a/mysql-test/suite/perfschema/r/privilege.result 2009-10-28 19:42:26 +0000
+++ b/mysql-test/suite/perfschema/r/privilege.result 2009-11-19 12:59:38 +0000
@@ -189,6 +189,27 @@ unlock tables;
lock table performance_schema.FILE_INSTANCES write;
ERROR 42000: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'FILE_INSTANCES'
unlock tables;
+#
+# WL#4818, NFS2: Can use grants to give normal user access
+# to view data from _CURRENT and _HISTORY tables
+#
+# Should work as pfs_user_1 and pfs_user_2, but not as pfs_user_3.
+# (Except for EVENTS_WAITS_CURRENT, which is granted.)
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_HISTORY LIMIT 1;
+EVENT_ID
+[EVENT_ID]
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_HISTORY_LONG LIMIT 1;
+EVENT_ID
+[EVENT_ID]
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_CURRENT LIMIT 1;
+EVENT_ID
+[EVENT_ID]
+SELECT EVENT_NAME FROM performance_schema.EVENTS_WAITS_SUMMARY_BY_INSTANCE LIMIT 1;
+EVENT_NAME
+[EVENT_NAME]
+SELECT EVENT_NAME FROM performance_schema.FILE_SUMMARY_BY_INSTANCE LIMIT 1;
+EVENT_NAME
+[EVENT_NAME]
drop table if exists test.t1;
rename table performance_schema.SETUP_INSTRUMENTS to test.t1;
ERROR 42000: Access denied for user 'pfs_user_1'@'localhost' to database 'performance_schema'
@@ -266,6 +287,27 @@ unlock tables;
lock table performance_schema.FILE_INSTANCES write;
ERROR 42000: SELECT,LOCK TABL command denied to user 'pfs_user_1'@'localhost' for table 'FILE_INSTANCES'
unlock tables;
+#
+# WL#4818, NFS2: Can use grants to give normal user access
+# to view data from _CURRENT and _HISTORY tables
+#
+# Should work as pfs_user_1 and pfs_user_2, but not as pfs_user_3.
+# (Except for EVENTS_WAITS_CURRENT, which is granted.)
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_HISTORY LIMIT 1;
+EVENT_ID
+[EVENT_ID]
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_HISTORY_LONG LIMIT 1;
+EVENT_ID
+[EVENT_ID]
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_CURRENT LIMIT 1;
+EVENT_ID
+[EVENT_ID]
+SELECT EVENT_NAME FROM performance_schema.EVENTS_WAITS_SUMMARY_BY_INSTANCE LIMIT 1;
+EVENT_NAME
+[EVENT_NAME]
+SELECT EVENT_NAME FROM performance_schema.FILE_SUMMARY_BY_INSTANCE LIMIT 1;
+EVENT_NAME
+[EVENT_NAME]
drop table if exists test.t1;
rename table performance_schema.SETUP_INSTRUMENTS to test.t1;
ERROR 42000: Access denied for user 'pfs_user_2'@'localhost' to database 'performance_schema'
@@ -343,6 +385,27 @@ unlock tables;
lock table performance_schema.FILE_INSTANCES write;
ERROR 42000: SELECT,LOCK TABL command denied to user 'pfs_user_2'@'localhost' for table 'FILE_INSTANCES'
unlock tables;
+#
+# WL#4818, NFS2: Can use grants to give normal user access
+# to view data from _CURRENT and _HISTORY tables
+#
+# Should work as pfs_user_1 and pfs_user_2, but not as pfs_user_3.
+# (Except for EVENTS_WAITS_CURRENT, which is granted.)
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_HISTORY LIMIT 1;
+EVENT_ID
+[EVENT_ID]
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_HISTORY_LONG LIMIT 1;
+EVENT_ID
+[EVENT_ID]
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_CURRENT LIMIT 1;
+EVENT_ID
+[EVENT_ID]
+SELECT EVENT_NAME FROM performance_schema.EVENTS_WAITS_SUMMARY_BY_INSTANCE LIMIT 1;
+EVENT_NAME
+[EVENT_NAME]
+SELECT EVENT_NAME FROM performance_schema.FILE_SUMMARY_BY_INSTANCE LIMIT 1;
+EVENT_NAME
+[EVENT_NAME]
drop table if exists test.t1;
rename table performance_schema.SETUP_INSTRUMENTS to test.t1;
ERROR 42000: Access denied for user 'pfs_user_3'@'localhost' to database 'performance_schema'
@@ -420,6 +483,23 @@ unlock tables;
lock table performance_schema.FILE_INSTANCES write;
ERROR 42000: SELECT,LOCK TABL command denied to user 'pfs_user_3'@'localhost' for table 'FILE_INSTANCES'
unlock tables;
+#
+# WL#4818, NFS2: Can use grants to give normal user access
+# to view data from _CURRENT and _HISTORY tables
+#
+# Should work as pfs_user_1 and pfs_user_2, but not as pfs_user_3.
+# (Except for EVENTS_WAITS_CURRENT, which is granted.)
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_HISTORY LIMIT 1;
+ERROR 42000: SELECT command denied to user 'pfs_user_3'@'localhost' for table 'EVENTS_WAITS_HISTORY'
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_HISTORY_LONG LIMIT 1;
+ERROR 42000: SELECT command denied to user 'pfs_user_3'@'localhost' for table 'EVENTS_WAITS_HISTORY_LONG'
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_CURRENT LIMIT 1;
+EVENT_ID
+[EVENT_ID]
+SELECT EVENT_NAME FROM performance_schema.EVENTS_WAITS_SUMMARY_BY_INSTANCE LIMIT 1;
+ERROR 42000: SELECT command denied to user 'pfs_user_3'@'localhost' for table 'EVENTS_WAITS_SUMMARY_BY_INSTANCE'
+SELECT EVENT_NAME FROM performance_schema.FILE_SUMMARY_BY_INSTANCE LIMIT 1;
+ERROR 42000: SELECT command denied to user 'pfs_user_3'@'localhost' for table 'FILE_SUMMARY_BY_INSTANCE'
revoke all privileges, grant option from 'pfs_user_1'@localhost;
revoke all privileges, grant option from 'pfs_user_2'@localhost;
revoke all privileges, grant option from 'pfs_user_3'@localhost;
@@ -427,3 +507,67 @@ drop user 'pfs_user_1'@localhost;
drop user 'pfs_user_2'@localhost;
drop user 'pfs_user_3'@localhost;
flush privileges;
+# Test cases from WL#4818
+# Setup user
+CREATE user pfs_user_4;
+#
+# WL#4818, NFS4: Normal user does not have access to view data
+# without grants
+#
+# Select as pfs_user_4 should fail without grant
+SELECT event_id FROM performance_schema.events_waits_history;
+ERROR 42000: SELECT command denied to user 'pfs_user_4'@'localhost' for table 'events_waits_history'
+SELECT event_id FROM performance_schema.events_waits_history_long;
+ERROR 42000: SELECT command denied to user 'pfs_user_4'@'localhost' for table 'events_waits_history_long'
+SELECT event_id FROM performance_schema.events_waits_current;
+ERROR 42000: SELECT command denied to user 'pfs_user_4'@'localhost' for table 'events_waits_current'
+SELECT event_name FROM performance_schema.events_waits_summary_by_instance;
+ERROR 42000: SELECT command denied to user 'pfs_user_4'@'localhost' for table 'events_waits_summary_by_instance'
+SELECT event_name FROM performance_schema.file_summary_by_instance;
+ERROR 42000: SELECT command denied to user 'pfs_user_4'@'localhost' for table 'file_summary_by_instance'
+#
+# WL#4818, NFS3: Normal user does not have access to change what is
+# instrumented without grants
+#
+# User pfs_user_4 should not be allowed to tweak instrumentation without
+# explicit grant
+UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'YES';
+ERROR 42000: UPDATE command denied to user 'pfs_user_4'@'localhost' for table 'setup_instruments'
+UPDATE performance_schema.setup_instruments SET enabled = 'YES'
+WHERE name LIKE 'wait/synch/mutex/%'
+ OR name LIKE 'wait/synch/rwlock/%';
+ERROR 42000: UPDATE command denied to user 'pfs_user_4'@'localhost' for table 'setup_instruments'
+UPDATE performance_schema.setup_consumers SET enabled = 'YES';
+ERROR 42000: UPDATE command denied to user 'pfs_user_4'@'localhost' for table 'setup_consumers'
+UPDATE performance_schema.setup_timers SET timer_name = 'TICK';
+ERROR 42000: UPDATE command denied to user 'pfs_user_4'@'localhost' for table 'setup_timers'
+TRUNCATE TABLE performance_schema.events_waits_history_long;
+ERROR 42000: DROP command denied to user 'pfs_user_4'@'localhost' for table 'events_waits_history_long'
+TRUNCATE TABLE performance_schema.events_waits_history;
+ERROR 42000: DROP command denied to user 'pfs_user_4'@'localhost' for table 'events_waits_history'
+TRUNCATE TABLE performance_schema.events_waits_current;
+ERROR 42000: DROP command denied to user 'pfs_user_4'@'localhost' for table 'events_waits_current'
+#
+# WL#4814, NFS1: Can use grants to give normal user access
+# to turn on and off instrumentation
+#
+# Grant access to change tables with the root account
+GRANT UPDATE ON performance_schema.SETUP_CONSUMERS TO pfs_user_4;
+GRANT UPDATE ON performance_schema.SETUP_TIMERS TO pfs_user_4;
+GRANT UPDATE, SELECT ON performance_schema.SETUP_INSTRUMENTS TO pfs_user_4;
+GRANT DROP ON performance_schema.EVENTS_WAITS_CURRENT TO pfs_user_4;
+GRANT DROP ON performance_schema.EVENTS_WAITS_HISTORY TO pfs_user_4;
+GRANT DROP ON performance_schema.EVENTS_WAITS_HISTORY_LONG TO pfs_user_4;
+# User pfs_user_4 should now be allowed to tweak instrumentation
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'NO', timed = 'YES';
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'YES'
+WHERE name LIKE 'wait/synch/mutex/%'
+ OR name LIKE 'wait/synch/rwlock/%';
+UPDATE performance_schema.SETUP_CONSUMERS SET enabled = 'YES';
+UPDATE performance_schema.SETUP_TIMERS SET timer_name = 'TICK';
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY_LONG;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_CURRENT;
+# Clean up
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM pfs_user_4;
+DROP USER pfs_user_4;
=== added file 'mysql-test/suite/perfschema/r/selects.result'
--- a/mysql-test/suite/perfschema/r/selects.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/r/selects.result 2009-11-19 12:59:38 +0000
@@ -0,0 +1,97 @@
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'YES', timed = 'YES';
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value')
+ENGINE=MyISAM;
+INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+SELECT OPERATION, SUM(NUMBER_OF_BYTES) AS TOTAL
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+GROUP BY OPERATION
+HAVING TOTAL IS NOT NULL
+ORDER BY OPERATION
+LIMIT 1;
+OPERATION TOTAL
+chsize [NUM_BYTES]
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_CURRENT
+WHERE THREAD_ID IN
+(SELECT THREAD_ID FROM performance_schema.PROCESSLIST)
+AND EVENT_NAME IN
+(SELECT NAME FROM performance_schema.SETUP_INSTRUMENTS
+WHERE NAME LIKE "wait/synch/%")
+LIMIT 1;
+EVENT_ID
+[EVENT_ID]
+SELECT DISTINCT EVENT_ID
+FROM performance_schema.EVENTS_WAITS_CURRENT
+JOIN performance_schema.EVENTS_WAITS_HISTORY USING (EVENT_ID)
+JOIN performance_schema.EVENTS_WAITS_HISTORY_LONG USING (EVENT_ID)
+ORDER BY EVENT_ID
+LIMIT 1;
+EVENT_ID
+[EVENT_ID]
+SELECT t1.THREAD_ID, t2.EVENT_ID, t3.EVENT_NAME, t4.TIMER_WAIT
+FROM performance_schema.EVENTS_WAITS_HISTORY t1
+JOIN performance_schema.EVENTS_WAITS_HISTORY t2 USING (EVENT_ID)
+JOIN performance_schema.EVENTS_WAITS_HISTORY t3 ON (t2.THREAD_ID = t3.THREAD_ID)
+JOIN performance_schema.EVENTS_WAITS_HISTORY t4 ON (t3.EVENT_NAME = t4.EVENT_NAME)
+ORDER BY t1.EVENT_ID, t2.EVENT_ID
+LIMIT 5;
+THREAD_ID EVENT_ID EVENT_NAME TIMER_WAIT
+[THREAD_ID] [EVENT_ID] [EVENT_NAME] [TIMER_WAIT]
+[THREAD_ID] [EVENT_ID] [EVENT_NAME] [TIMER_WAIT]
+[THREAD_ID] [EVENT_ID] [EVENT_NAME] [TIMER_WAIT]
+[THREAD_ID] [EVENT_ID] [EVENT_NAME] [TIMER_WAIT]
+[THREAD_ID] [EVENT_ID] [EVENT_NAME] [TIMER_WAIT]
+SELECT THREAD_ID, EVENT_ID FROM (
+SELECT THREAD_ID, EVENT_ID FROM performance_schema.EVENTS_WAITS_CURRENT
+UNION
+SELECT THREAD_ID, EVENT_ID FROM performance_schema.EVENTS_WAITS_HISTORY
+UNION
+SELECT THREAD_ID, EVENT_ID FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+) t1 ORDER BY THREAD_ID, EVENT_ID
+LIMIT 5;
+THREAD_ID EVENT_ID
+[THREAD_ID] [EVENT_ID]
+[THREAD_ID] [EVENT_ID]
+[THREAD_ID] [EVENT_ID]
+[THREAD_ID] [EVENT_ID]
+[THREAD_ID] [EVENT_ID]
+CREATE EVENT t_ps_event
+ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
+DO SELECT DISTINCT EVENT_ID
+FROM performance_schema.EVENTS_WAITS_CURRENT
+JOIN performance_schema.EVENTS_WAITS_HISTORY USING (EVENT_ID)
+ORDER BY EVENT_ID
+LIMIT 1;
+ALTER TABLE t1 ADD COLUMN c INT;
+CREATE TRIGGER t_ps_trigger BEFORE INSERT ON t1
+FOR EACH ROW BEGIN
+SET NEW.c = (SELECT MAX(EVENT_ID)
+FROM performance_schema.EVENTS_WAITS_CURRENT);
+END;
+|
+INSERT INTO t1 (id) VALUES (11), (12), (13);
+SELECT id, c FROM t1 WHERE id > 10 ORDER BY c;
+id c
+11 [EVENT_ID]
+12 [EVENT_ID]
+13 [EVENT_ID]
+DROP TRIGGER t_ps_trigger;
+CREATE PROCEDURE t_ps_proc(IN tid INT, OUT pid INT)
+BEGIN
+SELECT id FROM performance_schema.PROCESSLIST
+WHERE THREAD_ID = tid INTO pid;
+END;
+|
+CALL t_ps_proc(0, @p_id);
+CREATE FUNCTION t_ps_func(tid INT) RETURNS int
+BEGIN
+return (SELECT id FROM performance_schema.PROCESSLIST
+WHERE THREAD_ID = tid);
+END;
+|
+SELECT t_ps_func(0) = @p_id;
+t_ps_func(0) = @p_id
+1
+DROP PROCEDURE t_ps_proc;
+DROP FUNCTION t_ps_func;
+DROP TABLE t1;
=== modified file 'mysql-test/suite/perfschema/t/ddl_cond_instances.test'
--- a/mysql-test/suite/perfschema/t/ddl_cond_instances.test 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_cond_instances.test 2009-11-19 12:59:38 +0000
@@ -16,10 +16,8 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.COND_INSTANCES add column foo integer;
-
--- error ER_WRONG_PERFSCHEMA_USAGE
-truncate table performance_schema.COND_INSTANCES;
-
+--let $table_name= COND_INSTANCES
+--let $column_name= NAME
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/ddl_events_waits_current.test'
--- a/mysql-test/suite/perfschema/t/ddl_events_waits_current.test 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_events_waits_current.test 2009-11-19 12:59:38 +0000
@@ -16,9 +16,9 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.EVENTS_WAITS_CURRENT add column foo integer;
-
-truncate table performance_schema.EVENTS_WAITS_CURRENT;
+--let $table_name= EVENTS_WAITS_CURRENT
+--let $column_name= EVENT_ID
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/ddl_events_waits_history.test'
--- a/mysql-test/suite/perfschema/t/ddl_events_waits_history.test 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_events_waits_history.test 2009-11-19 12:59:38 +0000
@@ -16,9 +16,9 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.EVENTS_WAITS_HISTORY add column foo integer;
-
-truncate table performance_schema.EVENTS_WAITS_HISTORY;
+--let $table_name= EVENTS_WAITS_HISTORY
+--let $column_name= EVENT_id
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/ddl_events_waits_history_long.test'
--- a/mysql-test/suite/perfschema/t/ddl_events_waits_history_long.test 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_events_waits_history_long.test 2009-11-19 12:59:38 +0000
@@ -16,9 +16,9 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.EVENTS_WAITS_HISTORY_LONG add column foo integer;
-
-truncate table performance_schema.EVENTS_WAITS_HISTORY_LONG;
+--let $table_name= EVENTS_WAITS_HISTORY_LONG
+--let $column_name= EVENT_ID
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/ddl_ews_by_event_name.test'
--- a/mysql-test/suite/perfschema/t/ddl_ews_by_event_name.test 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_ews_by_event_name.test 2009-11-19 12:59:38 +0000
@@ -16,9 +16,9 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.EVENTS_WAITS_SUMMARY_BY_EVENT_NAME add column foo integer;
-
-truncate table performance_schema.EVENTS_WAITS_SUMMARY_BY_EVENT_NAME;
+--let $table_name= EVENTS_WAITS_SUMMARY_BY_EVENT_NAME
+--let $column_name= EVENT_NAME
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/ddl_ews_by_instance.test'
--- a/mysql-test/suite/perfschema/t/ddl_ews_by_instance.test 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_ews_by_instance.test 2009-11-19 12:59:38 +0000
@@ -16,9 +16,9 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.EVENTS_WAITS_SUMMARY_BY_INSTANCE add column foo integer;
-
-truncate table performance_schema.EVENTS_WAITS_SUMMARY_BY_INSTANCE;
+--let $table_name= EVENTS_WAITS_SUMMARY_BY_INSTANCE
+--let $column_name= EVENT_NAME
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/ddl_ews_by_thread_by_event_name.test'
--- a/mysql-test/suite/perfschema/t/ddl_ews_by_thread_by_event_name.test 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_ews_by_thread_by_event_name.test 2009-11-19 12:59:38 +0000
@@ -16,10 +16,9 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME
- add column foo integer;
-
-truncate table performance_schema.EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME;
+--let $table_name= EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME
+--let $column_name= THREAD_ID
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/ddl_file_instances.test'
--- a/mysql-test/suite/perfschema/t/ddl_file_instances.test 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_file_instances.test 2009-11-19 12:59:38 +0000
@@ -16,10 +16,9 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.FILE_INSTANCES add column foo integer;
-
--- error ER_WRONG_PERFSCHEMA_USAGE
-truncate table performance_schema.FILE_INSTANCES;
+--let $table_name= FILE_INSTANCES
+--let $column_name= FILE_NAME
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/ddl_fs_by_event_name.test'
--- a/mysql-test/suite/perfschema/t/ddl_fs_by_event_name.test 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_fs_by_event_name.test 2009-11-19 12:59:38 +0000
@@ -16,9 +16,9 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.FILE_SUMMARY_BY_EVENT_NAME add column foo integer;
-
-truncate table performance_schema.FILE_SUMMARY_BY_EVENT_NAME;
+--let $table_name= FILE_SUMMARY_BY_EVENT_NAME
+--let $column_name= NAME
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/ddl_fs_by_instance.test'
--- a/mysql-test/suite/perfschema/t/ddl_fs_by_instance.test 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_fs_by_instance.test 2009-11-19 12:59:38 +0000
@@ -16,9 +16,8 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.FILE_SUMMARY_BY_INSTANCE add column foo integer;
-
-truncate table performance_schema.FILE_SUMMARY_BY_INSTANCE;
-
+--let $table_name= FILE_SUMMARY_BY_INSTANCE
+--let $column_name= NAME
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/ddl_mutex_instances.test'
--- a/mysql-test/suite/perfschema/t/ddl_mutex_instances.test 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_mutex_instances.test 2009-11-19 12:59:38 +0000
@@ -16,10 +16,9 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.MUTEX_INSTANCES add column foo integer;
-
--- error ER_WRONG_PERFSCHEMA_USAGE
-truncate table performance_schema.MUTEX_INSTANCES;
+--let $table_name= MUTEX_INSTANCES
+--let $column_name= NAME
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/ddl_performance_timers.test'
--- a/mysql-test/suite/perfschema/t/ddl_performance_timers.test 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_performance_timers.test 2009-11-19 12:59:38 +0000
@@ -16,10 +16,9 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.PERFORMANCE_TIMERS add column foo integer;
-
--- error ER_WRONG_PERFSCHEMA_USAGE
-truncate table performance_schema.PERFORMANCE_TIMERS;
+--let $table_name= PERFORMANCE_TIMERS
+--let $column_name= TIMER_NAME
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/ddl_processlist.test'
--- a/mysql-test/suite/perfschema/t/ddl_processlist.test 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_processlist.test 2009-11-19 12:59:38 +0000
@@ -16,10 +16,9 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.PROCESSLIST add column foo integer;
-
--- error ER_WRONG_PERFSCHEMA_USAGE
-truncate table performance_schema.PROCESSLIST;
+--let $table_name= PROCESSLIST
+--let $column_name= ID
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/ddl_rwlock_instances.test'
--- a/mysql-test/suite/perfschema/t/ddl_rwlock_instances.test 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_rwlock_instances.test 2009-11-19 12:59:38 +0000
@@ -16,10 +16,9 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.RWLOCK_INSTANCES add column foo integer;
-
--- error ER_WRONG_PERFSCHEMA_USAGE
-truncate table performance_schema.RWLOCK_INSTANCES;
+--let $table_name= RWLOCK_INSTANCES
+--let $column_name= NAME
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/ddl_setup_consumers.test'
--- a/mysql-test/suite/perfschema/t/ddl_setup_consumers.test 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_setup_consumers.test 2009-11-19 12:59:38 +0000
@@ -16,11 +16,9 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
---replace_result '\'setup_consumers' '\'SETUP_CONSUMERS'
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.SETUP_CONSUMERS add column foo integer;
-
--- error ER_WRONG_PERFSCHEMA_USAGE
-truncate table performance_schema.SETUP_CONSUMERS;
+--let $table_name= SETUP_CONSUMERS
+--let $column_name= NAME
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/ddl_setup_instruments.test'
--- a/mysql-test/suite/perfschema/t/ddl_setup_instruments.test 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_setup_instruments.test 2009-11-19 12:59:38 +0000
@@ -16,11 +16,9 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
---replace_result '\'setup_instruments' '\'SETUP_INSTRUMENTS'
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.SETUP_INSTRUMENTS add column foo integer;
-
--- error ER_WRONG_PERFSCHEMA_USAGE
-truncate table performance_schema.SETUP_INSTRUMENTS;
+--let $table_name= SETUP_INSTRUMENTS
+--let $column_name= NAME
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/ddl_setup_objects.test'
--- a/mysql-test/suite/perfschema/t/ddl_setup_objects.test 2009-09-25 15:01:11 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_setup_objects.test 2009-11-19 12:59:38 +0000
@@ -16,10 +16,9 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
---replace_result '\'setup_objects' '\'SETUP_OBJECTS'
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.SETUP_OBJECTS add column foo integer;
-
-truncate table performance_schema.SETUP_OBJECTS;
+--let $table_name= SETUP_OBJECTS
+--let $column_name= OBJECT_NAME
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/ddl_setup_timers.test'
--- a/mysql-test/suite/perfschema/t/ddl_setup_timers.test 2009-10-23 23:09:26 +0000
+++ b/mysql-test/suite/perfschema/t/ddl_setup_timers.test 2009-11-19 12:59:38 +0000
@@ -16,11 +16,9 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
---replace_result '\'setup_timers' '\'SETUP_TIMERS'
--- error ER_DBACCESS_DENIED_ERROR
-alter table performance_schema.SETUP_TIMERS add column foo integer;
-
--- error ER_WRONG_PERFSCHEMA_USAGE
-truncate table performance_schema.SETUP_TIMERS;
+--let $table_name= SETUP_TIMERS
+--let $column_name= NAME
+--source ../include/table_helper.inc
=== modified file 'mysql-test/suite/perfschema/t/disabled.def'
--- a/mysql-test/suite/perfschema/t/disabled.def 2009-11-14 05:05:21 +0000
+++ b/mysql-test/suite/perfschema/t/disabled.def 2009-11-19 12:59:38 +0000
@@ -25,3 +25,4 @@
#
##############################################################################
+func_storage_engine: BUG#45090 2009-06-26 Jorgen Austvik Disabled awaiting bugfix
\ No newline at end of file
=== added file 'mysql-test/suite/perfschema/t/func_file_io.test'
--- a/mysql-test/suite/perfschema/t/func_file_io.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/t/func_file_io.test 2009-11-19 12:59:38 +0000
@@ -0,0 +1,187 @@
+# Copyright (C) 2008-2009 Sun Microsystems, Inc
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+##
+## WL#4814, 4.1.4 FILE IO
+##
+## Functional testing of File IO
+##
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'NO', timed = 'YES';
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'YES'
+WHERE name LIKE 'wait/io/file/%';
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+#
+# TODO: Change to InnoDB when it gets instrumentation
+#
+
+CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value')
+ENGINE=MyISAM;
+
+INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY_LONG;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_CURRENT;
+
+#
+# FF1: Count for file should increase with instrumentation enabled and
+# FF2: Count for file should not increase with instrumentation disabled
+#
+
+SELECT * FROM t1 WHERE id = 1;
+
+SET @before_count = (SELECT SUM(TIMER_WAIT)
+ FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+ WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile')
+ AND (OBJECT_NAME LIKE '%/t1.MYD'));
+
+SELECT * FROM t1 WHERE id < 4;
+
+SET @after_count = (SELECT SUM(TIMER_WAIT)
+ FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+ WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile')
+ AND (OBJECT_NAME LIKE '%/t1.MYD') AND (1 = 1));
+
+SELECT IF((@after_count - @before_count) > 0, 'Success', 'Failure') test_ff1_timed;
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled='NO';
+
+SET @before_count = (SELECT SUM(TIMER_WAIT)
+ FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+ WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile')
+ AND (OBJECT_NAME LIKE '%/t1.MYD') AND (2 = 2));
+
+SELECT * FROM t1 WHERE id < 6;
+
+SET @after_count = (SELECT SUM(TIMER_WAIT)
+ FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+ WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile')
+ AND (OBJECT_NAME LIKE '%/t1.MYD') AND (3 = 3));
+
+SELECT IF((COALESCE(@after_count, 0) - COALESCE(@before_count, 0)) = 0, 'Success', 'Failure') test_ff2_timed;
+
+#
+# Check not timed measurements
+#
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'YES'
+WHERE name LIKE 'wait/io/file/%';
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET timed = 'NO';
+
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY_LONG;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_CURRENT;
+
+SELECT * FROM t1 WHERE id > 4;
+
+SELECT * FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+WHERE TIMER_WAIT != NULL
+ OR TIMER_START != NULL
+ OR TIMER_END != NULL;
+
+SELECT * FROM performance_schema.EVENTS_WAITS_HISTORY
+WHERE TIMER_WAIT != NULL
+ OR TIMER_START != NULL
+ OR TIMER_END != NULL;
+
+SELECT * FROM performance_schema.EVENTS_WAITS_CURRENT
+WHERE TIMER_WAIT != NULL
+ OR TIMER_START != NULL
+ OR TIMER_END != NULL;
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET timed = 'YES';
+
+SELECT * FROM t1 WHERE id < 4;
+
+DROP TABLE t1;
+
+#
+# FF4: Use-case from Enterprise Monitor
+#
+
+--disable_result_log
+SELECT SUM(COUNT_READ) AS sum_count_read,
+ SUM(COUNT_WRITE) AS sum_count_write,
+ SUM(SUM_NUMBER_OF_BYTES_READ) AS sum_num_bytes_read,
+ SUM(SUM_NUMBER_OF_BYTES_WRITE) AS sum_num_bytes_write
+FROM performance_schema.FILE_SUMMARY_BY_INSTANCE
+WHERE FILE_NAME LIKE CONCAT('%', @@tmpdir, '%') ORDER BY NULL;
+--enable_result_log
+
+#
+# FF5: Troubleshooting tasks
+#
+# These queries will give different results based on timing,
+# but at least they should not crash.
+#
+
+#
+# Total and average wait time for different events on system level
+#
+--disable_result_log
+SELECT EVENT_NAME, COUNT_STAR, AVG_TIMER_WAIT, SUM_TIMER_WAIT
+FROM performance_schema.EVENTS_WAITS_SUMMARY_BY_EVENT_NAME
+WHERE COUNT_STAR > 0
+ORDER BY SUM_TIMER_WAIT DESC
+LIMIT 10;
+--enable_result_log
+
+#
+# Total and average wait time for different users
+#
+
+--disable_result_log
+SELECT i.user, SUM(TIMER_WAIT) SUM_WAIT
+# ((TIME_TO_SEC(TIMEDIFF(NOW(), i.startup_time)) * 1000) / SUM(TIMER_WAIT)) * 100 WAIT_PERCENTAGE
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG h
+INNER JOIN performance_schema.PROCESSLIST p USING (THREAD_ID)
+LEFT JOIN information_schema.PROCESSLIST i USING (ID)
+GROUP BY i.user
+ORDER BY SUM_WAIT DESC
+LIMIT 20;
+--enable_result_log
+
+#
+# Total and average wait times for different events for a session
+#
+--disable_result_log
+SELECT h.EVENT_NAME, SUM(h.TIMER_WAIT) TOTAL_WAIT
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG h
+INNER JOIN performance_schema.PROCESSLIST p USING (THREAD_ID)
+WHERE p.ID = 1
+GROUP BY h.EVENT_NAME
+HAVING TOTAL_WAIT > 0;
+--enable_result_log
+
+#
+# Which user reads and writes data
+#
+
+--disable_result_log
+SELECT i.user, h.operation, SUM(NUMBER_OF_BYTES) bytes
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG h
+INNER JOIN performance_schema.PROCESSLIST p USING (THREAD_ID)
+LEFT JOIN information_schema.PROCESSLIST i USING (ID)
+GROUP BY i.user, h.operation
+HAVING BYTES > 0
+ORDER BY i.user, h.operation;
+--enable_result_log
=== added file 'mysql-test/suite/perfschema/t/func_mutex.test'
--- a/mysql-test/suite/perfschema/t/func_mutex.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/t/func_mutex.test 2009-11-19 12:59:38 +0000
@@ -0,0 +1,128 @@
+# Copyright (C) 2008-2009 Sun Microsystems, Inc
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+##
+## WL#4818, 4.1.3 MUTEXES, RW-LOCKS, ...
+##
+## Functional testing of mutexes and RW-locks
+##
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'NO', timed = 'YES';
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'YES'
+WHERE name LIKE 'wait/synch/mutex/%'
+ OR name LIKE 'wait/synch/rwlock/%';
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+#
+# TODO: Change to InnoDB when it gets instrumentation
+#
+
+CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value')
+ENGINE=MyISAM;
+
+INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+
+#
+# FM1: Count for mutex should increase with instrumentation enabled and
+# FM2: Count for mutex should not increase with instrumentation disabled
+#
+
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY_LONG;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_CURRENT;
+
+SELECT * FROM t1 WHERE id = 1;
+
+SET @before_count = (SELECT SUM(TIMER_WAIT)
+ FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+ WHERE (EVENT_NAME = 'wait/synch/mutex/sql/LOCK_open'));
+
+SELECT * FROM t1;
+
+SET @after_count = (SELECT SUM(TIMER_WAIT)
+ FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+ WHERE (EVENT_NAME = 'wait/synch/mutex/sql/LOCK_open'));
+
+SELECT IF((@after_count - @before_count) > 0, 'Success', 'Failure') test_fm1_timed;
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'NO'
+WHERE NAME = 'wait/synch/mutex/sql/LOCK_open';
+
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY_LONG;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_CURRENT;
+
+SELECT * FROM t1 WHERE id = 1;
+
+SET @before_count = (SELECT SUM(TIMER_WAIT)
+ FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+ WHERE (EVENT_NAME = 'wait/synch/mutex/sql/LOCK_open'));
+
+SELECT * FROM t1;
+
+SET @after_count = (SELECT SUM(TIMER_WAIT)
+ FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+ WHERE (EVENT_NAME = 'wait/synch/mutex/sql/LOCK_open'));
+
+SELECT IF((COALESCE(@after_count, 0) - COALESCE(@before_count, 0)) = 0, 'Success', 'Failure') test_fm2_timed;
+
+#
+# Repeat for RW-lock
+#
+
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY_LONG;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_CURRENT;
+
+SELECT * FROM t1 WHERE id = 1;
+
+SET @before_count = (SELECT SUM(TIMER_WAIT)
+ FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+ WHERE (EVENT_NAME = 'wait/synch/rwlock/sql/LOCK_grant'));
+
+SELECT * FROM t1;
+
+SET @after_count = (SELECT SUM(TIMER_WAIT)
+ FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+ WHERE (EVENT_NAME = 'wait/synch/rwlock/sql/LOCK_grant'));
+
+SELECT IF((@after_count - @before_count) > 0, 'Success', 'Failure') test_fm1_rw_timed;
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'NO'
+WHERE NAME = 'wait/synch/rwlock/sql/LOCK_grant';
+
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY_LONG;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_CURRENT;
+
+SELECT * FROM t1 WHERE id = 1;
+
+SET @before_count = (SELECT SUM(TIMER_WAIT)
+ FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+ WHERE (EVENT_NAME = 'wait/synch/rwlock/sql/LOCK_grant'));
+
+SELECT * FROM t1;
+
+SET @after_count = (SELECT SUM(TIMER_WAIT)
+ FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+ WHERE (EVENT_NAME = 'wait/synch/rwlock/sql/LOCK_grant'));
+
+SELECT IF((COALESCE(@after_count, 0) - COALESCE(@before_count, 0)) = 0, 'Success', 'Failure') test_fm2_rw_timed;
+
+DROP TABLE t1;
=== added file 'mysql-test/suite/perfschema/t/func_storage_engine.test'
--- a/mysql-test/suite/perfschema/t/func_storage_engine.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/t/func_storage_engine.test 2009-11-19 12:59:38 +0000
@@ -0,0 +1,41 @@
+# Copyright (C) 2008-2009 Sun Microsystems, Inc
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+##
+## WL#4818, 4.1.2 STORAGE ENGINE
+##
+## Functional testing of the performance schema storage engine
+##
+
+#
+# FSE3: On startup all instruments are globally disabled
+#
+
+SHOW VARIABLES LIKE 'performance_schema';
+SELECT * FROM performance_schema.SETUP_INSTRUMENTS WHERE ENABLED = 'YES';
+SELECT * FROM performance_schema.SETUP_CONSUMERS WHERE ENABLED = 'YES';
+
+#
+# FSE4: There should be no rows in EVENTS_WAITS_CURRENT if
+# instrumentation is disabled
+#
+
+SELECT COUNT(*) FROM performance_schema.EVENTS_WAITS_CURRENT;
+SELECT COUNT(*) FROM performance_schema.EVENTS_WAITS_HISTORY;
+SELECT COUNT(*) FROM performance_schema.EVENTS_WAITS_HISTORY_LONG;
+
+#
+# FSE5: Warning if binlog is enabled is tested in binlog_stmt.test
+#
=== modified file 'mysql-test/suite/perfschema/t/privilege.test'
--- a/mysql-test/suite/perfschema/t/privilege.test 2009-11-14 05:05:21 +0000
+++ b/mysql-test/suite/perfschema/t/privilege.test 2009-11-19 12:59:38 +0000
@@ -16,6 +16,7 @@
# Tests for PERFORMANCE_SCHEMA
--source include/not_embedded.inc
+--source include/have_lowercase0.inc
show grants;
@@ -243,3 +244,100 @@ drop user 'pfs_user_2'@localhost;
drop user 'pfs_user_3'@localhost;
flush privileges;
+--echo # Test cases from WL#4818
+--echo # Setup user
+
+CREATE user pfs_user_4;
+--connect (pfs_user_4, localhost, pfs_user_4,,)
+
+--echo #
+--echo # WL#4818, NFS4: Normal user does not have access to view data
+--echo # without grants
+--echo #
+
+--connection pfs_user_4
+--echo # Select as pfs_user_4 should fail without grant
+
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT event_id FROM performance_schema.events_waits_history;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT event_id FROM performance_schema.events_waits_history_long;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT event_id FROM performance_schema.events_waits_current;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT event_name FROM performance_schema.events_waits_summary_by_instance;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT event_name FROM performance_schema.file_summary_by_instance;
+
+--echo #
+--echo # WL#4818, NFS3: Normal user does not have access to change what is
+--echo # instrumented without grants
+--echo #
+
+--connection pfs_user_4
+--echo # User pfs_user_4 should not be allowed to tweak instrumentation without
+--echo # explicit grant
+
+--error ER_TABLEACCESS_DENIED_ERROR
+UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'YES';
+
+--error ER_TABLEACCESS_DENIED_ERROR
+UPDATE performance_schema.setup_instruments SET enabled = 'YES'
+WHERE name LIKE 'wait/synch/mutex/%'
+ OR name LIKE 'wait/synch/rwlock/%';
+
+--error ER_TABLEACCESS_DENIED_ERROR
+UPDATE performance_schema.setup_consumers SET enabled = 'YES';
+
+--error ER_TABLEACCESS_DENIED_ERROR
+UPDATE performance_schema.setup_timers SET timer_name = 'TICK';
+
+--error ER_TABLEACCESS_DENIED_ERROR
+TRUNCATE TABLE performance_schema.events_waits_history_long;
+--error ER_TABLEACCESS_DENIED_ERROR
+TRUNCATE TABLE performance_schema.events_waits_history;
+--error ER_TABLEACCESS_DENIED_ERROR
+TRUNCATE TABLE performance_schema.events_waits_current;
+
+--echo #
+--echo # WL#4814, NFS1: Can use grants to give normal user access
+--echo # to turn on and off instrumentation
+--echo #
+
+--connection default
+--echo # Grant access to change tables with the root account
+
+GRANT UPDATE ON performance_schema.SETUP_CONSUMERS TO pfs_user_4;
+GRANT UPDATE ON performance_schema.SETUP_TIMERS TO pfs_user_4;
+GRANT UPDATE, SELECT ON performance_schema.SETUP_INSTRUMENTS TO pfs_user_4;
+GRANT DROP ON performance_schema.EVENTS_WAITS_CURRENT TO pfs_user_4;
+GRANT DROP ON performance_schema.EVENTS_WAITS_HISTORY TO pfs_user_4;
+GRANT DROP ON performance_schema.EVENTS_WAITS_HISTORY_LONG TO pfs_user_4;
+
+--connection pfs_user_4
+--echo # User pfs_user_4 should now be allowed to tweak instrumentation
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'NO', timed = 'YES';
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'YES'
+WHERE name LIKE 'wait/synch/mutex/%'
+ OR name LIKE 'wait/synch/rwlock/%';
+
+UPDATE performance_schema.SETUP_CONSUMERS SET enabled = 'YES';
+
+UPDATE performance_schema.SETUP_TIMERS SET timer_name = 'TICK';
+
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY_LONG;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_HISTORY;
+TRUNCATE TABLE performance_schema.EVENTS_WAITS_CURRENT;
+
+--echo # Clean up
+
+--connection default
+--disconnect pfs_user_4
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM pfs_user_4;
+DROP USER pfs_user_4;
=== added file 'mysql-test/suite/perfschema/t/selects.test'
--- a/mysql-test/suite/perfschema/t/selects.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/t/selects.test 2009-11-19 12:59:38 +0000
@@ -0,0 +1,153 @@
+# Copyright (C) 2008-2009 Sun Microsystems, Inc
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+#
+# WL#4814, 4.1.2 STORAGE ENGINE, FSE8: Selects
+#
+
+# Make some data that we can work on:
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'YES', timed = 'YES';
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value')
+ENGINE=MyISAM;
+INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+
+# ORDER BY, GROUP BY and HAVING
+
+--replace_column 2 [NUM_BYTES]
+SELECT OPERATION, SUM(NUMBER_OF_BYTES) AS TOTAL
+FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+GROUP BY OPERATION
+HAVING TOTAL IS NOT NULL
+ORDER BY OPERATION
+LIMIT 1;
+
+# Sub SELECT
+--replace_column 1 [EVENT_ID]
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_CURRENT
+WHERE THREAD_ID IN
+ (SELECT THREAD_ID FROM performance_schema.PROCESSLIST)
+AND EVENT_NAME IN
+ (SELECT NAME FROM performance_schema.SETUP_INSTRUMENTS
+ WHERE NAME LIKE "wait/synch/%")
+LIMIT 1;
+
+# JOIN
+
+--replace_column 1 [EVENT_ID]
+SELECT DISTINCT EVENT_ID
+FROM performance_schema.EVENTS_WAITS_CURRENT
+JOIN performance_schema.EVENTS_WAITS_HISTORY USING (EVENT_ID)
+JOIN performance_schema.EVENTS_WAITS_HISTORY_LONG USING (EVENT_ID)
+ORDER BY EVENT_ID
+LIMIT 1;
+
+# Self JOIN
+
+--replace_column 1 [THREAD_ID] 2 [EVENT_ID] 3 [EVENT_NAME] 4 [TIMER_WAIT]
+SELECT t1.THREAD_ID, t2.EVENT_ID, t3.EVENT_NAME, t4.TIMER_WAIT
+FROM performance_schema.EVENTS_WAITS_HISTORY t1
+JOIN performance_schema.EVENTS_WAITS_HISTORY t2 USING (EVENT_ID)
+JOIN performance_schema.EVENTS_WAITS_HISTORY t3 ON (t2.THREAD_ID = t3.THREAD_ID)
+JOIN performance_schema.EVENTS_WAITS_HISTORY t4 ON (t3.EVENT_NAME = t4.EVENT_NAME)
+ORDER BY t1.EVENT_ID, t2.EVENT_ID
+LIMIT 5;
+
+# UNION
+--replace_column 1 [THREAD_ID] 2 [EVENT_ID]
+SELECT THREAD_ID, EVENT_ID FROM (
+SELECT THREAD_ID, EVENT_ID FROM performance_schema.EVENTS_WAITS_CURRENT
+UNION
+SELECT THREAD_ID, EVENT_ID FROM performance_schema.EVENTS_WAITS_HISTORY
+UNION
+SELECT THREAD_ID, EVENT_ID FROM performance_schema.EVENTS_WAITS_HISTORY_LONG
+) t1 ORDER BY THREAD_ID, EVENT_ID
+LIMIT 5;
+
+# EVENT
+
+CREATE EVENT t_ps_event
+ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
+DO SELECT DISTINCT EVENT_ID
+ FROM performance_schema.EVENTS_WAITS_CURRENT
+ JOIN performance_schema.EVENTS_WAITS_HISTORY USING (EVENT_ID)
+ ORDER BY EVENT_ID
+ LIMIT 1;
+
+--sleep 2
+
+# TRIGGER
+
+ALTER TABLE t1 ADD COLUMN c INT;
+
+delimiter |;
+
+CREATE TRIGGER t_ps_trigger BEFORE INSERT ON t1
+ FOR EACH ROW BEGIN
+ SET NEW.c = (SELECT MAX(EVENT_ID)
+ FROM performance_schema.EVENTS_WAITS_CURRENT);
+ END;
+|
+
+delimiter ;|
+
+INSERT INTO t1 (id) VALUES (11), (12), (13);
+
+--replace_column 2 [EVENT_ID]
+SELECT id, c FROM t1 WHERE id > 10 ORDER BY c;
+
+DROP TRIGGER t_ps_trigger;
+
+# PROCEDURE
+
+delimiter |;
+
+CREATE PROCEDURE t_ps_proc(IN tid INT, OUT pid INT)
+BEGIN
+ SELECT id FROM performance_schema.PROCESSLIST
+ WHERE THREAD_ID = tid INTO pid;
+END;
+
+|
+
+delimiter ;|
+
+CALL t_ps_proc(0, @p_id);
+
+# FUNCTION
+
+delimiter |;
+
+CREATE FUNCTION t_ps_func(tid INT) RETURNS int
+BEGIN
+ return (SELECT id FROM performance_schema.PROCESSLIST
+ WHERE THREAD_ID = tid);
+END;
+
+|
+
+delimiter ;|
+
+SELECT t_ps_func(0) = @p_id;
+
+DROP PROCEDURE t_ps_proc;
+DROP FUNCTION t_ps_func;
+
+# Clean up
+DROP TABLE t1;
=== added directory 'mysql-test/suite/perfschema_stress'
=== added file 'mysql-test/suite/perfschema_stress/README'
--- a/mysql-test/suite/perfschema_stress/README 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema_stress/README 2009-11-19 12:59:38 +0000
@@ -0,0 +1,20 @@
+
+WL#4818 - Stress testing of PERFORMANCE_SCHEMA
+
+The performance schema storage engine is backed by data structures, we
+want to be sure that these data structures are not fragile under
+load.
+
+NFS1: 20 threads doing read of performance data (both mutex and
+disk), 5 threads changing what is being instrumented, 25 threads doing
+DML and DDL so that the statistics are updated. Connections should frequently
+disconnect or be KILLed, and reconnect.
+
+The database should not crash under any circumstance.
+
+How to run:
+
+MTR_VERSION=1 ./mtr --stress \
+ --stress-suite=perfschema_stress \
+ --stress-threads=50 \
+ --stress-test-duration=3600
=== added directory 'mysql-test/suite/perfschema_stress/include'
=== added file 'mysql-test/suite/perfschema_stress/include/settings.inc'
--- a/mysql-test/suite/perfschema_stress/include/settings.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema_stress/include/settings.inc 2009-11-19 12:59:38 +0000
@@ -0,0 +1,17 @@
+# Copyright (C) 2009 Sun Microsystems, Inc
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+let $num_stress_rows= 5000;
+let $default_engine_type= myisam;
=== added directory 'mysql-test/suite/perfschema_stress/r'
=== added file 'mysql-test/suite/perfschema_stress/r/modify.result'
--- a/mysql-test/suite/perfschema_stress/r/modify.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema_stress/r/modify.result 2009-11-19 12:59:38 +0000
@@ -0,0 +1,17 @@
+UPDATE performance_schema.SETUP_INSTRUMENTS SET TIMED = 'NO';
+UPDATE performance_schema.SETUP_INSTRUMENTS SET ENABLED = 'NO';
+UPDATE performance_schema.SETUP_TIMERS
+SET TIMER_NAME = 'NANOSECOND' WHERE NAME = 'Wait';
+UPDATE performance_schema.SETUP_TIMERS
+SET TIMER_NAME = 'CYCLE' WHERE NAME = 'Wait';
+UPDATE performance_schema.SETUP_INSTRUMENTS SET TIMED = 'YES';
+UPDATE performance_schema.SETUP_INSTRUMENTS SET ENABLED = 'YES'
+WHERE NAME IN ('wait/io/file/sql/FRM',
+'thread/sql/Connection',
+'wait/synch/cond/sql/COND_mdl',
+'wait/synch/rwlock/sql/LOCK_sys_init_connect',
+'wait/synch/mutex/sql/LOCK_mdl');
+UPDATE performance_schema.SETUP_CONSUMERS SET ENABLED = 'NO'
+WHERE NAME = 'events_waits_history';
+UPDATE performance_schema.SETUP_CONSUMERS SET ENABLED = 'YES'
+WHERE NAME = 'events_waits_history';
=== added file 'mysql-test/suite/perfschema_stress/r/read.result'
--- a/mysql-test/suite/perfschema_stress/r/read.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema_stress/r/read.result 2009-11-19 12:59:38 +0000
@@ -0,0 +1,29 @@
+SELECT * FROM performance_schema.SETUP_INSTRUMENTS
+WHERE ENABLED='NO' AND TIMED='NO';
+NAME ENABLED TIMED
+SELECT * FROM performance_schema.EVENTS_WAITS_CURRENT
+WHERE (TIMER_END - TIMER_START != TIMER_WAIT);
+THREAD_ID EVENT_ID EVENT_NAME SOURCE TIMER_START TIMER_END TIMER_WAIT SPINS OBJECT_SCHEMA OBJECT_NAME OBJECT_TYPE OBJECT_INSTANCE_BEGIN NESTING_EVENT_ID OPERATION NUMBER_OF_BYTES FLAGS
+SELECT * FROM performance_schema.EVENTS_WAITS_HISTORY
+WHERE SPINS != NULL;
+THREAD_ID EVENT_ID EVENT_NAME SOURCE TIMER_START TIMER_END TIMER_WAIT SPINS OBJECT_SCHEMA OBJECT_NAME OBJECT_TYPE OBJECT_INSTANCE_BEGIN NESTING_EVENT_ID OPERATION NUMBER_OF_BYTES FLAGS
+SELECT * FROM performance_schema.PROCESSLIST p,
+performance_schema.EVENTS_WAITS_CURRENT e
+WHERE p.THREAD_ID = e.THREAD_ID
+AND TIMER_START = 0
+ORDER BY e.EVENT_ID;
+THREAD_ID ID NAME THREAD_ID EVENT_ID EVENT_NAME SOURCE TIMER_START TIMER_END TIMER_WAIT SPINS OBJECT_SCHEMA OBJECT_NAME OBJECT_TYPE OBJECT_INSTANCE_BEGIN NESTING_EVENT_ID OPERATION NUMBER_OF_BYTES FLAGS
+SELECT * FROM performance_schema.EVENTS_WAITS_CURRENT
+WHERE THREAD_ID IN (SELECT THREAD_ID
+FROM performance_schema.PROCESSLIST
+ORDER BY THREAD_ID)
+AND TIMER_END = 0
+AND TIMER_WAIT != NULL
+ORDER BY EVENT_ID;
+THREAD_ID EVENT_ID EVENT_NAME SOURCE TIMER_START TIMER_END TIMER_WAIT SPINS OBJECT_SCHEMA OBJECT_NAME OBJECT_TYPE OBJECT_INSTANCE_BEGIN NESTING_EVENT_ID OPERATION NUMBER_OF_BYTES FLAGS
+SELECT SUM(COUNT_READ) AS sum_count_read,
+SUM(COUNT_WRITE) AS sum_count_write,
+SUM(SUM_NUMBER_OF_BYTES_READ) AS sum_num_bytes_read,
+SUM(SUM_NUMBER_OF_BYTES_WRITE) AS sum_num_bytes_write
+FROM performance_schema.FILE_SUMMARY_BY_INSTANCE
+WHERE FILE_NAME LIKE CONCAT('%', @@tmpdir, '%') ORDER BY NULL;
=== added file 'mysql-test/suite/perfschema_stress/r/work.result'
--- a/mysql-test/suite/perfschema_stress/r/work.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema_stress/r/work.result 2009-11-19 12:59:38 +0000
@@ -0,0 +1,16 @@
+SET @rowid = ROUND(RAND() * <num_stress_rows>);
+START TRANSACTION;
+UPDATE t1 SET b = 'changed' WHERE id=@rowid;
+SELECT b FROM t1 WHERE id=@rowid;
+b
+changed
+COMMIT;
+START TRANSACTION;
+DELETE FROM t1 WHERE id=@rowid;
+INSERT INTO t1 (id, b) VALUES (@rowid, 'newly_inserted');
+SELECT b FROM t1 WHERE id=@rowid;
+b
+newly_inserted
+COMMIT;
+CREATE TABLE tw_<conn_nr> AS SELECT * FROM t1;
+DROP TABLE tw_<conn_nr>;
=== added file 'mysql-test/suite/perfschema_stress/stress_init.txt'
--- a/mysql-test/suite/perfschema_stress/stress_init.txt 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema_stress/stress_init.txt 2009-11-19 12:59:38 +0000
@@ -0,0 +1 @@
+setup
=== added file 'mysql-test/suite/perfschema_stress/stress_tests.txt'
--- a/mysql-test/suite/perfschema_stress/stress_tests.txt 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema_stress/stress_tests.txt 2009-11-19 12:59:38 +0000
@@ -0,0 +1,10 @@
+read
+read
+read
+read
+modify
+work
+work
+work
+work
+work
=== added directory 'mysql-test/suite/perfschema_stress/t'
=== added file 'mysql-test/suite/perfschema_stress/t/modify.test'
--- a/mysql-test/suite/perfschema_stress/t/modify.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema_stress/t/modify.test 2009-11-19 12:59:38 +0000
@@ -0,0 +1,55 @@
+# Copyright (C) 2009 Sun Microsystems, Inc
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+--source include/not_embedded.inc
+--source suite/perfschema_stress/include/settings.inc
+
+let $have_table= `SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'`;
+
+if (`SELECT ($have_table = 0)`) {
+ --source suite/perfschema_stress/t/setup.test
+}
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET TIMED = 'NO';
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET ENABLED = 'NO';
+
+UPDATE performance_schema.SETUP_TIMERS
+SET TIMER_NAME = 'NANOSECOND' WHERE NAME = 'Wait';
+
+# Let it run some time with the new timer name and instruments
+--sleep 1
+
+UPDATE performance_schema.SETUP_TIMERS
+SET TIMER_NAME = 'CYCLE' WHERE NAME = 'Wait';
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET TIMED = 'YES';
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET ENABLED = 'YES'
+WHERE NAME IN ('wait/io/file/sql/FRM',
+ 'thread/sql/Connection',
+ 'wait/synch/cond/sql/COND_mdl',
+ 'wait/synch/rwlock/sql/LOCK_sys_init_connect',
+ 'wait/synch/mutex/sql/LOCK_mdl');
+
+# Two short lived changes to see that switching does not lead
+# to havoc.
+
+UPDATE performance_schema.SETUP_CONSUMERS SET ENABLED = 'NO'
+WHERE NAME = 'events_waits_history';
+
+UPDATE performance_schema.SETUP_CONSUMERS SET ENABLED = 'YES'
+WHERE NAME = 'events_waits_history';
=== added file 'mysql-test/suite/perfschema_stress/t/read.test'
--- a/mysql-test/suite/perfschema_stress/t/read.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema_stress/t/read.test 2009-11-19 12:59:38 +0000
@@ -0,0 +1,49 @@
+# Copyright (C) 2009 Sun Microsystems, Inc
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+--source include/not_embedded.inc
+
+SELECT * FROM performance_schema.SETUP_INSTRUMENTS
+WHERE ENABLED='NO' AND TIMED='NO';
+
+SELECT * FROM performance_schema.EVENTS_WAITS_CURRENT
+WHERE (TIMER_END - TIMER_START != TIMER_WAIT);
+
+SELECT * FROM performance_schema.EVENTS_WAITS_HISTORY
+WHERE SPINS != NULL;
+
+SELECT * FROM performance_schema.PROCESSLIST p,
+ performance_schema.EVENTS_WAITS_CURRENT e
+WHERE p.THREAD_ID = e.THREAD_ID
+ AND TIMER_START = 0
+ORDER BY e.EVENT_ID;
+
+SELECT * FROM performance_schema.EVENTS_WAITS_CURRENT
+WHERE THREAD_ID IN (SELECT THREAD_ID
+ FROM performance_schema.PROCESSLIST
+ ORDER BY THREAD_ID)
+ AND TIMER_END = 0
+ AND TIMER_WAIT != NULL
+ORDER BY EVENT_ID;
+
+--disable_result_log
+SELECT SUM(COUNT_READ) AS sum_count_read,
+ SUM(COUNT_WRITE) AS sum_count_write,
+ SUM(SUM_NUMBER_OF_BYTES_READ) AS sum_num_bytes_read,
+ SUM(SUM_NUMBER_OF_BYTES_WRITE) AS sum_num_bytes_write
+FROM performance_schema.FILE_SUMMARY_BY_INSTANCE
+WHERE FILE_NAME LIKE CONCAT('%', @@tmpdir, '%') ORDER BY NULL;
+--enable_result_log
+
=== added file 'mysql-test/suite/perfschema_stress/t/setup.test'
--- a/mysql-test/suite/perfschema_stress/t/setup.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema_stress/t/setup.test 2009-11-19 12:59:38 +0000
@@ -0,0 +1,64 @@
+# Copyright (C) 2009 Sun Microsystems, Inc
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+if (`SELECT VERSION() LIKE '%embedded%'`)
+{
+ --skip This test cannot run with the embedded server.
+}
+
+--source suite/perfschema_stress/include/settings.inc
+
+--disable_query_log
+--disable_result_log
+
+if (`SELECT LENGTH('$engine_type') = 0`) {
+ let $engine_type= $default_engine_type;
+}
+if (`SELECT '$engine_type' = 'Falcon'`) {
+ --source include/have_falcon.inc
+}
+
+--replace_result $engine_type <engine_type>
+eval CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value')
+ ENGINE=$engine_type;
+
+# Need something to start with
+INSERT INTO t1(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
+
+# Bulk load the correct number of rows
+while (`SELECT MAX(id) < $num_stress_rows FROM t1`)
+{
+ SELECT MAX(id) FROM t1 INTO @max;
+--replace_result $num_stress_rows <num_stress_rows>
+ eval INSERT INTO t1(id) SELECT id + @max FROM t1
+ WHERE id + @max <= $num_stress_rows;
+}
+
+# Turn on some instruments
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET TIMED = 'YES';
+
+UPDATE performance_schema.SETUP_INSTRUMENTS SET ENABLED = 'NO';
+UPDATE performance_schema.SETUP_INSTRUMENTS SET ENABLED = 'YES'
+WHERE NAME IN ('wait/io/file/sql/FRM',
+ 'thread/sql/Connection',
+ 'wait/synch/cond/sql/COND_mdl',
+ 'wait/synch/rwlock/sql/LOCK_sys_init_connect',
+ 'wait/synch/mutex/sql/LOCK_mdl');
+
+UPDATE performance_schema.SETUP_CONSUMERS SET ENABLED = 'YES';
+
+--enable_result_log
+--enable_query_log
=== added file 'mysql-test/suite/perfschema_stress/t/work.test'
--- a/mysql-test/suite/perfschema_stress/t/work.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema_stress/t/work.test 2009-11-19 12:59:38 +0000
@@ -0,0 +1,47 @@
+# Copyright (C) 2009 Sun Microsystems, Inc
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+--source include/not_embedded.inc
+--source suite/perfschema_stress/include/settings.inc
+
+let $have_table= `SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'`;
+
+if (`SELECT ($have_table = 0)`) {
+ --source suite/perfschema_stress/t/setup.test
+}
+
+--replace_result $num_stress_rows <num_stress_rows>
+eval SET @rowid = ROUND(RAND() * $num_stress_rows);
+
+let $conn_id= `SELECT CONNECTION_ID()`;
+let $conn_nr= conn_$conn_id;
+
+START TRANSACTION;
+UPDATE t1 SET b = 'changed' WHERE id=@rowid;
+SELECT b FROM t1 WHERE id=@rowid;
+COMMIT;
+
+START TRANSACTION;
+DELETE FROM t1 WHERE id=@rowid;
+INSERT INTO t1 (id, b) VALUES (@rowid, 'newly_inserted');
+SELECT b FROM t1 WHERE id=@rowid;
+COMMIT;
+
+--replace_result $conn_nr <conn_nr>
+eval CREATE TABLE tw_$conn_nr AS SELECT * FROM t1;
+
+--replace_result $conn_nr <conn_nr>
+eval DROP TABLE tw_$conn_nr;
Attachment: [text/bzr-bundle] bzr/jorgen.austvik@sun.com-20091119125938-4qccodvfkctswguc.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk-perfschema branch (jorgen.austvik:2959) WL#4818 | Jorgen Austvik | 19 Nov |
