WL#12571: Support fully qualified hostnames longer than 60 characters
Affects: Server-8.0 — Status: Complete
======= Summary ======= Hostnames are currently limited to 60 characters. This becomes a limiting factor when running MySQL in environments where hostnames tend to be long and the user is not in control of the domain and host names. So far, this resulted in, at least, the following bugs being reported: BUG#63814: Processlist hostname truncated at 60 characters BUG#90601: REPORT_HOST OPTION HAS A LIMIT OF 60 CHARACTERS RFC#1034(https://tools.ietf.org/html/rfc1034#section-3.1) states that: Each node has a label which is at most 63 octets long. A domain name is comprised by the list of labels from the node to the root of the tree. A domain name is represented by 255 or fewer octets (which includes the labels and the labels length). After this worklog is implemented, the server will be able to run with hostnames up to 255 characters. User Stories: U1. As a user, I want MySQL to support the maximum possible hostname length so I can take full advantage of my network setup. U2. As a user, I want the host information on tables to be capable of storing the maximum supported length for monitoring and automation purposes. U3. As a user, I want to be able to use MySQL in an environment where I may not control domain or host names.
FR1: The host name of user name provided in commands listed in section HLS 5.a) should accept 255 characters. FR2: The host name in definer specification in commands listed in section HLS 5.b) should accept 255 characters. FR3: The host name in server specification in commands listed in section HLS 5.c) should accept 255 characters. FR4: The host name in master/slave host specification in commands listed in section HLS 5.d) should accept 255 characters. FR5: The host name in GRANTEE column of I_S tables listed in section HLS 5.f) should accept 255 characters. FR6: The host name SHOW PROCESSLIST and I_S.PROCESSLIST should accept 255 characters. FR7: Use ASCII character set for fields discussed in FR1 to FR4. FR8: Upgrade from 5.7/8.0 to 8.0 version shall upgrade tables listed in HLS section 2. FR9: Upgrade from 5.7/8.0 to 8.0 version shall upgrade performance schema and sys schema tables. FR10: The host name shown from INFORMATION_SCHEMA.PROCESSLIST.HOST and INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_ATTEMPTS.USERHOST should accept 255 characters. FR11: Cross-version replication should exhibit following behavior, new->old replication: - old slave may not be able to register new master - old slave may not be able to report master's host name in SHOW commands, but it can truncate the hostname. old->new replication: - old master may not be able to register new slave - old master may not be able to report new slave's host name (p_s tables, SHOW commands), but it can truncate the hostname. FR12: Client connection using host name with 255 characters should be able to connect to MySQL server.
The following sections describes changes required. The section 1) speaks about hostname length and it's character set. section 2) lists all the metdata columns that are affected. Sections 3) lists performance schema and sys schema columns that are affected. Section 4) discusses about various macros that deal with hostnames. Section 5) lists all the commands that are related to hostnames. Section 6) discusses about changes related to plugin. Section 7) speaks about upgrade and Section 8) deals with fixing error messages to show long hostnames. 1. THE MAXIMUM HOSTNAME LENGTH: Note: 'man hostname' says following about length and charset: Each element of the hostname must be from 1 to 63 characters long and the entire hostname, including the dots, can be at most 253 characters long. Valid characters for hostnames are ASCII(7) letters from a to z, the digits from 0 to 9, and the hyphen (-). A hostname may no start with a hyphen. a. This WL sets HOSTNAME_LENGTH to 255. Note that this is the actual length available, and that room for a trailing '\0' is not included in this size. b. This WL uses ASCII charset for hostname metadata columns. And not for columns which store both username and hostname, e.g., Grantor and definer columns. See below for more details. This will also help reduce the index element size for various metadata tables. e.g., Index size for 'mysql.tables_priv' table would use 2121 bytes if the character set is utf8 when if 'Host' column is 255 characters and 'Grantor' column 288 characters. And InnoDB does not allow index size greater than 1982 bytes on MySQL instance using innodb_page_size=4k. 2. SYSTEM TABLES TO BE CHANGED: This section lists changes to metadata columns in server. a. SERVER METADATA TABLES: - Increased size to CHAR(255) and use CHARACTER SET ASCII COLLATE ascii_general_ci: mysql.db.Host mysql.user.Host mysql.default_roles.HOST mysql.default_roles.DEFAULT_ROLE_HOST mysql.role_edges.FROM_HOST mysql.role_edges.TO_HOST mysql.global_grants.HOST mysql.password_history.HOST mysql.servers.Host mysql.tables_priv.Host mysql.columns_priv.Host mysql.procs_priv.Host mysql.proxies_priv.Host mysql.proxies_priv.Proxied_host mysql.slave_master_info.host - Increased size to VARCHAR(288) and do not change the collation because the definer has user name part which can use utf8 characters: mysql.proxies_priv.Grantor mysql.procs_priv.Grantor mysql.tables_priv.Grantor - Update hardcoded schema definition in sql/sql_user_table.cc which are used to do table intact check by ACL code. Notes: - We may hit max key length issue. b. SERVER DATA DICTIONARY TABLES: - Increase size to VARCHAR(288) and do not change the collation because the definer has user name part which can use utf8 characters. mysql.events.definer mysql.routines.definer mysql.tables.view_definer mysql.triggers.definer - Increase the DD version number. 3. PERFORMANCE/SYS SCHEMA: - Host name column size in following PS tables are modified and the collation is changed to use CHARACTER SET ASCII. performance_schema.hosts performance_schema.accounts performance_schema.events_waits_summary_by_account_by_event_name performance_schema.events_errors_summary_by_host_by_error performance_schema.events_errors_summary_by_account_by_error performance_schema.events_stages_summary_by_account_by_event_name performance_schema.events_stages_summary_by_host_by_event_name performance_schema.events_statements_summary_by_account_by_event_name performance_schema.events_statements_summary_by_host_by_event_name performance_schema.events_transactions_summary_by_account_by_event_name performance_schema.events_transactions_summary_by_host_by_event_name performance_schema.events_waits_summary_by_host_by_event_name performance_schema.memory_summary_by_account_by_event_name performance_schema.memory_summary_by_host_by_event_name performance_schema.replication_connection_configuration performance_schema.replication_group_members performance_schema.setup_actors performance_schema.status_by_account performance_schema.status_by_host performance_schema.threads performance_schema.variables_info - Host name parameter size in following SYS function is modified sys.ps_is_account_enabled.in_host - Increase the PS_VERSION number. - Increase the sys.version.sys_version number. 4. MYSQL MACROS: We must adjust macros that relate to the context of this WL. 5. COMMANDS THAT ARE AFFECTS: a. Increases the host name length in user name. - CREATE USER - RENAME USER - ALTER USER - DROP USER - CREATE ROLE - DROP ROLE - SHOW CREATE USER - GRANT - SHOW GRANTS - REVOKE - SET ROLE - SET DEFAULT ROLE - SET PASSWORD b. Increases the host name length in definer specification. - CREATE EVENT - ALTER EVENT - SHOW EVENTS - CREATE PROCEDURE - SHOW CREATE PROCEDURE - CREATE FUNCTION - SHOW CREATE FUNCTION - CREATE VIEW - ALTER VIEW - SHOW CREATE VIEW - CREATE TRIGGER - SHOW CREATE TRIGGER c. Increases the host name length in server specification. - CREATE SERVER d. Increases the master/slave host name length. - SHOW SLAVE STATUS - CHANGE MASTER TO master_host e. Increases the host name length. - SHOW PROCESSLIST f. Increase GRANTEE column size to 288 in following I_S tables. - I_S.USER_PRIVILEGES.GRANTEE - I_S.SCHEMA_PRIVILEGES.GRANTEE - I_S.TABLE_PRIVILEGES.GRANTEE - I_S.COLUMN_PRIVILEGES.GRANTEE This change does not really need upgrade as these I_S tables work with ST_SCHEMA_TABLE framework where a temporary table is created upon every execution of query that uses these I_S tables. 6. Plugins: Increase mysql.audit_log_user.HOST column size to 255. This table is expected to be created by plugin user after the audit_log plugin is loaded. The hardcoded value of this column in source code is increased. ( in s_audit_log_filter_user_def ) 7. UPGRADE - Fix scripts/mysql_system_tables_fix.sql to upgrade mysql.* table listed in section 2.a above. - Adjust definitions of tables listed in section 2.b) above during upgrade from MySQL 5.7 to MySQL 8.0 or from MySQL pre-8.0.17 to MySQL 8.0.17. - Make sure performance schema definitions are upgraded. - Make sure sys schema definitions are upgraded. - Make sure mysql.audit_log_user.HOST column size is increase during upgrade if such table exists already. 8. Error messages: - Update error messages to display 255 characters for host name.
Copyright (c) 2000, 2021, Oracle Corporation and/or its affiliates. All rights reserved.