Duplicate entry error message format

I have a problem with mysql duplicate error messages. On one of my machines the error looks like:

Duplicate entry 'foobar' for key 2

on the other machine it looks like:

Duplicate entry 'foobar' for name

• One machine prints the key index, the other the key name.
• One machine is an Ubuntu 10.04 Desktop; the other a Debian Lenny Server
• MySQL Versions:

Ubuntu: Server version: 5.1.41-3ubuntu12.6 Protocol version: 10

Debian: Server version: 5.0.51a-24+lenny1-log Protocol version: 10

Should this be really a version issue? Any suggestions 开发者_运维问答how I can control this?

I'm guessing that you are attempting to read the error message in your code and take appropriate action and that the differing messages is causing a headache in trying to decide what action to take. If so, I suggest that you use the mysql error code rather than the error message. The message text will probably change with different software releases and language translations, but the code should always stay the same. In php you can check the code with:

mysql_errno($dbh);

I believe the error code that corresponds to that message is 1062.

after digging through the mysql server source code I can say this is a version issue.

version 5.0.41: the error_dump method looks similar to:

dump_error(..,.., key.nr +1);

version 5.1.51: the error_dump method looks similar to:

dump_error(..,.., key.name);

So there is no reliable way to detect what key is a doublette with mysql?

Here's a regex I use to detect a specific index in my code across both MySQL 5.0 and 5.1:

/Duplicate entry '.*?' for key ('index_films_on_imdb_id'|2)/

This is definitely brittle with the index number in there, but in my case the DB schema is pretty reliably generated and I didn't want to go down the route of parsing the CREATE TABLE statement which would have it's own difficulties.

The best thing would be to just get everything upgraded to 5.1 if possible.