MySQL Bugs: #10795: REPLACE reallocates new AUTO

Bug #10795	REPLACE reallocates new AUTO_INCREMENT
Submitted:	23 May 2005 6:58	Modified:	3 Oct 2008 19:11
Reporter:	B Jones	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.1.10	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
- We are using the MySQL REPLACE statement to update a table.
- That table has an AUTO_INCREMENT PRIMARY KEY.
- Whenever we update a record in that table, the a new AUTO_INCREMENT PRIMARY KEY is allocated.

For example
Customer_Id         Customer_Name         Customer_City
1000                    Fred Corporation         London
1001                    Nexus Inc                  New York

when we update using REPLACE, the customer ids have all changed.

Customer_Id         Customer_Name         Customer_City
1002                    Fred Corporation         London
1003                    Nexus Inc                  New York

This breaks all the foreign key relationships with the table.  In theory you can use a combination of temporary tables and DELETE and INSERT to work around this, but that slows things down and introduces code bloat.  REPLACE is a nice idea, but breaking the primary/foreign key relationships prevents you from using it in an normalised enterprise-level database.

It would be better if REPLACE gave you the option of preserving the old AUTO_INCREMENT value.

This was raised by someone else back in April 1999.  Monty (that Monty?) said it sounded was a good idea and went on the TODO list.  Did this ever happen?

http://lists.mysql.com/mysql/1406

How to repeat:
In this example, the master DONKEY table is being updated by the MULE table.

