MySQL Bugs: #25714: getGeneratedKeys() does not work with FEDERATED table

Bug #25714	getGeneratedKeys() does not work with FEDERATED table
Submitted:	19 Jan 2007 4:11	Modified:	30 Jul 2007 10:28
Reporter:	Suguru Namura	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S2 (Serious)
Version:	5.0.46-BK	OS:	Windows (Windows XP SP2, Linux)
Assigned to:	Antony Curtis	CPU Architecture:	Any
Tags:	auto_increment, federated, java

Description:
getGeneratedKeys returns empty ResultSet when connect to mysql FEDERATED table.

How to repeat:
Java1.6
Connector/J 5.0.4
mysql-max-nt 5.0.27

1. Create remote table

CREATE TABLE user (
       user_id INT UNSIGNED NOT NULL AUTO_INCREMENT
     , name CHAR(60) NOT NULL
     , status TINYINT UNSIGNED NOT NULL
     , PRIMARY KEY (user_id)
);

2. Create federated table

CREATE TABLE user (
       user_id INT UNSIGNED NOT NULL AUTO_INCREMENT
     , name CHAR(60) NOT NULL
     , status TINYINT UNSIGNED NOT NULL
     , PRIMARY KEY (user_id)
)
ENGINE=FEDERATED
CONNECTION='mysql://xxxx:3306/xxx/user';

3. Execute java code

PreparedStatement ps = conn.prepareStatement("insert into user values (0, 'test', 0);");
ps.executeUpdate();
		
ResultSet rs = ps.getGeneratedKeys();

if (rs.next()) {
System.out.println(rs.getInt(1));
} else {
System.out.println("GENERATED KEY DOES NOT EXISTS!");
}
		
rs.close();
ps.close();
conn.close();

4. Result

when connect to source(MyISAM) table
105 (generated value)

when connect to federated table
GENERATED KEY DOES NOT EXISTS!

Thank you for a problem report. Please, try to repeat with a newer version of Connector/J, 5.0.5, and server, if possible.

It does not looks like a server bug for me according to the following test:

openxs@suse:~/dbs/4.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.38-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE user (
    ->        user_id INT UNSIGNED NOT NULL AUTO_INCREMENT
    ->      , name CHAR(60) NOT NULL
    ->      , status TINYINT UNSIGNED NOT NULL
    ->      , PRIMARY KEY (user_id)
    -> )
    -> ENGINE=FEDERATED
    -> connection='mysql://root:root@127.0.0.1:3340/test/user';
Query OK, 0 rows affected (0.06 sec)

mysql> insert into user values(0, 'test' 0);
ERROR 1064: You have an error in your SQL syntax; check the manual that correspo
nds to your MySQL server version for the right syntax to use near '0)' at line 1

mysql> insert into user values(0, 'test', 0);
Query OK, 1 row affected (0.03 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.01 sec)

mysql> select * from user;
+---------+------+--------+
| user_id | name | status |
+---------+------+--------+
|       1 | test |      0 |
+---------+------+--------+
1 row in set (0.02 sec)

mysql> insert into user values(0, 'test', 0);
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+---------+------+--------+
| user_id | name | status |
+---------+------+--------+
|       1 | test |      0 |
|       2 | test |      0 |
+---------+------+--------+
2 rows in set (0.01 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

So, new auto_increment key values are really generated for federated table.

I tried with Connector/J 5.0.5, but getGeneratedKeys() still not returns generated value.

mysql > select last_insert_id()
is working on my test server.

Additonal info:

MySQL and MySQL(Federated) are running on same computer.
Client --> MySQL:3306 --federated--> MySQL:3307(Real data is here)

Sorry for a delay with this bug report. This is a serious server bug (mysql_insert_id/FEDERATED engine does NOT report last insert id for FEDERATED table) that affects connectors and C API. Verified with latest 5.0.46-BK on Linux:

openxs@linux:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.46-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table fed (id int auto_increment primary key, value int)
    -> engine=federated
    -> connection='mysql://root:root@127.0.0.1:3340/test/ed';
Query OK, 0 rows affected (0.02 sec)

mysql> select * from fed;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     2 |
+----+-------+
3 rows in set (0.01 sec)

mysql> insert into fed set value=4;
Query OK, 1 row affected (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                4 |
+------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

So, last_insert_id() works. But:

openxs@linux:~/dbs/5.0> export CFG=/home/openxs/dbs/5.0/bin/mysql_config        openxs@linux:~/dbs/5.0> export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/openxs/dbs/5.0/lib/mysql

openxs@linux:~/dbs/5.0> gcc -o 25714 `$CFG --cflags` bug25714.c `$CFG --libs`
openxs@linux:~/dbs/5.0> ./25714

0 inserted
0 inserted
openxs@linux:~/dbs/5.0> cat bug25714.c
#include <mysql.h>
#include <string.h>
#include <stdio.h>
#include <assert.h>

int main ()
{
    MYSQL       conn;

    mysql_init (&conn);
    if (!mysql_real_connect (
        &conn,
        "127.0.0.1",
        "root",
        "",
        "test",
        3306,
        NULL,
        CLIENT_FOUND_ROWS)) {
                fprintf(stderr, "Failed to connect to database: Error: %s\n",
        mysql_error(&conn));
                return 1;
        } else {
                printf("%s\n", mysql_error(&conn));
        }

        int             OK;

        const char* query4 = "INSERT INTO fed SET Value=54";

        OK = mysql_real_query (&conn, query4, strlen(query4));

        assert(0 == OK);

        printf("%ld inserted\n",
   (long) mysql_insert_id(&conn));

        const char* query5 = "INSERT INTO fed SET Value=55";

        OK = mysql_real_query (&conn, query5, strlen(query5));

        assert(0 == OK);

        printf("%ld inserted\n",
   (long) mysql_insert_id(&conn));

        return 0;
};
openxs@linux:~/dbs/5.0>

as proved with C API test case above, mysql_insert_id() does NOT work as expected for FEDERATED table.

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/31042

ChangeSet@1.2527, 2007-07-17 12:25:46-07:00, antony@pcg5ppc.xiphis.org +8 -0
  Bug#25714
    "getGeneratedKeys() does not work with FEDERATED table"
    mysql_insert() expected the storage engine to update the row data
    during the write_row() operation with the value of the new auto-
    increment field. The field must be updated when only one row has
    been inserted as mysql_insert() would ignore the thd->last_insert.
    This patch implements HA_STATUS_AUTO support in ha_federated::info()
    and ensures that ha_federated::write_row() does update the row's
    auto-increment value.
    The test case was written in C as the protocol's 'id' value is
    accessible through libmysqlclient and not via SQL statements.
    mysql-test-run.pl was extended to enable running the test binary.

pushed to 5.0-engines and 5.1-engines repositories

Pushed into 5.1.21-beta

Pushed into 5.0.48

A note has been added to the 5.1.21 and 5.0.48 changelogs: 

When using a FEDERATED table, the value of last_insert_id() would not correctly update the C API interface, which would affect the autogenerated ID returned both through the C API and the MySQL protocol, affecting Connectors that used the protocol and/or C API.

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/33225

ChangeSet@1.2509, 2007-08-28 16:06:08+02:00, df@pippilotta.erinye.com +1 -0
  fix bug in test case for bug#25714

Pushed into 5.0.50

Pushed into 5.1.23-beta