Proxysql: Problem with "LAST_INSERT_ID()" returning 0

[lengerad]

Hello,

I'm fan of your work, I've been testing Percona XtraDB cluster with ProxySQL. Unfortunately I have an issue which seems to be related to the ProxySQL and connections. In few cases we need to get id from last inserted row with auto incremental ID. Normally we were used to call INSERT .. and then SELECT LAST_INSERT_ID()..., but once I've set up ProxySQL, this method is just returning 0. I'm presuming that it's because the next SELECT after the INSERT is just listed as different connection or something like that? I've try to google it here on GIThub and also on Stack and stuff but I didn't found anything. So in case of duplicity, I'm sorry.

Our setup:
Percona XtraDB cluster -> two nodes + 1 arbitrator
ProxySQL version 1.3.6-1, codename Truls

So far everything else seems to work properly, I've also tried to do some query routing for R/W splits, so one node server is writing node and the second is for reading -> I thought that might be the problem, so I shut down the second node and the arbitrator but the problem is still there.

Have you had any similar issues or can you lead me what might be wrong?
Thanks!

[renecannao]

Hi @lengerad , thank you for the feedback.

a simple SELECT LAST_INSERT_ID() should be processed successfully. Here is an example:

mysql> INSERT INTO test (id) VALUES (NULL);
Query OK, 1 row affected (0.20 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2                |
+------------------+
1 row in set (0.00 sec)

In Admin:

mysql> SELECT digest_text,count_star,sum_time FROM stats_mysql_query_digest;
+-------------------------------------+------------+----------+
| digest_text                         | count_star | sum_time |
+-------------------------------------+------------+----------+
| INSERT INTO test (id) VALUES (NULL) | 1          | 191409   |
| SELECT LAST_INSERT_ID()             | 1          | 0        |
| select @@version_comment limit ?    | 1          | 0        |
| show databases                      | 1          | 24148    |
| show tables                         | 1          | 3070     |
+-------------------------------------+------------+----------+
5 rows in set (0.00 sec)

sum_time=0 in reality means that the query was not send to any backend.
In fact, ProxySQL is able to reply to SELECT LAST_INSERT_ID() without sending the request to any backend.
Maybe your query is not a simple SELECT LAST_INSERT_ID() but is a more complex query?

Thanks

[lengerad]

Hello @renecannao, thank you for a prompt reply!

Maybe a little dumb question, but I don't see my current selects/inserts in this table. Does it mean that I have not properly set up the monitoring user or smth similar? I can see some older entries, which would mean that it worked before.

[lengerad]

Hello @renecannao, I've made some changes and also stats are working properly right now.
As you were saying, when I do simple request and then call the LAST_INSERT_ID everything works fine (even when I do remote mysql query from different server to the ProxySQL). But once when I'm doing it from the Java application, I'm still getting 0 value.

I've attached screenshot, what I'm suspicious about is that you send that the sum_time should be=0, which in the second case really is, but in case of Java app, there is some amount time.
Do you think it might be related to some Hibernate stuff? Also one difference is that at Java app, write goes correctly to HG = 0 and read HG=1, but within the simple request everything is pointing to HG=0

[renecannao]

Do you think it might be related to some Hibernate stuff?

yes :sweat:

SELECT LAST_INSERT_ID() LIMIT ?

That is an easy fix tho.

[renecannao]

Fixed in 1.3.8 an 1.4.1

[roadrunner]

Hello @renecannao ,
After experiencing the same problem, i've updated proxysql 1.4.1 from 1.3.7.
But the interesting thing is it's not seems to be solved.

mysql> select * from global_variables where variable_name = 'admin-version';
+---------------+-------------------+
| variable_name | variable_value    |
+---------------+-------------------+
| admin-version | 1.4.1-45-gab4e6ee |
+---------------+-------------------+
1 row in set (0.01 sec)

mysql> select first_seen, hostgroup, substr(digest_text, 0, 40), count_star, sum_time from stats_mysql_query_digest order by first_seen desc limit 3;
+------------+-----------+-----------------------------------------+------------+----------+
| first_seen | hostgroup | substr(digest_text, 0, 40)              | count_star | sum_time |
+------------+-----------+-----------------------------------------+------------+----------+
| 1501696801 | 11        | SELECT LAST_INSERT_ID();                | 1          | 1560     |
| 1501696718 | 10        | INSERT INTO payment_order SET parent_id | 2          | 16781    |
| 1501696718 | 11        | SELECT LAST_INSERT_ID() LIMIT ?;        | 1          | 1631     |
+------------+-----------+-----------------------------------------+------------+----------+
3 rows in set (0.00 sec)

i'm using percona cluster as well.

any suggestion?

thanks..

[renecannao]

@roadrunner : is this a custom application?
If yes, can you remove the semi-column from the query?

[roadrunner]

hello @renecannao

it's just a basic php application;

and automatically generated by php mysqli library

if you want i can try with making query manually?

best,

[roadrunner]

@renecannao, after removing semicolon (sending query manually) it went to the write group, yay

mysql> select first_seen, hostgroup, substr(digest_text, 0, 40), count_star, sum_time from stats_mysql_query_digest order by first_seen desc limit 3;
+------------+-----------+-----------------------------------------+------------+----------+
| first_seen | hostgroup | substr(digest_text, 0, 40)              | count_star | sum_time |
+------------+-----------+-----------------------------------------+------------+----------+
| 1501704968 | 10        | SELECT LAST_INSERT_ID()                 | 1          | 0        |
| 1501696800 | 11        | SELECT LAST_INSERT_ID();                | 1          | 1560     |
| 1501696717 | 10        | INSERT INTO payment_order SET parent_id | 3          | 25155    |
+------------+-----------+-----------------------------------------+------------+----------+
3 rows in set (0.00 sec)

[renecannao]

@roadrunner : after removing the semicolumn, proxysql replied without sending the query anywhere (that is correct!) . sum_time=0

[renecannao]

re-opening , to handle the semi-column case

[roadrunner]

@renecannao, according to sum time it seems so..

[danschafer]

@renecannao, I'm also seeing the problem with things besides the ";". Could the following also be handled?

SELECT LAST_INSERT_ID() AS ?

[renecannao]

@danschafer : for reference, can you share an example of your application's query?
Thanks

[danschafer]

@renecannao, It's a pretty old application that's been modified by a lot of developers over the years, so I basically found every variation possible.

SELECT LAST_INSERT_ID();
SELECT LAST_INSERT_ID ( ) ;
SELECT LAST_INSERT_ID() insert_id;
SELECT LAST_INSERT_ID() "Insert Id";
SELECT LAST_INSERT_ID() AS insert_id;
SELECT LAST_INSERT_ID() AS "Insert-ID";
SELECT LAST_INSERT_ID() AS '1st Insert ID';
SELECT LAST_INSERT_ID() as `insert id`;

I used the following regex in a caseless rule to force them all to the hostgroup 0. They matched correctly, but it didn't give me the desired results.

^SELECT\s+LAST_INSERT_ID\s*\(\s*\)(?:(?:\s+AS)?\s+['"`]?[\w -]+['"`]?)?\s*;?$

[renecannao]

Developers have been very creative :smile:

The query rule won't help because due to multiplexing a different connection is being used.

I will get this fixed

[danschafer]

@renecannao, Thanks.

[danschafer]

If you are using LAST_INSERT_ID() in an INSERT ... ON DUPLICATE KEY UPDATE to ensure that the correct value is returned for the AUTO_INCREMENT column on update, this works correctly and isn't affected by this problem.

In the following instance, SELECT LAST_INSERT_ID(); can return 0, but it is a query problem and not a ProxySQL problem. It can also return the id of a previously successful insert within the current connection

INSERT INTO table (`column_with_unique_key`, `other_column`)
VALUES ('value_already_in_table', 'dummy_data') 
ON DUPLICATE KEY UPDATE `other_column` = VALUES(`other_column`);

The following returns the correct id for SELECT LAST_INSERT_ID();

INSERT INTO table (`column_with_unique_key`, `other_column`)
VALUES ('value_already_in_table', 'dummy_data') 
ON DUPLICATE KEY UPDATE `other_column` = VALUES(`other_column`), id = LAST_INSERT_ID(id);

https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

Although it was trivial to test, and I assumed that it wasn't going to be a problem, hopefully this will help some other developer in the future.

[danschafer]

@renecannao, I think I've found another failure case for LAST_INSERT_ID().

Using the following tables:

CREATE TABLE `proxysql_test_urls` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `url` varchar(255) NOT NULL DEFAULT '',
  `created_at` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  KEY `url` (`url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `proxysql_test_ids` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `profile_id` int(11) NOT NULL,
  `url_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  KEY `profile_id` (`profile_id`),
  KEY `url_id` (`url_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Run the following queries 10 or so times.

INSERT INTO `proxysql_test_urls` (`url`, `created_at`) 
  VALUES ('http://www.example.com/', NOW());
INSERT INTO `proxysql_test_ids` (`profile_id`, `url_id`, `created_at`) 
  VALUES (12345, LAST_INSERT_ID(), NOW());

Given the previous test, here is the outcome that I got in the proxysql_test_ids table. The url_ids of 9787655 and 61638685 were both insert_ids from earlier INSERT statements not shown, but within my session.

[renecannao]

INSERT INTO ... LAST_INSERT_ID() ... is not meant to work out of the box, because multiplexing is enabled after the previous INSERT .
To make the above works you should create 2 query rules:

• one query rule to disable multiplexing after the first INSERT
• another query rule to re-enable multiplexing after the second INSERT

[danschafer]

Thank you so much. I was in "see what I can break" mode on LAST_INSERT_ID() and though I found something. I'll check it out in the documentation.

[jotacor]

Hello everyone,
We are facing the same problem on two applications in ProxySQL v1.4.9
One is the Tikiwiki CMS which creates the query as "select last_insert_id() limit 1 offset 0" which is sent to the MySQL and it replies with the wrong last ID (sum_time != 0).

| hostgroup | text                                     | count_star | sum_time |
+-----------+------------------------------------------+------------+----------+
| 0         | SELECT LAST_INSERT_ID() LIMIT ?          | 3          | 0        |
| 0         | SELECT LAST_INSERT_ID() LIMIT ? OFFSET ? | 3          | 8855     |
+-----------+------------------------------------------+------------+----------+

The other one is a Moodle that inserts a semicolon at the end of the query, and we can't either modify it :(

| hostgroup | text                                     | count_star | sum_time |
+-----------+------------------------------------------+------------+----------+
| 0         | SELECT LAST_INSERT_ID();                 | 71         | 73252    |
+-----------+------------------------------------------+------------+----------+

[jotacor]

Hi,
We found a solution for this issue, rewriting the query with a query rule.

When a query with last_insert_id arrives, we detect it with the pattern ".last_insert_id." and we remove the " offset 0" (take care of the space, without it it doesn't work), we do the same for the semicolon with other query rewrite.

After that rquery ewrite, the sum_time is always 0 and the response is the proper.