Problems using Osmosis and MySql

Jon_Hill · May 18, 2009, 8:35am

Hi

I’m experimenting with loading planet data into a local database using Osmosis and am having some problems.

My source xml file was downloaded from Cloudmade (united_kingdom.osm) and the root element in the file is “”

I am using MySQL and obtained the schema from http://gweb.bretth.com/apidb06-mysql-latest.sql

First I tried to run Osmosis 0.31.1 against this schema with:
**
bin/osmosis --read-xml-0.6 file=“…/united_kingdom.osm” --write-mysql-0.6 populateCurrentTables=yes host=“localhost” database=“api06_test” user=“root” password=“root” validateSchemaVersion=no **

I get the Exception of org.openstreetmap.osmosis.core.OsmosisRuntimeException: Task type write-mysql-0.6 doesn’t exist

The same command run against Osmosis 0.30.3 gets me much further but eventually fails with a constraint error - stack trace below:

Does anyone have any ideas of where I might be going wrong?

All the best to you.

Jon

SEVERE: Thread for task 1-read-xml-0.6 failed
com.bretth.osmosis.core.OsmosisRuntimeException: Unable to load current way nodes.
at com.bretth.osmosis.core.mysql.v0_6.MysqlWriter.complete(MysqlWriter.java:1032)
at com.bretth.osmosis.core.xml.v0_6.XmlReader.run(XmlReader.java:111)
at java.lang.Thread.run(Thread.java:619)
Caused by: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (api06_test/current_way_nodes, CONSTRAINT current_way_nodes_ibfk_2 FOREIGN KEY (node_id) REFERENCES current_nodes (id))
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3256)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1313)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:874)
at com.bretth.osmosis.core.mysql.v0_6.MysqlWriter.complete(MysqlWriter.java:1029)
… 2 more

tholder · June 10, 2009, 12:23pm

Hi Jon,

Did you find a solution to this? I’m also stuck. I’m using Osmosis 0.31 with the following:

bin/osmosis --read-xml file=~/documents/osm/england.osm --write-mysql host=“server” database=“osm” user=“root” password=“pass”

I get the following:

Jun 10, 2009 1:17:01 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.31
Jun 10, 2009 1:17:01 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Jun 10, 2009 1:17:01 PM org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Task type write-mysql doesn’t exist.
at org.openstreetmap.osmosis.core.pipeline.common.TaskManagerFactoryRegister.getInstance(TaskManagerFactoryRegister.java:60)
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.buildTasks(Pipeline.java:50)
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.prepare(Pipeline.java:112)
at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:79)
at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:30)

I haven’t been able to track down v 0.30.3 yet as bretth.com server seems to be over bandwidth limit!

Thanks
Tom

tkappen · June 11, 2009, 11:47am

Hi!

Try the task --write-mysql-0.5. It seems that the mysql tasks has been renamed.

Cheers,
Thomas

Ezhick · July 2, 2009, 7:32pm

Got the same error, changing to --write-mysql-0.5 helps, but I get an other error - Task 3-bounding-box does not support data provided by default pipe stored at level 1 in the default pipe stack.

And when I use -=write-xml everything works fine. Whats wrong with osmosis???

Richard · July 3, 2009, 9:31am

Is --write-apidb what you’re looking for?

Ezhick · July 6, 2009, 11:55am

Yes, it is it. Thanx. But I have a new problem. I successfully imported planet.osm in mysql with osmosis and I’m trying to make updates work. And this is what I get (the bbox is the same as during import):

E:\osmosis\bin>osmosis -v  --read-change-interval work  --bounding-box left=19.43 right=180 top=83 bottom=41 --write-apidb-change dbType="mysql" user="osm"  database="osm" forceUtf8=yes validateSchemaVersion=no

06.07.2009 15:29:55 org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.31.1
06.07.2009 15:29:55 org.openstreetmap.osmosis.core.TaskRegistrar loadJPFPlugins
FINE: Searching for JPF plugins.
06.07.2009 15:29:55 org.openstreetmap.osmosis.core.TaskRegistrar loadJPFPlugins
FINE: Registering the core plugin.
06.07.2009 15:29:55 org.openstreetmap.osmosis.core.TaskRegistrar loadJPFPlugins
FINE: Registering the extension plugins.
06.07.2009 15:29:55 org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
06.07.2009 15:29:55 org.openstreetmap.osmosis.core.pipeline.common.Pipeline prep
are
FINE: Building tasks.
06.07.2009 15:29:55 org.openstreetmap.osmosis.core.pipeline.common.Pipeline buil
dTasks
FINE: Created task "1-read-change-interval"
06.07.2009 15:29:55 org.openstreetmap.osmosis.core.pipeline.common.Pipeline buil
dTasks
FINE: Created task "2-bounding-box"
06.07.2009 15:29:55 org.openstreetmap.osmosis.core.pipeline.common.Pipeline buil
dTasks
FINE: Created task "3-write-apidb-change"
06.07.2009 15:29:55 org.openstreetmap.osmosis.core.pipeline.common.Pipeline prep
are
FINE: Connecting tasks.
06.07.2009 15:29:55 org.openstreetmap.osmosis.core.pipeline.common.PipeTasks put
Task
FINE: Task "1-read-change-interval" produced unnamed pipe stored at level 1
in the default pipe stack.
06.07.2009 15:29:55 org.openstreetmap.osmosis.core.pipeline.common.Pipeline conn
ectTasks
FINE: Connected task "1-read-change-interval"
06.07.2009 15:29:55 org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Task 2-bounding-box
does not support data provided by default pipe stored at level 1 in the default
pipe stack.
        at org.openstreetmap.osmosis.core.pipeline.common.PipeTasks.retrieveTask
