MySQL Bugs: #58602: Constraint violation for duplicate entries

Bug #58602	Constraint violation for duplicate entries
Submitted:	30 Nov 2010 18:43	Modified:	1 Jan 2011 18:20
Reporter:	Tatiana Schevchenko	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I am new to mysql environment while executing a demo program i encountered the following ambiguity 

I mark a column as unique although i enter the same name twice. Now when i use the groupby clause mysql does not recognize the rows as similar and executes normally. 

I assign a table name say for example nam_dir. One row in the nam_dir table is "zandu". when i see the output using mysql> show create table nam_dir i get two different rows one with "zandu" and the other with zandu and a square in the end instead of a ?.

This cause several problems including the fact that I can't export and reimport the database. On reimporting an error mentions that a Insert has failed because it violates a constraint.

Is there a way to list all the anomalies or force the database to recheck constraints (and list all the values/rows that go against them) ?

How to repeat:
no test case identified yet

Suggested fix:
I could import the database lookout for the first error fix it and repeat the procedure for all the errors that pop up.

You are able to dump and import if the DUMP-file has statements like

SET FOREIGN_KEY_CHECKS = 0
SET UNIQUE_CHECKS = 0

Also you may create a CONSTRAINT violation if the session has set the SESSION variable(s) FOREIGN_KEY_CHECKS|SET UNIQUE_CHECKS = 0.  I don't see how else you could.

But not sure what GROUP BY should do or not do in such case!

Peter 
(not a MySQL person)

Not so simple!  I tried to create a UNIQUE violation setting UNIQUE_CHECKS = 0, but MySQL 5.1.53 won't let me (with any storage engine!).  And FOREIGN_KEY_CHECKS is irrelevant here.  My mistake.

I think you are encountering an issue with a server version (4.1) not supported any more.

if the two entries in the table are not the same then how do you expect mysql should consider it as a constraint violation?

They are the same and i reconfirmed it when i export and import the data

Thank you for the report.

Please send us output of SHOW CREATE TABLE and query you use.

Hi Tatiana,

I think you should first determine what exactly the special character is and try eliminating it and re run the query.

Back to Need feedback for Sveta's question.

msql> CREATE TABLE nam_dir
   -> (
   -> cus_name VARCHAR(60),
   -> ph_no INT
   -> );

*************************************
insert into nam_dir values('zandu',123);
insert into nam_dir values('zandu',123);
insert into nam_dir values('pepe',456);
insert into nam_dir values('svetlana',789);
insert into nam_dir values('ruska',654);
**************************************

mysql> select * from nam_dir;

+----------+-----------+
| cus_name | ph_no     |      
|          |           |                 
+----------+-----------+
|zandu     | 123       |
| 
|zandu[]   | 123       |
|
|pepe      | 456       |
|
|svetlana  | 789       |
|
|ruska     | 654       |
+----------+-----------+

5 rows in set (0.03 sec)

Have you checked to ensure that there is no white space in your column values?

No white spaces

I suggest you must create a dummy table and use a DISTINCT clause on the dummy table and using inner joins delete the entries from the original table i.e nam_dir

Let me try and post the updates

I can suggest you the following pseudocode. Hope this helps 

1. select * from nam_dir;

2. create a table called duplicate which is similar to nam_dir  
    
3. form the inner join of nam_dir and duplicate and delete the duplicate entries

4. insert single record of each duplicate entry into nam_dir

5. display the table

Please paste output from:

SELECT version();

Thank you for the feedback.

I see nor UNIQUE constraint, neither GROUP BY in data you provided. So this is rather not a bug for me unless you provide real query.

Apologies for erroneous display

mysql> CREATE TABLE nam_dir
    -> (
    -> cus_name VARCHAR(60),
    -> ph_no INT,
    -> primary key(cus_name)
    -> );

*************************************
insert into nam_dir values('zandu',123);
insert into nam_dir values('zandu',123);
insert into nam_dir values('pepe',456);
insert into nam_dir values('svetlana',789);
insert into nam_dir values('ruska',654);
**************************************

mysql> select * from nam_dir groupby cus_name;

+----------+-----------+
| cus_name | ph_no     |      
|          |           |                 
+----------+-----------+
|zandu     | 123       |
| 
|zandu[]   | 123       |
|
|pepe      | 456       |
|
|svetlana  | 789       |
|
|ruska     | 654       |
+----------+-----------+

5 rows in set (0.03 sec)

This statement was also tried and it resulted in the same table posted above.

mysql> select UNIQUE cus_name, ph_no from nam_dir groupby cus_name;

Thank you for the report.

I can not repeat described behavior and you surely have "zandu[]" in your table. Please send output of SELECT HEX(cus_name) FROM nam_dir

I agree completely with Sveta

SELECT HEX(cus_name) FROM nam_dir WHERE cus_name LIKE 'Zandu%'

I guess you will be able to determine the special character(#) proceeding 
zandu

then trim the character from the table using the following

UPDATE nam_dir SET cus_name=TRIM(TRAILING '#' FROM cus_name);

using this i guess you will be in a position to determine the special character and at the same time delete it

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".