Layer using MySQL select
------------------------
Key: GEOS-4528
URL: http://jira.codehaus.org/browse/GEOS-4528
Project: GeoServer
Issue Type: Bug
Components: Community modules
Affects Versions: 2.1-RC5
Reporter: Brian Youngblood
Assignee: Andrea Aime
Priority: Minor
Not sure the correct component category for this bug, but I wanted to get it logged in hopes we can figure something out. Not expecting a speedy solution...I understand the MySQL db is not officially supported.
I've exchanged emails with Andrea on the mailing list, and I'd imagine others use MySQL would want to use this feature. I'm still in the process of setting up a test environment for this issue, but maybe someone else has this setup and can use Andrea's last comment to add more information. Again, here is the exchange from the mailing list.
--brian-
I'm trying to create a layer based on a sql view using mysql, but the geometry type is responding as a byte. I'm testing with a simple select statement as "select * from `counties`" Adding the layer by selecting the table directly works fine. Here is my table description. Odd that it works directly, but doing so based on a sql statement produces different results.
CREATE TABLE `counties` (
`id` int(10) unsigned NOT NULL,
`county_name` varchar(32) NOT NULL DEFAULT '',
`state_name` varchar(25) NOT NULL DEFAULT '',
`state_initials` char(2) DEFAULT NULL,
`member_count` mediumint(9) DEFAULT '0',
`percentage` int(11) DEFAULT '0',
`state_fips` char(2) NOT NULL DEFAULT '',
`county_fips` char(3) NOT NULL DEFAULT '',
`fips` varchar(10) NOT NULL DEFAULT '0',
`nces_fips` varchar(10) DEFAULT NULL,
`county_shape` geometry NOT NULL DEFAULT '',
`county_center` point NOT NULL DEFAULT '',
UNIQUE KEY `fips` (`fips`),
SPATIAL KEY `geo` (`county_shape`),
KEY `state_initials` (`state_initials`),
KEY `county_fips` (`county_fips`)
) ENGINE=MyISAM AUTO_INCREMENT=3142 DEFAULT CHARSET=latin1;
--Andrea-
As far as I can see the sql view support in mysql has never been
tested, the test harness in geotools does not have that test class, only h2, postgis
and oracle have it (but 90% of the code is shared among databases, so it's likely it's
something quite specific to mysql).
The sql view code guesses the types of the various columns out of a resultset
metadata, it may be that the mysql jdbc driver is buggy and returning the wrong
type for the geometry column, or that the code needs to be amended to take
into account some mysql oddity.
It would help to have some developer that actually uses mysql for spatial data,
afaik Justin is trying to maintain the mysql store out of his good hearth
but he's not even using mysql regularly (props to him, but having someone
else that actually uses mysql day in/day out to help out in
development would be
a step forward)
--Andrea--
The mysql code is in geotools, here is the part common to all database:
http://svn.osgeo.org/geotools/trunk/modules/library/jdbc/
and here is the mysql dialect specific classes and tests:
http://svn.osgeo.org/geotools/trunk/modules/plugin/jdbc/jdbc-mysql/
Ah, try to run the mysql tests, that will fail telling you it was not possible
to connect to the test db, but at that point you should find in your home
directory a .geotols/mysql.properties.example, create a .geotols/mysql.properties
file with the right values in it and tests should start connecting to your local
mysql db for testing (Justin, did you have to add any property to that file to have it run on all tests?)
What mysql is missing is a sublcass of JDBCVirtualTableTest and, by what you've
said, modifications to make it actually work
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira