[ubuntu-us-mi] How to shut down MySQL on Ubuntu Lucid 10.04?

[Robert Citek]

How does one gracefully shut down MySQL on Ubuntu Lucid 10.04?

I cannot get MySQL to shutdown gracefully. ?It keeps restarting:

# ( ?set -x ; sudo mysqladmin status ; sleep 10 ; sudo mysqladmin
shutdown ; sleep 10 ; sudo mysqladmin status )
+ sudo mysqladmin status
Uptime: 92 ?Threads: 1 ?Questions: 7882 ?Slow queries: 1 ?Opens: 71
Flush tables: 1 ?Open tables: 64 ?Queries per second avg: 85.673
+ sleep 10
+ sudo mysqladmin shutdown
+ sleep 10
+ sudo mysqladmin status
Uptime: 11 ?Threads: 1 ?Questions: 919 ?Slow queries: 1 ?Opens: 49
Flush tables: 1 ?Open tables: 42 ?Queries per second avg: 83.545

This happens despite commenting out the respawn line in the upstart script:

$ grep respawn /etc/init/mysql.conf
# respawn

Using 'sudo /etc/init.d/mysql stop' does stop mysql at the expense of
sending mysqld a KILL after 5 seconds, thus corrupting the innodb
tables.

I found this bug report, which seems close, but not quite what I'm looking for:

https://bugs.launchpad.net/ubuntu/maverick/+source/mysql-dfsg-5.1/+bug/620441

Any pointers greatly appreciated.

Regards,
- Robert

[Craig Maloney]

Post by Robert Citek
How does one gracefully shut down MySQL on Ubuntu Lucid 10.04?

Best way is to use the service command:

sudo service mysql stop

Conversely, to start mysql, use:

sudo service mysql start

Hope this helps!

[Robert Citek]

Post by Craig Maloney

Post by Robert Citek
How does one gracefully shut down MySQL on Ubuntu Lucid 10.04?

sudo service mysql stop

That's the problem. The "best" way corrupts the tables. Even setting
the kill timeout to 300 doesn't change the default behaviour of
sending a KILL signal after 5 seconds:

$ grep timeout /etc/init/mysql.conf
kill timeout 300

[Robert Citek]

Post by Craig Maloney

Post by Robert Citek
How does one gracefully shut down MySQL on Ubuntu Lucid 10.04?

sudo service mysql stop

That's the problem. ?The "best" way corrupts the tables. ?Even setting
the kill timeout to 300 doesn't change the default behaviour of

Here's a more undesirable entry from the error.logs:
...
101216 17:13:40 [ERROR] /usr/sbin/mysqld: Table './biz_crm/basic' is
marked as crashed and should be repaired
101216 17:13:40 [Warning] Checking table: './biz_crm/basic'
101216 17:13:40 [ERROR] 1 client is using or hasn't closed the table properly
...

biz_crm/basic table is about 30 GB in size. So this is going to take
a while. And that "crash" is from doing a "sudo service mysql stop".

Regards,
- Robert

[Craig Maloney]

Post by Robert Citek

Post by Craig Maloney

Post by Robert Citek
How does one gracefully shut down MySQL on Ubuntu Lucid 10.04?

sudo service mysql stop

That's the problem. The "best" way corrupts the tables. Even setting
the kill timeout to 300 doesn't change the default behaviour of

If you're having problems with InnoDB corrupting tables, then your
problem isn't with the start / stop scripts... something isn't getting
shut down properly on that machine, or the files themselves have
corruption issues. I'd recommend looking further down the chain to see
what the problem is. Also, have you run a fsck on the filesystem that
the data files are on, lately? (full fsck, not just journal fsck).

Good luck. Sounds like a mess. :)

[Robert Citek]

Post by Craig Maloney

Post by Craig Maloney

Post by Robert Citek
How does one gracefully shut down MySQL on Ubuntu Lucid 10.04?

sudo service mysql stop

That's the problem. ?The "best" way corrupts the tables. ?Even setting
the kill timeout to 300 doesn't change the default behaviour of

If you're having problems with InnoDB corrupting tables, then your
problem isn't with the start / stop scripts... something isn't getting
shut down properly on that machine,

"MySQL upstart stop job does not cleanly shutdown mysql "

