Change in table information_schema.innodb_metrics in MariaDB 10.4 causes errors #494

TobiasGrave · 2020-08-14T14:35:40Z

In MariaDB version 10.4 the field STATUS in table information_schema.innodb_metrics has been renamed to ENABLED and the type changed from varchar to int. This causes the following error when collect.info_schema.innodb_metrics is enabled:

Error scraping for collect.info_schema.innodb_metrics: Error 1054: Unknown column 'status' in 'where clause'" source="exporter.go:171

I cannot find any hints in the MariaDB documentation about this change unfortunately.

This is the format of the information_schema.innodb_metrics table in MariaDB version 10.3 (and earlier):

+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| NAME            | varchar(193) | NO   |     |         |       |
| SUBSYSTEM       | varchar(193) | NO   |     |         |       |
| COUNT           | bigint(21)   | NO   |     | 0       |       |
| MAX_COUNT       | bigint(21)   | YES  |     | NULL    |       |
| MIN_COUNT       | bigint(21)   | YES  |     | NULL    |       |
| AVG_COUNT       | double       | YES  |     | NULL    |       |
| COUNT_RESET     | bigint(21)   | NO   |     | 0       |       |
| MAX_COUNT_RESET | bigint(21)   | YES  |     | NULL    |       |
| MIN_COUNT_RESET | bigint(21)   | YES  |     | NULL    |       |
| AVG_COUNT_RESET | double       | YES  |     | NULL    |       |
| TIME_ENABLED    | datetime     | YES  |     | NULL    |       |
| TIME_DISABLED   | datetime     | YES  |     | NULL    |       |
| TIME_ELAPSED    | bigint(21)   | YES  |     | NULL    |       |
| TIME_RESET      | datetime     | YES  |     | NULL    |       |
| STATUS          | varchar(193) | NO   |     |         |       |
| TYPE            | varchar(193) | NO   |     |         |       |
| COMMENT         | varchar(193) | NO   |     |         |       |
+-----------------+--------------+------+-----+---------+-------+

This is the format of the information_schema.innodb_metrics table in MariaDB version 10.4 (and later):

+-----------------+-------------------------------------------------------------------+------+-----+---------+-------+
| Field           | Type                                                              | Null | Key | Default | Extra |
+-----------------+-------------------------------------------------------------------+------+-----+---------+-------+
| NAME            | varchar(193)                                                      | NO   |     |         |       |
| SUBSYSTEM       | varchar(193)                                                      | NO   |     |         |       |
| COUNT           | bigint(21)                                                        | NO   |     | 0       |       |
| MAX_COUNT       | bigint(21)                                                        | YES  |     | NULL    |       |
| MIN_COUNT       | bigint(21)                                                        | YES  |     | NULL    |       |
| AVG_COUNT       | float                                                             | YES  |     | NULL    |       |
| COUNT_RESET     | bigint(21)                                                        | NO   |     | 0       |       |
| MAX_COUNT_RESET | bigint(21)                                                        | YES  |     | NULL    |       |
| MIN_COUNT_RESET | bigint(21)                                                        | YES  |     | NULL    |       |
| AVG_COUNT_RESET | float                                                             | YES  |     | NULL    |       |
| TIME_ENABLED    | datetime                                                          | YES  |     | NULL    |       |
| TIME_DISABLED   | datetime                                                          | YES  |     | NULL    |       |
| TIME_ELAPSED    | bigint(21)                                                        | YES  |     | NULL    |       |
| TIME_RESET      | datetime                                                          | YES  |     | NULL    |       |
| ENABLED         | int(1)                                                            | NO   |     | 0       |       |
| TYPE            | enum('value','status_counter','set_owner','set_member','counter') | NO   |     | NULL    |       |
| COMMENT         | varchar(193)                                                      | NO   |     |         |       |
+-----------------+-------------------------------------------------------------------+------+-----+---------+-------+

