[MDEV-29941] Use of a spatial column in a primary key leads to invalid CREATE TABLE, breaks mysqldump Created: 2022-11-03  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Create Table, GIS
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: 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.
Generated at Thu Feb 08 10:12:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.