https://bugs.launchpad.net/ubuntu/maverick/+source/mysql-dfsg-5.1/+bug/620441

Post by Craig Maloney
Good luck. Sounds like a mess. :)

Indeed. It's three issues rolled into one:

1) mysqladmin shuts down cleanly but upstart starts mysql up again
because of the respawn command
2) upstart's mysql script has a default of 5 seconds, which is too
short, resulting in upstart killing mysqld and corrupting tables
3) upstarts doesn't reread changes to the mysql script, including
changes to the respawn option and kill timeout.

It's making a few of us here lose a bit of faith in upstart.

Regards,
- Robert

[mike]

Maybe this can help.
http://upstart.ubuntu.com/faq.html#reload

Post by Robert Citek

Post by Craig Maloney

Post by Robert Citek

Post by Craig Maloney

Post by Robert Citek
How does one gracefully shut down MySQL on Ubuntu Lucid 10.04?

sudo service mysql stop

That's the problem. The "best" way corrupts the tables. Even setting
the kill timeout to 300 doesn't change the default behaviour of

If you're having problems with InnoDB corrupting tables, then your
problem isn't with the start / stop scripts... something isn't getting
shut down properly on that machine,

"MySQL upstart stop job does not cleanly shutdown mysql "
https://bugs.launchpad.net/ubuntu/maverick/+source/mysql-dfsg-5.1/+bug/620441

Post by Craig Maloney
Good luck. Sounds like a mess. :)

1) mysqladmin shuts down cleanly but upstart starts mysql up again
because of the respawn command
2) upstart's mysql script has a default of 5 seconds, which is too
short, resulting in upstart killing mysqld and corrupting tables
3) upstarts doesn't reread changes to the mysql script, including
changes to the respawn option and kill timeout.
It's making a few of us here lose a bit of faith in upstart.
Regards,
- Robert

[Robert Citek]

Post by mike
Maybe this can help.
http://upstart.ubuntu.com/faq.html#reload

Thanks, except that is also fraught with issues.

"If a job is running, the changes to the definition will not occur
until the job has been stopped;"

And I can't stop the job (mysql in this case) because upstart will
cause the tables to get corrupted by killing mysqld too soon.

"To force a reload you may send init the HUP signal, or use the
following command: initctl reload "

Unfortunately, that reload doesn't work for me:

$ sudo initctl reload
initctl: missing job name
Try `initctl --help' for more information.

If I specify mysql as the job, that seems to be equivalent to
/etc/init.d/mysql reload. The man page for initctl seems to confirm
this:

reload JOB [KEY=VALUE]...

Sends the SIGHUP signal to running process of the named
JOB instance.

So a reload sends SIGHUP to the JOB and not to init.

The one thing I have not tried is 'kill -SIGHUP 1". I'm going to
setup a VM before I try that one on our servers.

Regards,
- Robert

[David Barnett]

Is there anything in the mysql logs? It almost sounds likethe database is
ignoring the stop command. Maybe a permissions issue?

--dave

Post by Robert Citek

Post by mike
Maybe this can help.
http://upstart.ubuntu.com/faq.html#reload

Thanks, except that is also fraught with issues.
"If a job is running, the changes to the definition will not occur
until the job has been stopped;"
And I can't stop the job (mysql in this case) because upstart will
cause the tables to get corrupted by killing mysqld too soon.
"To force a reload you may send init the HUP signal, or use the
following command: initctl reload "
$ sudo initctl reload
initctl: missing job name
Try `initctl --help' for more information.
If I specify mysql as the job, that seems to be equivalent to
/etc/init.d/mysql reload. The man page for initctl seems to confirm
reload JOB [KEY=VALUE]...
Sends the SIGHUP signal to running process of the named
JOB instance.
So a reload sends SIGHUP to the JOB and not to init.
The one thing I have not tried is 'kill -SIGHUP 1". I'm going to
setup a VM before I try that one on our servers.
Regards,
- Robert
--
ubuntu-us-mi mailing list
ubuntu-us-mi at lists.ubuntu.com

https://lists.ubuntu.com/mailman/listinfo/ubuntu-us-mi
-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://lists.ubuntu.com/archives/ubuntu-us-mi/attachments/20101216/4744dae9/attachment-0001.htm

[Jay R. Wren]

