search

sysown / proxysql

High-performance MySQL proxy with a GPL license.
http://www.proxysql.com
GNU General Public License v3.0
6.05k stars 983 forks source link

get "Commands out of sync; you can't run this command now" when I use proxysql2.0.8 #2785

Open wangxin0529 opened 4 years ago

wangxin0529 commented 4 years ago

mysql version is percona5.6 proxysql version is v2.0.8

I set up three read nodes and one write nodes this is the user i set (hostgroup 2000 is read group bcz read qps more than wirte qps)

mysql> select * from mysql_users;
+----------+--------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password           | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+--------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
|          |                    | 1      | 0       | 2000              | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |         |
+----------+--------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
1 row in set (0.00 sec)

These are the variables I set

mysql> show variables like '%%';
+--------------------------------------------------------------+------------------------+
| Variable_name                                                | Value                  |
+--------------------------------------------------------------+------------------------+
| mysql-shun_on_failures                                       | 5                      |
| mysql-shun_recovery_time_sec                                 | 10                     |
| mysql-query_retries_on_failure                               | 1                      |
| mysql-client_multi_statements                                | false                  |
| mysql-connect_retries_delay                                  | 1                      |
| mysql-connection_delay_multiplex_ms                          | 0                      |
| mysql-connection_max_age_ms                                  | 0                      |
| mysql-connect_timeout_server_max                             | 10000                  |
| mysql-eventslog_filename                                     |                        |
| mysql-eventslog_filesize                                     | 104857600              |
| mysql-eventslog_default_log                                  | 0                      |
| mysql-eventslog_format                                       | 1                      |
| mysql-auditlog_filename                                      |                        |
| mysql-auditlog_filesize                                      | 104857600              |
| mysql-default_charset                                        | utf8                   |
| mysql-handle_unknown_charset                                 | 0                      |
| mysql-free_connections_pct                                   | 10                     |
| mysql-session_idle_ms                                        | 1000                   |
| mysql-have_ssl                                               | false                  |
| mysql-client_found_rows                                      | true                   |
| mysql-monitor_enabled                                        | true                   |
| mysql-monitor_connect_timeout                                | 600                    |
| mysql-monitor_ping_max_failures                              | 3                      |
| mysql-monitor_ping_timeout                                   | 1000                   |
| mysql-monitor_read_only_max_timeout_count                    | 3                      |
| mysql-monitor_replication_lag_interval                       | 10000                  |
| mysql-monitor_replication_lag_timeout                        | 1000                   |
| mysql-monitor_groupreplication_healthcheck_interval          | 5000                   |
| mysql-monitor_groupreplication_healthcheck_timeout           | 800                    |
| mysql-monitor_groupreplication_healthcheck_max_timeout_count | 3                      |
| mysql-monitor_galera_healthcheck_interval                    | 5000                   |
| mysql-monitor_galera_healthcheck_timeout                     | 800                    |
| mysql-monitor_galera_healthcheck_max_timeout_count           | 3                      |
| mysql-monitor_replication_lag_use_percona_heartbeat          |                        |
| mysql-monitor_query_interval                                 | 60000                  |
| mysql-monitor_query_timeout                                  | 100                    |
| mysql-monitor_slave_lag_when_null                            | 60                     |
| mysql-monitor_threads_min                                    | 8                      |
| mysql-monitor_threads_max                                    | 128                    |
| mysql-monitor_threads_queue_maxsize                          | 128                    |
| mysql-monitor_wait_timeout                                   | true                   |
| mysql-monitor_writer_is_also_reader                          | true                   |
| mysql-max_allowed_packet                                     | 67108864               |
| mysql-tcp_keepalive_time                                     | 0                      |
| mysql-use_tcp_keepalive                                      | 0                      |
| mysql-throttle_connections_per_sec_to_hostgroup              | 1000000                |
| mysql-max_transaction_time                                   | 14400000               |
| mysql-multiplexing                                           | false                  |
| mysql-log_unhealthy_connections                              | true                   |
| mysql-forward_autocommit                                     | false                  |
| mysql-enforce_autocommit_on_reads                            | false                  |
| mysql-autocommit_false_not_reusable                          | false                  |
| mysql-autocommit_false_is_transaction                        | false                  |
| mysql-verbose_query_error                                    | false                  |
| mysql-hostgroup_manager_verbose                              | 1                      |
| mysql-binlog_reader_connect_retry_msec                       | 3000                   |
| mysql-threshold_query_length                                 | 524288                 |
| mysql-threshold_resultset_size                               | 4194304                |
| mysql-query_digests_max_digest_length                        | 2048                   |
| mysql-query_digests_max_query_length                         | 65000                  |
| mysql-wait_timeout                                           | 28800000               |
| mysql-throttle_max_bytes_per_second_to_client                | 0                      |
| mysql-throttle_ratio_server_to_client                        | 0                      |
| mysql-max_stmts_per_connection                               | 20                     |
| mysql-max_stmts_cache                                        | 10000                  |
| mysql-mirror_max_concurrency                                 | 16                     |
| mysql-mirror_max_queue_length                                | 32000                  |
| mysql-default_max_latency_ms                                 | 1000                   |
| mysql-query_processor_iterations                             | 0                      |
| mysql-query_processor_regex                                  | 1                      |
| mysql-set_query_lock_on_hostgroup                            | 1                      |
| mysql-reset_connection_algorithm                             | 2                      |
| mysql-auto_increment_delay_multiplex                         | 5                      |
| mysql-long_query_time                                        | 1000                   |
| mysql-query_cache_size_MB                                    | 256                    |
| mysql-poll_timeout_on_failure                                | 100                    |
| mysql-keep_multiplexing_variables                            | tx_isolation,version   |
| mysql-kill_backend_connection_when_disconnect                | true                   |
| mysql-client_session_track_gtid                              | true                   |
| mysql-session_idle_show_processlist                          | true                   |
| mysql-show_processlist_extended                              | 0                      |
| mysql-query_digests                                          | true                   |
| mysql-query_digests_lowercase                                | false                  |
| mysql-query_digests_replace_null                             | false                  |
| mysql-query_digests_no_digits                                | false                  |
| mysql-query_digests_normalize_digest_text                    | false                  |
| mysql-query_digests_track_hostname                           | false                  |
| mysql-servers_stats                                          | true                   |
| mysql-default_reconnect                                      | true                   |
| mysql-ssl_p2s_ca                                             |                        |
| mysql-ssl_p2s_cert                                           |                        |
| mysql-ssl_p2s_key                                            |                        |
| mysql-ssl_p2s_cipher                                         |                        |
| mysql-init_connect                                           |                        |
| mysql-ldap_user_variable                                     |                        |
| mysql-add_ldap_user_comment                                  |                        |
| mysql-default_sql_mode                                       |                        |
| mysql-default_time_zone                                      | SYSTEM                 |
| mysql-default_isolation_level                                | READ COMMITTED         |
| mysql-default_transaction_read                               | WRITE                  |
| mysql-default_tx_isolation                                   | READ-COMMITTED         |
| mysql-default_character_set_results                          | NULL                   |
| mysql-default_session_track_gtids                            | OFF                    |
| mysql-default_sql_auto_is_null                               | OFF                    |
| mysql-default_sql_select_limit                               | DEFAULT                |
| mysql-default_sql_safe_updates                               | OFF                    |
| mysql-default_collation_connection                           |                        |
| mysql-default_net_write_timeout                              | 60                     |
| mysql-default_max_join_size                                  | 18446744073709551615   |
| mysql-connpoll_reset_queue_length                            | 50                     |
| mysql-min_num_servers_lantency_awareness                     | 1000                   |
| mysql-aurora_max_lag_ms_only_read_from_replicas              | 2                      |
| mysql-stats_time_backend_query                               | false                  |
| mysql-stats_time_query_processor                             | false                  |
| mysql-query_cache_stores_empty_result                        | true                   |
| mysql-threads                                                | 100                    |
| mysql-max_connections                                        | 2048                   |
| mysql-default_query_delay                                    | 0                      |
| mysql-default_query_timeout                                  | 36000000               |
| mysql-have_compress                                          | true                   |
| mysql-poll_timeout                                           | 2000                   |
| mysql-interfaces                                             | 0.0.0.0:3306           |
| mysql-default_schema                                         | information_schema     |
| mysql-stacksize                                              | 1048576                |
| mysql-server_version                                         | 5.5.30                 |
| mysql-connect_timeout_server                                 | 3000                   |
| mysql-monitor_username                                       | proxysql               |
| mysql-monitor_password                                       | cwE4YIkJo4RYMPPd9ouc67 |
| mysql-monitor_history                                        | 600000                 |
| mysql-monitor_connect_interval                               | 60000                  |
| mysql-monitor_ping_interval                                  | 10000                  |
| mysql-monitor_read_only_interval                             | 1500                   |
| mysql-monitor_read_only_timeout                              | 500                    |
| mysql-ping_interval_server_msec                              | 120000                 |
| mysql-ping_timeout_server                                    | 500                    |
| mysql-commands_stats                                         | true                   |
| mysql-sessions_sort                                          | true                   |
| mysql-connect_retries_on_failure                             | 10                     |
| mysql-server_capabilities                                    | 569899                 |
| admin-stats_credentials                                      | stats:stats            |
| admin-stats_mysql_connections                                | 60                     |
| admin-stats_mysql_connection_pool                            | 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-refresh_interval                                       | 2000                   |
| admin-read_only                                              | false                  |
| admin-hash_passwords                                         | true                   |
| admin-vacuum_stats                                           | true                   |
| admin-version                                                | 2.0.8-67-g877cab1      |
| admin-cluster_username                                       |                        |
| admin-cluster_password                                       |                        |
| admin-cluster_check_interval_ms                              | 1000                   |
| admin-cluster_check_status_frequency                         | 10                     |
| admin-cluster_mysql_query_rules_diffs_before_sync            | 3                      |
| admin-cluster_mysql_servers_diffs_before_sync                | 3                      |
| admin-cluster_mysql_users_diffs_before_sync                  | 3                      |
| admin-cluster_proxysql_servers_diffs_before_sync             | 3                      |
| admin-cluster_mysql_query_rules_save_to_disk                 | true                   |
| admin-cluster_mysql_servers_save_to_disk                     | true                   |
| admin-cluster_mysql_users_save_to_disk                       | true                   |
| admin-cluster_proxysql_servers_save_to_disk                  | true                   |
| admin-checksum_mysql_query_rules                             | true                   |
| admin-checksum_mysql_servers                                 | true                   |
| admin-checksum_mysql_users                                   | true                   |
| admin-web_enabled                                            | false                  |
| admin-web_port                                               | 6080                   |
| admin-admin_credentials                                      | admin:i7xyMpwdiR40K1kf |
| admin-mysql_ifaces                                           | 0.0.0.0:6032           |
+--------------------------------------------------------------+------------------------+

