verifying the structure of the DataBase and Tables

[eijal]

Is it posible to have a script in vicidial to verify that the structure of the DataBase and its tables is correct?
It could be great to have something like that?
THANKS

[mflorell]

Yes it would be great, are you volunteering to write it?

I have seen several projets that do this, and I feel that is would be extremely valuable to have this as a feature, especially when doing installations of astguiclient.

The problem is that writing a database validation tool to read in the MySQL_AST_CREATE_tables.sql file and compare the live DB against it would take quite a lot of time that I unfortunately don't have to spend on it. But if someone would like to write that script and then donate it to the project I would be very greatful.

[eijal]

Of course Matt, I want to help.
I am writting a script to do that. May be you can give me some tips to guide me
THANKs

[mflorell]

It should not be that difficult, but it might take a while to finish it, you should just have to read in the CREATE SQL file into an array, then go table by table and field by field and print out what does not match what is in the database.

[vctor]

Here is what I use, I got it from sourceforge

very much effective to check my database

Code: Select all

#!/bin/bash
#
# This is a small bash script that checks all mysql databases for errors
# and mails a log file to a specified email address. All variables are
# hardcoded for ease of use with cron. Any databases you wish not to check
# should be added to the DBEXCLUDE list, with a space in between each name.
#
# Note that DBEXCLUDE will only work with GNU sed, as BSD regular expressions
# on Darwin seem to have some trouble with word boundary anchors.
#
# sbray@csc.uvic.ca, UVic Fine Arts 2004.
# Some of this code was inspired from automysqlbackup.sh.2.0
# http://sourceforge.net/projects/automysqlbackup/

# system variables (change these according to your system)
USER=user
PASSWORD=user
DBHOST=localhost
LOGFILE=./mysql_check.log
MAILTO=youremail@address.com
TYPE1= # extra params to CHECK_TABLE e.g. FAST
TYPE2=
CORRUPT=no # start by assuming no corruption
DBNAMES="aheevaccs" # or a list delimited by space
DBEXCLUDE="" # or a list delimited by space

# I/O redirection...
touch $LOGFILE
exec 6>&1
exec > $LOGFILE # stdout redirected to $LOGFILE

echo -n "Logfile: "
date
echo "---------------------------------------------------------"
echo

# Get our list of databases to check...
# NOTE: the DBEXCLUDE feature seemed to only work with Linux regex, GNU sed
if test $DBNAMES = "all" ; then
DBNAMES="`mysql --user=$USER --password=$PASSWORD --batch -N -e "show databases"`"
for i in $DBEXCLUDE
do
DBNAMES=`echo $DBNAMES | sed "s/\b$i\b//g"`
done
fi

# Run through each database and execute our CHECK TABLE command for each table...
for i in $DBNAMES
do
# to fancy up our log file
echo ""
echo "Database: $i"
echo "---------------------------------------------------------"

DBTABLES="`mysql --user=$USER --password=$PASSWORD $i --batch -N -e "show tables"`"

for j in $DBTABLES
do
echo "CHECK TABLE $j $TYPE1 $TYPE2" | mysql -u$USER -p$PASSWORD $i
done
echo ""
done

exec 1>&6 6>&- # Restore stdout and close file descriptor #6

# test our logfile for corruption in the database...
for i in `cat $LOGFILE`
do
if test $i = "warning" ; then
CORRUPT=yes
elif test $i = "error" ; then
CORRUPT=yes
fi
done

# send off our results...
if test $CORRUPT = "yes" ; then
cat $LOGFILE | mail -s "MySQL CHECK Log [ERROR FOUND] for $DBHOST-`date`" $MAILTO
else
cat $LOGFILE | mail -s "MySQL CHECK Log [PASSED OK] for $HOST-`date`" $MAILTO
fi



[aster1]

Vctor what you suggest is good for checking error in mysql tables of running system but eijal is talking about a script that would CHECK proper structure of database during installation

[mflorell]

Looks like that just checks for data consistency, not for table structure consistency with a create database file like we are looking for, but at least it's a start, and not a bad thing to have either.