MySQL Bugs: #36505: rw-splitting.lua doesn't always execute LAST_INSERT

Bug #36505	rw-splitting.lua doesn't always execute LAST_INSERT_ID() on the master server
Submitted:	5 May 2008 12:34	Modified:	16 Nov 2009 11:01
Reporter:	Maarten Sander (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Proxy: Scripts	Severity:	S2 (Serious)
Version:	0.6.1	OS:	Linux (Debian Lenny - Linux 2.6.16-1-em64t-p4-smp)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	Contribution, lua, mysql-proxy, rw-splitting, rw-splitting.lua

Description:
After an INSERT statement, LAST_INSERT_ID() isn't executed on a master server, but on a slave server.

How to repeat:
1) Create enough connections to the proxy so connections to slaves are used for reads.
2) INSERT row into a table with an AUTOINCREMENT column.
3) Use LAST_INSERT_ID() to retrieve the ID of the inserted row.

Suggested fix:
LAST_INSERT_ID() isn't detected properly, since it's a TK_FUNCTION, not a TK_LITERAL.

Suggested fix.

Attachment: rw-splitting.diff (text/x-diff), 576 bytes.

Thank you for the report.

Verified as described. To repeat:

1. Start proxy as  ./src/mysql-proxy --proxy-backend-addresses=127.0.0.1:3351 --proxy-read-only-backend-addresses=127.0.0.1:3306 --proxy-lua-script=./lib/rw-splitting.lua 

2. Run following script in 3 terminals.

$cat bug36505.sh
#!/bin/bash

while (true); do php -r 'mysql_connect("127.0.0.1:4040", "root",""); mysql_select_db("test"); for($i=1; $i<=65536; $i++) {mysql_query("insert into t1 values();"); $res = mysql_query("select last_insert_id()");var_dump(mysql_fetch_row($res));var_dump(mysql_error());}'; done

3. Examine Proxy debug output:

[read_query] 127.0.0.1:64714
  current backend   = 0
  client default db = test
  client username   = root
  query             = insert into t1 values();
  sending to backend : 127.0.0.1:3351
    is_slave         : false
    server default db: test
    server username  : root
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true
(read_query_result) staying on the same backend
    in_trans        : false
    in_calc_found   : false
    have_insert_id  : true
[read_query] 127.0.0.1:459
  current backend   = 0
  client default db = test
  client username   = root
  query             = insert into t1 values();
  sending to backend : 127.0.0.1:3351
    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:65482
  current backend   = 1
  client default db = test
  client username   = root
  query             = select last_insert_id()
  sending to backend : 127.0.0.1:3306
    is_slave         : true
    server default db: test
    server username  : root
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true
(read_query_result) staying on the same backend
    in_trans        : false
    in_calc_found   : false
    have_insert_id  : true
[read_query] 127.0.0.1:64714
  current backend   = 1
  client default db = test
  client username   = root
  query             = select last_insert_id()
  sending to backend : 127.0.0.1:3306
    is_slave         : true
    server default db: test
    server username  : root
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true

Where 127.0.0.1:3351 is master and 127.0.0.1:3306 is slave

Alternative fix for this issue and an issue with wrong SQL_CALC_FOUND_ROWS/found_rows can be found in this bug report: http://bugs.mysql.com/?id=39629

Hello Maarten - 

In order for us to continue the process of reviewing your contribution to MySQL - We need you to review and sign the Sun|MySQL contributor agreement (the "SCA")

The process is explained here: 
http://forge.mysql.com/wiki/Sun_Contributor_Agreement

Getting a signed/approved SCA on file will help us facilitate your contribution-- this one, and others in the future.

Thank you ! 

Liz Drachnik  - Program Manager - MySQL

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

SCA has been received and approved.