MySQL Bugs: #87287: 5.7 startup scripts always initialize if empty datadir

Bug #87287	5.7 startup scripts always initialize if empty datadir
Submitted:	2 Aug 2017 19:20	Modified:	6 Oct 2017 18:02
Reporter:	Matthew Boehm	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S4 (Feature request)
Version:	5.7	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	MySQL, systemd

Description:
In RHEL7/CentOS7 (presumably any system that uses systemd/systemctl scripts), if the $datadir is empty, mysql will --initialize a fresh setup.

Pretend now that my $datadir is a remote mount (iSCSI, NFS, encrypted). If the mount is not present, the $datadir appears empty. When mysql starts (due to server restart say), a new data dir will be --initialized. This would give the appearance that mysql is “up” but all of the data is gone.

How can one prevent this? Is there a flag that says “do not auto-detect initialize?”

There needs to be some reliable way of only running --initialize absolutely only once.

How to repeat:
mount a partition to $datadir
Run mysql on above empty $datadir
stop mysql
unmount $datadir
Run mysql ($datadir detected as empty and new database is initialized)

Suggested fix:
turn off --initialize by default and instead print error that says "Your $datadir appears empty. You will need to --initialize it or restore an existing working datadir."

Just create a file in $datadir when the remote mounting is not done and you should be set or?

I like Matthew's idea about having to run the init manually. It can still run automatically when it's installed (as part of the packaging script) I suppose, but otherwise it may be a good thing not to do this automatically (if it is - not checked)?

@Terje, how would you accomplish that in an automated manner? Say your mount suddenly disappears (yes, speaking from experience) and MySQL crashes. systemd will auto-restart it. But now, the datadir is empty cause the mount is gone but the dir remains. How will you auto-create this file if the mount goes away to prevent unintended initialize?

Can this feature request be verified please?

Posted by developer:
 
Fixed in 5.7.20, 8.0.4.

For platforms that use systemd, the data directory is initialized if
empty at server startup. This might be a problem if the data
directory is a remote mount that has temporarily disappeared: The
mount point would appear to be an empty data directory, which then
would be initialized as a new data directory. It is now possible to
suppress this automatic initialization behavior. specify the
following line in the /etc/sysconfig/mysql file (create the file if
it does not exist):

O_INIT=true

Typo in previous message. /etc/sysconfig/mysql should be:

NO_INIT=true