Wrong hostgroup in table stats_mysql_query_digest for cached queries

[garutilorenzo]

Inspecting the table stats_mysql_query_digest to find out the slowest and the cached queries we see an unexpected hostgroup id '9223372036854775807'.

mysql> SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest order by sum_time limit 10;

+------------+----------+---------------------+--------------------+-----------------------------------+
| count_star | sum_time | hostgroup           | digest             | digest_text
+------------+----------+---------------------+--------------------+-----------------------------------+
| 5          | 0        | 9223372036854775807 | 0x625C5FB1CCFDC416 | select id, name from ... 
| 5          | 0        | 9223372036854775807 | 0x0B1BBF2FB16032FF | select id, name from ...
| 5          | 0        | 9223372036854775807 | 0xC6A2A0AA0AF14133 | select id, name from ...

ProxySQL version: ProxySQL version 2.0.6-73-gc746bf7, codename Truls
OS: CentOS Linux release 7.3.1611 (Core)
Kernel: 3.10.0-693.11.1.el7.x86_64 SMP Mon Dec 4 23:52:40 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

[pondix]

Can you please share the full configuration of ProxySQL (omitting sensitive information if present) i.e. mysql_servers, mysql_query_rules, mysql_users, any variables you have set explicitly and any other settings outside of these which you have applied to your configuration.

Ideally we would like a reproducible test case in order to diagnose this more efficiently, if you have any idea what may have triggered this it would be very useful.

Thanks!

[garutilorenzo]

ProxySQL variables:

