Memcached access to InnoDB is not replicated by Galera

[Przemek]

Even if daemon_memcached_enable_binlog is enabled, inserts to InnoDB via memcached plugin are not replicated in cluster.

percona11 mysql> select @@version,@@version_comment;
+--------------------+---------------------------------------------------------------------------------------------------+
| @@version | @@version_comment |
+--------------------+---------------------------------------------------------------------------------------------------+
| 5.6.20-68.0-56-log | Percona XtraDB Cluster (GPL), Release rel68.0, Revision 888, WSREP version 25.7, wsrep_25.7.r4126 |
+--------------------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

percona11 mysql> \! netstat -lpn|grep 11211
tcp 0 0 0.0.0.0:11211 0.0.0.0:* LISTEN 3911/mysqld
tcp 0 0 :::11211 :::* LISTEN 3911/mysqld
udp 0 0 0.0.0.0:11211 0.0.0.0:* 3911/mysqld
udp 0 0 :::11211 :::* 3911/mysqld

percona11 mysql> show variables like '%memc%';
+----------------------------------+------------------+
| Variable_name | Value |
+----------------------------------+------------------+
| daemon_memcached_enable_binlog | ON |
| daemon_memcached_engine_lib_name | innodb_engine.so |
| daemon_memcached_engine_lib_path | |
| daemon_memcached_option | |
| daemon_memcached_r_batch_size | 1 |
| daemon_memcached_w_batch_size | 1 |
+----------------------------------+------------------+
6 rows in set (0.00 sec)

percona11 mysql> select @@log_slave_updates;
+---------------------+
| @@log_slave_updates |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)

[root@percona11 ~]# telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
set a12 10 0 9
123456789
STORED
set a11 10 0 9
123456789
STORED
get a11
VALUE a11 10 9
123456789
END
quit
Connection closed by foreign host.

percona11 mysql> select * from test.demo_test;
+-----+-----------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+-----+-----------+------+------+------+
| a11 | 123456789 | 10 | 5 | 0 |
| a12 | 123456789 | 10 | 4 | 0 |
+-----+-----------+------+------+------+
2 rows in set (0.00 sec)

percona22 mysql> select * from test.demo_test;
Empty set (0.00 sec)

percona11 mysql> show binlog events in 'percona11-bin.000006';
+----------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------+
| percona11-bin.000006 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.20-68.0-56-log, Binlog ver: 4 |
| percona11-bin.000006 | 120 | Previous_gtids | 1 | 231 | 02ada5a1-426c-11e4-a2c9-080027079e3d:1-5,
3c4dfc5e-cbca-ee1b-55bb-69faa881cdcf:1-2 |
| percona11-bin.000006 | 231 | Gtid | 1 | 279 | SET @@SESSION.GTID_NEXT= '02ada5a1-426c-11e4-a2c9-080027079e3d:6' |
| percona11-bin.000006 | 279 | Query | 1 | 352 | BEGIN |
| percona11-bin.000006 | 352 | Table_map | 1 | 412 | table_id: 70 (test.demo_test) |
| percona11-bin.000006 | 412 | Write_rows | 1 | 479 | table_id: 70 flags: STMT_END_F |
| percona11-bin.000006 | 479 | Query | 1 | 553 | COMMIT |
| percona11-bin.000006 | 553 | Gtid | 1 | 601 | SET @@SESSION.GTID_NEXT= '02ada5a1-426c-11e4-a2c9-080027079e3d:7' |
| percona11-bin.000006 | 601 | Query | 1 | 674 | BEGIN |
| percona11-bin.000006 | 674 | Table_map | 1 | 734 | table_id: 70 (test.demo_test) |
| percona11-bin.000006 | 734 | Update_rows | 1 | 834 | table_id: 70 flags: STMT_END_F |
| percona11-bin.000006 | 834 | Query | 1 | 908 | COMMIT |
+----------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------+
12 rows in set (0.00 sec)

BEGIN
/*!*/;
# at 352
#700101 0:00:00 server id 1 end_log_pos 412 CRC32 0x1f2310e4 Table_map: `test`.`demo_test` mapped to number 70
# at 412
#700101 0:00:00 server id 1 end_log_pos 479 CRC32 0xd4f9081e Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `test`.`demo_test`
### SET
### @1='a12'
### @2='123456789'
### @3=10
### @4=4
### @5=0
# at 479
#700101 0:00:00 server id 1 end_log_pos 553 CRC32 0x42eeace6 Query thread_id=8 exec_time=1412253454 error_code=0
SET TIMESTAMP=0/*!*/;
COMMIT
/*!*/;
# at 553
#700101 0:00:00 server id 1 end_log_pos 601 CRC32 0x75b717ee GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '02ada5a1-426c-11e4-a2c9-080027079e3d:7'/*!*/;
# at 601
#700101 0:00:00 server id 1 end_log_pos 674 CRC32 0x98313ae1 Query thread_id=8 exec_time=1412253477 error_code=0
SET TIMESTAMP=0/*!*/;
BEGIN
/*!*/;
# at 674
#700101 0:00:00 server id 1 end_log_pos 734 CRC32 0x6cc9947a Table_map: `test`.`demo_test` mapped to number 70
# at 734
#700101 0:00:00 server id 1 end_log_pos 834 CRC32 0xb41784df Update_rows: table id 70 flags: STMT_END_F
### UPDATE `test`.`demo_test`
### WHERE
### @1='a11'
### @2='5535\x0d\x0a535'
### @3=10
### @4=3
### @5=0
### SET
### @1='a11'
### @2='123456789'
### @3=10
### @4=5
### @5=0
# at 834
#700101 0:00:00 server id 1 end_log_pos 908 CRC32 0x615a7623 Query thread_id=8 exec_time=1412253477 error_code=0
SET TIMESTAMP=0/*!*/;
COMMIT

