MySQL Bugs: #77537: InnoDB: Bogus check for maximum record length with SPATIAL KEY

Bug #77537	InnoDB: Bogus check for maximum record length with SPATIAL KEY
Submitted:	29 Jun 2015 10:54	Modified:	21 Jul 2015 19:11
Reporter:	Marko Mäkelä	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.7.8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The dict_index_too_big_for_undo() check for spatial (r-tree) indexes is bogus. For spatial indexes, we will not write a 3072-byte prefix to the undo log. Instead, a bounding box will be written. However, the check is wrongly assuming that we write a 3072-byte prefix, and wrongly rejecting some operations.

How to repeat:
CREATE TABLE t1 (
  p POINT PRIMARY KEY,
  r1 POINT NOT NULL, r2 POINT NOT NULL, r3 POINT NOT NULL,
  l LINESTRING NOT NULL,
  g GEOMETRY NOT NULL,
  SPATIAL KEY (p),
  SPATIAL KEY (r1), SPATIAL KEY (r2), SPATIAL KEY (r3),
  SPATIAL KEY (l), SPATIAL KEY (g)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE t1 DROP PRIMARY KEY;
DROP TABLE t1;

The ALTER TABLE will wrongly claim:
query 'ALTER TABLE t1 DROP PRIMARY KEY' failed: 1118: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

Suggested fix:
Make fict_index_too_big_for_undo() work properly with spatial indexes.

Posted by developer:
 
Jimmy says that we are indeed unnecessarily storing a 3072-byte prefix to the undo log records, instead of only storing a maximum bounding rectangle (MBR).
It seems that we would need a dict_col_t::gis_part flag for r-trees, instead of using the dict_col_t::ord_part that used to specific for b-trees.

Because fixing this bug changes the undo log format related to R-tree indexes, it should be fixed before 5.7 reaches GA.

Posted by developer:
 
We log only MBR for purge when a column is only used by spatial index, and the field data is stored externally, in which case, we logged prefix + MBR.

Posted by developer:
 
Fixed as of the upcoming 5.7.9, 5.8.0 releases, and here's the changelog entry:

For spatial indexes, InnoDB unnecessarily stored a 3072-byte prefix in
undo log records instead of just the maximum bounding rectangle (MBR). For
columns with externally stored data, both the prefix and MBR are logged.

Thank you for the bug report.