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
Triage: D3 (Medium)

[5 May 2008 12:34] Maarten Sander
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.
[5 May 2008 12:34] Maarten Sander
Suggested fix.

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

[26 Sep 2008 7:51] Sveta Smirnova
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
[29 Sep 2008 16:52] Hristo Erinin
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
[8 Jun 2009 23:14] liz drachnik
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
[3 Oct 2009 23:00] Bugs System
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".
[7 Nov 2009 0:00] Bugs System
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".
[24 Mar 2010 12:04] Lenz Grimmer
SCA has been received and approved.