MySQL Bugs: #85061: Data directory is not being initialized by "mysqld --initialize".

Bug #85061	Data directory is not being initialized by "mysqld --initialize".
Submitted:	18 Feb 2017 10:55	Modified:	24 Feb 2017 1:11
Reporter:	Dimitriy A	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7.17	OS:	MacOS (MacOS Sierra)
Assigned to:		CPU Architecture:	Any
Tags:	data directory, initialize, mysqld

Description:
Data directory is not being initialized by "mysqld --initialize". An empty data directory is created with some of the commands.

How to repeat:
Try any of the following commands:
/mysql/bin/mysql-5.7.17-macos10.12-x86_64/bin/mysqld --initialize --datadir=/mysql/mysql3308/data
/mysql/bin/mysql-5.7.17-macos10.12-x86_64/bin/mysqld --initialize --basedir=/mysql/bin/mysql-5.7.17-macos10.12-x86_64 --datadir=/mysql/mysql3308/data
/mysql/bin/mysql-5.7.17-macos10.12-x86_64/bin/mysqld --initialize --basedir=/mysql/bin/mysql-5.7.17-macos10.12-x86_64 --datadir=/mysql/mysql3308/data --defaults-file=/mysql/mysql3308/my.cnf
/mysql/bin/mysql-5.7.17-macos10.12-x86_64/bin/mysqld --initialize --basedir=/mysql/bin/mysql-5.7.17-macos10.12-x86_64 --datadir=/mysql/mysql3308/data --initialize-insecure
/mysql/bin/mysql-5.7.17-macos10.12-x86_64/bin/mysqld --initialize --defaults-file=/mysql/mysql3308/my.cnf

Suggested fix:
After wasting some time on this, it turns out that the following will work:
/mysql/bin/mysql-5.7.17-macos10.12-x86_64/bin/mysqld --defaults-file=/mysql/mysql3308/my.cnf --initialize

1) Please fix this by allowing options to be passed to mysqld in any order.
2) Provide verbose output with errors/warnings on why the data directory is being created empty.

--defaults-file must always be the first option specified, but strangely I don't see that documented at https://dev.mysql.com/doc/refman/5.7/en/option-file-options.html

I don't think this is abug if I don't have a my.cnf on my enviroment the --initialize alone works:

miguel@tikal:~/dbs/5.7 $ ls
bin/  COPYING  docs/  include/  lib/  man/  mysql-test/  README  share/  support-files/
miguel@tikal:~/dbs/5.7 $ ls
bin/  COPYING  docs/  include/  lib/  man/  mysql-test/  README  share/  support-files/
miguel@tikal:~/dbs/5.7 $ bin/mysqld --version
bin/mysqld  Ver 5.7.18 for Linux on x86_64 (Source distribution PULL: 2017-FEB-18)
miguel@tikal:~/dbs/5.7 $ bin/mysqld --initialize
2017-02-19T03:03:19.579819Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-02-19T03:03:19.579942Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2017-02-19T03:03:19.580207Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-02-19T03:03:20.814599Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-02-19T03:03:21.176579Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-02-19T03:03:21.374595Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: f885caee-f64f-11e6-96a0-ecf4bbfa653d.
2017-02-19T03:03:21.415630Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-02-19T03:03:21.415955Z 1 [Note] A temporary password is generated for root@localhost: #vsV#h/H+6QY
miguel@tikal:~/dbs/5.7 $ cd ..
miguel@tikal:~/dbs $ ./57c
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.18

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > set password='xxxxxxxxxx';
Query OK, 0 rows affected (0,00 sec)

mysql 5.7 > 

The way worked for you is explained in the mysqld --help --verbose:

miguel@tikal:~/dbs $ 5.7/bin/mysqld --help --verbose 
2017-02-19T22:57:07.693676Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
5.7/bin/mysqld  Ver 5.7.18-log for Linux on x86_64 (Source distribution PULL: 2017-FEB-18)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starts the MySQL database server.

Usage: 5.7/bin/mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 
The following groups are read: mysqld server mysqld-5.7
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file,
                        except for login file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#

A documentation bug instead explaining the case when a my.cnf file exists in the path.

Thank you for the bug report.

Posted by developer:
 
https://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html already says:

"Then invoke mysqld as follows (enter the command on a single line with the --defaults-file option first):"

First para of https://dev.mysql.com/doc/refman/5.7/en/option-file-options.html says:

"Most MySQL programs that support option files handle the following options. Because these options affect option-file handling, they must be given on the command line and not in an option file. To work properly, each of these options must be given before other options, with these exceptions:"

and --defaults-file is not one of the exceptions.

2020-04-26T15:42:44.827440Z 0 [Warning] [MY-010015] [Repl] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-04-26T15:42:44.880148Z 0 [Warning] [MY-010015] [Repl] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-04-26T15:42:44.920759Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-04-26T15:42:44.940239Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
2020-04-26T15:42:44.942311Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001146 - Table 'mysql.component' doesn't exist
2020-04-26T15:42:44.943095Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-003543 - The mysql.component table is missing or has an incorrect definition.
2020-04-26T15:42:44.946689Z 0 [ERROR] [MY-010326] [Server] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2020-04-26T15:42:44.947914Z 0 [ERROR] [MY-010952] [Server] The privilege system failed to initialize correctly. For complete instructions on how to upgrade MySQL to a new version please see the 'Upgrading MySQL' section from the MySQL manual.
2020-04-26T15:42:44.949535Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-04-26T15:42:45.829593Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19)  MySQL Community Server - GPL.