I guess that for MariaDB 10.4 the metrics query should be as follows:

MariaDB [(none)]> SELECT name, subsystem, type, comment, count FROM information_schema.innodb_metrics WHERE enabled;
+--------------------------------------------+---------------------+----------------+--------------------------------------------------------------------------------------------------------+-------------+
| name                                       | subsystem           | type           | comment                                                                                                | count       |
+--------------------------------------------+---------------------+----------------+--------------------------------------------------------------------------------------------------------+-------------+
| lock_deadlocks                             | lock                | counter        | Number of deadlocks                                                                                    |           0 |
| lock_timeouts                              | lock                | counter        | Number of lock timeouts                                                                                |           0 |
| lock_row_lock_current_waits                | lock                | status_counter | Number of row locks currently being waited for (innodb_row_lock_current_waits)                         |           0 |
| lock_row_lock_time                         | lock                | status_counter | Time spent in acquiring row locks, in milliseconds (innodb_row_lock_time)                              |          20 |
| lock_row_lock_time_max                     | lock                | value          | The maximum time to acquire a row lock, in milliseconds (innodb_row_lock_time_max)                     |           2 |
| lock_row_lock_waits                        | lock                | status_counter | Number of times a row lock had to be waited for (innodb_row_lock_waits)                                |          32 |
| lock_row_lock_time_avg                     | lock                | value          | The average time to acquire a row lock, in milliseconds (innodb_row_lock_time_avg)                     |           0 |
| buffer_pool_size                           | server              | value          | Server buffer pool size (all buffer pools) in bytes                                                    |  1073741824 |
| buffer_pool_reads                          | buffer              | status_counter | Number of reads directly from disk (innodb_buffer_pool_reads)                                          |      365565 |
| buffer_pool_read_requests                  | buffer              | status_counter | Number of logical read requests (innodb_buffer_pool_read_requests)                                     |   165186385 |
| buffer_pool_write_requests                 | buffer              | status_counter | Number of write requests (innodb_buffer_pool_write_requests)                                           |     9271194 |
| buffer_pool_wait_free                      | buffer              | status_counter | Number of times waited for free buffer (innodb_buffer_pool_wait_free)                                  |           0 |
| buffer_pool_read_ahead                     | buffer              | status_counter | Number of pages read as read ahead (innodb_buffer_pool_read_ahead)                                     |          24 |
| buffer_pool_read_ahead_evicted             | buffer              | status_counter | Read-ahead pages evicted without being accessed (innodb_buffer_pool_read_ahead_evicted)                |        1140 |
| buffer_pool_pages_total                    | buffer              | value          | Total buffer pool size in pages (innodb_buffer_pool_pages_total)                                       |       64464 |
| buffer_pool_pages_misc                     | buffer              | value          | Buffer pages for misc use such as row locks or the adaptive hash index (innodb_buffer_pool_pages_misc) |        1619 |
| buffer_pool_pages_data                     | buffer              | value          | Buffer pages containing data (innodb_buffer_pool_pages_data)                                           |       61825 |
| buffer_pool_bytes_data                     | buffer              | value          | Buffer bytes containing data (innodb_buffer_pool_bytes_data)                                           |  1039466496 |
| buffer_pool_pages_dirty                    | buffer              | value          | Buffer pages currently dirty (innodb_buffer_pool_pages_dirty)                                          |           0 |
| buffer_pool_bytes_dirty                    | buffer              | value          | Buffer bytes currently dirty (innodb_buffer_pool_bytes_dirty)                                          |           0 |
| buffer_pool_pages_free                     | buffer              | value          | Buffer pages currently free (innodb_buffer_pool_pages_free)                                            |        1020 |
| buffer_pages_created                       | buffer              | status_counter | Number of pages created (innodb_pages_created)                                                         |       25690 |
| buffer_pages_written                       | buffer              | status_counter | Number of pages written (innodb_pages_written)                                                         |      796558 |
| buffer_index_pages_written                 | buffer              | status_counter | Number of index pages written (innodb_index_pages_written)                                             |           0 |
| buffer_non_index_pages_written             | buffer              | status_counter | Number of non index pages written (innodb_non_index_pages_written)                                     |           0 |
| buffer_pages_read                          | buffer              | status_counter | Number of pages read (innodb_pages_read)                                                               |      365589 |
| buffer_index_sec_rec_cluster_reads         | buffer              | status_counter | Number of secondary record reads triggered cluster read                                                |    10864389 |
| buffer_index_sec_rec_cluster_reads_avoided | buffer              | status_counter | Number of secondary record reads avoided triggering cluster read                                       |           0 |
| buffer_data_reads                          | buffer              | status_counter | Amount of data read in bytes (innodb_data_reads)                                                       |  5938331648 |
| buffer_data_written                        | buffer              | status_counter | Amount of data written in bytes (innodb_data_written)                                                  | 25349185536 |
| os_data_reads                              | os                  | status_counter | Number of reads initiated (innodb_data_reads)                                                          |      375334 |
| os_data_writes                             | os                  | status_counter | Number of writes initiated (innodb_data_writes)                                                        |     1129684 |
| os_data_fsyncs                             | os                  | status_counter | Number of fsync() calls (innodb_data_fsyncs)                                                           |      454253 |
| os_pending_reads                           | os                  | counter        | Number of reads pending                                                                                |           0 |
| os_pending_writes                          | os                  | counter        | Number of writes pending                                                                               |           0 |
| os_log_bytes_written                       | os                  | status_counter | Bytes of log written (innodb_os_log_written)                                                           |  1153414144 |
| os_log_fsyncs                              | os                  | status_counter | Number of fsync log writes (innodb_os_log_fsyncs)                                                      |      296629 |
| os_log_pending_fsyncs                      | os                  | status_counter | Number of pending fsync write (innodb_os_log_pending_fsyncs)                                           |           0 |
| os_log_pending_writes                      | os                  | status_counter | Number of pending log file writes (innodb_os_log_pending_writes)                                       |           0 |
| trx_rseg_history_len                       | transaction         | value          | Length of the TRX_RSEG_HISTORY list                                                                    |           0 |
| log_waits                                  | recovery            | status_counter | Number of log waits due to small log buffer (innodb_log_waits)                                         |           0 |
| log_write_requests                         | recovery            | status_counter | Number of log write requests (innodb_log_write_requests)                                               |     1696248 |
| log_writes                                 | recovery            | status_counter | Number of log writes (innodb_log_writes)                                                               |      296631 |
| log_padded                                 | recovery            | status_counter | Bytes of log padded for log write ahead                                                                |   200835072 |
| adaptive_hash_searches                     | adaptive_hash_index | status_counter | Number of successful searches using Adaptive Hash Index                                                |           0 |
| adaptive_hash_searches_btree               | adaptive_hash_index | status_counter | Number of searches using B-tree on an index search                                                     |    25446699 |
| file_num_open_files                        | file_system         | value          | Number of files currently open (innodb_num_open_files)                                                 |        2048 |
| ibuf_merges_insert                         | change_buffer       | status_counter | Number of inserted records merged by change buffering                                                  |        2847 |
| ibuf_merges_delete_mark                    | change_buffer       | status_counter | Number of deleted records merged by change buffering                                                   |       42498 |
| ibuf_merges_delete                         | change_buffer       | status_counter | Number of purge records merged by change buffering                                                     |        7491 |
| ibuf_merges_discard_insert                 | change_buffer       | status_counter | Number of insert merged operations discarded                                                           |           0 |
| ibuf_merges_discard_delete_mark            | change_buffer       | status_counter | Number of deleted merged operations discarded                                                          |           0 |
| ibuf_merges_discard_delete                 | change_buffer       | status_counter | Number of purge merged  operations discarded                                                           |           0 |
| ibuf_merges                                | change_buffer       | status_counter | Number of change buffer merges                                                                         |        1063 |
| ibuf_size                                  | change_buffer       | status_counter | Change buffer size in pages                                                                            |         144 |
| innodb_activity_count                      | server              | status_counter | Current server activity count                                                                          |       55830 |
| innodb_dblwr_writes                        | server              | status_counter | Number of doublewrite operations that have been performed (innodb_dblwr_writes)                        |       33538 |
| innodb_dblwr_pages_written                 | server              | status_counter | Number of pages that have been written for doublewrite operations (innodb_dblwr_pages_written)         |      751357 |
| innodb_page_size                           | server              | value          | InnoDB page size in bytes (innodb_page_size)                                                           |       16384 |
| innodb_rwlock_s_spin_waits                 | server              | status_counter | Number of rwlock spin waits due to shared latch request                                                |       37952 |
| innodb_rwlock_x_spin_waits                 | server              | status_counter | Number of rwlock spin waits due to exclusive latch request                                             |       54357 |
| innodb_rwlock_sx_spin_waits                | server              | status_counter | Number of rwlock spin waits due to sx latch request                                                    |       17021 |
| innodb_rwlock_s_spin_rounds                | server              | status_counter | Number of rwlock spin loop rounds due to shared latch request                                          |      109324 |
| innodb_rwlock_x_spin_rounds                | server              | status_counter | Number of rwlock spin loop rounds due to exclusive latch request                                       |      143804 |
| innodb_rwlock_sx_spin_rounds               | server              | status_counter | Number of rwlock spin loop rounds due to sx latch request                                              |       40784 |
| innodb_rwlock_s_os_waits                   | server              | status_counter | Number of OS waits due to shared latch request                                                         |         921 |
| innodb_rwlock_x_os_waits                   | server              | status_counter | Number of OS waits due to exclusive latch request                                                      |        2094 |
| innodb_rwlock_sx_os_waits                  | server              | status_counter | Number of OS waits due to sx latch request                                                             |         336 |
| dml_inserts                                | dml                 | status_counter | Number of rows inserted                                                                                |      976075 |
| dml_deletes                                | dml                 | status_counter | Number of rows deleted                                                                                 |      551093 |
| dml_updates                                | dml                 | status_counter | Number of rows updated                                                                                 |       67476 |
| dml_system_reads                           | dml                 | status_counter | Number of system rows read                                                                             |      120393 |
| dml_system_inserts                         | dml                 | status_counter | Number of system rows inserted                                                                         |           0 |
| dml_system_deletes                         | dml                 | status_counter | Number of system rows deleted                                                                          |           0 |
| dml_system_updates                         | dml                 | status_counter | Number of system rows updated                                                                          |           0 |
+--------------------------------------------+---------------------+----------------+--------------------------------------------------------------------------------------------------------+-------------+

