MySQL Bugs: #46976: Duplicate erro output different for 5.1.33 from dev article

Bug #46976	Duplicate erro output different for 5.1.33 from dev article
Submitted:	28 Aug 2009 15:00	Modified:	29 Jan 2013 23:30
Reporter:	adi teo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.33	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	%d, %s, 1062, duplicate, error, ERROR 1062, key

Description:
both docs show the same information
http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html#error_er_dup_entry
http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html#error_er_dup_entry 

Error: |1062| SQLSTATE: |23000| (|ER_DUP_ENTRY|)
Message: Duplicate entry '%s' for key %d 

On 5.0 works rigth : Duplicate entry '1831111160025' for key 2 
On 5.1.33 the message is formatted wrong : Duplicate entry '1831111160025' for key 'cnp' 

so instead of %d is returned a '%s';

How to repeat:
Tested on unix machine and on winXp  (apache2.2, Mysql 5.1.33/5.0.51)

quite simple to replicate. Createa a table with an unique index. insert 2 identical values.

Tested also in command line mysql, on phpmyadmin and in php supported commands. 
Results are staed above.

Suggested fix:
Change '%s' to %d as stated in docs, for versions that changed it. (5.1)

Thank you for the problem report. I think http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html#error_er_dup_entry manual page should be updated to reflect this change in server's behavior.

Hm, are you aware that 5.1 has two similar error messages?

Error: 1062 SQLSTATE: 23000  (ER_DUP_ENTRY)

Message: Duplicate entry '%s' for key %d

Error: 1586 SQLSTATE: 23000  (ER_DUP_ENTRY_WITH_KEY_NAME)

 Message: Duplicate entry '%s' for key '%s'

So do you suggest that we don't use ER_DUP_ENTRY_WITH_KEY_NAME any more, but ER_DUP_ENTRY in all cases?

i am aware of that...and specifically use erro code 1062. The buh is real, as i specific test for the returned eroro code 1062:

elseif($this->last_errno==1062){

//for 5.1
if(preg_match("/for\skey\s\'?([a-zA-Z0-9_-]+)\'?/",$this->last_error,$m)) 

//for all before
if(preg_match("/for\skey\s([0-9]+)/",$this->last_error,$m)) 

{ 
//other
}
}.

i never gor the error code 1586.
thanks for ur reply

sql/share/errmsg.txt has a %d for the final field in both 5.0 and 5.1.

5.0:

ER_DUP_ENTRY 23000 S1009
   ...
   eng "Duplicate entry '%-.64s' for key %d"

5.1:

# When using this error code, please use ER(ER_DUP_ENTRY_WITH_KEY_NAME)
# for the message string.  See, for example, code in handler.cc.
ER_DUP_ENTRY 23000 S1009
   ...
   eng "Duplicate entry '%-.192s' for key %d"

So, aside from the difference in length of the %s field, both messages
contain a string and a number, which is what is indicated in both the
5.0 and 5.1 manuals:

"
Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY)

Message: Duplicate entry '%s' for key %d
"

By the way, testing the *content* of error messages is a bad idea. There is no guarantee that the content will remain constant or always in a given language.

sql/share/errmsg.txt has a %d for the final field in both 5.0 and 5.1.

5.0:

ER_DUP_ENTRY 23000 S1009
   ...
   eng "Duplicate entry '%-.64s' for key %d"

5.1:

# When using this error code, please use ER(ER_DUP_ENTRY_WITH_KEY_NAME)
# for the message string.  See, for example, code in handler.cc.
ER_DUP_ENTRY 23000 S1009
   ...
   eng "Duplicate entry '%-.192s' for key %d"

So, aside from the difference in length of the %s field, both messages
contain a string and a number, which is what is indicated in both the
5.0 and 5.1 manuals:

"
Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY)

Message: Duplicate entry '%s' for key %d
"

By the way, testing the *content* of error messages is a bad idea. There is no guarantee that the content will remain constant or always in a given language.

sql/share/errmsg.txt has a %d for the final field in both 5.0 and 5.1.

5.0:

ER_DUP_ENTRY 23000 S1009
   ...
   eng "Duplicate entry '%-.64s' for key %d"

5.1:

# When using this error code, please use ER(ER_DUP_ENTRY_WITH_KEY_NAME)
# for the message string.  See, for example, code in handler.cc.
ER_DUP_ENTRY 23000 S1009
   ...
   eng "Duplicate entry '%-.192s' for key %d"

So, aside from the difference in length of the %s field, both messages
contain a string and a number, which is what is indicated in both the
5.0 and 5.1 manuals:

"
Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY)

Message: Duplicate entry '%s' for key %d
"

By the way, testing the *content* of error messages is a bad idea. There is no guarantee that the content will remain constant or always in a given language.

thanks Paul,

i do know that testing for message content can't be that reliable..... i do need the index position (the check was for english)...never had problems until know with the different erro message returned.

besides that i do not see the point of using different messages in 5.1 when it reverted back as the original in subsequent versions.
[quote]
# When using this error code, please use ER(ER_DUP_ENTRY_WITH_KEY_NAME)
# for the message string.  See, for example, code in handler.cc.
ER_DUP_ENTRY 23000 S1009
   ...
   eng "Duplicate entry '%-.192s' for key %d"
[/quote]

i don't know how to use that specific error, as the mysql server returns the 1062 error all the times and never the ER_DUP_ENTRY_WITH_KEY_NAME, as i do not control the version of the mysql server.

i use that in universal DB wrapper, and i don't think that is such a great ideea to put in mysql version checks. It worked fine from version 4 to 6a, except 5.1.

@Ingo Strüwing 
i can't suggest what to use, as i always got 1062, as stated above.

if it's needed i can put up some test cases (print scrren or a server somewhere to verify that...)
in fact the %d in 1062 in 5.1 is the defiinition from the other....those might have been misplaced.

Here is a test case:

drop table if exists t;
create table t (i int not null primary key);
insert into t values(0),(0);

Output from 5.0.86:

mysql> drop table if exists t;
Query OK, 0 rows affected (0.04 sec)

mysql> create table t (i int not null primary key);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(0),(0);
ERROR 1062 (23000): Duplicate entry '0' for key 1

Output from 5.1.39:

mysql> drop table if exists t;
Query OK, 0 rows affected (0.06 sec)

mysql> create table t (i int not null primary key);
Query OK, 0 rows affected (0.14 sec)

mysql> insert into t values(0),(0);
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

So indeed in 5.1 the server is printing something other than a number for the final field.

thank you

Moving to documentation. The new message format is more descriptive and useful, there is no reason to revert the change as the message formats aren't guaranteed to be stable. Let's just ensure that the manual has a note that the error code ER_DUP_ENTRY uses the format message of ER_DUP_ENTRY_WITH_KEY_NAME.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Adding this to ER_DUP_ENTRY description:

As of MySQL 5.1.20, the message returned with this error uses the
format string for ER_DUP_ENTRY_WITH_KEY_NAME.