EFA is down due to SQL

[curibe]

here is the error when i log into mailwatch. How do i fix?

145: Table './mailscanner/maillog' is marked as crashed and should be repaired

SQL:
SELECT
COUNT(*) AS processed,
SUM(
CASE WHEN (
(virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
AND (ismcp=0 OR ismcp IS NULL)
AND (ishighmcp=0 OR ishighmcp IS NULL)
) THEN 1 ELSE 0 END
) AS clean,
ROUND((
SUM(
CASE WHEN (
(virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
AND (ismcp=0 OR ismcp IS NULL)
AND (ishighmcp=0 OR ishighmcp IS NULL)
) THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS cleanpercent,
SUM(
CASE WHEN
virusinfected>0
THEN 1 ELSE 0 END
) AS viruses,
ROUND((
SUM(
CASE WHEN
virusinfected>0
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS viruspercent,
SUM(
CASE WHEN
nameinfected>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
) AS blockedfiles,
ROUND((
SUM(
CASE WHEN
nameinfected>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS blockedfilespercent,
SUM(
CASE WHEN
otherinfected>0
AND (nameinfected=0 OR nameinfected IS NULL)
AND (virusinfected=0 OR virusinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
) AS otherinfected,
ROUND((
SUM(
CASE WHEN
otherinfected>0
AND (nameinfected=0 OR nameinfected IS NULL)
AND (virusinfected=0 OR virusinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS otherinfectedpercent,
SUM(
CASE WHEN
isspam>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
) AS spam,
ROUND((
SUM(
CASE WHEN
isspam>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS spampercent,
SUM(
CASE WHEN
ishighspam>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
THEN 1 ELSE 0 END
) AS highspam,
ROUND((
SUM(
CASE WHEN
ishighspam>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS highspampercent,
SUM(
CASE WHEN
ismcp>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
AND (ishighmcp=0 OR ishighmcp IS NULL)
THEN 1 ELSE 0 END
) AS mcp,
ROUND((
SUM(
CASE WHEN
ismcp>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
AND (ishighmcp=0 OR ishighmcp IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS mcppercent,
SUM(
CASE WHEN
ishighmcp>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
) AS highmcp,
ROUND((
SUM(
CASE WHEN
ishighmcp>0
AND (virusinfected=0 OR virusinfected IS NULL)
AND (nameinfected=0 OR nameinfected IS NULL)
AND (otherinfected=0 OR otherinfected IS NULL)
AND (isspam=0 OR isspam IS NULL)
AND (ishighspam=0 OR ishighspam IS NULL)
THEN 1 ELSE 0 END
)/COUNT(*))*100,1
) AS highmcppercent,
SUM(size) AS size
FROM
maillog
WHERE
date = CURRENT_DATE()
AND
(1=1)

[DaN]

I post you a link, where somebody has the same problem, but i'm not sure if their informations are right. (Make a snapshot/backup first)

Is your disk full?

http://comments.gmane.org/gmane.mail.vi ... neral/7931

For pro informations ask darky or shawn.

[curibe]

no, disk in not full only 6gb is been used.

Ill look at the link and let you know how it goes.

[curibe]

I post you a link, where somebody has the same problem, but i'm not sure if their informations are right. (Make a snapshot/backup first)

Is your disk full?

http://comments.gmane.org/gmane.mail.vi ... neral/7931

For pro informations ask darky or shawn.

to be host i do not understant the forum on this link. any help on running the command would greatly be appreciated.

[shawniverson]

Run the following:

Code: Select all

sudo /usr/local/sbin/EFA-Configure

Choose 10) MySQL Recovery and follow the prompts.

[curibe]

Run the following:

Code: Select all

sudo /usr/local/sbin/EFA-Configure

Choose 10) MySQL Recovery and follow the prompts.

Thanks. That worked.