mysql> show variables;
+-----------------------------------------------------+--------------------------------------------------------+
| Variable_name                                       | Value                                                  |
+-----------------------------------------------------+--------------------------------------------------------+
| admin-admin_credentials                             | admin:proxysql                                         |
| admin-checksum_mysql_query_rules                    | true                                                   |
| admin-checksum_mysql_servers                        | true                                                   |
| admin-checksum_mysql_users                          | true                                                   |
| admin-cluster_check_interval_ms                     | 1000                                                   |
| admin-cluster_check_status_frequency                | 10                                                     |
| admin-cluster_mysql_query_rules_diffs_before_sync   | 3                                                      |
| admin-cluster_mysql_query_rules_save_to_disk        | true                                                   |
| admin-cluster_mysql_servers_diffs_before_sync       | 3                                                      |
| admin-cluster_mysql_servers_save_to_disk            | true                                                   |
| admin-cluster_mysql_users_diffs_before_sync         | 3                                                      |
| admin-cluster_mysql_users_save_to_disk              | true                                                   |
| admin-cluster_password                              |                                                        |
| admin-cluster_proxysql_servers_diffs_before_sync    | 3                                                      |
| admin-cluster_proxysql_servers_save_to_disk         | true                                                   |
| admin-cluster_username                              |                                                        |
| admin-hash_passwords                                | true                                                   |
| admin-mysql_ifaces                                  | 0.0.0.0:6032                                           |
| admin-read_only                                     | false                                                  |
| admin-refresh_interval                              | 2000                                                   |
| admin-stats_credentials                             | stats:stats                                            |
| admin-stats_mysql_connection_pool                   | 60                                                     |
| admin-stats_mysql_connections                       | 60                                                     |
| admin-stats_mysql_query_cache                       | 60                                                     |
| admin-stats_system_cpu                              | 60                                                     |
| admin-stats_system_memory                           | 60                                                     |
| admin-telnet_admin_ifaces                           | (null)                                                 |
| admin-telnet_stats_ifaces                           | (null)                                                 |
| admin-vacuum_stats                                  | true                                                   |
| admin-version                                       | 2.0.6-73-gc746bf7                                      |
| admin-web_enabled                                   | false                                                  |
| admin-web_port                                      | 6080                                                   |
| mysql-add_ldap_user_comment                         |                                                        |
| mysql-auditlog_filename                             |                                                        |
| mysql-auditlog_filesize                             | 104857600                                              |
| mysql-auto_increment_delay_multiplex                | 5                                                      |
| mysql-autocommit_false_is_transaction               | false                                                  |
| mysql-autocommit_false_not_reusable                 | false                                                  |
| mysql-binlog_reader_connect_retry_msec              | 3000                                                   |
| mysql-client_found_rows                             | true                                                   |
| mysql-client_multi_statements                       | true                                                   |
| mysql-client_session_track_gtid                     | true                                                   |
| mysql-commands_stats                                | true                                                   |
| mysql-connect_retries_delay                         | 1                                                      |
| mysql-connect_retries_on_failure                    | 10                                                     |
| mysql-connect_timeout_server                        | 1000                                                   |
| mysql-connect_timeout_server_max                    | 10000                                                  |
| mysql-connection_delay_multiplex_ms                 | 0                                                      |
| mysql-connection_max_age_ms                         | 0                                                      |
| mysql-connpoll_reset_queue_length                   | 50                                                     |
| mysql-default_charset                               | utf8                                                   |
| mysql-default_max_latency_ms                        | 1000                                                   |
| mysql-default_query_delay                           | 0                                                      |
| mysql-default_query_timeout                         | 36000000                                               |
| mysql-default_reconnect                             | true                                                   |
| mysql-default_schema                                | information_schema                                     |
| mysql-default_sql_mode                              |                                                        |
| mysql-default_time_zone                             | SYSTEM                                                 |
| mysql-enforce_autocommit_on_reads                   | false                                                  |
| mysql-eventslog_default_log                         | 0                                                      |
| mysql-eventslog_filename                            |                                                        |
| mysql-eventslog_filesize                            | 104857600                                              |
| mysql-eventslog_format                              | 1                                                      |
| mysql-forward_autocommit                            | false                                                  |
| mysql-free_connections_pct                          | 10                                                     |
| mysql-have_compress                                 | true                                                   |
| mysql-have_ssl                                      | false                                                  |
| mysql-hostgroup_manager_verbose                     | 1                                                      |
| mysql-init_connect                                  |                                                        |
| mysql-interfaces                                    | 0.0.0.0:3306;0.0.0.0:3307;0.0.0.0:3308;/tmp/mysql.sock |
| mysql-keep_multiplexing_variables                   | tx_isolation,version                                   |
| mysql-kill_backend_connection_when_disconnect       | true                                                   |
| mysql-ldap_user_variable                            |                                                        |
| mysql-long_query_time                               | 1000                                                   |
| mysql-max_allowed_packet                            | 33554432                                               |
| mysql-max_connections                               | 2048                                                   |
| mysql-max_stmts_cache                               | 10000                                                  |
| mysql-max_stmts_per_connection                      | 20                                                     |
| mysql-max_transaction_time                          | 14400000                                               |
| mysql-min_num_servers_lantency_awareness            | 1000                                                   |
| mysql-mirror_max_concurrency                        | 16                                                     |
| mysql-mirror_max_queue_length                       | 32000                                                  |
| mysql-monitor_connect_interval                      | 60000                                                  |
| mysql-monitor_connect_timeout                       | 200                                                    |
| mysql-monitor_enabled                               | true                                                   |
| mysql-monitor_galera_healthcheck_interval           | 5000                                                   |
| mysql-monitor_galera_healthcheck_max_timeout_count  | 3                                                      |
| mysql-monitor_galera_healthcheck_timeout            | 800                                                    |
| mysql-monitor_groupreplication_healthcheck_interval | 5000                                                   |
| mysql-monitor_groupreplication_healthcheck_timeout  | 800                                                    |
| mysql-monitor_history                               | 600000                                                 |
| mysql-monitor_password                              | xxxxxxxx                                               |
| mysql-monitor_ping_interval                         | 2000                                                   |
| mysql-monitor_ping_max_failures                     | 1                                                      |
| mysql-monitor_ping_timeout                          | 750                                                    |
| mysql-monitor_query_interval                        | 60000                                                  |
| mysql-monitor_query_timeout                         | 100                                                    |
| mysql-monitor_read_only_interval                    | 1000                                                   |
| mysql-monitor_read_only_max_timeout_count           | 3                                                      |
| mysql-monitor_read_only_timeout                     | 800                                                    |
| mysql-monitor_replication_lag_interval              | 2000                                                   |
| mysql-monitor_replication_lag_timeout               | 1000                                                   |
| mysql-monitor_replication_lag_use_percona_heartbeat |                                                        |
| mysql-monitor_slave_lag_when_null                   | 60                                                     |
| mysql-monitor_threads_max                           | 128                                                    |
| mysql-monitor_threads_min                           | 8                                                      |
| mysql-monitor_threads_queue_maxsize                 | 128                                                    |
| mysql-monitor_username                              | xxxxxxxx                                               |
| mysql-monitor_wait_timeout                          | true                                                   |
| mysql-monitor_writer_is_also_reader                 | true                                                   |
| mysql-multiplexing                                  | true                                                   |
| mysql-ping_interval_server_msec                     | 2000                                                   |
| mysql-ping_timeout_server                           | 200                                                    |
| mysql-poll_timeout                                  | 2000                                                   |
| mysql-poll_timeout_on_failure                       | 100                                                    |
| mysql-query_cache_size_MB                           | 1024                                                   |
| mysql-query_cache_stores_empty_result               | true                                                   |
| mysql-query_digests                                 | true                                                   |
| mysql-query_digests_lowercase                       | false                                                  |
| mysql-query_digests_max_digest_length               | 2048                                                   |
| mysql-query_digests_max_query_length                | 65000                                                  |
| mysql-query_digests_normalize_digest_text           | false                                                  |
| mysql-query_digests_replace_null                    | false                                                  |
| mysql-query_digests_track_hostname                  | false                                                  |
| mysql-query_processor_iterations                    | 0                                                      |
| mysql-query_processor_regex                         | 1                                                      |
| mysql-query_retries_on_failure                      | 1                                                      |
| mysql-reset_connection_algorithm                    | 2                                                      |
| mysql-server_capabilities                           | 569867                                                 |
| mysql-server_version                                | 5.7.26                                                 |
| mysql-servers_stats                                 | true                                                   |
| mysql-session_idle_ms                               | 1000                                                   |
| mysql-session_idle_show_processlist                 | true                                                   |
| mysql-sessions_sort                                 | true                                                   |
| mysql-set_query_lock_on_hostgroup                   | 1                                                      |
| mysql-show_processlist_extended                     | 0                                                      |
| mysql-shun_on_failures                              | 1                                                      |
| mysql-shun_recovery_time_sec                        | 10                                                     |
| mysql-ssl_p2s_ca                                    |                                                        |
| mysql-ssl_p2s_cert                                  |                                                        |
| mysql-ssl_p2s_cipher                                |                                                        |
| mysql-ssl_p2s_key                                   |                                                        |
| mysql-stacksize                                     | 1048576                                                |
| mysql-stats_time_backend_query                      | false                                                  |
| mysql-stats_time_query_processor                    | false                                                  |
| mysql-threads                                       | 24                                                     |
| mysql-threshold_query_length                        | 524288                                                 |
| mysql-threshold_resultset_size                      | 4194304                                                |
| mysql-throttle_connections_per_sec_to_hostgroup     | 1000000                                                |
| mysql-throttle_max_bytes_per_second_to_client       | 0                                                      |
| mysql-throttle_ratio_server_to_client               | 0                                                      |
| mysql-verbose_query_error                           | false                                                  |
| mysql-wait_timeout                                  | 28800000                                               |
+-----------------------------------------------------+--------------------------------------------------------+

