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.

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

Unknown column 'SUM_CPU_TIME' in 'SELECT'" #905

Closed
nbari opened this issue Jan 9, 2025 · 6 comments · Fixed by #916
Closed

Unknown column 'SUM_CPU_TIME' in 'SELECT'" #905

nbari opened this issue Jan 9, 2025 · 6 comments · Fixed by #916

Comments

@nbari
Copy link

nbari commented Jan 9, 2025

Host operating system: output of uname -a

Linux test 5.14.0-503.15.1.el9_5.x86_64 #1 SMP PREEMPT_DYNAMIC Thu Nov 14 15:45:31 EST 2024 x86_64 x86_64 x86_64 GNU/Linux

mysqld_exporter version: output of mysqld_exporter --version

mysqld_exporter, version 0.16.0 (branch: HEAD, revision: c366424252a3140838a344a6cb0d40d44f1be91d)
  build user:       root@ad5ebc2e1a44
  build date:       20241108-16:00:22
  go version:       go1.23.3
  platform:         linux/amd64
  tags:             unknown

MySQL server version

  * 11.4.4  MariaDB Server
  * 10.11.9 MariaDB Server

mysqld_exporter command line flags

/usr/local/bin/mysqld_exporter --config.my-cnf=/home/mysql/.exporter.my.cnf \
  --collect.binlog_size \
  --collect.engine_innodb_status \
  --collect.global_status \
  --collect.global_variables \
  --collect.info_schema.clientstats \
  --collect.info_schema.innodb_metrics \
  --collect.info_schema.processlist \
  --collect.info_schema.query_response_time \
  --collect.perf_schema.eventsstatements \
  --collect.perf_schema.eventswaits \
  --collect.perf_schema.file_events \
  --collect.perf_schema.file_instances \
  --collect.perf_schema.indexiowaits \
  --collect.perf_schema.memory_events \
  --collect.perf_schema.tableiowaits \
  --collect.perf_schema.tablelocks

What did you do that produced an error?

upgrade to latest mysqld_exporter 0.16 from 0.14

What did you expect to see?

No errors when scraping

What did you see instead?

level=ERROR source=exporter.go:160 msg="Error from scraper" scraper=perf_schema.eventsstatements target=/tmp/mysql.sock err="Error 1054 (42S22): Unknown column 'SUM_CPU_TIME' in 'SELECT'"

@elad-bar
Copy link

elad-bar commented Feb 3, 2025

i have another error, almost the same message:

 level=ERROR source=exporter.go:160 msg="Error from scraper" scraper=perf_schema.eventsstatements target=IP:PORT err="Error 1054 (42S22): Unknown column 'SUM_CPU_TIME' in 'field list'"

@proffalken
Copy link

proffalken commented Feb 5, 2025

Looks like logic similar to

if instance.flavor == FlavorMariaDB && instance.version.GTE(semver.MustParse("10.5.0")) {
query = fmt.Sprintf(innodbTablespacesQueryMariaDB, tablespacesTablename, tablespacesTablename)
}

might be needed here?

@dbakit
Copy link

dbakit commented Feb 10, 2025

This bug was introduced by #862 . The correct approach is to check the MySQL version and determine which SQL query to use for fetching the metrics.

cristiangreco added a commit to cristiangreco/mysqld_exporter that referenced this issue Feb 10, 2025
…8.0.28

Fix issue introduced in prometheus#862

Only query SUM_LOCK_TIME and SUM_CPU_TIME when using MySQL >= 8.0.28.
cristiangreco added a commit to cristiangreco/mysqld_exporter that referenced this issue Feb 10, 2025
…8.0.28

Fix issue introduced in prometheus#862

Only query SUM_LOCK_TIME and SUM_CPU_TIME when using MySQL >= 8.0.28.

Signed-off-by: Cristian Greco <[email protected]>
cristiangreco added a commit that referenced this issue Feb 14, 2025
Fix #905: use SUM_LOCK_TIME and SUM_CPU_TIME with mysql >= 8.0.28
@nbari
Copy link
Author

nbari commented Feb 24, 2025

Hi @cristiangreco I updraged to the latest 0.17.1 but now I get this error:

 time=2025-02-24T14:11:36.862Z level=ERROR source=exporter.go:160 msg="Error from scraper" scraper=perf_schema.eventsstatements target=/tmp/mysql.sock err="Error 1054 (42S22): Unknown column 'QUANTILE_95' in 'SELECT'"

This is happening in a MariaDB 11.4.5, in where the column doesn't exist:

MariaDB> show create table performance_schema.events_statements_summary_by_digest \G
*************************** 1. row ***************************
       Table: events_statements_summary_by_digest
