[receiver/mysql] error with mariadb LTS - converting NULL to int64 is unsupported

[tkoeppen]

Component(s)

receiver/mysql

What happened?

Background

With version 0.101.0 a new feature "add table metrics" was introduced (#32680):

7ddb350

We were also affected by a reported bug (#33271) and reverted to version 0.100.0.
This bug was fixed with version 0.104.0 by removing the order by clause (to fix unknown column TABLE_LENGTH)
(#33283)

Error description

We now tried out the new 0.104.0 version and found,
that with mariabdb we get the following error:

2024-07-22T12:37:58.314Z        error   mysqlreceiver@v0.104.0/scraper.go:421   Failed to fetch table size stats        {"kind": "receiver", "name": "mysql", "data_type": "metrics", "error": "sql: Scan error on column index 2, name \"TABLE_ROWS\": converting NULL to int64 is unsupported"}
2024-07-22T12:37:58.321Z        error   scraperhelper/scrapercontroller.go:197  Error scraping metrics  {"kind": "receiver", "name": "mysql", "data_type": "metrics", "error": "sql: Scan error on column index 2, name \"TABLE_ROWS\": converting NULL to int64 is unsupported", "scraper": "mysql"}

In mariadb (current release 11.4.2, LTS until 05/2029) it can happen,
that TABLES_ROWS column is null instead of 0.
We found that mysql.user table in information_schema has "NULL" TABLE_ROWS.

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, INDEX_LENGTH
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA NOT in ('information_schema', 'sys')
        and TABLE_ROWS is null;

This is causing the "converting NULL to int64 is unsupported" in mysqlreceiver@v0.104.0/scraper.go:421.

Expected Result

Even if this is a bug upstream (to have null TABLE_ROWS instead of 0 in MariaDB information_schema table),
the situation may exist with MariaDB,
and the mysqlconnector should handle null values to prevent null to int64 conversion in go.

Collector version

v0.104.0

Environment information

docker or k8s

OpenTelemetry Collector configuration

No response

[github-actions]

Pinging code owners:

• receiver/mysql: @djaglowski

See Adding Labels via Comments if you do not have permissions to add labels yourself.

[tkoeppen]

an updated query could fix this and convert null to 0 (or handle it afterwords in go)

SELECT TABLE_SCHEMA, TABLE_NAME, 
        COALESCE(TABLE_ROWS, 0) AS TABLE_ROWS, COALESCE(AVG_ROW_LENGTH, 0) AS AVG_ROW_LENGTH, 
        COALESCE(DATA_LENGTH, 0) AS DATA_LENGTH, COALESCE(INDEX_LENGTH, 0) AS INDEX_LENGTH
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA NOT in ('information_schema', 'sys');

[knarfli]

I want to take a look into it, please assign to me.

[knarfli]

can we change the client.go like this
option1:
change the SQS query

option 2:
handle it in the code

[djaglowski]

Thanks for reporting this.

@knarfli, I'll assign it to you. My inclination would be to handle it in the query but I don't feel strongly about it.

[knarfli]

Thanks @djaglowski . Yes, I like to handle it in query too.
As the client is only for mysql/mariadb, we needn't worry about the DB dependency and reuse of the SQL query.
Handling in DB make the code clean and simple.

[wsmoak01]

I have a .104 installation with the same issue and the error for MariaDB instances, since .104 was installed.