The text was updated successfully, but these errors were encountered:

GameCharmer · 2020-09-16T20:53:39Z

This issue persists in MariaDB 10.5

Neurozone · 2020-10-04T14:45:17Z

I can confirm the issue with mariadb 10.5:

"msg="Error scraping for collect.info_schema.innodb_metrics: Error 1054: Unknown column 'status' in 'where clause'" source="exporter.go:171""

Neurozone · 2020-11-24T07:57:31Z

Can we have some information about that please ? The bug still persist

coderovich · 2020-12-20T18:38:26Z

I can confirm the issue with mariadb 10.5 still exists

lasat · 2020-12-28T10:37:49Z

In MariaDB version 10.4 the field STATUS in table information_schema.innodb_metrics has been renamed to ENABLED and the type changed from varchar to int. This causes the following error when collect.info_schema.innodb_metrics is enabled:

I cannot find any hints in the MariaDB documentation about this change unfortunately.

I currently use the 10.4 packages for ubuntu bionic (10.4.17) and recognized that the column is still named "status". For the 10.5 packages I could confirm that the column was renamed.

TobiasGrave · 2021-01-06T10:36:13Z

Hmm seems that I confused the version numbers and the change occured with MariaDB 10.5 and not 10.4:

https://mariadb.com/kb/en/mariadb-1050-release-notes -> MDEV-19940

