Bug #14768 last_insert_id not set using Federated
Submitted: 8 Nov 2005 19:38 Modified: 13 May 2006 4:51
Reporter: Scott Noyes (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.15-nt-max/BK 5.0.16 OS:Microsoft Windows (Windows XP/Linux)
Assigned to: Bugs System

[8 Nov 2005 19:38] Scott Noyes
Description:
Inserting to an auto_increment field in a Federated table does not set the value returned by last_insert_id().

How to repeat:
On remote host:
CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY);

On local host:
CREATE TABLE f_t (id INT AUTO_INCREMENT PRIMARY KEY) engine=federated CONNECTION='mysql://user:password@remote:port/test/t';
INSERT INTO f_t VALUES (NULL);
SELECT * FROM f_t; -- correctly returns '1'
SELECT LAST_INSERT_ID(); -- incorrectly returns '0'
[8 Nov 2005 22:04] Scott Noyes
This same effect appears in the NEW value for a trigger on a federated table.

-- The tables into which we will insert
create table f_t (id int auto_increment primary key) engine=federated connection='mysql://user:pass@host:port/db/table';
create table m_t (id int auto_increment primary key) engine=myisam

-- The table on which the trigger will act
create table t (id int);

-- The triggers
create trigger f_t_AI after insert on f_t for each row insert into t values (NEW.id);
create trigger m_t_AI after insert on m_t for each row insert into t values (NEW.id);

-- Example of bad results
INSERT INTO f_t VALUES (NULL);
SELECT * FROM f_t; -- correctly returns 1
SELECT * FROM t; -- incorrectly returns 0

-- Example of good results
INSERT INTO m_t VALUES (NULL);
SELECT * FROM m_t; -- correctly returns 1
SELECT * FROM t; -- correctly returns 1
[8 Nov 2005 23:19] Miguel Solorzano
c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-nt-max

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

mysql> CREATE TABLE f_t (id INT AUTO_INCREMENT PRIMARY KEY) engine=federated
    -> CONNECTION='mysql://user1:user1@hegel:3306/test/t';
Query OK, 0 rows affected (0.22 sec)

mysql> INSERT INTO f_t VALUES (NULL);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.03 sec)

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.16-debug

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

mysql> CREATE TABLE f_t (id INT AUTO_INCREMENT PRIMARY KEY) engine=federated
    -> CONNECTION='mysql://user1:user1@192.168.0.33:3306/test/t';
Query OK, 0 rows affected (0.04 sec)

mysql> show create table f_t\G
*************************** 1. row ***************************
       Table: f_t
Create Table: CREATE TABLE `f_t` (
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://user1:user1@192.168.0.33:3306/test/t'
1 row in set (0.00 sec)

mysql> INSERT INTO f_t VALUES (NULL);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT LAST_INSERT_ID(); 
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)
[8 Jan 2006 23:18] 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/757
[27 Jan 2006 23:39] 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/1765
[2 Feb 2006 18:23] Mike Hillyer
Need specific version numbers for the patch.
[12 May 2006 23:22] Patrick Galbraith
5.0.19
[13 May 2006 4:51] Paul Dubois
Noted in 5.0.19 changelog.

Generating an <literal>AUTO_INCREMENT</literal> value through
a <literal>FEDERATED</literal> table did not set the value
returned by <literal>LAST_INSERT_ID()</literal>.
[4 Jan 2007 19:19] Geoff
This is still an issue.
We are running mysql-server-5.0.22-1.FC5.1
When using performing an INSERT to a federated table LAST_INSERT_ID() returns 0