Corrupt tables due to MySQL restart

[Xandrios]

Hi,

Every once in a while a MySQL table gets corrupted somehow. Judging by the MySQL logs it is caused by a MySQL restart:

070320 19:44:26 [Note] /usr/sbin/mysqld: Normal shutdown

070320 19:44:26 InnoDB: Starting shutdown...
070320 19:44:28 InnoDB: Shutdown completed; log sequence number 0 43634
070320 19:44:28 [Note] /usr/sbin/mysqld: Shutdown complete

070320 19:44:28 mysqld ended

070320 19:44:28 mysqld started
070320 19:44:28 [Warning] Asked for 196608 thread stack, but got 126976
070320 19:44:28 InnoDB: Started; log sequence number 0 43634
/usr/sbin/mysqld: ready for connections.
Version: '4.1.21-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL)
070320 19:44:32 [ERROR] /usr/sbin/mysqld: Can't open file: 'cpg11dn_sessions.MYI' (errno: 145)
070320 19:44:32 [ERROR] /usr/sbin/mysqld: Can't open file: 'cpg11dn_sessions.MYI' (errno: 145)
070320 19:44:34 [ERROR] /usr/sbin/mysqld: Can't open file: 'cpg11dn_sessions.MYI' (errno: 145)
070320 19:44:34 [ERROR] /usr/sbin/mysqld: Can't open file: 'cpg11dn_sessions.MYI' (errno: 145)
070320 19:44:35 [ERROR] /usr/sbin/mysqld: Can't open file: 'cpg11dn_sessions.MYI' (errno: 145)
070320 19:44:35 [ERROR] /usr/sbin/mysqld: Can't open file: 'cpg11dn_sessions.MYI' (errno: 145)
070320 19:44:36 [ERROR] /usr/sbin/mysqld: Can't open file: 'cpg11dn_sessions.MYI' (errno: 145)

This happens 1-2 times a month, and only a table repair fixes the issue. Which script could be the cause for these restarts? It happens at different times, not always at 19:44.

It seems to be a 'clean' shutdown though, so I'm not sure if the script that restarts mysql is at fault. Could it be that the table got corrupted while in operation, and it shows when mysql is restarted?

Thanks

 

[Xandrios]

Does directadmin restart MySQL every XXX hours/days? And if so, which script initiates that?

 

[smtalk]

Xandrios, no it doesn't, unless you have an old system backup script. If you want to update:

# wget -O /usr/local/sysbk/mod/mysql.bk http://www.directadmin.com/mysqk.bk

Are you using the latest version of MySQL?

 

[Xandrios]

Xandrios, no it doesn't, unless you have an old system backup script. If you want to update:

# wget -O /usr/local/sysbk/mod/mysql.bk http://www.directadmin.com/mysqk.bk

Are you using the latest version of MySQL?

Thanks for your reply

Unfortunately that URL gives an error 404. The script that I have at that location does include something about restarting though:

if [ "$MYSQL_RVAL" = "" ] && [ ! -f "$MYSQL_PID" ]; then
echo_fail
echo "MySQL could not be started, aborting..."
echo "MySQL could not be started, aborting..." >> $QLOG
postbk
exit 1
elif [ "$MYSQL_RVAL" = "" ]; then
echo_fail
echo "MySQL could not be started, aborting..."
echo "MySQL could not be started, aborting..." >> $QLOG
postbk
exit 1
fi

Which might cause the problem, if MySQL connections are live while MySQL is being restarted.

I'm running MySQL 4.1.21 by the way

 

[smtalk]

Here is the contend of mysql.bk:

DIR_PREFIX="mysql"
if [ "$MYSQL_BK" = "1" ]; then
echo "Performing MySQL backup "
echo "Performing MySQL backup " >> $QLOG
echo -n "     Stage 1 MySQL backup:"
echo -n "     Stage 1 MySQL backup:" >> $QLOG

SIM_CRON=`cat /etc/crontab | grep sim`
if [ -f "/usr/local/sim/sim" ] && [ ! "$SIM_CRON" == "" ]; then
        chmod 000 /usr/local/sim/sim
        SIM_OFF="1"
fi

arc $MYSQL_PATH full-mysql mysql

echo -n "     Stage 2 MySQL backup:"
echo -n "     Stage 2 MySQL backup:" >> $QLOG
if [ "$MYSQL_RVAL" = "" ] && [ ! -f "$MYSQL_PID" ]; then
        $NICE -n $PRI $MYSQL_MYICHK --silent --force --fast --update-state -O key_buffer=64M \
        -O sort_buffer=64M -O read_buffer=1M -O write_buffer=1M $MYSQL_PATH/*/*.MYI
fi
echo_completed

echo "     Stage 3 MySQL backup:"
echo "     Stage 3 MySQL backup:" >> $QLOG

sleep 1
MYSQL_RVAL=`ps -auxww | grep mysqld | grep -v grep`
if [ "$MYSQL_RVAL" = "" ] && [ ! -f "$MYSQL_PID" ]; then
        echo_fail
        echo "MySQL could not be started, aborting..."
        echo "MySQL could not be started, aborting..." >> $QLOG
        postbk
        exit 1
elif [ "$MYSQL_RVAL" = "" ]; then
        echo_fail
        echo "MySQL could not be started, aborting..."
        echo "MySQL could not be started, aborting..." >> $QLOG
        postbk
        exit 1
fi
if [ "$SIM_OFF" == "1" ]; then
        chmod 755 /usr/local/sim/sim >> /dev/null 2>&1
        SIM_OFF="0"
fi
sleep 1
MYSQL_USRDB=`ls $MYSQL_PATH`
for i in $MYSQL_USRDB; do
if [ -d "$MYSQL_PATH/$i" ] && [ "$DIR_PREFIX" == "" ]; then
        echo -n "       Dumping database $i: "
        echo -n "       Dumping database $i: " >> $QLOG
        $NICE -n $PRI $MYSQL_DUMP -l -u$MYSQL_ROOTUN -p$MYSQL_ROOTPW $i > $BACKUP_PATH/$DATE/$i.sql
        arc $BACKUP_PATH/$DATE/$i.sql $i.sql
        rm -f $BACKUP_PATH/$DATE/$i.sql
elif [ -d "$MYSQL_PATH/$i" ] && [ ! "$DIR_PREFIX" == "" ]; then
        echo -n "       Dumping database $i: "
        echo -n "       Dumping database $i: " >> $QLOG
        $NICE -n $PRI $MYSQL_DUMP -l -u$MYSQL_ROOTUN -p$MYSQL_ROOTPW $i > $BACKUP_PATH/$DATE/$DIR_PREFIX/$i.sql
        arc $BACKUP_PATH/$DATE/$DIR_PREFIX/$i.sql $i.sql mysql
        rm -f $BACKUP_PATH/$DATE/$DIR_PREFIX/$i.sql
fi
done
fi