MySQL Bugs: #43326: Spatial key index prefix needs documentation in 5.1 upgrade docs

Bug #43326	Spatial key index prefix needs documentation in 5.1 upgrade docs
Submitted:	3 Mar 2009 10:22	Modified:	5 Mar 2009 18:27
Reporter:	Geert Vanderkelen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Dumps made with MySQL 5.0 which have SPATIAL KEY in a table will fail to replay dumps into MySQL 5.1 because 5.0 puts automagically a index prefix.

See bug #36080

How to repeat:
Expected behavior MySQL 5.1:
mysql> CREATE TABLE space1 (
    ->   p POINT,
    ->   SPATIAL KEY (p(32))
    -> );
ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

Suggested fix:
Should be documented in
 http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-5-0.html

Geert, it's probably not useful to say just "this will break your upgrade." Is there a preferred workaround or alternative upgrade strategy that you'd like to recommend?

Paul,

The easiest workaround was to edit the dump file and remove the prefix from the spacial key declaration. Don't have other idea yet..

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added this to upgrading section:

Known issue: Dumps performed by using mysqldump to generate a dump
file before the upgrade and reloading the file after upgrading are
subject to the following problem:

Before MySQL 5.0.40, mysqldump displays SPATIAL index definitions
using prefix lengths for the indexed columns. These prefix lengths
are accepted in MySQL 5.0, but not as of MySQL 5.1. If you use
mysqldump from versions of MySQL older than 5.0.40, any table
containing SPATIAL indexes will cause an error when the dump file is
reloaded into MySQL 5.1 or higher.

For example, a table definition might look like this when dumped in
MySQL 5.0:  

CREATE TABLE `t` (
 `g` geometry NOT NULL,
 SPATIAL KEY `g` (`g`(32))
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The SPATIAL index definition will not be accepted in MySQL 5.1. To
work around this, edit the dump file to remove the prefix:

CREATE TABLE `t` (
 `g` geometry NOT NULL,
 SPATIAL KEY `g` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Dump files can be large, so it may be preferable to dump table 
definitions and data separately to make it easier to edit the
definitions: 

shell> mysqldump --no-data other_args > definitions.sql
shell> mysqldump --no-create-info other_args > data.sql

Then edit definitions.sql before reloading definitions.sql and
data.sql, in that order.