renecannao
commented
2 years ago Please also attach the output of SHOW MYSQL VARIABLES; from Admin.
Thanks
Open FimChen opened 2 years ago
renecannao
commented
2 years ago Please also attach the output of SHOW MYSQL VARIABLES; from Admin.
Thanks
FimChen
commented
2 years ago Some supplementary information（if you need more information, please let me know）:
mysql> show mysql variables; +----------------------------------------------------------------------+-----------------------------+ | Variable_name | Value | +----------------------------------------------------------------------+-----------------------------+ | mysql-add_ldap_user_comment | | | mysql-auditlog_filename | /var/lib/proxysql/audit.log | | mysql-auditlog_filesize | 104857600 | | mysql-aurora_max_lag_ms_only_read_from_replicas | 2 | | mysql-auto_increment_delay_multiplex | 5 | | mysql-autocommit_false_is_transaction | false | | mysql-autocommit_false_not_reusable | false | | mysql-automatic_detect_sqli | false | | mysql-binlog_reader_connect_retry_msec | 3000 | | mysql-client_found_rows | true | | mysql-client_host_cache_size | 0 | | mysql-client_host_error_counts | 0 | | 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_client | 10000 | | mysql-connect_timeout_server | 3000 | | mysql-connect_timeout_server_max | 10000 | | mysql-connection_delay_multiplex_ms | 0 | | mysql-connection_max_age_ms | 0 | | mysql-connection_warming | false | | mysql-connpoll_reset_queue_length | 50 | | mysql-default_charset | utf8mb4 | | mysql-default_collation_connection | utf8mb4_0900_ai_ci | | 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_session_track_gtids | OFF | | mysql-default_tx_isolation | READ-COMMITTED | | mysql-enable_client_deprecate_eof | true | | mysql-enable_load_data_local_infile | false | | mysql-enable_server_deprecate_eof | true | | mysql-enforce_autocommit_on_reads | false | | mysql-eventslog_default_log | 1 | | mysql-eventslog_filename | /var/lib/proxysql/event.log | | mysql-eventslog_filesize | 104857600 | | mysql-eventslog_format | 2 | | mysql-firewall_whitelist_enabled | false | | mysql-firewall_whitelist_errormsg | Firewall blocked this query | | mysql-free_connections_pct | 10 | | mysql-handle_unknown_charset | 1 | | mysql-have_compress | true | | mysql-have_ssl | false | | mysql-hostgroup_manager_verbose | 1 | | mysql-init_connect | | | mysql-interfaces | 0.0.0.0:6033 | | mysql-keep_multiplexing_variables | tx_isolation,version | | mysql-kill_backend_connection_when_disconnect | true | | mysql-ldap_user_variable | | | mysql-log_mysql_warnings_enabled | false | | mysql-log_unhealthy_connections | true | | mysql-long_query_time | 1000 | | mysql-max_allowed_packet | 1073741824 | | mysql-max_connections | 10000 | | mysql-max_stmts_cache | 10000 | | mysql-max_stmts_per_connection | 20 | | mysql-max_transaction_idle_time | 14400000 | | 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 | 600 | | 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_max_timeout_count | 3 | | mysql-monitor_groupreplication_healthcheck_timeout | 800 | | mysql-monitor_groupreplication_max_transactions_behind_count | 3 | | mysql-monitor_groupreplication_max_transactions_behind_for_read_only | 1 | | mysql-monitor_history | 600000 | | mysql-monitor_password | monitor | | mysql-monitor_ping_interval | 10000 | | mysql-monitor_ping_max_failures | 3 | | mysql-monitor_ping_timeout | 1000 | | mysql-monitor_query_interval | 60000 | | mysql-monitor_query_timeout | 100 | | mysql-monitor_read_only_interval | 1500 | | mysql-monitor_read_only_max_timeout_count | 3 | | mysql-monitor_read_only_timeout | 500 | | mysql-monitor_replication_lag_count | 1 | | mysql-monitor_replication_lag_interval | 10000 | | 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 | monitor | | mysql-monitor_wait_timeout | true | | mysql-monitor_writer_is_also_reader | true | | mysql-multiplexing | true | | mysql-ping_interval_server_msec | 120000 | | mysql-ping_timeout_server | 500 | | mysql-poll_timeout | 2000 | | mysql-poll_timeout_on_failure | 100 | | mysql-query_cache_size_MB | 256 | | mysql-query_cache_stores_empty_result | true | | mysql-query_digests | true | | mysql-query_digests_grouping_limit | 3 | | mysql-query_digests_lowercase | false | | mysql-query_digests_max_digest_length | 2048 | | mysql-query_digests_max_query_length | 65000 | | mysql-query_digests_no_digits | false | | 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 | 569899 | | mysql-server_version | 8.0.25 | | mysql-servers_stats | true | | mysql-session_idle_ms | 1 | | mysql-session_idle_show_processlist | true | | mysql-sessions_sort | true | | mysql-set_query_lock_on_hostgroup | 0 | | mysql-show_processlist_extended | 0 | | mysql-shun_on_failures | 5 | | mysql-shun_recovery_time_sec | 10 | | mysql-ssl_p2s_ca | | | mysql-ssl_p2s_capath | | | mysql-ssl_p2s_cert | | | mysql-ssl_p2s_cipher | | | mysql-ssl_p2s_crl | | | mysql-ssl_p2s_crlpath | | | mysql-ssl_p2s_key | | | mysql-stacksize | 1048576 | | mysql-stats_time_backend_query | false | | mysql-stats_time_query_processor | false | | mysql-tcp_keepalive_time | 0 | | mysql-threads | 4 | | 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-use_tcp_keepalive | false | | mysql-verbose_query_error | false | | mysql-wait_timeout | 28800000 | +----------------------------------------------------------------------+-----------------------------+ 147 rows in set (0.00 sec)
primary node‘s event.log:
{"client":"6.34.12.190:50864","digest":"0xC51BDBBD6C9BB1D7","duration_us":0,"endtime":"2022-03-29 16:14:15.419451","endtime_timestamp_us":1648627272419451,"event":"COM_QUERY","hostgroup_id":-1,"query":"SET autocommit=0","rows_affected":0,"rows_sent":0,"schemaname":"DTBOARD_META","starttime":"2022-03-29 16:14:15.419451","starttime_timestamp_us":1648627272419451,"thread_id":34,"username":"DTBOARD_META"}
{"client":"6.34.12.190:50864","digest":"0xC51BDBBD6C9BB1D7","duration_us":820,"endtime":"2022-03-29 16:14:15.420975","endtime_timestamp_us":1648627272420975,"event":"COM_STMT_EXECUTE","hostgroup_id":2,"query":"select\n \n TEMPLATE_ID,TEMPLATE_NAME,TEMPLATE_TYPE,TEMPLATE_CLASS,TEMPLATE_HEIGHT,TEMPLATE_WIDTH,TEMPLATE_MODEL,TEMPLATE_ORDER,STATUS,VERSION,CREATE_TIME,UPDATE_TIME,IMG_PATH,LAYOUT_DATA,DRAWING_NUMBER\n \n from vip_manager_style_template_info\n where \n TEMPLATE_ID = ?","rows_affected":0,"rows_sent":1,"schemaname":"DTBOARD_META","server":"6.34.13.80:3306","starttime":"2022-03-29 16:14:15.420155","starttime_timestamp_us":1648627272420155,"thread_id":34,"username":"DTBOARD_META"}
{"client":"6.34.12.190:50864","digest":"0xD6639786E36A2800","duration_us":395,"endtime":"2022-03-29 16:14:15.423978","endtime_timestamp_us":1648627272423978,"event":"COM_QUERY","hostgroup_id":2,"query":"select @@session.transaction_read_only","rows_affected":0,"rows_sent":1,"schemaname":"DTBOARD_META","server":"6.34.13.80:3306","starttime":"2022-03-29 16:14:15.423583","starttime_timestamp_us":1648627272423583,"thread_id":34,"username":"DTBOARD_META"}
{"client":"6.34.12.190:50864","digest":"0xDB3A841EF5443C35","duration_us":295,"endtime":"2022-03-29 16:14:15.426043","endtime_timestamp_us":1648627272426043,"event":"COM_QUERY","hostgroup_id":2,"query":"commit","rows_affected":0,"rows_sent":0,"schemaname":"DTBOARD_META","server":"6.34.13.80:3306","starttime":"2022-03-29 16:14:15.425748","starttime_timestamp_us":1648627272425748,"thread_id":34,"username":"DTBOARD_META"}
{"client":"6.34.12.190:50864","digest":"0xC51BDBBD6C9BB1D7","duration_us":0,"endtime":"2022-03-29 16:14:15.426342","endtime_timestamp_us":1648627272426342,"event":"COM_QUERY","hostgroup_id":-1,"query":"SET autocommit=1","rows_affected":0,"rows_sent":0,"schemaname":"DTBOARD_META","starttime":"2022-03-29 16:14:15.426342","starttime_timestamp_us":1648627272426342,"thread_id":34,"username":"DTBOARD_META"}
{"client":"6.34.12.190:50864","digest":"0xD6639786E36A2800","duration_us":554,"endtime":"2022-03-29 16:14:15.427139","endtime_timestamp_us":1648627272427139,"event":"COM_QUERY","hostgroup_id":2,"query":"select @@session.transaction_read_only","rows_affected":0,"rows_sent":1,"schemaname":"DTBOARD_META","server":"6.34.13.80:3306","starttime":"2022-03-29 16:14:15.426585","starttime_timestamp_us":1648627272426585,"thread_id":34,"username":"DTBOARD_META"}
application's error:
[yusp-community, 6608] ERROR 2022-03-29 16:14:15:857 [XNIO-1 task-17, 6e6bf09d0b5c07c1, 08ac4afbda379503] cn.com.yusys.yusp.commons.mapper.log.SqlLogger.error:54 - {"Time":"9","SqlId":"cn.com.yusys.yusp.repository.mapper.VipManagerStyleTemplateInfoMapper.updateByPrimaryKeySelective","ExecuteSql":"update vip_manager_style_template_info SET TEMPLATE_ID = ?, TEMPLATE_NAME = ?, TEMPLATE_TYPE = ?, TEMPLATE_HEIGHT = ?, TEMPLATE_WIDTH = ?, STATUS = ?, CREATE_TIME = ?, UPDATE_TIME = ?, IMG_PATH = ?, LAYOUT_DATA = ?, DRAWING_NUMBER = ? where TEMPLATE_ID = ?","Success":"false","Parameters":"[b6b6f33b9b1e44aeb841355f2d008bfb, template, 1, 796, 1920, 0, Wed Mar 2320:36:43 CST 2022, Tue Mar 29 16:14:15 CST 2022, 3/2/20220329161415844-48040249.jpg, [{"templateId":"b6b6f33b9b1e44aeb841355f2d008bfb","templateName":"template","type":"TOB2","width":"944","height":"382","sort":0},{"templateId":"b6b6f33b9b1e44aeb841355f2d008bfb","templateName":"template","type":"TOB2","width":"944","height":"382","sort":1},{"templateId":"b6b6f33b9b1e44aeb841355f2d008bfb","templateName":"template","type":"TOB2","width":"944","height":"382","sort":2},{"templateId":"b6b6f33b9b1e44aeb841355f2d008bfb","templateName":"template","type":"TOB2","width":"944","height":"382","sort":3}], 8, b6b6f33b9b1e44aeb841355f2d008bfb]"}
[yusp-community, 6608] INFO 2022-03-29 16:14:15:870 [XNIO-1 task-17, 6e6bf09d0b5c07c1, 08ac4afbda379503] org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions:317 - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
[yusp-community, 6608] INFO 2022-03-29 16:15:11:078 [XNIO-1 task-19, d7d982fa57fab5f5, 127f566d0230f9a1] cn.com.yusys.yusp.commons.mapper.log.SqlLogger.info:41 - {"Time":"3","SqlId":"cn.com.yusys.yusp.repository.mapper.VipManagerStyleTemplateInfoMapper.selectByPrimaryKey","ExecuteSql":"select TEMPLATE_ID,TEMPLATE_NAME,TEMPLATE_TYPE,TEMPLATE_CLASS,TEMPLATE_HEIGHT,TEMPLATE_WIDTH,TEMPLATE_MODEL,TEMPLATE_ORDER,STATUS,VERSION,CREATE_TIME,UPDATE_TIME,IMG_PATH,LAYOUT_DATA,DRAWING_NUMBER from vip_manager_style_template_info where TEMPLATE_ID = ?","Success":"true","Parameters":"[b6b6f33b9b1e44aeb841355f2d008bfb]"}
In addition，directly using the client via 6033 port can execute those SQL successfully. Thanks!!!
Please also attach the output of
SHOW MYSQL VARIABLES;from Admin. Thanks
quenenni
commented
2 years ago Hello,
Sorry to intrude here. I noticed you used the read/write split rules given in the doc page:
mysql_query_rules;
+---------+--------+--------+------------------------+----------------------+--------------+-----------------------+-------+
| rule_id | active | flagIN | match_pattern | negate_match_pattern | re_modifiers | destination_hostgroup | apply |
+---------+--------+--------+------------------------+----------------------+--------------+-----------------------+-------+
| 1 | 1 | 0 | ^SELECT .* FOR UPDATE$ | 0 | CASELESS | 1 | 1 |
| 2 | 1 | 0 | ^SELECT | 0 | CASELESS | 2 | 1 |The thing is I tried the same rules first and the rule 1 (Select .* for update) never catched anything. I always wonder if these rule was kind of a generic explanation for read/write split and users had to manage them themselves.
So I tested with my own basic read/write split rules and since then, it works way better. Not sure yet if all write queries are going to my writer server, but from the proxysql query monitoring, it seems good.
Here are the basic rules I created:
+---------+--------+--------+--------------+---------------+--------------+-----------------------+-----+-------+
| rule_id | active | flagIN | match_digest | match_pattern | re_modifiers | destination_hostgroup | log | apply |
+---------+--------+--------+--------------+---------------+--------------+-----------------------+-----+-------+
| 1 | 1 | 0 | .* | NULL | CASELESS | NULL | 1 | 0 |
| 2 | 1 | 0 | ^DELETE | NULL | CASELESS | 2 | 0 | 1 |
| 3 | 1 | 0 | ^INSERT | NULL | CASELESS | 2 | 0 | 1 |
| 4 | 1 | 0 | ^UPDATE | NULL | CASELESS | 2 | 0 | 1 |
| 41 | 1 | 0 | .* | NULL | CASELESS | 3 | 0 | 1 |
+---------+--------+--------+--------------+---------------+--------------+-----------------------+-----+-------+select * from stats_mysql_query_rules;
+---------+-------+
| rule_id | hits |
+---------+-------+
| 1 | 16821 |
| 2 | 333 |
| 3 | 13 |
| 4 | 1006 |
| 41 | 15469 |
+---------+-------+When I did my tests combining the rules from the doc and mine, the table stats_mysql_query_rules looked like this (with rule 2 = "SELECT .* FOR UPDATES"):
+---------+-------+
| rule_id | hits |
+---------+-------+
| 1 | 16821 |
| 2 | 0 |
| 3 | 333 |
| 4 | 13 |
| 5 | 1006 |
| 41 | 15469 |
+---------+-------+My question would be.. Are the example rules for read/write split in the doc working or is it just an (non functionnal) example of what we have to do/create?
jalescardoso
commented
2 years ago HI @quenenni same thing here
proxysql> select * from stats_mysql_query_rules;
+---------+--------+
| rule_id | hits |
+---------+--------+
| 1 | 0 |
| 2 | 270188 |
| 3 | 0 |
| 4 | 609 |
| 5 | 6413 |
| 6 | 23672 |
| 7 | 319464 |
| 8 | 0 |
+---------+--------+proxysql> 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 | attributes | comment |
+---------+--------+-------------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
| 1 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT.*FOR UPDATE$ | NULL | 0 | CASELESS | NULL | NULL | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | | NULL |
| 2 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT | NULL | 0 | CASELESS | NULL | NULL | 20 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | | NULL |
| 3 | 1 | proxy_mysql | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT.*FOR UPDATE | NULL | 0 | CASELESS | NULL | NULL | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 | | NULL |
| 4 | 1 | proxy_mysql | NULL | 0 | NULL | NULL | NULL | NULL | ^DELETE | NULL | 0 | CASELESS | NULL | NULL | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | | NULL |
| 5 | 1 | proxy_mysql | NULL | 0 | NULL | NULL | NULL | NULL | ^INSERT | NULL | 0 | CASELESS | NULL | NULL | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | | NULL |
| 6 | 1 | proxy_mysql | NULL | 0 | NULL | NULL | NULL | NULL | ^UPDATE | NULL | 0 | CASELESS | NULL | NULL | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | | NULL |
| 7 | 1 | proxy_mysql | 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 |
| 8 | 1 | proxy_mysql | NULL | 0 | NULL | NULL | 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 |
+---------+--------+-------------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
ProxySQL version: mysql> select version(); +-------------------+ | version() | +-------------------+ | 2.3.2-10-g8cd66cf | +-------------------+
OS version: Redhat 7.6
Mysql is MGR： mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 61762dda-5fc4-11ec-b19c-005056b9574e | 6.34.13.80 | 3306 | ONLINE | SECONDARY | 8.0.25 | | group_replication_applier | 681c25a7-5fc4-11ec-ab61-005056b9894e | 6.34.13.78 | 3306 | ONLINE | PRIMARY | 8.0.25 | | group_replication_applier | 82570083-5fc4-11ec-b5d6-005056b9dcd6 | 6.34.13.79 | 3306 | ONLINE | SECONDARY | 8.0.25 | +---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec)
mysql> select * from proxysql_servers; +------------+------+--------+----------------+ | hostname | port | weight | comment | +------------+------+--------+----------------+ | 6.34.13.79 | 6032 | 1 | ProxySQL-node2 | | 6.34.13.80 | 6032 | 1 | ProxySQL-node3 | | 6.34.13.78 | 6032 | 1 | ProxySQL-node1 | +------------+------+--------+----------------+ 3 rows in set (0.01 sec)
mysql> select * from runtime_mysql_servers; +--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------------+ | 1 | 6.34.13.78 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | ProxySQL-node1 | | 2 | 6.34.13.79 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | ProxySQL-node2 | | 2 | 6.34.13.80 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | ProxySQL-node3 | | 2 | 6.34.13.78 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | ProxySQL-node1 | +--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------------+ 4 rows in set (0.01 sec)
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 | +--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------------+ | 1 | 6.34.13.78 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | ProxySQL-node1 | | 2 | 6.34.13.78 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | ProxySQL-node1 | | 2 | 6.34.13.80 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | ProxySQL-node3 | | 2 | 6.34.13.79 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | ProxySQL-node2 | +--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+----------------+ 4 rows in set (0.00 sec)
mysql> select rule_id,active,flagIN,match_pattern,negate_match_pattern,re_modifiers,destination_hostgroup,apply from mysql_query_rules; +---------+--------+--------+------------------------+----------------------+--------------+-----------------------+-------+ | rule_id | active | flagIN | match_pattern | negate_match_pattern | re_modifiers | destination_hostgroup | apply | +---------+--------+--------+------------------------+----------------------+--------------+-----------------------+-------+ | 1 | 1 | 0 | ^SELECT .* FOR UPDATE$ | 0 | CASELESS | 1 | 1 | | 2 | 1 | 0 | ^SELECT | 0 | CASELESS | 2 | 1 |
##############error： This is Complete SQL：
SET autocommit=0; select TEMPLATE_ID,TEMPLATE_NAME,TEMPLATE_TYPE,TEMPLATE_CLASS,TEMPLATE_HEIGHT,TEMPLATE_WIDTH,TEMPLATE_MODEL,
TEMPLATE_ORDER,STATUS,VERSION,CREATE_TIME,UPDATE_TIME,IMG_PATH,LAYOUT_DATA,DRAWING_NUMBER from vip_manager_style_template_info where TEMPLATE_ID = 'b6b6f33b9b1e44aeb841355f2d008bfb'; select @@session.transaction_read_only; update vip_manager_style_template_info SET TEMPLATE_ID = 'b6b6f33b9b1e44aeb841355f2d008bfb', TEMPLATE_NAME = 'template', TEMPLATE_TYPE = '1', TEMPLATE_HEIGHT = 796, TEMPLATE_WIDTH = 1920, STATUS = '0', CREATE_TIME = '2022-03-23 20:36:43', UPDATE_TIME = '2022-03-29 16:16:46.520000', IMG_PATH = '7/15/20220329161646520-154F3437.jpg', LAYOUT_DATA = '[{\"templateId\":\"b6b6f33b9b1e44aeb841355f2d008bfb\",\"templateName\":\"template\",\"type\":\"TOB2\",\"width\":\"944\",\"height\":\"382\",\"sort\":0},{\"templateId\":\"b6b6f33b9b1e44aeb841355f2d008bfb\",\"templateName\":\"template\",\"type\":\"TOB2\",\"width\":\"944\",\"height\":\"382\",\"sort\":1},{\"templateId\":\"b6b6f33b9b1e44aeb841355f2d008bfb\",\"templateName\":\"template\",\"type\":\"TOB2\",\"width\":\"944\",\"height\":\"382\",\"sort\":2},{\"templateId\":\"b6b6f33b9b1e44aeb841355f2d008bfb\",\"templateName\":\"template\",\"type\":\"TOB2\",\"width\":\"944\",\"height\":\"382\",\"sort\":3}]', DRAWING_NUMBER = 8 where TEMPLATE_ID = 'b6b6f33b9b1e44aeb841355f2d008bfb'; commit; SET autocommit=1;primary node‘s proyxsql.log: 2022-03-29 16:16:45 MySQL_Session.cpp:3989:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (2,6.34.13.80,3306,2430193): 1290, The MySQL server is running with the --read-only option so it cannot execute this statement
primary node‘s audit.log.00000001 : {"client_addr":"6.34.12.190:43890","event":"MySQL_Client_Quit","proxy_addr":"0.0.0.0:6033","schemaname":"DTBOARD_META","ssl":false,"thread_id":54,"time":"2022-03-29 16:23:00.173","timestamp":1648542180173,"username":"DTBOARD_META"}
Does anyone know this problem?
Thanks!!