[Nilnandan Joshi (nilnandan-joshi)]

Able to reproduce with PXC 5.6.20

mysql> select @@version,@@version_comment;
+--------------------+--------------------------------------------------------------+
| @@version | @@version_comment |
+--------------------+--------------------------------------------------------------+
| 5.6.20-68.0-56-log | Percona XtraDB Cluster (GPL), Release 25.7, wsrep_25.7.r4126 |
+--------------------+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> \! netstat -lpn|grep 11211
tcp 0 0 0.0.0.0:11211 0.0.0.0:* LISTEN 13354/mysqld
tcp6 0 0 :::11211 :::* LISTEN 13354/mysqld
udp 0 0 0.0.0.0:11211 0.0.0.0:* 13354/mysqld
udp6 0 0 :::11211 :::* 13354/mysqld
mysql>

mysql> show variables like '%memc%';
+----------------------------------+------------------+
| Variable_name | Value |
+----------------------------------+------------------+
| daemon_memcached_enable_binlog | ON |
| daemon_memcached_engine_lib_name | innodb_engine.so |
| daemon_memcached_engine_lib_path | |
| daemon_memcached_option | |
| daemon_memcached_r_batch_size | 1 |
| daemon_memcached_w_batch_size | 1 |
+----------------------------------+------------------+
6 rows in set (0.00 sec)

mysql> select @@log_slave_updates;
+---------------------+
| @@log_slave_updates |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)

mysql> quit
Bye
root@deb-pxc56-1:/var/lib/mysql# telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
set a12 10 0 9
123456789
STORED
set a11 10 0 9
123456789
STORED
get a11
VALUE a11 10 9
123456789
END
quit
Connection closed by foreign host.
root@deb-pxc56-1:/var/lib/mysql#
root@deb-pxc56-1:/var/lib/mysql# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.20-68.0-56-log Percona XtraDB Cluster (GPL), Release 25.7, wsrep_25.7.r4126

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from test.demo_test;
+-----+--------------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+-----+--------------+------+------+------+
| a11 | 123456789 | 10 | 2 | 0 |
| a12 | 123456789 | 10 | 1 | 0 |
| AA | HELLO, HELLO | 8 | 0 | 0 |
+-----+--------------+------+------+------+
3 rows in set (0.00 sec)

On node2,

mysql> select * from test.demo_test;
+----+--------------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+-...

[Krunal Bauskar (krunal-bauskar)]

Not sure if memcached insert replication is supported. Need to test it with upstream ?

[Ramesh Sivaraman (rameshvs02)]

With upstream also able to reproduce

Node 1
*****

mysql> select * from test.demo_test;
+-----+--------------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+-----+--------------+------+------+------+
| a11 | 123456789 | 10 | 2 | 0 |
| a12 | 123456789 | 10 | 1 | 0 |
| AA | HELLO, HELLO | 8 | 0 | 0 |
+-----+--------------+------+------+------+
3 rows in set (0.00 sec)

mysql> select * from test.demo_test;select @@wsrep_node_name;
+-----+--------------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+-----+--------------+------+------+------+
| a11 | 123456789 | 10 | 2 | 0 |
| a12 | 123456789 | 10 | 1 | 0 |
| AA | HELLO, HELLO | 8 | 0 | 0 |
+-----+--------------+------+------+------+
3 rows in set (0.00 sec)

+-------------------+
| @@wsrep_node_name |
+-------------------+
| node1 |
+-------------------+
1 row in set (0.00 sec)

mysql> select @@version,@@version_comment;
+------------+-------------------------------------------+
| @@version | @@version_comment |
+------------+-------------------------------------------+
| 5.6.23-log | MySQL Community Server (GPL), wsrep_25.10 |
+------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

Node 2
******

mysql> select * from test.demo_test;select @@wsrep_node_name;
+----+--------------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+----+--------------+------+------+------+
| AA | HELLO, HELLO | 8 | 0 | 0 |
+----+--------------+------+------+------+
1 row in set (0.00 sec)

+-------------------+
| @@wsrep_node_name |
+-------------------+
| node2 |
+-------------------+
1 row in set (0.01 sec)

mysql> select @@version,@@version_comment;
+------------+-------------------------------------------+
| @@version | @@version_comment |
+------------+-------------------------------------------+
| 5.6.23-log | MySQL Community Server (GPL), wsrep_25.10 |
+------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1017