Bug #10109 INSERT .. SELECT ... ON DUPLICATE KAY UPDATE fails
Submitted: 23 Apr 2005 9:44 Modified: 10 Aug 2005 22:53
Reporter: Rudenko Ilya
Status: Closed
Category:Server: MyISAM Severity:S2 (Serious)
Version:4.1.11-log OS:Linux (Fedora Core 3)
Assigned to: Bugs System Target Version:

[23 Apr 2005 9:44] Rudenko Ilya 
Description:
Executing query fails with error:

#1110 - Column 'bytes_out' specified twice 

How to repeat:
CREATE TABLE `stat_traffic_ip` (
  `ip` bigint(20) NOT NULL default '0',
  `dtime` datetime NOT NULL default '0000-00-00 00:00:00',
  `bytes_out` bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ip`,`dtime`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_ukrainian_ci;

CREATE TABLE `tmp_access_log` (
  `ip` int(11) NOT NULL default '0',
  `dtime` datetime NOT NULL default '0000-00-00 00:00:00',
  `response` smallint(5) unsigned NOT NULL default '0',
  `bytes_out` bigint(20) unsigned NOT NULL default '0',
  `url` text NOT NULL,
  `referer` text NOT NULL,
  KEY `ip` (`ip`),
  KEY `dtime` (`dtime`)
) TYPE=MyISAM ;

INSERT INTO stat_traffic_ip( `ip` , `dtime` , `bytes_out` ) 
SELECT ip, DATE_FORMAT( dtime, '%Y-%m-%d %H:00:00' ) , SUM( bytes_out ) 
FROM tmp_access_log
WHERE bytes_out >0
AND response !=404
GROUP BY ip, dtime ON DUPLICATE
KEY UPDATE bytes_out = bytes_out + VALUES (bytes_out) ;
[27 Jul 2005 21:47] Jeff C 
I would love to see this bug corrected.  The docs say you can do ... insert .. select ..
on duplicate key update, however it always errors out with duplicate source.

I've tested the other formats and they work correctly.

Thanks,
Jeff
[28 Jul 2005 17:34] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27682
[4 Aug 2005 16:26] Jeff C 
Can we get this in 4.1.14 ?  I'd love to have this resolved as this feature is very
important.

Thanks,
Jeff
[5 Aug 2005 17:46] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27929
[5 Aug 2005 23:51] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27941
[5 Aug 2005 23:51] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27942
[7 Aug 2005 4:16] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27964
[10 Aug 2005 21:44] Antony Curtis 
pushed fix to 4.1.14 and 5.0.12
[10 Aug 2005 22:53] Paul DuBois 
Noted in 4.1.14, 5.0.12 changelogs.
[11 Aug 2005 22:26] Jeff C 
Thank you so much for this bugfix.  Well done.