Bug #10109 INSERT .. SELECT ... ON DUPLICATE KAY UPDATE fails
Submitted: 23 Apr 2005 7:44 Modified: 10 Aug 2005 20:53
Reporter: Rudenko Ilya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.1.11-log OS:Linux (Fedora Core 3)
Assigned to: Antony Curtis CPU Architecture:Any

[23 Apr 2005 7: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 19: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 15: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 14: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 15: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 21: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 21: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 2: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 19:44] Antony Curtis 
pushed fix to 4.1.14 and 5.0.12
[10 Aug 2005 20:53] Paul DuBois 
Noted in 4.1.14, 5.0.12 changelogs.
[11 Aug 2005 20:26] Jeff C 
Thank you so much for this bugfix.  Well done.