MySQL Bugs: #108016: 8.0.30 initialization is broken with non-default innodb_log_group_home

Bug #108016	8.0.30 initialization is broken with non-default innodb_log_group_home_dir
Submitted:	28 Jul 2022 17:22	Modified:	29 Jul 2022 15:46
Reporter:	Matt Lord	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	8.0.30	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
You cannot initialize 8.0.30 when specifying innodb_log_group_home_dir.

I marked this as S1/Critical because I think this will also cause upgrades to fail. Both issues are pretty severe IMO.

This is tangentially related to: https://bugs.mysql.com/bug.php?id=108012

How to repeat:
$ mysqld --initialize-insecure --datadir=/usr/local/mysql/data --innodb_log_group_home_dir=/usr/local/mysql/data/innodb_logs 

2022-07-28T17:15:32.109331Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.30-macos12-arm64/bin/mysqld (mysqld 8.0.30) initializing of server in progress as process 32929
2022-07-28T17:15:32.111786Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2022-07-28T17:15:32.114185Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-07-28T17:15:32.139772Z 1 [ERROR] [MY-013861] [InnoDB] Failed to list redo log files in the redo log directory /usr/local/mysql/data/innodb_logs/
2022-07-28T17:15:32.139803Z 1 [ERROR] [MY-012929] [InnoDB] InnoDB Database creation was aborted with error not found. You may need to delete the ibdata1 file before trying to start up again.
2022-07-28T17:15:32.140308Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-07-28T17:15:32.140317Z 0 [ERROR] [MY-013236] [Server] The designated data directory /usr/local/mysql/data/ is unusable. You can remove all files that the server added to it.
2022-07-28T17:15:32.140322Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-07-28T17:15:32.140556Z 0 [System] [MY-010910] [Server] /usr/local/mysql-8.0.30-macos12-arm64/bin/mysqld: Shutdown complete (mysqld 8.0.30)  MySQL Community Server - GPL.

Suggested fix:
Expand the test suite to test with non-default values for various parameters.

Another example, FWIW:
$ mysqld --initialize-insecure --datadir=/usr/local/mysql/data --innodb_log_group_home_dir=/usr/local/mysql/innodb_logs

2022-07-28T17:20:32.864169Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.30-macos12-arm64/bin/mysqld (mysqld 8.0.30) initializing of server in progress as process 33096
2022-07-28T17:20:32.866468Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2022-07-28T17:20:32.869139Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-07-28T17:20:32.892936Z 1 [ERROR] [MY-013861] [InnoDB] Failed to list redo log files in the redo log directory /usr/local/mysql/innodb_logs/
2022-07-28T17:20:32.895236Z 1 [ERROR] [MY-012929] [InnoDB] InnoDB Database creation was aborted with error not found. You may need to delete the ibdata1 file before trying to start up again.
2022-07-28T17:20:32.895733Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-07-28T17:20:32.895741Z 0 [ERROR] [MY-013236] [Server] The designated data directory /usr/local/mysql/data/ is unusable. You can remove all files that the server added to it.
2022-07-28T17:20:32.895745Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-07-28T17:20:32.895951Z 0 [System] [MY-010910] [Server] /usr/local/mysql-8.0.30-macos12-arm64/bin/mysqld: Shutdown complete (mysqld 8.0.30)  MySQL Community Server - GPL.

One last example (not using a subdirectory of the datadir:

$  mysqld --initialize-insecure --datadir=/tmp/mysql/data --innodb_log_group_home_dir=/tmp/innodb/logs

2022-07-28T17:34:26.354590Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.30-macos12-arm64/bin/mysqld (mysqld 8.0.30) initializing of server in progress as process 33608
2022-07-28T17:34:26.356505Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /tmp/mysql/data/ is case insensitive
2022-07-28T17:34:26.359398Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-07-28T17:34:26.383408Z 1 [ERROR] [MY-013861] [InnoDB] Failed to list redo log files in the redo log directory /tmp/innodb/logs/
2022-07-28T17:34:26.385760Z 1 [ERROR] [MY-012929] [InnoDB] InnoDB Database creation was aborted with error not found. You may need to delete the ibdata1 file before trying to start up again.
2022-07-28T17:34:26.386274Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-07-28T17:34:26.386282Z 0 [ERROR] [MY-013455] [Server] The newly created data directory /tmp/mysql/data/ by --initialize is unusable. You can remove it.
2022-07-28T17:34:26.386309Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-07-28T17:34:26.386542Z 0 [System] [MY-010910] [Server] /usr/local/mysql-8.0.30-macos12-arm64/bin/mysqld: Shutdown complete (mysqld 8.0.30)  MySQL Community Server - GPL.

Ahhhhh, so if you specify the innodb-data-dir option you get a helpful hint:
$mysqld --initialize-insecure --datadir=/tmp/mysql/data --innodb-data-home-dir=/tmp/innodb/data --innodb_log_group_home_dir=/tmp/innodb/logs --innodb-tmpdir=/tmp/innodb/tmp

2022-07-28T18:09:06.936680Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.30-macos12-arm64/bin/mysqld (mysqld 8.0.30) initializing of server in progress as process 35686
2022-07-28T18:09:06.938147Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /tmp/mysql/data/ is case insensitive
2022-07-28T18:09:06.940207Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-07-28T18:09:06.947855Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2022-07-28T18:09:06.947871Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2022-07-28T18:09:06.947875Z 1 [ERROR] [MY-012594] [InnoDB] If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2022-07-28T18:09:06.947880Z 1 [ERROR] [MY-012646] [InnoDB] File /tmp/innodb/data/ibdata1: 'create' returned OS error 71. Cannot continue operation
2022-07-28T18:09:06.947887Z 1 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.

Specifically:
If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.

So the problem was that I didn’t pre-create the innodb_log_group_home_dir. I'll close this ticket then, sorry for the noise. 

I'll file a low priority request to add that same kind of helpful error message for the other innodb directories as well (seems to only get printed for the innodb-data-home-dir.

Hello Matt,

Thank you for the report and feedback.
As you rightly figured already, InnoDB does not create directories and thus it was causing problem.  I see note is already added in the doc page - https://dev.mysql.com/doc/refman/5.7/en/innodb-init-startup-configuration.html#innodb-star....

I'll close the bug for now but feel free to open if you think any enhancement to the docs required in this regard.

Sincerely,
Umesh

Thanks, Umesh! I do see the doc note on that now.