More information:
https://jira.mariadb.org/browse/MDEV-19940
MariaDB/server@d09aec7

However, this change is not mentioned in the MariaDB documentaion:
https://mariadb.com/kb/en/information-schema-innodb_metrics-table/

lasat · 2021-01-14T08:05:20Z

Hmm seems that I confused the version numbers and the change occurred with MariaDB 10.5 and not 10.4
@TobiasGrave: Is it possible to change the issue title?

maemigh · 2021-01-15T09:43:40Z

I submitted pull request #523 to fix this

Changes related to `replication_group_member_stats` collector: * metric "transaction_in_queue" was Counter instead of Gauge * renamed 3 metrics starting with `mysql_perf_schema_transaction_` to start with `mysql_perf_schema_transactions_` to be consistent with column names * exposing only server's own stats by matching MEMBER_ID with @@server_uuid resulting "member_id" label to be dropped. * [CHANGE] Switch to go-kit for logs. #433 * [BUGFIX] Fix binlog metrics on mysql 8.x #419 * [BUGFIX] Fix output value of wsrep_cluster_status #473 * [BUGFIX] Fix collect.info_schema.innodb_metrics for new field names (mariadb 10.5+) #494 * [BUGFIX] Fix log output of collect[] params #505 * [BUGFIX] Fix collect.info_schema.innodb_tablespaces for new table names #516 * [BUGFIX] Fix innodb_metrics for mariadb 10.5+ #523 * [BUGFIX] Allow perf_schema.memory summary current_bytes to be negative #517 * [ENHANCEMENT] Support heartbeats in UTC #471 * [FEATURE] Add `tls.insecure-skip-verify` flag to ignore tls verification errors #417 * [FEATURE] Add collector for AWS Aurora information_schema.replica_host_status #435 * [FEATURE] Add collector for `replication_group_members` #459 * [FEATURE] Add new metrics to `replication_group_member_stats` collector to support MySQL 8.x. #462 * [FEATURE] Add collector for `performance_schema.memory_summary_global_by_event_name` #515 * [FEATURE] Support authenticating using mTLS client cert and no password #539 Signed-off-by: Ben Kochie <[email protected]>

