Description:
if you insert into federated table "on duplicate key update" clause doesn't work and MySQL returns
ERROR 1022 (23000): Can't write; duplicate key in table 'gt_places'
How to repeat:
CREATE TABLE `gt_places` (
`dt` date NOT NULL,
`place_id` int(10) unsigned NOT NULL,
`cnt_exposure` int(10) NOT NULL,
`cnt_load` int(10) NOT NULL,
`cnt_click` int(10) NOT NULL,
`amt_money` double NOT NULL,
`event_type` enum('exp','click') NOT NULL DEFAULT 'click',
PRIMARY KEY (`dt`,`place_id`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='test/gtd_places';
drop temporary table if exists __places;
create temporary table __places (
`dt` date not null,
`place_id` int(10) unsigned not null,
`cnt_exposure` int(10) unsigned not null default '0',
`cnt_load` int(10) unsigned not null default '0',
`cnt_click` int(10) unsigned not null default '0',
`amt_money` double(11,4) unsigned not null default '0.0000',
`amt_money_conv` double(11,4) unsigned not null default '0.0000',
`event_type` enum('exp','click') not null default 'click',
primary key (`dt`, `place_id`)
) engine=innodb default charset=utf8;
insert into __places values
('2012-08-09', 4, 5, 5, 5, 333, 544, 'click');
insert into gt_places (dt, place_id, cnt_exposure, cnt_load, cnt_click, amt_money, event_type)
select
dt,
place_id,
cnt_exposure,
cnt_load,
cnt_click,
amt_money,
event_type
from
__places
on duplicate key update
cnt_exposure = values(cnt_exposure),
cnt_load = values(cnt_load),
cnt_click = values(cnt_click),
amt_money = values(amt_money)
;
Description: if you insert into federated table "on duplicate key update" clause doesn't work and MySQL returns ERROR 1022 (23000): Can't write; duplicate key in table 'gt_places' How to repeat: CREATE TABLE `gt_places` ( `dt` date NOT NULL, `place_id` int(10) unsigned NOT NULL, `cnt_exposure` int(10) NOT NULL, `cnt_load` int(10) NOT NULL, `cnt_click` int(10) NOT NULL, `amt_money` double NOT NULL, `event_type` enum('exp','click') NOT NULL DEFAULT 'click', PRIMARY KEY (`dt`,`place_id`) ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='test/gtd_places'; drop temporary table if exists __places; create temporary table __places ( `dt` date not null, `place_id` int(10) unsigned not null, `cnt_exposure` int(10) unsigned not null default '0', `cnt_load` int(10) unsigned not null default '0', `cnt_click` int(10) unsigned not null default '0', `amt_money` double(11,4) unsigned not null default '0.0000', `amt_money_conv` double(11,4) unsigned not null default '0.0000', `event_type` enum('exp','click') not null default 'click', primary key (`dt`, `place_id`) ) engine=innodb default charset=utf8; insert into __places values ('2012-08-09', 4, 5, 5, 5, 333, 544, 'click'); insert into gt_places (dt, place_id, cnt_exposure, cnt_load, cnt_click, amt_money, event_type) select dt, place_id, cnt_exposure, cnt_load, cnt_click, amt_money, event_type from __places on duplicate key update cnt_exposure = values(cnt_exposure), cnt_load = values(cnt_load), cnt_click = values(cnt_click), amt_money = values(amt_money) ;