A prefixed spatial column can be used in a primary key (as a part of it, or alone).
However, SHOW CREATE TABLE loses the prefix from the key definition. So, an attempt to re-execute this CREATE TABLE, e.g. upon restoring a mysqldump, fails with ER_BLOB_KEY_WITHOUT_LENGTH.
CREATE TABLE t1 (col_spatial GEOMETRY, PRIMARY KEY(col_spatial(4)));
|
SHOW CREATE TABLE `t1`;
|
DROP TABLE t1;
|
|
10.3 e7be2d31
|
CREATE TABLE t1 (col_spatial GEOMETRY, PRIMARY KEY(col_spatial(4)));
|
SHOW CREATE TABLE `t1`;
|
Table Create Table
|
t1 CREATE TABLE `t1` (
|
`col_spatial` geometry NOT NULL,
|
PRIMARY KEY (`col_spatial`)
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
DROP TABLE t1;
|
Trying to re-create:
query 'CREATE TABLE `t1` (
|
`col_spatial` geometry NOT NULL,
|
PRIMARY KEY (`col_spatial`)
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci' failed: 1170: BLOB/TEXT column 'col_spatial' used in key specification without a key length
|
Reproducible on all MariaDB 10.x versions, dead and alive.
5.5 was keeping the prefix in the definition, so does MySQL 5.7.
MySQL 8.0 forbids the original create.
Bug
Major