MySQL Bugs: #63814: Processlist hostname truncated at 60 characters

Bug #63814	Processlist hostname truncated at 60 characters
Submitted:	20 Dec 2011 21:43	Modified:	16 Apr 2019 18:19
Reporter:	Tyler Poland	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S4 (Feature request)
Version:	5.1.55	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In the output of Host in `show processlist` or HOST from information_schema.PROCESSLIST the value is truncated to 60 characters and then a ':' is appended to the end. DNS hostnames are allowed to have 63 characters per segment with a maximum length of 253 characters. When the hostname is truncated in this fashion the port number for the TCP connection is also not visible.

For monitoring and automation purposes it would be useful if the host information was capable of storing the DNS maximum length as well as have enough room to show the tcp port. 

How to repeat:
Connect to the database server from a host having a hostname of maximum legal DNS length (or at least longer than 60 characters) and view the output of the following.

`show processlist` view the host information
select HOST from information_schema.PROCESSLIST;

Suggested fix:
Increase the size of the hostname field to allow storage of full legal dns names. Possibly split out tcp port information to another field. `show processlist` could still provide short form as long as there is a way to get to full hostname (eg. via information_schema and `show full processlist`).

In 5.5.58 at least there should be no truncation, according to the following results:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.1.58-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> desc information_schema.processlist;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| ID      | bigint(4)   | NO   |     | 0       |       |
| USER    | varchar(16) | NO   |     |         |       |
| HOST    | varchar(64) | NO   |     |         |       |
| DB      | varchar(64) | YES  |     | NULL    |       |
| COMMAND | varchar(16) | NO   |     |         |       |
| TIME    | int(7)      | NO   |     | 0       |       |
| STATE   | varchar(64) | YES  |     | NULL    |       |
| INFO    | longtext    | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
8 rows in set (0.03 sec)

mysql> exit
Bye

Note 64 above, also for SHOW PROCESSLIST I do not see any limit of 60:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot --column-type-in
fo -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.1.58-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show processlist;
Field   1:  `Id`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     11
Max_length: 2
Decimals:   0
Flags:      NOT_NULL BINARY NUM

Field   2:  `User`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     48
Max_length: 4
Decimals:   31
Flags:      NOT_NULL

Field   3:  `Host`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     192
Max_length: 14
Decimals:   31
Flags:      NOT_NULL
...

But anyway you are right about entire/full hostname (http://tools.ietf.org/html/rfc1034#section-3.1): 

"To simplify implementations, the total number of octets that represent a
domain name (i.e., the sum of all label octets and label lengths) is
limited to 255."

Valeriy,

Wouldn't that still result in truncation at 192 characters?

Thank you,
Tyler

Posted by developer:
 
Fixed in wl#12571.

Posted by developer:
 
Fixed in 8.0.17.

The maximum permitted length of host names throughout MySQL has been
raised to 255 ASCII characters, up from the previous limit of 60
characters. This applies to, for example, host name-related columns
in the data dictionary, mysql system schema, Performance Schema,
INFORMATION_SCHEMA, and sys schema; the MASTER_HOST value for the
CHANGE MASTER TO statement; the Host column in SHOW PROCESSLIST
statement output; host names in account names (such as used in
account-management statements and in DEFINER attributes); and host
name-related command options and system variables.

Applications that expect host names to be a maximum of 60 characters
should be adjusted to account for this change.