Description:
There seems to be a compatibility problem between SPATIAL INDEX (which requires
type=MyISAM) and the @ManyToOne annotation in EJB 3.0 / Hibernate.
When @ManyToOne is used, Hibernate maps the class to the database by executing the
following command on the database:
alter table stations add index FK1 (providerID),
add constraint FK1 foreign key (providerID) references providers (id)
Unless I'm mistaken, the current stable version of MySQL requires for both tables to be
of type=InnoDB in order for FOREIGN KEY to work.
How to repeat:
CREATE TABLE stations (
id INT NOT NULL AUTO_INCREMENT,
providerID INT,
location POINT NOT NULL,
SPATIAL INDEX(location),
PRIMARY KEY (id) ) TYPE=MyISAM;
/* MyISAM so that SPATIAL INDEX works */
CREATE TABLE providers( id INT NOT NULL,
name VARCHAR(64) NOT NULL,
PRIMARY KEY (id) );
/* Then use @ManyToOne in a java class, or do it directly in MYSQL: */
ALTER TABLE stations ADD INDEX FK1 (providerID),
ADD CONSTRAINT FK1 FOREIGN KEY (providerID) REFERENCES providers (id);
/* Now expect an error: "errno:1005: Can't create table ???? (errno: 150)" */
ALTER TABLE stations TYPE=InnoDB;
/* The FOREIGN KEY command should now work - such a shame that the SPATIAL index won't */
Suggested fix:
From what I've read, it seems that InnoDB support for SPATIAL INDEX is not on the cards,
so can we get MyISAM support for FOREIGN KEY?
Description: There seems to be a compatibility problem between SPATIAL INDEX (which requires type=MyISAM) and the @ManyToOne annotation in EJB 3.0 / Hibernate. When @ManyToOne is used, Hibernate maps the class to the database by executing the following command on the database: alter table stations add index FK1 (providerID), add constraint FK1 foreign key (providerID) references providers (id) Unless I'm mistaken, the current stable version of MySQL requires for both tables to be of type=InnoDB in order for FOREIGN KEY to work. How to repeat: CREATE TABLE stations ( id INT NOT NULL AUTO_INCREMENT, providerID INT, location POINT NOT NULL, SPATIAL INDEX(location), PRIMARY KEY (id) ) TYPE=MyISAM; /* MyISAM so that SPATIAL INDEX works */ CREATE TABLE providers( id INT NOT NULL, name VARCHAR(64) NOT NULL, PRIMARY KEY (id) ); /* Then use @ManyToOne in a java class, or do it directly in MYSQL: */ ALTER TABLE stations ADD INDEX FK1 (providerID), ADD CONSTRAINT FK1 FOREIGN KEY (providerID) REFERENCES providers (id); /* Now expect an error: "errno:1005: Can't create table ???? (errno: 150)" */ ALTER TABLE stations TYPE=InnoDB; /* The FOREIGN KEY command should now work - such a shame that the SPATIAL index won't */ Suggested fix: From what I've read, it seems that InnoDB support for SPATIAL INDEX is not on the cards, so can we get MyISAM support for FOREIGN KEY?