Bug #76456 InnoDB may upgrade utf8->utf8mb4 without table rebuild in some cases
Submitted: 23 Mar 2015 12:09
Reporter: Andrii Nikitin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: utf8mb4

[23 Mar 2015 12:09] Andrii Nikitin
Description:
Rebuilding table is not technically needed for some cases when old character set is subset of new character set. In other words in some cases new table structure will be identical to old table structure except character set metadata.

This FR requests to identify those cases when no data rebuilt is needed and change only table definition.

That will simplify a lot adoption of utf8mb4 and ease life of many DBAs.

How to repeat:
FR

Suggested fix:
InnoDB may scip rebuilding data in some cases.
That may be standalone application as well, which only changes metadata replacing utf8 -> utf8mb4 for those cases when it is safe.

E.g. below is list of requirements when no data rebuilding is needed (to be confirmed by developers):

A. Size of row in utf8mb4 definition doesn't exceed 65535 bytes

B. Multicolumn indexes which contain converted utf8 columns do not exceed  3072 bytes in new definition

C1. Indexes on converted utf8 columns don't exceed 191 characters
C2. for  DYNAMIC and COMPRESSED tables with  innodb_large_prefix enabled:
  Indexes on converted utf8 columns don't exceed 768 characters

D1. For REDUNDANT tables following requirement isn't exceeded by switching to utf8mb4:
 total length of the fields in a record is less than 128 bytes
D2. For COMPACT tables following requirement isn't exceeded by switching to utf8mb4:
 maximum size of column in bytes is less than 255