[MXS-220] LAST_INSERT_ID() query is redirect to slave if function call is in where clause - Jira

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

Ascending order - Click to sort in descending order

Michaël de groot created issue - 2015-06-24 18:48

markus makela made changes - 2015-06-24 19:18

Field	Original Value	New Value
Component/s		ReadWriteSplit [ 11613 ]

markus makela made changes - 2015-06-24 19:18

Affects Version/s

1.1.1 [ 19404 ]

Michaël de groot made changes - 2015-06-24 21:31

Description

Hi,

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

Test case:
{code}
MariaDB [mariadb_test]> create table a (id serial, primary key(id));
Query OK, 0 rows affected (0.02 sec)

MariaDB [mariadb_test]> show create table a;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| a | CREATE TABLE `a` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 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)
{code}

201 = master, 203 = slave.

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

Hi,

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

Test case:
{code}
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)
{code}

201 = master, 203 = slave.

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

Michaël de groot made changes - 2015-06-25 12:17

Description

Hi,

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

Test case:
{code}
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)
{code}

201 = master, 203 = slave.

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

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:
{code}
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)
{code}

201 = master, 203 = slave.

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

Dipti Joshi (Inactive) made changes - 2015-07-02 09:56

Fix Version/s

1.2.1 [ 19400 ]

markus makela made changes - 2015-07-19 10:16

Affects Version/s

1.2.0 [ 18901 ]

markus makela made changes - 2015-07-20 16:13

Assignee

markus makela [ <markus.makela ]

markus makela made changes - 2015-07-20 16:13

Status

Open [ 1 ]

In Progress [ 3 ]

markus makela made changes - 2015-07-20 16:15

Status

In Progress [ 3 ]

In Review [ 10002 ]

markus makela made changes - 2015-07-20 16:27

Status

In Review [ 10002 ]

Stalled [ 10000 ]

markus makela made changes - 2015-07-20 16:30

Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Rasmus Johansson (Inactive) made changes - 2015-08-20 10:28

Fix Version/s		1.3.0 [ 19303 ]
Fix Version/s	1.2.1 [ 19400 ]

Sergei Golubchik made changes - 2021-12-06 21:29

Workflow

MariaDB v3 [ 70316 ]

MariaDB v4 [ 136872 ]

People

Assignee:: markus makela

Reporter:: Michaël de groot

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2015-06-24 18:48

Updated:: 2015-08-20 10:28

Resolved:: 2015-07-20 16:30

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB MaxScale

Details

Description

Attachments

Activity

People

Dates

Git Integration