You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

340 lines
18 KiB

  1. # PROBLEMS #
  2. There is no official support for MySQL, so there is no official database schema available.
  3. I found the old mysql schema on [Brett Henderson](https://github.com/brettch)'s Homepage. In order to be able to track the changes, I committed the latest schema to this repository and will make my changes to this file as I go along for happy diffing.
  4. $ mkdir osmosis-mysql
  5. $ cd osmosis-mysql
  6. $ touch README.md
  7. $ wget http://gweb.bretth.com/apidb06-mysql-latest.sql
  8. $ mv apidb06-mysql-latest.sql mysql-apidb06.sql
  9. $ git init
  10. $ git add mysql-apidb06.sql README.md
  11. $ git commit -m "Initial commit"
  12. Right now the database import fails as there have been changes to the schema that aren't reflected in this script.
  13. As a first goal it seems to me that we have to bring the schema inline with the JDBC statements executed by the `--write-apidb-0.6` target. The source code of which can be seen in [ApidbWriter.java](https://github.com/openstreetmap/osmosis/blob/master/apidb/src/main/java/org/openstreetmap/osmosis/apidb/v0_6/ApidbWriter.java).
  14. ## Task type write-mysql doesn't exist. ##
  15. I was tring to execute
  16. osmosis --read-xml file="bremen.osm.bz2" --write-mysql host="localhost" database="osm" user="root"
  17. I got
  18. SEVERE: Execution aborted.
  19. org.openstreetmap.osmosis.core.OsmosisRuntimeException: Task type write-mysql doesn't exist.
  20. at org.openstreetmap.osmosis.core.pipeline.common.TaskManagerFactoryRegister.getInstance(TaskManagerFactoryRegister.java:60)
  21. at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.buildTasks(Pipeline.java:50)
  22. at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.prepare(Pipeline.java:112)
  23. at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:86)
  24. at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:37)
  25. at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  26. at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
  27. at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  28. at java.lang.reflect.Method.invoke(Method.java:601)
  29. at org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:329)
  30. at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:239)
  31. at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:409)
  32. at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:352)
  33. at org.codehaus.classworlds.Launcher.main(Launcher.java:47)
  34. The [documentation](http://wiki.openstreetmap.org/wiki/Osmosis/Detailed_Usage) is weird. All goals listed are defaulting to a specific API version of OpenStreetMap, which is `0.6` for `osmosis` `0.4.1`. This version does not exist for the goal `--write-mysql` but is only available for `0.5`
  35. You have to use `write-apidb` goal instead.
  36. ## org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to create resultset. OR com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'osm.schema_migrations ##
  37. I tried
  38. osmosis --read-xml file="bremen.osm.bz2" --write-apidb-0.6 host="127.0.0.1" dbType="mysql" database="osm" user="osm" password="osm"
  39. and got
  40. org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to create resultset.
  41. at org.openstreetmap.osmosis.apidb.common.DatabaseContext.executeQuery(DatabaseContext.java:429)
  42. at org.openstreetmap.osmosis.apidb.v0_6.impl.SchemaVersionValidator.validateDBVersion(SchemaVersionValidator.java:82)
  43. at org.openstreetmap.osmosis.apidb.v0_6.impl.SchemaVersionValidator.validateVersion(SchemaVersionValidator.java:55)
  44. at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.initialize(ApidbWriter.java:324)
  45. at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.process(ApidbWriter.java:1089)
  46. at org.openstreetmap.osmosis.xml.v0_6.impl.NodeElementProcessor.end(NodeElementProcessor.java:139)
  47. at org.openstreetmap.osmosis.xml.v0_6.impl.OsmHandler.endElement(OsmHandler.java:107)
  48. at org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source)
  49. at org.apache.xerces.parsers.AbstractXMLDocumentParser.emptyElement(Unknown Source)
  50. at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanStartElement(Unknown Source)
  51. at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)
  52. at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
  53. at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
  54. at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
  55. at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
  56. at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
  57. at org.apache.xerces.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
  58. at org.apache.xerces.jaxp.SAXParserImpl.parse(Unknown Source)
  59. at javax.xml.parsers.SAXParser.parse(SAXParser.java:195)
  60. at org.openstreetmap.osmosis.xml.v0_6.XmlReader.run(XmlReader.java:111)
  61. at java.lang.Thread.run(Thread.java:722)
  62. Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'osm.schema_migrations' doesn't exist
  63. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  64. at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
  65. at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  66. at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
  67. at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
  68. at com.mysql.jdbc.Util.getInstance(Util.java:386)
  69. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
  70. at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
  71. at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
  72. at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
  73. at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
  74. at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
  75. at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
  76. at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1557)
  77. at org.openstreetmap.osmosis.apidb.common.DatabaseContext.executeQuery(DatabaseContext.java:424)
  78. ... 20 more
  79. The error message is Table `geo.schema_migrations`doesn't exist. This is a table that contains the version information of the schema. It is important that your schema is the one that osmosis is expecting.
  80. MySQL is no longer supported so there is no way to get a current schema for MySQL, on the other hand you would not need it for anything. You could pass `validateSchemaVersion=no` to osmosis to prevent it from checking the version, however that can cause an error in the process or the result.
  81. ## com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'osm.nodes' doesn't exist ##
  82. I executed
  83. osmosis --read-xml file="bremen.osm.bz2" --write-apidb-0.6 host="127.0.0.1" dbType="mysql" database="osm" user="osm" password="osm" validateSchemaVersion=no
  84. and got
  85. org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to execute statement.
  86. at org.openstreetmap.osmosis.apidb.common.DatabaseContext.executeStatement(DatabaseContext.java:330)
  87. at org.openstreetmap.osmosis.apidb.common.DatabaseContext.disableIndexes(DatabaseContext.java:208)
  88. at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.initialize(ApidbWriter.java:372)
  89. at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.process(ApidbWriter.java:1089)
  90. at org.openstreetmap.osmosis.xml.v0_6.impl.NodeElementProcessor.end(NodeElementProcessor.java:139)
  91. at org.openstreetmap.osmosis.xml.v0_6.impl.OsmHandler.endElement(OsmHandler.java:107)
  92. at org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source)
  93. at org.apache.xerces.parsers.AbstractXMLDocumentParser.emptyElement(Unknown Source)
  94. at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanStartElement(Unknown Source)
  95. at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)
  96. at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
  97. at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
  98. at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
  99. at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
  100. at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
  101. at org.apache.xerces.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
  102. at org.apache.xerces.jaxp.SAXParserImpl.parse(Unknown Source)
  103. at javax.xml.parsers.SAXParser.parse(SAXParser.java:195)
  104. at org.openstreetmap.osmosis.xml.v0_6.XmlReader.run(XmlReader.java:111)
  105. at java.lang.Thread.run(Thread.java:722)
  106. Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'osm.nodes' doesn't exist
  107. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  108. at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
  109. at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  110. at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
  111. at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
  112. at com.mysql.jdbc.Util.getInstance(Util.java:386)
  113. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
  114. at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
  115. at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
  116. at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
  117. at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
  118. at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
  119. at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
  120. at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:842)
  121. at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:681)
  122. at org.openstreetmap.osmosis.apidb.common.DatabaseContext.executeStatement(DatabaseContext.java:327)
  123. ... 19 more
  124. You have to import a valid schema before you can poulate the database. Unfortunately MySQL is no longer officialy supported, so there is no official schema available.
  125. See above for my attempts to create one.
  126. ## Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'krautundrueber' for key 'users_display_name_idx' ##
  127. After I changed some name of columns the imort seemed to work fine.
  128. Executing
  129. osmosis --read-xml file="bremen.osm.bz2" --write-apidb-0.6 host="127.0.0.1" dbType="mysql" database="api06_test" user="osm" password="osm" validateSchemaVersion=no
  130. works for a while but fails with
  131. ...
  132. Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'krautundrueber' for key 'users_display_name_idx'
  133. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  134. at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
  135. at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  136. at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
  137. at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
  138. at com.mysql.jdbc.Util.getInstance(Util.java:386)
  139. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1039)
  140. at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
  141. at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
  142. at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
  143. at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
  144. at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
  145. at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
  146. at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)
  147. at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2345)
  148. at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2330)
  149. at org.openstreetmap.osmosis.apidb.v0_6.impl.UserManager.insertUser(UserManager.java:140)
  150. ... 18 more
  151. On first look on entries look fine. So I had to dive deeper into Osmosis.
  152. I build Osmosis and imported it into Eclipse. Unfortunately the current project layout doesn't allow for launching any task. I had to create a Junit-Test in th `apidb` module, to have access to all plugins.
  153. @Test
  154. public void debugDuplicateEntry() {
  155. Osmosis.run(new String[] {
  156. "--read-xml-0.6",
  157. "/Users/q2web/Downloads/bremen.osm",
  158. "--write-apidb-0.6",
  159. "host=127.0.0.1",
  160. "dbType=mysql",
  161. "database=api06_test",
  162. "user=osm",
  163. "password=osm",
  164. "validateSchemaVersion=no"
  165. });
  166. }
  167. The user that makes problem is
  168. uid="341865" user="krautundrueber"
  169. It clashes with
  170. uid="14181" user="Krautundrueber"
  171. While the user user has a unique id, it seems that the index `users_display_name_idx` on `varchar(255)`, ignores case.
  172. So one has to set case sensitive collation
  173. ...
  174. WORD VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_cs
  175. ...
  176. Unfortunately the Collator `utf8_cs` isn't installed per default, so I tried `utf8_bin`
  177. Setting the default collate didn't help
  178. CREATE DATABASE IF NOT EXISTS api06_test
  179. DEFAULT CHARACTER SET utf8
  180. DEFAULT COLLATE utf8_bin;
  181. so I also set it expclitly for the `display_name` column
  182. `display_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
  183. ## com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '54750854-4-FIXME' for key 'PRIMARY' ##
  184. After fixing the character set on the user names, the same error happens on the tags for the ways
  185. Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '54750854-4-FIXME' for key 'PRIMARY'
  186. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  187. at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
  188. at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  189. at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
  190. at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
  191. at com.mysql.jdbc.Util.getInstance(Util.java:386)
  192. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1040)
  193. at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
  194. at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
  195. at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
  196. at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
  197. at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
  198. at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
  199. at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2450)
  200. at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2371)
  201. at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2355)
  202. at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.flushWayTags(ApidbWriter.java:724)
  203. ... 20 more
  204. This is because some tags are called `FIXME` and some are `fixme`.
  205. So set the collation for the keys in `way_tags`
  206. ...
  207. `k` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  208. ...
  209. While were at it do the same for `node_tags`
  210. ...
  211. `k` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
  212. ...
  213. and `relation_tags`
  214. ...
  215. `k` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
  216. ...
  217. and `current_relation_tags`
  218. ...
  219. `k` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
  220. ...
  221. and `changeset_tags`
  222. ...
  223. `k` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
  224. ...
  225. and `current_node_tags`
  226. ...
  227. `k` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  228. ...
  229. and `current_way_tags`
  230. ...
  231. `k` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',
  232. ...
  233. and `user_preferences`
  234. ...
  235. `k` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  236. ...
  237. ## org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to load current relation members. ##
  238. Adter fixing the collation errors I got
  239. org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to load current relation members.
  240. at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.populateCurrentRelations(ApidbWriter.java:1020)
  241. at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.populateCurrentTables(ApidbWriter.java:1032)
  242. at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.complete(ApidbWriter.java:1064)
  243. at org.openstreetmap.osmosis.xml.v0_6.XmlReader.run(XmlReader.java:113)
  244. at java.lang.Thread.run(Thread.java:722)
  245. Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'relation_id' in 'field list'
  246. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  247. at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
  248. at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  249. at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
  250. at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
  251. at com.mysql.jdbc.Util.getInstance(Util.java:386)
  252. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
  253. at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
  254. at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
  255. at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
  256. at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
  257. at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
  258. at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
  259. at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)
  260. at org.openstreetmap.osmosis.apidb.v0_6.ApidbWriter.populateCurrentRelations(ApidbWriter.java:1017)
  261. ... 4 more
  262. This is inline with the schema changes prefixing the `id` fields with the type. I forgot those the first time around.
  263. ## INFO: Total execution time: 87535 milliseconds. ##
  264. Woohoo!