diff options
Diffstat (limited to 'mysql-test/suite/sysschema/t')
90 files changed, 2778 insertions, 0 deletions
diff --git a/mysql-test/suite/sysschema/t/all_sys_objects_exist.test b/mysql-test/suite/sysschema/t/all_sys_objects_exist.test new file mode 100644 index 00000000000..f4c8ee36359 --- /dev/null +++ b/mysql-test/suite/sysschema/t/all_sys_objects_exist.test @@ -0,0 +1,16 @@ +-- source include/not_embedded.inc +# Ensure database exists +USE sys; + +# Ensure all views are created +SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'sys' ORDER BY TABLE_NAME; + +# Ensure all functions and routines are created +SELECT ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'sys' ORDER BY ROUTINE_TYPE, ROUTINE_NAME; + +# Ensure all triggers are created +SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = 'sys' ORDER BY TRIGGER_NAME; + +# Check the version of the schema (and ensure no updates slip in undetected) +SELECT sys_version FROM sys.version; + diff --git a/mysql-test/suite/sysschema/t/fn_extract_schema_from_file_name.test b/mysql-test/suite/sysschema/t/fn_extract_schema_from_file_name.test new file mode 100644 index 00000000000..f3051947054 --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_extract_schema_from_file_name.test @@ -0,0 +1,9 @@ +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.extract_schema_from_file_name() function perfoms as expected + +# Passing NULL should return NULL +SELECT sys.extract_schema_from_file_name(NULL); + +# Ensure the right part of the path is extracted +SELECT sys.extract_schema_from_file_name('/var/lib/mysql/employees/employee.ibd'); diff --git a/mysql-test/suite/sysschema/t/fn_extract_table_from_file_name.test b/mysql-test/suite/sysschema/t/fn_extract_table_from_file_name.test new file mode 100644 index 00000000000..6e45917492e --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_extract_table_from_file_name.test @@ -0,0 +1,9 @@ +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.extract_table_from_file_name() function perfoms as expected + +# Passing NULL should return NULL +SELECT sys.extract_table_from_file_name(NULL); + +# Ensure the right part of the path is extracted +SELECT sys.extract_table_from_file_name('/var/lib/mysql/employees/employee.ibd'); diff --git a/mysql-test/suite/sysschema/t/fn_format_bytes.test b/mysql-test/suite/sysschema/t/fn_format_bytes.test new file mode 100644 index 00000000000..418c85c7051 --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_format_bytes.test @@ -0,0 +1,30 @@ +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.format_bytes() function perfoms as expected + +# Passing NULL/nothing should return NULL +SELECT sys.format_bytes(NULL); + +# Format bytes +SELECT sys.format_bytes(1); +SELECT sys.format_bytes(1023); + +# Format kilobytes +SELECT sys.format_bytes(1024); +SELECT sys.format_bytes(1048575); + +# Format megabytes +SELECT sys.format_bytes(1048576); +SELECT sys.format_bytes(1073741823); + +# Format gigabytes +SELECT sys.format_bytes(1073741824); +SELECT sys.format_bytes(1099511627775); + +# Format terabytes +SELECT sys.format_bytes(1099511627776); +SELECT sys.format_bytes(1125899906842623); + +# Formate petabytes +SELECT sys.format_bytes(1125899906842624); +SELECT sys.format_bytes(1125899906842624238947293); diff --git a/mysql-test/suite/sysschema/t/fn_format_path.test b/mysql-test/suite/sysschema/t/fn_format_path.test new file mode 100644 index 00000000000..24f78f766f0 --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_format_path.test @@ -0,0 +1,17 @@ +-- source include/not_embedded.inc +-- source include/not_windows.inc + + +# Passing NULL should return NULL +SELECT sys.format_path(NULL); +# Ensure datadir is recognized and stripped +SET @mypath := CONCAT(@@global.datadir, 'foo/bar.foo'); + +SELECT sys.format_path(@mypath); + +# Ensure tmpdir is recognized and stripped +SET @mypath := CONCAT(@@global.tmpdir, '/foo/bar.foo'); +SELECT sys.format_path(@mypath); + +# Unrecognized paths should return the full path +SELECT sys.format_path('/foo/bar/baz.foo'); diff --git a/mysql-test/suite/sysschema/t/fn_format_statement.test b/mysql-test/suite/sysschema/t/fn_format_statement.test new file mode 100644 index 00000000000..a4cffef0789 --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_format_statement.test @@ -0,0 +1,13 @@ +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.format_statement() function perfoms as expected + +# Passing NULL should return NULL +SELECT sys.format_statement(NULL); + +# Ensure long statements are truncated correctly +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; + +# Increase truncation limit and test that it takes effect +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; diff --git a/mysql-test/suite/sysschema/t/fn_format_time.test b/mysql-test/suite/sysschema/t/fn_format_time.test new file mode 100644 index 00000000000..9973443e8d7 --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_format_time.test @@ -0,0 +1,42 @@ +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.format_time() function perfoms as expected + +# Passing NULL should return NULL +SELECT sys.format_time(NULL); + +# Format picoseconds +SELECT sys.format_time(1); +SELECT sys.format_time(999); + +# Format nanoseconds +SELECT sys.format_time(1000); +SELECT sys.format_time(999999); + +# Format microseconds +SELECT sys.format_time(1000000); +SELECT sys.format_time(999999999); + +# Format milliseconds +SELECT sys.format_time(1000000000); +SELECT sys.format_time(999999999999); + +# Format seconds +SELECT sys.format_time(1000000000000); +SELECT sys.format_time(59999999999999); + +# Format minutes +SELECT sys.format_time(60000000000000); +SELECT sys.format_time(3599999999999999); + +# Format hours +SELECT sys.format_time(3600000000000000); +SELECT sys.format_time(86399999999999988); + +# Format days +SELECT sys.format_time(86400000000000000); +SELECT sys.format_time(604799999999999888); + +# Format weeks +SELECT sys.format_time(604800000000000000); +SELECT sys.format_time(2389472398472389748237429837423984728374); diff --git a/mysql-test/suite/sysschema/t/fn_list_add.test b/mysql-test/suite/sysschema/t/fn_list_add.test new file mode 100644 index 00000000000..d2e479ed392 --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_list_add.test @@ -0,0 +1,39 @@ +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.list_add() function perfoms as expected + +# Should init with a single value +SELECT sys.list_add(NULL, 'foo'); + +# Should init with a single value +SELECT sys.list_add('', 'foo'); + +# Should add to the list with a comma +SELECT sys.list_add('bar', 'foo'); + +# Should add to the list with a single comma +SELECT sys.list_add('bar, ', 'foo'); + +# Should return an error with a NULL value to add +--error 1138 +SELECT sys.list_add('foo', NULL); + +# Test the sql mode update use case +# save old mode to restore +SET @sqlmode := @@sql_mode; + +SELECT @@sql_mode; + +SET SESSION sql_mode = sys.list_add(@@sql_mode, 'ANSI_QUOTES'); + +SELECT @@sql_mode; + +# restore old mode +SET SESSION sql_mode = @sqlmode; +SET @sqlmode := NULL; + +# Check too large a value +SET @input := REPEAT('a', 4194304); +-- error 1406 +SELECT sys.list_add(@input, 'foo'); +SET @input := NULL; diff --git a/mysql-test/suite/sysschema/t/fn_list_drop.test b/mysql-test/suite/sysschema/t/fn_list_drop.test new file mode 100644 index 00000000000..f7b0d16bb0a --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_list_drop.test @@ -0,0 +1,35 @@ +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.sql_mode_drop() function perfoms as expected + +# Remove from front of list +SELECT sys.list_drop('1,2,3,4,5', '1'); + +# Remove from middle of list +SELECT sys.list_drop('1,2,3,4,5', '3'); + +# Remove from end of list +SELECT sys.list_drop('1,2,3,4,5', '5'); + +# Make sure spaces are appropriately dealt with +SELECT sys.list_drop('1, 2, 3, 4, 5', '1'); +SELECT sys.list_drop('1, 2, 3, 4, 5', '3'); +SELECT sys.list_drop('1, 2, 3, 4, 5', '5'); + +# Should return an error with a NULL value to drop +--error 1138 +SELECT sys.list_drop('1,2,3,4,5', NULL); + +# Test the sql mode update use case +# save old mode to restore +SET @sqlmode := @@sql_mode; + +SELECT @@sql_mode; + +SET SESSION sql_mode = sys.list_drop(@@sql_mode, 'STRICT_TRANS_TABLES'); + +SELECT @@sql_mode; + +# restore old mode +SET SESSION sql_mode = @sqlmode; +SET @sqlmode := NULL; diff --git a/mysql-test/suite/sysschema/t/fn_ps_is_account_enabled.test b/mysql-test/suite/sysschema/t/fn_ps_is_account_enabled.test new file mode 100644 index 00000000000..c3bc7a25792 --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_ps_is_account_enabled.test @@ -0,0 +1,20 @@ +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.ps_is_account_enabled() function perfoms as expected + +# First test with the default "all enabled" for any random user +SELECT sys.ps_is_account_enabled('foo', 'bar'); + +# Now remove the wild card entry, and add some specific users to testx +DELETE FROM performance_schema.setup_actors; + +INSERT INTO performance_schema.setup_actors VALUES + ('test', 'test', '%', 'YES', 'NO'); + +# Now the random account should not be enabled +SELECT sys.ps_is_account_enabled('foo', 'bar'); + +# But the specified one should +SELECT sys.ps_is_account_enabled('test', 'test'); + +--source ../include/ps_setup_actors_cleanup.inc diff --git a/mysql-test/suite/sysschema/t/fn_ps_is_consumer_enabled.test b/mysql-test/suite/sysschema/t/fn_ps_is_consumer_enabled.test new file mode 100644 index 00000000000..e6e5fac0170 --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_ps_is_consumer_enabled.test @@ -0,0 +1,39 @@ +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.ps_is_consumer_enabled() function perfoms as expected + +# Should be disabled by default +UPDATE performance_schema.setup_consumers SET enabled = 'NO' WHERE name = 'events_stages_history'; +SELECT sys.ps_is_consumer_enabled('events_stages_history'); + +# Should be enabled by default +SELECT sys.ps_is_consumer_enabled('global_instrumentation'); + +# +# Check that hierarchy is properly reflected +# + +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'; + +# Should still be disabled, as events_stages_current not enabled +SELECT sys.ps_is_consumer_enabled('events_stages_history'); + +UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_stages_current'; + +# Now it should be enabled +SELECT sys.ps_is_consumer_enabled('events_stages_history'); + +# Toggling global_instrumentation should disable all other consumers +UPDATE performance_schema.setup_consumers SET enabled = 'NO' WHERE name = 'global_instrumentation'; + +SELECT sys.ps_is_consumer_enabled('thread_instrumentation'); +SELECT sys.ps_is_consumer_enabled('statements_digest'); +SELECT sys.ps_is_consumer_enabled('events_stages_current'); +SELECT sys.ps_is_consumer_enabled('events_stages_history'); +SELECT sys.ps_is_consumer_enabled('events_stages_history_long'); +SELECT sys.ps_is_consumer_enabled('events_statements_current'); +SELECT sys.ps_is_consumer_enabled('events_statements_history'); +SELECT sys.ps_is_consumer_enabled('events_statements_history_long'); + +--source ../include/ps_setup_consumers_cleanup.inc diff --git a/mysql-test/suite/sysschema/t/fn_ps_is_instrument_default_enabled.test b/mysql-test/suite/sysschema/t/fn_ps_is_instrument_default_enabled.test new file mode 100644 index 00000000000..a3eb67f1250 --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_ps_is_instrument_default_enabled.test @@ -0,0 +1,23 @@ +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.ps_is_instrument_default_enabled() function perfoms as expected + +# Should all be enabled +SELECT sys.ps_is_instrument_default_enabled('memory/performance_schema/internal_buffers'); +SELECT sys.ps_is_instrument_default_enabled('statement/sql/select'); +SELECT sys.ps_is_instrument_default_enabled('statement/sp/error'); +SELECT sys.ps_is_instrument_default_enabled('statement/com/Prepare'); +SELECT sys.ps_is_instrument_default_enabled('wait/io/file/sql/binlog'); +SELECT sys.ps_is_instrument_default_enabled('wait/io/table/sql/handler'); +SELECT sys.ps_is_instrument_default_enabled('wait/lock/table/sql/handler'); +SELECT sys.ps_is_instrument_default_enabled('idle'); + +# Should all be disabled +SELECT sys.ps_is_instrument_default_enabled('wait/synch/mutex/sql/LOCK_plugin'); +SELECT sys.ps_is_instrument_default_enabled('wait/synch/rwlock/sql/LOCK_grant'); +SELECT sys.ps_is_instrument_default_enabled('wait/synch/sxlock/innodb/btr_search_latch'); +SELECT sys.ps_is_instrument_default_enabled('wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond'); +SELECT sys.ps_is_instrument_default_enabled('stage/sql/end'); +SELECT sys.ps_is_instrument_default_enabled('transaction'); +SELECT sys.ps_is_instrument_default_enabled('wait/io/socket/sql/server_tcpip_socket'); +SELECT sys.ps_is_instrument_default_enabled('wait/lock/metadata/sql/mdl'); diff --git a/mysql-test/suite/sysschema/t/fn_ps_is_instrument_default_timed.test b/mysql-test/suite/sysschema/t/fn_ps_is_instrument_default_timed.test new file mode 100644 index 00000000000..1d53a96f1fa --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_ps_is_instrument_default_timed.test @@ -0,0 +1,23 @@ +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.ps_is_instrument_default_timed() function perfoms as expected + +# Should all be enabled +SELECT sys.ps_is_instrument_default_timed('wait/io/file/sql/binlog'); +SELECT sys.ps_is_instrument_default_timed('statement/sql/select'); +SELECT sys.ps_is_instrument_default_timed('statement/sp/error'); +SELECT sys.ps_is_instrument_default_timed('statement/com/Prepare'); +SELECT sys.ps_is_instrument_default_timed('wait/io/table/sql/handler'); +SELECT sys.ps_is_instrument_default_timed('wait/lock/table/sql/handler'); +SELECT sys.ps_is_instrument_default_timed('idle'); + +# Should all be disabled +SELECT sys.ps_is_instrument_default_timed('wait/synch/mutex/sql/LOCK_plugin'); +SELECT sys.ps_is_instrument_default_timed('wait/synch/rwlock/sql/LOCK_grant'); +SELECT sys.ps_is_instrument_default_timed('wait/synch/sxlock/innodb/btr_search_latch'); +SELECT sys.ps_is_instrument_default_timed('wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond'); +SELECT sys.ps_is_instrument_default_timed('stage/sql/end'); +SELECT sys.ps_is_instrument_default_timed('transaction'); +SELECT sys.ps_is_instrument_default_timed('wait/io/socket/sql/server_tcpip_socket'); +SELECT sys.ps_is_instrument_default_timed('memory/performance_schema/internal_buffers'); +SELECT sys.ps_is_instrument_default_timed('wait/lock/metadata/sql/mdl'); diff --git a/mysql-test/suite/sysschema/t/fn_ps_is_thread_instrumented.test b/mysql-test/suite/sysschema/t/fn_ps_is_thread_instrumented.test new file mode 100644 index 00000000000..0ee9bb50f2b --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_ps_is_thread_instrumented.test @@ -0,0 +1,18 @@ +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.ps_is_thread_instrumented() function perfoms as expected + +# Passing NULL/nothing should return NULL +SELECT sys.ps_is_thread_instrumented(NULL); + +# By default current thread should be instrumented +SELECT sys.ps_is_thread_instrumented(CONNECTION_ID()); + +# Now toggle our thread to off and re-check +UPDATE performance_schema.threads SET instrumented = 'NO' WHERE processlist_id = CONNECTION_ID(); +SELECT sys.ps_is_thread_instrumented(CONNECTION_ID()); + +# Try some huge number to check unknown connections +SELECT sys.ps_is_thread_instrumented(234623462376); + +--source ../include/ps_threads_cleanup.inc diff --git a/mysql-test/suite/sysschema/t/fn_ps_thread_account.test b/mysql-test/suite/sysschema/t/fn_ps_thread_account.test new file mode 100644 index 00000000000..77b6241add0 --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_ps_thread_account.test @@ -0,0 +1,11 @@ +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.ps_thread_account() function perfoms as expected + +# Passing NULL, or an unknown connection id, should return NULL +SELECT sys.ps_thread_account(NULL); +SELECT sys.ps_thread_account(234623462376); + + +# Check result +SELECT sys.ps_thread_account(sys.ps_thread_id(NULL)); diff --git a/mysql-test/suite/sysschema/t/fn_ps_thread_id.test b/mysql-test/suite/sysschema/t/fn_ps_thread_id.test new file mode 100644 index 00000000000..ca8b4fb437c --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_ps_thread_id.test @@ -0,0 +1,18 @@ +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.ps_thread_id() function perfoms as expected + +# Passing unknown connection id should return NULL +SELECT sys.ps_thread_id(234623462376); + +# Grab the thread_id for this connection directly from Performance Schema +let $MY_THREAD_ID=`SELECT thread_id FROM performance_schema.threads + WHERE PROCESSLIST_ID = connection_id()`; + +--disable_query_log ONCE +eval SET @ps_thread_id = $MY_THREAD_ID; + +SELECT sys.ps_thread_id(CONNECTION_ID()) = @ps_thread_id; + +# Passing NULL should also return the current connection thread id +SELECT sys.ps_thread_id(NULL) = @ps_thread_id; diff --git a/mysql-test/suite/sysschema/t/fn_ps_thread_trx_info.test b/mysql-test/suite/sysschema/t/fn_ps_thread_trx_info.test new file mode 100644 index 00000000000..ddf462473ca --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_ps_thread_trx_info.test @@ -0,0 +1,91 @@ +# Tests for sys schema +# Verify the sys.ps_thread_trx_info() function perfoms as expected + +-- source include/not_embedded.inc +# Performance schema tracks prepared statements separately, and does not +# yet have a summary view that we can use for this view. +# Until then, we disable this test with --ps-protocol +-- source include/no_protocol.inc + +# Passing unknown connection id should return NULL +SELECT sys.ps_thread_trx_info(234623462376); + +# Make sure current thread returns a valid JSON object +SELECT JSON_VALID(sys.ps_thread_trx_info(sys.ps_thread_id(NULL))); + +# Dummy up some transactions and inspect +CREATE DATABASE trx; +CREATE TABLE trx.info (id INT PRIMARY KEY, info VARCHAR(20)); + +--connect(con1,localhost,root,,) +connection con1; +let $NEW_TRX_ID=`SELECT thread_id FROM performance_schema.threads + WHERE PROCESSLIST_ID = connection_id()`; + +USE trx; +START TRANSACTION; +INSERT INTO info VALUES (1, 'foo'); +COMMIT; +START TRANSACTION; +INSERT INTO info VALUES (2, 'bar'); +COMMIT; + +connection default; +--disable_query_log ONCE +eval SET @ps_thread_id = $NEW_TRX_ID; + +# Get the JSON dump of the transaction info +SET @json_doc := sys.ps_thread_trx_info(@ps_thread_id); + +# JSON should be valid +SELECT JSON_VALID(@json_doc); + +# Should have two transactions in the array +SELECT JSON_LENGTH(@json_doc); + +# Expected keys are returned for first transaction details +SELECT JSON_KEYS(JSON_EXTRACT(@json_doc, '$[0]')); + +# Expected values are returned for the transaction details +SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$[0].time'); +SELECT JSON_CONTAINS(@json_doc, '"COMMITTED"', '$[0].state'); +SELECT JSON_CONTAINS(@json_doc, '"READ WRITE"', '$[0].mode'); +SELECT JSON_CONTAINS(@json_doc, '"NO"', '$[0].autocommitted'); +SELECT JSON_CONTAINS(@json_doc, '"AUTOMATIC"', '$[0].gtid'); +SELECT JSON_CONTAINS(@json_doc, '"REPEATABLE READ"', '$[0].isolation'); + +# Expected keys are returned for first transaction statements_executed details +SELECT JSON_KEYS(JSON_EXTRACT(@json_doc, '$[0].statements_executed[0]')); + +# Confirm statement details values +SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$[0].statements_executed[0].time'); +SELECT JSON_CONTAINS(@json_doc, '"INSERT INTO info VALUES (1, \'foo\')"', '$[0].statements_executed[0].sql_text'); +SELECT JSON_CONTAINS(@json_doc, '"trx"', '$[0].statements_executed[0].schema'); +SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].rows_examined'); +SELECT JSON_CONTAINS(@json_doc, '1', '$[0].statements_executed[0].rows_affected'); +SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].rows_sent'); +SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].tmp_tables'); +SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].tmp_disk_tables'); +SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].sort_rows'); +SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].sort_merge_passes'); + +# Second statement in transaction should be a COMMIT +SELECT JSON_CONTAINS(@json_doc, '"COMMIT"', '$[0].statements_executed[1].sql_text'); + +# Simulate a truncated set of output by lowering the @sys.ps_thread_trx_info.max_length user variable +# This also tests the user variable works appropriately, incidentally + +SET @sys.ps_thread_trx_info.max_length = 100; + +# Should return an error JSON object +--replace_regex /Row 1[1-2] was/Row 1X was/ +SELECT sys.ps_thread_trx_info(@ps_thread_id); + +# Setting the user variable back to NULL should reset to 65535 from sys_config, and no truncation +SET @sys.ps_thread_trx_info.max_length = NULL; +SELECT JSON_VALID(sys.ps_thread_trx_info(@ps_thread_id)); + +# Clean up + +disconnect con1; +DROP DATABASE trx; diff --git a/mysql-test/suite/sysschema/t/fn_quote_identifier.test b/mysql-test/suite/sysschema/t/fn_quote_identifier.test new file mode 100644 index 00000000000..0beb7fb2aa4 --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_quote_identifier.test @@ -0,0 +1,25 @@ +########### suite/sysschema/t/fn_quote_identifier.test ############# +# # +# Testing of of the sys.quote_identifier() function # +# # +# Creation: # +# 2016-05-23 jkrogh Implement this test as part of bug 22011361 # +# # +#################################################################### + +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.quote_identifer() function perfoms as expected + +# Passing NULL should return NULL +SELECT sys.quote_identifier(NULL); + +# Passing a simple string +SELECT sys.quote_identifier('abc'); + +# Passing a string containing backticks (`) +SELECT sys.quote_identifier('ab`c'); +SELECT sys.quote_identifier('ab``c'); +SELECT sys.quote_identifier('ab```c'); +SELECT sys.quote_identifier('a`b`c'); +SELECT sys.quote_identifier('a`b``c'); diff --git a/mysql-test/suite/sysschema/t/fn_sys_get_config.test b/mysql-test/suite/sysschema/t/fn_sys_get_config.test new file mode 100644 index 00000000000..1bdf87f1e05 --- /dev/null +++ b/mysql-test/suite/sysschema/t/fn_sys_get_config.test @@ -0,0 +1,13 @@ +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.sys_get_config() function perfoms as expected + +# Check a known parameter +SET @sys.statement_truncate_len := IFNULL(@sys.statement_truncate_len, sys.sys_get_config('statement_truncate_len', 128)); + +SELECT @sys.statement_truncate_len; + +# Check an unknown parameter +SET @sys.foo := IFNULL(@sys.foo, sys.sys_get_config('foo', 'foobar')); + +SELECT @sys.foo; diff --git a/mysql-test/suite/sysschema/t/mysqldump.test b/mysql-test/suite/sysschema/t/mysqldump.test new file mode 100644 index 00000000000..35abc704117 --- /dev/null +++ b/mysql-test/suite/sysschema/t/mysqldump.test @@ -0,0 +1,35 @@ +-- source include/not_embedded.inc +-- source include/have_innodb.inc +# Dump all databases +--exec $MYSQL_DUMP -uroot --all-databases > $MYSQLTEST_VARDIR/tmp/bug20902791.sql + +# Save a copy of the user/tables_priv, to restore later +# Otherwise the final mysql_upgrade will REPLACE and update timestamps etc. +CREATE TEMPORARY TABLE tmp_global_priv AS SELECT * FROM mysql.global_priv; +CREATE TEMPORARY TABLE tmp_tables_priv AS SELECT * FROM mysql.tables_priv; + +# Remove the sys schema +DROP DATABASE sys; + +# Reload the dump +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug20902791.sql + +# The sys schema should not exist +SHOW DATABASES; + +# Finally reload the sys schema to return to normal +--exec $MYSQL_UPGRADE --skip-verbose --force 2>&1 +SHOW DATABASES; + +# Restore the saved privileges +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; +--let $MYSQLD_DATADIR= `select @@datadir` +--remove_file $MYSQLD_DATADIR/mysql_upgrade_info diff --git a/mysql-test/suite/sysschema/t/pr_create_synonym_db.test b/mysql-test/suite/sysschema/t/pr_create_synonym_db.test new file mode 100644 index 00000000000..30c6f502e30 --- /dev/null +++ b/mysql-test/suite/sysschema/t/pr_create_synonym_db.test @@ -0,0 +1,69 @@ +########### suite/sysschema/t/pr_create_synonym_db.test ############# +# # +# Testing of of the sys.pr_create_synonym_db() procedure # +# # +# Creation: # +# 2016-05-23 jkrogh Implement this test as part of bug 22011361 # +# # +##################################################################### + +-- source include/not_embedded.inc +# Create a couple of tables and a view +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; + +# Make synonym db of test into test1 +CALL sys.create_synonym_db('test', 'test1'); +# Verify there's one view in test1 per table and view in test +SELECT TABLE_NAME, SECURITY_TYPE FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'test1' ORDER BY TABLE_NAME; +# Try again (should fail) +-- error ER_SIGNAL_EXCEPTION +CALL sys.create_synonym_db('test', 'test1'); + +# Try to make the synonym db for an existing empty schema +CREATE SCHEMA test2; +-- error ER_SIGNAL_EXCEPTION +CALL sys.create_synonym_db('test', 'test2'); +# Ensure test2 is still empty +SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test2'; + +# Use a reserved identifer, Bug #22011361 +CALL sys.create_synonym_db('test', 'is'); +# Verify there's one view in is per table and view in test +SELECT TABLE_NAME, SECURITY_TYPE FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'is' ORDER BY TABLE_NAME; + +# Copy the is schema to i`s schema: +CALL sys.create_synonym_db('is', 'i`s'); +# Verify there's one view in i`s per table and view in is +SELECT TABLE_NAME, SECURITY_TYPE FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'i`s' ORDER BY TABLE_NAME; + +# Clean up +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`; + +--echo # +--echo # MDEV-28342: sys.create_synonym_db fails +--echo # when a temporary table masks a base table +--echo # + +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); +--error ER_SIGNAL_EXCEPTION +call sys.create_synonym_db('db','db_copy'); + +drop database db; +drop database db_copy; diff --git a/mysql-test/suite/sysschema/t/pr_diagnostics.test b/mysql-test/suite/sysschema/t/pr_diagnostics.test new file mode 100644 index 00000000000..40ea90005a9 --- /dev/null +++ b/mysql-test/suite/sysschema/t/pr_diagnostics.test @@ -0,0 +1,35 @@ +########### suite/sysschema/t/pr_diagnostics.test ############# +# # +# Testing of of the sys.diagnostics()) procedure # +# # +# Creation: # +# 2015-07-28 jkrogh Implement this test as part of # +# WL#7804 REPORT FOR SUPPORT # +# # +############################################################### + +-- source include/not_embedded.inc + +# Sanity check that the procedure completes two iterations with full debug, +# raw output, and Information Schema table outputs without generating +# any warnings. + +--disable_result_log +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'; +--enable_result_log + +# Check input variable validation +-- error S45000 +CALL sys.diagnostics(0, 0, 'full'); +-- error S45000 +CALL sys.diagnostics(2, 0, 'full'); +-- error S45000 +CALL sys.diagnostics(1, 2, 'full'); diff --git a/mysql-test/suite/sysschema/t/pr_execute_prepared_stmt.test b/mysql-test/suite/sysschema/t/pr_execute_prepared_stmt.test new file mode 100644 index 00000000000..17a54aadea6 --- /dev/null +++ b/mysql-test/suite/sysschema/t/pr_execute_prepared_stmt.test @@ -0,0 +1,25 @@ +-- source include/not_embedded.inc + +# Do some valid changes, ensure they are successful +SET @sql := "CREATE TABLE test.t1 (i INT) Engine=MEMORY"; +CALL sys.execute_prepared_stmt(@sql); + +SHOW CREATE TABLE test.t1; + +SET @sql := CONCAT('INSERT INTO test.t1 VALUES (', 1, ')'); +CALL sys.execute_prepared_stmt(@sql); + +SELECT * FROM test.t1; + +SET @sql := "DROP TABLE test.t1"; +CALL sys.execute_prepared_stmt(@sql); + +SHOW TABLES; + +SET @sql = NULL; + +# Invalid inputs +-- error S45000 +CALL sys.execute_prepared_stmt(NULL); +-- error S45000 +CALL sys.execute_prepared_stmt('foo'); diff --git a/mysql-test/suite/sysschema/t/pr_ps_setup_reset_to_default.test b/mysql-test/suite/sysschema/t/pr_ps_setup_reset_to_default.test new file mode 100644 index 00000000000..1622957f8dd --- /dev/null +++ b/mysql-test/suite/sysschema/t/pr_ps_setup_reset_to_default.test @@ -0,0 +1,15 @@ +########### suite/sysschema/t/pr_ps_setup_reset_to_default.test ############# +# # +# Testing of of the sys.ps_setup_reset_to_default() procedure # +# # +# Creation: # +# 2015-08-14 jkrogh Implement this test as part of Bug 21550271/Bug77927 # +# # +############################################################################# + +-- source include/not_embedded.inc + +# Currently just a sanity check +CALL sys.ps_setup_reset_to_default(TRUE); + +-- source ../include/ps_setup_reset_to_default_cleanup.inc diff --git a/mysql-test/suite/sysschema/t/pr_ps_setup_show_disabled.test b/mysql-test/suite/sysschema/t/pr_ps_setup_show_disabled.test new file mode 100644 index 00000000000..8d6f03fa262 --- /dev/null +++ b/mysql-test/suite/sysschema/t/pr_ps_setup_show_disabled.test @@ -0,0 +1,37 @@ +-- source include/not_embedded.inc +-- source include/have_innodb.inc + +# Add to users to the setup_actors table: one enabled, and one disabled +INSERT INTO performance_schema.setup_actors +VALUES ('localhost', 'foo', '%', 'YES', 'YES'), + ('localhost', 'bar', '%', 'NO', 'NO'); + +# Disable a few instruments +UPDATE performance_schema.setup_instruments + SET ENABLED = 'NO' + WHERE NAME LIKE 'stage/innodb/%' + OR NAME LIKE 'statement/com/%' + OR NAME = 'idle'; + +# Disable the history_long consumers: +UPDATE performance_schema.setup_consumers + SET ENABLED = 'NO' + WHERE NAME LIKE '%\_history\_long'; + +# Disable some of the background threads: +UPDATE performance_schema.threads SET INSTRUMENTED = 'NO' WHERE NAME LIKE 'thread/innodb/srv\_%'; + +# Show limited info (no thread or instrument info) +CALL sys.ps_setup_show_disabled(FALSE, FALSE); + +# Should show instrument data, but not thread info +CALL sys.ps_setup_show_disabled(TRUE, FALSE); + +# Should show thread info, but no instrument data +CALL sys.ps_setup_show_disabled(FALSE, TRUE); + +# Should show all info +CALL sys.ps_setup_show_disabled(TRUE, TRUE); + +# Clean up +-- source ../include/ps_setup_reset_to_default_cleanup.inc diff --git a/mysql-test/suite/sysschema/t/pr_ps_setup_show_disabled_consumers.test b/mysql-test/suite/sysschema/t/pr_ps_setup_show_disabled_consumers.test new file mode 100644 index 00000000000..f362784b665 --- /dev/null +++ b/mysql-test/suite/sysschema/t/pr_ps_setup_show_disabled_consumers.test @@ -0,0 +1,11 @@ +-- source include/not_embedded.inc + +# Disable the history_long consumers: +UPDATE performance_schema.setup_consumers + SET ENABLED = 'NO' + WHERE NAME LIKE '%\_history\_long'; + +CALL sys.ps_setup_show_disabled_consumers(); + +# Clean up +-- source ../include/ps_setup_reset_to_default_cleanup.inc diff --git a/mysql-test/suite/sysschema/t/pr_ps_setup_show_disabled_instruments.test b/mysql-test/suite/sysschema/t/pr_ps_setup_show_disabled_instruments.test new file mode 100644 index 00000000000..4ea78a154be --- /dev/null +++ b/mysql-test/suite/sysschema/t/pr_ps_setup_show_disabled_instruments.test @@ -0,0 +1,14 @@ +-- source include/not_embedded.inc +-- source include/have_innodb.inc + +# Disable a few instruments +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(); + +# Clean up +-- source ../include/ps_setup_reset_to_default_cleanup.inc diff --git a/mysql-test/suite/sysschema/t/pr_ps_setup_show_enabled.test b/mysql-test/suite/sysschema/t/pr_ps_setup_show_enabled.test new file mode 100644 index 00000000000..b8ef622b2e9 --- /dev/null +++ b/mysql-test/suite/sysschema/t/pr_ps_setup_show_enabled.test @@ -0,0 +1,44 @@ +-- source include/not_embedded.inc +-- source include/have_innodb.inc + +# Add to users to the setup_actors table: one enabled, and one disabled +INSERT INTO performance_schema.setup_actors +VALUES ('localhost', 'foo', '%', 'YES', 'YES'), + ('localhost', 'bar', '%', 'NO', 'NO'); + +# Disable all but a few instruments (reduces output as well) +# It is not possible to disable memory/performance_schema/% +# so don't even try. +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'; + +# Disable the history_long consumers: +UPDATE performance_schema.setup_consumers + SET ENABLED = 'NO' + WHERE NAME LIKE '%\_history\_long'; + +# Disable some of the background threads (including those that differ between unix like and Windows systems): +UPDATE performance_schema.threads + SET INSTRUMENTED = 'NO' + WHERE NAME LIKE 'thread/innodb/srv\_%' + OR NAME LIKE '%con\_%' + OR NAME LIKE '%signal_handler%'; + +# Show limited info (no thread or instrument info) +CALL sys.ps_setup_show_enabled(FALSE, FALSE); + +# Should show instrument data, but not thread info +CALL sys.ps_setup_show_enabled(TRUE, FALSE); + +# Should show thread info, but no instrument data +CALL sys.ps_setup_show_enabled(FALSE, TRUE); + +# Should show all info +CALL sys.ps_setup_show_enabled(TRUE, TRUE); + +# Clean up +-- source ../include/ps_setup_reset_to_default_cleanup.inc diff --git a/mysql-test/suite/sysschema/t/pr_ps_setup_show_enabled_consumers.test b/mysql-test/suite/sysschema/t/pr_ps_setup_show_enabled_consumers.test new file mode 100644 index 00000000000..62b4d8081d3 --- /dev/null +++ b/mysql-test/suite/sysschema/t/pr_ps_setup_show_enabled_consumers.test @@ -0,0 +1,11 @@ +-- source include/not_embedded.inc + +# Disable the history_long consumers: +UPDATE performance_schema.setup_consumers + SET ENABLED = 'NO' + WHERE NAME LIKE '%\_history\_long'; + +CALL sys.ps_setup_show_enabled_consumers(); + +# Clean up +-- source ../include/ps_setup_reset_to_default_cleanup.inc diff --git a/mysql-test/suite/sysschema/t/pr_ps_setup_show_enabled_instruments.test b/mysql-test/suite/sysschema/t/pr_ps_setup_show_enabled_instruments.test new file mode 100644 index 00000000000..507741f4d83 --- /dev/null +++ b/mysql-test/suite/sysschema/t/pr_ps_setup_show_enabled_instruments.test @@ -0,0 +1,18 @@ +-- source include/not_embedded.inc +-- source include/have_innodb.inc + + +# Disable all but a few instruments (reduces output as well) +# It is not possible to disable memory/performance_schema/% +# so don't even try. +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(); + +# Clean up +-- source ../include/ps_setup_reset_to_default_cleanup.inc diff --git a/mysql-test/suite/sysschema/t/pr_ps_trace_statement_digest.test b/mysql-test/suite/sysschema/t/pr_ps_trace_statement_digest.test new file mode 100644 index 00000000000..a569210df8f --- /dev/null +++ b/mysql-test/suite/sysschema/t/pr_ps_trace_statement_digest.test @@ -0,0 +1,65 @@ +########### suite/sysschema/t/pr_ps_trace_statement_digest.test ############# +# # +# Testing of of the sys.ps_trace_statement_digest() procedure # +# # +# Creation: # +# 2016-06-21 jkrogh Implement this test as part of # +# Bug 23621189 PS_TRACE_STATEMENT_DIGEST FAILS AT EXPLAIN # +# # +############################################################################# + +-- source include/not_embedded.inc +# The ps_trace_statement_digest does not work with prepared statements +# So disable this test with --ps-protocol +-- source include/no_protocol.inc + +use test; + +# Get the thread id of this thread +# Store it in a user variable as otherwise repeated calls to sys.ps_thread_id() +# will keep changing performance_schema.events_statements_history +SET @threadid = sys.ps_thread_id(NULL); + +# Create a table +CREATE TABLE t1 (id INT PRIMARY KEY, val int) ENGINE=MEMORY; + +# Get digest of an INSERT statement with a qualified table name +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)'); + +# Get digest of an SELECT statement using the default schema +SELECT * FROM t1; +SET @digest.select = (SELECT DIGEST FROM performance_schema.events_statements_history WHERE THREAD_ID = @threadid AND SQL_TEXT LIKE 'SELECT * FROM t1'); + +# Get digets of a SHOW statement (doesn't support EXPLAIN) +SHOW CREATE TABLE test.t1; +SET @digest.show = (SELECT DIGEST FROM performance_schema.events_statements_history WHERE THREAD_ID = @threadid AND SQL_TEXT LIKE 'SHOW CREATE TABLE test.t1'); + +# Don't execute ps_trace_statement_digest() in the same schema as the queries +# to monitor - to ensure we handle queries using the default schema. +CREATE SCHEMA test_sys; +use test_sys; + +# Only do sanity checks - no error should occur, but the actual output is non-deterministic +--disable_result_log +# Regular EXPLAINable SELECT with a qualified table name +CALL sys.ps_trace_statement_digest(@digest.insert, 0.5, 0.1, FALSE, FALSE); +# Table in query is not qualified and is not in the current default schema +CALL sys.ps_trace_statement_digest(@digest.select, 0.5, 0.1, FALSE, FALSE); +# SHOW queries doesn't work with EXPLAIN +CALL sys.ps_trace_statement_digest(@digest.show , 0.5, 0.1, FALSE, FALSE); +# Test that finding no queries works - the TRUE argument resets the P_S tables +# used in ps_trace_statement_digest() +CALL sys.ps_trace_statement_digest(@digest.insert, 0.5, 0.1, TRUE , FALSE); +--enable_result_log + + + +# Clean up +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/t/pr_statement_performance_analyzer.test b/mysql-test/suite/sysschema/t/pr_statement_performance_analyzer.test new file mode 100644 index 00000000000..b8932381e52 --- /dev/null +++ b/mysql-test/suite/sysschema/t/pr_statement_performance_analyzer.test @@ -0,0 +1,298 @@ +########### suite/sysschema/t/pr_statement_performance_analyzer.test ############# +# # +# Testing of of the sys.statement_performance_analyzer() procedure # +# # +# Creation: # +# 2015-07-28 jkrogh Implement this test as part of # +# WL#7804 REPORT FOR SUPPORT # +# # +################################################################################## + +-- source include/not_embedded.inc +# Performance schema tracks prepared statements separately, and does not +# yet have a summary view that we can use for this view. +# Until then, we disable this test with --ps-protocol +-- source include/no_protocol.inc +# Bug #23290879 - For reasons unknown to man this test fails randomly only on Windows +#-- source include/not_windows.inc + +use test; +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TEMPORARY TABLE IF EXISTS tmp_digests_ini; +DROP VIEW IF EXISTS view_digests; +--enable_warnings + +# Create a table +CREATE TABLE t1 (id INT PRIMARY KEY, val int); + +# Create a new connection to make the actual changes +# Create the connection now to ensure queries like "SELECT @@`version_comment` LIMIT 1" are excluded +connect (con1,localhost,root,,); +connection con1; +use test; +--let $con1_thread_id= `SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = CONNECTION_ID()` + +# Disable instrumentation for all other threads other than the one that will +# my monitored for this test ($con1_thread_id) +connection default; +--disable_result_log +--replace_result $con1_thread_id CON1_THREAD_ID +eval 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); +--enable_result_log + +# Start with some initial queries +# Don't rely on the digests being constant across MySQL releases and versions, so find the +# digest for each of the three queries by getting the last event from performance_schema.events_statements_current +# for the con1 connection. +connection con1; +INSERT INTO t1 VALUES (1, 0); +connection default; +--let $wait_condition= SELECT SUBSTRING(SQL_TEXT, 1, 7) = 'INSERT ' FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id AND DIGEST IS NOT NULL +--source include/wait_condition.inc +--let $digest_insert= `SELECT DIGEST FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1` +--let $query_insert= `SELECT sys.format_statement(DIGEST_TEXT) FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1` +connection con1; +UPDATE t1 SET val = 1 WHERE id = 1; +connection default; +--let $wait_condition= SELECT SUBSTRING(SQL_TEXT, 1, 7) = 'UPDATE ' FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id AND DIGEST IS NOT NULL +--source include/wait_condition.inc +--let $digest_update= `SELECT DIGEST FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1` +--let $query_update= `SELECT sys.format_statement(DIGEST_TEXT) FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1` +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; +connection default; +--let $wait_condition= SELECT SUBSTRING(SQL_TEXT, 1, 7) = 'SELECT ' FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id AND DIGEST IS NOT NULL +--source include/wait_condition.inc +--let $digest_select= `SELECT DIGEST FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1` +--let $query_select= `SELECT sys.format_statement(DIGEST_TEXT) FROM performance_schema.events_statements_current WHERE THREAD_ID = $con1_thread_id ORDER BY EVENT_ID DESC LIMIT 1` + +# Enable to debug if some digests are not found +# --output /tmp/digest +# --eval SELECT '$digest_insert' AS DigestInsert, '$digest_update' AS DigestUpdate, '$digest_select' AS DigestSelect +# --output /tmp/digest_text +# --eval SELECT '$query_insert' AS DigestInsert, '$query_update' AS DigestUpdate, '$query_select' AS DigestSelect +# --output /tmp/ps_history +# SELECT THREAD_ID, EVENT_ID, END_EVENT_ID, DIGEST, SQL_TEXT FROM performance_schema.events_statements_history_long ORDER BY EVENT_ID; + +# Start collecting data +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); + +# Make sure the last_seen times will be different from the SELECT statement's first_seen. +DO SLEEP(1.2); + +# Make some more changes +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; +disconnect con1; + +# Make the second collection of data and create the output +connection default; +# Make sure all of the queries executing in con1 has been recorded in performance_schema.events_statements_summary_by_digest +# before continuing with the actual tests of pr_statement_performance_analyzer() +--let $wait_condition= SELECT COUNT_STAR = 2 FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST = '$digest_select' +--source include/wait_condition.inc + +# Get all values to be used in replacements now to minimize the risk of the +# values disappearing out of the performance_schema tables +--let $o_upd_total_latency= `SELECT total_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'` +--let $o_upd_max_latency= `SELECT max_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'` +--let $o_upd_avg_latency= `SELECT avg_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'` +--let $o_upd_first_seen= `SELECT first_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'` +--let $o_upd_last_seen= `SELECT last_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'` +--let $o_sel_total_latency= `SELECT total_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'` +--let $o_sel_max_latency= `SELECT max_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'` +--let $o_sel_avg_latency= `SELECT avg_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'` +--let $o_sel_first_seen= `SELECT first_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'` +--let $o_sel_last_seen= `SELECT last_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'` +--let $o_ins_total_latency= `SELECT total_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'` +--let $o_ins_max_latency= `SELECT max_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'` +--let $o_ins_avg_latency= `SELECT avg_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'` +--let $o_ins_first_seen= `SELECT first_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'` +--let $o_ins_last_seen= `SELECT last_seen FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'` +--let $o_upd_lock_latency= `SELECT lock_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_update'` +--let $o_sel_lock_latency= `SELECT lock_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_select'` +--let $o_ins_lock_latency= `SELECT lock_latency FROM sys.statement_analysis WHERE db = 'test' AND digest = '$digest_insert'` +--let $d_upd_total_latency= `SELECT sys.format_time(TIMER_WAIT) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_update' ORDER BY EVENT_ID DESC LIMIT 1` +--let $d_sel_total_latency= `SELECT sys.format_time(TIMER_WAIT) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_select' ORDER BY EVENT_ID DESC LIMIT 1` +--let $d_ins_total_latency= `SELECT sys.format_time(TIMER_WAIT) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_insert' ORDER BY EVENT_ID DESC LIMIT 1` +--let $d_upd_tock_latency= `SELECT sys.format_time(LOCK_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_update' ORDER BY EVENT_ID DESC LIMIT 1` +--let $d_sel_tock_latency= `SELECT sys.format_time(LOCK_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_select' ORDER BY EVENT_ID DESC LIMIT 1` +--let $d_ins_tock_latency= `SELECT sys.format_time(LOCK_TIME) FROM performance_schema.events_statements_history_long WHERE CURRENT_SCHEMA = 'test' AND DIGEST = '$digest_insert' ORDER BY EVENT_ID DESC LIMIT 1` + +CALL sys.statement_performance_analyzer('snapshot', NULL, NULL); + +# Do a sanity check to ensure we are assuming the queries has the digests they have and that there is nothing else in the events_statements_summary_by_digest than there actually is. +--replace_result $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT +--sorted_result +SELECT DIGEST, COUNT_STAR FROM performance_schema.events_statements_summary_by_digest; + +# with_runtimes_in_95th_percentile +# It is unknown what the result will be since the execution times for each query are unknown +# So just check that no warning or error occurs +--disable_result_log +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'); +--enable_result_log + +# analysis - as there's no control of the various latencies, it may be the same for two or more of the queries. +# So replace_result cannot be used to give it a unique value. Instead just use LATENCY for all rows. +--replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $query_update QUERY_UPDATE $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT $o_upd_total_latency LATENCY $o_upd_max_latency LATENCY $o_upd_avg_latency LATENCY $o_upd_lock_latency LATENCY $o_upd_first_seen FIRST_SEEN $o_upd_last_seen LAST_SEEN $o_sel_total_latency LATENCY $o_sel_max_latency LATENCY $o_sel_avg_latency LATENCY $o_sel_lock_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN $o_ins_total_latency LATENCY $o_ins_max_latency LATENCY $o_ins_avg_latency LATENCY $o_ins_lock_latency LATENCY $o_ins_first_seen FIRST_SEEN $o_ins_last_seen LAST_SEEN +--sorted_result +CALL sys.statement_performance_analyzer('overall', NULL, 'analysis'); + +--replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $query_update QUERY_UPDATE $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT $d_upd_total_latency LATENCY $o_upd_max_latency LATENCY $o_upd_first_seen FIRST_SEEN $o_upd_last_seen LAST_SEEN $d_upd_tock_latency LATENCY $d_sel_total_latency LATENCY $o_sel_max_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN $d_sel_tock_latency LATENCY $d_ins_total_latency LATENCY $o_ins_max_latency LATENCY $o_ins_first_seen FIRST_SEEN $o_ins_last_seen LAST_SEEN $d_ins_tock_latency LATENCY +--sorted_result +CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'analysis'); + +# Should give an empty result except for the banner generated by the procedure +CALL sys.statement_performance_analyzer('overall', NULL, 'with_errors_or_warnings'); +CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_errors_or_warnings'); + +--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $o_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN +CALL sys.statement_performance_analyzer('overall', NULL, 'with_full_table_scans'); +--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $d_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN +CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_full_table_scans'); + +--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $o_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN +CALL sys.statement_performance_analyzer('overall', NULL, 'with_sorting'); +--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $d_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN +CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_sorting'); + +--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $o_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN +CALL sys.statement_performance_analyzer('overall', NULL, 'with_temp_tables'); +--replace_result $query_select QUERY_SELECT $digest_select DIGEST_SELECT $d_sel_total_latency LATENCY $o_sel_first_seen FIRST_SEEN $o_sel_last_seen LAST_SEEN +CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'with_temp_tables'); + +# Try a custom view +# Sort by the query, then we know the order will be INSERT, SELECT, UPDATE +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'; +--replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $query_update QUERY_UPDATE $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT $o_upd_total_latency LATENCY $o_upd_avg_latency LATENCY $o_sel_total_latency LATENCY $o_sel_avg_latency LATENCY $o_ins_total_latency LATENCY $o_ins_avg_latency LATENCY +CALL sys.statement_performance_analyzer('overall', NULL, 'custom'); +--replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $query_update QUERY_UPDATE $digest_insert DIGEST_INSERT $digest_update DIGEST_UPDATE $digest_select DIGEST_SELECT $d_upd_total_latency LATENCY $d_sel_total_latency LATENCY $d_ins_total_latency LATENCY +CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'custom'); + +# Verify that setting a limit works +SET @sys.statement_performance_analyzer.limit = 2; +--replace_result $query_insert QUERY_INSERT $query_select QUERY_SELECT $digest_insert DIGEST_INSERT $digest_select DIGEST_SELECT $o_ins_total_latency LATENCY $o_ins_avg_latency LATENCY $o_sel_total_latency LATENCY $o_sel_avg_latency LATENCY +CALL sys.statement_performance_analyzer('overall', NULL, 'custom'); + +# Test for error conditions - some of the errors depend on the sql_mode so set it explicitly +# Which sql_modes are deprecated depends on the release, so disable warnings while setting the sql_mode + + +# Use non-strict sql_mode - keep NO_AUTO_CREATE_USER as this sql_mode will be mandatory in the future: +SET SESSION sql_mode = 'NO_AUTO_CREATE_USER'; +# Ask for a non-supported action - since strict more is not used, this will give the error: ERROR 1644 (45000): Unknown action: '' +# Note: the action passed to the procedure is actually lost because it's truncated. +--error ER_SIGNAL_EXCEPTION +CALL sys.statement_performance_analyzer('do magic', NULL, NULL); + + +# Use 5.7.9+ default: +--disable_warnings +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'; +--enable_warnings +# Ask for a non-supported action - since strict mode is used, this will give the error: ERROR 1265 (01000): Data truncated for column 'in_action' at row 1 +--error 1265 +CALL sys.statement_performance_analyzer('do magic', NULL, NULL); +# Try to create reserved table name +-- error S45000 +CALL sys.statement_performance_analyzer('create_tmp', 'sys.tmp_digests', NULL); +-- error S45000 +CALL sys.statement_performance_analyzer('create_tmp', 'sys.tmp_digests_delta', NULL); +-- error S45000 +CALL sys.statement_performance_analyzer('create_tmp', 'tmp_digests', NULL); +# This should succeed +CALL sys.statement_performance_analyzer('create_tmp', 'test.tmp_digests', NULL); +CREATE TABLE test.tmp_unsupported LIKE test.tmp_digests_ini; +# Try to create a temporary table that already exists +-- error S45000 +CALL sys.statement_performance_analyzer('create_tmp', 'test.tmp_digests_ini', NULL); +# Try to create a base table that already exists +-- error S45000 +CALL sys.statement_performance_analyzer('create_table', 'test.tmp_digests_ini', NULL); +-- error S45000 +CALL sys.statement_performance_analyzer('create_table', 'test.tmp_unsupported', NULL); +# Verify that the sanity check for the input table e.g. for saving snapshots works +ALTER TABLE test.tmp_unsupported ADD COLUMN myvar int DEFAULT 0; +-- error S45000 +CALL sys.statement_performance_analyzer('save', 'test.tmp_unsupported', NULL); +# Try to create a snapshot or overall output with the wrong in_table +-- error S45000 +CALL sys.statement_performance_analyzer('snapshot', 'test.new_table', NULL); +-- error S45000 +CALL sys.statement_performance_analyzer('overall', 'test.new_table', 'analysis'); +# Try to create a delta output or save a snapshot specifying a non-existing table. +-- error S45000 +CALL sys.statement_performance_analyzer('delta', 'test.new_table', 'analysis'); +-- error S45000 +CALL sys.statement_performance_analyzer('save', 'test.new_table', NULL); +# Verify custom views doesn't work without the user variable set +SET @sys.statement_performance_analyzer.view = NULL; +DELETE FROM sys.sys_config WHERE variable = 'statement_performance_analyzer.view'; +-- error S45000 +CALL sys.statement_performance_analyzer('overall', NULL, 'custom'); +# Set the custom view to a regular table - should not work +SET @sys.statement_performance_analyzer.view = 'test.tmp_unsupported'; +-- error S45000 +CALL sys.statement_performance_analyzer('overall', NULL, 'custom'); + + +# Remove the temporary tables created by the procedure +# First ensure the tables actually exists (to avoid passing the test if the table names are changed) +CALL sys.table_exists('sys', 'tmp_digests', @exists); +SELECT @exists; +CALL sys.table_exists('sys', 'tmp_digests_delta', @exists); +SELECT @exists; +CALL sys.statement_performance_analyzer('cleanup', NULL, NULL); +# Verify the internal tables really were removed +-- error ER_BAD_TABLE_ERROR +DROP TEMPORARY TABLE sys.tmp_digests; +-- error ER_BAD_TABLE_ERROR +DROP TEMPORARY TABLE sys.tmp_digests_delta; + +# An attempt to create a delta view should fail now as there's no longer an existing snapshot. +-- error S45000 +CALL sys.statement_performance_analyzer('delta', 'test.tmp_digests_ini', 'analysis'); +# Ensure no delta table was created. +-- error ER_BAD_TABLE_ERROR +DROP TEMPORARY TABLE sys.tmp_digests_delta; + +# Try and use a table.db name > 129 +SET @identifier := REPEAT('a', 65); +-- error 1406 +CALL sys.statement_performance_analyzer('snapshot', CONCAT(@identifier, '.', @identifier), NULL); + +# Clean up +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; +--source ../include/ps_setup_consumers_cleanup.inc +--source ../include/ps_threads_cleanup.inc +--source ../include/sys_config_cleanup.inc diff --git a/mysql-test/suite/sysschema/t/pr_table_exists.test b/mysql-test/suite/sysschema/t/pr_table_exists.test new file mode 100644 index 00000000000..83e1dc0b9d9 --- /dev/null +++ b/mysql-test/suite/sysschema/t/pr_table_exists.test @@ -0,0 +1,69 @@ +-- source include/not_embedded.inc + + +# Create a base table and a view +CREATE TABLE t1 (id INT PRIMARY KEY); +# Verify the base table and view is supported +CALL sys.table_exists('test', 't1', @exists); +SELECT @exists; +DROP TABLE t1; + +CREATE view v_t1 AS SELECT 1; +CALL sys.table_exists('test', 'v_t1', @exists); +SELECT @exists; +DROP VIEW v_t1; + +CREATE TABLE tv (i int) with system versioning; +CALL sys.table_exists('test','tv',@exists); +SELECT @exists; +DROP TABLE tv; + +CREATE SEQUENCE s; +CALL sys.table_exists('test','s',@exists); +SELECT @exists; +DROP SEQUENCE s; + +# Replace the base table with a temporary table +CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY); +CALL sys.table_exists('test', 't1', @exists); +SELECT @exists; +DROP TEMPORARY TABLE t1; + +CALL sys.table_exists('information_schema', 'all_plugins', @exists); +SELECT @exists; + +# Try a non-existing table +CALL sys.table_exists('test', 't2', @exists); +SELECT @exists; + +# Try variables longer than expected +SET @identifier := REPEAT('a', 65); + +-- error 1406 +CALL sys.table_exists(@identifier, 't1', @exists); + +-- error 1406 +CALL sys.table_exists('test', @identifier, @exists); + +SET @identifier := NULL; + +--echo # +--echo # MDEV-28391: table_exists procedure fails with +--echo # Incorrect table name with backtick identifiers +--echo # +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; +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; +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; +--echo # 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; +DROP TABLE `ab``c`;
\ No newline at end of file diff --git a/mysql-test/suite/sysschema/t/t_sys_config.test b/mysql-test/suite/sysschema/t/t_sys_config.test new file mode 100644 index 00000000000..d78f8c9fd11 --- /dev/null +++ b/mysql-test/suite/sysschema/t/t_sys_config.test @@ -0,0 +1,18 @@ +-- source include/not_embedded.inc +# Tests for sys schema +# Verify the sys.sys_config table is as expected +SELECT COUNT(*) FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='sys'; +DESC sys.sys_config; + +SELECT variable, value, set_by FROM sys.sys_config ORDER BY 1; + +# Ensure the sys.sys_config_update_set_user trigger functions correctly +UPDATE sys.sys_config SET value = 128 WHERE variable = 'statement_truncate_len'; + +SELECT variable, value, set_by FROM sys.sys_config ORDER BY 1; + +INSERT INTO sys.sys_config (variable, value) VALUES ('foo', 'bar'); + +SELECT variable, value, set_by FROM sys.sys_config ORDER BY 1; + +--source ../include/sys_config_cleanup.inc diff --git a/mysql-test/suite/sysschema/t/v_host_summary.test b/mysql-test/suite/sysschema/t/v_host_summary.test new file mode 100644 index 00000000000..7cea0d5b111 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_host_summary.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.host_summary view + +# Ensure structure changes don't slip in +DESC sys.host_summary; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.host_summary; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$host_summary; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$host_summary; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_host_summary_by_file_io.test b/mysql-test/suite/sysschema/t/v_host_summary_by_file_io.test new file mode 100644 index 00000000000..f4b31509da7 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_host_summary_by_file_io.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.host_summary_by_file_io view + +# Ensure structure changes don't slip in +DESC sys.host_summary_by_file_io; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.host_summary_by_file_io; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$host_summary_by_file_io; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$host_summary_by_file_io; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_host_summary_by_file_io_type.test b/mysql-test/suite/sysschema/t/v_host_summary_by_file_io_type.test new file mode 100644 index 00000000000..9ae9475c478 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_host_summary_by_file_io_type.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.host_summary_by_file_io_type view + +# Ensure structure changes don't slip in +DESC sys.host_summary_by_file_io_type; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.host_summary_by_file_io_type; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$host_summary_by_file_io_type; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$host_summary_by_file_io_type; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_host_summary_by_stages.test b/mysql-test/suite/sysschema/t/v_host_summary_by_stages.test new file mode 100644 index 00000000000..37918012b16 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_host_summary_by_stages.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.host_summary_by_stages view + +# Ensure structure changes don't slip in +DESC sys.host_summary_by_stages; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.host_summary_by_stages; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$host_summary_by_stages; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$host_summary_by_stages; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_host_summary_by_statement_latency.test b/mysql-test/suite/sysschema/t/v_host_summary_by_statement_latency.test new file mode 100644 index 00000000000..29639e3314f --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_host_summary_by_statement_latency.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.host_summary_by_statement_latency view + +# Ensure structure changes don't slip in +DESC sys.host_summary_by_statement_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.host_summary_by_statement_latency; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$host_summary_by_statement_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$host_summary_by_statement_latency; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_host_summary_by_statement_type.test b/mysql-test/suite/sysschema/t/v_host_summary_by_statement_type.test new file mode 100644 index 00000000000..e9fd3981b98 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_host_summary_by_statement_type.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.host_summary_by_statement_type view + +# Ensure structure changes don't slip in +DESC sys.host_summary_by_statement_type; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.host_summary_by_statement_type; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$host_summary_by_statement_type; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$host_summary_by_statement_type; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_innodb_buffer_stats_by_schema.test b/mysql-test/suite/sysschema/t/v_innodb_buffer_stats_by_schema.test new file mode 100644 index 00000000000..f44b87d43ca --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_innodb_buffer_stats_by_schema.test @@ -0,0 +1,23 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +-- source include/have_innodb.inc + +# Tests for sys schema +# Verify the sys.innodb_buffer_stats_by_schema view + +# Ensure structure changes don't slip in +DESC sys.innodb_buffer_stats_by_schema; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.innodb_buffer_stats_by_schema; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$innodb_buffer_stats_by_schema; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$innodb_buffer_stats_by_schema; +--enable_result_log diff --git a/mysql-test/suite/sysschema/t/v_innodb_buffer_stats_by_table.test b/mysql-test/suite/sysschema/t/v_innodb_buffer_stats_by_table.test new file mode 100644 index 00000000000..d65c28ff96d --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_innodb_buffer_stats_by_table.test @@ -0,0 +1,23 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +-- source include/have_innodb.inc + +# Tests for sys schema +# Verify the sys.innodb_buffer_stats_by_table view + +# Ensure structure changes don't slip in +DESC sys.innodb_buffer_stats_by_table; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.innodb_buffer_stats_by_table; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$innodb_buffer_stats_by_table; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$innodb_buffer_stats_by_table; +--enable_result_log diff --git a/mysql-test/suite/sysschema/t/v_innodb_lock_waits.test b/mysql-test/suite/sysschema/t/v_innodb_lock_waits.test new file mode 100644 index 00000000000..b784587d2a6 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_innodb_lock_waits.test @@ -0,0 +1,41 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +-- source include/have_innodb.inc +# Tests for sys schema +# Verify the sys.innodb_lock_waits view + +# Ensure structure changes don't slip in +DESC sys.innodb_lock_waits; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.innodb_lock_waits; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$innodb_lock_waits; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$innodb_lock_waits; +--enable_result_log + + +--echo # +--echo # Start of 10.6 tests +--echo # + +--echo # +--echo # MDEV-26507 Assertion `tmp != ((long long) 0x8000000000000000LL)' failed in TIME_from_longlong_datetime_packed +--echo # + +SET SESSION sql_mode='ALLOW_INVALID_DATES'; +--disable_result_log +SELECT * FROM sys.x$innodb_lock_waits; +--enable_result_log +SET SESSION sql_mode=DEFAULT; + +--echo # +--echo # End of 10.6 tests +--echo # diff --git a/mysql-test/suite/sysschema/t/v_io_by_thread_by_latency.test b/mysql-test/suite/sysschema/t/v_io_by_thread_by_latency.test new file mode 100644 index 00000000000..38a1f7a206c --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_io_by_thread_by_latency.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.io_by_thread_by_latency view + +# Ensure structure changes don't slip in +DESC sys.io_by_thread_by_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.io_by_thread_by_latency; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$io_by_thread_by_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$io_by_thread_by_latency; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_io_global_by_file_by_bytes.test b/mysql-test/suite/sysschema/t/v_io_global_by_file_by_bytes.test new file mode 100644 index 00000000000..ded74ae9eb6 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_io_global_by_file_by_bytes.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.io_global_by_file_by_bytes view + +# Ensure structure changes don't slip in +DESC sys.io_global_by_file_by_bytes; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.io_global_by_file_by_bytes; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$io_global_by_file_by_bytes; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$io_global_by_file_by_bytes; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_io_global_by_file_by_latency.test b/mysql-test/suite/sysschema/t/v_io_global_by_file_by_latency.test new file mode 100644 index 00000000000..893d9804bfa --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_io_global_by_file_by_latency.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.io_global_by_file_by_latency view + +# Ensure structure changes don't slip in +DESC sys.io_global_by_file_by_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.io_global_by_file_by_latency; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$io_global_by_file_by_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$io_global_by_file_by_latency; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_io_global_by_wait_by_bytes.test b/mysql-test/suite/sysschema/t/v_io_global_by_wait_by_bytes.test new file mode 100644 index 00000000000..102d8dbe0cc --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_io_global_by_wait_by_bytes.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.io_global_by_wait_by_bytes view + +# Ensure structure changes don't slip in +DESC sys.io_global_by_wait_by_bytes; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.io_global_by_wait_by_bytes; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$io_global_by_wait_by_bytes; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$io_global_by_wait_by_bytes; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_io_global_by_wait_by_latency.test b/mysql-test/suite/sysschema/t/v_io_global_by_wait_by_latency.test new file mode 100644 index 00000000000..8843841b09c --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_io_global_by_wait_by_latency.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.io_global_by_wait_by_latency view + +# Ensure structure changes don't slip in +DESC sys.io_global_by_wait_by_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.io_global_by_wait_by_latency; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$io_global_by_wait_by_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$io_global_by_wait_by_latency; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_latest_file_io.test b/mysql-test/suite/sysschema/t/v_latest_file_io.test new file mode 100644 index 00000000000..f6b7c78995b --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_latest_file_io.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.latest_file_io view + +# Ensure structure changes don't slip in +DESC sys.latest_file_io; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.latest_file_io; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$latest_file_io; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$latest_file_io; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_memory_by_host_by_current_bytes.test b/mysql-test/suite/sysschema/t/v_memory_by_host_by_current_bytes.test new file mode 100644 index 00000000000..6e3477e813a --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_memory_by_host_by_current_bytes.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.memory_by_host_by_current_bytes view + +# Ensure structure changes don't slip in +DESC sys.memory_by_host_by_current_bytes; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.memory_by_host_by_current_bytes; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$memory_by_host_by_current_bytes; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$memory_by_host_by_current_bytes; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_memory_by_thread_by_current_bytes.test b/mysql-test/suite/sysschema/t/v_memory_by_thread_by_current_bytes.test new file mode 100644 index 00000000000..c097ea7ed01 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_memory_by_thread_by_current_bytes.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.memory_by_thread_by_current_bytes view + +# Ensure structure changes don't slip in +DESC sys.memory_by_thread_by_current_bytes; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.memory_by_thread_by_current_bytes; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$memory_by_thread_by_current_bytes; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$memory_by_thread_by_current_bytes; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_memory_by_user_by_current_bytes.test b/mysql-test/suite/sysschema/t/v_memory_by_user_by_current_bytes.test new file mode 100644 index 00000000000..4d10428501d --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_memory_by_user_by_current_bytes.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.memory_by_user_by_current_bytes view + +# Ensure structure changes don't slip in +DESC sys.memory_by_user_by_current_bytes; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.memory_by_user_by_current_bytes; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$memory_by_user_by_current_bytes; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$memory_by_user_by_current_bytes; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_memory_global_by_current_bytes.test b/mysql-test/suite/sysschema/t/v_memory_global_by_current_bytes.test new file mode 100644 index 00000000000..1bed9c98c0c --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_memory_global_by_current_bytes.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.memory_global_by_current_bytes view + +# Ensure structure changes don't slip in +DESC sys.memory_global_by_current_bytes; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.memory_global_by_current_bytes; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$memory_global_by_current_bytes; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$memory_global_by_current_bytes; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_memory_global_total.test b/mysql-test/suite/sysschema/t/v_memory_global_total.test new file mode 100644 index 00000000000..572581d5a90 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_memory_global_total.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.memory_global_total view + +# Ensure structure changes don't slip in +DESC sys.memory_global_total; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.memory_global_total; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$memory_global_total; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$memory_global_total; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_metrics.test b/mysql-test/suite/sysschema/t/v_metrics.test new file mode 100644 index 00000000000..a8ff1cd9f28 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_metrics.test @@ -0,0 +1,12 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.metrics and sys.metrics_56 views + +# Ensure structure changes don't slip in +DESC sys.metrics; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.metrics; +--enable_result_log diff --git a/mysql-test/suite/sysschema/t/v_processlist.test b/mysql-test/suite/sysschema/t/v_processlist.test new file mode 100644 index 00000000000..fd3f01109bf --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_processlist.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.processlist view + +# Ensure structure changes don't slip in +DESC sys.processlist; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.processlist; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$processlist; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$processlist; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_ps_check_lost_instrumentation.test b/mysql-test/suite/sysschema/t/v_ps_check_lost_instrumentation.test new file mode 100644 index 00000000000..b5c7fd19cab --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_ps_check_lost_instrumentation.test @@ -0,0 +1,13 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.ps_check_lost_instrumentation view + +# Ensure structure changes don't slip in +DESC sys.ps_check_lost_instrumentation; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.ps_check_lost_instrumentation; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_ps_digest_95th_percentile_by_avg_us.test b/mysql-test/suite/sysschema/t/v_ps_digest_95th_percentile_by_avg_us.test new file mode 100644 index 00000000000..6f111b7fa98 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_ps_digest_95th_percentile_by_avg_us.test @@ -0,0 +1,24 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc + +DESC sys.x$ps_digest_95th_percentile_by_avg_us; + +DESC sys.x$ps_digest_95th_percentile_by_avg_us; + +# Ensure structure changes don't slip in +DESC sys.x$ps_digest_95th_percentile_by_avg_us; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$ps_digest_95th_percentile_by_avg_us; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$ps_digest_95th_percentile_by_avg_us; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$ps_digest_95th_percentile_by_avg_us; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_ps_digest_avg_latency_distribution.test b/mysql-test/suite/sysschema/t/v_ps_digest_avg_latency_distribution.test new file mode 100644 index 00000000000..6074ca3f46f --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_ps_digest_avg_latency_distribution.test @@ -0,0 +1,24 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc + +DESC sys.x$ps_digest_avg_latency_distribution; + +DESC sys.x$ps_digest_avg_latency_distribution; + +# Ensure structure changes don't slip in +DESC sys.x$ps_digest_avg_latency_distribution; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$ps_digest_avg_latency_distribution; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$ps_digest_avg_latency_distribution; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$ps_digest_avg_latency_distribution; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_ps_schema_table_statistics_io.test b/mysql-test/suite/sysschema/t/v_ps_schema_table_statistics_io.test new file mode 100644 index 00000000000..cfb222b9f5b --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_ps_schema_table_statistics_io.test @@ -0,0 +1,24 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc + +DESC sys.x$ps_schema_table_statistics_io; + +DESC sys.x$ps_schema_table_statistics_io; + +# Ensure structure changes don't slip in +DESC sys.x$ps_schema_table_statistics_io; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$ps_schema_table_statistics_io; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$ps_schema_table_statistics_io; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$ps_schema_table_statistics_io; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_schema_auto_increment_columns.test b/mysql-test/suite/sysschema/t/v_schema_auto_increment_columns.test new file mode 100644 index 00000000000..b3cae841ecf --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_schema_auto_increment_columns.test @@ -0,0 +1,159 @@ +-- source include/big_test.inc +-- source include/not_embedded.inc +-- source include/have_innodb.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.schema_auto_increment_columns view + +# Ensure structure changes don't slip in +DESC sys.schema_auto_increment_columns; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.schema_auto_increment_columns; +--enable_result_log + +# Test the output of the view is as expected +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; + +DROP DATABASE auto_incs; diff --git a/mysql-test/suite/sysschema/t/v_schema_index_statistics.test b/mysql-test/suite/sysschema/t/v_schema_index_statistics.test new file mode 100644 index 00000000000..660eb0db341 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_schema_index_statistics.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.schema_index_statistics view + +# Ensure structure changes don't slip in +DESC sys.schema_index_statistics; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.schema_index_statistics; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$schema_index_statistics; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$schema_index_statistics; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_schema_object_overview.test b/mysql-test/suite/sysschema/t/v_schema_object_overview.test new file mode 100644 index 00000000000..2d142c82c49 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_schema_object_overview.test @@ -0,0 +1,13 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.schema_object_overview view + +# Ensure structure changes don't slip in +DESC sys.schema_object_overview; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.schema_object_overview; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_schema_redundant_indexes.test b/mysql-test/suite/sysschema/t/v_schema_redundant_indexes.test new file mode 100644 index 00000000000..0cd2ac91fa4 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_schema_redundant_indexes.test @@ -0,0 +1,39 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.schema_redundant_indexes / x$schema_flattened_keys views + +# Ensure structure changes don't slip in +DESC sys.schema_redundant_indexes; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.schema_redundant_indexes; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$schema_flattened_keys; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$schema_flattened_keys; +--enable_result_log + +# Test correctly identifies 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; + + DROP DATABASE rkey; diff --git a/mysql-test/suite/sysschema/t/v_schema_table_lock_waits.test b/mysql-test/suite/sysschema/t/v_schema_table_lock_waits.test new file mode 100644 index 00000000000..eef77708769 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_schema_table_lock_waits.test @@ -0,0 +1,55 @@ +-- source include/not_embedded.inc +-- source include/have_innodb.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.schema_table_lock_waits view + +# Ensure structure changes don't slip in +DESC sys.schema_table_lock_waits; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.schema_table_lock_waits; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$schema_table_lock_waits; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$schema_table_lock_waits; +--enable_result_log + +# Ensure lock waits show up correctly +--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; +send ALTER TABLE test.lock_waits ADD k VARCHAR(20); + +connection default; +while (`SELECT COUNT(*)=0 FROM sys.schema_table_lock_waits`) +{ + --sleep 0.5 +} +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; + +disconnect con1; + +connection default; +while (`SELECT COUNT(*)<> 0 FROM sys.schema_table_lock_waits`) +{ + --sleep 0.5 +} +disconnect con2; +connection default; + +DROP TABLE test.lock_waits; diff --git a/mysql-test/suite/sysschema/t/v_schema_table_statistics.test b/mysql-test/suite/sysschema/t/v_schema_table_statistics.test new file mode 100644 index 00000000000..bb72de71721 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_schema_table_statistics.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.schema_table_statistics view + +# Ensure structure changes don't slip in +DESC sys.schema_table_statistics; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.schema_table_statistics; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$schema_table_statistics; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$schema_table_statistics; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_schema_table_statistics_with_buffer.test b/mysql-test/suite/sysschema/t/v_schema_table_statistics_with_buffer.test new file mode 100644 index 00000000000..9fbe523173d --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_schema_table_statistics_with_buffer.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.schema_table_statistics_with_buffer view + +# Ensure structure changes don't slip in +DESC sys.schema_table_statistics_with_buffer; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.schema_table_statistics_with_buffer; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$schema_table_statistics_with_buffer; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$schema_table_statistics_with_buffer; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_schema_tables_with_full_table_scans.test b/mysql-test/suite/sysschema/t/v_schema_tables_with_full_table_scans.test new file mode 100644 index 00000000000..1ada8284a26 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_schema_tables_with_full_table_scans.test @@ -0,0 +1,87 @@ +-- source include/not_embedded.inc +-- source include/have_innodb.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.schema_tables_with_full_table_scans view + +# Ensure structure changes don't slip in +DESC sys.schema_tables_with_full_table_scans; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.schema_tables_with_full_table_scans; +--enable_result_log + +# Ensure structure changes don't slip in +DESC sys.x$schema_tables_with_full_table_scans; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$schema_tables_with_full_table_scans; +--enable_result_log + +# Create a dummy table, force some full table scans, verify the results +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); + +# The table should now have 768 rows + +# Now truncate the P_S tables, to get fresh results +CALL sys.ps_truncate_all_tables(false); + +# Following returns 28 rows, but should full scan as j has no index +# Using RAND() to force no query caching, so ignore results, they are not important +--disable_result_log +SELECT i, j, RAND() FROM test.t WHERE j = 12; +--enable_result_log + +# Ensure the base performance schema data is aggregated first +--let $wait_condition= SELECT COUNT_STAR = 768 FROM performance_schema.table_io_waits_summary_by_index_usage WHERE object_schema = 'test' AND object_name = 't' AND index_name IS NULL +--source include/wait_condition.inc + +# Now verify the table shows up in the views with the right row count (should be 768) +SELECT object_schema, object_name, rows_full_scanned FROM sys.schema_tables_with_full_table_scans; + +SELECT object_schema, object_name, rows_full_scanned FROM sys.x$schema_tables_with_full_table_scans; + +# Scan again +--disable_result_log +SELECT i, j, RAND() FROM test.t WHERE j = 12; +--enable_result_log + +# Again ensure the base performance schema data is aggregated first +--let $wait_condition= SELECT COUNT_STAR = 1536 FROM performance_schema.table_io_waits_summary_by_index_usage WHERE object_schema = 'test' AND object_name = 't' AND index_name IS NULL +--source include/wait_condition.inc + +# Now verify that double the amount of rows in the table should be shown as scanned (should be 1536) +SELECT object_schema, object_name, rows_full_scanned FROM sys.schema_tables_with_full_table_scans; + +SELECT object_schema, object_name, rows_full_scanned FROM sys.x$schema_tables_with_full_table_scans; + +# Do a point lookup +SELECT * FROM test.t WHERE i = 10; + +# The number of rows scanned should not have changed (should still be 1536) +SELECT object_schema, object_name, rows_full_scanned FROM sys.schema_tables_with_full_table_scans; + +SELECT object_schema, object_name, rows_full_scanned FROM sys.x$schema_tables_with_full_table_scans; + +# Cleanup +DROP TABLE test.t; diff --git a/mysql-test/suite/sysschema/t/v_schema_unused_indexes.test b/mysql-test/suite/sysschema/t/v_schema_unused_indexes.test new file mode 100644 index 00000000000..a9000c2568d --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_schema_unused_indexes.test @@ -0,0 +1,13 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.schema_unused_indexes view + +# Ensure structure changes don't slip in +DESC sys.schema_unused_indexes; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.schema_unused_indexes; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_session.test b/mysql-test/suite/sysschema/t/v_session.test new file mode 100644 index 00000000000..44431653067 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_session.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.session view + +# Ensure structure changes don't slip in +DESC sys.session; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.session; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$session; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$session; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_session_ssl_status.test b/mysql-test/suite/sysschema/t/v_session_ssl_status.test new file mode 100644 index 00000000000..00d60737ca9 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_session_ssl_status.test @@ -0,0 +1,12 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.session_ssl_status view + +# Ensure structure changes don't slip in +DESC sys.session_ssl_status; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.session_ssl_status; +--enable_result_log diff --git a/mysql-test/suite/sysschema/t/v_statement_analysis.test b/mysql-test/suite/sysschema/t/v_statement_analysis.test new file mode 100644 index 00000000000..0a4d3e8886d --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_statement_analysis.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.statement_analysis view + +# Ensure structure changes don't slip in +DESC sys.statement_analysis; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.statement_analysis; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$statement_analysis; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$statement_analysis; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_statements_with_errors_or_warnings.test b/mysql-test/suite/sysschema/t/v_statements_with_errors_or_warnings.test new file mode 100644 index 00000000000..400b6f83d33 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_statements_with_errors_or_warnings.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.statements_with_errors_or_warnings view + +# Ensure structure changes don't slip in +DESC sys.statements_with_errors_or_warnings; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.statements_with_errors_or_warnings; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$statements_with_errors_or_warnings; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$statements_with_errors_or_warnings; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_statements_with_full_table_scans.test b/mysql-test/suite/sysschema/t/v_statements_with_full_table_scans.test new file mode 100644 index 00000000000..8ec48527ef0 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_statements_with_full_table_scans.test @@ -0,0 +1,87 @@ +-- source include/not_embedded.inc +-- source include/have_innodb.inc +-- source ../include/ps_truncate_all_tables.inc +# Performance schema tracks prepared statements separately, and does not +# yet have a summary view that we can use for this view. +# Until then, we disable this test with --ps-protocol +-- source include/no_protocol.inc + +# Tests for sys schema +# Verify the sys.statements_with_full_table_scans view + +# Ensure structure changes don't slip in +DESC sys.statements_with_full_table_scans; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.statements_with_full_table_scans; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$statements_with_full_table_scans; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$statements_with_full_table_scans; +--enable_result_log + +# Create a dummy table, force some full table scans, verify the results +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); + +# The table should now have 768 rows + +# Now truncate the P_S tables, to get fresh results +CALL sys.ps_truncate_all_tables(false); + +# Following returns 28 rows, but should full scan as j has no index +# Using RAND() to force no query caching, so ignore results, they are not important +--disable_result_log +SELECT i, j, RAND() FROM v_statements_with_full_table_scans.t WHERE j = 12; +--enable_result_log + +# Now verify the statement shows up in the views with the right row count (should be 768) +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; + +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; + +# Scan again +--disable_result_log +SELECT i, j, RAND() FROM v_statements_with_full_table_scans.t WHERE j = 12; +--enable_result_log + +# Now verify that double the amount of rows for the statement should be shown as scanned (should be 1536) +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; + +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; + +# Do a point lookup +SELECT * FROM v_statements_with_full_table_scans.t WHERE i = 10; + +# The number of rows scanned should not have changed (should still be 1536) +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; + +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; + +# Cleanup +DROP DATABASE v_statements_with_full_table_scans; diff --git a/mysql-test/suite/sysschema/t/v_statements_with_runtimes_in_95th_percentile.test b/mysql-test/suite/sysschema/t/v_statements_with_runtimes_in_95th_percentile.test new file mode 100644 index 00000000000..ec2865e172d --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_statements_with_runtimes_in_95th_percentile.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.statements_with_runtimes_in_95th_percentile view + +# Ensure structure changes don't slip in +DESC sys.statements_with_runtimes_in_95th_percentile; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.statements_with_runtimes_in_95th_percentile; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$statements_with_runtimes_in_95th_percentile; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$statements_with_runtimes_in_95th_percentile; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_statements_with_sorting.test b/mysql-test/suite/sysschema/t/v_statements_with_sorting.test new file mode 100644 index 00000000000..cf692c6e332 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_statements_with_sorting.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.statements_with_sorting view + +# Ensure structure changes don't slip in +DESC sys.statements_with_sorting; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.statements_with_sorting; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$statements_with_sorting; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$statements_with_sorting; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_statements_with_temp_tables.test b/mysql-test/suite/sysschema/t/v_statements_with_temp_tables.test new file mode 100644 index 00000000000..5d88e8d6cc2 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_statements_with_temp_tables.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.statements_with_temp_tables view + +# Ensure structure changes don't slip in +DESC sys.statements_with_temp_tables; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.statements_with_temp_tables; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$statements_with_temp_tables; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$statements_with_temp_tables; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_user_summary.test b/mysql-test/suite/sysschema/t/v_user_summary.test new file mode 100644 index 00000000000..19d8824bfe0 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_user_summary.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.user_summary view + +# Ensure structure changes don't slip in +DESC sys.user_summary; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.user_summary; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$user_summary; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$user_summary; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_user_summary_by_file_io.test b/mysql-test/suite/sysschema/t/v_user_summary_by_file_io.test new file mode 100644 index 00000000000..2818a70aeab --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_user_summary_by_file_io.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.user_summary_by_file_io view + +# Ensure structure changes don't slip in +DESC sys.user_summary_by_file_io; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.user_summary_by_file_io; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$user_summary_by_file_io; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$user_summary_by_file_io; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_user_summary_by_file_io_type.test b/mysql-test/suite/sysschema/t/v_user_summary_by_file_io_type.test new file mode 100644 index 00000000000..44d0edd449e --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_user_summary_by_file_io_type.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.user_summary_by_file_io_type view + +# Ensure structure changes don't slip in +DESC sys.user_summary_by_file_io_type; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.user_summary_by_file_io_type; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$user_summary_by_file_io_type; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$user_summary_by_file_io_type; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_user_summary_by_stages.test b/mysql-test/suite/sysschema/t/v_user_summary_by_stages.test new file mode 100644 index 00000000000..8977d067f18 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_user_summary_by_stages.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.user_summary_by_stages view + +# Ensure structure changes don't slip in +DESC sys.user_summary_by_stages; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.user_summary_by_stages; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$user_summary_by_stages; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$user_summary_by_stages; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_user_summary_by_statement_latency.test b/mysql-test/suite/sysschema/t/v_user_summary_by_statement_latency.test new file mode 100644 index 00000000000..4c962c3feba --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_user_summary_by_statement_latency.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.user_summary_by_statement_latency view + +# Ensure structure changes don't slip in +DESC sys.user_summary_by_statement_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.user_summary_by_statement_latency; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$user_summary_by_statement_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$user_summary_by_statement_latency; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_user_summary_by_statement_type.test b/mysql-test/suite/sysschema/t/v_user_summary_by_statement_type.test new file mode 100644 index 00000000000..a21762150ea --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_user_summary_by_statement_type.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.user_summary_by_statement_type view + +# Ensure structure changes don't slip in +DESC sys.user_summary_by_statement_type; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.user_summary_by_statement_type; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$user_summary_by_statement_type; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$user_summary_by_statement_type; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_version.test b/mysql-test/suite/sysschema/t/v_version.test new file mode 100644 index 00000000000..bb60de8dd01 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_version.test @@ -0,0 +1,11 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.version view + +# We do not do a DESC of this view, as the size of the version field +# can change depending on the build. +# Instead, we just select the sys_version, to ensure no errors/unwanted updates + +SELECT sys_version FROM sys.version; + diff --git a/mysql-test/suite/sysschema/t/v_wait_classes_global_by_avg_latency.test b/mysql-test/suite/sysschema/t/v_wait_classes_global_by_avg_latency.test new file mode 100644 index 00000000000..63eb79fbca3 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_wait_classes_global_by_avg_latency.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.wait_classes_global_by_avg_latency view + +# Ensure structure changes don't slip in +DESC sys.wait_classes_global_by_avg_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.wait_classes_global_by_avg_latency; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$wait_classes_global_by_avg_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$wait_classes_global_by_avg_latency; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_wait_classes_global_by_latency.test b/mysql-test/suite/sysschema/t/v_wait_classes_global_by_latency.test new file mode 100644 index 00000000000..87923996b90 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_wait_classes_global_by_latency.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.wait_classes_global_by_latency view + +# Ensure structure changes don't slip in +DESC sys.wait_classes_global_by_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.wait_classes_global_by_latency; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$wait_classes_global_by_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$wait_classes_global_by_latency; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_waits_by_host_by_latency.test b/mysql-test/suite/sysschema/t/v_waits_by_host_by_latency.test new file mode 100644 index 00000000000..56235abc0c9 --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_waits_by_host_by_latency.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.waits_by_host_by_latency view + +# Ensure structure changes don't slip in +DESC sys.waits_by_host_by_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.waits_by_host_by_latency; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$waits_by_host_by_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$waits_by_host_by_latency; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_waits_by_user_by_latency.test b/mysql-test/suite/sysschema/t/v_waits_by_user_by_latency.test new file mode 100644 index 00000000000..0f98c3a254a --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_waits_by_user_by_latency.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.waits_by_user_by_latency view + +# Ensure structure changes don't slip in +DESC sys.waits_by_user_by_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.waits_by_user_by_latency; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$waits_by_user_by_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$waits_by_user_by_latency; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/v_waits_global_by_latency.test b/mysql-test/suite/sysschema/t/v_waits_global_by_latency.test new file mode 100644 index 00000000000..a41aff01cda --- /dev/null +++ b/mysql-test/suite/sysschema/t/v_waits_global_by_latency.test @@ -0,0 +1,22 @@ +-- source include/not_embedded.inc +-- source ../include/ps_truncate_all_tables.inc +# Tests for sys schema +# Verify the sys.waits_global_by_latency view + +# Ensure structure changes don't slip in +DESC sys.waits_global_by_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.waits_global_by_latency; +--enable_result_log + + +# Ensure structure changes don't slip in +DESC sys.x$waits_global_by_latency; + +# Make sure view select does not error, but ignore results +--disable_result_log +SELECT * FROM sys.x$waits_global_by_latency; +--enable_result_log + diff --git a/mysql-test/suite/sysschema/t/version_functions.test b/mysql-test/suite/sysschema/t/version_functions.test new file mode 100644 index 00000000000..29a0d744fd8 --- /dev/null +++ b/mysql-test/suite/sysschema/t/version_functions.test @@ -0,0 +1,27 @@ +########### suite/sysschema/t/version_functions.test ############# +# # +# Testing of the sys.version_major(), sys.version_minor(), # +# and sys.version_patch() functions # +# # +# Creation: # +# 2015-08-14 jkrogh Implement this test # +# # +################################################################## + +-- source include/not_embedded.inc + +# Sanity check - the functions should not return any warnings or errors +--disable_result_log +SELECT sys.version_major(); +SELECT sys.version_minor(); +SELECT sys.version_patch(); +--enable_result_log + +# Check that concatenating the three version parts gives the beginning of the output of VERSION() +# This is not truly an independent test, but there isn't really anywhere else to get the actual version, +# so it at least verifies that the three parts go back together in the right way. +let $MY_VERSION=`SELECT CONCAT(sys.version_major(), '.', sys.version_minor(), '.', sys.version_patch())`; +--disable_query_log ONCE +eval SET @my_version = '$MY_VERSION'; + +SELECT @my_version = SUBSTRING(VERSION(), 1, CHAR_LENGTH(@my_version)); |