diff options
Diffstat (limited to 'scripts/sys_schema/NEWS.md')
| -rw-r--r-- | scripts/sys_schema/NEWS.md | 218 |
1 files changed, 218 insertions, 0 deletions
diff --git a/scripts/sys_schema/NEWS.md b/scripts/sys_schema/NEWS.md new file mode 100644 index 00000000..7aeefb7e --- /dev/null +++ b/scripts/sys_schema/NEWS.md @@ -0,0 +1,218 @@ +# Change history for the MySQL sys schema + +## 1.5.1 (07/07/16) + +### Improvements + +* A `quote_identifier` function was added, which can be used to properly backtick identifier names +* The `Tls_version` column was added to the output from the `mysql.slave_master_info` table, from the `diagnostics` procedure (backported from 5.7 upstream change) + +### Bug Fixes + +* MySQL Bug #77853 / Oracle Bug #21512106 - The `format_path` function did not consider directory boundaries when comparing variables to paths - it now does. Also fixed to no longer translate backslashes within Windows paths to forward slash +* Oracle Bug #21663578 - Fixed an instability within the sysschema.v_schema_tables_with_full_table_scans test +* Oracle Bug #21970078 - The host_summary view could fail with a division by zero error +* MySQL Bug #78874 / Oracle Bug #22066096 - The `ps_setup_show_enabled` procedure showed all rows for the `performance_schema.setup_objects` table, rather than only those that are enabled +* MySQL Bug #80569 / Oracle Bug #22848110 - The `max_latency` column for the `host_summary_by_statement_latency` view incorrectly showed the SUM of latency +* MySQL Bug #80833 / Oracle Bug #22988461 - The `pages_hashed` and `pages_old` columns within the `innodb_buffer_stats_by_schema` and `innodb_buffer_stats_by_table` views were calculated incorrectly (**Contributed by Tsubasa Tanaka**) +* MySQL Bug #78823 / Oracle Bug #22011361 - The `create_synonym_db` procedure failed when using reserved words as the synonym name (this change also introduced the `quote_identifier` function mentioned above **Contriubuted by Paul Dubois**) +* MySQL Bug #81564 / Oracle Bug #23335880 - The `ps_setup_show_enabled` and `ps_setup_show_disabled` procedures were fixed to: +** Show `user@host` instead of `host@user` for accounts +** Fixed the column header for `disabled_users` within `ps_setup_show_disabled` +** Explicitly ordered all output for test stability +** Show disabled users for 5.7.6+ +* Oracle Bug #21970806 - The `sysschema.fn_ps_thread_trx_info` test was unstable +* Oracle Bug #23621189 - The `ps_trace_statement_digest` procedure ran EXPLAIN incorrectly in certain cases (such as on a SHOW statement, no query being specified, or not having a full qualified table), the procedure now catches these issues and ignores them + +## 1.5.0 (11/09/15) + +### Improvements + +* The `format_bytes` function now shows no decimal places when outputting a simple bytes value +* The `processlist`/`x$processlist` views where improved, changes include: + * The `pid` and `program_name` of the connection are shown, if set within the `performance_schema.session_connect_attrs` table (**Contributed by Daniël van Eeden**) + * Issue #50 - The current statement progress is reported via the new stage progress reporting within Performance Schema stages within 5.7 (such as ALTER TABLE progress reporting) + * Issue #60 - A new `statement_latency` column was added to all versions, which reports the current statement latency with picosecond precision from the `performance_schema.events_statements_current` table, when enabled + * Some transaction information was exposed, with the `trx_latency` (for the current or last transaction depending on `trx_state`), `trx_state` (ACTIVE, COMMITTED, ROLLED BACK), and `trx_autocommit` (YES/NO) columns +* A new `metrics` view has been added. On 5.7 this provides a union view of the performance_schema.global_status and information_schema.innodb_metrics tables, along with P_S memory and the current time, as a single metrics output. On 5.6 it provides a union view of the information_schema.global_status and information_schema.innodb_metrics tables, along with the current time. (**Contributed by Jesper Wisborg Krogh**) +* New `session`/`x$session` views have been added, which give the same output as the `processlist` view counterparts, but filtered to only show foreground connections (**Contributed by Morgan Tocker**) +* A new `session_ssl_status` view was added, which shows the SSL version, ciper and session resuse statistics for each connection (**Contributed by Daniël van Eeden**) +* A new `schema_auto_increment_columns` view was added, that shows statistics on each auto_incrment within the instance, including the `auto_increment_ratio`, so you can easily monitor how full specific auto_increment columns are (**Contributed by Shlomi Noach**) +* A new `schema_redundant_indexes` view was added, that shows indexes made redundant (or duplicated) by other more dominant indexes. Also includes the the helper view `x$schema_flattened_keys`. (**Contributed by Shlomi Noach**) +* New `schema_table_lock_waits`/`x$schema_table_lock_waits` views have been added, which show any sessions that are waiting for table level metadata locks, and the sessions that are blocking them. Resolves Git Issue #57, inspired by the suggestion from Daniël van Eeden +* The `innodb_lock_waits` view had the following columns added to it, following a manually merged contribution from Shlomi Noach for a similar view + * `wait_age_secs` - the current row lock wait time in seconds + * `sql_kill_blocking_query` - the "KILL QUERY <connection_id>" command to run to kill the blocking session current statement + * `sql_kill_blocking_connection` - the "KILL <connection_id" command to run to kill the blocking session +* A new `table_exists` procedure was added, which checks for the existence of table, and if it exists, returns the type (BASE TABLE, VIEW, TEMPORARY) (**Contributed by Jesper Wisborg Krogh**) +* A new `execute_prepared_stmt()` procedure was added, which takes a SQL statement as an input variable and executes it as a prepared statement (**Contributed by Jesper Wisborg Krogh**) +* A new `statement_performance_analyzer()` procedure was added, that allows reporting on the statements that are have been running over snapshot periods (**Contributed by Jesper Wisborg Krogh**) +* A new `diagnostics()` procedure was added, which creates a large diagnostics report based upon most of the new instrumentation now available, computed over a configurable number of snapshot intervals (**Contributed by Jesper Wisborg Krogh**) +* A 5.7 specific `ps_trace_thread()` procedure was added, which now shows the hierarchy of transactions and stored routines, as well as statements, stages and waits, if enabled +* Added a new `ps_thread_account()` stored function, that returns the "user@host" account for a given Performance Schema thread id +* Added a new `ps_thread_trx_info()` stored function which outputs, for a given thread id, the transactions, and statements that those transactions have executed, as a JSON object +* Added new `list_add()` and `list_drop()` stored functions, that take a string csv list, and either add or remove items from that list respectively. Can be used to easily update variables that take such lists, like `sql_mode`. +* The `ps_thread_id` stored function now returns the thread id for the current connection if NULL is passed for the in_connection_id parameter +* Added a new `version_major()` stored function, which returns the major version of MySQL Server (**Contributed by Jesper Wisborg Krogh**) +* Added a new `version_minor()` stored function, which returns the minor (release series) version of MySQL Server (**Contributed by Jesper Wisborg Krogh**) +* Added a new `version_patch()` stored function, which returns the patch release version of MySQL Server (**Contributed by Jesper Wisborg Krogh**) +* The `ps_is_account_enabled` function was updated to take a VARCHAR(32) user input on 5.7, as a part of WL#2284 +* The generate_sql_file.sh script had a number of improvements: + * Generated files are now output in to a "gen" directory, that is ignored by git + * Added using a new default "mysql.sys@localhost" user (that has the account locked) for the MySQL 5.7+ integration as the DEFINER for all objects + * Added a warning to the top of the generated integration file to also submit changes to the sys project + * Improved the the option of skipping binary logs, so that all routines can load as well - those that used SET sql_log_bin will now select a warning when being used instead of setting the option + +### Bug Fixes + +* Git Issue #51 - Fixed the `generate_sql_file.sh` script to also replace the definer in the before_setup.sql output +* Git Issue #52 - Removed apostrophe from the `format_statement` function comment because TOAD no likey +* Git Issue #56 - Installation failed on 5.6 with ONLY_FULL_GROUP_BY enabled +* Git Issue #76 - Fixes for the new show_compatibility_56 variable. 5.7 versions of the `format_path()` function and `ps_check_lost_instrumentation` view were added, that use performance_schema.global_status/global_variables instead of information_schema.global_status/global_variables +* Git Issue #79 - Fixed grammar within `statements_with_runtimes_in_95th_percentile` view descriptions +* Oracle Bug #21484593 / Oracle Bug #21281955 - The `format_path()` function incorrectly took and returned a VARCHAR(260) instead of VARCHAR(512) (as the underlying is exposed as in Performance Schema) causing sporadic test failures +* Oracle Bug #21550271 - Fixed the `ps_setup_reset_to_default` for 5.7 with the addition of the new `history` column on the `performance_schema.setup_actors` table +* Oracle Bug #21550054 - It is possible that the views can show data that overflows when aggregating very large values, reset all statistics before each test to ensure no overflows +* Oracle Bug #21647101 - Fixed the `ps_is_instrument_default_enabed` and `ps_is_instrument_default_timed` to take in to account the new instruments added within 5.7 +* MySQL Bug #77848 - Added the missing ps_setup_instruments_cleanup.inc +* Fixed the `ps_setup_reset_to_default()` procedure to also set the new `ENABLED` column within `performance_schema.setup_actors` within 5.7 +* The `user_summary_by_file_io`/`x$user_summary_by_file_io` and `host_summary_by_file_io`/`x$host_summary_by_file_io` tables were incorrectly aggregating all wait events, not just `wait/io/file/%` + +### Implementation Details + +* Tests were improved via 5.7 integration +* Template files were added for stored procedures and functions +* Improved the sys_config_cleanup.inc procedure in tests to be able to reset the sys_config table completely (including the set_by column to NULL). The triggers can now be set to not update the column by setting the @sys.ignore_sys_config_triggers user variable to true + +## 1.4.0 (09/03/2015) + +### Backwards Incompatible Changes + +* The `memory_global_by_current_allocated` views were renamed to `memory_global_by_current_bytes` for consistency with the other memory views +* The `ps_setup_enable_consumers` procedure was renamed to `ps_setup_disable_consumer` for naming consistency (everything is now singular, not plural) +* The `format_time` function displayed values in minutes incorrectly, it now rounds to minutes, and uses an 'm' suffix, like the rest of the units + +### Improvements + +* The beginnings of a mysql-test suite have been added +* The `innodb_lock_waits`/`x$innodb_lock_waits` views were improved (**Contributions by both Jesper Wisborg Krogh and Mark Matthews**) + * Added the `wait_started`, `wait_age`, `waiting_trx_started` `waiting_trx_age`, `waiting_trx_rows_locked` and `waiting_trx_rows_modified` columns for waiting transactions + * Added the `blocking_trx_started`, `blocking_trx_age`, `blocking_trx_rows_locked` and `blocking_trx_rows_modified` for blocking transactions + * Order the result set so the oldest lock waits are first + * The `waiting_table` and `waiting_index` were always the same as the `blocking_table` and `blocking_index`. So the blocking_% columns have been removed and the waiting_% columns have been renamed to locked_% + * The `waiting_lock_type` and `blocking_lock_type` were also always the same. So these were removed and replaced with a single `locked_type` column + * Renamed the `waiting_thread` and `blocking_thread` to `waiting_pid` and `blocking_pid` respectively to avoid confusion with the threads from the Performance Schema. +* Added the `sys_get_config` function, used to get configuration parameters from the `sys_config` table - primarily from other sys objects, but can be used individually (**Contributed by Jesper Wisborg Krogh**) +* Add an option to generate_sql_file.sh to generate a mysql_install_db / mysqld --initialize format friendly file +* Added the `ps_is_thread_instrumented` function, to check whether a specified thread is instrumented within Performance Schema +* Added the `ps_is_consumer_enabled` function, to check whether a specified consumer is enabled within Performance Schema (**Contributed by Jesper Wisborg Krogh**) +* Added some further replacements to the `format_path` function (`slave_load_tmpdir`, `innodb_data_home_dir`, `innodb_log_group_home_dir` and `innodb_undo_directory`) + +### Bug Fixes + +* The 5.6 `host_summary` and `x$host_summary` views incorrectly had the column with `COUNT(DISTINCT accounts.user)` named `unique_hosts` instead of `unique_users` (**Contributed by Jesper Wisborg Krogh**) +* Both the `format_time` and `format_bytes` took a BIGINT as input, and output VARCHAR, but BIGINT could be too small for aggregated values for the inputs. Now both functions both use TEXT as their input (Issue #34, Issue #38) +* The `format_time` function displayed values in minutes incorrectly, it now rounds to minutes, and uses an 'm' suffix, like the rest of the units +* The `sys_config` related triggers had no DEFINER clause set +* The `ps_setup_disable_thread` procedure always disabled the current thread and was ignoring the connection id given as an argument (**Contributed by Jesper Wisborg Krogh**) +* The `ps_trace_thread` procedure had an incorrect calculation of how long the procedure has been running (**Contributed by Jesper Wisborg Krogh**) + +### Implementation Details + +Various changes were made to allow better generation of integration sql files: + +* The formatting for all comments has been standardized on -- line comments. C-style /* comments */ have been removed + * Issue #35 had one instance of this resolved in this release (**Contributed by Joe Grasse**), but the entire code base has now been done +* Each object has been created within it's own file. No longer do x$ views live with their non-x$ counterparts +* DELIMITERs were standardized to $$ + +## 1.3.0 (23/10/2014) + +### Improvements + +* Added an `innodb_lock_waits` set of views, showing each thread that is waiting on a lock within InnoDB, and the blocking thread lock information (**Contributed by Jesper Wisborg Krogh**) + +### Bug Fixes + +* Fixed broken `host_summary_by_stages` views, broken with a last minute change before the 1.2.0 release that went unnoticed (facepalm) + +## 1.2.0 (22/10/2014) + +### Backwards Incompatible Changes + +* The `host_summary_by_stages` and `user_summary_by_stages` `wait_sum` and `wait_avg` columns were renamed to `total_latency` and `avg_latency` respectively, for consistency. +* The `host_summary_by_file_io_type` and `user_summary_by_file_io_type `latency` column was renamed to `total_latency`, for consistency. + +### Improvements + +* Made the truncation length for the `format_statement` view configurable + * This includes adding a new persistent `sys_config` table to store the new variable - `statement_truncate_len` - see the README for usage +* Added `total_latency` to the `schema_tables_with_full_table_scans` view, and added an x$ counterpart +* Added `innodb_buffer_free` to the `schema_table_statistics_with_buffer` view, to summarize how much free space is allocated per table in the buffer pool +* The `schema_unused_indexes` view now ignores indexes named `PRIMARY` (primary keys) +* Added `rows_affected` and `rows_affected_avg` stats to the `statement_analysis` views +* The `statements_with_full_table_scans` view now ignores any SQL that starts with `SHOW` +* Added a script, `generate_sql_file.sh`, that can be used to generate a single SQL file, also allowing substitution of the MySQL user to use, and/or whether the `SET sql_log_bin ...` statements should be omitted. + * This is useful for those using RDS, where the root@localhost user is not accessible, and sql_log_bin is disabled (Issue #5) +* Added a set of `memory_by_thread_by_current_bytes` views, that summarize memory usage per thread with MySQL 5.7's memory instrumentation +* Improved each of the host specific views to return aggregate values for `background` threads, instead of ignoring them, in the same way as the user summary views + +### Bug Fixes + +* Added the missing `memory_by_host` view for MySQL 5.7 +* Added missing space for hour notation within the `format_time` function +* Fixed views affected by MySQL 5.7 ONLY_FULL_GROUP_BY and functional dependency changes + +## 1.1.0 (04/09/2014) + +### Improvements + +* Added host summary views, which have the same structure as the user summary views, but aggregated by host instead (**Contributed by Arnaud Adant**) + * `host_summary` + * `host_summary_by_file_io_type` + * `host_summary_by_file_io` + * `host_summary_by_statement_type` + * `host_summary_by_statement_latency` + * `host_summary_by_stages` + * `waits_by_host_by_latency` + +* Added functions which return instruments are either enabled, or timed by default (#15) (**Contributed by Jesper Wisborg Krogh**) + * `ps_is_instrument_default_enabled` + * `ps_is_instrument_default_timed` + +* Added a `ps_thread_id` function, which returns the thread_id value exposed within performance_schema for the current connection (**Contributed by Jesper Wisborg Krogh**) +* Improved each of the user specific views to return aggregate values for `background` threads, instead of ignoring them (**Contributed by Joe Grasse**) +* Optimized the `schema_table_statistics` and `schema_table_statistics_with_buffer` views, to use a new view that will get materialized (`x$ps_schema_table_statistics_io`), along with the changes to the RETURN types for `extract_schema_from_file_name` and `extract_table_from_file_name`, this results in a significant performance improvement - in one test changing the run time from 14 minutes to 20 seconds. (**Conceived by Roy Lyseng, Mark Leith and Jesper Wisborg Krogh, implemented and contributed by Jesper Wisborg Krogh**) + +### Bug Fixes + +* Removed unintentially committed sys_56_rds.sql file (See Issue #5, which is still outstanding) +* Fixed the `ps_trace_statement_digest` and `ps_trace_thread` procedures to properly set sql_log_bin, and reset the thread INSTRUMENTED value correctly (**Contributed by Jesper Wisborg Krogh**) +* Removed various sql_log_bin disabling from other procedures that no longer require it - DML against the performance_schema data is no longer replicated (**Contributed by Jesper Wisborg Krogh**) +* Fixed EXPLAIN within `ps_trace_statement_digest` procedure (**Contributed by Jesper Wisborg Krogh**) +* Fixed the datatype for the `thd_id` variable within the `ps_thread_stack` procedure (**Contributed by Jesper Wisborg Krogh**) +* Fixed datatypes used for temporary tables within the `ps_trace_statement_digest` procedure (**Contributed by Jesper Wisborg Krogh**) +* Fixed the RETURN datatype `extract_schema_from_file_name` and `extract_table_from_file_name` to return a VARCHAR(64) (**Contributed by Jesper Wisborg Krogh**) +* Added events_transactions_current to the default enabled consumers in 5.7 (#25) + +## 1.0.1 (23/05/2014) + +### Improvements + +* Added procedures to enable / disable Performance Schema consumers. (**Contributed by the MySQL QA Team**) + * `ps_setup_disable_consumers(<LIKE string>)` allows disabling any consumers matching the LIKE string. + * `ps_setup_enable_consumers(<LIKE string>)` allows enabling any consumers matching the LIKE string. + +* Added procedures to show both enabled and disbled consumers or instruments individually, these are more useful for tooling than the `ps_setup_show_enabled`/`ps_setup_show_disabled` procedures which show all configuration in multiple result sets. (**Contributed by the MySQL QA Team**) + * `ps_setup_show_disabled_consumers` shows only disabled consumers. + * `ps_setup_show_disabled_instruments` shows only disabled instruments. + * `ps_setup_show_enabled_consumers` shows only enabled consumers. + * `ps_setup_show_enabled_instruments` shows only enabled instruments. + +### Bug Fixes + +* Running the installation scripts sometimes failed because of the comment format. (#1) (**Contributed by Joe Grasse**) +* Some views did not work with the ERROR_FOR_DIVISION_BY_ZERO SQL mode. (#6) (**Contributed by Joe Grasse**) +* On Windows the `ps_thread_stack()` stored function failed to escape file path backslashes correctly within the JSON output. + +## 1.0.0 (11/04/2014) |