Uploaded image for project: 'MaxScale'
  1. MaxScale
  2. MXS-220

LAST_INSERT_ID() query is redirect to slave if function call is in where clause

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.1.1, 1.2.0
    • Fix Version/s: 1.3.0
    • Component/s: readwritesplit
    • Labels:
      None

      Description

      Hi,

      If a query contains LAST_INSERT_ID() it should be redirected to master, even if it is in WHERE clause.

      Also another syntax is not redirected to the master: SELECT @id:=LAST_INSERT_ID(); - please let me know if you need a test case for this as well.

      Test case:

      MariaDB [mariadb_test]> create table a (id serial, primary key(id));
      Query OK, 0 rows affected (0.02 sec)
       
      MariaDB [mariadb_test]> insert into a values(null);
      Query OK, 1 row affected (0.00 sec)
       
      MariaDB [mariadb_test]> select @@hostname from a WHERE id=last_insert_id();
      Empty set (0.00 sec)
       
      michaeldg [5:44 PM]
      MariaDB [mariadb_test]> select @@hostname from a WHERE id=last_insert_id() or id=1;
      +------------------------+
      | @@hostname             |
      +------------------------+
      | 203.local |
      +------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [mariadb_test]> select @@hostname, last_insert_id() FROM a WHERE id=last_insert_id();
      +------------------------+------------------+
      | @@hostname             | last_insert_id() |
      +------------------------+------------------+
      | 201.local |                1 |
      +------------------------+------------------+
      1 row in set (0.00 sec)

      201 = master, 203 = slave.

      Please change it so all queries with LAST_INSERT_ID() function call get redirected to master.

        Attachments

          Activity

            People

            • Assignee:
              markus makela markus makela
              Reporter:
              michaeldg Michaël de groot
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: