MySQL + DIH + SpatialSearch

[Eric Angel <er...@gmail.com>]

I had difficulties getting this to work, so hopefully this will help others
having the same issue.

My environment:

Solr 3.1
MySQL 5.0.77

Schema:
     <fieldType name="location" class="solr.LatLonType"
subFieldSuffix="_coordinate"/>
    <field name="latlng" type="location" indexed="true" stored="true"/>
    <dynamicField name="*_coordinate"  type="tdouble" indexed="true"
stored="false"/>

DIH data-config:
    <dataSource driver="java.lang.String"
url="jdbc:mysql://xxx.xxx.xxx.xxx/db1" user="user" password="secret"
readOnly="true" batchSize="-1"/>
    <entity name="practice" pk="id" query="select id, name, concat_ws(',',
lat, lng) as latlng from practice "></entity>

I kept getting build errors similar to this:

org.apache.solr.common.SolrException:
org.apache.lucene.spatial.tier.InvalidGeoException: incompatible dimension
(2) and values ([B@2964a05d).  Only 0 values specified
at org.apache.solr.schema.PointType.createFields(PointType.java:77)
at org.apache.solr.update.DocumentBuilder.addField(DocumentBuilder.java:199)
at 
org.apache.solr.update.DocumentBuilder.toDocument(DocumentBuilder.java:277)
at 
org.apache.solr.update.processor.RunUpdateProcessor.processAdd(RunUpdateProc
essorFactory.java:60)
at org.apache.solr.handler.dataimport.SolrWriter.upload(SolrWriter.java:73)
at 
org.apache.solr.handler.dataimport.DataImportHandler$1.upload(DataImportHand
ler.java:291)
at 
org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:
625)
at 
org.apache.solr.handler.dataimport.DocBuilder.doFullDump(DocBuilder.java:265
)
at 
org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:184)
at 
org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.ja
va:335)
at 
org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:393
)
at 
org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:374)
Caused by: org.apache.lucene.spatial.tier.InvalidGeoException: incompatible
dimension (2) and values ([B@2964a05d).  Only 0 values specified
at 
org.apache.lucene.spatial.DistanceUtils.parsePoint(DistanceUtils.java:376)
at org.apache.solr.schema.PointType.createFields(PointType.java:75)

This would happen regardless of whether I used PointType, LatLonType, or
GeoHashField.

So I thought maybe I should pay attention to what the error says -
"incompatible dimension (2) and values ([B@2964a05d).  Only 0 values
specified".  Looking at the code, this revealed that it's trying to parse
"B@2964a05d" into a spatial field.  So my DIH was getting bad values ­
apparently, there's a bug in MySQL 5.0:
http://bugs.mysql.com/bug.php?id=12030 ­ where concat changes the character
set to binary.

To solve this, you can either:
* upgrade to MySQL 5.5 (according to the bug page, it was fixed in 5.5, but
I haven't tested it).
* Or you can typecast before you concat:
> * <entity name="practice" pk="id" query="select id, name, concat_ws(',',
> cast(lat as char), cast(lng as char)) as latlng from practice "></entity>

Eric

Re: MySQL + DIH + SpatialSearch

[Stefan Matheis <ma...@googlemail.com>]

Hey Eric,

On Mon, Jan 24, 2011 at 7:23 PM, Eric Angel <er...@gmail.com> wrote:

> * Or you can typecast before you concat:
> > * <entity name="practice" pk="id" query="select id, name, concat_ws(',',
> > cast(lat as char), cast(lng as char)) as latlng from practice "></entity>
>

Casting before or after concat'ing, work's both - as we've seen two weeks
ago, in a similar thread (
http://search.lucidimagination.com/search/document/250975238eaeb9e0/solr_4_0_spatial_search_how_to#60c4c05c9b482df1
)

But anyway, thanks for pointing out, that it's really a (confirmed)
MySQL-Bug - very annoying :/

Regards
Stefan