Node configuration (server-id and host names of course being different per node):
[mysqld]
|
server-id=1
|
bind-address=0.0.0.0
|
|
log-bin
|
log-slave-updates
|
binlog-format=ROW
|
|
innodb_buffer_pool_size = 1G
|
|
wsrep_on=ON
|
wsrep_provider=/usr/lib/galera/libgalera_smm.so
|
|
wsrep_cluster_name=master_cluster
|
wsrep_cluster_address=gcomm://master-node-1,master-node-2,
|
wsrep_node_address=master-node-1
|
wsrep_node_name=master-node-1
|
|
wsrep_sst_method=mariabackup
|
wsrep_sst_auth=galera:...password...
|
|
query_cache_type=1
|
query_cache_size=10M
|
Test table created on master-node-1 as:
CREATE TABLE t1 (id serial primary key, msg varchar(100));
|
INSERT INTO t1 values(NULL, md5(rand());
|
INSERT INTO t1 SELECT NULL, md5(rand()) from t1 LIMIT 1000000;
|
... repeat previous line until the table has a few million rows in it
|
INSERT INTO t1 values(NULL, 'foobar');
|
Run
SELECT * FROM t1 WHERE msg='foobar';
|
on all nodes to see that it takes a non-zero amount of time.
Run the same query once again to verify that the result now immediately comes back from the query cache.
Now add another row with same msg value once more:
INSERT INTO t1 values(NULL, 'foobar');
|
And now re-run the SELECT on all nodes, see that it takes its time and returns two rows now, as expected, on master-node-1, master-node-2 and slave-node-2, but is still fast and returns a single row only on slave-node-1.
Rewrite it slightly and run it again on slave-node-1 to verify that the change was correctly applied, just the query cache entries for table t1 not purged:
SELECT id, msg FROM t1 WHERE msg='foobar';
|
With "id, msg" instead of "*" it now takes its time on slave-node-1, too, and correctly returns two result rows now.
Bug
Critical
Node configuration (server-id and host names of course being different per node):
[mysqld]server-id=1bind-address=0.0.0.0log-binlog-slave-updatesbinlog-format=ROWinnodb_buffer_pool_size = 1Gwsrep_on=ONwsrep_provider=/usr/lib/galera/libgalera_smm.sowsrep_cluster_name=master_clusterwsrep_cluster_address=gcomm://master-node-1,master-node-2,wsrep_node_address=master-node-1wsrep_node_name=master-node-1wsrep_sst_method=mariabackupwsrep_sst_auth=galera:...password...query_cache_type=1query_cache_size=10MTest table created on master-node-1 as:
CREATE TABLE t1 (id serial primary key, msg varchar(100));INSERT INTO t1 values(NULL, md5(rand());INSERT INTO t1 SELECT NULL, md5(rand()) from t1 LIMIT 1000000;... repeat previous line until the table has a few million rows in itINSERT INTO t1 values(NULL, 'foobar');Run
SELECT * FROM t1 WHERE msg='foobar';on all nodes to see that it takes a non-zero amount of time.
Run the same query once again to verify that the result now immediately comes back from the query cache.
Now add another row with same msg value once more:
INSERT INTO t1 values(NULL, 'foobar');And now re-run the SELECT on all nodes, see that it takes its time and returns two rows now, as expected, on master-node-1, master-node-2 and slave-node-2, but is still fast and returns a single row only on slave-node-1.
Rewrite it slightly and run it again on slave-node-1 to verify that the change was correctly applied, just the query cache entries for table t1 not purged:
SELECT id, msg FROM t1 WHERE msg='foobar';With "id, msg" instead of "*" it now takes its time on slave-node-1, too, and correctly returns two result rows now.