MySQL Bugs: #15095: Duplicates Error

Bug #15095	Duplicates Error
Submitted:	21 Nov 2005 11:53	Modified:	22 Nov 2005 17:21
Reporter:	hello world	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0	OS:	Windows (Winxp)
Assigned to:		CPU Architecture:	Any

Description:
all records are generally unique. while applying those record to a unique index table , so many records are removed.

How to repeat:

CREATE TABLE IF NOT EXISTS CDR 
(
A varchar(100) NOT NULL default '',
B varchar(100) NOT NULL default '',
CD varchar(100) NOT NULL default '',
EF varchar(100) NOT NULL default '',
GH varchar(100) NOT NULL default '',
IJ varchar(100) NOT NULL default '',
KL varchar(100) NOT NULL default '',
Mn varchar(100) NOT NULL default '',
Jer varchar(100) NOT NULL default '',
agentid varchar(100) NOT NULL default '',
compid varchar(100) NOT NULL default '',
date varchar(100) NOT NULL default '',
time varchar(100) NOT NULL default '',
pin varchar(100) NOT NULL default '',
duration varchar(100) NOT NULL default '',
blegzone varchar(100) NOT NULL default '',
Otor varchar(100) NOT NULL default '',
Bft varchar(100) NOT NULL default '',
CrIn varchar(100) NOT NULL default '',
Lin varchar(100) NOT NULL default '',
Ce varchar(100) NOT NULL default '',
T varchar(100) NOT NULL default '',
ADr varchar(100) NOT NULL default '',
Calck varchar(100) NOT NULL default '',
AbyLe varchar(100) NOT NULL default '',
Bbne varchar(100) NOT NULL default '',
Oute varchar(100) NOT NULL default '',
Diigin varchar(100) NOT NULL default '',
Pipe varchar(100) NOT NULL default '',
cdrid varchar(100) NOT NULL default '',
OutdsdNo varchar(100) NOT NULL default '',
Pisdfdsfsdde varchar(100) NOT NULL default '',
Indaffsdate varchar(100) NOT NULL default '',
Insdfdasfost varchar(100) NOT NULL default '',
Csdffsde varchar(100) NOT NULL default '',
Bsdfwerwcee varchar(100) NOT NULL default '',
Bmat varchar(100) NOT NULL default '',
Beading varchar(100) NOT NULL default '',
Bsdfaelay varchar(100) NOT NULL default '',
Cae varchar(100) NOT NULL default '',
BLd varchar(100) NOT NULL default '',
Ane varchar(100) NOT NULL default '',
ALd varchar(100) NOT NULL default '',
Be varchar(100) NOT NULL default '',
BLId varchar(100) NOT NULL default '',
AIsdfqew varchar(5) NOT NULL default '',
ClIdewrwe varchar(100) NOT NULL default '',
BhewrewrId varchar(100) NOT NULL default '',
Caewrewy varchar(100) NOT NULL default '',
Carewrwe varchar(100) NOT NULL default '',
Extsdfe varchar(100) NOT NULL default '',
Vasdfsdfsde varchar(100) NOT NULL default '',
ID Bigint not null auto_increment,
Index aid_cid (Agentid,Compid), index d_d (Date,Duration), index bzone (Blegzone),
index date (Date), index date_cid (Date,Compid), index aid (Agentid), Index date_time(Date,Time),
Primary Key (ID)
) TYPE=MyIsam;

insert unique 30405 records  and then follow ..... as following..

mysql> select count(compid) from cdr where cdrid='20051116';
+---------------+
| count(compid) |
+---------------+
|         30405 |
+---------------+
1 row in set (0.27 sec)

mysql> Alter ignore table cdr add unique index (a,b,cd,ef,gh,ij,kl);
Query OK, 120608 rows affected (25.78 sec)
Records: 120608  Duplicates: 2557  Warnings: 0

mysql> select count(compid) from cdr where cdrid='20051116';
+---------------+
| count(compid) |
+---------------+
|         29564 |
+---------------+
1 row in set (0.34 sec)

mysql>

Suggested fix:
cant apply unique index and trust on them.

what exactly are you trying to accomplish here?

as far as i understand the rows in your table are all unique
and you have 30405 rows with cdrid=20051116

now you add an unique index on the first 7 fields a,b,cd,ef,gh,ij,kl
and as you chose to use ALTER IGNORE all rows with duplicate
values *in this group of fields* are removed, 
which is expected and documented behavior:

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

  IGNORE is a MySQL extension to standard SQL. It controls how 
  ALTER TABLE works if there are duplicates on unique keys in the 
  new table or if warnings occur when STRICT mode is enabled. 
  If IGNORE is not specified, the copy is aborted and rolled back
  if duplicate-key errors occur. If IGNORE is specified, then for rows
  with duplicates on a unique key, only the first row is used. 
  The others conflicting rows are deleted. Wrong values are 
  truncated to the closest matching acceptable value.

thinking a call records, the file has 30405  rows , all are 100% unique rows with call records.

example: Date, Time, Pin, Destno = in a specific time , you are calling from a telephone number to a destination number.

now i have a unique (date,time,pin,destno) index. after inserting all i see 29564 rows inserted. 

doesnt it mean mysql unique (date,time,pin,destno)   not working for me ? 

i hope you understand.

/**************************
You are right, this is not bug.
/**************************

i am sorry, this is my fault, i have to find the unique combination from that rows.

by the way how many fields i can make uniqe ? maximum

Closed because it is not a bug, but a reprter fault.

As for "how many fields i can make uniqe ? maximum", please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html, for example). You need unique index, and index may have up to 15 columns. But there are some total key lenth limitations also.