MySQL Bugs: #89411: initializing 8.0.4 fails to populate mysql directory

Bug #89411	initializing 8.0.4 fails to populate mysql directory
Submitted:	25 Jan 2018 14:47	Modified:	25 Jan 2018 19:03
Reporter:	IGG t	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S2 (Serious)
Version:	8.0.4	OS:	Windows (Windows 10)
Assigned to:		CPU Architecture:	Any
Tags:	8.0.4, initialize

Description:
Trying to setup a new instance of MySQL 8.0.4 on Windows 10.

Following the initialize process the data/mysql directory is empty, and mysql fails to start

How to repeat:

I download the mysql-8.0.4-rc-winx64.zip file, and copy the files to the C:/MySQL directory

I then initialize the new instance using:

c:/mysql/mysqld.exe --initialize --user=mysql --base-dir=c:/mysql/

on completion however, it turns out the the DATA/mysql directory is empty. So although the Service starts up, there is no way to log in to MySQL. Looking in the error log I can see:

[Warning] [MY-010952] The privilege system failed to initialize correctly.

https://bugs.mysql.com/bug.php?id=89382

Also read:
https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_pas...

Thank you for the bug report.

C:\>mkdir mysql

C:\>cd mysql

C:\mysql>mkdir data

C:\mysql>bin\mysqld --initialize-insecure --basedir=c:\mysql --datadir=c:\mysql\data

C:\mysql>bin\mysqld --standalone --console
2018-01-25T19:04:59.578884Z 0 [System] [MY-010116] C:\mysql\bin\mysqld.exe (mysqld 8.0.4-rc-log) starting as process 11472 ...
2018-01-25T19:05:01.214940Z 0 [Warning] [MY-010068] CA certificate ca.pem is self signed.
2018-01-25T19:05:01.264509Z 0 [System] [MY-010931] C:\mysql\bin\mysqld.exe: ready for connections. Version: '8.0.4-rc-log'  socket: ''  port: 3306  MySQL Community Server (GPL).

C:\>cd mysql

C:\mysql>dir data
 Volume in drive C has no label.
 Volume Serial Number is 7442-72F5

 Directory of C:\mysql\data

01/25/2018  05:05 PM    <DIR>          .
01/25/2018  05:05 PM    <DIR>          ..
01/25/2018  05:04 PM                56 auto.cnf
01/25/2018  05:05 PM               155 binlog.000001
01/25/2018  05:05 PM                16 binlog.index
01/25/2018  05:04 PM             1,676 ca-key.pem
01/25/2018  05:04 PM             1,116 ca.pem
01/25/2018  05:04 PM             1,116 client-cert.pem
01/25/2018  05:04 PM             1,680 client-key.pem
01/25/2018  05:05 PM        12,582,912 ibdata1
01/25/2018  05:04 PM        12,582,912 ibtmp1
01/25/2018  05:04 PM             6,406 ib_buffer_pool
01/25/2018  05:05 PM        50,331,648 ib_logfile0
01/25/2018  05:04 PM        50,331,648 ib_logfile1
01/25/2018  05:04 PM    <DIR>          mysql
01/25/2018  05:05 PM        25,165,824 mysql.ibd
01/25/2018  05:04 PM    <DIR>          performance_schema
01/25/2018  05:04 PM             1,680 private_key.pem
01/25/2018  05:04 PM               452 public_key.pem
01/25/2018  05:04 PM             1,116 server-cert.pem
01/25/2018  05:04 PM             1,676 server-key.pem
01/25/2018  05:04 PM    <DIR>          sys
01/25/2018  05:05 PM        10,485,760 undo_001
01/25/2018  05:05 PM        10,485,760 undo_002
01/25/2018  05:04 PM               383 ural.err
01/25/2018  05:05 PM                 6 ural.pid
              21 File(s)    171,983,998 bytes
               5 Dir(s)  107,358,765,056 bytes free

C:\mysql>bin\mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.4-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> set password='miguel';
Query OK, 0 rows affected (0.01 sec)

mysql>

I can confirm that this issue is happening to me with 8.0.4-rc on a Debian install.  Unfortunately, the workaround, --initialize-insecure is not working either.

I wonder if this is related: https://bugs.mysql.com/bug.php?id=89382

In my case I have a dual-boot setup (Win7+Win10) and the error happens on the Win10 partition named D:

reporter had a typo in his command.  there is not such option :
  --base-dir=c:/mysql/

and reporter didn't specific a datadir...
Also, c:/mysql/mysqld.exe  is not a valid path.

Same issue on CentOS 7 when installing as a non-privileged user in home directory.

$ mysqld --initialize --basedir=/home/motif/mysql8 --datadir=/home/motif/mysql8/data --tmpdir=/home/motif/mysql8/tmp --user=motif
2018-04-21T21:23:18.587656Z 0 [Warning] [MY-010140] [Server] Could not increase number of max_open_files to more than 20000 (request: 150000)
2018-04-21T21:23:18.588667Z 0 [Warning] [MY-010143] [Server] Ignoring user change to 'motif' because the user was set to 'mysql' earlier on the command line
2018-04-21T21:23:18.588863Z 0 [System] [MY-013169] [Server] /home/motif/mysql8/bin/mysqld (mysqld 8.0.11) initializing of server in progress as process 12266
2018-04-21T21:23:18.594646Z 0 [Warning] [MY-010122] [Server] One can only use the --user switch if running as root
2018-04-21T21:23:18.602668Z 0 [Warning] [MY-012364] [InnoDB] InnoDB: innodb_open_files should not be greater than the open_files_limit.