(PipeTasks.java:157)
        at org.openstreetmap.osmosis.core.pipeline.common.TaskManager.getInputTa
sk(TaskManager.java:165)
        at org.openstreetmap.osmosis.core.pipeline.v0_6.SinkSourceManager.connec
t(SinkSourceManager.java:51)
        at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.connectTasks(
Pipeline.java:74)
        at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.prepare(Pipel
ine.java:116)
        at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:79)
        at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:30)

What’s the problem?

Ezhick · July 6, 2009, 3:00pm

Problem understood. Osmosis does not support cutting changes by bounding-polygon or bounding-box…

Kyle_Polich · July 26, 2009, 5:28am

I’m having a similar problem I am hoping someone can help me with. I am trying to run osmosis two different ways (based on some solutions that seem to work for other people suggested in the forums) but each is giving me an different error. Can anyone please help me sort this out?

Thanks in advance.

METHOD #1:

$ osmosis --read-xml file=“/home/kyle/osm/planet-090617.osm” --write-apidb dbtype=“mysql” host=“localhost” database=“osm” user=“user” password=“pass”
Jul 25, 2009 10:25:10 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.31.1
Jul 25, 2009 10:25:10 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Jul 25, 2009 10:25:10 PM org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Argument dbtype for task 2-write-apidb was not recognised.
at org.openstreetmap.osmosis.core.pipeline.common.TaskManagerFactory.createTaskManager(TaskManagerFactory.java:64)
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.buildTasks(Pipeline.java:50)
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.prepare(Pipeline.java:112)
at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:79)
at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:30)

METHOD #2:

$ osmosis --read-xml file=“/home/kyle/osm/planet-090617.osm” --write-mysql-0.5 host=“localhost” database=“osm” user=“user” password=“pass”
Jul 25, 2009 10:25:24 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.31.1
Jul 25, 2009 10:25:24 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Jul 25, 2009 10:25:24 PM org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Task 2-write-mysql-0.5 does not support data provided by default pipe stored at level 1 in the default pipe stack.
at org.openstreetmap.osmosis.core.pipeline.common.PipeTasks.retrieveTask(PipeTasks.java:157)
at org.openstreetmap.osmosis.core.pipeline.common.TaskManager.getInputTask(TaskManager.java:165)
at org.openstreetmap.osmosis.core.pipeline.v0_5.SinkManager.connect(SinkManager.java:50)
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.connectTasks(Pipeline.java:74)
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.prepare(Pipeline.java:116)
at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:79)
at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:30)

DJSTroky · August 12, 2009, 4:29am

I was having these problems too and eventually gave up on Osmosis. I’m a big noob on all this stuff, but I just couldn’t seem to get PostgreSQL installed properly and when I thought I did, Osmosis didn’t seem to work either.

If it helps anyone, I wrote a python script to import an osm file into a mysql db (stuff that I know how to make work). All you experienced developers are welcome to bash on me, since I’m sure this code won’t properly import everything (namely UTF-8 characters and perhaps other stuff as well) and it seems to be pretty slow compared to some performance statistics about Osmosis (when it was working for them of course).

http://www.evansiroky.com/files/import_osm2-web%20friendly.py

I’ll probably remove the link to this file eventually, FYI.

delta_foxtrot2 · August 12, 2009, 4:39am

I did the same thing, but in php

the_nerdling · August 24, 2009, 9:39am

I can report the same error for Method 1, and a diferent error for Method 2, as Kyle Polich - see below. My setup is Ubuntu 8.04 and mysql 5. Advice appreciated.

I’ve been able to import to PostGres using the osm2pgrouting tool, and I’m tempted port from PostGres to mysql.

METHOD #1:

bin/osmosis --read-xml file=“/root/map.osm” --write-apidb dbtype=“mysql” host=“localhost” database=“osmdummy” user=“root” password=“…”

