Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
-
None
Description
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.