MySQL Bugs: #26921: Problem in mysql_insert_id() Embedded C API function

Bug #26921	Problem in mysql_insert_id() Embedded C API function
Submitted:	7 Mar 2007 16:22	Modified:	24 Jan 2008 20:11
Reporter:	Oleg Yaroshevych	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Embedded Library ( libmysqld )	Severity:	S3 (Non-critical)
Version:	5.1.22	OS:	Windows (XP SP2)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any
Tags:	embedded

Description:
In Embedded server the mysql_insert_id() function returns invalid value (0) after SELECT commands. This function works fine when using libmysql.dll.

How to repeat:
I have no time to create program in C (since I use C# wrappers), but I can provide an algorithm to reproduce the problem:
- invoke INSERT query;
- mysql_insert_id() returns correct value;
- invoke SELECT query;
- mysql_insert_id() returns incorrect value (0).

Thank you for the bug report. Could you please test with version 4.1.XX
since the version reported is currently not supported:

http://dev.mysql.com/doc/refman/5.0/en/libmysqld.html

22.1. libmysqld, the Embedded MySQL Server Library
The embedded MySQL server library is NOT part of MySQL 5.0. It is part of previous editions and will be included in future versions, starting with MySQL 5.1. You can find appropriate documentation in the corresponding manuals for these versions. In this manual, only an overview of the embedded library is provided. 

Thanks in advance.

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".

Bug is reprodiced with 5.1.22.

Here is code to reproduce the problem (I use VS2005):

static char *server_options[] = { "mysql_test", "--defaults-file=my.ini", NULL };

int num_elements = (sizeof(server_options) / sizeof(char *)) - 1;

static char *server_groups[] = { "embedded", "server",  "this_program_SERVER",  (char *)NULL};

int test_insert_id()
{
  mysql_library_init(num_elements, server_options, server_groups);
  MYSQL *mysql = mysql_init(NULL);
  mysql_options(mysql, MYSQL_READ_DEFAULT_GROUP, "libmysqld_client");
  mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);
  int clientflag = CLIENT_FOUND_ROWS | CLIENT_MULTI_QUERIES | CLIENT_MULTI_STATEMENTS;
  mysql_real_connect(mysql, NULL,NULL,NULL, "mysql", 0,NULL,clientflag);

  cout << mysql_get_server_info(mysql) << endl;

  if (mysql_query(mysql, "drop table if exists autoinc_test") != 0)
  {
    cout << "error dropping table" << endl;
    return 1;
  }

  if (mysql_query(mysql, "create table autoinc_test (id int primary key auto_increment, col1 char(20))") != 0)
  {
    cout << "error creating table" << endl;
    return 1;
  }

  if (mysql_query(mysql, "insert into autoinc_test (col1) values ('a')") != 0)
  {
    cout << "insert error" << endl;
    return 1;
  }

  cout << "inserted" << endl;

  cout << "mysql_insert_id = " << mysql_insert_id(mysql) << endl;

  if (mysql_query(mysql, "select id from autoinc_test") != 0)
  {
    cout << "select error" << endl;
    return 1;
  }

  MYSQL_RES * results = mysql_store_result(mysql);

  if (results == 0)
  {
    cout << "no results" << endl;
    return 1;
  }

  MYSQL_ROW record;

  while((record = mysql_fetch_row(results)) != 0)
    cout << record[0] << endl;

  mysql_free_result(results);

  cout << "mysql_insert_id = " << mysql_insert_id(mysql) << endl;

  mysql_close(mysql);
  mysql_library_end();
  return 0;
}

int main(void)
{
  return test_insert_id();
}
----------------------------
Output from 5.1.22:

5.1.22-rc-community-debug-embedded
inserted
mysql_insert_id = 1
1
mysql_insert_id = 0
Press any key to continue . . .
----------------------------
Output from 4.1.18:

4.1.18-embedded-debug
inserted
mysql_insert_id = 1
1
mysql_insert_id = 1
Press any key to continue . . .

Bug is reprodiced with 5.1.22.

Here is code to reproduce the problem (I use VS2005):

static char *server_options[] = { "mysql_test", "--defaults-file=my.ini", NULL };

int num_elements = (sizeof(server_options) / sizeof(char *)) - 1;

static char *server_groups[] = { "embedded", "server",  "this_program_SERVER",  (char *)NULL};

