WL#8792: Update to sys schema 1.5.0

Affects: Server-5.7   —   Status: Complete

Pull in the changes from the 1.5.0 release of the sys schema.

This includes bug fixes for many of the updates to 5.7 (such as new instruments, 
new columns in performance_schema.setup_actors), as well as general test case 
fixes, a number of new improvements contributed by external contributors, and a 
number of internally developed (via Support and others) improvements.

Also fixes:

* WL#8460 - Progress Reporting in Sys Schema
* WL#7804 - REPORT FOR SUPPORT

* V_IO_GLOBAL_BY_FILE_BY_BYTES: 1265: DATA TRUNCATED FOR COLUMN 'PATH' AT ROW 270
* MEMORY ERROR FAILURE IN SYSSCHEMA.V_IO_GLOBAL_BY_FILE_BY_BYTES
* SYSSCHEMA.V_WAIT_CLASSES_GLOBAL_BY_AVG_LATENCY HAS SPORADIC FAILURES ON PB2
* SYS SCHEMA INCOMPATIBLE WITH FIX FOR BUG 75156; TIMER_END / TIMER_WAIT NOT NULL
* SYS.PS_SETUP_RESET_TO_DEFAULT FAILS DUE TO ENABLE/HISTORY COLUMN IN SETUP_ACTORS
* PS_SETUP_RESET_TO_DEFAULT_CLEANUP.INC INCLUDES PS_SETUP_INSTRUMENTS_CLEANUP.INC 
* PS_IS_INSTRUMENT_DEFAULT_ENABLED AND PS_IS_INSTRUMENT_DEFAULT_TIMED IN 5.7.7+
F1 On upgrade from a previous 5.7 release (after mysql_ugprade) the following new 
objects should exist

F1.1 Stored Routines
- 1.1.1 diagnostics
- 1.1.2 execute_prepared_stmt
- 1.1.3 statement_performance_analyzer
- 1.1.4 table_exists

F1.2 Stored Functions
- 1.2.1 list_add
- 1.2.2 list_drop
- 1.2.3 ps_thread_account
- 1.2.4 ps_thread_trx_info
- 1.2.5 version_major
- 1.2.6 version_minor
- 1.2.7 version_patch

F1.3 Views
- 1.3.1 metrics
- 1.3.3 schema_auto_increment_columns
- 1.3.4 schema_redundant_indexes
- 1.3.5 schema_table_lock_waits
- 1.3.6 session
- 1.3.7 session_ssl_status
- 1.3.8 x$schema_flattened_keys
- 1.3.9 x$schema_table_lock_waits
- 1.3.10 x$session

F2 All objects should be owned by a new 'mysql.sys'@'localhost' user
- 2.1 The new account should be created with a fixed invalid password of 
'*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE', using the 
mysql_native_password plugin
- 2.2 The new account should be locked
- 2.3 The new account should only have the TRIGGER privilege globally, and SELECT 
on the sys.sys_config table
The 1.5.0 sys changelog details the changes that will be pulled in:

##### 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 " command to run 
to kill the blocking session current statement
  * `sql_kill_blocking_connection` - the "KILL 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 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

* 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

* 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

* 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

* 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/%`

* 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

##### 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