MySQLDataStore not ready for MySQL 4.1 geometric datatypes

Description

The new DataStore implementation for MySQL does not yet handle MySQL 4.1's geometric datatypes. Most of the code is there to handle geometry, but some MySQL server and driver data packet irregularities prevent the updating of any column or row in a table that has geometry.

MySQL 4.1 introduced certain geometric datatypes, a brand new concept in MySQL. These types are listed at http://www.mysql.com/doc/en/MySQL_spatial_datatypes.html - http://www.mysql.com/doc/en/MySQL_spatial_datatypes.html .

MySQL has a standard format for the data packets it sends to the MySQL driver. Each packet represents one data value (one column in one row). Unfortunately, MySQL 4.1 uses a different format for its geometric packets than for non-geometric packets (VARCHAR, INT, etc.). Because of this, the MySQL Connector/J driver, while able to read the data, deems the packet unsuitable for updating. So any ResultSet objects obtained from a table with geometry are non-updatable, even if the programmer requests an updatable ResultSet.

The DataStore implementation uses updatable ResultSet objects for updating rows. Therefore, outside of a possible repair to MySQL or the MySQL driver, the fix for the Geotools MySQL DataStore implementation would involve overriding all of the existing JDBCDataStore update code. I have not tried this yet, since it would be messy and would waste the good work that has already been done on JDBCDataStore.

I hope that someone will have a better suggestion, but I don't see an option other than waiting for MySQL to be fixed or overriding the JDBCDataStore update code. Any ideas?

Environment

None

Activity

Show:
codehaus
April 10, 2015, 3:19 PM

CodeHaus Comment From: cholmes - Time: Thu, 8 Jan 2004 16:03:07 -0600
---------------------
Postgis actually faces similar problems. They might be easier to overcome, I need to spend some more time with the jdbc driver - it actually tries to go ahead and do the update, it just somehow renames the update column astext and fails.

I guess it's a bit different for Postgis, as we already had a bunch of solid code that just does updates by directly issueing commands. So it was not that difficult at all to override the jdbc updating code.

Actually, looking back over my code, you may just be able to use WKTAttributeIO.java (in data/org/geotools/data/jdbc). Its write method actually contains a call to issue the sql statement directly instead of relying on a JDBC update method. When writing this class I was actually hoping the mysql would make use of it as well, since it also does things with WKT. Postgis now just uses the class directly, but if there are specific things that it does not offer than we could put common elements in it and both subclass.

It might need a bit more testing, I remember problems with updates that effect both the geometry and non-geometry fields, and am not sure if they were resolved. For GeoServer I just implemented a PostgisFeatureStore which bypassed all the JDBCFeatureWriter code, since I just wasn't feeling that confident in it, and knew that the code from PostgisDataSource worked. But I'm planning on eventually moving over to the writer code. PostgisDataStore currently has flags to switch between the two.

So I'd say try out WKTAttributeIO, as I was using it to tackle similar problems in Postgis.

codehaus
April 10, 2015, 3:19 PM

CodeHaus Comment From: - Time: Thu, 8 Jan 2004 20:00:01 -0600
---------------------
Chris, thank you for your comments and for pointing me in the right direction. I'll try WKTAttributeIO.

I am (relatively) new to this project, so I thank you for your patience and your advice.

codehaus
April 10, 2015, 3:19 PM

CodeHaus Comment From: shepshep - Time: Fri, 9 Jan 2004 08:45:06 -0600
---------------------
Chris, it looks like WKTAttributeIO will work fine, with one gotcha. It looks like the current version puts double quotes around table names and field names. This will not work for MySQL, but everything else would work.

Could I add a private String formatFieldName(String) method to WKTAttributeIO? WKTAttributeIO's version of the method would return a String with double quotes around the field name, so no existing implementations (such as Postgis) will break. The MySQL implementation will override formatFieldName(String) to return just the field name.

I'll code it up and try it out for MySQL. Let me know if you can think of any potential problems with my idea.

codehaus
April 10, 2015, 3:19 PM

CodeHaus Comment From: shepshep - Time: Fri, 9 Jan 2004 14:42:27 -0600
---------------------
We can now insert into tables that have geometry. Updating is trickier, and I'm hoping to have it done by Monday.

Assignee

Unassigned

Reporter

codehaus

Triage

None

Components

Fix versions

Affects versions

Priority

Medium
Configure