GAE/MySQL Spatial Index?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

GAE/MySQL Spatial Index?

Angelo Torres
I'm running into some issues getting hibernate-spatial-4.0-M1 to work with Google Cloud SQL (MySQL 5.5.34)

Following the tutorial (http://www.hibernatespatial.org/tutorial-hs4.html), when I try to .persist() an Entity I get:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'location' at row 1
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4094)

I moved the @Type annotation to the getter and was now able to .persist()

private com.vividsolutions.jts.geom.Point location;
@Type(type = "org.hibernate.spatial.GeometryType") public Point getLocation() { return location; } public void setLocation(Point location) { this.location = location; }

Now I want to create a spatial index. But I have to manually alter the table engine to MyISAM and even then can't create the spatial index because 'location' is stored as a tinyblob, not a Geometry:

CREATE SPATIAL INDEX loc_idx ON Event (location)

Error 1687: 
A SPATIAL index may only contain a geometrical type column


Is there a (clean) way to get Hibernate/Hibernate Spatial to create this table with a MyISAM engine and create a spatial index on this column?

For reference, I'm using hibernate 4.2.6.Final and my persistence.xml looks like:
<properties>
<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.GoogleDriver" />
<property name="javax.persistence.jdbc.url" value="jdbc:google:mysql://*" />
<property name="hibernate.dialect" value="org.hibernate.spatial.dialect.mysql.MySQLSpatialDialect" />
<property name="hibernate.show_sql" value="true" />
<property name="hibernate.hbm2ddl.auto" value="update" />
<property name="hibernate.connection.pool_size" value="0" />
</properties>



 





_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Reply | Threaded
Open this post in threaded view
|

Re: GAE/MySQL Spatial Index?

Karel Maesen
Administrator
Hi,

Could you enable Hibernate's SQL-logging and post the CREATE TABLE statement? I'm thinking it has to do with the fact that the Google SQL engine has some differences in behavior that make it incompatible with Hibernate Spatial. Maybe you first should see if you can create the table manually, and then see if the persist, query and retrieve operations work OK.

Btw, other dialects have support for the InnoDB engine (check the JavaDocs). Maybe you have more lock with those.

Regards,

Karel

On 18 Oct 2013, at 02:53, Angelo Torres <[hidden email]> wrote:

> I'm running into some issues getting hibernate-spatial-4.0-M1 to work with Google Cloud SQL (MySQL 5.5.34)
>
> Following the tutorial (http://www.hibernatespatial.org/tutorial-hs4.html), when I try to .persist() an Entity I get:
>
> com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'location' at row 1
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4094)
>
>
> I moved the @Type annotation to the getter and was now able to .persist()
>
>         private com.vividsolutions.jts.geom.Point location;
>
> @Type(type = "org.hibernate.spatial.GeometryType")
> public Point getLocation() {
> return location;
> }
>
> public void setLocation(Point location) {
> this.location = location;
> }
>
>
> Now I want to create a spatial index. But I have to manually alter the table engine to MyISAM and even then can't create the spatial index because 'location' is stored as a tinyblob, not a Geometry:
>
> CREATE SPATIAL INDEX loc_idx ON Event (location)
>
> Error 1687:  A SPATIAL index may only contain a geometrical type column
>
> Is there a (clean) way to get Hibernate/Hibernate Spatial to create this table with a MyISAM engine and create a spatial index on this column?
>
> For reference, I'm using hibernate 4.2.6.Final and my persistence.xml looks like:
> <properties>
> <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.GoogleDriver" />
> <property name="javax.persistence.jdbc.url" value="jdbc:google:mysql://*" />
> <property name="hibernate.dialect" value="org.hibernate.spatial.dialect.mysql.MySQLSpatialDialect" />
> <property name="hibernate.show_sql" value="true" />
> <property name="hibernate.hbm2ddl.auto" value="update" />
> <property name="hibernate.connection.pool_size" value="0" />
> </properties>
>
>
>
>  
>
>
>
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Reply | Threaded
Open this post in threaded view
|

Re: GAE/MySQL Spatial Index?

Angelo Torres
I think I fixed this by moving @Type back to the field, from the getter (and moving all my other annotations to their fields).

I also upgraded to GAE 1.8.6 and JDK 1.7.0_45 today so that might have something to do with it. Either way, it seems to be working now.

Thanks for the help!


On Sat, Oct 19, 2013 at 6:13 AM, Karel Maesen <[hidden email]> wrote:
Hi,

Could you enable Hibernate's SQL-logging and post the CREATE TABLE statement? I'm thinking it has to do with the fact that the Google SQL engine has some differences in behavior that make it incompatible with Hibernate Spatial. Maybe you first should see if you can create the table manually, and then see if the persist, query and retrieve operations work OK.

Btw, other dialects have support for the InnoDB engine (check the JavaDocs). Maybe you have more lock with those.

Regards,

Karel

On 18 Oct 2013, at 02:53, Angelo Torres <[hidden email]> wrote:

> I'm running into some issues getting hibernate-spatial-4.0-M1 to work with Google Cloud SQL (MySQL 5.5.34)
>
> Following the tutorial (http://www.hibernatespatial.org/tutorial-hs4.html), when I try to .persist() an Entity I get:
>
> com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'location' at row 1
>       at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4094)
>
>
> I moved the @Type annotation to the getter and was now able to .persist()
>
>         private com.vividsolutions.jts.geom.Point location;
>
>       @Type(type = "org.hibernate.spatial.GeometryType")
>       public Point getLocation() {
>               return location;
>       }
>
>       public void setLocation(Point location) {
>               this.location = location;
>       }
>
>
> Now I want to create a spatial index. But I have to manually alter the table engine to MyISAM and even then can't create the spatial index because 'location' is stored as a tinyblob, not a Geometry:
>
> CREATE SPATIAL INDEX loc_idx ON Event (location)
>
> Error 1687:  A SPATIAL index may only contain a geometrical type column
>
> Is there a (clean) way to get Hibernate/Hibernate Spatial to create this table with a MyISAM engine and create a spatial index on this column?
>
> For reference, I'm using hibernate 4.2.6.Final and my persistence.xml looks like:
> <properties>
> <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.GoogleDriver" />
> <property name="javax.persistence.jdbc.url" value="jdbc:google:mysql://*" />
> <property name="hibernate.dialect" value="org.hibernate.spatial.dialect.mysql.MySQLSpatialDialect" />
> <property name="hibernate.show_sql" value="true" />
> <property name="hibernate.hbm2ddl.auto" value="update" />
> <property name="hibernate.connection.pool_size" value="0" />
> </properties>
>
>
>
>
>
>
>
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users


_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users