MySQL Bugs: #90557: Initializing 8.0.11 fails to populate data directory and mysql system database

Bug #90557	Initializing 8.0.11 fails to populate data directory and mysql system database
Submitted:	22 Apr 2018 16:07	Modified:	23 Apr 2018 15:59
Reporter:	Muhammad Perreira	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	8.0.11	OS:	CentOS (El7)
Assigned to:		CPU Architecture:	x86 (Linux 3.10.0-229.1.2.el7.x86_64 x86_64)
Tags:	initialize, MySQL

Description:
Trying to setup a new instance of MySQL 8.0.11 on CentOS 7 as non-root / non-privileged user.

Following the initialize process, and although the data directory is populated, some tables are missing in MySQL server. Therefore i'm unable to run mysql_secure_installation to set the root password.

$ pwd
/home/motif/mysql8/data

$ ll
total 125004
-rw-r----- 1 motif motif       56 Apr 21 22:08 auto.cnf
-rw-r----- 1 motif motif      155 Apr 21 22:14 binlog.000001
-rw-r----- 1 motif motif      155 Apr 21 22:20 binlog.000002
-rw-r----- 1 motif motif      155 Apr 22 01:04 binlog.000003
-rw-r----- 1 motif motif      155 Apr 22 01:21 binlog.000004
-rw-r----- 1 motif motif      155 Apr 22 01:21 binlog.000005
-rw-r----- 1 motif motif       80 Apr 22 01:21 binlog.index
-rw------- 1 motif motif     1676 Apr 21 22:08 ca-key.pem
-rw-r--r-- 1 motif motif     1112 Apr 21 22:08 ca.pem
-rw-r--r-- 1 motif motif     1112 Apr 21 22:08 client-cert.pem
-rw------- 1 motif motif     1676 Apr 21 22:08 client-key.pem
-rw-r----- 1 motif motif     3340 Apr 21 22:08 ib_buffer_pool
-rw-r----- 1 motif motif 33554432 Apr 22 01:21 ib_logfile0
-rw-r----- 1 motif motif 33554432 Apr 21 22:08 ib_logfile1
-rw-r----- 1 motif motif 12582912 Apr 22 01:21 ibdata1
-rw-r----- 1 motif motif 12582912 Apr 22 01:21 ibtmp1
drwxr-x--- 2 motif motif     4096 Apr 21 22:08 mysql
-rw-r----- 1 motif motif 14680064 Apr 22 01:21 mysql.ibd
drwxr-x--- 2 motif motif     4096 Apr 21 22:08 performance_schema
-rw------- 1 motif motif     1676 Apr 21 22:08 private_key.pem
-rw-r--r-- 1 motif motif      452 Apr 21 22:08 public_key.pem
-rw-r--r-- 1 motif motif     1112 Apr 21 22:08 server-cert.pem
-rw------- 1 motif motif     1680 Apr 21 22:08 server-key.pem
-rw-r----- 1 motif motif 10485760 Apr 22 01:21 undo_001
-rw-r----- 1 motif motif 10485760 Apr 22 01:21 undo_002

How to repeat:
$ 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.

You should try fix your my.cnf and comment out the query cache variables,  or start mysqld with --no-defaults as the first option so that my.cnf is ignored..

>
>  2018-04-21T21:23:21.013169Z 0 [ERROR] [MY-011071] [Server] unknown variable 'query_cache_type=1'
>

Please try what Shane asked. Thanks.

Thanks for your help! However this doesn't resolve the issue.

query_cache_type has always been set to off.

No defaults:

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

2018-04-23T15:06:47.816174Z 0 [ERROR] [MY-011071] [Server] unknown variable 'query_cache_type=1'
2018-04-23T15:06:47.816234Z 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-23T15:06:47.816253Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-04-23T15:06:49.196406Z 0 [System] [MY-010910] [Server] /home/motif/mysql8/bin/mysqld: Shutdown complete (mysqld 8.0.11)  Source distribution.

With defaults:

$ 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-23T15:05:07.180204Z 0 [Warning] [MY-010140] [Server] Could not increase number of max_open_files to more than 20000 (request: 150000)
2018-04-23T15:05:07.181477Z 0 [System] [MY-013169] [Server] /home/motif/mysql8/bin/mysqld (mysqld 8.0.11) initializing of server in progress as process 26946
2018-04-23T15:05:07.187299Z 0 [Warning] [MY-010122] [Server] One can only use the --user switch if running as root
2018-04-23T15:05:07.195284Z 0 [Warning] [MY-012364] [InnoDB] InnoDB: innodb_open_files should not be greater than the open_files_limit.

2018-04-23T15:05:09.428034Z 0 [ERROR] [MY-011071] [Server] unknown variable 'query_cache_type=1'
2018-04-23T15:05:09.428119Z 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-23T15:05:09.428151Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-04-23T15:05:10.835599Z 0 [System] [MY-010910] [Server] /home/motif/mysql8/bin/mysqld: Shutdown complete (mysqld 8.0.11)  Source distribution.

Basic my.cnf file:

$ cat etc/my.cnf 
[mysqld]
query_cache_type=0
port=18868
datadir=/home/motif/mysql8/data
basedir=/home/motif/mysql8
max_allowed_packet=16M
user=motif
socket=/home/motif/mysql8/mysql.sock
tmpdir=/home/motif/mysql8/tmp

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

Sorry, but this is not a bug. Query cache options need to be removed now.

https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/

furthermore, --defaults-file and --no-defaults must always be the first options provided.

Alright, I commented query_cache_type and ran the following:

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

2018-04-23T15:31:08.730201Z 0 [ERROR] [MY-011071] [Server] unknown variable 'query_cache_type=1'
2018-04-23T15:31:08.730262Z 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-23T15:31:08.730282Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-04-23T15:31:09.923687Z 0 [System] [MY-010910] [Server] /home/motif/mysql8/bin/mysqld: Shutdown complete (mysqld 8.0.11)  Source distribution.

$ cat etc/my.cnf 
[mysqld]
#query_cache_type=0
port=18868
datadir=/home/motif/mysql8/data
basedir=/home/motif/mysql8
max_allowed_packet=16M
user=motif
socket=/home/motif/mysql8/mysql.sock
tmpdir=/home/motif/mysql8/tmp

This worked though:

$ mysqld --no-defaults --initialize --basedir=/home/motif/mysql8 --datadir=/home/motif/mysql8/data --tmpdir=/home/motif/mysql8/tmp --port=18868 --user=motif
2018-04-23T15:30:18.478683Z 0 [System] [MY-013169] [Server] /home/motif/mysql8/bin/mysqld (mysqld 8.0.11) initializing of server in progress as process 30093
2018-04-23T15:30:20.980702Z 4 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: a9f_.lrpOK3=
2018-04-23T15:30:22.928402Z 0 [System] [MY-013170] [Server] /home/motif/mysql8/bin/mysqld (mysqld 8.0.11) initializing of server has completed

So why by default is query_cache_type being called and it's been removed in MySQL 8? I only added query_cache_type=0 to my.cnf because of the error message.

Thanks

Recommend to search for other my.cnf's on your system.

mysqld --help --verbose|grep "cnf"

should show some locations of other my.cnf that is being read.
Make sure they also don't contain query cache paraphernalia .

@Shane Bester thanks for your help. It seems it was reading it from the system-wide v5.7 install.

This can be closed now.

Many thanks