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
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.2
• OS: Any
• 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.