BREAKING CHANGES: Changes related to `replication_group_member_stats` collector: * metric "transaction_in_queue" was Counter instead of Gauge * renamed 3 metrics starting with `mysql_perf_schema_transaction_` to start with `mysql_perf_schema_transactions_` to be consistent with column names * exposing only server's own stats by matching MEMBER_ID with @@server_uuid resulting "member_id" label to be dropped. Changes: * [CHANGE] Switch to go-kit for logs. #433 * [FEATURE] Add `tls.insecure-skip-verify` flag to ignore tls verification errors #417 * [FEATURE] Add collector for AWS Aurora information_schema.replica_host_status #435 * [FEATURE] Add collector for `replication_group_members` #459 * [FEATURE] Add new metrics to `replication_group_member_stats` collector to support MySQL 8.x. #462 * [FEATURE] Add collector for `performance_schema.memory_summary_global_by_event_name` #515 * [FEATURE] Support authenticating using mTLS client cert and no password #539 * [ENHANCEMENT] Support heartbeats in UTC #471 * [BUGFIX] Fix binlog metrics on mysql 8.x #419 * [BUGFIX] Fix output value of wsrep_cluster_status #473 * [BUGFIX] Fix collect.info_schema.innodb_metrics for new field names (mariadb 10.5+) #494 * [BUGFIX] Fix log output of collect[] params #505 * [BUGFIX] Fix collect.info_schema.innodb_tablespaces for new table names #516 * [BUGFIX] Fix innodb_metrics for mariadb 10.5+ #523 * [BUGFIX] Allow perf_schema.memory summary current_bytes to be negative #517 Signed-off-by: Ben Kochie <[email protected]>