this is query_rules i set

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 |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| 1       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SELECT.*FOR UPDATE$ | NULL          | 0                    | CASELESS     | NULL    | NULL            | 1000                  | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
| 2       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SELECT              | NULL          | 0                    | CASELESS     | NULL    | NULL            | 2000                  | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
| 3       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^INSERT              | NULL          | 0                    | CASELESS     | NULL    | NULL            | 1000                  | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
| 4       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^UPDATE              | NULL          | 0                    | CASELESS     | NULL    | NULL            | 1000                  | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
| 5       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^DELETE              | NULL          | 0                    | CASELESS     | NULL    | NULL            | 1000                  | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
5 rows in set (0.00 sec)

the reason for this error is that the resultset of the previous query has not returned yet and the next query is started. When this error appeared, we restarted the application, and then the error disappeared.But after a while, there will still be this error

Do you have any idea to solve this problem?

renecannao commented 4 years ago

Without any detailed analysis (I don't see any error log attached), because the issue is solved restarting the app, it is likely that the problem is in the application.

wangxin0529 commented 4 years ago

@renecannao I'm sorry for taking so long to reply,because the frequency after this phenomenon becomes less frequent But now there are some similar errors While reporting the error, I observed the proxysql error log image

This output seems to be actively doing some connection recovery