Corrupt Bayes Table

Questions and answers about how to do stuff
Post Reply
mcit
Posts: 27
Joined: 23 May 2014 01:24

Corrupt Bayes Table

Post by mcit »

After a server crash, my EFA would no longer start MariaDB.

By using innodb_force_recovery=2 I have been able to start the database and mail is flowing, however, the Maria Recovery process fails on the sa_bayes.bayes_token table.


[eFa] Do you wish to continue? (y/n): y

Beginning recovery...

Stopping services
Performing additional database checks
efa.tokens
note : The storage engine for the table doesn't support repair
mailscanner.audit_log
note : The storage engine for the table doesn't support repair
mailscanner.autorelease
note : The storage engine for the table doesn't support repair
mailscanner.blacklist
note : The storage engine for the table doesn't support repair
mailscanner.inq
note : The storage engine for the table doesn't support repair
mailscanner.maillog
note : The storage engine for the table doesn't support repair
mailscanner.mcp_rules
note : The storage engine for the table doesn't support repair
mailscanner.mtalog
note : The storage engine for the table doesn't support repair
mailscanner.mtalog_ids
note : The storage engine for the table doesn't support repair
mailscanner.outq
note : The storage engine for the table doesn't support repair
mailscanner.sa_rules
note : The storage engine for the table doesn't support repair
mailscanner.saved_filters
note : The storage engine for the table doesn't support repair
mailscanner.user_filters
note : The storage engine for the table doesn't support repair
mailscanner.users
note : The storage engine for the table doesn't support repair
mailscanner.whitelist
note : The storage engine for the table doesn't support repair
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.global_priv OK
mysql.gtid_slave_pos
note : The storage engine for the table doesn't support repair
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.index_stats OK
mysql.innodb_index_stats
note : The storage engine for the table doesn't support repair
mysql.innodb_table_stats
note : The storage engine for the table doesn't support repair
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.transaction_registry
note : The storage engine for the table doesn't support repair
opendmarc.arcauthresults
note : The storage engine for the table doesn't support repair
opendmarc.arcseals
note : The storage engine for the table doesn't support repair
opendmarc.domains
note : The storage engine for the table doesn't support repair
opendmarc.ipaddr
note : The storage engine for the table doesn't support repair
opendmarc.messages
note : The storage engine for the table doesn't support repair
opendmarc.reporters
note : The storage engine for the table doesn't support repair
opendmarc.requests
note : The storage engine for the table doesn't support repair
opendmarc.selectors
note : The storage engine for the table doesn't support repair
opendmarc.signatures
note : The storage engine for the table doesn't support repair
sa_bayes.bayes_expire
note : The storage engine for the table doesn't support repair
sa_bayes.bayes_global_vars
note : The storage engine for the table doesn't support repair
sa_bayes.bayes_seen
note : The storage engine for the table doesn't support repair
sa_bayes.bayes_token
Error : Got error 194 "Tablespace is missing for a table" from storage engine InnoDB
error : Corrupt
sa_bayes.bayes_vars
note : The storage engine for the table doesn't support repair
sa_bayes.txrep
note : The storage engine for the table doesn't support repair
sqlgrey.config
note : The storage engine for the table doesn't support repair
sqlgrey.connect
note : The storage engine for the table doesn't support repair
sqlgrey.domain_awl
note : The storage engine for the table doesn't support repair
sqlgrey.from_awl
note : The storage engine for the table doesn't support repair
sqlgrey.optin_domain
note : The storage engine for the table doesn't support repair
sqlgrey.optin_email
note : The storage engine for the table doesn't support repair
sqlgrey.optout_domain
note : The storage engine for the table doesn't support repair
sqlgrey.optout_email
note : The storage engine for the table doesn't support repair
Press [Enter] key to continue...
Runing database optimization
efa.tokens OK
mailscanner.audit_log OK
mailscanner.autorelease OK
mailscanner.blacklist OK
mailscanner.inq OK
mailscanner.maillog OK
mailscanner.mcp_rules OK
mailscanner.mtalog OK
mailscanner.mtalog_ids OK
mailscanner.outq OK
mailscanner.sa_rules OK
mailscanner.saved_filters OK
mailscanner.user_filters OK
mailscanner.users OK
mailscanner.whitelist OK
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.global_priv OK
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.index_stats OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.transaction_registry OK
opendmarc.arcauthresults OK
opendmarc.arcseals OK
opendmarc.domains OK
opendmarc.ipaddr OK
opendmarc.messages OK
opendmarc.reporters OK
opendmarc.requests OK
opendmarc.selectors OK
opendmarc.signatures OK
sa_bayes.bayes_expire OK
sa_bayes.bayes_global_vars OK
sa_bayes.bayes_seen OK
sa_bayes.bayes_token
Error : Got error 194 "Tablespace is missing for a table" from storage engine InnoDB
error : Corrupt
sa_bayes.bayes_vars OK
sa_bayes.txrep OK
sqlgrey.config OK
sqlgrey.connect OK
sqlgrey.domain_awl OK
sqlgrey.from_awl OK
sqlgrey.optin_domain OK
sqlgrey.optin_email OK
sqlgrey.optout_domain OK
sqlgrey.optout_email OK
Press [Enter] key to continue...


