Enlarge `state` field from `information_schema.processlist`

[tangenta]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> desc information_schema.processlist;
+----------+---------------------+------+------+---------+-------+
| Field    | Type                | Null | Key  | Default | Extra |
+----------+---------------------+------+------+---------+-------+
| ID       | bigint(21) unsigned | 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(7)          | YES  |      | NULL    |       |
| INFO     | longtext            | YES  |      | NULL    |       |
| DIGEST   | varchar(64)         | YES  |      |         |       |
| MEM      | bigint(21) unsigned | YES  |      | NULL    |       |
| DISK     | bigint(21) unsigned | YES  |      | NULL    |       |
| TxnStart | varchar(64)         | NO   |      |         |       |
+----------+---------------------+------+------+---------+-------+
12 rows in set (0.09 sec)

mysql> select state from information_schema.processlist;
+------------+
| state      |
+------------+
| autocommit |
+------------+

The column type of state is varchar(7) but the length of "autocommit" is 10.

2. What did you expect to see? (Required)

The length should be at least 20(ref).

3. What did you see instead (Required)

NA

4. What is your TiDB version? (Required)

commit f830d012d29c6add78053da4c8432275067aaae0 (HEAD)
Author: wjhuang2016 <huangwenjun1997@gmail.com>
Date:   Fri Sep 24 16:58:11 2021 +0800

    refine comment

    Signed-off-by: wjhuang2016 <huangwenjun1997@gmail.com>

[kennytm]

on MySQL 8 the field is varchar(64).

mysql> explain information_schema.processlist;
+---------+-----------------+------+-----+---------+-------+
| Field   | Type            | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+-------+
| ID      | bigint unsigned | NO   |     |         |       |
| USER    | varchar(32)     | NO   |     |         |       |
| HOST    | varchar(261)    | NO   |     |         |       |
| DB      | varchar(64)     | YES  |     |         |       |
| COMMAND | varchar(16)     | NO   |     |         |       |
| TIME    | int             | NO   |     |         |       |
| STATE   | varchar(64)     | YES  |     |         |       |
| INFO    | varchar(65535)  | YES  |     |         |       |
+---------+-----------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

Considering only the common fields

Field	TiDB 5.2	MySQL 8.0	MariaDB 10.4
ID	bigint unsigned	bigint unsigned	bigint	✓
USER	varchar(16)	varchar(32)	varchar(128)	✗
HOST	varchar(64)	varchar(261)	varchar(64)	✓?
DB	varchar(64)	varchar(64)	varchar(64)	✓
COMMAND	varchar(16)	varchar(16)	varchar(16)	✓
TIME	int	int	int	✓
STATE	varchar(7)	varchar(64)	varchar(64)	✗
INFO	longtext	varchar(65535)	longtext	✓

[morgo]

Host was only recently expanded in https://github.com/pingcap/tidb/pull/27887

It might be worth checking if other infoschema tables mention host, since we missed this in the review.

[kennytm]

The HOST thing was upgraded on the MySQL side in https://dev.mysql.com/worklog/task/?id=12571. Comparing with the WL list, at least the following tables are missed as of 9955eeebf:

• mysql.default_roles.{HOST, DEFAULT_ROLE_HOST} (60 → 255)
• mysql.role_edges.{FROM_HOST, TO_HOST} (60 → 255)
• mysql.tables_priv.Grantor (77 → 288)
• performance_schema.setup_actors.HOST (60 → 255)
• information_schema.user_privileges.GRANTEE (81 → 288)
• information_schema.schema_privileges.GRANTEE (81 → 288)
• information_schema.table_privileges.GRANTEE (81 → 288)
• information_schema.column_privileges.GRANTEE (81 → 288)

there should be more since the WL doesn't list much information_schema objects.

this should be filed into another issue?