Bug #89139 mysqlclient does not return correct mysql_insert_id after ping()
Submitted: 8 Jan 2018 20:06 Modified: 16 Apr 2018 11:11
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S3 (Non-critical)
Version:5.7.20, 8.0.3 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[8 Jan 2018 20:06] Daniël van Eeden
Description:
This behaves differently with 5.6 and 5.7 client libraries:

mysql_query(mysql, "INSERT INTO t1() VALUES();");
mysql_ping(mysql);
my_ulonglong insertid = mysql_insert_id(mysql);

With 5.6: this returns the correct id.
With 5.7: this returns 0

Note that 5.7.17 and older 5.7 releases are known not to work because of:
Bug #78778 	mysqlclient does not return correct mysql_insert_id via database handle

How to repeat:
======================================================
#include <mysql.h>
#include <stdio.h>
#include <string.h>
#include <assert.h>

/* Table created with: create table t1 (id serial primary key) */

int main()
{
  MYSQL *mysql, *conn;
  mysql = mysql_init(NULL);

  conn = mysql_real_connect(mysql, "127.0.0.1", "msandbox", "msandbox",
                            "test", 5718, NULL, 0);

  if (conn == NULL)
  {
    printf("Uhoh: %s\n", mysql_error(mysql));
  }

  printf("Client compile version: %d\n", MYSQL_VERSION_ID);
  printf("Client runtime version: %s\n", mysql_get_client_info());
  printf("Server version: %lu\n", mysql_get_server_version(mysql));

  for (int i=1; i < 3; i++)
  {
    printf("\nRound %d\n", i);
    mysql_query(mysql, "INSERT INTO t1() VALUES();");

    printf("mysql->insert_id: %llu\n", mysql->insert_id);
    if (i == 2)
    {
      printf("ping\n");
      mysql_ping(mysql);
    }
    printf("mysql->insert_id: %llu\n", mysql->insert_id);

    my_ulonglong insertid = mysql_insert_id(mysql);
    printf("Got back insert id: %llu\n", insertid);

    assert(insertid > 0);
  }

  mysql_close(mysql);
}
======================================================

To build:
======================================================
gcc -Wall -Werror --std=c11 -g $(mysql_config --cflags --libs) mysql_bug_insertid.c -o mysql_bug_insertid_57

gcc -Wall -Werror --std=c11 -g $(/opt/mysql/5.6.38/bin/mysql_config --cflags --libs) mysql_bug_insertid.c -o mysql_bug_insertid_56
======================================================

Output:
======================================================
$ ./mysql_bug_insertid_56
Client compile version: 50638
Client runtime version: 5.6.37
Server version: 50718

Round 1
mysql->insert_id: 45
mysql->insert_id: 45
Got back insert id: 45

Round 2
mysql->insert_id: 46
ping
mysql->insert_id: 46
Got back insert id: 46
$ ./mysql_bug_insertid_57
Client compile version: 50720
Client runtime version: 5.7.20
Server version: 50718

Round 1
mysql->insert_id: 47
mysql->insert_id: 47
Got back insert id: 47

Round 2
mysql->insert_id: 48
ping
mysql->insert_id: 0
Got back insert id: 0
mysql_bug_insertid_57: mysql_bug_insertid.c:41: main: Assertion `insertid > 0' failed.
Aborted (core dumped)
======================================================

Suggested fix:
Return the correct id, even if ping was used in between inserting and getting the id.
[8 Jan 2018 20:25] Daniël van Eeden
$ ./mysql_bug_insertid_80 
Client compile version: 80003
Client runtime version: 8.0.3-rc
Server version: 50718

Round 1
mysql->insert_id: 49
mysql->insert_id: 49
Got back insert id: 49

Round 2
mysql->insert_id: 50
ping
mysql->insert_id: 0
Got back insert id: 0
mysql_bug_insertid_80: mysql_bug_insertid.c:41: main: Assertion `insertid > 0' failed.
Aborted (core dumped)
[8 Jan 2018 20:53] Daniël van Eeden
On 5.6.38: sql-common/client.c:4199 (in cli_read_query_result) is where mysql->insert_id is set.
On 5.7.20: sql-common/client.c:751 (in read_ok_ex) is where mysql->insert_id is set.

If I set a breakpoint on these then the breakpoint is hit twice for 5.6 and three times for 5.7.

So after sending ping it sets mysql->insert_id while handling the response.
[9 Jan 2018 5:39] MySQL Verification Team
Hello Daniël,

Thank you for the report and test case.

Thanks,
Umesh
[12 Jan 2018 7:46] Daniël van Eeden
https://github.com/mysql/mysql-server/pull/191
[12 Jan 2018 15:51] OCA Admin
Contribution submitted via Github - Fix for Bug #89139 
(*) Contribution by Daniël van Eeden (Github dveeden, mysql-server/pull/191#issuecomment-357236947): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_162563388.txt (text/plain), 821 bytes.

[16 Apr 2018 11:11] Paul DuBois
Posted by developer:
 
Updated:

https://dev.mysql.com/doc/refman/5.7/en/mysql-insert-id.html
https://dev.mysql.com/doc/refman/8.0/en/mysql-insert-id.html