2018-04-21T21:23:21.013169Z 0 [ERROR] [MY-011071] [Server] unknown variable 'query_cache_type=1'
2018-04-21T21:23:21.013237Z 0 [Warning] [MY-010952] [Server] The privilege system failed to initialize correctly. If you have upgraded your server, make sure you're executing mysql_upgrade to correct the issue.
2018-04-21T21:23:21.013258Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-04-21T21:23:22.089409Z 0 [System] [MY-010910] [Server] /home/motif/mysql8/bin/mysqld: Shutdown complete (mysqld 8.0.11)  Source distribution.

====================

$ mysqld --initialize --basedir=/home/motif/mysql8 --datadir=/home/motif/mysql8/data --tmpdir=/home/motif/mysql8/tmp --defaults-file=/home/motif/mysql8/etc/my.cnf 
2018-04-21T21:28:12.014988Z 0 [Warning] [MY-010140] [Server] Could not increase number of max_open_files to more than 20000 (request: 150000)
2018-04-21T21:28:12.016120Z 0 [System] [MY-013169] [Server] /home/motif/mysql8/bin/mysqld (mysqld 8.0.11) initializing of server in progress as process 12941
2018-04-21T21:28:12.021625Z 0 [Warning] [MY-010122] [Server] One can only use the --user switch if running as root
2018-04-21T21:28:12.029383Z 0 [Warning] [MY-012364] [InnoDB] InnoDB: innodb_open_files should not be greater than the open_files_limit.

2018-04-21T21:28:13.867154Z 0 [ERROR] [MY-011071] [Server] unknown variable 'query_cache_type=1'
2018-04-21T21:28:13.867207Z 0 [Warning] [MY-010952] [Server] The privilege system failed to initialize correctly. If you have upgraded your server, make sure you're executing mysql_upgrade to correct the issue.
2018-04-21T21:28:13.867226Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-04-21T21:28:15.242341Z 0 [System] [MY-010910] [Server] /home/motif/mysql8/bin/mysqld: Shutdown complete (mysqld 8.0.11)  Source distribution.

========================

$ mysqld_safe --defaults-file=$HOME/mysql8/etc/my.cnf --plugin-dir=/home/motif/mysql8/lib/plugin --log-error=/home/motif/mysql8/logs/mysqld.log --pid-file=/home/motif/mysql8/mysql.pid
2018-04-22T01:21:37.441926Z mysqld_safe Logging to '/home/motif/mysql8/logs/mysqld.log'.
2018-04-22T01:21:37.563609Z mysqld_safe Starting mysqld daemon with databases from /home/motif/mysql8/data

$ cat mysqld.log 
2018-04-22T01:21:37.441926Z mysqld_safe Logging to '/home/motif/mysql8/logs/mysqld.log'.
2018-04-22T01:21:37.563609Z mysqld_safe Starting mysqld daemon with databases from /home/motif/mysql8/data
2018-04-22T01:21:38.139391Z 0 [System] [MY-010116] [Server] /home/motif/mysql8/bin/mysqld (mysqld 8.0.11) starting as process 12553
mysqld: Table 'mysql.plugin' doesn't exist
2018-04-22T01:21:38.828022Z 0 [ERROR] [MY-010735] [Server] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2018-04-22T01:21:38.865811Z 0 [System] [MY-010229] [Server] Starting crash recovery...
2018-04-22T01:21:38.865955Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2018-04-22T01:21:38.873228Z 0 [Warning] [MY-010015] [Repl] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-04-22T01:21:38.875676Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2018-04-22T01:21:38.877918Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
2018-04-22T01:21:38.878329Z 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
2018-04-22T01:21:38.878402Z 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.
2018-04-22T01:21:38.879589Z 0 [ERROR] [MY-010326] [Server] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2018-04-22T01:21:38.879705Z 0 [Warning] [MY-010952] [Server] The privilege system failed to initialize correctly. If you have upgraded your server, make sure you're executing mysql_upgrade to correct the issue.
2018-04-22T01:21:38.880110Z 0 [Warning] [MY-010357] [Server] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
2018-04-22T01:21:38.881016Z 0 [ERROR] [MY-010353] [Server] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
2018-04-22T01:21:38.882410Z 0 [Warning] [MY-010405] [Repl] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2018-04-22T01:21:38.882476Z 0 [ERROR] [MY-010422] [Repl] Error in checking mysql.slave_master_info repository info type of TABLE.
2018-04-22T01:21:38.882515Z 0 [ERROR] [MY-010415] [Repl] Error creating master info: Error checking repositories.
2018-04-22T01:21:38.882532Z 0 [ERROR] [MY-010426] [Repl] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
2018-04-22T01:21:38.882555Z 0 [ERROR] [MY-010529] [Repl] Failed to create or recover replication info repositories.
2018-04-22T01:21:38.883034Z 0 [System] [MY-010931] [Server] /home/motif/mysql8/bin/mysqld: ready for connections. Version: '8.0.11'  socket: '/home/motif/mysql8/mysql.sock'  port: 18868  Source distribution.

>
>    [ERROR] [MY-011071] [Server] unknown variable 'query_cache_type=1'
>

Remove that from my.cnf....

moving to https://bugs.mysql.com/bug.php?id=90557

bug attached