mySQL and LAST_INSERT_ID problem

Question

Hi,

I have a DB-Application and now we have to start with replication (master-master-replication). We build an stored-function witch returns an BIGINT. This value is unique on all involved servers.

The situation: I have a table definition:

create table test (
 id BIGINT not null primary key auto_increment,
 col1 TEXT);

the table has an before insert trigger:

CREATE TRIGGER test_insert BEFORE INSERT ON test
 FOR EACH ROW BEGIN
  IF NEW.id = 0 THEN
   SET @my_uuid = MYUUID();
   SET NEW.id = @my_uuid;
  END IF;
 END;

after an insert into test (col1) values ("foo") I need the value of the LAST_INSERT_ID() - but I only get the value "0".

I try´d this in the trigger:

SET NEW.id = LAST_INSERT_ID(@my_uuid);

but it don´t work.

I read the mysql manpage witch says, that all changes on last_insert_id within triggers and functions will be canceled at the end of the trigger.

So I try to avoid changing the application (witch use php.last_insert_id())...

any ideas how to solve this without changing php-code?

greatings.

Answer 1

I assume that you're trying to avoid an insert on the two masters ending up with the same ID.

One way to do this (assuming 2 masters) is to set auto_increment_increment to 2, and auto_increment_offset to 0 on one master, and 1 on the other.

This will result in ids on each master that cannot collide with the other.

Aside: with a bigint and random UUIDs, you current approach is likely to have a collision somewhere around 3 billion rows due to the birthday paradox.