Create Table: CREATE TABLE `events_statements_summary_by_digest` (
  `SCHEMA_NAME` varchar(64) DEFAULT NULL COMMENT 'Database name. Records are summarised together with DIGEST.',
  `DIGEST` varchar(32) DEFAULT NULL COMMENT 'Performance Schema digest. Records are summarised together with SCHEMA NAME.',
  `DIGEST_TEXT` longtext DEFAULT NULL COMMENT 'The unhashed form of the digest.',
  `COUNT_STAR` bigint(20) unsigned NOT NULL COMMENT 'Number of summarized events',
  `SUM_TIMER_WAIT` bigint(20) unsigned NOT NULL COMMENT 'Total wait time of the summarized events that are timed.',
  `MIN_TIMER_WAIT` bigint(20) unsigned NOT NULL COMMENT 'Minimum wait time of the summarized events that are timed.',
  `AVG_TIMER_WAIT` bigint(20) unsigned NOT NULL COMMENT 'Average wait time of the summarized events that are timed.',
  `MAX_TIMER_WAIT` bigint(20) unsigned NOT NULL COMMENT 'Maximum wait time of the summarized events that are timed.',
  `SUM_LOCK_TIME` bigint(20) unsigned NOT NULL COMMENT 'Sum of the LOCK_TIME column in the events_statements_current table.',
  `SUM_ERRORS` bigint(20) unsigned NOT NULL COMMENT 'Sum of the ERRORS column in the events_statements_current table.',
  `SUM_WARNINGS` bigint(20) unsigned NOT NULL COMMENT 'Sum of the WARNINGS column in the events_statements_current table.',
  `SUM_ROWS_AFFECTED` bigint(20) unsigned NOT NULL COMMENT 'Sum of the ROWS_AFFECTED column in the events_statements_current table.',
  `SUM_ROWS_SENT` bigint(20) unsigned NOT NULL COMMENT 'Sum of the ROWS_SENT column in the events_statements_current table.',
  `SUM_ROWS_EXAMINED` bigint(20) unsigned NOT NULL COMMENT 'Sum of the ROWS_EXAMINED column in the events_statements_current table.',
  `SUM_CREATED_TMP_DISK_TABLES` bigint(20) unsigned NOT NULL COMMENT 'Sum of the CREATED_TMP_DISK_TABLES column in the events_statements_current table.',
  `SUM_CREATED_TMP_TABLES` bigint(20) unsigned NOT NULL COMMENT 'Sum of the CREATED_TMP_TABLES column in the events_statements_current table.',
  `SUM_SELECT_FULL_JOIN` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SELECT_FULL_JOIN column in the events_statements_current table.',
  `SUM_SELECT_FULL_RANGE_JOIN` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SELECT_FULL_RANGE_JOIN column in the events_statements_current table.',
  `SUM_SELECT_RANGE` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SELECT_RANGE column in the events_statements_current table.',
  `SUM_SELECT_RANGE_CHECK` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SELECT_RANGE_CHECK column in the events_statements_current table.',
  `SUM_SELECT_SCAN` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SELECT_SCAN column in the events_statements_current table.',
  `SUM_SORT_MERGE_PASSES` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SORT_MERGE_PASSES column in the events_statements_current table.',
  `SUM_SORT_RANGE` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SORT_RANGE column in the events_statements_current table.',
  `SUM_SORT_ROWS` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SORT_ROWS column in the events_statements_current table.',
  `SUM_SORT_SCAN` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SORT_SCAN column in the events_statements_current table.',
  `SUM_NO_INDEX_USED` bigint(20) unsigned NOT NULL COMMENT 'Sum of the NO_INDEX_USED column in the events_statements_current table.',
  `SUM_NO_GOOD_INDEX_USED` bigint(20) unsigned NOT NULL COMMENT 'Sum of the NO_GOOD_INDEX_USED column in the events_statements_current table.',
  `FIRST_SEEN` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Time at which the digest was first seen.',
  `LAST_SEEN` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Time at which the digest was most recently seen.'
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

@cristiangreco
Copy link
Contributor

@nbari thanks for reporting the issue. Are you able to test #922?

@nbari
Copy link
Author

nbari commented Feb 25, 2025

Hi @cristiangreco, I can't compile in the VM's, but maybe you can try with podman:

Download the 11.4 mariadb:

podman pull mariadb:11.4

Create dir for the my.cnf:

mkdir $HOME/podman/mariadb

Change to that dir:

cd $HOME/podman/mariadb

Create a my.cnf file with contents:

[mysqld]
performance_schema=ON

Run podman with:

podman run -d --name mariadb \
  -e MARIADB_ROOT_PASSWORD=secret \
  -v $PWD/my.cnf:/etc/mysql/my.cnf:ro \
  -p 3306:3306 \
  mariadb:11.4

Then you can login with  mariadb -h 127.0.0.1 -uroot --password=secret

MariaDB [(none)]> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

MariaDB [(none)]> show create table performance_schema.events_statements_summary_by_digest \G
*************************** 1. row ***************************
       Table: events_statements_summary_by_digest
Create Table: CREATE TABLE `events_statements_summary_by_digest` (
  `SCHEMA_NAME` varchar(64) DEFAULT NULL COMMENT 'Database name. Records are summarised together with DIGEST.',
  `DIGEST` varchar(32) DEFAULT NULL COMMENT 'Performance Schema digest. Records are summarised together with SCHEMA NAME.',
  `DIGEST_TEXT` longtext DEFAULT NULL COMMENT 'The unhashed form of the digest.',
  `COUNT_STAR` bigint(20) unsigned NOT NULL COMMENT 'Number of summarized events',
  `SUM_TIMER_WAIT` bigint(20) unsigned NOT NULL COMMENT 'Total wait time of the summarized events that are timed.',
  `MIN_TIMER_WAIT` bigint(20) unsigned NOT NULL COMMENT 'Minimum wait time of the summarized events that are timed.',
  `AVG_TIMER_WAIT` bigint(20) unsigned NOT NULL COMMENT 'Average wait time of the summarized events that are timed.',
  `MAX_TIMER_WAIT` bigint(20) unsigned NOT NULL COMMENT 'Maximum wait time of the summarized events that are timed.',
  `SUM_LOCK_TIME` bigint(20) unsigned NOT NULL COMMENT 'Sum of the LOCK_TIME column in the events_statements_current table.',
  `SUM_ERRORS` bigint(20) unsigned NOT NULL COMMENT 'Sum of the ERRORS column in the events_statements_current table.',
  `SUM_WARNINGS` bigint(20) unsigned NOT NULL COMMENT 'Sum of the WARNINGS column in the events_statements_current table.',
  `SUM_ROWS_AFFECTED` bigint(20) unsigned NOT NULL COMMENT 'Sum of the ROWS_AFFECTED column in the events_statements_current table.',
  `SUM_ROWS_SENT` bigint(20) unsigned NOT NULL COMMENT 'Sum of the ROWS_SENT column in the events_statements_current table.',
  `SUM_ROWS_EXAMINED` bigint(20) unsigned NOT NULL COMMENT 'Sum of the ROWS_EXAMINED column in the events_statements_current table.',
  `SUM_CREATED_TMP_DISK_TABLES` bigint(20) unsigned NOT NULL COMMENT 'Sum of the CREATED_TMP_DISK_TABLES column in the events_statements_current table.',
  `SUM_CREATED_TMP_TABLES` bigint(20) unsigned NOT NULL COMMENT 'Sum of the CREATED_TMP_TABLES column in the events_statements_current table.',
  `SUM_SELECT_FULL_JOIN` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SELECT_FULL_JOIN column in the events_statements_current table.',
  `SUM_SELECT_FULL_RANGE_JOIN` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SELECT_FULL_RANGE_JOIN column in the events_statements_current table.',
  `SUM_SELECT_RANGE` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SELECT_RANGE column in the events_statements_current table.',
  `SUM_SELECT_RANGE_CHECK` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SELECT_RANGE_CHECK column in the events_statements_current table.',
  `SUM_SELECT_SCAN` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SELECT_SCAN column in the events_statements_current table.',
  `SUM_SORT_MERGE_PASSES` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SORT_MERGE_PASSES column in the events_statements_current table.',
  `SUM_SORT_RANGE` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SORT_RANGE column in the events_statements_current table.',
  `SUM_SORT_ROWS` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SORT_ROWS column in the events_statements_current table.',
  `SUM_SORT_SCAN` bigint(20) unsigned NOT NULL COMMENT 'Sum of the SORT_SCAN column in the events_statements_current table.',
  `SUM_NO_INDEX_USED` bigint(20) unsigned NOT NULL COMMENT 'Sum of the NO_INDEX_USED column in the events_statements_current table.',
  `SUM_NO_GOOD_INDEX_USED` bigint(20) unsigned NOT NULL COMMENT 'Sum of the NO_GOOD_INDEX_USED column in the events_statements_current table.',
  `FIRST_SEEN` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Time at which the digest was first seen.',
  `LAST_SEEN` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Time at which the digest was most recently seen.'
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
1 row in set (0.005 sec)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants