2

I'm running Percona MySQL server 5.6 (from packages) on debian wheezy (same behavior on jessie). Unfortunately I always get this:

# service mysql stop
[FAIL] Stopping MySQL (Percona Server): mysqld failed!

I can say about my configuration that

  • /etc/init.d/mysql is untouched
  • /etc/mysql/debian.cnf contains valid and working (tested) credentials for debian-sys-maint

I investigated the the init script a little bit more and came to the conclusion that the problem seems to be mysqladmin.

The init script calls

mysqladmin --defaults-file=/etc/mysql/debian.cnf shutdown

After mysqladmin returns, it checks whether the server has been properly shut down. Now this seems to be the problem: mysqladmin returns BEFORE it has been shut down completely and then checks TOO EARLY that it is still running.

I was also watching the log files while investigating this. My finding:

2017-01-05 00:18:49 12595 [Note] InnoDB: Starting shutdown...
2017-01-05 00:18:49 7f70e4df7700 InnoDB: Dumping buffer pool(s) to .//ib_buffer_pool
2017-01-05 00:18:49 7f70e4df7700 InnoDB: Buffer pool(s) dump completed at 170105  0:18:49
2017-01-05 00:18:49 12595 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool

This is the moment mysqladmin returns. Still it continues:

2017-01-05 00:18:51 12595 [Note] InnoDB: Shutdown completed; log sequence number 120471740085
2017-01-05 00:18:51 12595 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2017-01-05 00:18:51 12595 [Note] Shutting down plugin 'ARCHIVE'
2017-01-05 00:18:51 12595 [Note] Shutting down plugin 'MyISAM'
2017-01-05 00:18:51 12595 [Note] Shutting down plugin 'MEMORY'
2017-01-05 00:18:51 12595 [Note] Shutting down plugin 'CSV'
2017-01-05 00:18:51 12595 [Note] Shutting down plugin 'MRG_MYISAM'
2017-01-05 00:18:51 12595 [Note] Shutting down plugin 'sha256_password'
2017-01-05 00:18:51 12595 [Note] Shutting down plugin 'mysql_old_password'
2017-01-05 00:18:51 12595 [Note] Shutting down plugin 'mysql_native_password'
2017-01-05 00:18:51 12595 [Note] Shutting down plugin 'binlog'
2017-01-05 00:18:51 12595 [Note] /usr/sbin/mysqld: Shutdown complete

Proof: I added "sleep 5" in my init script after the shutdown to delay the check - works.

Now my 2 questions: 1. Is the debian init script broken at this point? 2. Is there a way to configure the server in a way that mysqladmin will wait for the full shutdown?

turbophi
  • 71
  • 4
  • 2
    Take a look at the [source](https://github.com/mysql/mysql-server/blob/5.7/client/mysqladmin.cc). Add the `--verbose` option to `mysqladmin` and see if the message is logged about waiting for the pid file. It could be that something in the config incorrectly convinces `mysqladmin` that it isn't shutting down the *local* server (it can shutdown any server, remotely, if you have connectivity, valid credentials, and the necessary privilege), in which case, it doesn't watch the pid file and would very likely terminate sooner than you would expect. – Michael - sqlbot Jan 05 '17 at 03:18
  • 1
    Sounds reasonable, but: I tried on one server that is bound to 127.0.0.1 and on one server that is bound to a 10.* IP address. Same behavior on both. In /etc/mysql/debian.cnf the socket is configured (and it's the same as in /etc/mysql/my.cnf for [mysqld]). Any other idea? – turbophi Jan 05 '17 at 08:15
  • 1
    Did you add `--verbose` and see the output that should have been generated by `if (opt_verbose) printf("Shutdown signal sent to server; Waiting for pid file to disappear\n");`? – Michael - sqlbot Jan 05 '17 at 09:11
  • It doesn't output anything. – turbophi Jan 05 '17 at 09:24
  • 2
    Does `mysqladmin version` show "TCP Socket" or "Unix Socket" in its output? Looking at the source code, the pid file check doesn't happen unless the connection is using the Unix socket. – Michael - sqlbot Jan 05 '17 at 11:30
  • This is the solution. If /etc/mysql/debian.cnf contains an IP address, it will use TCP/IP, if it says "localhost" it will use the socket. Even defining "127.0.0.1" will switch from socket to TCP/IP. So knowing this behavior... the init script is correct. Thanks! – turbophi Jan 05 '17 at 13:10
  • Exactly where in the configuration file? The `[mysql]` section, `host = ...`? Or...? – Michael - sqlbot Jan 05 '17 at 13:16
  • [client] host = localhost socket = /var/.... Maybe you post that as the answer so I can accept that – turbophi Jan 05 '17 at 13:23
  • @turbophi can you try if you can reproduce with upstream MySQL package? if you can't reproduce this *may* be a bug and you can file one here https://bugs.launchpad.net/percona-server/+filebug – jerichorivera Jan 06 '17 at 06:45

1 Answers1

0

I came across a similar issue but with mysql 5.7, the issue I thought would would be, is MySQL not honouring the default shutdown-timeout of 3600 seconds . So when I used the following shutdown command the mysqladmin waited until the shutdown process completed successfully.

mysqladmin -u username -p password shutdown  --shutdown-timeout=3600

Added on 12-Mar-2019:

Mostly the issue was due to the following bug https://bugs.mysql.com/bug.php?id=91803 . After the addition of pid-file in my.cnf, the mysqladmin is waiting for the shutdown process to complete

jones1008
  • 3
  • 3
JOTHIBABU
  • 1
  • 1