0

When I run this below queries, I get Duplicate Key error on MySQL version 5.6.31-log

CREATE TABLE `calls_outbound_temp` (
  `uniquecallid` double NOT NULL,
  `extn` int(11) DEFAULT NULL,
  `disposition` varchar(50) DEFAULT NULL,
  `src` varchar(100) DEFAULT NULL,
  `dst` varchar(100) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `calldate` datetime DEFAULT NULL,
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uniquecallid` (`uniquecallid`,`extn`,`calldate`),
  KEY `idx_extn` (`extn`),
  KEY `idx_disposition` (`disposition`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO calls_outbound_temp (uniquecallid,extn,disposition,src,dst,duration,calldate) VALUES
("1593582027.3368257","7180","ANSWERED","7180","7247","8","2020-07-01 11:10:27"),
("1593582027.3368255","7180","NO ANSWER","7180","7247","9","2020-07-01 11:10:27");

Exact error:

ERROR 1062 (23000): Duplicate entry '1593582027.3368256-7180-2020-07-01 11:10:27' for key 'uniquecallid'

I am not sure how to troubleshoot further. uniquecallid clearly is different and are not duplicate. Not sure why uniquecallid is considered as 1593582027.3368256

We can even regenerate this issue on http://sqlfiddle.com/, just copy-paste above snippet in MySQL 5.6.

Jigar
  • 3,256
  • 1
  • 30
  • 51
  • 1
    Does this answer your question? [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) The discussion of numeric strings vs actual numbers here is also relevant: https://stackoverflow.com/a/63552038/157957 – IMSoP Sep 01 '20 at 15:46
  • Thanks. Makes sense. – Jigar Sep 01 '20 at 15:50
  • Should not check for uniqueness on column with datatype double. This is design issue. https://forums.mysql.com/read.php?39,91219,91240#msg-91240 – Jigar Sep 02 '20 at 04:34

0 Answers0