Bug #76016 Incorrect assumption about InnoDB record length in optimizer temporary tables
Submitted: 24 Feb 2015 6:53 Modified: 27 Mar 2015 14:44
Reporter: Marko Mäkelä Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.7.7 OS:Any
Assigned to: CPU Architecture:Any

[24 Feb 2015 6:53] Marko Mäkelä
WL#6737 made InnoDB the default storage engine for temporary tables that are created by the optimizer to execute ORDER BY, joins and similar things.

There seems to be an incorrect assumption about handler::max_supported_key_part_length() somewhere. It could be a recent regression, because I did not see this failure a week or two before the 5.7.6 clone-off.

How to repeat:
./mtr --mysqld=--innodb-large-prefix=on --mysqld=--innodb-file-format=barracuda \
--parallel=auto --force --retry=0 --suite=main \
internal_tmp_disk_storage_engine subquery_{sj_mat_nosj,mat,mat_all}

Note that WL#7703 will change the defaults.

Suggested fix:
Do not assume that ha_innobase::max_supported_key_part_length() returns the same value for every InnoDB table.

Consider filing a follow-up bug against InnoDB, to ensure that the optimizer-internal temporary tables are always created with ROW_FORMAT=DYNAMIC and large prefix, no matter what the parameters say.
[27 Mar 2015 14:44] Paul DuBois
Noted in 5.7.7, 5.8.0 changelogs.

With InnoDB as the default temporary table storage engine, InnoDB
sometimes made incorrect assumptions about temporary table key part