Bug #85614 alter table fails when default character set changes to utf8mb4
Submitted: 24 Mar 2017 15:28 Modified: 27 Mar 2017 18:40
Reporter: Tor Didriksen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.2 OS:Any
Assigned to: CPU Architecture:Any

[24 Mar 2017 15:28] Tor Didriksen
Description:
1089: Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

How to repeat:
CREATE TABLE t1(
  i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY);
ALTER TABLE t1
  DROP i,
  ADD i INT UNSIGNED NOT NULL AUTO_INCREMENT,
  AUTO_INCREMENT = 1;
DROP TABLE t1;

Suggested fix:
don't do this in prepare_key_column()
      column_length= column->length * sql_field->charset->mbmaxlen;
for an integer column
[27 Mar 2017 18:40] Paul Dubois
Posted by developer:
 
Noted in 8.0.2 changelog.

ALTER TABLE could fail when the default character set changed to
utf8mb4 due to incorrect column length calculations.