Home CMS Planet MySQL

    PostHeaderIcon Newsfeeds

    Planet MySQL
    Planet MySQL - https://planet.mysql.com

    • Fun with Bugs #80 - On MySQL Bug Reports I am Subscribed to, Part XVI
      Today I'd like to continue my review of public MySQL bug reports with a list of some bugs I've subscribed to over last 3 weeks. It's already long enough and includes nice cases to check and share. Note that I usually subscribe to a bug either because it directly affects me or customers I work with, or I consider it technically interesting (so I mostly care about InnoDB, replication, partitioning and optimizer bugs), or it's a "metabug" - a problem in the way public bug report is handled by Oracle engineers. These are my interests related to MySQL bugs.As usual, I start with the oldest bugs and try to mention bug reporters by name with links to their other reports whenever this may give something useful to a reader. I try to check if MariaDB is also affected in some cases. Check also my summary comments at the end of this blog post. Bug #94148 - "Unnecessary Shared lock on parent table During UPDATE on a child table". In this bug report Uday Varagani reasonably pointed out that formally there is no need to lock parent row when column NOT included in the foreign key gets updated. This happens though when this column is included into the index used to support foreign key constraint. IMHO it's a reasonable feature request and both Trey Raymond and Sveta Smirnova tried their best to  highlight this, but this report now has a "Need Feedback" status with a request to explain new algorithm suggested. It's simple - "Stop it", check that column changed is NOT the one foreign key is defined on, even if it's in the same index...I see no reason NOT to verify this as a reasonable feature request. Is it a new policy that every feature request should come with details on how to implement it? I truly doubt. Bug #94224 - "[5.6] Optimizer reconsiders index based on index definition order, not value". Domas Mituzas found yet another case (see also Bug #36817 - "Non optimal index choice, depending on index creation order" from Jocelyn Fournier, the bug I verified more than 10 years ago) when in MySQL order of index definition matters more for optimizer than anything else.  My quick check shows that MariaDB 10.3.7 is not affected: MariaDB [test]> explain select distinct b from t1 where c not in (0) and d > 0;+------+-------------+-------+-------+---------------+--------------------+---------+------+------+-------------+| id   | select_type | table | type  | possible_keys | key            | key_len| ref  | rows | Extra                    |+------+-------------+-------+-------+---------------+--------------------+--------+------+------+-------------+|    1 | SIMPLE      | t1    | index | NULL          | non_covering_index | 9    | NULL |    1 | Using where |+------+-------------+-------+-------+---------------+--------------------+---------+------+------+-------------+1 row in set (0.002 sec)MariaDB [test]> alter table t1 add index covering_index (b, c, d);Query OK, 0 rows affected (0.149 sec)Records: 0  Duplicates: 0  Warnings: 0MariaDB [test]> explain select distinct b from t1 where c not in (0) and d > 0;+------+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+| id   | select_type | table | type  | possible_keys | key            | key_len| ref  | rows | Extra                    |+------+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+|    1 | SIMPLE      | t1    | index | NULL          | covering_index | 14| NULL |    1 | Using where; Using index |+------+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+1 row in set (0.025 sec) Fortunately MySQL 8 is no longer affected. Unfortunately we do not see a public comment showing the results of testing on MySQL 5.7 (or any version, for that matter), from engineer who verified the bug. I already pointed out that this "metabug" becomes popular in my previous blog post. Bug #94243 - "WL#9508 introduced non-idiomatic potentially-broken C macros". Laurynas Biveinis from Percona found new code that in ideal world wound not pass any serious code review. Bug #94251 - "Aggregate function result is dependent by window is defined directly or as named". This bug was reported by Владислав Сокол. From what I see:MariaDB [test]> WITH RECURSIVE cte AS (    -> SELECT 1 num    -> UNION ALL    -> SELECT num+1 FROM cte WHERE num < 5    -> )    -> SELECT num, COUNT(*) OVER (frame) cnt_named, COUNT(*) OVER (ORDER BY numDESC) cnt_direct    -> FROM cte    -> WINDOW frame AS (ORDER BY num DESC);+------+-----------+------------+| num  | cnt_named | cnt_direct |+------+-----------+------------+|    1 |         5 |          5 ||    2 |         4 |          4 ||    3 |         3 |          3 ||    4 |         2 |          2 ||    5 |         1 |          1 |+------+-----------+------------+5 rows in set (0.117 sec)MariaDB [test]> WITH RECURSIVE cte AS (    -> SELECT 1 num    -> UNION ALL    -> SELECT num+1 FROM cte WHERE num < 5    -> )    -> SELECT num, COUNT(*) OVER (frame) cnt_named, COUNT(*) OVER (ORDER BY numDESC) cnt_direct    -> FROM cte    -> WINDOW frame AS (ORDER BY num DESC)    -> ORDER BY num desc;+------+-----------+------------+| num  | cnt_named | cnt_direct |+------+-----------+------------+|    5 |         1 |          1 ||    4 |         2 |          2 ||    3 |         3 |          3 ||    2 |         4 |          4 ||    1 |         5 |          5 |+------+-----------+------------+5 rows in set (0.003 sec)MariaDB 10.3.7 is NOT affected. Bug #94283 - "MySQL 8.0.15 is slower than MySQL 5.7.25". Percona's CTO Vadim Tkachenko reported that MySQL 8.0.15 is notably slower than 5.7.25 on a simple oltp_read_write sysbench test. He had recently written a separate blog post about this, with more details.There is one detail to clarify based on today's comment from Peter Zaitsev (was the same default character set used), but as my dear friend Sinisa Milivojevic verified the bug without any questions, requests or his own test outputs shared, we can assume that Oracle officially accepted this performance regression (even though "regression" tag was not set).Check also later Bug #94387 - "MySQL 8.0.15 is slower than MySQL 5.7.25 in read only workloads", yet another performance regression report from Vadim, where he found that on read only (sysbench oltp_point_select) all in memory workloads MySQL 8.0.15 may also be slower than MySQL 5.7.25. Bug #94302 - "reset master could not break dump thread in some cases". This bug was reported by Ashe Sun. This is definitely a corner case, as it happens only master is still writing to the very first binary log. We can not find out from public comments in the bug report if any other versions besides 5.7.x are affected. This is yet another "metabug" - during my days in Oracle's MySQL bugs verification team we had to check on all versions still supported and present the results explicitly. Bug #94319 - "Format_description_log_event::write can cause segfaults". Nice bug report by Manuel Ung from Facebook. Bug #94330 - "Test for possible compressed failures before upgrade?". Change of zlib version starting from MySQL 5.7.24 means that some operations for InnoDB tables with ROW_FORMAT=COMPRESSED that previously worked may start to fail. In this report Monty Solomon asks for some way to determine if there will be a problem with existing compressed tables before upgrading to 5.7.24. The bug is still "Open". Bug #94338 - "Dirty read-like behavior in READ COMMITTED transaction". Bug reporter, Masaki Oguro, stated that MySQL 8 is not affected (only 5.6 and 5.7) and the bug is verified on these versions, so we should assume it's really the case. But I miss public comment showing the result of testing on recent MySQL 8.0.15. Bug #94340 - "backwards incompatible changes in 8.0: Error number: 3747". Simon Mudd complains about incompatible change in 8.0.13 that does not allow slave to easily switch from SBR to RBR without restart (and was not clearly documented as a change in behavior). Make sure to read all comments. Bug #94370 - "Performance regression of btr_cur_prefetch_siblings". Nice bug report with a patch from Zhai Weixiang. Bug #94383 - "simple ALTER cause unnecessary InnoDB index rebuilds, 5.7.23 or later 5.7 rlses". In this bug report Mikhail Izioumtchenko presented the detailed analysis and suggested diagnostics patches to show what really happens and why. This bug is also a regression of a kind, so while testing results are presented, I still think that it could be processed better according to the good old rules I have in mind. Bug #94394 - "Absence of mysql.user leads to auto-apply of --skip-grant-tables". Great finding by Ceri Williams from Percona. Sveta Smirnova provided a separate MTR test case and clarified the impact of the bug. Surely this is also a regression comparing to MySQL 5.7, as there you can not start MySQL if mysql.user table is missing. I leave it to a reader to decide if there is any security-related impact of this bug... Bug #94396 - "Error message too broad: The used command is not allowed with this MySQL version". This bug was reported by my former colleague in Percona Support, famous Bill Karwin. Informative error messages matter for good user experience. We rely on MySQL in a same way as that guys on top of dolphins pyramid on this strange monument in some court somewhere at the Lanes. Reliable foundation matters, so regressions should better be avoided. To summarize: Looks like it's time for Oracle to spend some efforts to make MySQL 8 great again, by fixing some of the bugs mentioned above, especially performance regressions vs MySQL 5.7 found recently by Vadim Tkachenko from Percona. Oracle continues to introduce backward-incompatible changes in behavior in minor MySQL 8.0.x releases at GA stage. This is not really good for any production environment. Asking bug reporters to provide "the basics of such a new algorithm" when they complain that current one is wrong or not optimal is a new word in bugs processing! When I joined MySQL bugs verification team in 2005 we've set up a culture of bugs processing that included, among other things, presenting in a public comment any successful or unsuccessful attempt to verify the bug, by copy-pasting all commands and statements used along with the outputs, whenever possible and with enough context to show what was really checked. I've studied this approach from Oracle's Tom Kyte over the previous 10 years when I followed him closely. I used to think it's standard for more than a decade already, a kind of my (and not only my) "heritage". It's sad to see this approach is no longer followed by many Oracle engineers who process bugs, in too many cases. Oracle engineers still do not use "regression" tag when setting "Verified" status for obviously regression bugs. I think bug reporters should care then to always set it when they report regression of any kind.

    • Percona Live 2019 First Sneak Peek!
      We know you’ve been really looking forward to a glimpse of what to expect at Percona Live Austin, so here is the first sneak peek of the agenda! Our conference committee has been reviewing hundreds of talks over the last few weeks and is delighted to present some initial talks. New features in MySQL 8.0 Replication by Luís Soares, Oracle OSS Shaping the Future of Privacy & Data Protection by Cristina DeLisle, XWiki SAS Galera Cluster New Features by Seppo Jaakola, Codership MySQL Security and Standardization at PayPal by Stacy Yuan &  Yashada Jadha, PayPal Mailchimp Scale: a MySQL Perspective by John Scott, Mailchimp The State of Databases in 2019 by Dinesh Joshi, Apache Cassandra PingCAP will be sponsoring the TiDB track and have a day of really exciting content to share! Liu Tang, Chief Engineer at PingCAP, will be presenting: Using Chaos Engineering to Build a Reliable TiDB. Keep your eye out for more coming soon! We could not put on this conference without the support of our sponsors. By being a sponsor at Percona Live it gives companies the opportunity to showcase their products and services, interact with the community for invaluable face time, meet with users or customers and showcase their recruitment opportunities. It’s with great pleasure to announce the first round of sponsors for Percona Live! Diamond Sponsors     Silver Sponsors If you’d like to find out more about being a sponsor, download the prospectus here   Stay tuned for more updates on the conference agenda! 

    • Shinguz: FromDual Backup and Recovery Manager for MariaDB and MySQL 2.1.0 has been released
      FromDual has the pleasure to announce the release of the new version 2.1.0 of its popular Backup and Recovery Manager for MariaDB and MySQL (brman). The new FromDual Backup and Recovery Manager can be downloaded from here. How to install and use the Backup and Recovery Manager is describe in FromDual Backup and Recovery Manager (brman) installation guide. In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the FromDual Bugtracker or just send us an email. Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com. Upgrade from 1.2.x to 2.1.0 brman 2.1.0 requires a new PHP package for ssh connections. shell> sudo apt-get install php-ssh2 shell> cd ${HOME}/product shell> tar xf /download/brman-2.1.0.tar.gz shell> rm -f brman shell> ln -s brman-2.1.0 brman Changes in FromDual Backup and Recovery Manager 2.1.0 This release is a new major release series. It contains a lot of new features. We have tried to maintain backward-compatibility with the 1.2 and 2.0 release series. But you should test the new release seriously! You can verify your current FromDual Backup Manager version with the following command: shell> fromdual_bman --version shell> bman --version FromDual Backup Manager Usage (--help) updated. Some WARN severities downgraded to INFO to keep mail output clean. Error messages made more flexible and fixed PHP library advice. Split some redundant code from bman library into brman library. Security fix: Password from config file is hidden now. Bug on simulation of physical backup fixed (xtrabackup_binlog_info not found). Options --backup-name and --backup-overwrite introduced for restore automation. Minor typo bugs fixed. Option --options remove. Sort order for schema backup changed to ORDER BY ASC. 2 PHP errors fixed for simulation. Maskerade API added. Physical backup sftp archiving with special characters (+foodmarat) in archive directory name fixed. FromDual Recovery Manager Rman has progress report. Full logical restore is implemented. Schema logical restore is implemented. Physical restore is implemented. Physical restore of compressed backups is implemented. Option --cleanup-first was implemented for physical backup as well. Option: --stop-instance implemented. FromDual Backup Manager Catalog No changes. Subscriptions for commercial use of FromDual Backup and Recovery Manager you can get from from us. Taxonomy upgrade extras:  Backup Restore Recovery pitr brman release bman rman

    • ProxySQL Native Galera Support
      One of the latest enhancements in ProxySQL v2.0 is native support for Galera Clustering. In previous versions of ProxySQL an external scheduler was required to track the status of Galera nodes however due to the widespread use we have now integrated support directly in ProxySQL's core configuration. This blog discusses how to take an advantage of the new feature and integrate ProxySQL with Galera Cluster to monitor node status and implement read-write split with ProxySQL using a 3x node cluster. So, let’s have a look at whats new in ProxySQL's admin interface! In the admin interface you'll find the following new tables and variables available to configure your Galera cluster and monitor the cluster's status. ProxySQL Admin The definition of the mysql_galera_hostgroups table used to configure your Galera cluster is as follows: +--------------------------------------------+ | tables | +--------------------------------------------+ | [..] | | mysql_galera_hostgroups | | [..] | | runtime_mysql_galera_hostgroups | | [..] | +--------------------------------------------+ CREATE TABLE mysql_galera_hostgroups ( writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY, backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL, reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0), offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0), active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1, writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1,2)) NOT NULL DEFAULT 0, max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0, comment VARCHAR, UNIQUE (reader_hostgroup), UNIQUE (offline_hostgroup), UNIQUE (backup_writer_hostgroup)); The fields have the following semantics: writer_hostgroup: the id of the hostgroup that will contain all the members that are writersbackup_writer_hostgroup: if the cluster is running in multi-primary mode (i.e. there are multiple nodes with read_only=0) and max_writers is set to a smaller number than the total number of nodes, the additional nodes are moved to this backup writer hostgroup reader_hostgroup: the id of the hostgroup that will contain all the members that are readers (i.e. nodes that have read_only=1) offline_hostgroup: when ProxySQL's monitoring determines a host to be OFFLINE, the host will be moved to the offline_hostgroup active: a boolean value (0 or 1) to activate a hostgroup max_writers: controls the maximum number of allowable nodes in the writer hostgroup, as mentioned previously, additional nodes will be moved to the backup_writer_hostgroup writer_is_also_reader: when 1, a node in the writer_hostgroup will also be placed in the reader_hostgroup so that it will be used for reads. When set to 2, the nodes from backup_writer_hostgroup will be placed in the reader_hostgroup_, instead of the node(s) in the writer_hostgroup. max_transactions_behind: determines the maximum number of writesets a node in the cluster can have queued before the node is SHUNNED to prevent stale reads (this is determined by querying the wsrep_local_recv_queue Galera variable). For reference: https://github.com/sysown/proxysql/wiki/Main-(runtime)#mysql_galera_hostgroups The definition of the monitor.mysql_server_galera_log table used to monitor your Galera cluster is as follows: +------------------------------------+ | tables | +------------------------------------+ | [..] | | mysql_server_galera_log | | [..] | +------------------------------------+ CREATE TABLE mysql_server_galera_log ( hostname VARCHAR NOT NULL, port INT NOT NULL DEFAULT 3306, time_start_us INT NOT NULL DEFAULT 0, success_time_us INT DEFAULT 0, primary_partition VARCHAR NOT NULL DEFAULT 'NO', read_only VARCHAR NOT NULL DEFAULT 'YES', wsrep_local_recv_queue INT DEFAULT 0, wsrep_local_state INT DEFAULT 0, wsrep_desync VARCHAR NOT NULL DEFAULT 'NO', wsrep_reject_queries VARCHAR NOT NULL DEFAULT 'NO', wsrep_sst_donor_rejects_queries VARCHAR NOT NULL DEFAULT 'NO', error VARCHAR, PRIMARY KEY (hostname, port, time_start_us)) The fields have the following semantics: hostname: the Galera node hostname or IP address port: the Galera MySQL service port time_start_us: the time the monitor check was started (microseconds) success_time_us: the amount of time for the monitor check to complete (microseconds) primary_partition: whether the Galera member node is PRIMARY read_only: whether the node is READ ONLY wsrep_local_recv_queue: the length of the receive queue during the check see wsrep_local_recv_queue wsrep_local_state: the node's local state number see wsrep_local_state wsrep_desync: whether the node has been set to desync see wsrep_desync wsrep_reject_queries: whether the node has been set to reject queries see wsrep_reject_queries wsrep_sst_donor_rejects_queries: whether the node has been set to reject queries when donor see wsrep_sst_donor_reject_queries error: any error messages that occurred while checking a node The global variables to control timeout and interval check: ProxySQL Admin> select * from global_variables where variable_name like '%monitor_galera%'; +-------------------------------------------+----------------+ | variable_name | variable_value | +-------------------------------------------+----------------+ | mysql-monitor_galera_healthcheck_interval | 5000 | | mysql-monitor_galera_healthcheck_timeout | 800 | +-------------------------------------------+----------------+ ProxySQL Configuration We will configure the 3x node cluster in ProxySQL as follows: one writer node responsible for handling all write traffic one backup writer node which will be available as a standby in case the primary writer node goes offline (or becomes unavailable for writes) one reader node for handling SELECT traffic The nodes that will be used are: 172.16.1.112: db-node01 172.16.1.113: db-node02 172.16.1.114: db-node03 First connect to the admin interface to start configuring ProxySQL: mysql -P6032 -uadmin -padmin -h 127.0.0.1 --prompt "ProxySQL Admin> " Now we can set up ProxySQL’s behavior for our Galera cluster. Lets setup the following hostgroups: offline_hostgroup with hostgroup_id=1 writer_hostgroup with hostgroup_id=2 reader_hostgroup with hostgroup_id=3 backup_writer_hostgroup with hostgroup_id=4 We'll set max_writers=1 to ensure we only have 1x writer at a time and also configure the writer to be dedicated for writes ONLY so we'll also set writer_is_also_reader=0. INSERT INTO mysql_galera_hostgroups (offline_hostgroup, writer_hostgroup, reader_hostgroup, backup_writer_hostgroup, active, max_writers, writer_is_also_reader, max_transactions_behind) VALUES (1,2,3,4,1,1,0,100); We need to manually populate the mysql_servers table with information about our Galera nodes. The node with the lowest weight will be moved to the backup_writer_hostgroup automatically after loading configuration into runtime, for now we'll just add it to the writer_hostgroup. INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (2,'172.16.1.112',3306,100); INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (2,'172.16.1.113',3306,10); INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (3,'172.16.1.114',3306,100); Now, we can verify the configured tables: ProxySQL Admin> select hostgroup_id,hostname,port,status,weight,max_connections from mysql_servers; +--------------+--------------+------+--------+--------+-----------------+ | hostgroup_id | hostname | port | status | weight | max_connections | +--------------+--------------+------+--------+--------+-----------------+ | 2 | 172.16.1.112 | 3306 | ONLINE | 100 | 1000 | | 2 | 172.16.1.113 | 3306 | ONLINE | 10 | 1000 | | 3 | 172.16.1.114 | 3306 | ONLINE | 100 | 1000 | +--------------+--------------+------+--------+--------+-----------------+ ProxySQL Admin> select * from mysql_galera_hostgroups; +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+ | writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment | +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+ | 2 | 4 | 3 | 1 | 1 | 1 | 0 | 100 | NULL | +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+ 1 row in set (0.00 sec) We can now load our configuration to runtime and also save the configuration to disk to persist across restarts: LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; After loading the configuration to runtime, we can now see that host 172.16.1.113, which is configured with a lower weight, has been moved to hostgroup 4. To recap, this happened because we configured ProxySQL to have max_writers=1 and backup_writer_hostgroup=4. The backup writer node will only be used in case the node 172.16.1.112 becomes unavailable. ProxySQL Admin> select hostgroup,srv_host,status,ConnUsed,MaxConnUsed,Queries,Latency_us from stats.stats_mysql_connection_pool order by srv_host; +-----------+--------------+--------+----------+-------------+-----------+------------+ | hostgroup | srv_host | status | ConnUsed | MaxConnUsed | Queries | Latency_us | +-----------+--------------+--------+----------+-------------+-----------+------------+ | 2 | 172.16.1.112 | ONLINE | 3 | 4 | 930742390 | 118 | | 4 | 172.16.1.113 | ONLINE | 0 | 0 | 0 | 136 | | 3 | 172.16.1.114 | ONLINE | 1 | 1 | 233130345 | 123 | +-----------+--------------+--------+----------+-------------+-----------+------------+ Now it’s time to go ahead and define some query rules, which will handle read/write split. For illustrative purposes we'll just use some generic rules to redirect SELECT traffic (NOTE: IT IS NOT RECOMMENDED TO USE GENERIC QUERY RULES IN A PRODUCTION ENVIRONMENT, QUERY RULES SHOULD BE GENERATED FOR SPECIFIC QUERIES OR QUERY PATTERNS INSTEAD). The default_hostgroup for the application user in the mysql_users table is set to 2 i.e the writer_hostgroup. 1st Rule: Query processor scans the query rule to find a match for ^SELECT.* pattern and if a match is found, ProxySQL will forward these queries to destination_hostgroup=3. 2nd Rule: Queries with a ^SELECT.* FOR UPDATE pattern should always be served from the writer hostgroup, so we must set the destination_hostgroup=2. All traffic not matching the above criteria will be routed to the default hostgroup i.e. the writer_hostgroup INSERT INTO mysql_query_rules (active, match_digest, destination_hostgroup, apply) VALUES (1, '^SELECT.*',3, 0); INSERT INTO mysql_query_rules (active, match_digest, destination_hostgroup, apply) VALUES (1, '^SELECT.* FOR UPDATE',2, 1); LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK; Make sure read_only is enabled in MySQL for any hosts that should be part of the reader_hostgroup and optionally set writer_is_also_reader=0 if you want to prevent hosts in your writer_hostgroup to be used for reads. db-node03 mysql> SET GLOBAL read_only=ON; Alternatively, you can configure writer_is_also_reader=2: in this way you won't need to set read_only=ON because the host(s) in backup_writer_hostgroup will be used as reader(s). When you are done with the configuration, as you can see below, there is a useful table in ProxySQL which helps to get a quick view of the state of each node of the cluster, as seen by ProxySQL: ProxySQL Admin> select * from mysql_server_galera_log order by time_start_us desc limit 3; +--------------+------+------------------+-----------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+-------+ | hostname | port | time_start_us | success_time_us | primary_partition | read_only | wsrep_local_recv_queue | wsrep_local_state | wsrep_desync | wsrep_reject_queries | wsrep_sst_donor_rejects_queries | error | +--------------+------+------------------+-----------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+-------+ | 172.16.1.114 | 3306 | 1529510693289001 | 1234 | YES | NO | 0 | 4 | NO | NO | NO | NULL | | 172.16.1.113 | 3306 | 1529510693287804 | 1209 | YES | NO | 0 | 4 | NO | NO | NO | NULL | | 172.16.1.112 | 3306 | 1529510693286879 | 1158 | YES | NO | 0 | 4 | NO | NO | NO | NULL | +--------------+------+------------------+-----------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+-------+ Testing time! Let’s enable a Donor/Desync state on the Galera node we've configured as the priority writer node and check how ProxySQL handles write traffic. When the writer node changes to a Donor/Desync status we expect to see ProxySQL move all write traffic to the backup writer node after promoting it from HG4 (backup_writer_hostgroup) to HG2 (writer_hostgroup). 1.) Check the initial ProxySQL configuration: ProxySQL Admin> select hostgroup,srv_host,status,ConnUsed,MaxConnUsed,Queries,Latency_us from stats.stats_mysql_connection_pool order by srv_host; +-----------+--------------+--------+----------+-------------+------------+------------+ | hostgroup | srv_host | status | ConnUsed | MaxConnUsed | Queries | Latency_us | +-----------+--------------+--------+----------+-------------+------------+------------+ | 2 | 172.16.1.112 | ONLINE | 4 | 4 | 2295114892 | 131 | | 4 | 172.16.1.113 | ONLINE | 0 | 0 | 0 | 162 | | 3 | 172.16.1.114 | ONLINE | 1 | 1 | 539211603 | 142 | +-----------+--------------+--------+----------+-------------+------------+------------+ 2.) Desync db-node01 by setting wsrep_desync=ON db-node01 mysql> SET GLOBAL wsrep_desync=ON; 3.) Re-check ProxySQL's configuration: ProxySQL Admin> select hostgroup,srv_host,status,ConnUsed,MaxConnUsed,Queries,Latency_us from stats.stats_mysql_connection_pool order by srv_host; +-----------+--------------+--------+----------+-------------+-----------+------------+ | hostgroup | srv_host | status | ConnUsed | MaxConnUsed | Queries | Latency_us | +-----------+--------------+--------+----------+-------------+-----------+------------+ | 1 | 172.16.1.112 | ONLINE | 0 | 0 | 0 | 149 | | 2 | 172.16.1.113 | ONLINE | 3 | 4 | 1156479 | 129 | | 3 | 172.16.1.114 | ONLINE | 0 | 1 | 542028027 | 128 | +-----------+--------------+--------+----------+-------------+-----------+------------+ Great stuff! We see that db-node01 was moved to the offline_hostgroup as expected and db-node02 has been allocated to the writer_hostgroup in order to continue serving write traffic. The same behaviour will occur when the primary writer node goes down and leaves the cluster. As a final note, its worthwhile to mention that apart from monitoring wsrep_dsync variable, ProxySQL is also continuously checking the status of wsrep_reject_queries and wsrep_sst_donor_rejects_queries variables and take a required action when needed. Happy ProxySQLing ! Authored by: Ashwini Ahire & Nick Vyzas

    • “How to write your first patch ? ” – MariaDB Unconference Presentations
       Have you ever wondered how to get started with contributions to the world’s most popular open source database? Did you have a problems with building and configuring from source code, writing the contribution patch and testing the server with  use of mysql-test-run (mtr) framework  afterwards? How to make your patch visible to other developers? In […] The post “How to write your first patch ? ” – MariaDB Unconference Presentations appeared first on MariaDB.org.