MySQL Bugs: #14768: last_insert_id not set using Federated

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:	Windows (Windows XP/Linux)
Assigned to:	Bugs System	CPU Architecture:	Any

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'

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

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)

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

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

Need specific version numbers for the patch.

5.0.19

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

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