MySQL Bugs: #99892: initialize with innodb_page_size=4096 gets "Specified key was too long" errors

Bug #99892	initialize with innodb_page_size=4096 gets "Specified key was too long" errors
Submitted:	15 Jun 2020 15:32	Modified:	21 Jan 2021 20:32
Reporter:	Mark Callaghan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Dictionary	Severity:	S3 (Non-critical)
Version:	8.0.18, 8.0.20	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Initializing 8.0.18 with innodb_page_size=4096 gets several errors for:
mysqld: Specified key was too long; max key length is 768 bytes

This occurs with 8.0.18. It does not occur with 5.7.29

How to repeat:
For 8.0.18, run:
bin/mysqld --initialize-insecure

With etc/my.cnf set as:
[mysqld]
ssl=0
default_authentication_plugin=mysql_native_password

tmpdir=/data/m/my
log_bin=/data/m/my/binlogs/bl
datadir=/data/m/my/data
innodb_data_home_dir=/data/m/my/data
innodb_data_file_path=ibdata1:512M:autoextend
innodb_log_group_home_dir=/data/m/my/txlogs

innodb_dedicated_server=ON
innodb_page_size=4K

Then repeat with innodb_page_size=8K

The output from innodb_page_size=4K is:
2020-06-15T15:11:00.269542Z 0 [System] [MY-013169] [Server] /home/mdcallag/d/my8018/bin/mysqld (mysqld 8.0.18) initializing of server in progress as process 20510
2020-06-15T15:11:00.288682Z 0 [Warning] [MY-012363] [InnoDB] innodb-page-size has been changed from the default value 16384 to 4096.
 100 200 300 400 500
 100 200 300 400 500 600 700 800 900 1000
 100 200 300 400 500 600 700 800 900 1000
 100 200 300 400 500 600 700 800 900 1000
 100 200 300 400 500 600 700 800 900 1000
 100 200 300 400 500 600 700 800 900 1000
 100 200 300 400 500 600 700 800 900 1000
 100 200 300 400 500 600 700 800 900 1000
 100 200 300 400 500 600 700 800 900 1000
 100 200 300 400 500 600 700 800 900 1000
mysqld: Specified key was too long; max key length is 768 bytes
mysqld: Specified key was too long; max key length is 768 bytes
mysqld: Specified key was too long; max key length is 768 bytes
mysqld: Specified key was too long; max key length is 768 bytes
mysqld: Specified key was too long; max key length is 768 bytes
mysqld: Specified key was too long; max key length is 768 bytes
2020-06-15T15:11:17.631235Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

Hello Mark,

Thank you for the report and feedback!

Thanks,
Umesh

- 8.0.20

 bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/99892 --log-error-verbosity=3 --innodb_page_size=4096
2020-06-16T05:26:15.973733Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.

.
2020-06-16T05:26:16.359018Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-06-16T05:26:16.361857Z 1 [Note] [MY-011088] [Server] Data dictionary initializing version '80017'.
mysqld: Specified key was too long; max key length is 768 bytes
mysqld: Specified key was too long; max key length is 768 bytes
mysqld: Specified key was too long; max key length is 768 bytes
mysqld: Specified key was too long; max key length is 768 bytes
mysqld: Specified key was too long; max key length is 768 bytes
mysqld: Specified key was too long; max key length is 768 bytes
2020-06-16T05:26:16.635195Z 1 [Note] [MY-010007] [Server] Installed data dictionary with version 80017

Posted by developer:
 
Fixed as of the upcoming 8.0.24 release, and here's the proposed changelog entry from the documentation team:

Initializing the server with an with a 4K InnoDB page size caused key too
long errors to be written to the error log. 

Thank you for the bug report.

Thank you, Daniel .....