249 lines
8.2 KiB
Plaintext
249 lines
8.2 KiB
Plaintext
#
|
|
# Upgrading of sys schema tests
|
|
#
|
|
CALL mtr.add_suppression('Found \\d+ sys .*, but expected \\d+. Re-installing the sys schema.');
|
|
CREATE TABLE mysql.tmp_backup_tables_priv AS SELECT * FROM mysql.tables_priv;
|
|
CREATE TABLE mysql.tmp_backup_user AS SELECT * FROM mysql.user;
|
|
# Remove the sys schema
|
|
DROP DATABASE sys;
|
|
# Now run it normally, to ensure the sys schema is created
|
|
# restart:--upgrade=FORCE
|
|
SHOW DATABASES;
|
|
Database
|
|
__recycle_bin__
|
|
information_schema
|
|
mtr
|
|
mysql
|
|
performance_schema
|
|
sys
|
|
test
|
|
# Now drop procedures and make sure re-install is done
|
|
DROP PROCEDURE sys.ps_setup_save;
|
|
DROP PROCEDURE sys.ps_setup_reload_saved;
|
|
# restart:--upgrade=FORCE
|
|
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'sys' AND ROUTINE_TYPE = 'PROCEDURE';
|
|
ROUTINE_NAME
|
|
create_synonym_db
|
|
diagnostics
|
|
execute_prepared_stmt
|
|
ps_setup_disable_background_threads
|
|
ps_setup_disable_consumer
|
|
ps_setup_disable_instrument
|
|
ps_setup_disable_thread
|
|
ps_setup_enable_background_threads
|
|
ps_setup_enable_consumer
|
|
ps_setup_enable_instrument
|
|
ps_setup_enable_thread
|
|
ps_setup_reload_saved
|
|
ps_setup_reset_to_default
|
|
ps_setup_save
|
|
ps_setup_show_disabled
|
|
ps_setup_show_disabled_consumers
|
|
ps_setup_show_disabled_instruments
|
|
ps_setup_show_enabled
|
|
ps_setup_show_enabled_consumers
|
|
ps_setup_show_enabled_instruments
|
|
ps_statement_avg_latency_histogram
|
|
ps_trace_statement_digest
|
|
ps_trace_thread
|
|
ps_truncate_all_tables
|
|
statement_performance_analyzer
|
|
table_exists
|
|
# Now drop functions and make sure re-install is done
|
|
DROP FUNCTION sys.format_bytes;
|
|
DROP FUNCTION sys.format_time;
|
|
# restart:--upgrade=FORCE
|
|
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'sys' AND ROUTINE_TYPE = 'FUNCTION';
|
|
ROUTINE_NAME
|
|
extract_schema_from_file_name
|
|
extract_table_from_file_name
|
|
format_bytes
|
|
format_path
|
|
format_statement
|
|
format_time
|
|
list_add
|
|
list_drop
|
|
ps_is_account_enabled
|
|
ps_is_consumer_enabled
|
|
ps_is_instrument_default_enabled
|
|
ps_is_instrument_default_timed
|
|
ps_is_thread_instrumented
|
|
ps_thread_account
|
|
ps_thread_id
|
|
ps_thread_stack
|
|
ps_thread_trx_info
|
|
quote_identifier
|
|
sys_get_config
|
|
version_major
|
|
version_minor
|
|
version_patch
|
|
# Now drop views and make sure re-install is done
|
|
DROP VIEW sys.host_summary;
|
|
DROP VIEW sys.processlist;
|
|
# restart:--upgrade=FORCE
|
|
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'sys' AND TABLE_TYPE = 'VIEW' ORDER BY TABLE_NAME;
|
|
TABLE_NAME
|
|
host_summary
|
|
host_summary_by_file_io
|
|
host_summary_by_file_io_type
|
|
host_summary_by_stages
|
|
host_summary_by_statement_latency
|
|
host_summary_by_statement_type
|
|
innodb_buffer_stats_by_schema
|
|
innodb_buffer_stats_by_table
|
|
innodb_lock_waits
|
|
io_by_thread_by_latency
|
|
io_global_by_file_by_bytes
|
|
io_global_by_file_by_latency
|
|
io_global_by_wait_by_bytes
|
|
io_global_by_wait_by_latency
|
|
latest_file_io
|
|
memory_by_host_by_current_bytes
|
|
memory_by_thread_by_current_bytes
|
|
memory_by_user_by_current_bytes
|
|
memory_global_by_current_bytes
|
|
memory_global_total
|
|
metrics
|
|
processlist
|
|
ps_check_lost_instrumentation
|
|
schema_auto_increment_columns
|
|
schema_index_statistics
|
|
schema_object_overview
|
|
schema_redundant_indexes
|
|
schema_table_lock_waits
|
|
schema_table_statistics
|
|
schema_table_statistics_with_buffer
|
|
schema_tables_with_full_table_scans
|
|
schema_unused_indexes
|
|
session
|
|
session_ssl_status
|
|
statement_analysis
|
|
statements_with_errors_or_warnings
|
|
statements_with_full_table_scans
|
|
statements_with_runtimes_in_95th_percentile
|
|
statements_with_sorting
|
|
statements_with_temp_tables
|
|
user_summary
|
|
user_summary_by_file_io
|
|
user_summary_by_file_io_type
|
|
user_summary_by_stages
|
|
user_summary_by_statement_latency
|
|
user_summary_by_statement_type
|
|
version
|
|
wait_classes_global_by_avg_latency
|
|
wait_classes_global_by_latency
|
|
waits_by_host_by_latency
|
|
waits_by_user_by_latency
|
|
waits_global_by_latency
|
|
x$host_summary
|
|
x$host_summary_by_file_io
|
|
x$host_summary_by_file_io_type
|
|
x$host_summary_by_stages
|
|
x$host_summary_by_statement_latency
|
|
x$host_summary_by_statement_type
|
|
x$innodb_buffer_stats_by_schema
|
|
x$innodb_buffer_stats_by_table
|
|
x$innodb_lock_waits
|
|
x$io_by_thread_by_latency
|
|
x$io_global_by_file_by_bytes
|
|
x$io_global_by_file_by_latency
|
|
x$io_global_by_wait_by_bytes
|
|
x$io_global_by_wait_by_latency
|
|
x$latest_file_io
|
|
x$memory_by_host_by_current_bytes
|
|
x$memory_by_thread_by_current_bytes
|
|
x$memory_by_user_by_current_bytes
|
|
x$memory_global_by_current_bytes
|
|
x$memory_global_total
|
|
x$processlist
|
|
x$ps_digest_95th_percentile_by_avg_us
|
|
x$ps_digest_avg_latency_distribution
|
|
x$ps_schema_table_statistics_io
|
|
x$schema_flattened_keys
|
|
x$schema_index_statistics
|
|
x$schema_table_lock_waits
|
|
x$schema_table_statistics
|
|
x$schema_table_statistics_with_buffer
|
|
x$schema_tables_with_full_table_scans
|
|
x$session
|
|
x$statement_analysis
|
|
x$statements_with_errors_or_warnings
|
|
x$statements_with_full_table_scans
|
|
x$statements_with_runtimes_in_95th_percentile
|
|
x$statements_with_sorting
|
|
x$statements_with_temp_tables
|
|
x$user_summary
|
|
x$user_summary_by_file_io
|
|
x$user_summary_by_file_io_type
|
|
x$user_summary_by_stages
|
|
x$user_summary_by_statement_latency
|
|
x$user_summary_by_statement_type
|
|
x$wait_classes_global_by_avg_latency
|
|
x$wait_classes_global_by_latency
|
|
x$waits_by_host_by_latency
|
|
x$waits_by_user_by_latency
|
|
x$waits_global_by_latency
|
|
# Now drop triggers and make sure re-install is done
|
|
DROP TRIGGER sys.sys_config_insert_set_user;
|
|
DROP TRIGGER sys.sys_config_update_set_user;
|
|
# restart:--upgrade=FORCE
|
|
SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = 'sys';
|
|
TRIGGER_NAME
|
|
sys_config_insert_set_user
|
|
sys_config_update_set_user
|
|
# Now drop the sys_config table and make sure re-install is done
|
|
DROP TABLE sys.sys_config;
|
|
# restart:--upgrade=FORCE
|
|
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'sys' AND TABLE_TYPE = 'BASE TABLE';
|
|
TABLE_NAME
|
|
sys_config
|
|
#
|
|
# WL#2284: Increase the length of a user name
|
|
#
|
|
# First downgrade all tables with user name columnd to 5.6
|
|
CALL mtr.add_suppression("'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.");
|
|
SET sql_mode= (SELECT replace(@@sql_mode,'NO_ZERO_DATE',''));
|
|
Warnings:
|
|
Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
|
|
SET sql_mode= (SELECT replace(@@sql_mode,'STRICT_TRANS_TABLES',''));
|
|
Warnings:
|
|
Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
|
|
CREATE TABLE mysql.db_backup SELECT * FROM mysql.db;
|
|
ALTER TABLE mysql.tables_priv
|
|
MODIFY User char(16) NOT NULL default '',
|
|
MODIFY Grantor char(77) DEFAULT '' NOT NULL;
|
|
ALTER TABLE mysql.columns_priv
|
|
MODIFY User char(16) NOT NULL default '';
|
|
ALTER TABLE mysql.user
|
|
MODIFY User char(16) NOT NULL default '';
|
|
ALTER TABLE mysql.db
|
|
MODIFY User char(16) NOT NULL default '';
|
|
ALTER TABLE mysql.procs_priv
|
|
MODIFY User char(16) binary DEFAULT '' NOT NULL,
|
|
MODIFY Grantor char(77) DEFAULT '' NOT NULL;
|
|
Warnings:
|
|
Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
|
|
#
|
|
# Bug #21762656 AFTER RUNNING MYSQL_UPGRADE PROXIES_PRIV USER COLUMNS
|
|
# ARE NOT UPDATED TO 32
|
|
#
|
|
ALTER TABLE mysql.proxies_priv MODIFY User char(16) binary DEFAULT '' NOT NULL;
|
|
Warnings:
|
|
Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
|
|
ALTER TABLE mysql.proxies_priv MODIFY Proxied_user char(16) binary DEFAULT '' NOT NULL;
|
|
Warnings:
|
|
Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
|
|
ALTER TABLE mysql.proxies_priv MODIFY Grantor char(77) DEFAULT '' NOT NULL;
|
|
# Tables with user name column downgraded to 5.6, now run mysql_upgrade
|
|
# restart:--upgrade=FORCE --sql-mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
|
|
TRUNCATE TABLE mysql.tables_priv;
|
|
INSERT INTO mysql.tables_priv (SELECT * FROM mysql.tmp_backup_tables_priv);
|
|
DROP TABLE mysql.tmp_backup_tables_priv;
|
|
TRUNCATE TABLE mysql.user;
|
|
INSERT INTO mysql.user (SELECT * FROM mysql.tmp_backup_user);
|
|
DROP TABLE mysql.tmp_backup_user;
|
|
TRUNCATE TABLE mysql.db;
|
|
INSERT INTO mysql.db SELECT * FROM mysql.db_backup;
|
|
DROP TABLE mysql.db_backup;
|
|
# restart:
|