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

collect.info_schema.processlist leads to error when sql_mode ONLY_FULL_GROUP_BY is set on MariaDB 10.5 #610

Open
lusor opened this issue Jan 10, 2022 · 2 comments · May be fixed by #684
Open

collect.info_schema.processlist leads to error when sql_mode ONLY_FULL_GROUP_BY is set on MariaDB 10.5 #610

lusor opened this issue Jan 10, 2022 · 2 comments · May be fixed by #684

Comments

@lusor
Copy link

lusor commented Jan 10, 2022 •
edited

Host operating system: output of uname -a

Linux hostname 4.19.0-18-amd64 #1 SMP Debian 4.19.208-1 (2021-09-29) x86_64 GNU/Linux

mysqld_exporter version: output of mysqld_exporter --version

mysqld_exporter, version 0.13.0 (branch: v0.13.0, revision: 6125d4c90fa72487e7c0cf1905ea3c92260f2828)
build user:
build date: 2021-12-09T15:38:01+00:00
go version: go1.17.4
platform: linux/amd64

MySQL server version

10.5.13-MariaDB-1:10.5.13+maria~buster-log

mysqld_exporter command line flags

--collect.global_status
--collect.info_schema.innodb_metrics
--collect.auto_increment.columns
--collect.info_schema.processlist
--collect.binlog_size
--collect.info_schema.tablestats
--collect.global_variables
--no-collect.info_schema.query_response_time
--collect.info_schema.userstats
--collect.info_schema.tables
--collect.slave_status
--web.listen-address=0.0.0.0:9104
--web.config.file=/etc/prometheus/web.yml

What did you do that produced an error?

Metrics are fetched by Prometheus

What did you expect to see?

No error from mysqld_exporter

What did you see instead?

From MariaDB error log:
ERROR 1055: 'information_schema.processlist.HOST' isn't in GROUP BY : SELECT
user,
SUBSTRING_INDEX(host, ':', 1) AS host,
COALESCE(command,'') AS command,
COALESCE(state,'') AS state,
count(*) AS processes,
sum(time) AS seconds
FROM information_schema.processlist
WHERE ID != connection_id()
AND TIME >= 0
GROUP BY user,SUBSTRING_INDEX(host, ':', 1),command,state
ORDER BY null

This is triggered by collect.info_schema.processlist (https://github.com/prometheus/mysqld_exporter/blob/main/collector/info_schema_processlist.go#L31).

The SQL_MODE is set to "REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" (which is expanded from "ANSI,TRADITIONAL,ONLY_FULL_GROUP_BY")

If I do not set ONLY_FULL_GROUP_BY, the query works fine.

Interestingly, if I try to execute the query manually, I do not get an error, but only 2 warnings:

| Warning | 1052 | Column 'state' in group statement is ambiguous   |```

Cheers
Volker
@guruguruguru
Copy link

Hello, has anyone seen this?

@darxriggs
Copy link

A had a more detailed look into the problem.

According to MySQL Handling of GROUP BY:

  1. MySQL extends standard SQL to permit noncolumn expressions in GROUP BY clauses...

  2. In the presence of a noncolumn expression in the GROUP BY clause, MySQL recognizes equality between that expression and expressions in the select list.

Therefore it should support the query mentioned in this issue which uses SUBSTRING_INDEX(host, ':', 1) in the select list and in the GROUP BY clause. The AS host alias is not relevant here.

Some testing shows that the charset seems to be the culprit. The information_schema.processlist table uses utf8 as charset. With an equivalent table that uses latin1 as charset, the above query is working fine. I am not sure if this is an issue in MySQL or what's the reason behind this.

MySQL Handling of GROUP BY also states:

Standard SQL also does not permit aliases in GROUP BY clauses. MySQL extends standard SQL to permit aliases...

Therefore the solution could be to use the host alias in the GROUP BY clause to also support database instances which have ONLY_FULL_GROUP_BY enabled in sql_mode.

@darxriggs darxriggs linked a pull request Nov 19, 2022 that will close this issue
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Change processlist query to support ONLY_FULL_GROUP_BY sql_mode darxriggs/mysqld_exporter
3 participants
@darxriggs @lusor @guruguruguru