MySQL Bugs: #26665: @ManyToOne incompatible with SPATIAL INDEX(locn)

Bug #26665	@ManyToOne incompatible with SPATIAL INDEX(locn)
Submitted:	27 Feb 2007 6:02	Modified:	12 May 2015 11:39
Reporter:	Nicholas Albion	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S2 (Serious)
Version:	5.0.17	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	@ManyToOne, errno 150, innodb, myisam, spatial index

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?

Thank you for a problem report. Adding FOREIGN KEYs support to MyISAM is work in progress already. But do not expect this to be implemented in 5.0.x or 5.1.x.

Posted by developer:
 
Closing this feature request since InnoDB spatial indexes were implemented in MySQL 5.7.5.