Can't read from information_schema.table_statistics

[aazon]

Hello,

I created a dedicated user prom with all required grants.
However there is no data related to collect.info_schema.tables in Prometheus. I tried to go inside the code in this repo and found out that the following query is used:

SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  ROWS_READ,
  ROWS_CHANGED,
  ROWS_CHANGED_X_INDEXES
  FROM information_schema.table_statistics

When I run this query on behalf of root I do receive results

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM information_schema.table_statistics;
+----------+
| count(*) |
+----------+
|       30 |
+----------+
1 row in set (0.00 sec)

However when I run this query on behalf of prom` I receive an empty set.

mysql> select user();
+----------------+
| user()         |
+----------------+
| prom@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM information_schema.table_statistics;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

I think this is the problem why prom can not get desired information and push it further to Prometheus. Could you please help to figure out what additional grants should user prom get in order to be able to fetch results from information_schema.table_statistics?

Current privileges are the following:

mysql> show grants for 'prom'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for prom@localhost                                                                                                  |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'prom'@'localhost' IDENTIFIED BY PASSWORD <secret> WITH MAX_USER_CONNECTIONS 3 |
| GRANT SELECT ON `performance_schema`.* TO 'prom'@'%'                                                                       |
+----------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.6.29-76.2-log |
+-----------------+
1 row in set (0.00 sec)

[roman-vynar]

No, flag -collect.info_schema.tables stands for this query https://github.com/prometheus/mysqld_exporter/blob/master/collector/info_schema_tables.go#L15

Table information_schema.table_statistics is used by this collector https://github.com/prometheus/mysqld_exporter/blob/master/collector/info_schema_tablestats.go#L12 and flag -collect.info_schema.tablestats which requires @@userstat enabled on MySQL and the corresponding plugins (Percona Server/MariaDB).

[aazon]

Sorry my bad, I pasted the wrong flag name.

Anyway I have a situation that I granted all permissions listed in README.md but mysql user still can't get this information. Could you please point out what is wrong with my setup?

mysql> SHOW VARIABLES LIKE 'userstat';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| userstat      | ON    |
+---------------+-------+
1 row in set (0.00 sec)

[roman-vynar]

Weird, I reproduced the same issue.

You can query client/user_statistics tables (they require PROCESS or SUPER priv) but not table_statistics which does not require anything according to the docs. Moreover, you can't grant privs to information_schema db or tables separately as this is built-in one.

Looks like the fix is to grant SELECT on *.* to access the tables, including information_schema.tables (all data) or information_schema.table_statistics.

[aazon]

GRANT SELECT ON *.* TO 'prom'@'localhost'; helped to solve this issue.

Thanks for the tip.

PS: I suppose it would make sense to update README.md as well.

[SuperQ]

Hrm, I wanted to avoid having to recommend granting *.* to the exporter, to avoid any possibility of information leak through the exporter.

[aazon]

When I try to grant on information_schema db only I receive an error:

mysql> GRANT SELECT ON `information_schema`.* TO 'prom'@'localhost';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

I'm wondering would it make sense to list all tables from information_schema DB and try to grant access on those tables only?

[roman-vynar]

It is neither possible. No grants work against information_schema. The idea behind this is if you can't select from tables you should not know the list of them available.

[SuperQ]

Fixed with dd111ec