The launchpad bug to which you linked says it is fixed in
5.1.41-3ubuntu12.8. So it sounds like a nasty bug which has been fixed
and you have the unfortunate task of performing some recovery.

is there a way you can install 5.1.41-3ubuntu12.8 and not bounce the
mysql service so that upstart is refreshed (presumably with the upgrade
to 5.1.41-3ubuntu12.8) and you can safely shutdown with service mysql
stop? Maybe a dpkg --unpack or dpkg --no-triggers?
--
Jay

Post by David Barnett
Is there anything in the mysql logs? It almost sounds likethe database
is ignoring the stop command. Maybe a permissions issue?
--dave
On Dec 16, 2010 1:48 PM, "Robert Citek" <robert.citek at gmail.com

On Thu, Dec 16, 2010 at 1:03 PM, mike <mcruz32 at wowway.com

Post by mike
Maybe this can help.
http://upstart.ubuntu.com/faq.html#reload

Thanks, except that is also fraught with issues.
"If a job is running, the changes to the definition will not occur
until the job has been stopped;"
And I can't stop the job (mysql in this case) because upstart will
cause the tables to get corrupted by killing mysqld too soon.
"To force a reload you may send init the HUP signal, or use the
following command: initctl reload "
$ sudo initctl reload
initctl: missing job name
Try `initctl --help' for more information.
If I specify mysql as the job, that seems to be equivalent to
/etc/init.d/mysql reload. The man page for initctl seems to confirm
reload JOB [KEY=VALUE]...
Sends the SIGHUP signal to running process of the named
JOB instance.
So a reload sends SIGHUP to the JOB and not to init.
The one thing I have not tried is 'kill -SIGHUP 1". I'm going to
setup a VM before I try that one on our servers.
Regards,
- Robert
--
ubuntu-us-mi mailing list
ubuntu-us-mi at lists.ubuntu.com <mailto:ubuntu-us-mi at lists.ubuntu.com>

https://lists.ubuntu.com/mailman/listinfo/ubuntu-us-mi

-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://lists.ubuntu.com/archives/ubuntu-us-mi/attachments/20101216/3aba5d1a/attachment.htm

[Robert Citek]

Post by Jay R. Wren
The launchpad bug to which you linked says it is fixed in
5.1.41-3ubuntu12.8. So it sounds like a nasty bug which has been fixed and
you have the unfortunate task of performing some recovery.
is there a way you can install 5.1.41-3ubuntu12.8 and not bounce the mysql
service so that upstart is refreshed (presumably with the upgrade to
5.1.41-3ubuntu12.8) and you can safely shutdown with service mysql stop?
Maybe a dpkg --unpack or dpkg --no-triggers?

What that fix does is increase the timeout to 300, which we have done manually.

At this point our safest solutions appears to be this:

$ sudo mysqladmin shutdown ; sudo service mysql stop

The shutdown will gracefully bring down the server and then try to
bring it back up. The stop brings it down right as it is trying to
come back up. A mysqladmin flush-tables may work as well.

We'll then apply any upgrades and see what happens.

On the bright side, this is happening on one of our slaves. Once we
find a solution we'll use that on our other slaves and the master.

Regards,
- Robert

[Robert Citek]

Post by Robert Citek
$ sudo mysqladmin shutdown ; sudo service mysql stop

Seems to work:

https://bugs.launchpad.net/upstart/+bug/620441/comments/24

Regards,
- Robert

[Robert Citek]

Post by David Barnett
Is there anything in the mysql logs? It almost sounds likethe database is
ignoring the stop command. Maybe a permissions issue?

MySQL sees the shutdown. Here are normal shutdown log entries:

101216 16:03:02 InnoDB: Starting shutdown...
101216 16:03:05 InnoDB: Shutdown completed; log sequence number 7 2909857323
101216 16:03:05 [Note] /usr/sbin/mysqld: Shutdown complete

Notice that the normal shutdown took 3 seconds, just 2 seconds shy of
the 5 second cutoff.

Here are the entries when upstart prematurely kills mysql, followed by
entries for a start:

101216 15:20:39 InnoDB: Starting shutdown...
101216 15:23:15 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Log scan progressed past the checkpoint lsn 7 2773825966
101216 15:23:17 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite

Notice the absence of the "Shutdown completed" entry and the presence
of the "Database was not shut down normally" entry.

Regards,
- Robert