Error 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='

[leeleahulkcs]

Describe the bug
Getting this error when querying the ndbinfo.resources table of MySQL NDB Cluster:

time=2025-10-11T19:28:36.954Z level=ERROR source=promhttp.go:51 msg="Error gathering metrics" error="[from Gatherer #1] [target=ndbinfo_sql2,collector=ndbinfo_stats,query=TRANSACTION_MEMORY] Error 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='"

The query is:

select node_id, resource_name, reserved, used, max, spare from ndbinfo.resources where resource_name = 'TRANSACTION_MEMORY'

To Reproduce
Steps to reproduce the behavior:

1. Create a metric with that query
2. Re-start sql_exporter
3. See error

Expected behavior
The error to not occur and the data get is exported.

[leeleahulkcs]

With some testing in MySQL Workbench...

This works

SET NAMES utf8mb4 collate utf8mb4_0900_ai_ci
;
select node_id, resource_name, reserved, used, max, spare
from ndbinfo.resources
where resource_name = 'QUERY_MEMORY'
;

This fails with the error (also reported above):

Error Code: 1267. Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='

SET NAMES utf8mb4 collate utf8mb4_general_ci
;
select node_id, resource_name, reserved, used, max, spare
from ndbinfo.resources
where resource_name = 'QUERY_MEMORY'
;

This works to setting collate on the string pass to where...

SET NAMES utf8mb4 collate utf8mb4_general_ci
;
select node_id, resource_name, reserved, used, max, spare
from ndbinfo.resources
where resource_name = 'QUERY_MEMORY' collate utf8mb4_0900_ai_ci
;

When I change the query in the metric accordingly:

select node_id, resource_name, reserved, used, max, spare
from ndbinfo.resources
where resource_name = 'TRANSACTION_MEMORY' collate utf8mb4_0900_ai_ci

I get a new error:

time=2025-10-11T19:48:24.211Z level=ERROR source=promhttp.go:51 msg="Error gathering metrics" error="[from Gatherer #1] [target=ndbinfo_sql2,collector=ndbinfo_stats,query=TRANSACTION_MEMORY] sql: Scan error on column index 1, name \"resource_name\": converting driver.Value type []uint8 (\"TRANSACTION_MEMORY\") to a float64: invalid syntax"

[burningalchemist]

Hi @leeleahulkcs,
Could you please also share the configuration snippet from sql_exporter which describes the metric?

This looks to me like a misconfiguration and not a bug, I'm happy to assist. 👍

Also, to specify the collation you can specify the connection parameter (using ?param=value in DSN), please check it out in the driver documentation here - https://github.com/go-sql-driver/mysql?tab=readme-ov-file#unicode-support

[leeleahulkcs]

I run sql_exporter with the docker image. Pulled latest today.

docker container run --rm --name ndbinfo --net mysql-ndb --ip x.x.x.52 -v '$(pwd):/data' burningalchemist/sql_exporter:latest -config.file /data/ndbinfo_sql2.yml

The file /data/ndbinfo_sql2.yml:

global:
  scrape_timeout: 10s
  scrape_timeout_offset: 500ms
  min_interval: 0s
  max_connections: 3
  max_idle_connections: 3

target:
  name: ndbinfo_sql2
  data_source_name: 'mysql://xxxx:[email protected]:3306/ndbinfo'
  collectors: [ndbinfo_stats]
  enable_ping: true

collector_files:
 - "ndbinfo_stats.collector.yml"

The file ndbinfo_stats.collector.yml:

collector_name: ndbinfo_stats

metrics:
  - metric_name: TRANSACTION_MEMORY
    type: counter
    help: 'Memory allocated for transactions on this data node. This can be controlled using the TransactionMemory configuration parameter.'
    static_labels:
      counter: TRANSACTION_MEMORY
    value_label: resource_name
    values:
      - node_id
      - resource_name
      - reserved
      - used
      - max
      - spare
    query: |
      select node_id, resource_name, reserved, used, max, spare
      from ndbinfo.resources
      where resource_name = 'TRANSACTION_MEMORY'

[leeleahulkcs]

Hi @burningalchemist,

Also, to specify the collation you can specify the connection parameter (using ?param=value in DSN), please check it out in the driver documentation here - https://github.com/go-sql-driver/mysql?tab=readme-ov-file#unicode-support

This is new to me and I have tried this, but without success.

Without this setting (what I posted above), I get this error (the first error):

time=2025-10-11T20:28:36.014Z level=ERROR source=promhttp.go:51 msg="Error gathering metrics" error="[from Gatherer #1] [target=ndbinfo_sql2,collector=ndbinfo_stats,query=TRANSACTION_MEMORY] Error 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='"

When I add just ?collation=utf8mb4_0900_ai_ci to the DSN, I get this error (the second error):