DROP   TABLE donkey;
CREATE TABLE donkey (
  id int(11) NOT NULL auto_increment,
  name char(64) collate latin1_general_ci default NULL,
  city char(64) collate latin1_general_ci default NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY name (name)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1;
INSERT INTO donkey VALUES (1, 'Mr Ed', 'Texas');
INSERT INTO donkey VALUES (2, 'Quick Draw McGraw', 'Cactus');

DROP   TABLE mule;
CREATE TABLE mule (
  name char(64) collate latin1_general_ci default NULL,
  city char(64) collate latin1_general_ci default NULL,
  UNIQUE KEY name (name)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
INSERT INTO mule VALUES ('Mr Ed', 'Miami');
INSERT INTO mule VALUES ('Shadowfax', 'Rohan');

SELECT * FROM donkey;
SELECT * FROM mule;

REPLACE donkey (name, city)
	SELECT name, city 
	FROM mule;
	
SELECT * FROM donkey;

--  Everytime replace runs, the master table primary keys change.  This breaks any foreign keys.

select * from donkey
--------------

+----+-------------------+--------+-------+
| id | name              | city   | dflag |
+----+-------------------+--------+-------+
| 13 | Mr Ed             | Miami  |  NULL |
|  2 | Quick Draw McGraw | Cactus |  NULL |
| 14 | Shadowfax         | Miami  |  NULL |
+----+-------------------+--------+-------+
3 rows in set (0.00 sec)

mysql> replace into donkey (name, city) select name, city from mule; select * from donkey;
--------------
replace into donkey (name, city) select name, city from mule
--------------

Query OK, 4 rows affected (0.01 sec)
Records: 2  Duplicates: 2  Warnings: 0

--------------
select * from donkey
--------------

+----+-------------------+--------+-------+
| id | name              | city   | dflag |
+----+-------------------+--------+-------+
| 15 | Mr Ed             | Miami  |  NULL |
|  2 | Quick Draw McGraw | Cactus |  NULL |
| 16 | Shadowfax         | Miami  |  NULL |
+----+-------------------+--------+-------+
3 rows in set (0.00 sec)

mysql> replace into donkey (name, city) select name, city from mule; select * from donkey;
--------------
replace into donkey (name, city) select name, city from mule
--------------

Query OK, 4 rows affected (0.00 sec)
Records: 2  Duplicates: 2  Warnings: 0

--------------
select * from donkey
--------------

+----+-------------------+--------+-------+
| id | name              | city   | dflag |
+----+-------------------+--------+-------+
| 17 | Mr Ed             | Miami  |  NULL |
|  2 | Quick Draw McGraw | Cactus |  NULL |
| 18 | Shadowfax         | Miami  |  NULL |
+----+-------------------+--------+-------+
3 rows in set (0.00 sec)

Suggested fix:
Have an option for REPLACE's DELETE/INSERT combo to keep the DELETEd records last INSERT ID.

This is the expected behavior:

http://dev.mysql.com/doc/mysql/en/replace.html

REPLACE works exactly like INSERT, except that if an old record in the table has
the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old
record is deleted before the new record is inserted. See Section 13.1.4, “INSERT Syntax”.

Then a new auto_increment is applied.

I'm aware how REPLACE works.  That wasn't the issue.

To recap: The suggestion was that REPLACE lets you keep the same primary key.  Monty said this was on their TODO list. Could you please tell me the status of that?

Please don't close this bug report.

I don't think it happened yet.

besides, we have now INSERT ... ON DUPLICATE KEY UPDATE.
It's what one should use to *update* the record, REPLACE is, indeed, just an DELETE+INSERT.

Hi Sergei,

Thanks for your response.  Had investigated INSERT ... ON DUPLICATE KEY but the problem is you have to hardcode the data assignments.  That's okay if you're updating specific values, but not if you want to update your data from another table.

By examples, you can do this:

INSERT INTO donkey (name, city)
VALUES ('Mr Ed', 'London')
ON DUPLICATE KEY UPDATE 
	city = 'London';

But this is the shortcoming; you can't do this (which has redundant code anyway):

INSERT INTO donkey (name, city)
SELECT name, city from mule M
ON DUPLICATE KEY 
   UPDATE donkey.city = M.city;
	
It would be nicer if you could just do this:  Ideal actually (ie. without having to specify hard-coded column values):

INSERT INTO donkey (name, city)
SELECT name, city from mule M
ON DUPLICATE KEY UPDATE;

Of course you can do the same thing with DELETEs, INSERTs, etc., or use REPLACE with a temporary table, but all these involve a speed penalty, because we're looking up the table twice.

Which gets us back to REPLACE.  Even if it did that, and let us keep the old AUTO_INCREMENT field, that would be fine.  REPLACE is getting long in the tooth anyway and could use an overhaull; it is incompatibe with InnoDB's referential integrity (when REPLACE DELETE's a record, it breaks all the primary-foreign key relationships).

REPLACE INTO donkey (name, city)
SELECT name, city from mule M;

To sum up, what we are asking for is an easier way to REPLACE or UPDATE records from a SELECT group.  Just a bit of background; We've been using MySQL for online transactions for some time now. Lately we've been building an enterprise database with MySQL using InnoDB.  Hope this suggestion is useful.

You should be able to do

INSERT INTO donkey (name, city)
SELECT name, city from mule M
ON DUPLICATE KEY UPDATE donkey.city = VALUES(city);

VALUES() pseudo-function returns the value that would be inserted into the column
if INSERT succeeded.

Hi Sergei,

That looked promising; pretty much the sort of statement requested,
but apparently MySQL doesn't let you you specify a column in both the
INSERT SELECT clause and the DUPLICATE KEY UPDATE.

INSERT INTO donkey (name, city)
SELECT name, city from mule M
ON DUPLICATE KEY UPDATE donkey.city = VALUES(city);
--------------	
ERROR 1110 (42000): Column 'city' specified twice
	
-- If they're mutually exclusive, that's ok.
--
INSERT INTO donkey (name)
SELECT name from mule M
ON DUPLICATE KEY UPDATE donkey.city = VALUES(M.city);
--------------
Query OK, 3 rows affected (0.05 sec)
Records: 2  Duplicates: 1  Warnings: 0

-- But if it's the same, it throws an error
--
INSERT INTO donkey (name)
SELECT name from mule M
ON DUPLICATE KEY UPDATE donkey.name = VALUES(M.city);
--------------
ERROR 1110 (42000): Column 'name' specified twice

Not sure if this is considered a bug, but perhaps if INSERT ON DUPLICATE KEY 
was enhanced to allow you to provide the column to be specified in either place, that would provide the sort of functionality originally requested? It'd also be compatible with existing code.

This was considered a bug (http://bugs.mysql.com/bug.php?id=8147), and is fixed in 4.1.11

Thanks very much, Sergei.  Good Timing!