24-Aug-09 10:10:03 org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.31
24-Aug-09 10:10:04 org.java.plugin.registry.xml.ManifestParser
INFO: got SAX parser factory - gnu.xml.stream.SAXParserFactory@26b330
24-Aug-09 10:10:04 org.java.plugin.registry.xml.PluginRegistryImpl configure
INFO: configured, stopOnError=false, isValidating=true
24-Aug-09 10:10:14 org.java.plugin.registry.xml.PluginRegistryImpl register
INFO: plug-in and fragment descriptors registered - 1
24-Aug-09 10:10:14 org.java.plugin.standard.StandardPluginManager activatePlugin
INFO: plug-in started - org.openstreetmap.osmosis.core.plugin.Core@0.31.0
24-Aug-09 10:10:14 org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
24-Aug-09 10:10:14 org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Argument dbtype for task 2-write-apidb was not recognised.
at org.openstreetmap.osmosis.core.pipeline.common.TaskManagerFactory.createTaskManager(TaskManagerFactory.java:64)
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.buildTasks(Pipeline.java:50)
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.prepare(Pipeline.java:112)
at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:79)
at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:30)

METHOD #2:

bin/osmosis --read-xml file=“/root/map.osm” --write-mysql-0.5 host=“localhost” osmdummy=“osm” user=“root” password=“…”

24-Aug-09 10:11:11 org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.31
24-Aug-09 10:11:12 org.java.plugin.registry.xml.ManifestParser
INFO: got SAX parser factory - gnu.xml.stream.SAXParserFactory@7853d8
24-Aug-09 10:11:12 org.java.plugin.registry.xml.PluginRegistryImpl configure
INFO: configured, stopOnError=false, isValidating=true
24-Aug-09 10:11:21 org.java.plugin.registry.xml.PluginRegistryImpl register
INFO: plug-in and fragment descriptors registered - 1
24-Aug-09 10:11:21 org.java.plugin.standard.StandardPluginManager activatePlugin
INFO: plug-in started - org.openstreetmap.osmosis.core.plugin.Core@0.31.0
24-Aug-09 10:11:21 org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
24-Aug-09 10:11:21 org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
java.lang.IllegalArgumentException
at java.util.regex.Pattern.compile(libgcj.so.81)
at org.openstreetmap.osmosis.core.mysql.v0_5.impl.EmbeddedTagProcessor.(EmbeddedTagProcessor.java:33)
at org.openstreetmap.osmosis.core.mysql.v0_5.MysqlWriter.(MysqlWriter.java:303)
at org.openstreetmap.osmosis.core.mysql.v0_5.MysqlWriterFactory.createTaskManagerImpl(MysqlWriterFactory.java:41)
at org.openstreetmap.osmosis.core.pipeline.common.TaskManagerFactory.createTaskManager(TaskManagerFactory.java:60)
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.buildTasks(Pipeline.java:50)
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.prepare(Pipeline.java:112)
at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:79)
at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:30)

softeis · November 22, 2009, 12:12am

I found this:
Yep, the dbType argument isn’t supported in 0.31. You may be able to workaround it by specifying the authFile argument and including the dbType parameter in that file but I haven’t tested it out.

It is fixed in the nightly builds. It was fixed in revision 15078. Version 0.31 is revision 14929.

You can download a nightly build which should fix your problem:
http://www.bretth.com/osmosis/osmosis-0.31.1-bin.zip

or find one on the OSM osmosis pages

Kyle Polich:

I’m having a similar problem I am hoping someone can help me with. I am trying to run osmosis two different ways (based on some solutions that seem to work for other people suggested in the forums) but each is giving me an different error. Can anyone please help me sort this out?

Thanks in advance.

METHOD #1:

$ osmosis --read-xml file=“/home/kyle/osm/planet-090617.osm” --write-apidb dbtype=“mysql” host=“localhost” database=“osm” user=“user” password=“pass”
Jul 25, 2009 10:25:10 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.31.1
Jul 25, 2009 10:25:10 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Jul 25, 2009 10:25:10 PM org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Argument dbtype for task 2-write-apidb was not recognised.
at org.openstreetmap.osmosis.core.pipeline.common.TaskManagerFactory.createTaskManager(TaskManagerFactory.java:64)
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.buildTasks(Pipeline.java:50)
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.prepare(Pipeline.java:112)
at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:79)
at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:30)

METHOD #2:

$ osmosis --read-xml file=“/home/kyle/osm/planet-090617.osm” --write-mysql-0.5 host=“localhost” database=“osm” user=“user” password=“pass”
Jul 25, 2009 10:25:24 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.31.1
Jul 25, 2009 10:25:24 PM org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
Jul 25, 2009 10:25:24 PM org.openstreetmap.osmosis.core.Osmosis main
SEVERE: Execution aborted.
org.openstreetmap.osmosis.core.OsmosisRuntimeException: Task 2-write-mysql-0.5 does not support data provided by default pipe stored at level 1 in the default pipe stack.
at org.openstreetmap.osmosis.core.pipeline.common.PipeTasks.retrieveTask(PipeTasks.java:157)
at org.openstreetmap.osmosis.core.pipeline.common.TaskManager.getInputTask(TaskManager.java:165)
at org.openstreetmap.osmosis.core.pipeline.v0_5.SinkManager.connect(SinkManager.java:50)
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.connectTasks(Pipeline.java:74)
at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.prepare(Pipeline.java:116)
at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:79)
at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:30)