[osmosis-dev] node_tags duplicate key with MySQL varbinary - fixed using Blackhole and trigger

[Simon Nuttall]

Picking up my earlier question at:
http://lists.openstreetmap.org/pipermail/osmosis-dev/2011-July/001092.html

On 22 July 2011 08:22, Shaun McDonald <shaun at shaunmcdonald.me.uk> wrote:
> It is a problem with trailing spaces in MySQL VARCHAR: http://sql-info.de/mysql/gotchas.html#1_6

Yeah, trailing spaces and character sets have got me here, and my
query about an idea to use INSERT IGNORE to fill the the node_tags
table was not picked up, but I think I've got a way through now.

I can define the node_tags table to use the BLACKHOLE engine, and
create a before trigger to fill my own version of the table:

CREATE TABLE  `node_tags` (
  `id` bigint(64) NOT NULL,
  `version` bigint(20) NOT NULL,
-- Changing this to varchar(255) binary wasn't enough to fix the
problem with a boutique/clothes shop somewhere in eastern Europe.
  `k` varchar(255) NOT NULL default '',
  `v` varchar(255) NOT NULL default '',
-- Removed `version`, from this index as we only deal with the latest.
-- Because OSM allows duplicated key names this may need to be
downgraded from a PRIMARY KEY to just KEY, this gets fixed up during
import.
   KEY `id_k` (`id`,`k`)
--  CONSTRAINT `node_tags_ibfk_1` FOREIGN KEY (`id`, `version`)
REFERENCES `nodes` (`id`, `version`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8;


-- Create the structure of the nodeTag similar to the node_tags table
but with fewer fields and a primary key on id,k.;
CREATE TABLE `nodeTag` (
     `id` bigint(64) NOT NULL DEFAULT '0',
     `k` varchar(255) NOT NULL,
     `v` varchar(255) NOT NULL,
     PRIMARY KEY (`id`,`k`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- A trigger that diverts node_tags insertions into nodeTag
drop trigger if exists insert_node_tags;
delimiter //
create trigger insert_node_tags before insert on node_tags
for each row
begin
	insert ignore nodeTag (id, k, v) values (new.id, new.k, new.v);
end;//
delimiter ;


I'm yet to discover how efficient this will be.