[MDEV-29651] information_schema.processlist.host is empty when server started with --skip-grant-tables Created: 2022-09-28  Updated: 2023-12-13

Status: Open
Project: MariaDB Server
Component/s: Documentation
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Daniel Lenski Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: Documentation, information_schema

Attachments: PNG File screenshot-1.png    
Issue Links:
PartOf

 Description   

1. MariaDB docs on information_schema.processlist do not mention the fact that :port is appended to the .host column, when connecting via TCP:

mysql --host 127.0.0.1 --port 3307 --protocol=tcp -uroot -pXXXXXXX -e "select @@version; select * from information_schema.processlist";
 
+----------------+
 
| @@version      |
 
+----------------+
 
| 10.6.8-MariaDB |
 
+----------------+
 
+----+----------+-----------------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+-------+
 
| ID | USER     | HOST            | DB   | COMMAND | TIME | STATE                | INFO                                         | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY                                  | TID   |
 
+----+----------+-----------------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+-------+
 
| 13 | root     | localhost:58162 | NULL | Query   |    0 | Filling schema table | select * from information_schema.processlist |   0.401 |     0 |         0 |    0.000 |      139544 |          139544 |             0 |        6 | select * from information_schema.processlist | 27930 |
 
+----+----------+-----------------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+-------+
 
1 row in set (0.001 sec)

The MySQL 8.0 docs on information_schema.processlist do mention the presence of the TCP port.

2. When the server is started with --skip-grant-tables, then information_schema.processlist.host is '' (empty string).

$ mysql --host 127.0.0.1 --port 3306 --protocol=tcp -uroot -pXXXXX  -e 'select * from information_schema.processlist';
 
+----+----------+------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+------+
 
| ID | USER     | HOST | DB   | COMMAND | TIME | STATE                | INFO                                         | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY                                  | TID  |
 
+----+----------+------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+------+
 
|  4 | root     |      | NULL | Query   |    0 | Filling schema table | select * from information_schema.processlist |   0.257 |     0 |         0 |    0.000 |      123176 |          123176 |             0 |        3 | select * from information_schema.processlist | 6999 |
 
+----+----------+------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+------+
 
 
 
$ mysql --protocol=socket --socket="$PATH/mysql.sock" -uroot -pXXXXX -e 'select * from information_schema.processlist';
 
+----+----------+------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+------+
 
| ID | USER     | HOST | DB   | COMMAND | TIME | STATE                | INFO                                         | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY                                  | TID  |
 
+----+----------+------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+------+
 
|  6 | root     |      | NULL | Query   |    0 | Filling schema table | select * from information_schema.processlist |   0.244 |     0 |         0 |    0.000 |      123176 |          123176 |             0 |        7 | select * from information_schema.processlist | 6999 |
 
+----+----------+------+------+---------+------+----------------------+----------------------------------------------+---------+-------+-----------+----------+-------------+-----------------+---------------+----------+----------------------------------------------+------+

The lack of documentation about the TCP port is a potential security hazard: if users are looking for connections as root from the local system, they may use a query like the following, and overlook TCP-based connections:

select * from information_schema.processlist where user='root' and host='localhost';   -- fails to notice TCP connections from localhost
 
select * from information_schema.processlist where host regexp '^localhost(:[0-9]+)?$';  -- does include TCP connections from localhost

The fact that host='' when the server is started with --skip-grant-tables feels more like a bug/inadvertent omission. Likely caused by a nonexistent JOIN with mysql.user.

 Comments   
Comment by Daniel Lenski [ 2022-09-28 ]

Not clear how to deal with (2), but I updated the processlist documentation to provide more information on (1):

Comment by Ian Gilfillan [ 2022-10-05 ]

The documentation issue has been addressed, so I've unassigned and renamed the issue to highlight (2).

Comment by Daniel Black [ 2022-10-19 ]

It seems possible that with --skip-grant-tables the THD isn't populated with the host and that's why its empty. Is it important to fix or can this just be documented too?

Comment by Daniel Lenski [ 2022-10-19 ]

It seems possible that with --skip-grant-tables the THD isn't populated with the host and that's why its empty.

Shouldn't it be?

Is it important to fix or can this just be documented too?

"Documenting the inconsistent behavior" would be better than "undocumented inconsistent behavior", but "consistent behavior" would be a lot better than just "documenting the inconsistent behavior."

As in the example I gave above, users may have long-existing queries where they expect the value of host="localhost" for users connected via Unix socket, and likely will not realize that skip-grant-tables interferes with this even if documentation is added.

Generated at Thu Feb 08 10:10:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.