[osmosis-dev] node_tags duplicate key with MySQL varbinary

[Shaun McDonald]

It is a problem with trailing spaces in MySQL VARCHAR: http://sql-info.de/mysql/gotchas.html#1_6

It would seem that upgrading MySQL may be an option, as MySQL5.0.3+ adhere to the standard and don't truncate the trailing spaces.
http://dev.mysql.com/doc/refman/5.5/en/char.html:

"For VARCHAR columns, trailing spaces in excess of the column length are truncated prior to insertion and a warning is generated, regardless of the SQL mode in use. For CHAR columns, truncation of excess trailing spaces from inserted values is performed silently regardless of the SQL mode.

VARCHAR values are not padded when they are stored. Trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL."

I however cannot find documentation to see if this applies to indexes and foreign keys too.

Shaun

On 20 Jul 2011, at 09:55, Simon Nuttall wrote:

> I'm using this line to fill a MySQL database of Europe:
> 
> osmosis -v 100 --read-bin europe.osm.pbf --tag-filter accept-ways
> highway=* cycleway=* access=* foot=* bicycle=* oneway=* --tag-filter
> reject-ways highway=motorway,motorway_link --buffer --write-apidb
> dbType=mysql populateCurrentTables=no database=planetExtractOSM110718
> validateSchemaVersion=no
> 
> 
> When the node_tags table had this definition for the 'k' field...
> 
> `k` varchar(255) binary NOT NULL default '',
> 
> ... it failed because version 4 of...
> 
> http://www.openstreetmap.org/browse/node/778149001
> 
> had a duplicated 'k' that had a trailing space (i.e. "shop" =
> "clothes" ... and "shop "= "boutique").
> 
> 
> So I changed the definition to use:
> 
>  `k` varbinary(255) NOT NULL default '',
> 
> but the problem I now hit (after about 28 hours of processing) is:
> 
> 
>    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
> Duplicate entry '1207174847-4-??????' for key 'PRIMARY'
>    Offending node:
>    http://www.openstreetmap.org/browse/node/1207174847
> 
> mysql> select `id`, `version`, `k`, `v` from node_tags where id=1207174847;
> +------------+---------+---------+-------------+
> | id         | version | k       | v           |
> +------------+---------+---------+-------------+
> | 1207174847 |       4 | ??????  | 20-?? ????? |
> | 1207174847 |       4 | amenity | grave_yard  |
> | 1207174847 |       4 | name    | 3?          |
> +------------+---------+---------+-------------+
> 3 rows in set (0.00 sec)
> 
> 
> I get the feeling that VARBINARY is the wrong solution and that
> VARCHAR BINARY is better, but if so how can I avoid hitting the
> trailing space issue again?
> 
> _______________________________________________
> osmosis-dev mailing list
> osmosis-dev at openstreetmap.org
> http://lists.openstreetmap.org/listinfo/osmosis-dev