<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Marko Sutic on Medium]]></title>
        <description><![CDATA[Stories by Marko Sutic on Medium]]></description>
        <link>https://medium.com/@marko.sutic?source=rss-a4089c23af47------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/2*q-FfoYau3jtKEmNqroNA8w.jpeg</url>
            <title>Stories by Marko Sutic on Medium</title>
            <link>https://medium.com/@marko.sutic?source=rss-a4089c23af47------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 01:10:59 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@marko.sutic/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[DDL/DML processing on Galera Cluster]]></title>
            <link>https://medium.com/@marko.sutic/ddl-dml-processing-on-galera-cluster-b0edba784686?source=rss-a4089c23af47------2</link>
            <guid isPermaLink="false">https://medium.com/p/b0edba784686</guid>
            <category><![CDATA[galera]]></category>
            <category><![CDATA[percona]]></category>
            <category><![CDATA[mysql]]></category>
            <dc:creator><![CDATA[Marko Sutic]]></dc:creator>
            <pubDate>Thu, 17 Oct 2019 19:14:17 GMT</pubDate>
            <atom:updated>2019-10-17T19:14:17.420Z</atom:updated>
            <content:encoded><![CDATA[<p>When working with Galera Cluster you must read carefully about differences and limitations in comparison to single instance database.<br>One of the major differences is<strong> DDL processing</strong> in Galera Cluster.</p><p>For example, if you run in a <strong>single instance</strong> database:</p><pre>SESSION 1<br>start transaction;<br>insert into test values (1,’test’);</pre><pre>SESSION 2<br>alter table test add column val varchar(50);<br><strong>&lt;&lt; waiting &gt;&gt;</strong></pre><pre>SESSION 3<br>+ — — — — -+ — — — + — — — — — — — — — — — — — — — — -+ — — — — — — — — — — — — — — — — — — — — — — -+ — — — — — -+ — — — — — — — -+<br>| Command | Time | State | Info | Rows_sent | Rows_examined |<br>+ — — — — -+ — — — + — — — — — — — — — — — — — — — — -+ — — — — — — — — — — — — — — — — — — — — — — -+ — — — — — -+ — — — — — — — -+<br>| Sleep | 1258 | | NULL | 0 | 1 |<br>| Sleep | 119 | | NULL | 0 | 0 |<br>| Query | 70 | Waiting for table metadata lock | alter table test add column val varchar(50) | 0 | 0 |<br>| Query | 0 | starting | show processlist | 0 | 0 |<br>+ — — — — -+ — — — + — — — — — — — — — — — — — — — — -+ — — — — — — — — — — — — — — — — — — — — — — -+ — — — — — -+ — — — — — — — -+</pre><p>Notice that ALTER statement from SESSION 2 is blocked, waiting for a transaction to complete.<br>This is expected behaviour on single instance database.</p><p>What if we start database as a <strong>Galera Cluster</strong> node:</p><pre>SESSION 1<br>start transaction;<br>insert into test values (1,’test’);</pre><pre>SESSION 2<br>alter table test add column val varchar(50);<br>Query OK, 0 rows affected (0.06 sec)<br>Records: 0 Duplicates: 0 Warnings: 0</pre><pre>SESSION 1<br>mysql&gt; commit;<br>ERROR 1213 (40001): WSREP detected deadlock/conflict and aborted the transaction. Try restarting the transaction</pre><pre>mysql&gt; select * from test;<br>Empty set (0.00 sec)</pre><p>In this case ALTER statement <strong>completed</strong> while transaction on the table was in <strong>progress</strong>.</p><p>When using Total Order Isolation method, which is default on Galera Cluster, <strong>DDL is replicated upfront with higher priority</strong>. <br>Transaction will get a deadlock error at commit time and will be rolled back.</p><p>This is expected behaviour on Galera Cluster.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b0edba784686" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Inconsistency and wsrep_on variable on MySQL/MariaDB Galera Cluster]]></title>
            <link>https://medium.com/krakensystems-blog/data-inconsistency-and-wsrep-on-variable-on-mysql-mariadb-galera-cluster-ba63dda60701?source=rss-a4089c23af47------2</link>
            <guid isPermaLink="false">https://medium.com/p/ba63dda60701</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[proxysql]]></category>
            <category><![CDATA[galera]]></category>
            <category><![CDATA[mariadb]]></category>
            <dc:creator><![CDATA[Marko Sutic]]></dc:creator>
            <pubDate>Sun, 28 Jul 2019 21:05:17 GMT</pubDate>
            <atom:updated>2019-07-29T13:29:58.128Z</atom:updated>
            <content:encoded><![CDATA[<p>Galera Cluster is no-downtime or near-zero downtime cluster software which allows you to perform rolling upgrades or schema changes without significant effect on the application.</p><p>To perform rolling upgrade or schema migration you must spend some time on preparation and testing because just slight mistake or oversight can cause cluster freeze and significant application downtime. There are various tools and techniques explaining how to perform such tasks and I would recommend to test them out and choose the one which is the best for your task.</p><p>To mitigate effect on the whole cluster while performing sensitive tasks like schema changes some DBA’s set “<strong>wsrep_on=OFF</strong>” variable without thinking about possible consequences.</p><p>One of the biggest advantages of Galera Cluster over standard replication is plug-in for InnoDB which provides exact data copies between nodes using <strong>certification-based replication</strong>. You don’t have to worry about on which node you will write or how to add/remove a node from the cluster. It is very convenient.</p><p>Although you can perform checks to be sure that you have exact data copies between nodes, as you would often do for standard replicas, it is very rare that anybody does that.</p><p>This is why <strong>data inconsistency </strong>in Galera Cluster can go unnoticed for months or years or you might never find out that nodes in your cluster don’t have exact data.</p><h3><strong>Test Environment</strong></h3><p>MariaDB Galera Cluster 10.4.6 — wsrep_provider_version=26.4.2(r4498)<br>Ubuntu 18.04.1</p><pre>CREATE TABLE `t1` (<br>`id` int(11) NOT NULL,<br>`text` varchar(20) DEFAULT NULL,<br>`modified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),<br>PRIMARY KEY (`id`),<br>KEY `modified` (`modified`)<br>) ENGINE=InnoDB DEFAULT CHARSET=utf8</pre><h3><strong>WSREP_ON variable</strong></h3><p>This is Global/Session-based variable for enabling/disabling wsrep replication.</p><p>It can be useful to disable cluster replication when performing an upgrade or schema changes but you have to be very careful when using it.</p><p>Imagine we have to perform some schema changes on the live “Node2” server.</p><p>As “wsrep_on” is a dynamic variable we can alter it without node restart or affecting a running application.</p><pre>MariaDB Node2&gt;set global wsrep_on=OFF;</pre><p>While we have disabled replication on Node2 some workload is happening on the whole cluster.</p><pre>MariaDB Node1&gt;insert into t1(id,text) values (1,@@hostname);<br>Query OK, 1 row affected (0.013 sec)</pre><pre>MariaDB Node1&gt;commit;<br>Query OK, 0 rows affected (0.000 sec)</pre><pre>MariaDB Node3&gt;insert into t1(id,text) values (3,@@hostname);<br>Query OK, 1 row affected (0.012 sec)</pre><pre>MariaDB Node3&gt;commit;<br>Query OK, 0 rows affected (0.000 sec)</pre><pre>MariaDB Node2&gt;insert into t1(id,text) values (2,@@hostname);<br>Query OK, 1 row affected (0.010 sec)</pre><pre>MariaDB Node2&gt;commit;<br>Query OK, 0 rows affected (0.000 sec)</pre><h3><strong>Data Inconsistency</strong></h3><p>What if we query the T1 table:</p><pre>MariaDB Node1&gt;select * from t1;<br>+ — — + — — — — -+ — — — — — — — — — — -+<br>| id | text | modified |<br>+ — — + — — — — -+ — — — — — — — — — — -+<br>| 1 | marija1 | 2019–07–27 23:59:02 |<br>| 3 | marija3 | 2019–07–27 23:59:11 |<br>+ — — + — — — — -+ — — — — — — — — — — -+<br>2 rows in set (0.001 sec)</pre><pre>MariaDB Node3&gt;select * from t1;<br>+ — — + — — — — -+ — — — — — — — — — — -+<br>| id | text | modified |<br>+ — — + — — — — -+ — — — — — — — — — — -+<br>| 1 | marija1 | 2019–07–27 23:59:02 |<br>| 3 | marija3 | 2019–07–27 23:59:11 |<br>+ — — + — — — — -+ — — — — — — — — — — -+<br>2 rows in set (0.008 sec)</pre><pre>MariaDB Node2&gt;select * from t1;<br>+ — — + — — — — -+ — — — — — — — — — — -+<br>| id | text | modified |<br>+ — — + — — — — -+ — — — — — — — — — — -+<br>| 1 | marija1 | 2019–07–27 23:59:02 |<br><strong>| 2 | marija2 | 2019–07–28 00:01:42 |<br></strong>| 3 | marija3 | 2019–07–27 23:59:11 |<br>+ — — + — — — — -+ — — — — — — — — — — -+<br>3 rows in set (0.001 sec)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*71G88oclkT99cMdUSeCv2Q.jpeg" /><figcaption>Data Inconsistency between nodes</figcaption></figure><p>Notice that we have one extra row on “Node2”, an error that is very hard to notice.</p><p>When we change back wsrep_on to ON <strong>no error is received</strong> and all nodes in the cluster are acting as everything is fine and we have a <strong>healthy</strong> cluster.</p><h3><strong>Recovering Inconsistent Database</strong></h3><p>But not everything is lost and cluster could recover automatically from data inconsistency.</p><p>Galera Cluster 4 has strict inconsistency policy — cluster runs inconsistency voting protocol and reacts accordingly to detected inconsistency.</p><p>In this case, if we are very lucky, a transaction affecting the problematic row will happen.</p><pre>Node2&gt;update t1 set text=concat(text,’_updated’) where id&gt;1;<br>ERROR 2013 (HY000): Lost connection to MySQL server during query</pre><p>Problem is found on Node2 and Node2 is immediately <strong>aborted</strong> — dropped out from the cluster.</p><pre>Jul 28 00:32:26 marija2 systemd[1]: mariadb.service: Main process exited, code=dumped, status=6/ABRT</pre><pre>Jul 28 00:32:26 marija2 systemd[1]: mariadb.service: Failed with result ‘core-dump’.</pre><pre>Jul 28 00:32:31 marija2 systemd[1]: mariadb.service: Service hold-off time over, scheduling restart.</pre><pre>Jul 28 00:32:31 marija2 systemd[1]: mariadb.service: Scheduled restart job, restart counter is at 6.</pre><p>To resolve data inconsistency Galera Cluster immediately runs State Snapshot Transfer (SST) and recreates node from a full data copy.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UujXrfQ3GBq5AcmKnM3aoA.jpeg" /><figcaption>Data Inconsistency Recovery</figcaption></figure><h3>Galera Aware Load Balancer</h3><p>If you are using Galera aware load balancer like ProxySQL node with disabled cluster replication will be automatically excluded from rotation. Transactions will be redirected to avoid possible data inconsistencies.</p><h3><strong>Lessons Learned</strong></h3><p>Be very careful when using “wsrep_on=ON” variable on a GLOBAL scale, either setting a global variable or starting node with modified variable.</p><p>If you plan to use it make sure you don’t have client transactions running on the node.</p><p>Use Galera aware SQL proxy for incoming traffic from MySQL clients. Don’t connect to cluster nodes directly.</p><p>You must be 100% confident in what you are doing or avoid touching <strong>wsrep_on</strong> variable because mistakes can be very expensive.</p><p>Remember — the biggest problem for Galera Cluster is <strong>data inconsistency</strong>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ba63dda60701" width="1" height="1" alt=""><hr><p><a href="https://medium.com/krakensystems-blog/data-inconsistency-and-wsrep-on-variable-on-mysql-mariadb-galera-cluster-ba63dda60701">Data Inconsistency and wsrep_on variable on MySQL/MariaDB Galera Cluster</a> was originally published in <a href="https://medium.com/krakensystems-blog">KrakenSystems</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>