time=2025-10-11T20:32:03.549Z level=ERROR source=promhttp.go:51 msg="Error gathering metrics" error="[from Gatherer #1] [target=ndbinfo_sql2,collector=ndbinfo_stats,query=TRANSACTION_MEMORY] sql: Scan error on column index 1, name \"resource_name\": converting driver.Value type []uint8 (\"TRANSACTION_MEMORY\") to a float64: invalid syntax"

When I add ?charset=utf8mb4&collation=utf8mb4_0900_ai_ci to the DSN, I get the same second error.

I've also tried this with success in MySQL Workbench, but it fails with the second error in the metric query:

SET NAMES utf8mb4 collate utf8mb4_general_ci
;
select *
from (
select node_id, resource_name collate utf8mb4_0900_ai_ci as resource_name, reserved, used, max, spare
from ndbinfo.resources
) a
where resource_name = 'QUERY_MEMORY'
;

It seems to me, that I've found three different ways to resolve the Illegal mix of collations error (collate on where, collate on field in subquery, and collate as DSN param), but all three ways trigger the second error Scan error on column index which I have no idea how to work around.

[burningalchemist]

@leeleahulkcs,

I'd keep that parameter that returns invalid syntax error for now.

I still would like to see the configuration snippet from sql_exporter configuration file. This is where I expect the find the misconfiguration. Could you please share it?

Just to add a bit more details:

• The first error resulted during the query execution (so it comes from the driver unable to execute the query).
• The second error happens when the result set is returned, however it cannot be converted to a Prometheus-formatted metric due to some issues.

Sometimes, it's about changing metric configuration on the sql_exporter side, sometimes it requires adjusting the query a bit. 🙂👍

[leeleahulkcs]

If it helps, I'm testing against MySQL NDB Cluster 8.4.6 LTS.
https://dev.mysql.com/doc/refman/8.4/en/mysql-cluster.html
https://dev.mysql.com/downloads/cluster/

[leeleahulkcs]

@leeleahulkcs,

I'd keep that parameter that returns invalid syntax error for now.

I still would like to see the configuration snippet from sql_exporter configuration file. This is where I expect the find the misconfiguration. Could you please share it?

Yes. I have shared my configuration above. #820 (comment)

[leeleahulkcs]

Just to add a bit more details:

• The first error resulted during the query execution (so it comes from the driver unable to execute the query).
• The second error happens when the result set is returned, however it cannot be converted to a Prometheus-formatted metric due to some issues.

Sometimes, it's about changing metric configuration on the sql_exporter side, sometimes it requires adjusting the query a bit. 🙂👍

I've also tried casting resource_name to BINARY - both the select and where statements. This results in the second error happening and without having to specify any charset or collation values anywhere in the query or DSN.

select node_id, cast(resource_name as binary) as resource_name, reserved, used, max, spare
from ndbinfo.resources
where cast(resource_name as binary) = 'TRANSACTION_MEMORY'

Do you have any other recommendations / ideas to try for the metric configuration or the query ?

[burningalchemist]

@leeleahulkcs Apologies, I missed the previously shared snippet. 😅 I'll have a look today and let you know. 👍

[burningalchemist]

@leeleahulkcs, I think here's the issue:

metrics:
  - metric_name: TRANSACTION_MEMORY
    type: counter
    help: 'Memory allocated for transactions on this data node. This can be controlled using the TransactionMemory configuration parameter.'
    static_labels:
      counter: TRANSACTION_MEMORY
    value_label: resource_name <--
    values:
      - node_id 
      - resource_name <--
      - reserved
      - used
      - max
      - spare
    query: |
      select node_id, resource_name, reserved, used, max, spare
      from ndbinfo.resources
      where resource_name = 'TRANSACTION_MEMORY'

resource_name is most likely the string. values columns must be numbers (as of the Prometheus metric definition). So my suggestion would be to change it to the following configuration:

metrics:
  - metric_name: TRANSACTION_MEMORY
    type: counter
    help: 'Memory allocated for transactions on this data node. This can be controlled using the TransactionMemory configuration parameter.'
    key_labels:
      - resource_name
      - node_id
    value_label: memory_type
    values:
      - reserved
      - used
      - max
      - spare
    query: |
      select node_id, resource_name, reserved, used, max, spare
      from ndbinfo.resources
      where resource_name = 'TRANSACTION_MEMORY'

What's changed:

1. Move metadata columns (node_id and resource_name) under key_labels list, they will appear as labels in the metric record;
2. value_label is a user-defined key name that would automatically add reserved, used, max and spare labels (e.g. memory_type="reserved") and assign respective values to them. I defined it as memory_type;
3. Dropped static_labels as it's basically a user-defined label that doesn't change for any user-defined metadata (e.g. location or region, etc.).

Please note that parameters in my snippet aren't duplicated. Give it a try and let me know. 👍

[leeleahulkcs]

Thank you. That works.

In fact, I read that part yesterday in the documentation, but I did fully make the connection between the documentation example and my use case.