MySQL Bugs: #23790: Calling mysqld while another instance is running, removes pid file.

Bug #23790	Calling mysqld while another instance is running, removes pid file.
Submitted:	31 Oct 2006 4:54	Modified:	3 Apr 2012 18:31
Reporter:	Sean Pringle	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.27 5.0.46	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	bfsm_2007_08_16

Description:
Running the mysqld binary directly with incorrect or missing arguments (ie -v vs -V), while another MySQL instance is already running, results in mysqld:

1) Deleting the pid file set by the first instance.
2) Trying to start.
3) Failing to bind to port 3306.
4) Aborting.
5) Deleting its own pid file.

Zero hostname.pid files now exist in the data directory.

How to repeat:
1) Install and start MySQL service.

2) /usr/local/mysql/bin/mysqld -v

  Note error here; -v should be -V

3) Watch mysqld start and die:

[ERROR] Can't start server: Bind on TCP/IP port: Address already in use
[ERROR] Do you already have another mysqld server running on por t: 3306 ?
[ERROR] Aborting
[Note] /usr/local/mysql/bin/mysqld: Shutdown complete

4) Pid file of first instance now missing from data directory.

Suggested fix:
mysqld could check for another instance's pid file before starting and spit out a helpful error message, leaving said pid file intact.

verified, pid file checking is the task of mysqld_safe, not mysqld itself, but still it shouldn't remove an existing pid file imho ...

probably sufficient to move the pid file handling to a later state?

I have attached a spec file for our group's custom mysql build of 4.1.18.  This will come up with a missing PID file error every time when it tries to start up mysql for real at the end of the %post section.  It may have something to do with the byzantine nature of my install but it seems more like a mysql bug.

mysqld does not drop a pid file until innodb files, socket and port are opened. 
This is especially obvious during crash recovery (no pid is dropped until innodb files recovered) and can lead to restart scripts attempting to start new instances (and failing at being able to open ibdata files)

Pushing the pid managment or locking mechanisim further up the chain should prevent both the original report, and the following behavior.

trace;

10257 mysqld NAMI "./ib_logfile1"
10257 mysqld RET open 4
-- 
10257 mysqld NAMI "./ibdata1"
10257 mysqld RET open 4
-- 
10257 mysqld NAMI "./ib_logfile0"
10257 mysqld RET open 9
-- 
10257 mysqld NAMI "./ib_logfile1"
10257 mysqld RET open 10/0xa
-- 
10257 mysqld NAMI "/var/tmp/ibzQRblH"
10257 mysqld RET open 11/0xb
-- 
10257 mysqld RET setsockopt 0
10257 mysqld CALL bind(0xb,0xbffffc04,0x10)
10257 mysqld RET bind 0
-- 
10257 mysqld RET umask 6
10257 mysqld CALL bind(0xd,0xbffffb98,0x6a)
10257 mysqld NAMI "/tmp/mysql.sock"
10257 mysqld RET bind 0
-- 
10257 mysqld CALL open(0x4cbbc0,0x601,0x1b4)
10257 mysqld NAMI "/usr/local/mysql/data/tape.local.pid"
10257 mysqld RET open 14/0xe

This may be fixed at the same time as Bug #41908, "pid file is created after InnoDB init".

See also Bug#41908

Setting the same lead as in Bug#41908

Bug #61700 was marked as duplicate of this one

Noted in 5.6.6 changelog.

mysqld during the startup process could incorrectly remove the PID
file of an already running mysqld.

Noted in 5.1.68, 5.5.30 changelogs.