diff options
Diffstat (limited to 'mysql-test/suite/sysschema/r')
90 files changed, 3584 insertions, 0 deletions
diff --git a/mysql-test/suite/sysschema/r/all_sys_objects_exist.result b/mysql-test/suite/sysschema/r/all_sys_objects_exist.result new file mode 100644 index 00000000..6dddd8a1 --- /dev/null +++ b/mysql-test/suite/sysschema/r/all_sys_objects_exist.result @@ -0,0 +1,159 @@ +USE sys; +SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'sys' ORDER BY TABLE_NAME; +TABLE_NAME +host_summary +host_summary_by_file_io +host_summary_by_file_io_type +host_summary_by_stages +host_summary_by_statement_latency +host_summary_by_statement_type +innodb_buffer_stats_by_schema +innodb_buffer_stats_by_table +innodb_lock_waits +io_by_thread_by_latency +io_global_by_file_by_bytes +io_global_by_file_by_latency +io_global_by_wait_by_bytes +io_global_by_wait_by_latency +latest_file_io +memory_by_host_by_current_bytes +memory_by_thread_by_current_bytes +memory_by_user_by_current_bytes +memory_global_by_current_bytes +memory_global_total +metrics +processlist +ps_check_lost_instrumentation +schema_auto_increment_columns +schema_index_statistics +schema_object_overview +schema_redundant_indexes +schema_tables_with_full_table_scans +schema_table_lock_waits +schema_table_statistics +schema_table_statistics_with_buffer +schema_unused_indexes +session +session_ssl_status +statements_with_errors_or_warnings +statements_with_full_table_scans +statements_with_runtimes_in_95th_percentile +statements_with_sorting +statements_with_temp_tables +statement_analysis +sys_config +user_summary +user_summary_by_file_io +user_summary_by_file_io_type +user_summary_by_stages +user_summary_by_statement_latency +user_summary_by_statement_type +version +waits_by_host_by_latency +waits_by_user_by_latency +waits_global_by_latency +wait_classes_global_by_avg_latency +wait_classes_global_by_latency +x$host_summary +x$host_summary_by_file_io +x$host_summary_by_file_io_type +x$host_summary_by_stages +x$host_summary_by_statement_latency +x$host_summary_by_statement_type +x$innodb_buffer_stats_by_schema +x$innodb_buffer_stats_by_table +x$innodb_lock_waits +x$io_by_thread_by_latency +x$io_global_by_file_by_bytes +x$io_global_by_file_by_latency +x$io_global_by_wait_by_bytes +x$io_global_by_wait_by_latency +x$latest_file_io +x$memory_by_host_by_current_bytes +x$memory_by_thread_by_current_bytes +x$memory_by_user_by_current_bytes +x$memory_global_by_current_bytes +x$memory_global_total +x$processlist +x$ps_digest_95th_percentile_by_avg_us +x$ps_digest_avg_latency_distribution +x$ps_schema_table_statistics_io +x$schema_flattened_keys +x$schema_index_statistics +x$schema_tables_with_full_table_scans +x$schema_table_lock_waits +x$schema_table_statistics +x$schema_table_statistics_with_buffer +x$session +x$statements_with_errors_or_warnings +x$statements_with_full_table_scans +x$statements_with_runtimes_in_95th_percentile +x$statements_with_sorting +x$statements_with_temp_tables +x$statement_analysis +x$user_summary +x$user_summary_by_file_io +x$user_summary_by_file_io_type +x$user_summary_by_stages +x$user_summary_by_statement_latency +x$user_summary_by_statement_type +x$waits_by_host_by_latency +x$waits_by_user_by_latency +x$waits_global_by_latency +x$wait_classes_global_by_avg_latency +x$wait_classes_global_by_latency +SELECT ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'sys' ORDER BY ROUTINE_TYPE, ROUTINE_NAME; +ROUTINE_NAME ROUTINE_TYPE +extract_schema_from_file_name FUNCTION +extract_table_from_file_name FUNCTION +format_bytes FUNCTION +format_path FUNCTION +format_statement FUNCTION +format_time FUNCTION +list_add FUNCTION +list_drop FUNCTION +ps_is_account_enabled FUNCTION +ps_is_consumer_enabled FUNCTION +ps_is_instrument_default_enabled FUNCTION +ps_is_instrument_default_timed FUNCTION +ps_is_thread_instrumented FUNCTION +ps_thread_account FUNCTION +ps_thread_id FUNCTION +ps_thread_stack FUNCTION +ps_thread_trx_info FUNCTION +quote_identifier FUNCTION +sys_get_config FUNCTION +version_major FUNCTION +version_minor FUNCTION +version_patch FUNCTION +create_synonym_db PROCEDURE +diagnostics PROCEDURE +execute_prepared_stmt PROCEDURE +ps_setup_disable_background_threads PROCEDURE +ps_setup_disable_consumer PROCEDURE +ps_setup_disable_instrument PROCEDURE +ps_setup_disable_thread PROCEDURE +ps_setup_enable_background_threads PROCEDURE +ps_setup_enable_consumer PROCEDURE +ps_setup_enable_instrument PROCEDURE +ps_setup_enable_thread PROCEDURE +ps_setup_reload_saved PROCEDURE +ps_setup_reset_to_default PROCEDURE +ps_setup_save PROCEDURE +ps_setup_show_disabled PROCEDURE +ps_setup_show_disabled_consumers PROCEDURE +ps_setup_show_disabled_instruments PROCEDURE +ps_setup_show_enabled PROCEDURE +ps_setup_show_enabled_consumers PROCEDURE +ps_setup_show_enabled_instruments PROCEDURE +ps_statement_avg_latency_histogram PROCEDURE +ps_trace_statement_digest PROCEDURE +ps_trace_thread PROCEDURE +ps_truncate_all_tables PROCEDURE +statement_performance_analyzer PROCEDURE +table_exists PROCEDURE +SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = 'sys' ORDER BY TRIGGER_NAME; +TRIGGER_NAME +SELECT sys_version FROM sys.version; +sys_version +1.5.1 diff --git a/mysql-test/suite/sysschema/r/fn_extract_schema_from_file_name.result b/mysql-test/suite/sysschema/r/fn_extract_schema_from_file_name.result new file mode 100644 index 00000000..c25f82eb --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_extract_schema_from_file_name.result @@ -0,0 +1,6 @@ +SELECT sys.extract_schema_from_file_name(NULL); +sys.extract_schema_from_file_name(NULL) +NULL +SELECT sys.extract_schema_from_file_name('/var/lib/mysql/employees/employee.ibd'); +sys.extract_schema_from_file_name('/var/lib/mysql/employees/employee.ibd') +employees diff --git a/mysql-test/suite/sysschema/r/fn_extract_table_from_file_name.result b/mysql-test/suite/sysschema/r/fn_extract_table_from_file_name.result new file mode 100644 index 00000000..a9297feb --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_extract_table_from_file_name.result @@ -0,0 +1,6 @@ +SELECT sys.extract_table_from_file_name(NULL); +sys.extract_table_from_file_name(NULL) +NULL +SELECT sys.extract_table_from_file_name('/var/lib/mysql/employees/employee.ibd'); +sys.extract_table_from_file_name('/var/lib/mysql/employees/employee.ibd') +employee diff --git a/mysql-test/suite/sysschema/r/fn_format_bytes.result b/mysql-test/suite/sysschema/r/fn_format_bytes.result new file mode 100644 index 00000000..5bf6c99b --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_format_bytes.result @@ -0,0 +1,39 @@ +SELECT sys.format_bytes(NULL); +sys.format_bytes(NULL) +NULL +SELECT sys.format_bytes(1); +sys.format_bytes(1) +1 bytes +SELECT sys.format_bytes(1023); +sys.format_bytes(1023) +1023 bytes +SELECT sys.format_bytes(1024); +sys.format_bytes(1024) +1.00 KiB +SELECT sys.format_bytes(1048575); +sys.format_bytes(1048575) +1024.00 KiB +SELECT sys.format_bytes(1048576); +sys.format_bytes(1048576) +1.00 MiB +SELECT sys.format_bytes(1073741823); +sys.format_bytes(1073741823) +1024.00 MiB +SELECT sys.format_bytes(1073741824); +sys.format_bytes(1073741824) +1.00 GiB +SELECT sys.format_bytes(1099511627775); +sys.format_bytes(1099511627775) +1024.00 GiB +SELECT sys.format_bytes(1099511627776); +sys.format_bytes(1099511627776) +1.00 TiB +SELECT sys.format_bytes(1125899906842623); +sys.format_bytes(1125899906842623) +1024.00 TiB +SELECT sys.format_bytes(1125899906842624); +sys.format_bytes(1125899906842624) +1.00 PiB +SELECT sys.format_bytes(1125899906842624238947293); +sys.format_bytes(1125899906842624238947293) +1000000000.00 PiB diff --git a/mysql-test/suite/sysschema/r/fn_format_path.result b/mysql-test/suite/sysschema/r/fn_format_path.result new file mode 100644 index 00000000..5672b09f --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_format_path.result @@ -0,0 +1,14 @@ +SELECT sys.format_path(NULL); +sys.format_path(NULL) +NULL +SET @mypath := CONCAT(@@global.datadir, 'foo/bar.foo'); +SELECT sys.format_path(@mypath); +sys.format_path(@mypath) +@@datadir/foo/bar.foo +SET @mypath := CONCAT(@@global.tmpdir, '/foo/bar.foo'); +SELECT sys.format_path(@mypath); +sys.format_path(@mypath) +@@tmpdir/foo/bar.foo +SELECT sys.format_path('/foo/bar/baz.foo'); +sys.format_path('/foo/bar/baz.foo') +/foo/bar/baz.foo diff --git a/mysql-test/suite/sysschema/r/fn_format_statement.result b/mysql-test/suite/sysschema/r/fn_format_statement.result new file mode 100644 index 00000000..b07e4b02 --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_format_statement.result @@ -0,0 +1,10 @@ +SELECT sys.format_statement(NULL); +sys.format_statement(NULL) +NULL +SELECT sys.format_statement('SELECT foo, bar, baz, foobar, foobaz FROM foo JOIN bar USING (foobar) JOIN baz USING (foobar) WHERE foo = \'foo\' AND bar = \'bar\'') AS statement; +statement +SELECT foo, bar, baz, foobar, ... RE foo = 'foo' AND bar = 'bar' +SET @sys.statement_truncate_len = 80; +SELECT sys.format_statement('SELECT foo, bar, baz, foobar, foobaz FROM foo JOIN bar USING (foobar) JOIN baz USING (foobar) WHERE foo = \'foo\' AND bar = \'bar\'') AS statement; +statement +SELECT foo, bar, baz, foobar, foobaz F ... bar) WHERE foo = 'foo' AND bar = 'bar' diff --git a/mysql-test/suite/sysschema/r/fn_format_time.result b/mysql-test/suite/sysschema/r/fn_format_time.result new file mode 100644 index 00000000..6854bb88 --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_format_time.result @@ -0,0 +1,57 @@ +SELECT sys.format_time(NULL); +sys.format_time(NULL) +NULL +SELECT sys.format_time(1); +sys.format_time(1) +1 ps +SELECT sys.format_time(999); +sys.format_time(999) +999 ps +SELECT sys.format_time(1000); +sys.format_time(1000) +1.00 ns +SELECT sys.format_time(999999); +sys.format_time(999999) +1000.00 ns +SELECT sys.format_time(1000000); +sys.format_time(1000000) +1.00 us +SELECT sys.format_time(999999999); +sys.format_time(999999999) +1000.00 us +SELECT sys.format_time(1000000000); +sys.format_time(1000000000) +1.00 ms +SELECT sys.format_time(999999999999); +sys.format_time(999999999999) +1000.00 ms +SELECT sys.format_time(1000000000000); +sys.format_time(1000000000000) +1.00 s +SELECT sys.format_time(59999999999999); +sys.format_time(59999999999999) +60.00 s +SELECT sys.format_time(60000000000000); +sys.format_time(60000000000000) +1.00 m +SELECT sys.format_time(3599999999999999); +sys.format_time(3599999999999999) +60.00 m +SELECT sys.format_time(3600000000000000); +sys.format_time(3600000000000000) +1.00 h +SELECT sys.format_time(86399999999999988); +sys.format_time(86399999999999988) +24.00 h +SELECT sys.format_time(86400000000000000); +sys.format_time(86400000000000000) +1.00 d +SELECT sys.format_time(604799999999999888); +sys.format_time(604799999999999888) +7.00 d +SELECT sys.format_time(604800000000000000); +sys.format_time(604800000000000000) +1.00 w +SELECT sys.format_time(2389472398472389748237429837423984728374); +sys.format_time(2389472398472389748237429837423984728374) +3950847219696411300000.00 w diff --git a/mysql-test/suite/sysschema/r/fn_list_add.result b/mysql-test/suite/sysschema/r/fn_list_add.result new file mode 100644 index 00000000..c3ade975 --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_list_add.result @@ -0,0 +1,28 @@ +SELECT sys.list_add(NULL, 'foo'); +sys.list_add(NULL, 'foo') +foo +SELECT sys.list_add('', 'foo'); +sys.list_add('', 'foo') +foo +SELECT sys.list_add('bar', 'foo'); +sys.list_add('bar', 'foo') +bar,foo +SELECT sys.list_add('bar, ', 'foo'); +sys.list_add('bar, ', 'foo') +bar,foo +SELECT sys.list_add('foo', NULL); +ERROR 02200: Function sys.list_add: in_add_value input variable should not be NULL +SET @sqlmode := @@sql_mode; +SELECT @@sql_mode; +@@sql_mode +STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION +SET SESSION sql_mode = sys.list_add(@@sql_mode, 'ANSI_QUOTES'); +SELECT @@sql_mode; +@@sql_mode +ANSI_QUOTES,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION +SET SESSION sql_mode = @sqlmode; +SET @sqlmode := NULL; +SET @input := REPEAT('a', 4194304); +SELECT sys.list_add(@input, 'foo'); +ERROR 22001: Data too long for column 'in_list' at row 1 +SET @input := NULL; diff --git a/mysql-test/suite/sysschema/r/fn_list_drop.result b/mysql-test/suite/sysschema/r/fn_list_drop.result new file mode 100644 index 00000000..17c1ede1 --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_list_drop.result @@ -0,0 +1,30 @@ +SELECT sys.list_drop('1,2,3,4,5', '1'); +sys.list_drop('1,2,3,4,5', '1') +2,3,4,5 +SELECT sys.list_drop('1,2,3,4,5', '3'); +sys.list_drop('1,2,3,4,5', '3') +1,2,4,5 +SELECT sys.list_drop('1,2,3,4,5', '5'); +sys.list_drop('1,2,3,4,5', '5') +1,2,3,4 +SELECT sys.list_drop('1, 2, 3, 4, 5', '1'); +sys.list_drop('1, 2, 3, 4, 5', '1') + 2, 3, 4, 5 +SELECT sys.list_drop('1, 2, 3, 4, 5', '3'); +sys.list_drop('1, 2, 3, 4, 5', '3') +1, 2, 4, 5 +SELECT sys.list_drop('1, 2, 3, 4, 5', '5'); +sys.list_drop('1, 2, 3, 4, 5', '5') +1, 2, 3, 4 +SELECT sys.list_drop('1,2,3,4,5', NULL); +ERROR 02200: Function sys.list_drop: in_drop_value input variable should not be NULL +SET @sqlmode := @@sql_mode; +SELECT @@sql_mode; +@@sql_mode +STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION +SET SESSION sql_mode = sys.list_drop(@@sql_mode, 'STRICT_TRANS_TABLES'); +SELECT @@sql_mode; +@@sql_mode +ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION +SET SESSION sql_mode = @sqlmode; +SET @sqlmode := NULL; diff --git a/mysql-test/suite/sysschema/r/fn_ps_is_account_enabled.result b/mysql-test/suite/sysschema/r/fn_ps_is_account_enabled.result new file mode 100644 index 00000000..79bad719 --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_ps_is_account_enabled.result @@ -0,0 +1,14 @@ +SELECT sys.ps_is_account_enabled('foo', 'bar'); +sys.ps_is_account_enabled('foo', 'bar') +YES +DELETE FROM performance_schema.setup_actors; +INSERT INTO performance_schema.setup_actors VALUES +('test', 'test', '%', 'YES', 'NO'); +SELECT sys.ps_is_account_enabled('foo', 'bar'); +sys.ps_is_account_enabled('foo', 'bar') +NO +SELECT sys.ps_is_account_enabled('test', 'test'); +sys.ps_is_account_enabled('test', 'test') +YES +TRUNCATE TABLE performance_schema.setup_actors; +INSERT INTO performance_schema.setup_actors VALUES ('%', '%', '%', 'YES', 'YES'); diff --git a/mysql-test/suite/sysschema/r/fn_ps_is_consumer_enabled.result b/mysql-test/suite/sysschema/r/fn_ps_is_consumer_enabled.result new file mode 100644 index 00000000..e69c0728 --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_ps_is_consumer_enabled.result @@ -0,0 +1,42 @@ +UPDATE performance_schema.setup_consumers SET enabled = 'NO' WHERE name = 'events_stages_history'; +SELECT sys.ps_is_consumer_enabled('events_stages_history'); +sys.ps_is_consumer_enabled('events_stages_history') +NO +SELECT sys.ps_is_consumer_enabled('global_instrumentation'); +sys.ps_is_consumer_enabled('global_instrumentation') +YES +UPDATE performance_schema.setup_consumers SET enabled = 'NO' WHERE name = 'events_stages_current'; +UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_stages_history'; +SELECT sys.ps_is_consumer_enabled('events_stages_history'); +sys.ps_is_consumer_enabled('events_stages_history') +NO +UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_stages_current'; +SELECT sys.ps_is_consumer_enabled('events_stages_history'); +sys.ps_is_consumer_enabled('events_stages_history') +YES +UPDATE performance_schema.setup_consumers SET enabled = 'NO' WHERE name = 'global_instrumentation'; +SELECT sys.ps_is_consumer_enabled('thread_instrumentation'); +sys.ps_is_consumer_enabled('thread_instrumentation') +NO +SELECT sys.ps_is_consumer_enabled('statements_digest'); +sys.ps_is_consumer_enabled('statements_digest') +NO +SELECT sys.ps_is_consumer_enabled('events_stages_current'); +sys.ps_is_consumer_enabled('events_stages_current') +NO +SELECT sys.ps_is_consumer_enabled('events_stages_history'); +sys.ps_is_consumer_enabled('events_stages_history') +NO +SELECT sys.ps_is_consumer_enabled('events_stages_history_long'); +sys.ps_is_consumer_enabled('events_stages_history_long') +NO +SELECT sys.ps_is_consumer_enabled('events_statements_current'); +sys.ps_is_consumer_enabled('events_statements_current') +NO +SELECT sys.ps_is_consumer_enabled('events_statements_history'); +sys.ps_is_consumer_enabled('events_statements_history') +NO +SELECT sys.ps_is_consumer_enabled('events_statements_history_long'); +sys.ps_is_consumer_enabled('events_statements_history_long') +NO +UPDATE performance_schema.setup_consumers SET enabled = 'YES'; diff --git a/mysql-test/suite/sysschema/r/fn_ps_is_instrument_default_enabled.result b/mysql-test/suite/sysschema/r/fn_ps_is_instrument_default_enabled.result new file mode 100644 index 00000000..e9143faf --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_ps_is_instrument_default_enabled.result @@ -0,0 +1,48 @@ +SELECT sys.ps_is_instrument_default_enabled('memory/performance_schema/internal_buffers'); +sys.ps_is_instrument_default_enabled('memory/performance_schema/internal_buffers') +YES +SELECT sys.ps_is_instrument_default_enabled('statement/sql/select'); +sys.ps_is_instrument_default_enabled('statement/sql/select') +YES +SELECT sys.ps_is_instrument_default_enabled('statement/sp/error'); +sys.ps_is_instrument_default_enabled('statement/sp/error') +YES +SELECT sys.ps_is_instrument_default_enabled('statement/com/Prepare'); +sys.ps_is_instrument_default_enabled('statement/com/Prepare') +YES +SELECT sys.ps_is_instrument_default_enabled('wait/io/file/sql/binlog'); +sys.ps_is_instrument_default_enabled('wait/io/file/sql/binlog') +YES +SELECT sys.ps_is_instrument_default_enabled('wait/io/table/sql/handler'); +sys.ps_is_instrument_default_enabled('wait/io/table/sql/handler') +YES +SELECT sys.ps_is_instrument_default_enabled('wait/lock/table/sql/handler'); +sys.ps_is_instrument_default_enabled('wait/lock/table/sql/handler') +YES +SELECT sys.ps_is_instrument_default_enabled('idle'); +sys.ps_is_instrument_default_enabled('idle') +YES +SELECT sys.ps_is_instrument_default_enabled('wait/synch/mutex/sql/LOCK_plugin'); +sys.ps_is_instrument_default_enabled('wait/synch/mutex/sql/LOCK_plugin') +NO +SELECT sys.ps_is_instrument_default_enabled('wait/synch/rwlock/sql/LOCK_grant'); +sys.ps_is_instrument_default_enabled('wait/synch/rwlock/sql/LOCK_grant') +NO +SELECT sys.ps_is_instrument_default_enabled('wait/synch/sxlock/innodb/btr_search_latch'); +sys.ps_is_instrument_default_enabled('wait/synch/sxlock/innodb/btr_search_latch') +NO +SELECT sys.ps_is_instrument_default_enabled('wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond'); +sys.ps_is_instrument_default_enabled('wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond') +NO +SELECT sys.ps_is_instrument_default_enabled('stage/sql/end'); +sys.ps_is_instrument_default_enabled('stage/sql/end') +NO +SELECT sys.ps_is_instrument_default_enabled('transaction'); +sys.ps_is_instrument_default_enabled('transaction') +NO +SELECT sys.ps_is_instrument_default_enabled('wait/io/socket/sql/server_tcpip_socket'); +sys.ps_is_instrument_default_enabled('wait/io/socket/sql/server_tcpip_socket') +NO +SELECT sys.ps_is_instrument_default_enabled('wait/lock/metadata/sql/mdl'); +sys.ps_is_instrument_default_enabled('wait/lock/metadata/sql/mdl') +NO diff --git a/mysql-test/suite/sysschema/r/fn_ps_is_instrument_default_timed.result b/mysql-test/suite/sysschema/r/fn_ps_is_instrument_default_timed.result new file mode 100644 index 00000000..99f426c6 --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_ps_is_instrument_default_timed.result @@ -0,0 +1,48 @@ +SELECT sys.ps_is_instrument_default_timed('wait/io/file/sql/binlog'); +sys.ps_is_instrument_default_timed('wait/io/file/sql/binlog') +YES +SELECT sys.ps_is_instrument_default_timed('statement/sql/select'); +sys.ps_is_instrument_default_timed('statement/sql/select') +YES +SELECT sys.ps_is_instrument_default_timed('statement/sp/error'); +sys.ps_is_instrument_default_timed('statement/sp/error') +YES +SELECT sys.ps_is_instrument_default_timed('statement/com/Prepare'); +sys.ps_is_instrument_default_timed('statement/com/Prepare') +YES +SELECT sys.ps_is_instrument_default_timed('wait/io/table/sql/handler'); +sys.ps_is_instrument_default_timed('wait/io/table/sql/handler') +YES +SELECT sys.ps_is_instrument_default_timed('wait/lock/table/sql/handler'); +sys.ps_is_instrument_default_timed('wait/lock/table/sql/handler') +YES +SELECT sys.ps_is_instrument_default_timed('idle'); +sys.ps_is_instrument_default_timed('idle') +YES +SELECT sys.ps_is_instrument_default_timed('wait/synch/mutex/sql/LOCK_plugin'); +sys.ps_is_instrument_default_timed('wait/synch/mutex/sql/LOCK_plugin') +NO +SELECT sys.ps_is_instrument_default_timed('wait/synch/rwlock/sql/LOCK_grant'); +sys.ps_is_instrument_default_timed('wait/synch/rwlock/sql/LOCK_grant') +NO +SELECT sys.ps_is_instrument_default_timed('wait/synch/sxlock/innodb/btr_search_latch'); +sys.ps_is_instrument_default_timed('wait/synch/sxlock/innodb/btr_search_latch') +NO +SELECT sys.ps_is_instrument_default_timed('wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond'); +sys.ps_is_instrument_default_timed('wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond') +NO +SELECT sys.ps_is_instrument_default_timed('stage/sql/end'); +sys.ps_is_instrument_default_timed('stage/sql/end') +NO +SELECT sys.ps_is_instrument_default_timed('transaction'); +sys.ps_is_instrument_default_timed('transaction') +NO +SELECT sys.ps_is_instrument_default_timed('wait/io/socket/sql/server_tcpip_socket'); +sys.ps_is_instrument_default_timed('wait/io/socket/sql/server_tcpip_socket') +NO +SELECT sys.ps_is_instrument_default_timed('memory/performance_schema/internal_buffers'); +sys.ps_is_instrument_default_timed('memory/performance_schema/internal_buffers') +NO +SELECT sys.ps_is_instrument_default_timed('wait/lock/metadata/sql/mdl'); +sys.ps_is_instrument_default_timed('wait/lock/metadata/sql/mdl') +NO diff --git a/mysql-test/suite/sysschema/r/fn_ps_is_thread_instrumented.result b/mysql-test/suite/sysschema/r/fn_ps_is_thread_instrumented.result new file mode 100644 index 00000000..90583e3c --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_ps_is_thread_instrumented.result @@ -0,0 +1,14 @@ +SELECT sys.ps_is_thread_instrumented(NULL); +sys.ps_is_thread_instrumented(NULL) +NULL +SELECT sys.ps_is_thread_instrumented(CONNECTION_ID()); +sys.ps_is_thread_instrumented(CONNECTION_ID()) +YES +UPDATE performance_schema.threads SET instrumented = 'NO' WHERE processlist_id = CONNECTION_ID(); +SELECT sys.ps_is_thread_instrumented(CONNECTION_ID()); +sys.ps_is_thread_instrumented(CONNECTION_ID()) +NO +SELECT sys.ps_is_thread_instrumented(234623462376); +sys.ps_is_thread_instrumented(234623462376) +UNKNOWN +UPDATE performance_schema.threads SET instrumented = 'YES'; diff --git a/mysql-test/suite/sysschema/r/fn_ps_thread_account.result b/mysql-test/suite/sysschema/r/fn_ps_thread_account.result new file mode 100644 index 00000000..67f7e8fa --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_ps_thread_account.result @@ -0,0 +1,9 @@ +SELECT sys.ps_thread_account(NULL); +sys.ps_thread_account(NULL) +NULL +SELECT sys.ps_thread_account(234623462376); +sys.ps_thread_account(234623462376) +NULL +SELECT sys.ps_thread_account(sys.ps_thread_id(NULL)); +sys.ps_thread_account(sys.ps_thread_id(NULL)) +root@localhost diff --git a/mysql-test/suite/sysschema/r/fn_ps_thread_id.result b/mysql-test/suite/sysschema/r/fn_ps_thread_id.result new file mode 100644 index 00000000..8d1677af --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_ps_thread_id.result @@ -0,0 +1,9 @@ +SELECT sys.ps_thread_id(234623462376); +sys.ps_thread_id(234623462376) +NULL +SELECT sys.ps_thread_id(CONNECTION_ID()) = @ps_thread_id; +sys.ps_thread_id(CONNECTION_ID()) = @ps_thread_id +1 +SELECT sys.ps_thread_id(NULL) = @ps_thread_id; +sys.ps_thread_id(NULL) = @ps_thread_id +1 diff --git a/mysql-test/suite/sysschema/r/fn_ps_thread_trx_info.result b/mysql-test/suite/sysschema/r/fn_ps_thread_trx_info.result new file mode 100644 index 00000000..fe11e702 --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_ps_thread_trx_info.result @@ -0,0 +1,92 @@ +SELECT sys.ps_thread_trx_info(234623462376); +sys.ps_thread_trx_info(234623462376) +NULL +SELECT JSON_VALID(sys.ps_thread_trx_info(sys.ps_thread_id(NULL))); +JSON_VALID(sys.ps_thread_trx_info(sys.ps_thread_id(NULL))) +1 +CREATE DATABASE trx; +CREATE TABLE trx.info (id INT PRIMARY KEY, info VARCHAR(20)); +connect con1,localhost,root,,; +connection con1; +USE trx; +START TRANSACTION; +INSERT INTO info VALUES (1, 'foo'); +COMMIT; +START TRANSACTION; +INSERT INTO info VALUES (2, 'bar'); +COMMIT; +connection default; +SET @json_doc := sys.ps_thread_trx_info(@ps_thread_id); +SELECT JSON_VALID(@json_doc); +JSON_VALID(@json_doc) +1 +SELECT JSON_LENGTH(@json_doc); +JSON_LENGTH(@json_doc) +2 +SELECT JSON_KEYS(JSON_EXTRACT(@json_doc, '$[0]')); +JSON_KEYS(JSON_EXTRACT(@json_doc, '$[0]')) +["time", "state", "mode", "autocommitted", "gtid", "isolation", "statements_executed"] +SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$[0].time'); +JSON_CONTAINS_PATH(@json_doc, 'one', '$[0].time') +1 +SELECT JSON_CONTAINS(@json_doc, '"COMMITTED"', '$[0].state'); +JSON_CONTAINS(@json_doc, '"COMMITTED"', '$[0].state') +1 +SELECT JSON_CONTAINS(@json_doc, '"READ WRITE"', '$[0].mode'); +JSON_CONTAINS(@json_doc, '"READ WRITE"', '$[0].mode') +1 +SELECT JSON_CONTAINS(@json_doc, '"NO"', '$[0].autocommitted'); +JSON_CONTAINS(@json_doc, '"NO"', '$[0].autocommitted') +1 +SELECT JSON_CONTAINS(@json_doc, '"AUTOMATIC"', '$[0].gtid'); +JSON_CONTAINS(@json_doc, '"AUTOMATIC"', '$[0].gtid') +0 +SELECT JSON_CONTAINS(@json_doc, '"REPEATABLE READ"', '$[0].isolation'); +JSON_CONTAINS(@json_doc, '"REPEATABLE READ"', '$[0].isolation') +1 +SELECT JSON_KEYS(JSON_EXTRACT(@json_doc, '$[0].statements_executed[0]')); +JSON_KEYS(JSON_EXTRACT(@json_doc, '$[0].statements_executed[0]')) +["sql_text", "time", "schema", "rows_examined", "rows_affected", "rows_sent", "tmp_tables", "tmp_disk_tables", "sort_rows", "sort_merge_passes"] +SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$[0].statements_executed[0].time'); +JSON_CONTAINS_PATH(@json_doc, 'one', '$[0].statements_executed[0].time') +1 +SELECT JSON_CONTAINS(@json_doc, '"INSERT INTO info VALUES (1, \'foo\')"', '$[0].statements_executed[0].sql_text'); +JSON_CONTAINS(@json_doc, '"INSERT INTO info VALUES (1, \'foo\')"', '$[0].statements_executed[0].sql_text') +1 +SELECT JSON_CONTAINS(@json_doc, '"trx"', '$[0].statements_executed[0].schema'); +JSON_CONTAINS(@json_doc, '"trx"', '$[0].statements_executed[0].schema') +1 +SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].rows_examined'); +JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].rows_examined') +1 +SELECT JSON_CONTAINS(@json_doc, '1', '$[0].statements_executed[0].rows_affected'); +JSON_CONTAINS(@json_doc, '1', '$[0].statements_executed[0].rows_affected') +1 +SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].rows_sent'); +JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].rows_sent') +1 +SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].tmp_tables'); +JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].tmp_tables') +1 +SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].tmp_disk_tables'); +JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].tmp_disk_tables') +1 +SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].sort_rows'); +JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].sort_rows') +1 +SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].sort_merge_passes'); +JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].sort_merge_passes') +1 +SELECT JSON_CONTAINS(@json_doc, '"COMMIT"', '$[0].statements_executed[1].sql_text'); +JSON_CONTAINS(@json_doc, '"COMMIT"', '$[0].statements_executed[1].sql_text') +1 +SET @sys.ps_thread_trx_info.max_length = 100; +SELECT sys.ps_thread_trx_info(@ps_thread_id); +sys.ps_thread_trx_info(@ps_thread_id) +{ "error": "Trx info truncated: Row 1X was cut by GROUP_CONCAT()" } +SET @sys.ps_thread_trx_info.max_length = NULL; +SELECT JSON_VALID(sys.ps_thread_trx_info(@ps_thread_id)); +JSON_VALID(sys.ps_thread_trx_info(@ps_thread_id)) +1 +disconnect con1; +DROP DATABASE trx; diff --git a/mysql-test/suite/sysschema/r/fn_quote_identifier.result b/mysql-test/suite/sysschema/r/fn_quote_identifier.result new file mode 100644 index 00000000..5d0a4964 --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_quote_identifier.result @@ -0,0 +1,21 @@ +SELECT sys.quote_identifier(NULL); +sys.quote_identifier(NULL) +NULL +SELECT sys.quote_identifier('abc'); +sys.quote_identifier('abc') +`abc` +SELECT sys.quote_identifier('ab`c'); +sys.quote_identifier('ab`c') +`ab``c` +SELECT sys.quote_identifier('ab``c'); +sys.quote_identifier('ab``c') +`ab````c` +SELECT sys.quote_identifier('ab```c'); +sys.quote_identifier('ab```c') +`ab``````c` +SELECT sys.quote_identifier('a`b`c'); +sys.quote_identifier('a`b`c') +`a``b``c` +SELECT sys.quote_identifier('a`b``c'); +sys.quote_identifier('a`b``c') +`a``b````c` diff --git a/mysql-test/suite/sysschema/r/fn_sys_get_config.result b/mysql-test/suite/sysschema/r/fn_sys_get_config.result new file mode 100644 index 00000000..26345cfd --- /dev/null +++ b/mysql-test/suite/sysschema/r/fn_sys_get_config.result @@ -0,0 +1,8 @@ +SET @sys.statement_truncate_len := IFNULL(@sys.statement_truncate_len, sys.sys_get_config('statement_truncate_len', 128)); +SELECT @sys.statement_truncate_len; +@sys.statement_truncate_len +64 +SET @sys.foo := IFNULL(@sys.foo, sys.sys_get_config('foo', 'foobar')); +SELECT @sys.foo; +@sys.foo +foobar diff --git a/mysql-test/suite/sysschema/r/mysqldump.result b/mysql-test/suite/sysschema/r/mysqldump.result new file mode 100644 index 00000000..43a4fcbf --- /dev/null +++ b/mysql-test/suite/sysschema/r/mysqldump.result @@ -0,0 +1,25 @@ +CREATE TEMPORARY TABLE tmp_global_priv AS SELECT * FROM mysql.global_priv; +CREATE TEMPORARY TABLE tmp_tables_priv AS SELECT * FROM mysql.tables_priv; +DROP DATABASE sys; +SHOW DATABASES; +Database +information_schema +mtr +mysql +performance_schema +test +SHOW DATABASES; +Database +information_schema +mtr +mysql +performance_schema +sys +test +TRUNCATE TABLE mysql.global_priv; +INSERT INTO mysql.global_priv (SELECT * FROM tmp_global_priv); +DROP TEMPORARY TABLE tmp_global_priv; +TRUNCATE TABLE mysql.tables_priv; +INSERT INTO mysql.tables_priv (SELECT * FROM tmp_tables_priv); +DROP TEMPORARY TABLE tmp_tables_priv; +FLUSH PRIVILEGES; diff --git a/mysql-test/suite/sysschema/r/pr_create_synonym_db.result b/mysql-test/suite/sysschema/r/pr_create_synonym_db.result new file mode 100644 index 00000000..8c0e8dfa --- /dev/null +++ b/mysql-test/suite/sysschema/r/pr_create_synonym_db.result @@ -0,0 +1,66 @@ +CREATE TABLE t1 (t1_id int PRIMARY KEY, t1_val varchar(10)); +CREATE TABLE t2 (t2_id int PRIMARY KEY, t1_id int, t2_val int, INDEX (t1_id)); +CREATE TABLE `is` (t1_id int PRIMARY KEY, t1_val varchar(10)); +CREATE TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10)); +CREATE SQL SECURITY INVOKER VIEW myview AS SELECT * FROM t1 NATURAL JOIN t2; +CALL sys.create_synonym_db('test', 'test1'); +summary +Created 5 views in the `test1` database +SELECT TABLE_NAME, SECURITY_TYPE FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'test1' ORDER BY TABLE_NAME; +TABLE_NAME SECURITY_TYPE +ab`c INVOKER +is INVOKER +myview INVOKER +t1 INVOKER +t2 INVOKER +CALL sys.create_synonym_db('test', 'test1'); +ERROR HY000: Can't create database test1; database exists +CREATE SCHEMA test2; +CALL sys.create_synonym_db('test', 'test2'); +ERROR HY000: Can't create database test2; database exists +SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test2'; +COUNT(*) +0 +CALL sys.create_synonym_db('test', 'is'); +summary +Created 5 views in the `is` database +SELECT TABLE_NAME, SECURITY_TYPE FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'is' ORDER BY TABLE_NAME; +TABLE_NAME SECURITY_TYPE +ab`c INVOKER +is INVOKER +myview INVOKER +t1 INVOKER +t2 INVOKER +CALL sys.create_synonym_db('is', 'i`s'); +summary +Created 5 views in the `i``s` database +SELECT TABLE_NAME, SECURITY_TYPE FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'i`s' ORDER BY TABLE_NAME; +TABLE_NAME SECURITY_TYPE +ab`c INVOKER +is INVOKER +myview INVOKER +t1 INVOKER +t2 INVOKER +DROP SCHEMA test1; +DROP SCHEMA test2; +DROP SCHEMA `is`; +DROP SCHEMA `i``s`; +DROP VIEW test.myview; +DROP TABLE test.t1; +DROP TABLE test.t2; +DROP TABLE `is`; +DROP TABLE `ab``c`; +# +# MDEV-28342: sys.create_synonym_db fails +# when a temporary table masks a base table +# +create database db; +use db; +create table a(a int); +create table t (b int); +create table b(a int); +create temporary table b (a int); +call sys.create_synonym_db('db','db_copy'); +ERROR HY000: Table`db`.`b`shadows base table. View cannot be created! Terminating! +drop database db; +drop database db_copy; diff --git a/mysql-test/suite/sysschema/r/pr_diagnostics.result b/mysql-test/suite/sysschema/r/pr_diagnostics.result new file mode 100644 index 00000000..d07295bd --- /dev/null +++ b/mysql-test/suite/sysschema/r/pr_diagnostics.result @@ -0,0 +1,15 @@ +SET @sys.debug = 'ON', +@sys.diagnostics.allow_i_s_tables = 'ON', +@sys.diagnostics.include_raw = 'ON'; +CALL sys.diagnostics(4, 2, 'full'); +SET @sys.debug = 'OFF', +@sys.diagnostics.allow_i_s_tables = 'OFF', +@sys.diagnostics.include_raw = 'OFF'; +CALL sys.diagnostics(0, 0, 'full'); +summary +Disabled 1 thread +ERROR 45000: in_max_runtime must be greater than 0 +CALL sys.diagnostics(2, 0, 'full'); +ERROR 45000: in_interval must be greater than 0 +CALL sys.diagnostics(1, 2, 'full'); +ERROR 45000: in_max_runtime must be greater than or equal to in_interval diff --git a/mysql-test/suite/sysschema/r/pr_execute_prepared_stmt.result b/mysql-test/suite/sysschema/r/pr_execute_prepared_stmt.result new file mode 100644 index 00000000..5cedd970 --- /dev/null +++ b/mysql-test/suite/sysschema/r/pr_execute_prepared_stmt.result @@ -0,0 +1,21 @@ +SET @sql := "CREATE TABLE test.t1 (i INT) Engine=MEMORY"; +CALL sys.execute_prepared_stmt(@sql); +SHOW CREATE TABLE test.t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `i` int(11) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SET @sql := CONCAT('INSERT INTO test.t1 VALUES (', 1, ')'); +CALL sys.execute_prepared_stmt(@sql); +SELECT * FROM test.t1; +i +1 +SET @sql := "DROP TABLE test.t1"; +CALL sys.execute_prepared_stmt(@sql); +SHOW TABLES; +Tables_in_test +SET @sql = NULL; +CALL sys.execute_prepared_stmt(NULL); +ERROR 45000: The @sys.execute_prepared_stmt.sql must contain a query +CALL sys.execute_prepared_stmt('foo'); +ERROR 45000: The @sys.execute_prepared_stmt.sql must contain a query diff --git a/mysql-test/suite/sysschema/r/pr_ps_setup_reset_to_default.result b/mysql-test/suite/sysschema/r/pr_ps_setup_reset_to_default.result new file mode 100644 index 00000000..78258de1 --- /dev/null +++ b/mysql-test/suite/sysschema/r/pr_ps_setup_reset_to_default.result @@ -0,0 +1,60 @@ +CALL sys.ps_setup_reset_to_default(TRUE); +status +Resetting: setup_actors +DELETE +FROM performance_schema.setup_actors + WHERE NOT (HOST = '%' AND USER = '%' AND ROLE = '%') +status +Resetting: setup_actors +INSERT IGNORE INTO performance_schema.setup_actors +VALUES ('%', '%', '%', 'YES', 'YES') +status +Resetting: setup_instruments +UPDATE performance_schema.setup_instruments + SET ENABLED = sys.ps_is_instrument_default_enabled(NAME), + TIMED = sys.ps_is_instrument_default_timed(NAME) +status +Resetting: setup_consumers +UPDATE performance_schema.setup_consumers + SET ENABLED = IF(NAME IN ('events_statements_current', 'events_transactions_current', 'global_instrumentation', 'thread_instrumentation', 'statements_digest'), 'YES', 'NO') +status +Resetting: setup_objects +DELETE +FROM performance_schema.setup_objects + WHERE NOT (OBJECT_TYPE IN ('EVENT', 'FUNCTION', 'PROCEDURE', 'TABLE', 'TRIGGER') AND OBJECT_NAME = '%' + AND (OBJECT_SCHEMA = 'mysql'AND ENABLED = 'NO'AND TIMED = 'NO' ) +OR (OBJECT_SCHEMA = 'performance_schema' AND ENABLED = 'NO'AND TIMED = 'NO' ) +OR (OBJECT_SCHEMA = 'information_schema' AND ENABLED = 'NO'AND TIMED = 'NO' ) +OR (OBJECT_SCHEMA = '%'AND ENABLED = 'YES' AND TIMED = 'YES')) +status +Resetting: setup_objects +INSERT IGNORE INTO performance_schema.setup_objects +VALUES ('EVENT', 'mysql' , '%', 'NO' , 'NO' ), + ('EVENT', 'performance_schema', '%', 'NO' , 'NO' ), + ('EVENT', 'information_schema', '%', 'NO' , 'NO' ), + ('EVENT', '%' , '%', 'YES', 'YES'), + ('FUNCTION' , 'mysql' , '%', 'NO' , 'NO' ), + ('FUNCTION' , 'performance_schema', '%', 'NO' , 'NO' ), + ('FUNCTION' , 'information_schema', '%', 'NO' , 'NO' ), + ('FUNCTION' , '%' , '%', 'YES', 'YES'), + ('PROCEDURE', 'mysql' , '%', 'NO' , 'NO' ), + ('PROCEDURE', 'performance_schema', '%', 'NO' , 'NO' ), + ('PROCEDURE', 'information_schema', '%', 'NO' , 'NO' ), + ('PROCEDURE', '%' , '%', 'YES', 'YES'), + ('TABLE', 'mysql' , '%', 'NO' , 'NO' ), + ('TABLE', 'performance_schema', '%', 'NO' , 'NO' ), + ('TABLE', 'information_schema', '%', 'NO' , 'NO' ), + ('TABLE', '%' , '%', 'YES', 'YES'), + ('TRIGGER', 'mysql' , '%', 'NO' , 'NO' ), + ('TRIGGER', 'performance_schema', '%', 'NO' , 'NO' ), + ('TRIGGER', 'information_schema', '%', 'NO' , 'NO' ), + ('TRIGGER', '%' , '%', 'YES', 'YES') +status +Resetting: threads +UPDATE performance_schema.threads + SET INSTRUMENTED = 'YES' +UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'; +TRUNCATE TABLE performance_schema.setup_actors; +INSERT INTO performance_schema.setup_actors VALUES ('%', '%', '%', 'YES', 'YES'); +UPDATE performance_schema.setup_consumers SET enabled = 'YES'; +UPDATE performance_schema.threads SET instrumented = 'YES'; diff --git a/mysql-test/suite/sysschema/r/pr_ps_setup_show_disabled.result b/mysql-test/suite/sysschema/r/pr_ps_setup_show_disabled.result new file mode 100644 index 00000000..cf8ca76e --- /dev/null +++ b/mysql-test/suite/sysschema/r/pr_ps_setup_show_disabled.result @@ -0,0 +1,205 @@ +INSERT INTO performance_schema.setup_actors +VALUES ('localhost', 'foo', '%', 'YES', 'YES'), +('localhost', 'bar', '%', 'NO', 'NO'); +UPDATE performance_schema.setup_instruments +SET ENABLED = 'NO' + WHERE NAME LIKE 'stage/innodb/%' + OR NAME LIKE 'statement/com/%' + OR NAME = 'idle'; +UPDATE performance_schema.setup_consumers +SET ENABLED = 'NO' + WHERE NAME LIKE '%\_history\_long'; +UPDATE performance_schema.threads SET INSTRUMENTED = 'NO' WHERE NAME LIKE 'thread/innodb/srv\_%'; +CALL sys.ps_setup_show_disabled(FALSE, FALSE); +performance_schema_enabled +1 +object_type objects enabled timed +EVENT information_schema.% NO NO +EVENT mysql.% NO NO +EVENT performance_schema.% NO NO +FUNCTION information_schema.% NO NO +FUNCTION mysql.% NO NO +FUNCTION performance_schema.% NO NO +PROCEDURE information_schema.% NO NO +PROCEDURE mysql.% NO NO +PROCEDURE performance_schema.% NO NO +TABLE information_schema.% NO NO +TABLE mysql.% NO NO +TABLE performance_schema.% NO NO +TRIGGER information_schema.% NO NO +TRIGGER mysql.% NO NO +TRIGGER performance_schema.% NO NO +disabled_consumers +events_stages_history_long +events_statements_history_long +events_transactions_history_long +events_waits_history_long +CALL sys.ps_setup_show_disabled(TRUE, FALSE); +performance_schema_enabled +1 +object_type objects enabled timed +EVENT information_schema.% NO NO +EVENT mysql.% NO NO +EVENT performance_schema.% NO NO +FUNCTION information_schema.% NO NO +FUNCTION mysql.% NO NO +FUNCTION performance_schema.% NO NO +PROCEDURE information_schema.% NO NO +PROCEDURE mysql.% NO NO +PROCEDURE performance_schema.% NO NO +TABLE information_schema.% NO NO +TABLE mysql.% NO NO +TABLE performance_schema.% NO NO +TRIGGER information_schema.% NO NO +TRIGGER mysql.% NO NO +TRIGGER performance_schema.% NO NO +disabled_consumers +events_stages_history_long +events_statements_history_long +events_transactions_history_long +events_waits_history_long +disabled_instruments timed +idle YES +stage/innodb/alter table (end) YES +stage/innodb/alter table (insert) YES +stage/innodb/alter table (log apply index) YES +stage/innodb/alter table (log apply table) YES +stage/innodb/alter table (merge sort) YES +stage/innodb/alter table (read PK and internal sort) YES +stage/innodb/buffer pool load YES +statement/com/Binlog Dump YES +statement/com/Bulk_execute YES +statement/com/Change user YES +statement/com/Close stmt YES +statement/com/Connect YES +statement/com/Connect Out YES +statement/com/Create DB YES +statement/com/Daemon YES +statement/com/Debug YES +statement/com/Delayed insert YES +statement/com/Drop DB YES +statement/com/Error YES +statement/com/Execute YES +statement/com/Fetch YES +statement/com/Field List YES +statement/com/Init DB YES +statement/com/Kill YES +statement/com/Long Data YES +statement/com/Ping YES +statement/com/Prepare YES +statement/com/Processlist YES +statement/com/Quit YES +statement/com/Refresh YES +statement/com/Register Slave YES +statement/com/Reset connection YES +statement/com/Reset stmt YES +statement/com/Set option YES +statement/com/Shutdown YES +statement/com/Slave_IO YES +statement/com/Slave_SQL YES +statement/com/Slave_worker YES +statement/com/Sleep YES +statement/com/Statistics YES +statement/com/Table Dump YES +statement/com/Time YES +statement/com/Unimpl get tid YES +CALL sys.ps_setup_show_disabled(FALSE, TRUE); +performance_schema_enabled +1 +object_type objects enabled timed +EVENT information_schema.% NO NO +EVENT mysql.% NO NO +EVENT performance_schema.% NO NO +FUNCTION information_schema.% NO NO +FUNCTION mysql.% NO NO +FUNCTION performance_schema.% NO NO +PROCEDURE information_schema.% NO NO +PROCEDURE mysql.% NO NO +PROCEDURE performance_schema.% NO NO +TABLE information_schema.% NO NO +TABLE mysql.% NO NO +TABLE performance_schema.% NO NO +TRIGGER information_schema.% NO NO +TRIGGER mysql.% NO NO +TRIGGER performance_schema.% NO NO +disabled_consumers +events_stages_history_long +events_statements_history_long +events_transactions_history_long +events_waits_history_long +disabled_threads thread_type +CALL sys.ps_setup_show_disabled(TRUE, TRUE); +performance_schema_enabled +1 +object_type objects enabled timed +EVENT information_schema.% NO NO +EVENT mysql.% NO NO +EVENT performance_schema.% NO NO +FUNCTION information_schema.% NO NO +FUNCTION mysql.% NO NO +FUNCTION performance_schema.% NO NO +PROCEDURE information_schema.% NO NO +PROCEDURE mysql.% NO NO +PROCEDURE performance_schema.% NO NO +TABLE information_schema.% NO NO +TABLE mysql.% NO NO +TABLE performance_schema.% NO NO +TRIGGER information_schema.% NO NO +TRIGGER mysql.% NO NO +TRIGGER performance_schema.% NO NO +disabled_consumers +events_stages_history_long +events_statements_history_long +events_transactions_history_long +events_waits_history_long +disabled_threads thread_type +disabled_instruments timed +idle YES +stage/innodb/alter table (end) YES +stage/innodb/alter table (insert) YES +stage/innodb/alter table (log apply index) YES +stage/innodb/alter table (log apply table) YES +stage/innodb/alter table (merge sort) YES +stage/innodb/alter table (read PK and internal sort) YES +stage/innodb/buffer pool load YES +statement/com/Binlog Dump YES +statement/com/Bulk_execute YES +statement/com/Change user YES +statement/com/Close stmt YES +statement/com/Connect YES +statement/com/Connect Out YES +statement/com/Create DB YES +statement/com/Daemon YES +statement/com/Debug YES +statement/com/Delayed insert YES +statement/com/Drop DB YES +statement/com/Error YES +statement/com/Execute YES +statement/com/Fetch YES +statement/com/Field List YES +statement/com/Init DB YES +statement/com/Kill YES +statement/com/Long Data YES +statement/com/Ping YES +statement/com/Prepare YES +statement/com/Processlist YES +statement/com/Quit YES +statement/com/Refresh YES +statement/com/Register Slave YES +statement/com/Reset connection YES +statement/com/Reset stmt YES +statement/com/Set option YES +statement/com/Shutdown YES +statement/com/Slave_IO YES +statement/com/Slave_SQL YES +statement/com/Slave_worker YES +statement/com/Sleep YES +statement/com/Statistics YES +statement/com/Table Dump YES +statement/com/Time YES +statement/com/Unimpl get tid YES +UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'; +TRUNCATE TABLE performance_schema.setup_actors; +INSERT INTO performance_schema.setup_actors VALUES ('%', '%', '%', 'YES', 'YES'); +UPDATE performance_schema.setup_consumers SET enabled = 'YES'; +UPDATE performance_schema.threads SET instrumented = 'YES'; diff --git a/mysql-test/suite/sysschema/r/pr_ps_setup_show_disabled_consumers.result b/mysql-test/suite/sysschema/r/pr_ps_setup_show_disabled_consumers.result new file mode 100644 index 00000000..c9aae2d2 --- /dev/null +++ b/mysql-test/suite/sysschema/r/pr_ps_setup_show_disabled_consumers.result @@ -0,0 +1,14 @@ +UPDATE performance_schema.setup_consumers +SET ENABLED = 'NO' + WHERE NAME LIKE '%\_history\_long'; +CALL sys.ps_setup_show_disabled_consumers(); +disabled_consumers +events_stages_history_long +events_statements_history_long +events_transactions_history_long +events_waits_history_long +UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'; +TRUNCATE TABLE performance_schema.setup_actors; +INSERT INTO performance_schema.setup_actors VALUES ('%', '%', '%', 'YES', 'YES'); +UPDATE performance_schema.setup_consumers SET enabled = 'YES'; +UPDATE performance_schema.threads SET instrumented = 'YES'; diff --git a/mysql-test/suite/sysschema/r/pr_ps_setup_show_disabled_instruments.result b/mysql-test/suite/sysschema/r/pr_ps_setup_show_disabled_instruments.result new file mode 100644 index 00000000..b0aa5f6d --- /dev/null +++ b/mysql-test/suite/sysschema/r/pr_ps_setup_show_disabled_instruments.result @@ -0,0 +1,56 @@ +UPDATE performance_schema.setup_instruments +SET ENABLED = 'NO' + WHERE NAME LIKE 'stage/innodb/%' + OR NAME LIKE 'statement/com/%' + OR NAME = 'idle'; +CALL sys.ps_setup_show_disabled_instruments(); +disabled_instruments timed +idle YES +stage/innodb/alter table (end) YES +stage/innodb/alter table (insert) YES +stage/innodb/alter table (log apply index) YES +stage/innodb/alter table (log apply table) YES +stage/innodb/alter table (merge sort) YES +stage/innodb/alter table (read PK and internal sort) YES +stage/innodb/buffer pool load YES +statement/com/Binlog Dump YES +statement/com/Bulk_execute YES +statement/com/Change user YES +statement/com/Close stmt YES +statement/com/Connect YES +statement/com/Connect Out YES +statement/com/Create DB YES +statement/com/Daemon YES +statement/com/Debug YES +statement/com/Delayed insert YES +statement/com/Drop DB YES +statement/com/Error YES +statement/com/Execute YES +statement/com/Fetch YES +statement/com/Field List YES +statement/com/Init DB YES +statement/com/Kill YES +statement/com/Long Data YES +statement/com/Ping YES +statement/com/Prepare YES +statement/com/Processlist YES +statement/com/Quit YES +statement/com/Refresh YES +statement/com/Register Slave YES +statement/com/Reset connection YES +statement/com/Reset stmt YES +statement/com/Set option YES +statement/com/Shutdown YES +statement/com/Slave_IO YES +statement/com/Slave_SQL YES +statement/com/Slave_worker YES +statement/com/Sleep YES +statement/com/Statistics YES +statement/com/Table Dump YES +statement/com/Time YES +statement/com/Unimpl get tid YES +UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'; +TRUNCATE TABLE performance_schema.setup_actors; +INSERT INTO performance_schema.setup_actors VALUES ('%', '%', '%', 'YES', 'YES'); +UPDATE performance_schema.setup_consumers SET enabled = 'YES'; +UPDATE performance_schema.threads SET instrumented = 'YES'; diff --git a/mysql-test/suite/sysschema/r/pr_ps_setup_show_enabled.result b/mysql-test/suite/sysschema/r/pr_ps_setup_show_enabled.result new file mode 100644 index 00000000..261ccb19 --- /dev/null +++ b/mysql-test/suite/sysschema/r/pr_ps_setup_show_enabled.result @@ -0,0 +1,362 @@ +INSERT INTO performance_schema.setup_actors +VALUES ('localhost', 'foo', '%', 'YES', 'YES'), +('localhost', 'bar', '%', 'NO', 'NO'); +UPDATE performance_schema.setup_instruments +SET ENABLED = 'NO' + WHERE NAME NOT LIKE 'memory/performance_schema/%' + AND NAME NOT LIKE 'stage/innodb/%' + AND NAME NOT LIKE 'statement/com/%' + AND NAME <> 'idle'; +UPDATE performance_schema.setup_consumers +SET ENABLED = 'NO' + WHERE NAME LIKE '%\_history\_long'; +UPDATE performance_schema.threads +SET INSTRUMENTED = 'NO' + WHERE NAME LIKE 'thread/innodb/srv\_%' + OR NAME LIKE '%con\_%' + OR NAME LIKE '%signal_handler%'; +CALL sys.ps_setup_show_enabled(FALSE, FALSE); +performance_schema_enabled +1 +enabled_users +'%'@'%' +'foo'@'localhost' +object_type objects enabled timed +EVENT %.% YES YES +FUNCTION %.% YES YES +PROCEDURE %.% YES YES +TABLE %.% YES YES +TRIGGER %.% YES YES +enabled_consumers +events_stages_current +events_stages_history +events_statements_current +events_statements_history +events_transactions_current +events_transactions_history +events_waits_current +events_waits_history +global_instrumentation +statements_digest +thread_instrumentation +CALL sys.ps_setup_show_enabled(TRUE, FALSE); +performance_schema_enabled +1 +enabled_users +'%'@'%' +'foo'@'localhost' +object_type objects enabled timed +EVENT %.% YES YES +FUNCTION %.% YES YES +PROCEDURE %.% YES YES +TABLE %.% YES YES +TRIGGER %.% YES YES +enabled_consumers +events_stages_current +events_stages_history +events_statements_current +events_statements_history +events_transactions_current +events_transactions_history +events_waits_current +events_waits_history +global_instrumentation +statements_digest +thread_instrumentation +enabled_instruments timed +idle YES +memory/performance_schema/accounts NO +memory/performance_schema/cond_class NO +memory/performance_schema/cond_instances NO +memory/performance_schema/events_stages_history NO +memory/performance_schema/events_stages_history_long NO +memory/performance_schema/events_stages_summary_by_account_by_event_name NO +memory/performance_schema/events_stages_summary_by_host_by_event_name NO +memory/performance_schema/events_stages_summary_by_thread_by_event_name NO +memory/performance_schema/events_stages_summary_by_user_by_event_name NO +memory/performance_schema/events_stages_summary_global_by_event_name NO +memory/performance_schema/events_statements_current NO +memory/performance_schema/events_statements_current.sqltext NO +memory/performance_schema/events_statements_current.tokens NO +memory/performance_schema/events_statements_history NO +memory/performance_schema/events_statements_history.sqltext NO +memory/performance_schema/events_statements_history.tokens NO +memory/performance_schema/events_statements_history_long NO +memory/performance_schema/events_statements_history_long.sqltext NO +memory/performance_schema/events_statements_history_long.tokens NO +memory/performance_schema/events_statements_summary_by_account_by_event_name NO +memory/performance_schema/events_statements_summary_by_digest NO +memory/performance_schema/events_statements_summary_by_digest.tokens NO +memory/performance_schema/events_statements_summary_by_host_by_event_name NO +memory/performance_schema/events_statements_summary_by_program NO +memory/performance_schema/events_statements_summary_by_thread_by_event_name NO +memory/performance_schema/events_statements_summary_by_user_by_event_name NO +memory/performance_schema/events_statements_summary_global_by_event_name NO +memory/performance_schema/events_transactions_history NO +memory/performance_schema/events_transactions_history_long NO +memory/performance_schema/events_transactions_summary_by_account_by_event_name NO +memory/performance_schema/events_transactions_summary_by_host_by_event_name NO +memory/performance_schema/events_transactions_summary_by_thread_by_event_name NO +memory/performance_schema/events_transactions_summary_by_user_by_event_name NO +memory/performance_schema/events_waits_history NO +memory/performance_schema/events_waits_history_long NO +memory/performance_schema/events_waits_summary_by_account_by_event_name NO +memory/performance_schema/events_waits_summary_by_host_by_event_name NO +memory/performance_schema/events_waits_summary_by_thread_by_event_name NO +memory/performance_schema/events_waits_summary_by_user_by_event_name NO +memory/performance_schema/file_class NO +memory/performance_schema/file_handle NO +memory/performance_schema/file_instances NO +memory/performance_schema/hosts NO +memory/performance_schema/memory_class NO +memory/performance_schema/memory_summary_by_account_by_event_name NO +memory/performance_schema/memory_summary_by_host_by_event_name NO +memory/performance_schema/memory_summary_by_thread_by_event_name NO +memory/performance_schema/memory_summary_by_user_by_event_name NO +memory/performance_schema/memory_summary_global_by_event_name NO +memory/performance_schema/metadata_locks NO +memory/performance_schema/mutex_class NO +memory/performance_schema/mutex_instances NO +memory/performance_schema/prepared_statements_instances NO +memory/performance_schema/rwlock_class NO +memory/performance_schema/rwlock_instances NO +memory/performance_schema/scalable_buffer NO +memory/performance_schema/session_connect_attrs NO +memory/performance_schema/setup_actors NO +memory/performance_schema/setup_objects NO +memory/performance_schema/socket_class NO +memory/performance_schema/socket_instances NO +memory/performance_schema/stage_class NO +memory/performance_schema/statement_class NO +memory/performance_schema/table_handles NO +memory/performance_schema/table_io_waits_summary_by_index_usage NO +memory/performance_schema/table_lock_waits_summary_by_table NO +memory/performance_schema/table_shares NO +memory/performance_schema/threads NO +memory/performance_schema/thread_class NO +memory/performance_schema/users NO +stage/innodb/alter table (end) YES +stage/innodb/alter table (insert) YES +stage/innodb/alter table (log apply index) YES +stage/innodb/alter table (log apply table) YES +stage/innodb/alter table (merge sort) YES +stage/innodb/alter table (read PK and internal sort) YES +stage/innodb/buffer pool load YES +statement/com/Binlog Dump YES +statement/com/Bulk_execute YES +statement/com/Change user YES +statement/com/Close stmt YES +statement/com/Connect YES +statement/com/Connect Out YES +statement/com/Create DB YES +statement/com/Daemon YES +statement/com/Debug YES +statement/com/Delayed insert YES +statement/com/Drop DB YES +statement/com/Error YES +statement/com/Execute YES +statement/com/Fetch YES +statement/com/Field List YES +statement/com/Init DB YES +statement/com/Kill YES +statement/com/Long Data YES +statement/com/Ping YES +statement/com/Prepare YES +statement/com/Processlist YES +statement/com/Quit YES +statement/com/Refresh YES +statement/com/Register Slave YES +statement/com/Reset connection YES +statement/com/Reset stmt YES +statement/com/Set option YES +statement/com/Shutdown YES +statement/com/Slave_IO YES +statement/com/Slave_SQL YES +statement/com/Slave_worker YES +statement/com/Sleep YES +statement/com/Statistics YES +statement/com/Table Dump YES +statement/com/Time YES +statement/com/Unimpl get tid YES +CALL sys.ps_setup_show_enabled(FALSE, TRUE); +performance_schema_enabled +1 +enabled_users +'%'@'%' +'foo'@'localhost' +object_type objects enabled timed +EVENT %.% YES YES +FUNCTION %.% YES YES +PROCEDURE %.% YES YES +TABLE %.% YES YES +TRIGGER %.% YES YES +enabled_consumers +events_stages_current +events_stages_history +events_statements_current +events_statements_history +events_transactions_current +events_transactions_history +events_waits_current +events_waits_history +global_instrumentation +statements_digest +thread_instrumentation +enabled_threads thread_type +aria/checkpoint_background BACKGROUND +innodb/page_cleaner_thread BACKGROUND +mysys/statement_timer BACKGROUND +root@localhost FOREGROUND +sql/main BACKGROUND +sql/manager BACKGROUND +CALL sys.ps_setup_show_enabled(TRUE, TRUE); +performance_schema_enabled +1 +enabled_users +'%'@'%' +'foo'@'localhost' +object_type objects enabled timed +EVENT %.% YES YES +FUNCTION %.% YES YES +PROCEDURE %.% YES YES +TABLE %.% YES YES +TRIGGER %.% YES YES +enabled_consumers +events_stages_current +events_stages_history +events_statements_current +events_statements_history +events_transactions_current +events_transactions_history +events_waits_current +events_waits_history +global_instrumentation +statements_digest +thread_instrumentation +enabled_threads thread_type +aria/checkpoint_background BACKGROUND +innodb/page_cleaner_thread BACKGROUND +mysys/statement_timer BACKGROUND +root@localhost FOREGROUND +sql/main BACKGROUND +sql/manager BACKGROUND +enabled_instruments timed +idle YES +memory/performance_schema/accounts NO +memory/performance_schema/cond_class NO +memory/performance_schema/cond_instances NO +memory/performance_schema/events_stages_history NO +memory/performance_schema/events_stages_history_long NO +memory/performance_schema/events_stages_summary_by_account_by_event_name NO +memory/performance_schema/events_stages_summary_by_host_by_event_name NO +memory/performance_schema/events_stages_summary_by_thread_by_event_name NO +memory/performance_schema/events_stages_summary_by_user_by_event_name NO +memory/performance_schema/events_stages_summary_global_by_event_name NO +memory/performance_schema/events_statements_current NO +memory/performance_schema/events_statements_current.sqltext NO +memory/performance_schema/events_statements_current.tokens NO +memory/performance_schema/events_statements_history NO +memory/performance_schema/events_statements_history.sqltext NO +memory/performance_schema/events_statements_history.tokens NO +memory/performance_schema/events_statements_history_long NO +memory/performance_schema/events_statements_history_long.sqltext NO +memory/performance_schema/events_statements_history_long.tokens NO +memory/performance_schema/events_statements_summary_by_account_by_event_name NO +memory/performance_schema/events_statements_summary_by_digest NO +memory/performance_schema/events_statements_summary_by_digest.tokens NO +memory/performance_schema/events_statements_summary_by_host_by_event_name NO +memory/performance_schema/events_statements_summary_by_program NO +memory/performance_schema/events_statements_summary_by_thread_by_event_name NO +memory/performance_schema/events_statements_summary_by_user_by_event_name NO +memory/performance_schema/events_statements_summary_global_by_event_name NO +memory/performance_schema/events_transactions_history NO +memory/performance_schema/events_transactions_history_long NO +memory/performance_schema/events_transactions_summary_by_account_by_event_name NO +memory/performance_schema/events_transactions_summary_by_host_by_event_name NO +memory/performance_schema/events_transactions_summary_by_thread_by_event_name NO +memory/performance_schema/events_transactions_summary_by_user_by_event_name NO +memory/performance_schema/events_waits_history NO +memory/performance_schema/events_waits_history_long NO +memory/performance_schema/events_waits_summary_by_account_by_event_name NO +memory/performance_schema/events_waits_summary_by_host_by_event_name NO +memory/performance_schema/events_waits_summary_by_thread_by_event_name NO +memory/performance_schema/events_waits_summary_by_user_by_event_name NO +memory/performance_schema/file_class NO +memory/performance_schema/file_handle NO +memory/performance_schema/file_instances NO +memory/performance_schema/hosts NO +memory/performance_schema/memory_class NO +memory/performance_schema/memory_summary_by_account_by_event_name NO +memory/performance_schema/memory_summary_by_host_by_event_name NO +memory/performance_schema/memory_summary_by_thread_by_event_name NO +memory/performance_schema/memory_summary_by_user_by_event_name NO +memory/performance_schema/memory_summary_global_by_event_name NO +memory/performance_schema/metadata_locks NO +memory/performance_schema/mutex_class NO +memory/performance_schema/mutex_instances NO +memory/performance_schema/prepared_statements_instances NO +memory/performance_schema/rwlock_class NO +memory/performance_schema/rwlock_instances NO +memory/performance_schema/scalable_buffer NO +memory/performance_schema/session_connect_attrs NO +memory/performance_schema/setup_actors NO +memory/performance_schema/setup_objects NO +memory/performance_schema/socket_class NO +memory/performance_schema/socket_instances NO +memory/performance_schema/stage_class NO +memory/performance_schema/statement_class NO +memory/performance_schema/table_handles NO +memory/performance_schema/table_io_waits_summary_by_index_usage NO +memory/performance_schema/table_lock_waits_summary_by_table NO +memory/performance_schema/table_shares NO +memory/performance_schema/threads NO +memory/performance_schema/thread_class NO +memory/performance_schema/users NO +stage/innodb/alter table (end) YES +stage/innodb/alter table (insert) YES +stage/innodb/alter table (log apply index) YES +stage/innodb/alter table (log apply table) YES +stage/innodb/alter table (merge sort) YES +stage/innodb/alter table (read PK and internal sort) YES +stage/innodb/buffer pool load YES +statement/com/Binlog Dump YES +statement/com/Bulk_execute YES +statement/com/Change user YES +statement/com/Close stmt YES +statement/com/Connect YES +statement/com/Connect Out YES +statement/com/Create DB YES +statement/com/Daemon YES +statement/com/Debug YES +statement/com/Delayed insert YES +statement/com/Drop DB YES +statement/com/Error YES +statement/com/Execute YES +statement/com/Fetch YES +statement/com/Field List YES +statement/com/Init DB YES +statement/com/Kill YES +statement/com/Long Data YES +statement/com/Ping YES +statement/com/Prepare YES +statement/com/Processlist YES +statement/com/Quit YES +statement/com/Refresh YES +statement/com/Register Slave YES +statement/com/Reset connection YES +statement/com/Reset stmt YES +statement/com/Set option YES +statement/com/Shutdown YES +statement/com/Slave_IO YES +statement/com/Slave_SQL YES +statement/com/Slave_worker YES +statement/com/Sleep YES +statement/com/Statistics YES +statement/com/Table Dump YES +statement/com/Time YES +statement/com/Unimpl get tid YES +UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'; +TRUNCATE TABLE performance_schema.setup_actors; +INSERT INTO performance_schema.setup_actors VALUES ('%', '%', '%', 'YES', 'YES'); +UPDATE performance_schema.setup_consumers SET enabled = 'YES'; +UPDATE performance_schema.threads SET instrumented = 'YES'; diff --git a/mysql-test/suite/sysschema/r/pr_ps_setup_show_enabled_consumers.result b/mysql-test/suite/sysschema/r/pr_ps_setup_show_enabled_consumers.result new file mode 100644 index 00000000..283a58f6 --- /dev/null +++ b/mysql-test/suite/sysschema/r/pr_ps_setup_show_enabled_consumers.result @@ -0,0 +1,21 @@ +UPDATE performance_schema.setup_consumers +SET ENABLED = 'NO' + WHERE NAME LIKE '%\_history\_long'; +CALL sys.ps_setup_show_enabled_consumers(); +enabled_consumers +events_stages_current +events_stages_history +events_statements_current +events_statements_history +events_transactions_current +events_transactions_history +events_waits_current +events_waits_history +global_instrumentation +statements_digest +thread_instrumentation +UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'; +TRUNCATE TABLE performance_schema.setup_actors; +INSERT INTO performance_schema.setup_actors VALUES ('%', '%', '%', 'YES', 'YES'); +UPDATE performance_schema.setup_consumers SET enabled = 'YES'; +UPDATE performance_schema.threads SET instrumented = 'YES'; diff --git a/mysql-test/suite/sysschema/r/pr_ps_setup_show_enabled_instruments.result b/mysql-test/suite/sysschema/r/pr_ps_setup_show_enabled_instruments.result new file mode 100644 index 00000000..36399f0d --- /dev/null +++ b/mysql-test/suite/sysschema/r/pr_ps_setup_show_enabled_instruments.result @@ -0,0 +1,127 @@ +UPDATE performance_schema.setup_instruments +SET ENABLED = 'NO' + WHERE NAME NOT LIKE 'memory/performance_schema/%' + AND NAME NOT LIKE 'stage/innodb/%' + AND NAME NOT LIKE 'statement/com/%' + AND NAME <> 'idle'; +CALL sys.ps_setup_show_enabled_instruments(); +enabled_instruments timed +idle YES +memory/performance_schema/accounts NO +memory/performance_schema/cond_class NO +memory/performance_schema/cond_instances NO +memory/performance_schema/events_stages_history NO +memory/performance_schema/events_stages_history_long NO +memory/performance_schema/events_stages_summary_by_account_by_event_name NO +memory/performance_schema/events_stages_summary_by_host_by_event_name NO +memory/performance_schema/events_stages_summary_by_thread_by_event_name NO +memory/performance_schema/events_stages_summary_by_user_by_event_name NO +memory/performance_schema/events_stages_summary_global_by_event_name NO +memory/performance_schema/events_statements_current NO +memory/performance_schema/events_statements_current.sqltext NO +memory/performance_schema/events_statements_current.tokens NO +memory/performance_schema/events_statements_history NO +memory/performance_schema/events_statements_history.sqltext NO +memory/performance_schema/events_statements_history.tokens NO +memory/performance_schema/events_statements_history_long NO +memory/performance_schema/events_statements_history_long.sqltext NO +memory/performance_schema/events_statements_history_long.tokens NO +memory/performance_schema/events_statements_summary_by_account_by_event_name NO +memory/performance_schema/events_statements_summary_by_digest NO +memory/performance_schema/events_statements_summary_by_digest.tokens NO +memory/performance_schema/events_statements_summary_by_host_by_event_name NO +memory/performance_schema/events_statements_summary_by_program NO +memory/performance_schema/events_statements_summary_by_thread_by_event_name NO +memory/performance_schema/events_statements_summary_by_user_by_event_name NO +memory/performance_schema/events_statements_summary_global_by_event_name NO +memory/performance_schema/events_transactions_history NO +memory/performance_schema/events_transactions_history_long NO +memory/performance_schema/events_transactions_summary_by_account_by_event_name NO +memory/performance_schema/events_transactions_summary_by_host_by_event_name NO +memory/performance_schema/events_transactions_summary_by_thread_by_event_name NO +memory/performance_schema/events_transactions_summary_by_user_by_event_name NO +memory/performance_schema/events_waits_history NO +memory/performance_schema/events_waits_history_long NO +memory/performance_schema/events_waits_summary_by_account_by_event_name NO +memory/performance_schema/events_waits_summary_by_host_by_event_name NO +memory/performance_schema/events_waits_summary_by_thread_by_event_name NO +memory/performance_schema/events_waits_summary_by_user_by_event_name NO +memory/performance_schema/file_class NO +memory/performance_schema/file_handle NO +memory/performance_schema/file_instances NO +memory/performance_schema/hosts NO +memory/performance_schema/memory_class NO +memory/performance_schema/memory_summary_by_account_by_event_name NO +memory/performance_schema/memory_summary_by_host_by_event_name NO +memory/performance_schema/memory_summary_by_thread_by_event_name NO +memory/performance_schema/memory_summary_by_user_by_event_name NO +memory/performance_schema/memory_summary_global_by_event_name NO +memory/performance_schema/metadata_locks NO +memory/performance_schema/mutex_class NO +memory/performance_schema/mutex_instances NO +memory/performance_schema/prepared_statements_instances NO +memory/performance_schema/rwlock_class NO +memory/performance_schema/rwlock_instances NO +memory/performance_schema/scalable_buffer NO +memory/performance_schema/session_connect_attrs NO +memory/performance_schema/setup_actors NO +memory/performance_schema/setup_objects NO +memory/performance_schema/socket_class NO +memory/performance_schema/socket_instances NO +memory/performance_schema/stage_class NO +memory/performance_schema/statement_class NO +memory/performance_schema/table_handles NO +memory/performance_schema/table_io_waits_summary_by_index_usage NO +memory/performance_schema/table_lock_waits_summary_by_table NO +memory/performance_schema/table_shares NO +memory/performance_schema/threads NO +memory/performance_schema/thread_class NO +memory/performance_schema/users NO +stage/innodb/alter table (end) YES +stage/innodb/alter table (insert) YES +stage/innodb/alter table (log apply index) YES +stage/innodb/alter table (log apply table) YES +stage/innodb/alter table (merge sort) YES +stage/innodb/alter table (read PK and internal sort) YES +stage/innodb/buffer pool load YES +statement/com/Binlog Dump YES +statement/com/Bulk_execute YES +statement/com/Change user YES +statement/com/Close stmt YES +statement/com/Connect YES +statement/com/Connect Out YES +statement/com/Create DB YES +statement/com/Daemon YES +statement/com/Debug YES +statement/com/Delayed insert YES +statement/com/Drop DB YES +statement/com/Error YES +statement/com/Execute YES +statement/com/Fetch YES +statement/com/Field List YES +statement/com/Init DB YES +statement/com/Kill YES +statement/com/Long Data YES +statement/com/Ping YES +statement/com/Prepare YES +statement/com/Processlist YES +statement/com/Quit YES +statement/com/Refresh YES +statement/com/Register Slave YES +statement/com/Reset connection YES +statement/com/Reset stmt YES +statement/com/Set option YES +statement/com/Shutdown YES +statement/com/Slave_IO YES +statement/com/Slave_SQL YES +statement/com/Slave_worker YES +statement/com/Sleep YES +statement/com/Statistics YES +statement/com/Table Dump YES +statement/com/Time YES +statement/com/Unimpl get tid YES +UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'; +TRUNCATE TABLE performance_schema.setup_actors; +INSERT INTO performance_schema.setup_actors VALUES ('%', '%', '%', 'YES', 'YES'); +UPDATE performance_schema.setup_consumers SET enabled = 'YES'; +UPDATE performance_schema.threads SET instrumented = 'YES'; diff --git a/mysql-test/suite/sysschema/r/pr_ps_trace_statement_digest.result b/mysql-test/suite/sysschema/r/pr_ps_trace_statement_digest.result new file mode 100644 index 00000000..99854665 --- /dev/null +++ b/mysql-test/suite/sysschema/r/pr_ps_trace_statement_digest.result @@ -0,0 +1,30 @@ +use test; +SET @threadid = sys.ps_thread_id(NULL); +CREATE TABLE t1 (id INT PRIMARY KEY, val int) ENGINE=MEMORY; +INSERT INTO test.t1 VALUES (1, 9); +SET @digest.insert = (SELECT DIGEST FROM performance_schema.events_statements_history WHERE THREAD_ID = @threadid AND SQL_TEXT LIKE 'INSERT INTO test.t1 VALUES (1, 9)'); +SELECT * FROM t1; +id val +1 9 +SET @digest.select = (SELECT DIGEST FROM performance_schema.events_statements_history WHERE THREAD_ID = @threadid AND SQL_TEXT LIKE 'SELECT * FROM t1'); +SHOW CREATE TABLE test.t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `val` int(11) DEFAULT NULL, + PRIMARY KEY (`id`) +) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SET @digest.show = (SELECT DIGEST FROM performance_schema.events_statements_history WHERE THREAD_ID = @threadid AND SQL_TEXT LIKE 'SHOW CREATE TABLE test.t1'); +CREATE SCHEMA test_sys; +use test_sys; +CALL sys.ps_trace_statement_digest(@digest.insert, 0.5, 0.1, FALSE, FALSE); +CALL sys.ps_trace_statement_digest(@digest.select, 0.5, 0.1, FALSE, FALSE); +CALL sys.ps_trace_statement_digest(@digest.show , 0.5, 0.1, FALSE, FALSE); +CALL sys.ps_trace_statement_digest(@digest.insert, 0.5, 0.1, TRUE , FALSE); +use test; +DROP SCHEMA test_sys; +DROP TABLE t1; +SET @threadid = NULL, +@digest.insert = NULL, +@digest.select = NULL, +@digest.show = NULL; diff --git a/mysql-test/suite/sysschema/r/pr_statement_performance_analyzer.result b/mysql-test/suite/sysschema/r/pr_statement_performance_analyzer.result new file mode 100644 index 00000000..c7bb029d --- /dev/null +++ b/mysql-test/suite/sysschema/r/pr_statement_performance_analyzer.result @@ -0,0 +1,211 @@ +use test; +DROP TABLE IF EXISTS t1; +DROP TEMPORARY TABLE IF EXISTS tmp_digests_ini; +DROP VIEW IF EXISTS view_digests; +CREATE TABLE t1 (id INT PRIMARY KEY, val int); +connect con1,localhost,root,,; +connection con1; +use test; +connection default; +UPDATE performance_schema.threads SET INSTRUMENTED = IF(THREAD_ID = CON1_THREAD_ID, 'YES', 'NO'); +CALL sys.ps_setup_enable_consumer('events_statements_history_long'); +CALL sys.ps_truncate_all_tables(FALSE); +connection con1; +INSERT INTO t1 VALUES (1, 0); +connection default; +connection con1; +UPDATE t1 SET val = 1 WHERE id = 1; +connection default; +connection con1; +SELECT t1a.* FROM t1 AS t1a LEFT OUTER JOIN (SELECT * FROM t1 AS t1b1 INNER JOIN t1 AS t1b2 USING (id, val)) AS t1b ON t1b.id > t1a.id ORDER BY t1a.val, t1a.id; +id val +1 1 +connection default; +CALL sys.statement_performance_analyzer('create_tmp', 'test.tmp_digests_ini', NULL); +CALL sys.statement_performance_analyzer('snapshot', NULL, NULL); +CALL sys.statement_performance_analyzer('save', 'test.tmp_digests_ini', NULL); +DO SLEEP(1.2); +connection con1; +INSERT INTO t1 VALUES (2, 0); +UPDATE t1 SET val = 1 WHERE id = 2; +SELECT t1a.* FROM t1 AS t1a LEFT OUTER JOIN (SELECT * FROM t1 AS t1b1 INNER JOIN t1 AS t1b2 USING (id, val)) AS t1b ON t1b.id > t1a.id ORDER BY t1a.val, t1a.id; +id val +1 1 +2 1 +disconnect con1; +connection default; +CALL sys.statement_performance_analyzer('snapshot', NULL, NULL); +SELECT DIGEST, COUNT_STAR FROM performance_schema.events_statements_summary_by_digest; +DIGEST COUNT_STAR +DIGEST_INSERT 2 +DIGEST_SELECT 2 +DIGEST_UPDATE 2 +CALL sys.statement_performance_analyzer('overall', NULL, 'with_runtimes_in_95th_percentile'); +CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_runtimes_in_95th_percentile'); +CALL sys.statement_performance_analyzer('overall', NULL, 'analysis'); +Next Output +QUERY_INSERT test 2 0 0 LATENCY LATENCY LATENCY LATENCY 0 0 0 0 2 1 0 0 0 0 DIGEST_INSERT FIRST_SEEN LAST_SEEN +QUERY_SELECT test * 2 0 0 LATENCY LATENCY LATENCY LATENCY 3 2 9 5 0 0 0 0 2 0 DIGEST_SELECT FIRST_SEEN LAST_SEEN +QUERY_UPDATE test 2 0 0 LATENCY LATENCY LATENCY LATENCY 0 0 2 1 2 1 0 0 0 0 DIGEST_UPDATE FIRST_SEEN LAST_SEEN +Top 100 Queries Ordered by Total Latency +query db full_scan exec_count err_count warn_count total_latency max_latency avg_latency lock_latency rows_sent rows_sent_avg rows_examined rows_examined_avg rows_affected rows_affected_avg tmp_tables tmp_disk_tables rows_sorted sort_merge_passes digest first_seen last_seen +CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'analysis'); +Next Output +QUERY_INSERT test 1 0 0 LATENCY LATENCY LATENCY LATENCY 0 0 0 0 1 1 0 0 0 0 DIGEST_INSERT FIRST_SEEN LAST_SEEN +QUERY_SELECT test * 1 0 0 LATENCY LATENCY LATENCY LATENCY 2 2 9 9 0 0 0 0 2 0 DIGEST_SELECT FIRST_SEEN LAST_SEEN +QUERY_UPDATE test 1 0 0 LATENCY LATENCY LATENCY LATENCY 0 0 1 1 1 1 0 0 0 0 DIGEST_UPDATE FIRST_SEEN LAST_SEEN +Top 100 Queries Ordered by Total Latency +query db full_scan exec_count err_count warn_count total_latency max_latency avg_latency lock_latency rows_sent rows_sent_avg rows_examined rows_examined_avg rows_affected rows_affected_avg tmp_tables tmp_disk_tables rows_sorted sort_merge_passes digest first_seen last_seen +CALL sys.statement_performance_analyzer('overall', NULL, 'with_errors_or_warnings'); +Next Output +Top 100 Queries with Errors +query db exec_count errors error_pct warnings warning_pct first_seen last_seen digest +CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_errors_or_warnings'); +Next Output +Top 100 Queries with Errors +query db exec_count errors error_pct warnings warning_pct first_seen last_seen digest +CALL sys.statement_performance_analyzer('overall', NULL, 'with_full_table_scans'); +Next Output +Top 100 Queries with Full Table Scan +query db exec_count total_latency no_index_used_count no_good_index_used_count no_index_used_pct rows_sent rows_examined rows_sent_avg rows_examined_avg first_seen last_seen digest +QUERY_SELECT test 2 LATENCY 1 0 50 3 9 2 5 FIRST_SEEN LAST_SEEN DIGEST_SELECT +CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_full_table_scans'); +Next Output +Top 100 Queries with Full Table Scan +query db exec_count total_latency no_index_used_count no_good_index_used_count no_index_used_pct rows_sent rows_examined rows_sent_avg rows_examined_avg first_seen last_seen digest +QUERY_SELECT test 1 LATENCY 1 0 100 2 9 2 9 FIRST_SEEN LAST_SEEN DIGEST_SELECT +CALL sys.statement_performance_analyzer('overall', NULL, 'with_sorting'); +Next Output +Top 100 Queries with Sorting +query db exec_count total_latency sort_merge_passes avg_sort_merges sorts_using_scans sort_using_range rows_sorted avg_rows_sorted first_seen last_seen digest +QUERY_SELECT test 2 LATENCY 0 0 1 0 2 1 FIRST_SEEN LAST_SEEN DIGEST_SELECT +CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_sorting'); +Next Output +Top 100 Queries with Sorting +query db exec_count total_latency sort_merge_passes avg_sort_merges sorts_using_scans sort_using_range rows_sorted avg_rows_sorted first_seen last_seen digest +QUERY_SELECT test 1 LATENCY 0 0 1 0 2 2 FIRST_SEEN LAST_SEEN DIGEST_SELECT +CALL sys.statement_performance_analyzer('overall', NULL, 'with_temp_tables'); +Next Output +Top 100 Queries with Internal Temporary Tables +query db exec_count total_latency memory_tmp_tables disk_tmp_tables avg_tmp_tables_per_query tmp_tables_to_disk_pct first_seen last_seen digest +CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_temp_tables'); +Next Output +Top 100 Queries with Internal Temporary Tables +query db exec_count total_latency memory_tmp_tables disk_tmp_tables avg_tmp_tables_per_query tmp_tables_to_disk_pct first_seen last_seen digest +CREATE VIEW test.view_digests AS +SELECT sys.format_statement(DIGEST_TEXT) AS query, +SCHEMA_NAME AS db, +COUNT_STAR AS exec_count, +sys.format_time(SUM_TIMER_WAIT) AS total_latency, +sys.format_time(AVG_TIMER_WAIT) AS avg_latency, +ROUND(IFNULL(SUM_ROWS_SENT / NULLIF(COUNT_STAR, 0), 0)) AS rows_sent_avg, +ROUND(IFNULL(SUM_ROWS_EXAMINED / NULLIF(COUNT_STAR, 0), 0)) AS rows_examined_avg, +ROUND(IFNULL(SUM_ROWS_AFFECTED / NULLIF(COUNT_STAR, 0), 0)) AS rows_affected_avg, +DIGEST AS digest +FROM performance_schema.events_statements_summary_by_digest +ORDER BY SUBSTRING(query, 1, 6); +SET @sys.statement_performance_analyzer.view = 'test.view_digests'; +CALL sys.statement_performance_analyzer('overall', NULL, 'custom'); +Next Output +Top 100 Queries Using Custom View +query db exec_count total_latency avg_latency rows_sent_avg rows_examined_avg rows_affected_avg digest +QUERY_INSERT test 2 LATENCY LATENCY 0 0 1 DIGEST_INSERT +QUERY_SELECT test 2 LATENCY LATENCY 2 5 0 DIGEST_SELECT +QUERY_UPDATE test 2 LATENCY LATENCY 0 1 1 DIGEST_UPDATE +CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'custom'); +Next Output +Top 100 Queries Using Custom View +query db exec_count total_latency avg_latency rows_sent_avg rows_examined_avg rows_affected_avg digest +QUERY_INSERT test 1 LATENCY LATENCY 0 0 1 DIGEST_INSERT +QUERY_SELECT test 1 LATENCY LATENCY 2 9 0 DIGEST_SELECT +QUERY_UPDATE test 1 LATENCY LATENCY 0 1 1 DIGEST_UPDATE +SET @sys.statement_performance_analyzer.limit = 2; +CALL sys.statement_performance_analyzer('overall', NULL, 'custom'); +Next Output +Top 2 Queries Using Custom View +query db exec_count total_latency avg_latency rows_sent_avg rows_examined_avg rows_affected_avg digest +QUERY_INSERT test 2 LATENCY LATENCY 0 0 1 DIGEST_INSERT +QUERY_SELECT test 2 LATENCY LATENCY 2 5 0 DIGEST_SELECT +SET SESSION sql_mode = 'NO_AUTO_CREATE_USER'; +CALL sys.statement_performance_analyzer('do magic', NULL, NULL); +ERROR 45000: Unknown action. Supported actions are: cleanup, create_table, create_tmp, delta, overall, save, snapshot +SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; +CALL sys.statement_performance_analyzer('do magic', NULL, NULL); +ERROR 01000: Data truncated for column 'in_action' at row 0 +CALL sys.statement_performance_analyzer('create_tmp', 'sys.tmp_digests', NULL); +ERROR 45000: Invalid value for in_table: `sys`.`tmp_digests` is reserved table name. +CALL sys.statement_performance_analyzer('create_tmp', 'sys.tmp_digests_delta', NULL); +ERROR 45000: Invalid value for in_table: `sys`.`tmp_digests_delta` is reserved table name. +CALL sys.statement_performance_analyzer('create_tmp', 'tmp_digests', NULL); +ERROR 45000: Invalid value for in_table: `sys`.`tmp_digests` is reserved table name. +CALL sys.statement_performance_analyzer('create_tmp', 'test.tmp_digests', NULL); +CREATE TABLE test.tmp_unsupported LIKE test.tmp_digests_ini; +CALL sys.statement_performance_analyzer('create_tmp', 'test.tmp_digests_ini', NULL); +ERROR 45000: Cannot create the table `test`.`tmp_digests_ini` as it already exists. +CALL sys.statement_performance_analyzer('create_table', 'test.tmp_digests_ini', NULL); +ERROR 45000: Cannot create the table `test`.`tmp_digests_ini` as it already exists as a temporary table. +CALL sys.statement_performance_analyzer('create_table', 'test.tmp_unsupported', NULL); +ERROR 45000: Cannot create the table `test`.`tmp_unsupported` as it already exists. +ALTER TABLE test.tmp_unsupported ADD COLUMN myvar int DEFAULT 0; +CALL sys.statement_performance_analyzer('save', 'test.tmp_unsupported', NULL); +ERROR 45000: The table `test`.`tmp_unsupported` has the wrong definition. +CALL sys.statement_performance_analyzer('snapshot', 'test.new_table', NULL); +ERROR 45000: The snapshot action requires in_table to be NULL, NOW() or specify an existing table. The table ...`.`new_table` does not exist. +CALL sys.statement_performance_analyzer('overall', 'test.new_table', 'analysis'); +ERROR 45000: The overall action requires in_table to be NULL, NOW() or specify an existing table. The table ...`.`new_table` does not exist. +CALL sys.statement_performance_analyzer('delta', 'test.new_table', 'analysis'); +ERROR 45000: The delta action requires in_table to be an existing table. The table `test`.`new_table` does not exist. +CALL sys.statement_performance_analyzer('save', 'test.new_table', NULL); +ERROR 45000: The save action requires in_table to be an existing table. The table `test`.`new_table` does not exist. +SET @sys.statement_performance_analyzer.view = NULL; +DELETE FROM sys.sys_config WHERE variable = 'statement_performance_analyzer.view'; +CALL sys.statement_performance_analyzer('overall', NULL, 'custom'); +Next Output +Top 2 Queries Using Custom View +ERROR 45000: The @sys.statement_performance_analyzer.view user variable must be set with the view or query to use. +SET @sys.statement_performance_analyzer.view = 'test.tmp_unsupported'; +CALL sys.statement_performance_analyzer('overall', NULL, 'custom'); +Next Output +Top 2 Queries Using Custom View +ERROR 45000: The @sys.statement_performance_analyzer.view user variable is set but specified neither an existing view nor a query. +CALL sys.table_exists('sys', 'tmp_digests', @exists); +SELECT @exists; +@exists +TEMPORARY +CALL sys.table_exists('sys', 'tmp_digests_delta', @exists); +SELECT @exists; +@exists +TEMPORARY +CALL sys.statement_performance_analyzer('cleanup', NULL, NULL); +DROP TEMPORARY TABLE sys.tmp_digests; +ERROR 42S02: Unknown table 'sys.tmp_digests' +DROP TEMPORARY TABLE sys.tmp_digests_delta; +ERROR 42S02: Unknown table 'sys.tmp_digests_delta' +CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'analysis'); +ERROR 45000: An existing snapshot generated with the statement_performance_analyzer() must exist. +DROP TEMPORARY TABLE sys.tmp_digests_delta; +ERROR 42S02: Unknown table 'sys.tmp_digests_delta' +SET @identifier := REPEAT('a', 65); +CALL sys.statement_performance_analyzer('snapshot', CONCAT(@identifier, '.', @identifier), NULL); +ERROR 22001: Data too long for column 'in_table' at row 0 +DROP TEMPORARY TABLE test.tmp_digests_ini; +DROP TEMPORARY TABLE test.tmp_digests; +DROP TABLE test.tmp_unsupported; +DROP TABLE test.t1; +DROP VIEW view_digests; +SET @identifier := NULL; +SET SESSION sql_mode = @@global.sql_mode; +SET @sys.statement_performance_analyzer.limit = NULL; +SET @sys.statement_performance_analyzer.view = NULL; +UPDATE performance_schema.setup_consumers SET enabled = 'YES'; +UPDATE performance_schema.threads SET instrumented = 'YES'; +SET @sys.ignore_sys_config_triggers := true; +DELETE FROM sys.sys_config; +INSERT IGNORE INTO sys.sys_config (variable, value) VALUES +('statement_truncate_len', 64), +('statement_performance_analyzer.limit', 100), +('statement_performance_analyzer.view', NULL), +('diagnostics.allow_i_s_tables', 'OFF'), +('diagnostics.include_raw', 'OFF'), +('ps_thread_trx_info.max_length', 65535); +SET @sys.ignore_sys_config_triggers := NULL; diff --git a/mysql-test/suite/sysschema/r/pr_table_exists.result b/mysql-test/suite/sysschema/r/pr_table_exists.result new file mode 100644 index 00000000..76085bd7 --- /dev/null +++ b/mysql-test/suite/sysschema/r/pr_table_exists.result @@ -0,0 +1,72 @@ +CREATE TABLE t1 (id INT PRIMARY KEY); +CALL sys.table_exists('test', 't1', @exists); +SELECT @exists; +@exists +BASE TABLE +DROP TABLE t1; +CREATE view v_t1 AS SELECT 1; +CALL sys.table_exists('test', 'v_t1', @exists); +SELECT @exists; +@exists +VIEW +DROP VIEW v_t1; +CREATE TABLE tv (i int) with system versioning; +CALL sys.table_exists('test','tv',@exists); +SELECT @exists; +@exists +BASE TABLE +DROP TABLE tv; +CREATE SEQUENCE s; +CALL sys.table_exists('test','s',@exists); +SELECT @exists; +@exists +SEQUENCE +DROP SEQUENCE s; +CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY); +CALL sys.table_exists('test', 't1', @exists); +SELECT @exists; +@exists +TEMPORARY +DROP TEMPORARY TABLE t1; +CALL sys.table_exists('information_schema', 'all_plugins', @exists); +SELECT @exists; +@exists +SYSTEM VIEW +CALL sys.table_exists('test', 't2', @exists); +SELECT @exists; +@exists + +SET @identifier := REPEAT('a', 65); +CALL sys.table_exists(@identifier, 't1', @exists); +ERROR 22001: Data too long for column 'in_db' at row 1 +CALL sys.table_exists('test', @identifier, @exists); +ERROR 22001: Data too long for column 'in_table' at row 0 +SET @identifier := NULL; +# +# MDEV-28391: table_exists procedure fails with +# Incorrect table name with backtick identifiers +# +CREATE TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10)); +CALL sys.table_exists('test', 'ab`c', @tbl_type); +SELECT @tbl_type; +@tbl_type +BASE TABLE +DROP TABLE `ab``c`; +CREATE TEMPORARY TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10)); +CALL sys.table_exists('test', 'ab`c', @tbl_type); +SELECT @tbl_type; +@tbl_type +TEMPORARY +DROP TABLE `ab``c`; +CREATE TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10)); +CREATE TEMPORARY TABLE `ab``c` (t1_id int PRIMARY KEY, t1_val varchar(10)); +CALL sys.table_exists('test', 'ab`c', @tbl_type); +SELECT @tbl_type; +@tbl_type +TEMPORARY +# We cannot send quoted identifer to the procedure, no table will be found +CALL sys.table_exists('test', '`ab``c`', @tbl_type); +SELECT @tbl_type; +@tbl_type + +DROP TABLE `ab``c`; diff --git a/mysql-test/suite/sysschema/r/t_sys_config.result b/mysql-test/suite/sysschema/r/t_sys_config.result new file mode 100644 index 00000000..f13196c6 --- /dev/null +++ b/mysql-test/suite/sysschema/r/t_sys_config.result @@ -0,0 +1,46 @@ +SELECT COUNT(*) FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='sys'; +COUNT(*) +0 +DESC sys.sys_config; +Field Type Null Key Default Extra +variable varchar(128) NO PRI NULL +value varchar(128) YES NULL +set_time timestamp NO current_timestamp() on update current_timestamp() +set_by varchar(128) YES NULL +SELECT variable, value, set_by FROM sys.sys_config ORDER BY 1; +variable value set_by +diagnostics.allow_i_s_tables OFF NULL +diagnostics.include_raw OFF NULL +ps_thread_trx_info.max_length 65535 NULL +statement_performance_analyzer.limit 100 NULL +statement_performance_analyzer.view NULL NULL +statement_truncate_len 64 NULL +UPDATE sys.sys_config SET value = 128 WHERE variable = 'statement_truncate_len'; +SELECT variable, value, set_by FROM sys.sys_config ORDER BY 1; +variable value set_by +diagnostics.allow_i_s_tables OFF NULL +diagnostics.include_raw OFF NULL +ps_thread_trx_info.max_length 65535 NULL +statement_performance_analyzer.limit 100 NULL +statement_performance_analyzer.view NULL NULL +statement_truncate_len 128 NULL +INSERT INTO sys.sys_config (variable, value) VALUES ('foo', 'bar'); +SELECT variable, value, set_by FROM sys.sys_config ORDER BY 1; +variable value set_by +diagnostics.allow_i_s_tables OFF NULL +diagnostics.include_raw OFF NULL +foo bar NULL +ps_thread_trx_info.max_length 65535 NULL +statement_performance_analyzer.limit 100 NULL +statement_performance_analyzer.view NULL NULL +statement_truncate_len 128 NULL +SET @sys.ignore_sys_config_triggers := true; +DELETE FROM sys.sys_config; +INSERT IGNORE INTO sys.sys_config (variable, value) VALUES +('statement_truncate_len', 64), +('statement_performance_analyzer.limit', 100), +('statement_performance_analyzer.view', NULL), +('diagnostics.allow_i_s_tables', 'OFF'), +('diagnostics.include_raw', 'OFF'), +('ps_thread_trx_info.max_length', 65535); +SET @sys.ignore_sys_config_triggers := NULL; diff --git a/mysql-test/suite/sysschema/r/v_host_summary.result b/mysql-test/suite/sysschema/r/v_host_summary.result new file mode 100644 index 00000000..b619a8a5 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_host_summary.result @@ -0,0 +1,30 @@ +DESC sys.host_summary; +Field Type Null Key Default Extra +host varchar(255) YES NULL +statements decimal(64,0) YES NULL +statement_latency text YES NULL +statement_avg_latency text YES NULL +table_scans decimal(65,0) YES NULL +file_ios decimal(64,0) YES NULL +file_io_latency text YES NULL +current_connections decimal(41,0) YES NULL +total_connections decimal(41,0) YES NULL +unique_users bigint(21) NO 0 +current_memory text YES NULL +total_memory_allocated text YES NULL +SELECT * FROM sys.host_summary; +DESC sys.x$host_summary; +Field Type Null Key Default Extra +host varchar(255) YES NULL +statements decimal(64,0) YES NULL +statement_latency decimal(64,0) YES NULL +statement_avg_latency decimal(65,4) YES NULL +table_scans decimal(65,0) YES NULL +file_ios decimal(64,0) YES NULL +file_io_latency decimal(64,0) YES NULL +current_connections decimal(41,0) YES NULL +total_connections decimal(41,0) YES NULL +unique_users bigint(21) NO 0 +current_memory decimal(63,0) YES NULL +total_memory_allocated decimal(64,0) YES NULL +SELECT * FROM sys.x$host_summary; diff --git a/mysql-test/suite/sysschema/r/v_host_summary_by_file_io.result b/mysql-test/suite/sysschema/r/v_host_summary_by_file_io.result new file mode 100644 index 00000000..8e257163 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_host_summary_by_file_io.result @@ -0,0 +1,12 @@ +DESC sys.host_summary_by_file_io; +Field Type Null Key Default Extra +host varchar(255) YES NULL +ios decimal(42,0) YES NULL +io_latency text YES NULL +SELECT * FROM sys.host_summary_by_file_io; +DESC sys.x$host_summary_by_file_io; +Field Type Null Key Default Extra +host varchar(255) YES NULL +ios decimal(42,0) YES NULL +io_latency decimal(42,0) YES NULL +SELECT * FROM sys.x$host_summary_by_file_io; diff --git a/mysql-test/suite/sysschema/r/v_host_summary_by_file_io_type.result b/mysql-test/suite/sysschema/r/v_host_summary_by_file_io_type.result new file mode 100644 index 00000000..c9a56aa9 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_host_summary_by_file_io_type.result @@ -0,0 +1,16 @@ +DESC sys.host_summary_by_file_io_type; +Field Type Null Key Default Extra +host varchar(255) YES NULL +event_name varchar(128) NO NULL +total bigint(20) unsigned NO NULL +total_latency text YES NULL +max_latency text YES NULL +SELECT * FROM sys.host_summary_by_file_io_type; +DESC sys.x$host_summary_by_file_io_type; +Field Type Null Key Default Extra +host varchar(255) YES NULL +event_name varchar(128) NO NULL +total bigint(20) unsigned NO NULL +total_latency bigint(20) unsigned NO NULL +max_latency bigint(20) unsigned NO NULL +SELECT * FROM sys.x$host_summary_by_file_io_type; diff --git a/mysql-test/suite/sysschema/r/v_host_summary_by_stages.result b/mysql-test/suite/sysschema/r/v_host_summary_by_stages.result new file mode 100644 index 00000000..0ee6f255 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_host_summary_by_stages.result @@ -0,0 +1,16 @@ +DESC sys.host_summary_by_stages; +Field Type Null Key Default Extra +host varchar(255) YES NULL +event_name varchar(128) NO NULL +total bigint(20) unsigned NO NULL +total_latency text YES NULL +avg_latency text YES NULL +SELECT * FROM sys.host_summary_by_stages; +DESC sys.x$host_summary_by_stages; +Field Type Null Key Default Extra +host varchar(255) YES NULL +event_name varchar(128) NO NULL +total bigint(20) unsigned NO NULL +total_latency bigint(20) unsigned NO NULL +avg_latency bigint(20) unsigned NO NULL +SELECT * FROM sys.x$host_summary_by_stages; diff --git a/mysql-test/suite/sysschema/r/v_host_summary_by_statement_latency.result b/mysql-test/suite/sysschema/r/v_host_summary_by_statement_latency.result new file mode 100644 index 00000000..882a30b7 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_host_summary_by_statement_latency.result @@ -0,0 +1,24 @@ +DESC sys.host_summary_by_statement_latency; +Field Type Null Key Default Extra +host varchar(255) YES NULL +total decimal(42,0) YES NULL +total_latency text YES NULL +max_latency text YES NULL +lock_latency text YES NULL +rows_sent decimal(42,0) YES NULL +rows_examined decimal(42,0) YES NULL +rows_affected decimal(42,0) YES NULL +full_scans decimal(43,0) YES NULL +SELECT * FROM sys.host_summary_by_statement_latency; +DESC sys.x$host_summary_by_statement_latency; +Field Type Null Key Default Extra +host varchar(255) YES NULL +total decimal(42,0) YES NULL +total_latency decimal(42,0) YES NULL +max_latency bigint(20) unsigned YES NULL +lock_latency decimal(42,0) YES NULL +rows_sent decimal(42,0) YES NULL +rows_examined decimal(42,0) YES NULL +rows_affected decimal(42,0) YES NULL +full_scans decimal(43,0) YES NULL +SELECT * FROM sys.x$host_summary_by_statement_latency; diff --git a/mysql-test/suite/sysschema/r/v_host_summary_by_statement_type.result b/mysql-test/suite/sysschema/r/v_host_summary_by_statement_type.result new file mode 100644 index 00000000..eec8f869 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_host_summary_by_statement_type.result @@ -0,0 +1,26 @@ +DESC sys.host_summary_by_statement_type; +Field Type Null Key Default Extra +host varchar(255) YES NULL +statement varchar(128) YES NULL +total bigint(20) unsigned NO NULL +total_latency text YES NULL +max_latency text YES NULL +lock_latency text YES NULL +rows_sent bigint(20) unsigned NO NULL +rows_examined bigint(20) unsigned NO NULL +rows_affected bigint(20) unsigned NO NULL +full_scans bigint(21) unsigned NO 0 +SELECT * FROM sys.host_summary_by_statement_type; +DESC sys.x$host_summary_by_statement_type; +Field Type Null Key Default Extra +host varchar(255) YES NULL +statement varchar(128) YES NULL +total bigint(20) unsigned NO NULL +total_latency bigint(20) unsigned NO NULL +max_latency bigint(20) unsigned NO NULL +lock_latency bigint(20) unsigned NO NULL +rows_sent bigint(20) unsigned NO NULL +rows_examined bigint(20) unsigned NO NULL +rows_affected bigint(20) unsigned NO NULL +full_scans bigint(21) unsigned NO 0 +SELECT * FROM sys.x$host_summary_by_statement_type; diff --git a/mysql-test/suite/sysschema/r/v_innodb_buffer_stats_by_schema.result b/mysql-test/suite/sysschema/r/v_innodb_buffer_stats_by_schema.result new file mode 100644 index 00000000..fbfa41f7 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_innodb_buffer_stats_by_schema.result @@ -0,0 +1,20 @@ +DESC sys.innodb_buffer_stats_by_schema; +Field Type Null Key Default Extra +object_schema text YES NULL +allocated text YES NULL +data text YES NULL +pages bigint(21) NO 0 +pages_hashed bigint(21) NO 0 +pages_old bigint(21) NO 0 +rows_cached decimal(44,0) YES NULL +SELECT * FROM sys.innodb_buffer_stats_by_schema; +DESC sys.x$innodb_buffer_stats_by_schema; +Field Type Null Key Default Extra +object_schema text YES NULL +allocated decimal(43,0) YES NULL +data decimal(43,0) YES NULL +pages bigint(21) NO 0 +pages_hashed bigint(21) NO 0 +pages_old bigint(21) NO 0 +rows_cached decimal(44,0) NO 0 +SELECT * FROM sys.x$innodb_buffer_stats_by_schema; diff --git a/mysql-test/suite/sysschema/r/v_innodb_buffer_stats_by_table.result b/mysql-test/suite/sysschema/r/v_innodb_buffer_stats_by_table.result new file mode 100644 index 00000000..27c7d991 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_innodb_buffer_stats_by_table.result @@ -0,0 +1,22 @@ +DESC sys.innodb_buffer_stats_by_table; +Field Type Null Key Default Extra +object_schema text YES NULL +object_name text YES NULL +allocated text YES NULL +data text YES NULL +pages bigint(21) NO 0 +pages_hashed bigint(21) NO 0 +pages_old bigint(21) NO 0 +rows_cached decimal(44,0) YES NULL +SELECT * FROM sys.innodb_buffer_stats_by_table; +DESC sys.x$innodb_buffer_stats_by_table; +Field Type Null Key Default Extra +object_schema text YES NULL +object_name text YES NULL +allocated decimal(43,0) YES NULL +data decimal(43,0) YES NULL +pages bigint(21) NO 0 +pages_hashed bigint(21) NO 0 +pages_old bigint(21) NO 0 +rows_cached decimal(44,0) NO 0 +SELECT * FROM sys.x$innodb_buffer_stats_by_table; diff --git a/mysql-test/suite/sysschema/r/v_innodb_lock_waits.result b/mysql-test/suite/sysschema/r/v_innodb_lock_waits.result new file mode 100644 index 00000000..d8e2c496 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_innodb_lock_waits.result @@ -0,0 +1,70 @@ +DESC sys.innodb_lock_waits; +Field Type Null Key Default Extra +wait_started datetime YES NULL +wait_age time /* mariadb-5.3 */ YES NULL +wait_age_secs bigint(21) YES NULL +locked_table varchar(1024) NO NULL +locked_index varchar(1024) YES NULL +locked_type enum('RECORD','TABLE') NO NULL +waiting_trx_id bigint(21) unsigned NO NULL +waiting_trx_started datetime NO NULL +waiting_trx_age time /* mariadb-5.3 */ YES NULL +waiting_trx_rows_locked bigint(21) unsigned NO NULL +waiting_trx_rows_modified bigint(21) unsigned NO NULL +waiting_pid bigint(21) unsigned NO NULL +waiting_query longtext YES NULL +waiting_lock_id varchar(81) NO NULL +waiting_lock_mode enum('S','S,GAP','X','X,GAP','IS','IS,GAP','IX','IX,GAP','AUTO_INC') NO NULL +blocking_trx_id bigint(21) unsigned NO NULL +blocking_pid bigint(21) unsigned NO NULL +blocking_query longtext YES NULL +blocking_lock_id varchar(81) NO NULL +blocking_lock_mode enum('S','S,GAP','X','X,GAP','IS','IS,GAP','IX','IX,GAP','AUTO_INC') NO NULL +blocking_trx_started datetime NO NULL +blocking_trx_age time /* mariadb-5.3 */ YES NULL +blocking_trx_rows_locked bigint(21) unsigned NO NULL +blocking_trx_rows_modified bigint(21) unsigned NO NULL +sql_kill_blocking_query varchar(32) YES NULL +sql_kill_blocking_connection varchar(26) YES NULL +SELECT * FROM sys.innodb_lock_waits; +DESC sys.x$innodb_lock_waits; +Field Type Null Key Default Extra +wait_started datetime YES NULL +wait_age time /* mariadb-5.3 */ YES NULL +wait_age_secs bigint(21) YES NULL +locked_table varchar(1024) NO NULL +locked_index varchar(1024) YES NULL +locked_type enum('RECORD','TABLE') NO NULL +waiting_trx_id bigint(21) unsigned NO NULL +waiting_trx_started datetime NO NULL +waiting_trx_age time /* mariadb-5.3 */ YES NULL +waiting_trx_rows_locked bigint(21) unsigned NO NULL +waiting_trx_rows_modified bigint(21) unsigned NO NULL +waiting_pid bigint(21) unsigned NO NULL +waiting_query varchar(1024) YES NULL +waiting_lock_id varchar(81) NO NULL +waiting_lock_mode enum('S','S,GAP','X','X,GAP','IS','IS,GAP','IX','IX,GAP','AUTO_INC') NO NULL +blocking_trx_id bigint(21) unsigned NO NULL +blocking_pid bigint(21) unsigned NO NULL +blocking_query varchar(1024) YES NULL +blocking_lock_id varchar(81) NO NULL +blocking_lock_mode enum('S','S,GAP','X','X,GAP','IS','IS,GAP','IX','IX,GAP','AUTO_INC') NO NULL +blocking_trx_started datetime NO NULL +blocking_trx_age time /* mariadb-5.3 */ YES NULL +blocking_trx_rows_locked bigint(21) unsigned NO NULL +blocking_trx_rows_modified bigint(21) unsigned NO NULL +sql_kill_blocking_query varchar(32) YES NULL +sql_kill_blocking_connection varchar(26) YES NULL +SELECT * FROM sys.x$innodb_lock_waits; +# +# Start of 10.6 tests +# +# +# MDEV-26507 Assertion `tmp != ((long long) 0x8000000000000000LL)' failed in TIME_from_longlong_datetime_packed +# +SET SESSION sql_mode='ALLOW_INVALID_DATES'; +SELECT * FROM sys.x$innodb_lock_waits; +SET SESSION sql_mode=DEFAULT; +# +# End of 10.6 tests +# diff --git a/mysql-test/suite/sysschema/r/v_io_by_thread_by_latency.result b/mysql-test/suite/sysschema/r/v_io_by_thread_by_latency.result new file mode 100644 index 00000000..fddd2f26 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_io_by_thread_by_latency.result @@ -0,0 +1,22 @@ +DESC sys.io_by_thread_by_latency; +Field Type Null Key Default Extra +user varchar(384) YES NULL +total decimal(42,0) YES NULL +total_latency text YES NULL +min_latency text YES NULL +avg_latency text YES NULL +max_latency text YES NULL +thread_id bigint(20) unsigned NO NULL +processlist_id bigint(20) unsigned YES NULL +SELECT * FROM sys.io_by_thread_by_latency; +DESC sys.x$io_by_thread_by_latency; +Field Type Null Key Default Extra +user varchar(384) YES NULL +total decimal(42,0) YES NULL +total_latency decimal(42,0) YES NULL +min_latency bigint(20) unsigned YES NULL +avg_latency decimal(24,4) YES NULL +max_latency bigint(20) unsigned YES NULL +thread_id bigint(20) unsigned NO NULL +processlist_id bigint(20) unsigned YES NULL +SELECT * FROM sys.x$io_by_thread_by_latency; diff --git a/mysql-test/suite/sysschema/r/v_io_global_by_file_by_bytes.result b/mysql-test/suite/sysschema/r/v_io_global_by_file_by_bytes.result new file mode 100644 index 00000000..06e88393 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_io_global_by_file_by_bytes.result @@ -0,0 +1,24 @@ +DESC sys.io_global_by_file_by_bytes; +Field Type Null Key Default Extra +file varchar(512) YES NULL +count_read bigint(20) unsigned NO NULL +total_read text YES NULL +avg_read text YES NULL +count_write bigint(20) unsigned NO NULL +total_written text YES NULL +avg_write text YES NULL +total text YES NULL +write_pct decimal(26,2) NO 0.00 +SELECT * FROM sys.io_global_by_file_by_bytes; +DESC sys.x$io_global_by_file_by_bytes; +Field Type Null Key Default Extra +file varchar(512) NO NULL +count_read bigint(20) unsigned NO NULL +total_read bigint(20) NO NULL +avg_read decimal(23,4) NO 0.0000 +count_write bigint(20) unsigned NO NULL +total_written bigint(20) NO NULL +avg_write decimal(23,4) NO 0.0000 +total bigint(21) NO 0 +write_pct decimal(26,2) NO 0.00 +SELECT * FROM sys.x$io_global_by_file_by_bytes; diff --git a/mysql-test/suite/sysschema/r/v_io_global_by_file_by_latency.result b/mysql-test/suite/sysschema/r/v_io_global_by_file_by_latency.result new file mode 100644 index 00000000..32d992c2 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_io_global_by_file_by_latency.result @@ -0,0 +1,24 @@ +DESC sys.io_global_by_file_by_latency; +Field Type Null Key Default Extra +file varchar(512) YES NULL +total bigint(20) unsigned NO NULL +total_latency text YES NULL +count_read bigint(20) unsigned NO NULL +read_latency text YES NULL +count_write bigint(20) unsigned NO NULL +write_latency text YES NULL +count_misc bigint(20) unsigned NO NULL +misc_latency text YES NULL +SELECT * FROM sys.io_global_by_file_by_latency; +DESC sys.x$io_global_by_file_by_latency; +Field Type Null Key Default Extra +file varchar(512) NO NULL +total bigint(20) unsigned NO NULL +total_latency bigint(20) unsigned NO NULL +count_read bigint(20) unsigned NO NULL +read_latency bigint(20) unsigned NO NULL +count_write bigint(20) unsigned NO NULL +write_latency bigint(20) unsigned NO NULL +count_misc bigint(20) unsigned NO NULL +misc_latency bigint(20) unsigned NO NULL +SELECT * FROM sys.x$io_global_by_file_by_latency; diff --git a/mysql-test/suite/sysschema/r/v_io_global_by_wait_by_bytes.result b/mysql-test/suite/sysschema/r/v_io_global_by_wait_by_bytes.result new file mode 100644 index 00000000..3d5dcbff --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_io_global_by_wait_by_bytes.result @@ -0,0 +1,32 @@ +DESC sys.io_global_by_wait_by_bytes; +Field Type Null Key Default Extra +event_name varchar(128) YES NULL +total bigint(20) unsigned NO NULL +total_latency text YES NULL +min_latency text YES NULL +avg_latency text YES NULL +max_latency text YES NULL +count_read bigint(20) unsigned NO NULL +total_read text YES NULL +avg_read text YES NULL +count_write bigint(20) unsigned NO NULL +total_written text YES NULL +avg_written text YES NULL +total_requested text YES NULL +SELECT * FROM sys.io_global_by_wait_by_bytes; +DESC sys.x$io_global_by_wait_by_bytes; +Field Type Null Key Default Extra +event_name varchar(128) YES NULL +total bigint(20) unsigned NO NULL +total_latency bigint(20) unsigned NO NULL +min_latency bigint(20) unsigned NO NULL +avg_latency bigint(20) unsigned NO NULL +max_latency bigint(20) unsigned NO NULL +count_read bigint(20) unsigned NO NULL +total_read bigint(20) NO NULL +avg_read decimal(23,4) NO 0.0000 +count_write bigint(20) unsigned NO NULL +total_written bigint(20) NO NULL +avg_written decimal(23,4) NO 0.0000 +total_requested bigint(21) NO 0 +SELECT * FROM sys.x$io_global_by_wait_by_bytes; diff --git a/mysql-test/suite/sysschema/r/v_io_global_by_wait_by_latency.result b/mysql-test/suite/sysschema/r/v_io_global_by_wait_by_latency.result new file mode 100644 index 00000000..1c5db297 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_io_global_by_wait_by_latency.result @@ -0,0 +1,34 @@ +DESC sys.io_global_by_wait_by_latency; +Field Type Null Key Default Extra +event_name varchar(128) YES NULL +total bigint(20) unsigned NO NULL +total_latency text YES NULL +avg_latency text YES NULL +max_latency text YES NULL +read_latency text YES NULL +write_latency text YES NULL +misc_latency text YES NULL +count_read bigint(20) unsigned NO NULL +total_read text YES NULL +avg_read text YES NULL +count_write bigint(20) unsigned NO NULL +total_written text YES NULL +avg_written text YES NULL +SELECT * FROM sys.io_global_by_wait_by_latency; +DESC sys.x$io_global_by_wait_by_latency; +Field Type Null Key Default Extra +event_name varchar(128) YES NULL +total bigint(20) unsigned NO NULL +total_latency bigint(20) unsigned NO NULL +avg_latency bigint(20) unsigned NO NULL +max_latency bigint(20) unsigned NO NULL +read_latency bigint(20) unsigned NO NULL +write_latency bigint(20) unsigned NO NULL +misc_latency bigint(20) unsigned NO NULL +count_read bigint(20) unsigned NO NULL +total_read bigint(20) NO NULL +avg_read decimal(23,4) NO 0.0000 +count_write bigint(20) unsigned NO NULL +total_written bigint(20) NO NULL +avg_written decimal(23,4) NO 0.0000 +SELECT * FROM sys.x$io_global_by_wait_by_latency; diff --git a/mysql-test/suite/sysschema/r/v_latest_file_io.result b/mysql-test/suite/sysschema/r/v_latest_file_io.result new file mode 100644 index 00000000..a65cdb2d --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_latest_file_io.result @@ -0,0 +1,16 @@ +DESC sys.latest_file_io; +Field Type Null Key Default Extra +thread varchar(214) YES NULL +file varchar(512) YES NULL +latency text YES NULL +operation varchar(32) NO NULL +requested text YES NULL +SELECT * FROM sys.latest_file_io; +DESC sys.x$latest_file_io; +Field Type Null Key Default Extra +thread varchar(214) YES NULL +file varchar(512) YES NULL +latency bigint(20) unsigned YES NULL +operation varchar(32) NO NULL +requested bigint(20) YES NULL +SELECT * FROM sys.x$latest_file_io; diff --git a/mysql-test/suite/sysschema/r/v_memory_by_host_by_current_bytes.result b/mysql-test/suite/sysschema/r/v_memory_by_host_by_current_bytes.result new file mode 100644 index 00000000..9b0e0f91 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_memory_by_host_by_current_bytes.result @@ -0,0 +1,18 @@ +DESC sys.memory_by_host_by_current_bytes; +Field Type Null Key Default Extra +host varchar(60) YES NULL +current_count_used decimal(41,0) YES NULL +current_allocated text YES NULL +current_avg_alloc text YES NULL +current_max_alloc text YES NULL +total_allocated text YES NULL +SELECT * FROM sys.memory_by_host_by_current_bytes; +DESC sys.x$memory_by_host_by_current_bytes; +Field Type Null Key Default Extra +host varchar(60) YES NULL +current_count_used decimal(41,0) YES NULL +current_allocated decimal(41,0) YES NULL +current_avg_alloc decimal(45,4) NO 0.0000 +current_max_alloc bigint(20) YES NULL +total_allocated decimal(42,0) YES NULL +SELECT * FROM sys.x$memory_by_host_by_current_bytes; diff --git a/mysql-test/suite/sysschema/r/v_memory_by_thread_by_current_bytes.result b/mysql-test/suite/sysschema/r/v_memory_by_thread_by_current_bytes.result new file mode 100644 index 00000000..80bb84c3 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_memory_by_thread_by_current_bytes.result @@ -0,0 +1,20 @@ +DESC sys.memory_by_thread_by_current_bytes; +Field Type Null Key Default Extra +thread_id bigint(20) unsigned NO NULL +user varchar(384) YES NULL +current_count_used decimal(41,0) YES NULL +current_allocated text YES NULL +current_avg_alloc text YES NULL +current_max_alloc text YES NULL +total_allocated text YES NULL +SELECT * FROM sys.memory_by_thread_by_current_bytes; +DESC sys.x$memory_by_thread_by_current_bytes; +Field Type Null Key Default Extra +thread_id bigint(20) unsigned NO NULL +user varchar(384) YES NULL +current_count_used decimal(41,0) YES NULL +current_allocated decimal(41,0) YES NULL +current_avg_alloc decimal(45,4) NO 0.0000 +current_max_alloc bigint(20) YES NULL +total_allocated decimal(42,0) YES NULL +SELECT * FROM sys.x$memory_by_thread_by_current_bytes; diff --git a/mysql-test/suite/sysschema/r/v_memory_by_user_by_current_bytes.result b/mysql-test/suite/sysschema/r/v_memory_by_user_by_current_bytes.result new file mode 100644 index 00000000..95d56d9d --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_memory_by_user_by_current_bytes.result @@ -0,0 +1,18 @@ +DESC sys.memory_by_user_by_current_bytes; +Field Type Null Key Default Extra +user varchar(32) YES NULL +current_count_used decimal(41,0) YES NULL +current_allocated text YES NULL +current_avg_alloc text YES NULL +current_max_alloc text YES NULL +total_allocated text YES NULL +SELECT * FROM sys.memory_by_user_by_current_bytes; +DESC sys.x$memory_by_user_by_current_bytes; +Field Type Null Key Default Extra +user varchar(32) YES NULL +current_count_used decimal(41,0) YES NULL +current_allocated decimal(41,0) YES NULL +current_avg_alloc decimal(45,4) NO 0.0000 +current_max_alloc bigint(20) YES NULL +total_allocated decimal(42,0) YES NULL +SELECT * FROM sys.x$memory_by_user_by_current_bytes; diff --git a/mysql-test/suite/sysschema/r/v_memory_global_by_current_bytes.result b/mysql-test/suite/sysschema/r/v_memory_global_by_current_bytes.result new file mode 100644 index 00000000..aef384dd --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_memory_global_by_current_bytes.result @@ -0,0 +1,20 @@ +DESC sys.memory_global_by_current_bytes; +Field Type Null Key Default Extra +event_name varchar(128) NO NULL +current_count bigint(20) NO NULL +current_alloc text YES NULL +current_avg_alloc text YES NULL +high_count bigint(20) NO NULL +high_alloc text YES NULL +high_avg_alloc text YES NULL +SELECT * FROM sys.memory_global_by_current_bytes; +DESC sys.x$memory_global_by_current_bytes; +Field Type Null Key Default Extra +event_name varchar(128) NO NULL +current_count bigint(20) NO NULL +current_alloc bigint(20) NO NULL +current_avg_alloc decimal(23,4) NO 0.0000 +high_count bigint(20) NO NULL +high_alloc bigint(20) NO NULL +high_avg_alloc decimal(23,4) NO 0.0000 +SELECT * FROM sys.x$memory_global_by_current_bytes; diff --git a/mysql-test/suite/sysschema/r/v_memory_global_total.result b/mysql-test/suite/sysschema/r/v_memory_global_total.result new file mode 100644 index 00000000..e114cefa --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_memory_global_total.result @@ -0,0 +1,8 @@ +DESC sys.memory_global_total; +Field Type Null Key Default Extra +total_allocated text YES NULL +SELECT * FROM sys.memory_global_total; +DESC sys.x$memory_global_total; +Field Type Null Key Default Extra +total_allocated decimal(41,0) YES NULL +SELECT * FROM sys.x$memory_global_total; diff --git a/mysql-test/suite/sysschema/r/v_metrics.result b/mysql-test/suite/sysschema/r/v_metrics.result new file mode 100644 index 00000000..bfc840ed --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_metrics.result @@ -0,0 +1,7 @@ +DESC sys.metrics; +Field Type Null Key Default Extra +Variable_name varchar(193) YES NULL +Variable_value varchar(1024) YES NULL +Type varchar(210) YES NULL +Enabled varchar(3) NO +SELECT * FROM sys.metrics; diff --git a/mysql-test/suite/sysschema/r/v_processlist.result b/mysql-test/suite/sysschema/r/v_processlist.result new file mode 100644 index 00000000..dfc04814 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_processlist.result @@ -0,0 +1,62 @@ +DESC sys.processlist; +Field Type Null Key Default Extra +thd_id bigint(20) unsigned NO NULL +conn_id bigint(20) unsigned YES NULL +user varchar(384) YES NULL +db varchar(64) YES NULL +command varchar(16) YES NULL +state varchar(64) YES NULL +time bigint(20) YES NULL +current_statement longtext YES NULL +statement_latency mediumtext YES NULL +progress decimal(26,2) YES NULL +lock_latency text YES NULL +rows_examined bigint(20) unsigned YES NULL +rows_sent bigint(20) unsigned YES NULL +rows_affected bigint(20) unsigned YES NULL +tmp_tables bigint(20) unsigned YES NULL +tmp_disk_tables bigint(20) unsigned YES NULL +full_scan varchar(3) YES NULL +last_statement longtext YES NULL +last_statement_latency mediumtext YES NULL +current_memory text YES NULL +last_wait varchar(128) YES NULL +last_wait_latency mediumtext YES NULL +source varchar(64) YES NULL +trx_latency text YES NULL +trx_state enum('ACTIVE','COMMITTED','ROLLED BACK') YES NULL +trx_autocommit enum('YES','NO') YES NULL +pid varchar(1024) YES NULL +program_name varchar(1024) YES NULL +SELECT * FROM sys.processlist; +DESC sys.x$processlist; +Field Type Null Key Default Extra +thd_id bigint(20) unsigned NO NULL +conn_id bigint(20) unsigned YES NULL +user varchar(384) YES NULL +db varchar(64) YES NULL +command varchar(16) YES NULL +state varchar(64) YES NULL +time bigint(20) YES NULL +current_statement longtext YES NULL +statement_latency bigint(20) unsigned YES NULL +progress decimal(26,2) YES NULL +lock_latency bigint(20) unsigned YES NULL +rows_examined bigint(20) unsigned YES NULL +rows_sent bigint(20) unsigned YES NULL +rows_affected bigint(20) unsigned YES NULL +tmp_tables bigint(20) unsigned YES NULL +tmp_disk_tables bigint(20) unsigned YES NULL +full_scan varchar(3) YES NULL +last_statement longtext YES NULL +last_statement_latency bigint(20) unsigned YES NULL +current_memory decimal(41,0) YES NULL +last_wait varchar(128) YES NULL +last_wait_latency varchar(20) YES NULL +source varchar(64) YES NULL +trx_latency bigint(20) unsigned YES NULL +trx_state enum('ACTIVE','COMMITTED','ROLLED BACK') YES NULL +trx_autocommit enum('YES','NO') YES NULL +pid varchar(1024) YES NULL +program_name varchar(1024) YES NULL +SELECT * FROM sys.x$processlist; diff --git a/mysql-test/suite/sysschema/r/v_ps_check_lost_instrumentation.result b/mysql-test/suite/sysschema/r/v_ps_check_lost_instrumentation.result new file mode 100644 index 00000000..dfbd2046 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_ps_check_lost_instrumentation.result @@ -0,0 +1,5 @@ +DESC sys.ps_check_lost_instrumentation; +Field Type Null Key Default Extra +variable_name varchar(64) NO NULL +variable_value varchar(1024) YES NULL +SELECT * FROM sys.ps_check_lost_instrumentation; diff --git a/mysql-test/suite/sysschema/r/v_ps_digest_95th_percentile_by_avg_us.result b/mysql-test/suite/sysschema/r/v_ps_digest_95th_percentile_by_avg_us.result new file mode 100644 index 00000000..6378c9cf --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_ps_digest_95th_percentile_by_avg_us.result @@ -0,0 +1,18 @@ +DESC sys.x$ps_digest_95th_percentile_by_avg_us; +Field Type Null Key Default Extra +avg_us decimal(21,0) YES NULL +percentile decimal(46,4) NO 0.0000 +DESC sys.x$ps_digest_95th_percentile_by_avg_us; +Field Type Null Key Default Extra +avg_us decimal(21,0) YES NULL +percentile decimal(46,4) NO 0.0000 +DESC sys.x$ps_digest_95th_percentile_by_avg_us; +Field Type Null Key Default Extra +avg_us decimal(21,0) YES NULL +percentile decimal(46,4) NO 0.0000 +SELECT * FROM sys.x$ps_digest_95th_percentile_by_avg_us; +DESC sys.x$ps_digest_95th_percentile_by_avg_us; +Field Type Null Key Default Extra +avg_us decimal(21,0) YES NULL +percentile decimal(46,4) NO 0.0000 +SELECT * FROM sys.x$ps_digest_95th_percentile_by_avg_us; diff --git a/mysql-test/suite/sysschema/r/v_ps_digest_avg_latency_distribution.result b/mysql-test/suite/sysschema/r/v_ps_digest_avg_latency_distribution.result new file mode 100644 index 00000000..a3eb1e28 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_ps_digest_avg_latency_distribution.result @@ -0,0 +1,18 @@ +DESC sys.x$ps_digest_avg_latency_distribution; +Field Type Null Key Default Extra +cnt bigint(21) NO 0 +avg_us decimal(21,0) YES NULL +DESC sys.x$ps_digest_avg_latency_distribution; +Field Type Null Key Default Extra +cnt bigint(21) NO 0 +avg_us decimal(21,0) YES NULL +DESC sys.x$ps_digest_avg_latency_distribution; +Field Type Null Key Default Extra +cnt bigint(21) NO 0 +avg_us decimal(21,0) YES NULL +SELECT * FROM sys.x$ps_digest_avg_latency_distribution; +DESC sys.x$ps_digest_avg_latency_distribution; +Field Type Null Key Default Extra +cnt bigint(21) NO 0 +avg_us decimal(21,0) YES NULL +SELECT * FROM sys.x$ps_digest_avg_latency_distribution; diff --git a/mysql-test/suite/sysschema/r/v_ps_schema_table_statistics_io.result b/mysql-test/suite/sysschema/r/v_ps_schema_table_statistics_io.result new file mode 100644 index 00000000..0b1ae3bf --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_ps_schema_table_statistics_io.result @@ -0,0 +1,50 @@ +DESC sys.x$ps_schema_table_statistics_io; +Field Type Null Key Default Extra +table_schema varchar(64) YES NULL +table_name varchar(64) YES NULL +count_read decimal(42,0) YES NULL +sum_number_of_bytes_read decimal(41,0) YES NULL +sum_timer_read decimal(42,0) YES NULL +count_write decimal(42,0) YES NULL +sum_number_of_bytes_write decimal(41,0) YES NULL +sum_timer_write decimal(42,0) YES NULL +count_misc decimal(42,0) YES NULL +sum_timer_misc decimal(42,0) YES NULL +DESC sys.x$ps_schema_table_statistics_io; +Field Type Null Key Default Extra +table_schema varchar(64) YES NULL +table_name varchar(64) YES NULL +count_read decimal(42,0) YES NULL +sum_number_of_bytes_read decimal(41,0) YES NULL +sum_timer_read decimal(42,0) YES NULL +count_write decimal(42,0) YES NULL +sum_number_of_bytes_write decimal(41,0) YES NULL +sum_timer_write decimal(42,0) YES NULL +count_misc decimal(42,0) YES NULL +sum_timer_misc decimal(42,0) YES NULL +DESC sys.x$ps_schema_table_statistics_io; +Field Type Null Key Default Extra +table_schema varchar(64) YES NULL +table_name varchar(64) YES NULL +count_read decimal(42,0) YES NULL +sum_number_of_bytes_read decimal(41,0) YES NULL +sum_timer_read decimal(42,0) YES NULL +count_write decimal(42,0) YES NULL +sum_number_of_bytes_write decimal(41,0) YES NULL +sum_timer_write decimal(42,0) YES NULL +count_misc decimal(42,0) YES NULL +sum_timer_misc decimal(42,0) YES NULL +SELECT * FROM sys.x$ps_schema_table_statistics_io; +DESC sys.x$ps_schema_table_statistics_io; +Field Type Null Key Default Extra +table_schema varchar(64) YES NULL +table_name varchar(64) YES NULL +count_read decimal(42,0) YES NULL +sum_number_of_bytes_read decimal(41,0) YES NULL +sum_timer_read decimal(42,0) YES NULL +count_write decimal(42,0) YES NULL +sum_number_of_bytes_write decimal(41,0) YES NULL +sum_timer_write decimal(42,0) YES NULL +count_misc decimal(42,0) YES NULL +sum_timer_misc decimal(42,0) YES NULL +SELECT * FROM sys.x$ps_schema_table_statistics_io; diff --git a/mysql-test/suite/sysschema/r/v_schema_auto_increment_columns.result b/mysql-test/suite/sysschema/r/v_schema_auto_increment_columns.result new file mode 100644 index 00000000..ef2ad76b --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_schema_auto_increment_columns.result @@ -0,0 +1,143 @@ +DESC sys.schema_auto_increment_columns; +Field Type Null Key Default Extra +table_schema varchar(64) NO NULL +table_name varchar(64) NO NULL +column_name varchar(64) NO NULL +data_type varchar(64) NO NULL +column_type longtext NO NULL +is_signed int(1) NO 0 +is_unsigned int(1) NO 0 +max_value bigint(21) unsigned YES NULL +auto_increment bigint(21) unsigned YES NULL +auto_increment_ratio decimal(25,4) unsigned YES NULL +SELECT * FROM sys.schema_auto_increment_columns; +CREATE DATABASE auto_incs; +CREATE TABLE auto_incs.tinyintcol ( +id TINYINT AUTO_INCREMENT PRIMARY KEY, +foo INT +); +INSERT INTO auto_incs.tinyintcol (foo) VALUES (100); +INSERT INTO auto_incs.tinyintcol (foo) (SELECT foo FROM auto_incs.tinyintcol); +INSERT INTO auto_incs.tinyintcol (foo) (SELECT foo FROM auto_incs.tinyintcol); +INSERT INTO auto_incs.tinyintcol (foo) (SELECT foo FROM auto_incs.tinyintcol); +INSERT INTO auto_incs.tinyintcol (foo) (SELECT foo FROM auto_incs.tinyintcol); +CREATE TABLE auto_incs.tinyintcolunsigned ( +id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, +foo INT +); +INSERT INTO auto_incs.tinyintcolunsigned (foo) (SELECT foo FROM auto_incs.tinyintcol); +CREATE TABLE auto_incs.smallintcol ( +id SMALLINT AUTO_INCREMENT PRIMARY KEY, +foo INT +); +INSERT INTO auto_incs.smallintcol (foo) VALUES (200); +INSERT INTO auto_incs.smallintcol (foo) (SELECT foo FROM auto_incs.smallintcol); +INSERT INTO auto_incs.smallintcol (foo) (SELECT foo FROM auto_incs.smallintcol); +INSERT INTO auto_incs.smallintcol (foo) (SELECT foo FROM auto_incs.smallintcol); +INSERT INTO auto_incs.smallintcol (foo) (SELECT foo FROM auto_incs.smallintcol); +INSERT INTO auto_incs.smallintcol (foo) (SELECT foo FROM auto_incs.smallintcol); +INSERT INTO auto_incs.smallintcol (foo) (SELECT foo FROM auto_incs.smallintcol); +INSERT INTO auto_incs.smallintcol (foo) (SELECT foo FROM auto_incs.smallintcol); +INSERT INTO auto_incs.smallintcol (foo) (SELECT foo FROM auto_incs.smallintcol); +INSERT INTO auto_incs.smallintcol (foo) (SELECT foo FROM auto_incs.smallintcol); +INSERT INTO auto_incs.smallintcol (foo) (SELECT foo FROM auto_incs.smallintcol); +INSERT INTO auto_incs.smallintcol (foo) (SELECT foo FROM auto_incs.smallintcol); +CREATE TABLE auto_incs.smallintcolunsigned ( +id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, +foo INT +); +INSERT INTO auto_incs.smallintcolunsigned (foo) (SELECT foo FROM auto_incs.smallintcol); +CREATE TABLE auto_incs.mediumintcol ( +id MEDIUMINT AUTO_INCREMENT PRIMARY KEY, +foo INT +); +INSERT INTO auto_incs.mediumintcol (foo) VALUES (300); +INSERT INTO auto_incs.mediumintcol (foo) (SELECT foo FROM auto_incs.mediumintcol); +INSERT INTO auto_incs.mediumintcol (foo) (SELECT foo FROM auto_incs.mediumintcol); +INSERT INTO auto_incs.mediumintcol (foo) (SELECT foo FROM auto_incs.mediumintcol); +INSERT INTO auto_incs.mediumintcol (foo) (SELECT foo FROM auto_incs.mediumintcol); +INSERT INTO auto_incs.mediumintcol (foo) (SELECT foo FROM auto_incs.mediumintcol); +INSERT INTO auto_incs.mediumintcol (foo) (SELECT foo FROM auto_incs.mediumintcol); +INSERT INTO auto_incs.mediumintcol (foo) (SELECT foo FROM auto_incs.mediumintcol); +INSERT INTO auto_incs.mediumintcol (foo) (SELECT foo FROM auto_incs.mediumintcol); +INSERT INTO auto_incs.mediumintcol (foo) (SELECT foo FROM auto_incs.mediumintcol); +INSERT INTO auto_incs.mediumintcol (foo) (SELECT foo FROM auto_incs.mediumintcol); +INSERT INTO auto_incs.mediumintcol (foo) (SELECT foo FROM auto_incs.mediumintcol); +INSERT INTO auto_incs.mediumintcol (foo) (SELECT foo FROM auto_incs.mediumintcol); +INSERT INTO auto_incs.mediumintcol (foo) (SELECT foo FROM auto_incs.mediumintcol); +INSERT INTO auto_incs.mediumintcol (foo) (SELECT foo FROM auto_incs.mediumintcol); +INSERT INTO auto_incs.mediumintcol (foo) (SELECT foo FROM auto_incs.mediumintcol); +INSERT INTO auto_incs.mediumintcol (foo) (SELECT foo FROM auto_incs.mediumintcol); +INSERT INTO auto_incs.mediumintcol (foo) (SELECT foo FROM auto_incs.mediumintcol); +CREATE TABLE auto_incs.mediumintcolunsigned ( +id MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, +foo INT +); +INSERT INTO auto_incs.mediumintcolunsigned (foo) (SELECT foo FROM auto_incs.mediumintcol); +CREATE TABLE auto_incs.intcol ( +id INT AUTO_INCREMENT PRIMARY KEY, +foo INT +); +INSERT INTO auto_incs.intcol (foo) VALUES (400); +INSERT INTO auto_incs.intcol (foo) (SELECT foo FROM auto_incs.intcol); +INSERT INTO auto_incs.intcol (foo) (SELECT foo FROM auto_incs.intcol); +INSERT INTO auto_incs.intcol (foo) (SELECT foo FROM auto_incs.intcol); +INSERT INTO auto_incs.intcol (foo) (SELECT foo FROM auto_incs.intcol); +INSERT INTO auto_incs.intcol (foo) (SELECT foo FROM auto_incs.intcol); +INSERT INTO auto_incs.intcol (foo) (SELECT foo FROM auto_incs.intcol); +INSERT INTO auto_incs.intcol (foo) (SELECT foo FROM auto_incs.intcol); +INSERT INTO auto_incs.intcol (foo) (SELECT foo FROM auto_incs.intcol); +INSERT INTO auto_incs.intcol (foo) (SELECT foo FROM auto_incs.intcol); +INSERT INTO auto_incs.intcol (foo) (SELECT foo FROM auto_incs.intcol); +INSERT INTO auto_incs.intcol (foo) (SELECT foo FROM auto_incs.intcol); +INSERT INTO auto_incs.intcol (foo) (SELECT foo FROM auto_incs.intcol); +INSERT INTO auto_incs.intcol (foo) (SELECT foo FROM auto_incs.intcol); +INSERT INTO auto_incs.intcol (foo) (SELECT foo FROM auto_incs.intcol); +INSERT INTO auto_incs.intcol (foo) (SELECT foo FROM auto_incs.intcol); +INSERT INTO auto_incs.intcol (foo) (SELECT foo FROM auto_incs.intcol); +INSERT INTO auto_incs.intcol (foo) (SELECT foo FROM auto_incs.intcol); +CREATE TABLE auto_incs.intcolunsigned ( +id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, +foo INT +); +INSERT INTO auto_incs.intcolunsigned (foo) (SELECT foo FROM auto_incs.intcol); +CREATE TABLE auto_incs.bigintcol ( +id BIGINT AUTO_INCREMENT PRIMARY KEY, +foo INT +); +INSERT INTO auto_incs.bigintcol (foo) VALUES (500); +INSERT INTO auto_incs.bigintcol (foo) (SELECT foo FROM auto_incs.bigintcol); +INSERT INTO auto_incs.bigintcol (foo) (SELECT foo FROM auto_incs.bigintcol); +INSERT INTO auto_incs.bigintcol (foo) (SELECT foo FROM auto_incs.bigintcol); +INSERT INTO auto_incs.bigintcol (foo) (SELECT foo FROM auto_incs.bigintcol); +INSERT INTO auto_incs.bigintcol (foo) (SELECT foo FROM auto_incs.bigintcol); +INSERT INTO auto_incs.bigintcol (foo) (SELECT foo FROM auto_incs.bigintcol); +INSERT INTO auto_incs.bigintcol (foo) (SELECT foo FROM auto_incs.bigintcol); +INSERT INTO auto_incs.bigintcol (foo) (SELECT foo FROM auto_incs.bigintcol); +INSERT INTO auto_incs.bigintcol (foo) (SELECT foo FROM auto_incs.bigintcol); +INSERT INTO auto_incs.bigintcol (foo) (SELECT foo FROM auto_incs.bigintcol); +INSERT INTO auto_incs.bigintcol (foo) (SELECT foo FROM auto_incs.bigintcol); +INSERT INTO auto_incs.bigintcol (foo) (SELECT foo FROM auto_incs.bigintcol); +INSERT INTO auto_incs.bigintcol (foo) (SELECT foo FROM auto_incs.bigintcol); +INSERT INTO auto_incs.bigintcol (foo) (SELECT foo FROM auto_incs.bigintcol); +INSERT INTO auto_incs.bigintcol (foo) (SELECT foo FROM auto_incs.bigintcol); +INSERT INTO auto_incs.bigintcol (foo) (SELECT foo FROM auto_incs.bigintcol); +INSERT INTO auto_incs.bigintcol (foo) (SELECT foo FROM auto_incs.bigintcol); +CREATE TABLE auto_incs.bigintcolunsigned ( +id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, +foo INT +); +INSERT INTO auto_incs.bigintcolunsigned (foo) (SELECT foo FROM auto_incs.bigintcol); +SELECT * FROM sys.schema_auto_increment_columns; +table_schema table_name column_name data_type column_type is_signed is_unsigned max_value auto_increment auto_increment_ratio +auto_incs tinyintcol id tinyint tinyint(4) 1 0 127 17 0.1339 +auto_incs tinyintcolunsigned id tinyint tinyint(3) unsigned 0 1 255 17 0.0667 +auto_incs smallintcol id smallint smallint(6) 1 0 32767 2049 0.0625 +auto_incs smallintcolunsigned id smallint smallint(5) unsigned 0 1 65535 2049 0.0313 +auto_incs mediumintcol id mediumint mediumint(9) 1 0 8388607 131073 0.0156 +auto_incs mediumintcolunsigned id mediumint mediumint(8) unsigned 0 1 16777215 131073 0.0078 +auto_incs intcol id int int(11) 1 0 2147483647 131073 0.0001 +auto_incs intcolunsigned id int int(10) unsigned 0 1 4294967295 131073 0.0000 +auto_incs bigintcol id bigint bigint(20) 1 0 9223372036854775807 131073 0.0000 +auto_incs bigintcolunsigned id bigint bigint(20) unsigned 0 1 18446744073709551615 131073 0.0000 +DROP DATABASE auto_incs; diff --git a/mysql-test/suite/sysschema/r/v_schema_index_statistics.result b/mysql-test/suite/sysschema/r/v_schema_index_statistics.result new file mode 100644 index 00000000..63cf1931 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_schema_index_statistics.result @@ -0,0 +1,28 @@ +DESC sys.schema_index_statistics; +Field Type Null Key Default Extra +table_schema varchar(64) YES NULL +table_name varchar(64) YES NULL +index_name varchar(64) YES NULL +rows_selected bigint(20) unsigned NO NULL +select_latency text YES NULL +rows_inserted bigint(20) unsigned NO NULL +insert_latency text YES NULL +rows_updated bigint(20) unsigned NO NULL +update_latency text YES NULL +rows_deleted bigint(20) unsigned NO NULL +delete_latency text YES NULL +SELECT * FROM sys.schema_index_statistics; +DESC sys.x$schema_index_statistics; +Field Type Null Key Default Extra +table_schema varchar(64) YES NULL +table_name varchar(64) YES NULL +index_name varchar(64) YES NULL +rows_selected bigint(20) unsigned NO NULL +select_latency bigint(20) unsigned NO NULL +rows_inserted bigint(20) unsigned NO NULL +insert_latency bigint(20) unsigned NO NULL +rows_updated bigint(20) unsigned NO NULL +update_latency bigint(20) unsigned NO NULL +rows_deleted bigint(20) unsigned NO NULL +delete_latency bigint(20) unsigned NO NULL +SELECT * FROM sys.x$schema_index_statistics; diff --git a/mysql-test/suite/sysschema/r/v_schema_object_overview.result b/mysql-test/suite/sysschema/r/v_schema_object_overview.result new file mode 100644 index 00000000..1c6144e9 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_schema_object_overview.result @@ -0,0 +1,6 @@ +DESC sys.schema_object_overview; +Field Type Null Key Default Extra +db varchar(64) NO +object_type varchar(64) YES NULL +count bigint(21) NO 0 +SELECT * FROM sys.schema_object_overview; diff --git a/mysql-test/suite/sysschema/r/v_schema_redundant_indexes.result b/mysql-test/suite/sysschema/r/v_schema_redundant_indexes.result new file mode 100644 index 00000000..2199c959 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_schema_redundant_indexes.result @@ -0,0 +1,37 @@ +DESC sys.schema_redundant_indexes; +Field Type Null Key Default Extra +table_schema varchar(64) NO NULL +table_name varchar(64) NO NULL +redundant_index_name varchar(64) NO NULL +redundant_index_columns mediumtext YES NULL +redundant_index_non_unique bigint(1) YES NULL +dominant_index_name varchar(64) NO NULL +dominant_index_columns mediumtext YES NULL +dominant_index_non_unique bigint(1) YES NULL +subpart_exists int(1) YES NULL +sql_drop_index varchar(223) YES NULL +SELECT * FROM sys.schema_redundant_indexes; +DESC sys.x$schema_flattened_keys; +Field Type Null Key Default Extra +table_schema varchar(64) NO NULL +table_name varchar(64) NO NULL +index_name varchar(64) NO NULL +non_unique bigint(1) YES NULL +subpart_exists bigint(1) YES NULL +index_columns mediumtext YES NULL +SELECT * FROM sys.x$schema_flattened_keys; +CREATE DATABASE rkey; +CREATE TABLE rkey.rkey ( +i INT, +j INT, +k INT, +PRIMARY KEY (i), +KEY (j), +KEY (j, k), +KEY (i, j, k) +); +SELECT * FROM sys.schema_redundant_indexes; +table_schema table_name redundant_index_name redundant_index_columns redundant_index_non_unique dominant_index_name dominant_index_columns dominant_index_non_unique subpart_exists sql_drop_index +rkey rkey j j 1 j_2 j,k 1 0 ALTER TABLE `rkey`.`rkey` DROP INDEX `j` +rkey rkey i i,j,k 1 PRIMARY i 0 0 ALTER TABLE `rkey`.`rkey` DROP INDEX `i` +DROP DATABASE rkey; diff --git a/mysql-test/suite/sysschema/r/v_schema_table_lock_waits.result b/mysql-test/suite/sysschema/r/v_schema_table_lock_waits.result new file mode 100644 index 00000000..e51b3f27 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_schema_table_lock_waits.result @@ -0,0 +1,61 @@ +DESC sys.schema_table_lock_waits; +Field Type Null Key Default Extra +object_schema varchar(64) YES NULL +object_name varchar(64) YES NULL +waiting_thread_id bigint(20) unsigned NO NULL +waiting_pid bigint(20) unsigned YES NULL +waiting_account text YES NULL +waiting_lock_type varchar(32) NO NULL +waiting_lock_duration varchar(32) NO NULL +waiting_query longtext YES NULL +waiting_query_secs bigint(20) YES NULL +waiting_query_rows_affected bigint(20) unsigned YES NULL +waiting_query_rows_examined bigint(20) unsigned YES NULL +blocking_thread_id bigint(20) unsigned NO NULL +blocking_pid bigint(20) unsigned YES NULL +blocking_account text YES NULL +blocking_lock_type varchar(32) NO NULL +blocking_lock_duration varchar(32) NO NULL +sql_kill_blocking_query varchar(31) YES NULL +sql_kill_blocking_connection varchar(25) YES NULL +SELECT * FROM sys.schema_table_lock_waits; +DESC sys.x$schema_table_lock_waits; +Field Type Null Key Default Extra +object_schema varchar(64) YES NULL +object_name varchar(64) YES NULL +waiting_thread_id bigint(20) unsigned NO NULL +waiting_pid bigint(20) unsigned YES NULL +waiting_account text YES NULL +waiting_lock_type varchar(32) NO NULL +waiting_lock_duration varchar(32) NO NULL +waiting_query longtext YES NULL +waiting_query_secs bigint(20) YES NULL +waiting_query_rows_affected bigint(20) unsigned YES NULL +waiting_query_rows_examined bigint(20) unsigned YES NULL +blocking_thread_id bigint(20) unsigned NO NULL +blocking_pid bigint(20) unsigned YES NULL +blocking_account text YES NULL +blocking_lock_type varchar(32) NO NULL +blocking_lock_duration varchar(32) NO NULL +sql_kill_blocking_query varchar(31) YES NULL +sql_kill_blocking_connection varchar(25) YES NULL +SELECT * FROM sys.x$schema_table_lock_waits; +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection con1; +CREATE TABLE test.lock_waits (i INT PRIMARY KEY, j INT) ENGINE = InnoDB; +LOCK TABLE lock_waits WRITE; +connection con2; +ALTER TABLE test.lock_waits ADD k VARCHAR(20); +connection default; +SELECT object_schema, object_name, +waiting_account, waiting_lock_type, waiting_query, +blocking_account, blocking_lock_type, blocking_lock_duration +FROM sys.schema_table_lock_waits; +object_schema object_name waiting_account waiting_lock_type waiting_query blocking_account blocking_lock_type blocking_lock_duration +test lock_waits root@localhost SHARED_UPGRADABLE ALTER TABLE test.lock_waits ADD k VARCHAR(20) root@localhost SHARED_NO_READ_WRITE TRANSACTION +disconnect con1; +connection default; +disconnect con2; +connection default; +DROP TABLE test.lock_waits; diff --git a/mysql-test/suite/sysschema/r/v_schema_table_statistics.result b/mysql-test/suite/sysschema/r/v_schema_table_statistics.result new file mode 100644 index 00000000..fda0d7da --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_schema_table_statistics.result @@ -0,0 +1,44 @@ +DESC sys.schema_table_statistics; +Field Type Null Key Default Extra +table_schema varchar(64) YES NULL +table_name varchar(64) YES NULL +total_latency text YES NULL +rows_fetched bigint(20) unsigned NO NULL +fetch_latency text YES NULL +rows_inserted bigint(20) unsigned NO NULL +insert_latency text YES NULL +rows_updated bigint(20) unsigned NO NULL +update_latency text YES NULL +rows_deleted bigint(20) unsigned NO NULL +delete_latency text YES NULL +io_read_requests decimal(42,0) YES NULL +io_read text YES NULL +io_read_latency text YES NULL +io_write_requests decimal(42,0) YES NULL +io_write text YES NULL +io_write_latency text YES NULL +io_misc_requests decimal(42,0) YES NULL +io_misc_latency text YES NULL +SELECT * FROM sys.schema_table_statistics; +DESC sys.x$schema_table_statistics; +Field Type Null Key Default Extra +table_schema varchar(64) YES NULL +table_name varchar(64) YES NULL +total_latency bigint(20) unsigned NO NULL +rows_fetched bigint(20) unsigned NO NULL +fetch_latency bigint(20) unsigned NO NULL +rows_inserted bigint(20) unsigned NO NULL +insert_latency bigint(20) unsigned NO NULL +rows_updated bigint(20) unsigned NO NULL +update_latency bigint(20) unsigned NO NULL +rows_deleted bigint(20) unsigned NO NULL +delete_latency bigint(20) unsigned NO NULL +io_read_requests decimal(42,0) YES NULL +io_read decimal(41,0) YES NULL +io_read_latency decimal(42,0) YES NULL +io_write_requests decimal(42,0) YES NULL +io_write decimal(41,0) YES NULL +io_write_latency decimal(42,0) YES NULL +io_misc_requests decimal(42,0) YES NULL +io_misc_latency decimal(42,0) YES NULL +SELECT * FROM sys.x$schema_table_statistics; diff --git a/mysql-test/suite/sysschema/r/v_schema_table_statistics_with_buffer.result b/mysql-test/suite/sysschema/r/v_schema_table_statistics_with_buffer.result new file mode 100644 index 00000000..809e8a4e --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_schema_table_statistics_with_buffer.result @@ -0,0 +1,56 @@ +DESC sys.schema_table_statistics_with_buffer; +Field Type Null Key Default Extra +table_schema varchar(64) YES NULL +table_name varchar(64) YES NULL +rows_fetched bigint(20) unsigned NO NULL +fetch_latency text YES NULL +rows_inserted bigint(20) unsigned NO NULL +insert_latency text YES NULL +rows_updated bigint(20) unsigned NO NULL +update_latency text YES NULL +rows_deleted bigint(20) unsigned NO NULL +delete_latency text YES NULL +io_read_requests decimal(42,0) YES NULL +io_read text YES NULL +io_read_latency text YES NULL +io_write_requests decimal(42,0) YES NULL +io_write text YES NULL +io_write_latency text YES NULL +io_misc_requests decimal(42,0) YES NULL +io_misc_latency text YES NULL +innodb_buffer_allocated text YES NULL +innodb_buffer_data text YES NULL +innodb_buffer_free text YES NULL +innodb_buffer_pages bigint(21) YES 0 +innodb_buffer_pages_hashed bigint(21) YES 0 +innodb_buffer_pages_old bigint(21) YES 0 +innodb_buffer_rows_cached decimal(44,0) YES 0 +SELECT * FROM sys.schema_table_statistics_with_buffer; +DESC sys.x$schema_table_statistics_with_buffer; +Field Type Null Key Default Extra +table_schema varchar(64) YES NULL +table_name varchar(64) YES NULL +rows_fetched bigint(20) unsigned NO NULL +fetch_latency bigint(20) unsigned NO NULL +rows_inserted bigint(20) unsigned NO NULL +insert_latency bigint(20) unsigned NO NULL +rows_updated bigint(20) unsigned NO NULL +update_latency bigint(20) unsigned NO NULL +rows_deleted bigint(20) unsigned NO NULL +delete_latency bigint(20) unsigned NO NULL +io_read_requests decimal(42,0) YES NULL +io_read decimal(41,0) YES NULL +io_read_latency decimal(42,0) YES NULL +io_write_requests decimal(42,0) YES NULL +io_write decimal(41,0) YES NULL +io_write_latency decimal(42,0) YES NULL +io_misc_requests decimal(42,0) YES NULL +io_misc_latency decimal(42,0) YES NULL +innodb_buffer_allocated decimal(43,0) YES NULL +innodb_buffer_data decimal(43,0) YES NULL +innodb_buffer_free decimal(44,0) YES NULL +innodb_buffer_pages bigint(21) YES 0 +innodb_buffer_pages_hashed bigint(21) YES 0 +innodb_buffer_pages_old bigint(21) YES 0 +innodb_buffer_rows_cached decimal(44,0) YES 0 +SELECT * FROM sys.x$schema_table_statistics_with_buffer; diff --git a/mysql-test/suite/sysschema/r/v_schema_tables_with_full_table_scans.result b/mysql-test/suite/sysschema/r/v_schema_tables_with_full_table_scans.result new file mode 100644 index 00000000..a6aca341 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_schema_tables_with_full_table_scans.result @@ -0,0 +1,51 @@ +DESC sys.schema_tables_with_full_table_scans; +Field Type Null Key Default Extra +object_schema varchar(64) YES NULL +object_name varchar(64) YES NULL +rows_full_scanned bigint(20) unsigned NO NULL +latency text YES NULL +SELECT * FROM sys.schema_tables_with_full_table_scans; +DESC sys.x$schema_tables_with_full_table_scans; +Field Type Null Key Default Extra +object_schema varchar(64) YES NULL +object_name varchar(64) YES NULL +rows_full_scanned bigint(20) unsigned NO NULL +latency bigint(20) unsigned NO NULL +SELECT * FROM sys.x$schema_tables_with_full_table_scans; +CREATE TABLE test.t (i BIGINT AUTO_INCREMENT PRIMARY KEY, j BIGINT) ENGINE = innodb; +INSERT INTO test.t (j) VALUES (1), (2), (3); +INSERT INTO test.t (j) (SELECT j*2 FROM test.t); +INSERT INTO test.t (j) (SELECT j*2 FROM test.t); +INSERT INTO test.t (j) (SELECT j*2 FROM test.t); +INSERT INTO test.t (j) (SELECT j*2 FROM test.t); +INSERT INTO test.t (j) (SELECT j*2 FROM test.t); +INSERT INTO test.t (j) (SELECT j*2 FROM test.t); +INSERT INTO test.t (j) (SELECT j*2 FROM test.t); +INSERT INTO test.t (j) (SELECT j*2 FROM test.t); +CALL sys.ps_truncate_all_tables(false); +summary +Truncated 44 tables +SELECT i, j, RAND() FROM test.t WHERE j = 12; +SELECT object_schema, object_name, rows_full_scanned FROM sys.schema_tables_with_full_table_scans; +object_schema object_name rows_full_scanned +test t 768 +SELECT object_schema, object_name, rows_full_scanned FROM sys.x$schema_tables_with_full_table_scans; +object_schema object_name rows_full_scanned +test t 768 +SELECT i, j, RAND() FROM test.t WHERE j = 12; +SELECT object_schema, object_name, rows_full_scanned FROM sys.schema_tables_with_full_table_scans; +object_schema object_name rows_full_scanned +test t 1536 +SELECT object_schema, object_name, rows_full_scanned FROM sys.x$schema_tables_with_full_table_scans; +object_schema object_name rows_full_scanned +test t 1536 +SELECT * FROM test.t WHERE i = 10; +i j +10 4 +SELECT object_schema, object_name, rows_full_scanned FROM sys.schema_tables_with_full_table_scans; +object_schema object_name rows_full_scanned +test t 1536 +SELECT object_schema, object_name, rows_full_scanned FROM sys.x$schema_tables_with_full_table_scans; +object_schema object_name rows_full_scanned +test t 1536 +DROP TABLE test.t; diff --git a/mysql-test/suite/sysschema/r/v_schema_unused_indexes.result b/mysql-test/suite/sysschema/r/v_schema_unused_indexes.result new file mode 100644 index 00000000..fc8395dc --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_schema_unused_indexes.result @@ -0,0 +1,6 @@ +DESC sys.schema_unused_indexes; +Field Type Null Key Default Extra +object_schema varchar(64) YES NULL +object_name varchar(64) YES NULL +index_name varchar(64) YES NULL +SELECT * FROM sys.schema_unused_indexes; diff --git a/mysql-test/suite/sysschema/r/v_session.result b/mysql-test/suite/sysschema/r/v_session.result new file mode 100644 index 00000000..fd88b8f1 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_session.result @@ -0,0 +1,62 @@ +DESC sys.session; +Field Type Null Key Default Extra +thd_id bigint(20) unsigned NO NULL +conn_id bigint(20) unsigned YES NULL +user varchar(384) YES NULL +db varchar(64) YES NULL +command varchar(16) YES NULL +state varchar(64) YES NULL +time bigint(20) YES NULL +current_statement longtext YES NULL +statement_latency mediumtext YES NULL +progress decimal(26,2) YES NULL +lock_latency text YES NULL +rows_examined bigint(20) unsigned YES NULL +rows_sent bigint(20) unsigned YES NULL +rows_affected bigint(20) unsigned YES NULL +tmp_tables bigint(20) unsigned YES NULL +tmp_disk_tables bigint(20) unsigned YES NULL +full_scan varchar(3) YES NULL +last_statement longtext YES NULL +last_statement_latency mediumtext YES NULL +current_memory text YES NULL +last_wait varchar(128) YES NULL +last_wait_latency mediumtext YES NULL +source varchar(64) YES NULL +trx_latency text YES NULL +trx_state enum('ACTIVE','COMMITTED','ROLLED BACK') YES NULL +trx_autocommit enum('YES','NO') YES NULL +pid varchar(1024) YES NULL +program_name varchar(1024) YES NULL +SELECT * FROM sys.session; +DESC sys.x$session; +Field Type Null Key Default Extra +thd_id bigint(20) unsigned NO NULL +conn_id bigint(20) unsigned YES NULL +user varchar(384) YES NULL +db varchar(64) YES NULL +command varchar(16) YES NULL +state varchar(64) YES NULL +time bigint(20) YES NULL +current_statement longtext YES NULL +statement_latency bigint(20) unsigned YES NULL +progress decimal(26,2) YES NULL +lock_latency bigint(20) unsigned YES NULL +rows_examined bigint(20) unsigned YES NULL +rows_sent bigint(20) unsigned YES NULL +rows_affected bigint(20) unsigned YES NULL +tmp_tables bigint(20) unsigned YES NULL +tmp_disk_tables bigint(20) unsigned YES NULL +full_scan varchar(3) YES NULL +last_statement longtext YES NULL +last_statement_latency bigint(20) unsigned YES NULL +current_memory decimal(41,0) YES NULL +last_wait varchar(128) YES NULL +last_wait_latency varchar(20) YES NULL +source varchar(64) YES NULL +trx_latency bigint(20) unsigned YES NULL +trx_state enum('ACTIVE','COMMITTED','ROLLED BACK') YES NULL +trx_autocommit enum('YES','NO') YES NULL +pid varchar(1024) YES NULL +program_name varchar(1024) YES NULL +SELECT * FROM sys.x$session; diff --git a/mysql-test/suite/sysschema/r/v_session_ssl_status.result b/mysql-test/suite/sysschema/r/v_session_ssl_status.result new file mode 100644 index 00000000..e0e397c3 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_session_ssl_status.result @@ -0,0 +1,7 @@ +DESC sys.session_ssl_status; +Field Type Null Key Default Extra +thread_id bigint(20) unsigned NO NULL +ssl_version varchar(1024) YES NULL +ssl_cipher varchar(1024) YES NULL +ssl_sessions_reused varchar(1024) YES NULL +SELECT * FROM sys.session_ssl_status; diff --git a/mysql-test/suite/sysschema/r/v_statement_analysis.result b/mysql-test/suite/sysschema/r/v_statement_analysis.result new file mode 100644 index 00000000..c3dd167c --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_statement_analysis.result @@ -0,0 +1,52 @@ +DESC sys.statement_analysis; +Field Type Null Key Default Extra +query longtext YES NULL +db varchar(64) YES NULL +full_scan varchar(1) NO +exec_count bigint(20) unsigned NO NULL +err_count bigint(20) unsigned NO NULL +warn_count bigint(20) unsigned NO NULL +total_latency text YES NULL +max_latency text YES NULL +avg_latency text YES NULL +lock_latency text YES NULL +rows_sent bigint(20) unsigned NO NULL +rows_sent_avg decimal(21,0) NO 0 +rows_examined bigint(20) unsigned NO NULL +rows_examined_avg decimal(21,0) NO 0 +rows_affected bigint(20) unsigned NO NULL +rows_affected_avg decimal(21,0) NO 0 +tmp_tables bigint(20) unsigned NO NULL +tmp_disk_tables bigint(20) unsigned NO NULL +rows_sorted bigint(20) unsigned NO NULL +sort_merge_passes bigint(20) unsigned NO NULL +digest varchar(32) YES NULL +first_seen timestamp NO 0000-00-00 00:00:00 +last_seen timestamp NO 0000-00-00 00:00:00 +SELECT * FROM sys.statement_analysis; +DESC sys.x$statement_analysis; +Field Type Null Key Default Extra +query longtext YES NULL +db varchar(64) YES NULL +full_scan varchar(1) NO +exec_count bigint(20) unsigned NO NULL +err_count bigint(20) unsigned NO NULL +warn_count bigint(20) unsigned NO NULL +total_latency bigint(20) unsigned NO NULL +max_latency bigint(20) unsigned NO NULL +avg_latency bigint(20) unsigned NO NULL +lock_latency bigint(20) unsigned NO NULL +rows_sent bigint(20) unsigned NO NULL +rows_sent_avg decimal(21,0) NO 0 +rows_examined bigint(20) unsigned NO NULL +rows_examined_avg decimal(21,0) NO 0 +rows_affected bigint(20) unsigned NO NULL +rows_affected_avg decimal(21,0) NO 0 +tmp_tables bigint(20) unsigned NO NULL +tmp_disk_tables bigint(20) unsigned NO NULL +rows_sorted bigint(20) unsigned NO NULL +sort_merge_passes bigint(20) unsigned NO NULL +digest varchar(32) YES NULL +first_seen timestamp NO 0000-00-00 00:00:00 +last_seen timestamp NO 0000-00-00 00:00:00 +SELECT * FROM sys.x$statement_analysis; diff --git a/mysql-test/suite/sysschema/r/v_statements_with_errors_or_warnings.result b/mysql-test/suite/sysschema/r/v_statements_with_errors_or_warnings.result new file mode 100644 index 00000000..250f4ec1 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_statements_with_errors_or_warnings.result @@ -0,0 +1,26 @@ +DESC sys.statements_with_errors_or_warnings; +Field Type Null Key Default Extra +query longtext YES NULL +db varchar(64) YES NULL +exec_count bigint(20) unsigned NO NULL +errors bigint(20) unsigned NO NULL +error_pct decimal(27,4) NO 0.0000 +warnings bigint(20) unsigned NO NULL +warning_pct decimal(27,4) NO 0.0000 +first_seen timestamp NO 0000-00-00 00:00:00 +last_seen timestamp NO 0000-00-00 00:00:00 +digest varchar(32) YES NULL +SELECT * FROM sys.statements_with_errors_or_warnings; +DESC sys.x$statements_with_errors_or_warnings; +Field Type Null Key Default Extra +query longtext YES NULL +db varchar(64) YES NULL +exec_count bigint(20) unsigned NO NULL +errors bigint(20) unsigned NO NULL +error_pct decimal(27,4) NO 0.0000 +warnings bigint(20) unsigned NO NULL +warning_pct decimal(27,4) NO 0.0000 +first_seen timestamp NO 0000-00-00 00:00:00 +last_seen timestamp NO 0000-00-00 00:00:00 +digest varchar(32) YES NULL +SELECT * FROM sys.x$statements_with_errors_or_warnings; diff --git a/mysql-test/suite/sysschema/r/v_statements_with_full_table_scans.result b/mysql-test/suite/sysschema/r/v_statements_with_full_table_scans.result new file mode 100644 index 00000000..a762cfd7 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_statements_with_full_table_scans.result @@ -0,0 +1,69 @@ +DESC sys.statements_with_full_table_scans; +Field Type Null Key Default Extra +query longtext YES NULL +db varchar(64) YES NULL +exec_count bigint(20) unsigned NO NULL +total_latency text YES NULL +no_index_used_count bigint(20) unsigned NO NULL +no_good_index_used_count bigint(20) unsigned NO NULL +no_index_used_pct decimal(24,0) NO 0 +rows_sent bigint(20) unsigned NO NULL +rows_examined bigint(20) unsigned NO NULL +rows_sent_avg decimal(21,0) unsigned YES NULL +rows_examined_avg decimal(21,0) unsigned YES NULL +first_seen timestamp NO 0000-00-00 00:00:00 +last_seen timestamp NO 0000-00-00 00:00:00 +digest varchar(32) YES NULL +SELECT * FROM sys.statements_with_full_table_scans; +DESC sys.x$statements_with_full_table_scans; +Field Type Null Key Default Extra +query longtext YES NULL +db varchar(64) YES NULL +exec_count bigint(20) unsigned NO NULL +total_latency bigint(20) unsigned NO NULL +no_index_used_count bigint(20) unsigned NO NULL +no_good_index_used_count bigint(20) unsigned NO NULL +no_index_used_pct decimal(24,0) NO 0 +rows_sent bigint(20) unsigned NO NULL +rows_examined bigint(20) unsigned NO NULL +rows_sent_avg decimal(21,0) unsigned YES NULL +rows_examined_avg decimal(21,0) unsigned YES NULL +first_seen timestamp NO 0000-00-00 00:00:00 +last_seen timestamp NO 0000-00-00 00:00:00 +digest varchar(32) YES NULL +SELECT * FROM sys.x$statements_with_full_table_scans; +CREATE DATABASE v_statements_with_full_table_scans; +CREATE TABLE v_statements_with_full_table_scans.t (i BIGINT AUTO_INCREMENT PRIMARY KEY, j BIGINT) ENGINE = innodb; +INSERT INTO v_statements_with_full_table_scans.t (j) VALUES (1), (2), (3); +INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t); +INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t); +INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t); +INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t); +INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t); +INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t); +INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t); +INSERT INTO v_statements_with_full_table_scans.t (j) (SELECT j*2 FROM v_statements_with_full_table_scans.t); +CALL sys.ps_truncate_all_tables(false); +summary +Truncated 44 tables +SELECT i, j, RAND() FROM v_statements_with_full_table_scans.t WHERE j = 12; +SELECT db, query, rows_examined FROM sys.statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC; +db query rows_examined +SELECT db, query, rows_examined FROM sys.x$statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC; +db query rows_examined +test SELECT `i` , `j` , `RAND` ( ) FROM `v_statements_with_full_table_scans` . `t` WHERE `j` = ? 768 +SELECT i, j, RAND() FROM v_statements_with_full_table_scans.t WHERE j = 12; +SELECT db, query, rows_examined FROM sys.statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC; +db query rows_examined +SELECT db, query, rows_examined FROM sys.x$statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC; +db query rows_examined +test SELECT `i` , `j` , `RAND` ( ) FROM `v_statements_with_full_table_scans` . `t` WHERE `j` = ? 1536 +SELECT * FROM v_statements_with_full_table_scans.t WHERE i = 10; +i j +10 4 +SELECT db, query, rows_examined FROM sys.statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC; +db query rows_examined +SELECT db, query, rows_examined FROM sys.x$statements_with_full_table_scans WHERE query LIKE '%v_statements_with_full_table_scans%' ORDER BY rows_examined DESC; +db query rows_examined +test SELECT `i` , `j` , `RAND` ( ) FROM `v_statements_with_full_table_scans` . `t` WHERE `j` = ? 1536 +DROP DATABASE v_statements_with_full_table_scans; diff --git a/mysql-test/suite/sysschema/r/v_statements_with_runtimes_in_95th_percentile.result b/mysql-test/suite/sysschema/r/v_statements_with_runtimes_in_95th_percentile.result new file mode 100644 index 00000000..5cc05f27 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_statements_with_runtimes_in_95th_percentile.result @@ -0,0 +1,38 @@ +DESC sys.statements_with_runtimes_in_95th_percentile; +Field Type Null Key Default Extra +query longtext YES NULL +db varchar(64) YES NULL +full_scan varchar(1) NO +exec_count bigint(20) unsigned NO NULL +err_count bigint(20) unsigned NO NULL +warn_count bigint(20) unsigned NO NULL +total_latency text YES NULL +max_latency text YES NULL +avg_latency text YES NULL +rows_sent bigint(20) unsigned NO NULL +rows_sent_avg decimal(21,0) NO 0 +rows_examined bigint(20) unsigned NO NULL +rows_examined_avg decimal(21,0) NO 0 +first_seen timestamp NO 0000-00-00 00:00:00 +last_seen timestamp NO 0000-00-00 00:00:00 +digest varchar(32) YES NULL +SELECT * FROM sys.statements_with_runtimes_in_95th_percentile; +DESC sys.x$statements_with_runtimes_in_95th_percentile; +Field Type Null Key Default Extra +query longtext YES NULL +db varchar(64) YES NULL +full_scan varchar(1) NO +exec_count bigint(20) unsigned NO NULL +err_count bigint(20) unsigned NO NULL +warn_count bigint(20) unsigned NO NULL +total_latency bigint(20) unsigned NO NULL +max_latency bigint(20) unsigned NO NULL +avg_latency bigint(20) unsigned NO NULL +rows_sent bigint(20) unsigned NO NULL +rows_sent_avg decimal(21,0) NO 0 +rows_examined bigint(20) unsigned NO NULL +rows_examined_avg decimal(21,0) NO 0 +first_seen timestamp NO 0000-00-00 00:00:00 +last_seen timestamp NO 0000-00-00 00:00:00 +digest varchar(32) YES NULL +SELECT * FROM sys.x$statements_with_runtimes_in_95th_percentile; diff --git a/mysql-test/suite/sysschema/r/v_statements_with_sorting.result b/mysql-test/suite/sysschema/r/v_statements_with_sorting.result new file mode 100644 index 00000000..034987b8 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_statements_with_sorting.result @@ -0,0 +1,32 @@ +DESC sys.statements_with_sorting; +Field Type Null Key Default Extra +query longtext YES NULL +db varchar(64) YES NULL +exec_count bigint(20) unsigned NO NULL +total_latency text YES NULL +sort_merge_passes bigint(20) unsigned NO NULL +avg_sort_merges decimal(21,0) NO 0 +sorts_using_scans bigint(20) unsigned NO NULL +sort_using_range bigint(20) unsigned NO NULL +rows_sorted bigint(20) unsigned NO NULL +avg_rows_sorted decimal(21,0) NO 0 +first_seen timestamp NO 0000-00-00 00:00:00 +last_seen timestamp NO 0000-00-00 00:00:00 +digest varchar(32) YES NULL +SELECT * FROM sys.statements_with_sorting; +DESC sys.x$statements_with_sorting; +Field Type Null Key Default Extra +query longtext YES NULL +db varchar(64) YES NULL +exec_count bigint(20) unsigned NO NULL +total_latency bigint(20) unsigned NO NULL +sort_merge_passes bigint(20) unsigned NO NULL +avg_sort_merges decimal(21,0) NO 0 +sorts_using_scans bigint(20) unsigned NO NULL +sort_using_range bigint(20) unsigned NO NULL +rows_sorted bigint(20) unsigned NO NULL +avg_rows_sorted decimal(21,0) NO 0 +first_seen timestamp NO 0000-00-00 00:00:00 +last_seen timestamp NO 0000-00-00 00:00:00 +digest varchar(32) YES NULL +SELECT * FROM sys.x$statements_with_sorting; diff --git a/mysql-test/suite/sysschema/r/v_statements_with_temp_tables.result b/mysql-test/suite/sysschema/r/v_statements_with_temp_tables.result new file mode 100644 index 00000000..52683a56 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_statements_with_temp_tables.result @@ -0,0 +1,28 @@ +DESC sys.statements_with_temp_tables; +Field Type Null Key Default Extra +query longtext YES NULL +db varchar(64) YES NULL +exec_count bigint(20) unsigned NO NULL +total_latency text YES NULL +memory_tmp_tables bigint(20) unsigned NO NULL +disk_tmp_tables bigint(20) unsigned NO NULL +avg_tmp_tables_per_query decimal(21,0) NO 0 +tmp_tables_to_disk_pct decimal(24,0) NO 0 +first_seen timestamp NO 0000-00-00 00:00:00 +last_seen timestamp NO 0000-00-00 00:00:00 +digest varchar(32) YES NULL +SELECT * FROM sys.statements_with_temp_tables; +DESC sys.x$statements_with_temp_tables; +Field Type Null Key Default Extra +query longtext YES NULL +db varchar(64) YES NULL +exec_count bigint(20) unsigned NO NULL +total_latency bigint(20) unsigned NO NULL +memory_tmp_tables bigint(20) unsigned NO NULL +disk_tmp_tables bigint(20) unsigned NO NULL +avg_tmp_tables_per_query decimal(21,0) NO 0 +tmp_tables_to_disk_pct decimal(24,0) NO 0 +first_seen timestamp NO 0000-00-00 00:00:00 +last_seen timestamp NO 0000-00-00 00:00:00 +digest varchar(32) YES NULL +SELECT * FROM sys.x$statements_with_temp_tables; diff --git a/mysql-test/suite/sysschema/r/v_user_summary.result b/mysql-test/suite/sysschema/r/v_user_summary.result new file mode 100644 index 00000000..233fcccc --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_user_summary.result @@ -0,0 +1,30 @@ +DESC sys.user_summary; +Field Type Null Key Default Extra +user varchar(128) YES NULL +statements decimal(64,0) YES NULL +statement_latency text YES NULL +statement_avg_latency text YES NULL +table_scans decimal(65,0) YES NULL +file_ios decimal(64,0) YES NULL +file_io_latency text YES NULL +current_connections decimal(41,0) YES NULL +total_connections decimal(41,0) YES NULL +unique_hosts bigint(21) NO 0 +current_memory text YES NULL +total_memory_allocated text YES NULL +SELECT * FROM sys.user_summary; +DESC sys.x$user_summary; +Field Type Null Key Default Extra +user varchar(128) YES NULL +statements decimal(64,0) YES NULL +statement_latency decimal(64,0) YES NULL +statement_avg_latency decimal(65,4) NO 0.0000 +table_scans decimal(65,0) YES NULL +file_ios decimal(64,0) YES NULL +file_io_latency decimal(64,0) YES NULL +current_connections decimal(41,0) YES NULL +total_connections decimal(41,0) YES NULL +unique_hosts bigint(21) NO 0 +current_memory decimal(63,0) YES NULL +total_memory_allocated decimal(64,0) YES NULL +SELECT * FROM sys.x$user_summary; diff --git a/mysql-test/suite/sysschema/r/v_user_summary_by_file_io.result b/mysql-test/suite/sysschema/r/v_user_summary_by_file_io.result new file mode 100644 index 00000000..f6f106a5 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_user_summary_by_file_io.result @@ -0,0 +1,12 @@ +DESC sys.user_summary_by_file_io; +Field Type Null Key Default Extra +user varchar(128) YES NULL +ios decimal(42,0) YES NULL +io_latency text YES NULL +SELECT * FROM sys.user_summary_by_file_io; +DESC sys.x$user_summary_by_file_io; +Field Type Null Key Default Extra +user varchar(128) YES NULL +ios decimal(42,0) YES NULL +io_latency decimal(42,0) YES NULL +SELECT * FROM sys.x$user_summary_by_file_io; diff --git a/mysql-test/suite/sysschema/r/v_user_summary_by_file_io_type.result b/mysql-test/suite/sysschema/r/v_user_summary_by_file_io_type.result new file mode 100644 index 00000000..c143697c --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_user_summary_by_file_io_type.result @@ -0,0 +1,16 @@ +DESC sys.user_summary_by_file_io_type; +Field Type Null Key Default Extra +user varchar(128) YES NULL +event_name varchar(128) NO NULL +total bigint(20) unsigned NO NULL +latency text YES NULL +max_latency text YES NULL +SELECT * FROM sys.user_summary_by_file_io_type; +DESC sys.x$user_summary_by_file_io_type; +Field Type Null Key Default Extra +user varchar(128) YES NULL +event_name varchar(128) NO NULL +total bigint(20) unsigned NO NULL +latency bigint(20) unsigned NO NULL +max_latency bigint(20) unsigned NO NULL +SELECT * FROM sys.x$user_summary_by_file_io_type; diff --git a/mysql-test/suite/sysschema/r/v_user_summary_by_stages.result b/mysql-test/suite/sysschema/r/v_user_summary_by_stages.result new file mode 100644 index 00000000..9b90fe9c --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_user_summary_by_stages.result @@ -0,0 +1,16 @@ +DESC sys.user_summary_by_stages; +Field Type Null Key Default Extra +user varchar(128) YES NULL +event_name varchar(128) NO NULL +total bigint(20) unsigned NO NULL +total_latency text YES NULL +avg_latency text YES NULL +SELECT * FROM sys.user_summary_by_stages; +DESC sys.x$user_summary_by_stages; +Field Type Null Key Default Extra +user varchar(128) YES NULL +event_name varchar(128) NO NULL +total bigint(20) unsigned NO NULL +total_latency bigint(20) unsigned NO NULL +avg_latency bigint(20) unsigned NO NULL +SELECT * FROM sys.x$user_summary_by_stages; diff --git a/mysql-test/suite/sysschema/r/v_user_summary_by_statement_latency.result b/mysql-test/suite/sysschema/r/v_user_summary_by_statement_latency.result new file mode 100644 index 00000000..36dadc29 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_user_summary_by_statement_latency.result @@ -0,0 +1,24 @@ +DESC sys.user_summary_by_statement_latency; +Field Type Null Key Default Extra +user varchar(128) YES NULL +total decimal(42,0) YES NULL +total_latency text YES NULL +max_latency text YES NULL +lock_latency text YES NULL +rows_sent decimal(42,0) YES NULL +rows_examined decimal(42,0) YES NULL +rows_affected decimal(42,0) YES NULL +full_scans decimal(43,0) YES NULL +SELECT * FROM sys.user_summary_by_statement_latency; +DESC sys.x$user_summary_by_statement_latency; +Field Type Null Key Default Extra +user varchar(128) YES NULL +total decimal(42,0) YES NULL +total_latency decimal(42,0) YES NULL +max_latency decimal(42,0) YES NULL +lock_latency decimal(42,0) YES NULL +rows_sent decimal(42,0) YES NULL +rows_examined decimal(42,0) YES NULL +rows_affected decimal(42,0) YES NULL +full_scans decimal(43,0) YES NULL +SELECT * FROM sys.x$user_summary_by_statement_latency; diff --git a/mysql-test/suite/sysschema/r/v_user_summary_by_statement_type.result b/mysql-test/suite/sysschema/r/v_user_summary_by_statement_type.result new file mode 100644 index 00000000..bd006c87 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_user_summary_by_statement_type.result @@ -0,0 +1,26 @@ +DESC sys.user_summary_by_statement_type; +Field Type Null Key Default Extra +user varchar(128) YES NULL +statement varchar(128) YES NULL +total bigint(20) unsigned NO NULL +total_latency text YES NULL +max_latency text YES NULL +lock_latency text YES NULL +rows_sent bigint(20) unsigned NO NULL +rows_examined bigint(20) unsigned NO NULL +rows_affected bigint(20) unsigned NO NULL +full_scans bigint(21) unsigned NO 0 +SELECT * FROM sys.user_summary_by_statement_type; +DESC sys.x$user_summary_by_statement_type; +Field Type Null Key Default Extra +user varchar(128) YES NULL +statement varchar(128) YES NULL +total bigint(20) unsigned NO NULL +total_latency bigint(20) unsigned NO NULL +max_latency bigint(20) unsigned NO NULL +lock_latency bigint(20) unsigned NO NULL +rows_sent bigint(20) unsigned NO NULL +rows_examined bigint(20) unsigned NO NULL +rows_affected bigint(20) unsigned NO NULL +full_scans bigint(21) unsigned NO 0 +SELECT * FROM sys.x$user_summary_by_statement_type; diff --git a/mysql-test/suite/sysschema/r/v_version.result b/mysql-test/suite/sysschema/r/v_version.result new file mode 100644 index 00000000..ac914fc8 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_version.result @@ -0,0 +1,3 @@ +SELECT sys_version FROM sys.version; +sys_version +1.5.1 diff --git a/mysql-test/suite/sysschema/r/v_wait_classes_global_by_avg_latency.result b/mysql-test/suite/sysschema/r/v_wait_classes_global_by_avg_latency.result new file mode 100644 index 00000000..f6559cb7 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_wait_classes_global_by_avg_latency.result @@ -0,0 +1,18 @@ +DESC sys.wait_classes_global_by_avg_latency; +Field Type Null Key Default Extra +event_class varchar(128) YES NULL +total decimal(42,0) YES NULL +total_latency text YES NULL +min_latency text YES NULL +avg_latency text YES NULL +max_latency text YES NULL +SELECT * FROM sys.wait_classes_global_by_avg_latency; +DESC sys.x$wait_classes_global_by_avg_latency; +Field Type Null Key Default Extra +event_class varchar(128) YES NULL +total decimal(42,0) YES NULL +total_latency decimal(42,0) YES NULL +min_latency bigint(20) unsigned YES NULL +avg_latency decimal(46,4) NO 0.0000 +max_latency bigint(20) unsigned YES NULL +SELECT * FROM sys.x$wait_classes_global_by_avg_latency; diff --git a/mysql-test/suite/sysschema/r/v_wait_classes_global_by_latency.result b/mysql-test/suite/sysschema/r/v_wait_classes_global_by_latency.result new file mode 100644 index 00000000..10db5ead --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_wait_classes_global_by_latency.result @@ -0,0 +1,18 @@ +DESC sys.wait_classes_global_by_latency; +Field Type Null Key Default Extra +event_class varchar(128) YES NULL +total decimal(42,0) YES NULL +total_latency text YES NULL +min_latency text YES NULL +avg_latency text YES NULL +max_latency text YES NULL +SELECT * FROM sys.wait_classes_global_by_latency; +DESC sys.x$wait_classes_global_by_latency; +Field Type Null Key Default Extra +event_class varchar(128) YES NULL +total decimal(42,0) YES NULL +total_latency decimal(42,0) YES NULL +min_latency bigint(20) unsigned YES NULL +avg_latency decimal(46,4) NO 0.0000 +max_latency bigint(20) unsigned YES NULL +SELECT * FROM sys.x$wait_classes_global_by_latency; diff --git a/mysql-test/suite/sysschema/r/v_waits_by_host_by_latency.result b/mysql-test/suite/sysschema/r/v_waits_by_host_by_latency.result new file mode 100644 index 00000000..5e595d9c --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_waits_by_host_by_latency.result @@ -0,0 +1,18 @@ +DESC sys.waits_by_host_by_latency; +Field Type Null Key Default Extra +host varchar(255) YES NULL +event varchar(128) NO NULL +total bigint(20) unsigned NO NULL +total_latency text YES NULL +avg_latency text YES NULL +max_latency text YES NULL +SELECT * FROM sys.waits_by_host_by_latency; +DESC sys.x$waits_by_host_by_latency; +Field Type Null Key Default Extra +host varchar(255) YES NULL +event varchar(128) NO NULL +total bigint(20) unsigned NO NULL +total_latency bigint(20) unsigned NO NULL +avg_latency bigint(20) unsigned NO NULL +max_latency bigint(20) unsigned NO NULL +SELECT * FROM sys.x$waits_by_host_by_latency; diff --git a/mysql-test/suite/sysschema/r/v_waits_by_user_by_latency.result b/mysql-test/suite/sysschema/r/v_waits_by_user_by_latency.result new file mode 100644 index 00000000..860db68c --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_waits_by_user_by_latency.result @@ -0,0 +1,18 @@ +DESC sys.waits_by_user_by_latency; +Field Type Null Key Default Extra +user varchar(128) YES NULL +event varchar(128) NO NULL +total bigint(20) unsigned NO NULL +total_latency text YES NULL +avg_latency text YES NULL +max_latency text YES NULL +SELECT * FROM sys.waits_by_user_by_latency; +DESC sys.x$waits_by_user_by_latency; +Field Type Null Key Default Extra +user varchar(128) YES NULL +event varchar(128) NO NULL +total bigint(20) unsigned NO NULL +total_latency bigint(20) unsigned NO NULL +avg_latency bigint(20) unsigned NO NULL +max_latency bigint(20) unsigned NO NULL +SELECT * FROM sys.x$waits_by_user_by_latency; diff --git a/mysql-test/suite/sysschema/r/v_waits_global_by_latency.result b/mysql-test/suite/sysschema/r/v_waits_global_by_latency.result new file mode 100644 index 00000000..1e32a9b8 --- /dev/null +++ b/mysql-test/suite/sysschema/r/v_waits_global_by_latency.result @@ -0,0 +1,16 @@ +DESC sys.waits_global_by_latency; +Field Type Null Key Default Extra +events varchar(128) NO NULL +total bigint(20) unsigned NO NULL +total_latency text YES NULL +avg_latency text YES NULL +max_latency text YES NULL +SELECT * FROM sys.waits_global_by_latency; +DESC sys.x$waits_global_by_latency; +Field Type Null Key Default Extra +events varchar(128) NO NULL +total bigint(20) unsigned NO NULL +total_latency bigint(20) unsigned NO NULL +avg_latency bigint(20) unsigned NO NULL +max_latency bigint(20) unsigned NO NULL +SELECT * FROM sys.x$waits_global_by_latency; diff --git a/mysql-test/suite/sysschema/r/version_functions.result b/mysql-test/suite/sysschema/r/version_functions.result new file mode 100644 index 00000000..57082ebc --- /dev/null +++ b/mysql-test/suite/sysschema/r/version_functions.result @@ -0,0 +1,6 @@ +SELECT sys.version_major(); +SELECT sys.version_minor(); +SELECT sys.version_patch(); +SELECT @my_version = SUBSTRING(VERSION(), 1, CHAR_LENGTH(@my_version)); +@my_version = SUBSTRING(VERSION(), 1, CHAR_LENGTH(@my_version)) +1 |