Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Metrics not being collected due to rename of MySQL 8 information schema tables

Description

Hi,

Some information tables will be renamed in MySQL 8

We will need to enhance mysql exporter to handle both old and new name

Table 1.1 Renamed InnoDB Information Schema Views

Old Name

New Name

INNODB_SYS_COLUMNS

INNODB_COLUMNS

INNODB_SYS_DATAFILES

INNODB_DATAFILES

INNODB_SYS_FIELDS

INNODB_FIELDS

INNODB_SYS_FOREIGN

INNODB_FOREIGN

INNODB_SYS_FOREIGN_COLS

INNODB_FOREIGN_COLS

INNODB_SYS_INDEXES

INNODB_INDEXES

INNODB_SYS_TABLES

INNODB_TABLES

INNODB_SYS_TABLESPACES

INNODB_TABLESPACES

INNODB_SYS_TABLESTATS

INNODB_TABLESTATS

INNODB_SYS_VIRTUAL

INNODB_VIRTUAL

 

How to test

1. Start a MySQL 8 instance. Example: 

docker run --name mysql8 -p 3306:3306 -e MYSQL_USER=msandbox -e MYSQL_PASSWORD=msandbox -e MYSQL_ROOT_PASSWORD=msandbox -d mysql:8.0

2. Create a MySQL config file like this: my.cnf

[client] host=127.0.0.1 port=3306 user=msandbox password=msandbox

3. Run the MySQL exporter

./mysqld_exporter --config.my-cnf <path-to-my.cnf-from-step-2> --collect.info_schema.innodb_tablespaces

4. Browse to http://localhost:9104 and in the console for step 3 there shouldn't be any error.

 

How to test

None

How to document

None

Confluence content

mentioned on

Smart Checklist

Activity

Carlos Salguero March 23, 2021 at 7:13 PM

Both solutions are different.
This approach runs only one query per scrape so, it affects less the DB that's being monitored. 

Francisco Miguel Biete Banon March 19, 2021 at 11:38 AM

Why not rebase mysqld_exporter to upstream and use their fix? You are coding things they already did (and in this case their approach looks cleaner/easier)

https://github.com/prometheus/mysqld_exporter/blob/master/collector/info_schema_innodb_sys_tablespaces.go

Carlos Salguero February 19, 2021 at 7:59 PM

Taras Kozub February 15, 2021 at 12:52 PM

, please discuss this ticket with support guys

C W February 5, 2021 at 8:11 AM

pmm-agent log

Feb 05 08:09:34 db-mysql.percona-dev pmm-agent[1049]: INFO[2021-02-05T08:09:34.059+00:00] time="2021-02-05T08:09:34Z" level=error msg="Error scraping for collect.info_schema.innodb_tablespaces: Error 1109: Unknown table 'INNODB_SYS_TABLESPACES' in information_schema" source="exporter.go:116" agentID=/agent_id/bd3e9e2a-cadf-43e6-aa8b-472c9b1e9f53 component=agent-process type=mysqld_exporter

MySQL version

/usr/sbin/mysqld Ver 8.0.22-13 for Linux on x86_64 (Percona Server (GPL), Release 13, Revision 6f7822f)

pmm2-client version

ProjectName: pmm-admin Version: 2.12.0 PMMVersion: 2.12.0 Timestamp: 2020-12-01 11:57:04 (UTC) FullCommit: e81fe912b1029095f310945f831abc71c824e139
Done

Details

Assignee

Reporter

Priority

Components

Labels

Needs QA

Yes

Fix versions

Story Points

Sprint

Smart Checklist

Created September 29, 2017 at 2:06 PM
Updated March 6, 2024 at 8:15 AM
Resolved March 9, 2021 at 3:57 PM

Flag notifications