Description andreas 2004-07-19 10:20:15 UTC

	Had mysql database corruption under FreeBSD 5.2.1-STABLE

Fix: 

Update for 4 ports:
	mysql323-server mysql40-server mysql41-server mysql50-server

	New mysql-server.sh start/stop script:
	- new rc-ng style
		mysql_enable="NO"		(defaul)
	- allows easy configuration of delay if you shutdown server
	  to hopefully avoid races to prevent db corruption
		mysql_shutdown_delay="5"	(defaul)

	After some weeks my dspam database was suddenly corrupted
	rendering the anti spam database unuseable.
	I noted occasionally, not always, that /var couldn't be unmounted
	successfully if I run system shutdown using Ctrl-Alt-Delete key
	sequence.
	Since mysql database is under /var/db is strongly assume, that
	the corruption happens, because mysql was unable to write all
	data to database.
	My mysql database is sometimes busy when server shuts if much
	new mail comes in, since I run fetching of e-mail batched via UUCP
	and fetchmail and Im subscribed to many mailinglists and get a
	lot of SPAM.

	I suggest another update to mysql server ports:

	I like the automatically Database Backup Mechanism, which is
	included in the postgresql port.

	Providing automatic db updates prevents complete data loss.

	If Postgresql port can do so, we should add the same type/level
	of service to mysql database.

	I can't promise if I do the work, but I would strongly suggest
	to do such an update.

The following files have to be updated
- mysql323-server/files/mysql-server.sh
- mysql40-server/files/mysql-server.sh
- mysql41-server/files/mysql-server.sh
- mysql50-server/files/mysql-server.sh
with the following script.

A creation of a suitable pkg-message file (see apache2 port for example)
is the only thing I'd like to pass to the mysql port maintainer ;-)

#!/bin/sh
#
# $FreeBSD$
#
# created by Andreas Klemm <andreas@FreeBSD.org>
#

# PROVIDE: mysql
# REQUIRE: NETWORKING SERVERS
# BEFORE: DAEMON
# KEYWORD: FreeBSD shutdown

#
# Add the following lines to /etc/rc.conf to enable mysql:
# mysql_enable (bool):		Set to "NO" by default.
#				Set it to "YES" to enable service.
# mysql_shutdown_delay (str):	Set to "5" seconds by default.
#				Time to ensure proper db termination on shutdown
#

. /etc/rc.subr

name=mysql
rcvar=`set_rcvar`
start_cmd="mysql_start"
stop_cmd="mysql_stop"
restart_cmd="mysql_restart"

dbdir=/var/db/mysql
mysql_command=/usr/local/bin/mysqld_safe
mysql_user=mysql
pidfile=${dbdir}/`/bin/hostname -s`.pid

mysql_start()
{
	/usr/bin/limits -U $mysql_user \
	$mysql_command \
	--user=$mysql_user \
	--datadir=${dbdir} \
	--pid-file=${pidfile} > /dev/null &
	echo -n ' mysqld'
}

mysql_stop()
{
	if [ -f ${pidfile} ]; then
		/bin/kill `cat ${pidfile}` > /dev/null 2>&1 \
		&& echo -n ' mysqld'
		sleep $mysql_shutdown_delay
	else
		echo "mysql-server isn't running"
	fi
}

mysql_restart()
{
	mysql_stop
	mysql_start
}

# set defaults
[ -z "$mysql_enable" ]		&& mysql_enable="NO"
[ -z "$mysql_shutdown_delay" ]	&& mysql_shutdown_delay="5"

required_dirs=${dbdir}
required_files=${dbdir}/mysql/host.frm

load_rc_config $name
run_rc_command "$1"
How-To-Repeat: 	Not repeatable (hopefully)