ProxySQL Server:

mysql> select * from mysql_servers;
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
| hostgroup_id | hostname        | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment      |
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
| 10           | master  | 3306 | 0         | ONLINE | 1      | 0           | 100             | 0                   | 0       | 0              |              |
| 20           | master  | 3306 | 0         | ONLINE | 1      | 0           | 100             | 1                   | 0       | 0              |              |
| 20           | slave1  | 3306 | 0         | ONLINE | 50     | 0           | 100             | 1                   | 0       | 0              |              |
| 20           | slave2  | 3306 | 0         | ONLINE | 50     | 0           | 100             | 1                   | 0       | 0              |              |
| 20           | slave3  | 3306 | 0         | ONLINE | 50     | 0           | 100             | 1                   | 0       | 0              |              |
| 30           | slave1  | 3306 | 0         | ONLINE | 1      | 0           | 100             | 3600                | 0       | 0              | slow replica |
| 30           | slave2  | 3306 | 0         | ONLINE | 1      | 0           | 100             | 3600                | 0       | 0              | slow replica |
| 30           | slave3  | 3306 | 0         | ONLINE | 1      | 0           | 100             | 3600                | 0       | 0              | slow replica |
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
8 rows in set (0.00 sec)

ProxySQL query rules:

mysql> select * from mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| 50      | 1      | NULL     | NULL       | 0      | NULL        | NULL       | 3307       | NULL   | NULL         | NULL          | 0                    | CASELESS     | NULL    | NULL            | 30                    | 4000      | 1                  | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
| 100     | 1      | NULL     | NULL       | 0      | NULL        | NULL       | 3308       | NULL   | NULL         | NULL          | 0                    | CASELESS     | NULL    | NULL            | 20                    | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
| 300     | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | .*            | 0                    | CASELESS     | NULL    | NULL            | 10                    | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+

We have upgraded ProxySQL from 2.0.4 to 2.0.6 (using yum) without doing proxysql --initial

[unixy]

Seeing that as well:

mysql> SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+------------+----------+---------------------+--------------------+------------------------------------+
| count_star | sum_time | hostgroup           | digest             | digest_text                        |
+------------+----------+---------------------+--------------------+------------------------------------+
| 2          | 3244     | 1                   | 0xA6C46E5523B65891 | select a,b from db_test limit ? |
| 20304      | 0        | 9223372036854775807 | 0xA6C46E5523B65891 | select a,b from db_test limit ? |
| 20306      | 0        | 1                   | 0x226CD90D52A2BA0B | select @@version_comment limit ?   |
+------------+----------+---------------------+--------------------+------------------------------------+

9223372036854775807 = 2^63 - 1
So the math says this could be an overflow or an error or some sort. I'm on 2.0.6 as well:

# proxysql -V
ProxySQL version 2.0.6-percona-1.1, codename Truls

There's no caching.

Thanks

[renecannao]

I confirm the issue.

Although, this must be related to caching.
@unixy : it seems you have run 20306 connections, for 2 times the query was executed on backend (hostgroup=1, sum_time=3244) , while for 20304 times the query was returned from the cache (hostgroup=-1 (incorrectly reported as 9223372036854775807) and sum_time=0).

[renecannao]

Bug introduced in 2.0.6 , and now fixed in 2.0.8 .
Thank you for the report!