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

[7 Mar 2007 16:22] Oleg Yaroshevych
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).
[8 Mar 2007 10:39] MySQL Verification Team
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.
[8 Apr 2007 23:00] Bugs System
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".
[22 Nov 2007 15:00] Oleg Yaroshevych
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 . . .
[22 Nov 2007 15:10] Oleg Yaroshevych
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 . . .
[26 Nov 2007 17:41] MySQL Verification Team
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;
}
[30 Nov 2007 15:23] Bugs System
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
[14 Dec 2007 8:17] Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 8:20] Bugs System
Pushed into 6.0.5-alpha
[24 Jan 2008 20:11] Paul DuBois
Noted in 5.1.23, 6.0.5 changelogs.

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