BREAKING CHANGES: Changes related to `replication_group_member_stats` collector: * metric "transaction_in_queue" was Counter instead of Gauge * renamed 3 metrics starting with `mysql_perf_schema_transaction_` to start with `mysql_perf_schema_transactions_` to be consistent with column names * exposing only server's own stats by matching MEMBER_ID with @@server_uuid resulting "member_id" label to be dropped. Changes: * [CHANGE] Switch to go-kit for logs. #433 * [FEATURE] Add `tls.insecure-skip-verify` flag to ignore tls verification errors #417 * [FEATURE] Add collector for AWS Aurora information_schema.replica_host_status #435 * [FEATURE] Add collector for `replication_group_members` #459 * [FEATURE] Add new metrics to `replication_group_member_stats` collector to support MySQL 8.x. #462 * [FEATURE] Add collector for `performance_schema.memory_summary_global_by_event_name` #515 * [FEATURE] Support authenticating using mTLS client cert and no password #539 * [FEATURE] Add TLS and basic authentication #522 * [ENHANCEMENT] Support heartbeats in UTC #471 * [ENHANCEMENT] Improve parsing of boolean strings #548 * [BUGFIX] Fix binlog metrics on mysql 8.x #419 * [BUGFIX] Fix output value of wsrep_cluster_status #473 * [BUGFIX] Fix collect.info_schema.innodb_metrics for new field names (mariadb 10.5+) #494 * [BUGFIX] Fix log output of collect[] params #505 * [BUGFIX] Fix collect.info_schema.innodb_tablespaces for new table names #516 * [BUGFIX] Fix innodb_metrics for mariadb 10.5+ #523 * [BUGFIX] Allow perf_schema.memory summary current_bytes to be negative #517 Signed-off-by: Ben Kochie <[email protected]>

maemigh mentioned this issue Jan 15, 2021

fix innodb_metrics for mariadb 10.5+ (fixes #494) #523

Merged

SuperQ closed this as completed in 662d1a1 Apr 8, 2021

SuperQ mentioned this issue Apr 23, 2021

Release 0.13.0-rc.0 #546

Merged

SuperQ mentioned this issue May 18, 2021

Release 0.13.0 #552

Merged

jeremyj11 mentioned this issue Jun 18, 2021

Performance schema and Sysschema installation for MariaDB >= 10.4 fails major/MySQLTuner-perl#556

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Change in table information_schema.innodb_metrics in MariaDB 10.4 causes errors #494

Change in table information_schema.innodb_metrics in MariaDB 10.4 causes errors #494

TobiasGrave commented Aug 14, 2020 •

edited

Loading

GameCharmer commented Sep 16, 2020

Neurozone commented Oct 4, 2020

Neurozone commented Nov 24, 2020

coderovich commented Dec 20, 2020

lasat commented Dec 28, 2020

TobiasGrave commented Jan 6, 2021

lasat commented Jan 14, 2021

maemigh commented Jan 15, 2021

Change in table information_schema.innodb_metrics in MariaDB 10.4 causes errors #494

Change in table information_schema.innodb_metrics in MariaDB 10.4 causes errors #494

Comments

TobiasGrave commented Aug 14, 2020 • edited Loading

GameCharmer commented Sep 16, 2020

Neurozone commented Oct 4, 2020

Neurozone commented Nov 24, 2020

coderovich commented Dec 20, 2020

lasat commented Dec 28, 2020

TobiasGrave commented Jan 6, 2021

lasat commented Jan 14, 2021

maemigh commented Jan 15, 2021

TobiasGrave commented Aug 14, 2020 •

edited

Loading