Bug #35942 MySQL-Proxy with rw-splitting.lua writes to slave because of SQL_CALC_FOUND_ROWS
Submitted: 9 Apr 2008 15:59 Modified: 8 Dec 2008 14:39
Reporter: Ralf Narozny Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Proxy Severity:S2 (Serious)
Version:0.6.1 , rev 511 OS:Any (SunOS 5.11 NexentaOS_20080131 i86pc i386 i86pc Solaris - Linux)
Assigned to: Jan Kneschke CPU Architecture:Any
Tags: mysql-proxy, rw-splitting.lua

[9 Apr 2008 15:59] Ralf Narozny 
Description:
Hi,

the MySQL-Proxy can be easily fooled to write to the slave.

Here is the output of the mysql-proxy with activated debug:

[connect_server] 127.0.0.1:21675
  [1].connected_clients = 0
  [1].pool.cur_idle     = 2
  [1].pool.max_idle     = 8
  [1].pool.min_idle     = 1
  [1].type = 1
  [1].state = 1
  [2].connected_clients = 0
  [2].pool.cur_idle     = 0
  [2].pool.max_idle     = 8
  [2].pool.min_idle     = 1
  [2].type = 2
  [2].state = 0
  [2] idle-conns below min-idle
[read_query] 127.0.0.1:21675
  current backend   = 0
  client default db = test
  client username   = root
  query             = show databases
  sending to backend : :3306
    is_slave         : false
    server default db: test
    server username  : root
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true
[read_query] 127.0.0.1:21675
  current backend   = 0
  client default db = test
  client username   = root
  query             = show tables
  sending to backend : :3306
    is_slave         : false
    server default db: test
    server username  : root
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true
[read_query] 127.0.0.1:21675
  current backend   = 0
  client default db = test
  client username   = root
  sending to backend : :3306
    is_slave         : false
    server default db: test
    server username  : root
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : false
[read_query] 127.0.0.1:21675
  current backend   = 0
  client default db = test
  client username   = root
  query             = select @@version_comment limit 1
  sending to backend : 127.0.0.1:3307
    is_slave         : true
    server default db: test
    server username  : root
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true
[read_query] 127.0.0.1:21675
  current backend   = 0
  client default db = test
  client username   = root
  query             = select USER()
  sending to backend : 127.0.0.1:3307
    is_slave         : true
    server default db: test
    server username  : root
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true
[read_query] 127.0.0.1:21675
  current backend   = 0
  client default db = test
  client username   = root
  query             = CREATE TABLE test (test int)
  sending to backend : :3306
    is_slave         : false
    server default db: test
    server username  : root
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true
[read_query] 127.0.0.1:21675
  current backend   = 0
  client default db = test
  client username   = root
  query             = SELECT * FROM test
  sending to backend : 127.0.0.1:3307
    is_slave         : true
    server default db: test
    server username  : root
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true
[read_query] 127.0.0.1:21675
  current backend   = 0
  client default db = test
  client username   = root
  query             = SELECT SQL_CALC_FOUND_ROWS * FROM test
  sending to backend : 127.0.0.1:3307
    is_slave         : true
    server default db: test
    server username  : root
    in_trans        : false
    in_calc_found   : true
    COM_QUERY       : true
(read_query_result) staying on the same backend
    in_trans        : false
    in_calc_found   : true
    have_insert_id  : nil
[read_query] 127.0.0.1:21675
  current backend   = 2
  client default db = test
  client username   = root
  query             = INSERT INTO test VALUES (1)
  sending to backend : 127.0.0.1:3307
    is_slave         : true
    server default db: test
    server username  : root
    in_trans        : false
    in_calc_found   : true
    COM_QUERY       : true
(read_query_result) staying on the same backend
    in_trans        : false
    in_calc_found   : true
    have_insert_id  : false

How to repeat:
Start a proxy with one master and one slave (I did with)

mysql-proxy \
  --pid-file=/var/run/mysqld/mysql-proxy.pid \
  --proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua \
  --proxy-backend-addresses=:3306 \
  --proxy-read-only-backend-addresses=127.0.0.1:3307

Create enough connections to the proxy to make the proxy actually connect you to the slave for reads.

Try:

CREATE TABLE test (test int); -- master chosen as expected
SELECT * FROM test;  -- slave chosen
SELECT SQL_CALC_FOUND_ROWS * FROM test; -- slave chosen again
INSERT INTO test VALUES (1); -- done on the slave

Suggested fix:
Make the writing statements overrule 'is_in_select_calc_found_rows' in server selection.
[11 Aug 2008 3:50] Diego Medina 
Verified as described using svn rev 511
[8 Dec 2008 14:39] Ralf Narozny 
Hi,

no news on this bug for ages. Shall we forget about using the proxy for rw-splitting?

Regards,
 Ralf
[8 Dec 2008 15:29] Kay Roepke 
Quite frankly, r/w splitting should be considered as an experimental feature.
There are lots of issues to be solved with regards to implicit and explicit state of the connection and it seems that many of them cannot be solved in a generically useful way.

I would not expect a "one-size-fits-all" rw-splitting.lua script any time soon if at all.

Sorry to be so blunt, but it _is_ hard (and too hard to just needing some days of work).

I am actually considering removing the script entirely from future distributions because it causes so much confusion.