Most things are working, however, something that no longer works is the ability to search for messages. The page loads, but no results are ever displayed.

This issue occurred over a week ago so I do not think the auto backup is going to help me here.

Is there a way that I can recover / rebuild this table in the database?

I can survive loosing all the bayes information if that is what it takes.

I thought I could spin up a fresh EFA, dump this table from the clean install, and import it back to the existing, however, I am a little out of my depth on doing this. I have tried:

mysqldump -uroot mailscanner sa_bayes.bayes_token > /tmp/bayestoken.sql

but that fails with:

mysqldump: Couldn't find table: "bayes_token"


Could anyone guide me towards a solution for this issue?

Matthew
User avatar
pdwalker
Posts: 1553
Joined: 18 Mar 2015 09:16

Re: Corrupt Bayes Table

Post by pdwalker »

Do you have a backup?

If not, then you will need to recreate the related tables, make sure they are in innodb format and then repopulate the tables by retraining your bayes filter with known spam.

Good luck!
mcit
Posts: 27
Joined: 23 May 2014 01:24

Re: Corrupt Bayes Table

Post by mcit »

I was thinking this might be the case. The backups I have will include the problem, nothing from before. Could you guide me on the commands to actually re-create the table?

Matthew
User avatar
pdwalker
Posts: 1553
Joined: 18 Mar 2015 09:16

Re: Corrupt Bayes Table

Post by pdwalker »

Sorry for the delay.

Can you tell me what your host OS is, what database you are using and what version of EFA you are running?

I'll need to find the correct SQL creation command from the installer as I don't want to guess and give you the wrong command.
mcit
Posts: 27
Joined: 23 May 2014 01:24

Re: Corrupt Bayes Table

Post by mcit »

No problem.

OS is CentOS 7.9.2009
MySQL version 10.4.17-MariaDB
EFA version is 4.0.4

Thanks for your help.
User avatar
pdwalker
Posts: 1553
Joined: 18 Mar 2015 09:16

Re: Corrupt Bayes Table

Post by pdwalker »

Looks like this will do the trick: https://github.com/E-F-A/v4/blob/master ... _mysql.sql

Code: Select all

CREATE TABLE bayes_token (
  id int(11) NOT NULL default '0',
  token binary(5) NOT NULL default '',
  spam_count int(11) NOT NULL default '0',
  ham_count int(11) NOT NULL default '0',
  atime int(11) NOT NULL default '0',
  PRIMARY KEY  (id, token),
  INDEX bayes_token_idx1 (id, atime)
) ENGINE=InnoDB;
I also checked the update scripts under https://github.com/E-F-A/v4/tree/master ... /eFa-4.0.4 and saw no further alterations to this table.
Post Reply