auto_increment.columns Produces Error 1064 on MySQL 5.7

[satyagrahaha]

Host operating system: output of uname -a

Linux VM-RC-STB 3.10.0-1160.71.1.el7.x86_64 #1 SMP Tue Jun 28 15:37:28 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux

mysqld_exporter version: output of mysqld_exporter --version

mysqld_exporter, version 0.15.0 (branch: main, revision: c0b5cc3)
build user: hellerj@VM-RC-STB
build date: 20230708-04:36:57
go version: go1.19.10
platform: linux/amd64
tags: netgo

MySQL server version

mysqld Ver 5.7.42 for Linux on x86_64 (MySQL Community Server (GPL))

mysqld_exporter command line flags

/usr/local/bin/mysqld_exporter
--config.my-cnf /etc/.mysqld_exporter.cnf
--collect.global_status
--collect.auto_increment.columns
--collect.info_schema.innodb_metrics
--collect.info_schema.processlist
--collect.binlog_size
--collect.info_schema.tablestats
--collect.global_variables
--collect.info_schema.query_response_time
--collect.info_schema.userstats
--collect.info_schema.tables
--collect.perf_schema.tablelocks
--collect.perf_schema.file_events
--collect.perf_schema.eventswaits
--collect.perf_schema.indexiowaits
--collect.perf_schema.tableiowaits
--collect.slave_status
--web.listen-address=0.0.0.0:12347

What did you do that produced an error?

Run mysqld_exporter with collect.auto_increment.columns using MySQL 5.7.

What did you expect to see?

I expected auto_increment.columns to run without error.

What did you see instead?

Instead I had errors like:
mysqld_exporter[XXX]: ts=XXX caller=exporter.go:173 level=error msg="Error from scraper" scraper=auto_increment.columns target=127.0.0.1:3306 err="Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING (table_schema,table_name)\n\t\t WHERE c.extra = 'auto_increment' AND t.auto_' at line 10"

Although auto_increment.columns lists its version requirement as 5.1, this appears to be because MySQL 5.7 does not support a USING clause on a STRAIGHT_JOIN (https://dev.mysql.com/doc/refman/5.7/en/join.html).

This can be found in info_schema_auto_increment.go:27
27 SELECT table_schema, table_name, column_name, auto_increment,
28 pow(2, case data_type
29 when 'tinyint' then 7
30 when 'smallint' then 15
31 when 'mediumint' then 23
32 when 'int' then 31
33 when 'bigint' then 63
34 end+(column_type like '% unsigned'))-1 as max_int
35 FROM information_schema.columns c
36 STRAIGHT_JOIN information_schema.tables t USING (table_schema,table_name)
37 WHERE c.extra = 'auto_increment' AND t.auto_increment IS NOT NULL
38 `

[hbokh]

Still not fixed in 0.15.1! We had to revert to 0.12.1 ...

[vgeorgiev69]

Can confirm this is still bugged on 0.15.1, unfortunately I'm getting the same errors on 0.12.1

Aug 30 03:21:31 mysqld_exporter[1146892]: ts=2024-08-30T10:21:31.477Z caller=exporter.go:173 level=error msg="Error from scraper" scraper=auto_increment.columns target=x.x.x.x:3306 err="Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING (table_schema,table_name)\n\t\t WHERE c.extra = 'auto_increment' AND t.auto_' at line 10"

[fxkjnj]

how to fix？？