int test_insert_id()
{
  mysql_library_init(num_elements, server_options, server_groups);
  MYSQL *mysql = mysql_init(NULL);
  mysql_options(mysql, MYSQL_READ_DEFAULT_GROUP, "libmysqld_client");
  mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);
  int clientflag = CLIENT_FOUND_ROWS | CLIENT_MULTI_QUERIES | CLIENT_MULTI_STATEMENTS;
  mysql_real_connect(mysql, NULL,NULL,NULL, "mysql", 0,NULL,clientflag);

  cout << mysql_get_server_info(mysql) << endl;

  if (mysql_query(mysql, "drop table if exists autoinc_test") != 0)
  {
    cout << "error dropping table" << endl;
    return 1;
  }

  if (mysql_query(mysql, "create table autoinc_test (id int primary key auto_increment, col1 char(20))") != 0)
  {
    cout << "error creating table" << endl;
    return 1;
  }

  if (mysql_query(mysql, "insert into autoinc_test (col1) values ('a')") != 0)
  {
    cout << "insert error" << endl;
    return 1;
  }

  cout << "inserted" << endl;

  cout << "mysql_insert_id = " << mysql_insert_id(mysql) << endl;

  if (mysql_query(mysql, "select id from autoinc_test") != 0)
  {
    cout << "select error" << endl;
    return 1;
  }

  MYSQL_RES * results = mysql_store_result(mysql);

  if (results == 0)
  {
    cout << "no results" << endl;
    return 1;
  }

  MYSQL_ROW record;

  while((record = mysql_fetch_row(results)) != 0)
    cout << record[0] << endl;

  mysql_free_result(results);

  cout << "mysql_insert_id = " << mysql_insert_id(mysql) << endl;

  mysql_close(mysql);
  mysql_library_end();
  return 0;
}

int main(void)
{
  return test_insert_id();
}
----------------------------
Output from 5.1.22:

5.1.22-rc-community-debug-embedded
inserted
mysql_insert_id = 1
1
mysql_insert_id = 0
Press any key to continue . . .
----------------------------
Output from 4.1.18:

4.1.18-embedded-debug
inserted
mysql_insert_id = 1
1
mysql_insert_id = 1
Press any key to continue . . .

Thank you for the bug report.

C:\mysql>bug26921.exe
Embedded Server: 5.1.23-rc-nt-embedded
inserted
mysql_insert_id = 1
1 - (null)
mysql_insert_id = 0

C:\mysql>cd ..

C:\>cd mysql

C:\mysql>bug26921.exe
Embedded Server: 4.1.24-embedded
inserted
mysql_insert_id = 1
1 - (null)
mysql_insert_id = 1

C:\mysql>

#include <stdio.h>
#include <stdlib.h>
#include <stdarg.h>
#include <my_global.h>
#include <mysql.h>

MYSQL *mysql;
MYSQL_RES *results;
MYSQL_ROW record;

static char *server_options[] = \
{ "mysql_test", "--defaults-file=c:/my.ini", NULL };
int num_elements = (sizeof(server_options) / sizeof(char *)) - 1;

static char *server_groups[] = { "libmysqld_server", 
                                 "libmysqld_client", NULL };

int main(void)
{
   mysql_library_init(num_elements, server_options, server_groups);
   mysql = mysql_init(NULL);
   mysql_options(mysql, MYSQL_READ_DEFAULT_GROUP, "libmysqld_client");
   mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);

   mysql_real_connect(mysql, NULL,NULL,NULL, "test", 0,NULL,0);

   printf("Embedded Server: %s\n",mysql_get_server_info(mysql));

   if (mysql_query(mysql, "drop table if exists autoinc_test") != 0)
  {
    printf("error dropping table\n");
    return 1;
  }

  if (mysql_query(mysql, "create table autoinc_test (id int primary key auto_increment,col1 char(20))") != 0)
  {
    printf("error creating table\n");
    return 1;
  }

  if (mysql_query(mysql, "insert into autoinc_test (col1) values ('a')") != 0)
  {
    printf("insert error\n");
    return 1;
  }

  printf("inserted\n");

  printf("mysql_insert_id = %d\n",mysql_insert_id(mysql));

  if (mysql_query(mysql, "select id from autoinc_test") != 0)
  {
    printf("select error\n");
    return 1;
  }

   results = mysql_store_result(mysql);

   while((record = mysql_fetch_row(results))) {
     printf("%s - %s \n", record[0], record[1]);
   }

   printf("mysql_insert_id = %d\n",mysql_insert_id(mysql));

   mysql_free_result(results);
   mysql_close(mysql);
   mysql_library_end();

   return 0;
}

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38978

ChangeSet@1.2581, 2007-11-30 19:16:13+04:00, holyfoot@mysql.com +2 -0
  Bug #26921 Problem in mysql_insert_id() Embedded C API function.
  
  client library only sets mysql->insert_id when query returned
  no recordset. So the embedded library should behave the same way

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

Noted in 5.1.23, 6.0.5 changelogs.

The mysql_insert_id() C API function sometimes returned different
results for libmysqld and libmysqlclient.