sql: Scan error on column index 9, name \"current_memory\": converting driver.Value type []uint8 (\"-157874864\") to a uint64: invalid syntax

Host operating system: output of uname -a

Linux xxx 5.4.0-153-generic #170-Ubuntu SMP Fri Jun 16 13:43:31 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux

mysqld_exporter version: output of mysqld_exporter --version

0.15.0

MySQL server version

mysql Ver 15.1 Distrib 10.11.4-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

mysqld_exporter command line flags

--collect.auto_increment.columns --collect.info_schema.processlist --collect.mysql.user --collect.sys.user_summary

What did you do that produced an error?

Adding --collect.sys.user_summary flag

What did you expect to see?

I expect no error

What did you see instead?

sql: Scan error on column index 9, name "current_memory": converting driver.Value type []uint8 ("-157874864") to a uint64: invalid syntax

[tspotts]

I'm also seeing this same error with the --collect.sys.user_summary flag:

level=error msg="Error from scraper" scraper=sys.user_summary target=localhost:3306 err="sql: Scan error on column index 9, name \"current_memory\": converting driver.Value type []uint8 (\"-239934168\") to a uint64: invalid syntax"

Server version: 8.0.28-20 Percona Server (GPL), Release 20, Revision fd4b5a776a6

Looks like the current_memory in the sys.user_summary view is a negative value -228.82 MiB...

I did some analysis.
It seems to be a bug at mariadb: https://jira.mariadb.org/browse/MDEV-23936

SELECT
	MIN(CURRENT_COUNT_USED) ,
	MAX(CURRENT_COUNT_USED),
	MIN(CURRENT_NUMBER_OF_BYTES_USED) ,
	MAX(CURRENT_NUMBER_OF_BYTES_USED)
FROM
	performance_schema.memory_summary_by_thread_by_event_name

The problems are those columns.

Which version of mysql/mariadb shall it be working?

[zxzharmlesszxz]

Maybe need switch uint64 -> int64 and not this field only.
I think that negative values of current_memory likes look as shortage of memory
Look on table definition

mysql> describe x$user_summary;
+------------------------+---------------+------+-----+---------+-------+
| Field                  | Type          | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| user                   | varchar(32)   | YES  |     | NULL    |       |
| statements             | decimal(64,0) | YES  |     | NULL    |       |
| statement_latency      | decimal(64,0) | YES  |     | NULL    |       |
| statement_avg_latency  | decimal(65,4) | NO   |     | 0.0000  |       |
| table_scans            | decimal(65,0) | YES  |     | NULL    |       |
| file_ios               | decimal(64,0) | YES  |     | NULL    |       |
| file_io_latency        | decimal(64,0) | YES  |     | NULL    |       |
| current_connections    | decimal(41,0) | YES  |     | NULL    |       |
| total_connections      | decimal(41,0) | YES  |     | NULL    |       |
| unique_hosts           | bigint        | NO   |     | 0       |       |
| current_memory         | decimal(63,0) | YES  |     | NULL    |       |
| total_memory_allocated | decimal(64,0) | YES  |     | NULL    |       |
+------------------------+---------------+------+-----+---------+-------+

[staylorx]

I want to +1 this issue. I have the error in my logs,

{"caller":"exporter.go:173","err":"sql: Scan error on column index 9, name \"current_memory\": converting driver.Value type []uint8 (\"-3094296\") to a uint64: │
│  invalid syntax","level":"error","msg":"Error from scraper","scraper":"sys.user_summary","target":"10.197.22.198:3306","ts":"2024-06-07T14:19:03.681Z"}

The circumstances are different though and not fixable on my side. We're using a managed MySQL instance from Oracle Cloud. Works fine. But we've had to get used to a few changes in user permission grants and some triggers, because the root user isn't really a root anymore. That's managed.

This error looks like more of the same. I might recommend setting the number to zero, but that's a bad signal for others. Maybe a fixed number like 99999 which should be a signal for folks that the number has gone negative and therefore the system doesn't support the metric. Another idea might be to make this or other metrics optional somehow. Config doc? command line? Not sure.

In our case the stdout of the logger is itself being scraped and shot off to a log collector. So we're a little noisy in our production of Prometheus metrics. Would love to quiet this down. If you have any ideas or thoughts, I might be able to take a look at it for a PR. I'd just need a direction consistent with how you've got things now.

Thanks!

[zxzharmlesszxz]

Negative value of current_memory indicates us what problem exists with mysql configuration - threads, memory pools and limits, cache, connection params etc.
Maybe need to alert for negative value(when it's switched uint64->int64) like this - Wrong memory/connection cache/limit/pool size.

[staylorx]

Negative value of current_memory indicates us what problem exists with mysql configuration - threads, memory pools and limits, cache, connection params etc. Maybe need to alert for negative value(when it's switched uint64->int64) like this - Wrong memory/connection cache/limit/pool size.

In my case it doesn't seem like a problem with the configuration, just that managed service of MySQL doesn't expose the metric to us. Maybe a different flavour of exporter for MySQL as a Service?

[zxzharmlesszxz]

Negative value of current_memory indicates us what problem exists with mysql configuration - threads, memory pools and limits, cache, connection params etc. Maybe need to alert for negative value(when it's switched uint64->int64) like this - Wrong memory/connection cache/limit/pool size.

In my case it doesn't seem like a problem with the configuration, just that managed service of MySQL doesn't expose the metric to us. Maybe a different flavour of exporter for MySQL as a Service?

I talk about patched build(current_memory uint64->int64)

[SuperQ]

Fixed in #985