MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Yang Zhang Date:February 21 2010 3:14am
Subject:Duplicate entries despite group by
View as plain text  
I have the following table:

CREATE TABLE `graph` (
  `tableid1` varchar(20) NOT NULL,
  `tupleid1` int(11) NOT NULL,
  `tableid2` varchar(20) NOT NULL,
  `tupleid2` int(11) NOT NULL,
  `node1` int(11) NOT NULL,
  `node2` int(11) NOT NULL,
  `weight` int(10) NOT NULL,
  PRIMARY KEY (`tableid1`,`tupleid1`,`tableid2`,`tupleid2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

and I'm running this query (note the 'group by'):

        insert into graph (node1, node2, tableid1, tupleid1, tableid2,
tupleid2, weight)
        select 0, 0, a.tableid, a.tupleid, b.tableid, b.tupleid, count(*)
        from transactionlog a, transactionlog b
        where a.transactionid = b.transactionid
          and (a.tableid, a.tupleid) {'<'} (b.tableid, b.tupleid)
        group by a.tableid, a.tupleid, b.tableid, b.tupleid

However, after running for a few hours, the query fails with the
following error:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
Duplicate entry 'new_order-248642-order_line-13126643' for key
'group_key'

How is this possible? There were no concurrently running queries
inserting into 'graph'. I'm using mysql-5.4.3; is this a beta
bug/anyone else happen to know something about this? Thanks in
advance.
--
Yang Zhang
http://www.mit.edu/~y_z/
Thread
Duplicate entries despite group byYang Zhang21 Feb
  • Re: Duplicate entries despite group byCarsten Pedersen21 Feb