[Bug 326768] Re: mysqld_safe thinks mysqld has crashed when it hasn't

[Mathias Gug]

** Description changed:

  --Impact--
  I'm running mythtv on jaunty with mysql-server-5.0 version 5.1.30really5.0.75-0ubuntu5.  During boot mysql starts, then mythtv starts, then mysql restarts and mythtv gets confused.
  
  This is caused by some portions of a debian patch that is applied on top
  of MySQL.  It has not been accepted yet at upstream MySQL.  The MySQL
  server is receiving a SIGHUP which the behavior is changed because of
  the debian patch.
  
  Here are some relevant syslog sections:
  
  Feb  8 12:35:07 willvo mysqld_safe[3668]: started
  Feb  8 12:35:08 willvo mysqld[3671]: 090208 12:35:08  InnoDB: Started; log sequence number 0 43655
  Feb  8 12:35:08 willvo mysqld[3671]: 090208 12:35:08 [Note] /usr/sbin/mysqld: ready for connections.
  Feb  8 12:35:08 willvo mysqld[3671]: Version: '5.0.75-0ubuntu5'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
  Feb  8 12:35:08 willvo /etc/mysql/debian-start[3710]: Upgrading MySQL tables if necessary.
  Feb  8 12:35:08 willvo /etc/mysql/debian-start[3713]: Looking for 'mysql' as: /usr/bin/mysql
  Feb  8 12:35:08 willvo /etc/mysql/debian-start[3713]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
  Feb  8 12:35:08 willvo /etc/mysql/debian-start[3713]: This installation of MySQL is already upgraded to 5.0.75, use --force if you still need to run mysql_upgrade
  Feb  8 12:35:08 willvo /etc/mysql/debian-start[3720]: Checking for insecure root accounts.
  Feb  8 12:35:08 willvo /etc/mysql/debian-start[3724]: Triggering myisam-recover for all MyISAM tables
  
  Feb  8 12:35:13 willvo mythtv-backend[4560]: Started mythtv-backend
  
  Feb  8 12:35:16 willvo mysqld_safe[5212]: Number of processes running now: 1
  Feb  8 12:35:16 willvo mysqld_safe[5223]: mysqld process hanging, pid 3670 - killed
  Feb  8 12:35:16 willvo mysqld_safe[5227]: restarted
  Feb  8 12:35:16 willvo mysqld[5231]: 090208 12:35:16  InnoDB: Started; log sequence number 0 43655
  Feb  8 12:35:17 willvo mysqld[5231]: 090208 12:35:17 [Note] /usr/sbin/mysqld: ready for connections.
  Feb  8 12:35:17 willvo mysqld[5231]: Version: '5.0.75-0ubuntu5'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
  
  Note that mysqld_safe is finds one mysqld process still running after it
  is supposed to have crashed.  mysqld_safe then kills that process and
  starts another.  This only seems to happen once during boot - it doesn't
  keep restarting mysqld in normal use.
  
  --Addressing--
  This has *not* yet been addressed in the karmic branch, but it has been verified that a PPA resolve the problem.  MySQL hasn't changed yet in karmic, so this can easily be brought to karmic if viewed to properly solve the problem.
  
- --Test Case - Myth
- To reproduce this, you can boot up off of a fresh install of Mythbuntu 9.04 (which includes MySQL and mythtv-backend preinstalled).  Check /var/log/syslog and you will see errors regarding mysql getting restarted because of a hanging process.  Depending on the speed of your system, this may or may not cause problems with mythtv-backend because of the race condition inherent in this problem.
+ The problem comes from the fact that mysqld_safe starts mysqld and then
+ waits for its crash. However installing a trap for SIGHUP makes the wait
+ command return immediately when a SIGHUP is received by mysqld_safe.
+ This mysqld_safe proceed to kill the remaining mysqld process (which
+ hasn't crashed). The proposed fix is to add a wait command to the trap.
  
- -- Test Case - non-Myth
- sudo killall -HUP mysqld_safe       # should cause mysql to reload its config, but causes it to restart instead.
+ -- Test Case
+ 1. Install mysql-server-5.0 and watch the log. Record the PID of mysqld. Issue a status command to see how many flush tables have been done:
+ $ sudo /usr/bin/mysqladmin --defaults-extra-file=/etc/mysql/debian.cnf status
+ Uptime: 36 Threads: 1 Questions: 1 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 6 Queries per second avg: 0.028
+ 
+ 2. From a terminal: 
+   sudo killall -HUP mysqld_safe
+ 
+ 3. 
+  a. WIthtout the patch: mysqld_safe reports that a mysqld process is stuck, kills it and restarts it. This can be verified by checking that mysqld has new a PID.
+  b. With the patch: mysqld is *not* restarted. mysqld has the same PID. Only the refresh command is sent to mysqld. This can be verified by checking that the number of flush tables has been incremented by one.
  
  --Regression Potential--
- This type of patch has implications if users were dependent on the behavior of this debian/ubuntu specific patch to issue a mysql refresh via a SIGHUP.
+ There isn't any change in behavior. Signals are still handled the way they were before.

** Tags removed: verification-failed

** Changed in: mysql-dfsg-5.0 (Ubuntu Karmic)
       Status: Confirmed => In Progress

** Changed in: mysql-dfsg-5.0 (Ubuntu Karmic)
     Assignee: Canonical Server Team (canonical-server) => Mathias Gug (mathiaz)

** Changed in: mysql-dfsg-5.0 (Ubuntu Jaunty)
       Status: Confirmed => In Progress

** Changed in: mysql-dfsg-5.0 (Ubuntu Jaunty)
     Assignee: (unassigned) => Mathias Gug (mathiaz)

-- 
mysqld_safe thinks mysqld has crashed when it hasn't
https://bugs.launchpad.net/bugs/326768
You received this bug notification because you are a member of Ubuntu
Server Team, which is subscribed to mysql-dfsg-5.0 in ubuntu.