Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

MySQL Receiver expects non-existent column in information_schema #33271

Closed
heaje opened this issue May 28, 2024 · 2 comments
Closed

MySQL Receiver expects non-existent column in information_schema #33271

heaje opened this issue May 28, 2024 · 2 comments
Assignees
@heaje
Labels
bug Something isn't working receiver/mysql

Comments

@heaje
Copy link

heaje commented May 28, 2024

Component(s)

receiver/mysql

What happened?

Description

When running the MySQL receiver changes introduced in 0.101.0, the OTel collector produces errors like these:

May 28 19:55:31 ue1010217215146 otelcol-contrib[92515]: 2024-05-28T19:55:31.661Z        error        mysqlreceiver@v0.101.0/scraper.go:421        Failed to fetch table size stats        {"kind": "receiver", "name": "mysql/a.b.c:3306", "data_type": "metrics", "error": "Error 1054 (42S22): Unknown column 'TABLE_LENGTH' in 'order clause'"}

This error occurs when the collector is used to extract metrics from MySQL 8.0.35. I do not know about other MySQL versions. These errors do not occur after downgrading to OTel collector 0.100.0.

Steps to Reproduce

  1. Set up a MySQL 8.0.35 instance to monitor.
  2. Enable the MySQL receiver in OTel Collector 0.101.0 with a basic configuration like this:
mysql/a.b.c:3306:
    endpoint: a.b.c:3306
    collection_interval: 60s
    username: yyyyyyyyyy
    password: xxxxxxxxx
  1. Start the Collector and observe the mentioned error in the log output from OTel collector.
  2. Downgrade to OTel Collector 0.100.0 and use the exact same configuration.
  3. Restart the Collector and observe that no error occurs.

Expected Result

  1. No error should occur

Actual Result

  1. The error indicated in the description happens.

Collector version

0.101.0

Environment information

Environment

OS: Rocky 8.7
Collector installed using an RPM from https://github.com/open-telemetry/opentelemetry-collector-releases/releases (specific RPM is otelcol-contrib_0.101.0_linux_amd64.rpm)

OpenTelemetry Collector configuration

receivers:
  mysql/a.b.c:3306:
    endpoint: a.b.c:3306
    collection_interval: 60s
    username: yyyyyyyyyy
    password: xxxxxxxxx

Log output

May 28 19:55:31 ue1010217215146 otelcol-contrib[92515]: 2024-05-28T19:55:31.661Z        error        mysqlreceiver@v0.101.0/scraper.go:421        Failed to fetch table size stats        {"kind": "receiver", "name": "mysql/a.b.c:3306", "data_type": "metrics", "error": "Error 1054 (42S22): Unknown column 'TABLE_LENGTH' in 'order clause'"}

Additional context

No response
@heaje heaje added bug Something isn't working needs triage New item requiring triage labels May 28, 2024
@github-actions GitHub Actions
Copy link
Contributor

Pinging code owners:

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

@terakoya76 terakoya76 mentioned this issue May 29, 2024
Merged
@JaredTan95 JaredTan95 removed the needs triage New item requiring triage label May 29, 2024
djaglowski added a commit that referenced this issue May 30, 2024
@terakoya76 @djaglowski @crobert-1
[receiver/mysql] fix: remove the order by clause for non-existent col…
e912bcd 
…umn (#33283)

**Description:** <Describe what has changed.>
<!--Ex. Fixing a bug - Describe the bug and how this fixes the issue.
Ex. Adding a feature - Explain what this achieves.-->

Remove the unnecessary order by clause causing undefined reference
error.
This order by clause was introduced in [this
commit](bbcb16c).
In [the next
commit](5437618),
the author made a change to the metrics model, but I think he forgot to
remove the order by clause at that time.

I think it could be changed to order by another column, but I didn't
really feel the need to keep it sorted, so I removed it.


**Link to tracking Issue:**
#33271

**Testing:** <Describe what testing was performed and which tests were
added.>

Tested working as expected with mysql@8.0.35 which is reported version
in the issue.

Also it can be tested as sql.
```sql
-- before
mysql> 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') ORDER BY TABLE_LENGTH DESC;
ERROR 1054 (42S22): Unknown column 'TABLE_LENGTH' in 'order clause'

-- after
mysql> 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');
+--------------------+------------------------------------------------------+------------+----------------+-------------+--------------+
| TABLE_SCHEMA       | TABLE_NAME                                           | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | INDEX_LENGTH |
+--------------------+------------------------------------------------------+------------+----------------+-------------+--------------+
| mysql              | innodb_table_stats                                   |          2 |           8192 |       16384 |            0 |
| mysql              | innodb_index_stats                                   |          6 |           2730 |       16384 |            0 |
| performance_schema | cond_instances                                       |        256 |              0 |           0 |            0 |
| performance_schema | error_log                                            |          9 |              0 |           0 |            0 |
| performance_schema | events_waits_current                                 |       1536 |              0 |           0 |            0 |
(snip)
```

**Documentation:** <Describe the documentation added.>

---------

Co-authored-by: Daniel Jaglowski <jaglows3@gmail.com>
Co-authored-by: Curtis Robert <crobert@splunk.com>
@crobert-1
Copy link
Member

Resolved by #33283

@LucaLanziani LucaLanziani mentioned this issue Jun 2, 2024
Closed
cparkins pushed a commit to AmadeusITGroup/opentelemetry-collector-contrib that referenced this issue Jul 11, 2024
@terakoya76 @djaglowski
@crobert-1 @cparkins
[receiver/mysql] fix: remove the order by clause for non-existent col…
7533ca1 
…umn (open-telemetry#33283)

**Description:** <Describe what has changed.>
<!--Ex. Fixing a bug - Describe the bug and how this fixes the issue.
Ex. Adding a feature - Explain what this achieves.-->

Remove the unnecessary order by clause causing undefined reference
error.
This order by clause was introduced in [this
commit](open-telemetry@bbcb16c).
In [the next
commit](open-telemetry@5437618),
the author made a change to the metrics model, but I think he forgot to
remove the order by clause at that time.

I think it could be changed to order by another column, but I didn't
really feel the need to keep it sorted, so I removed it.


**Link to tracking Issue:**
open-telemetry#33271

**Testing:** <Describe what testing was performed and which tests were
added.>

Tested working as expected with mysql@8.0.35 which is reported version
in the issue.

Also it can be tested as sql.
```sql
-- before
mysql> 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') ORDER BY TABLE_LENGTH DESC;
ERROR 1054 (42S22): Unknown column 'TABLE_LENGTH' in 'order clause'

-- after
mysql> 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');
+--------------------+------------------------------------------------------+------------+----------------+-------------+--------------+
| TABLE_SCHEMA       | TABLE_NAME                                           | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | INDEX_LENGTH |
+--------------------+------------------------------------------------------+------------+----------------+-------------+--------------+
| mysql              | innodb_table_stats                                   |          2 |           8192 |       16384 |            0 |
| mysql              | innodb_index_stats                                   |          6 |           2730 |       16384 |            0 |
| performance_schema | cond_instances                                       |        256 |              0 |           0 |            0 |
| performance_schema | error_log                                            |          9 |              0 |           0 |            0 |
| performance_schema | events_waits_current                                 |       1536 |              0 |           0 |            0 |
(snip)
```

**Documentation:** <Describe the documentation added.>

---------

Co-authored-by: Daniel Jaglowski <jaglows3@gmail.com>
Co-authored-by: Curtis Robert <crobert@splunk.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@heaje heaje

Labels
bug Something isn't working receiver/mysql
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@heaje @JaredTan95 @crobert-1