Suppress-o-matic - Stop database error spam

[GeekyDesigns]

What is Suppress-o-matic?
Suppress-o-matic is a change to the way vBulletin handles database error emails. It limits sending the same error email repeatedly to your inbox every time a database error is generated. On a busy site if a table is crashed or mysql itself goes down you could recieve several thousand emails. This causes the email server to bog down, and possibly the server itself if there is enough traffic generating emails.

Why do I want Suppress-o-matic?
It should be pretty clear, you still get the vital information that your vBulletin forum is offline for whichever reason, without the hassle of having to delete and manage your inbox afterwords.

Why do I have to edit the files?
If the database is down, there is no way to get to the plugins. Plugins are stored in the vBulletin database.

Isn't there a vBulletin Option for this?
There is an option to disable some emails, assuming that the settings table can be read. If it cannot then it doesn't work. It even states this on the option itself.

Can't I just remove my tech email from the config file?
This doesn't keep you up to date. What if a serious error like error 28 (no space left) crops up? This means that your server could potentially have serious issues. Not resolving these problems could cause major issues.

Are you considering adding more functionality to Suppress-o-matic?
We have plans to add a repair feature at some point in time, with an on/off switch.

What is SQlite?
SQLite is a tiny database engine that is found in PHP. It is very lite, and requires only that php have the extension. There is no other dedicated server for it. You can find more information here: http://en.wikipedia.org/wiki/SQLite


Whats required?
PHP5
SQLITE2 or newer (found in most php installations)
1 file edit
1 file to upload

Future versions:
Repair table feature



Project Credits:
Zachery - Project Planning, design goals, pestering.
Link14716 - Hard work.

This works for vBulletin 4.0 as well as 3.7+. vB3 thread located here (https://vborg.vbsupport.ru/showthread.php?t=218620).

[Link14716]

Reserved.

[dacho]

thanks you for that man, I will use it

[abdobasha2004]

thanks
needed for vb4 for sure
reserved

[nomoreturn]

installed thanks

[Dekard]

Great idea.

[Zaiaku]

I needed this so long ago, well glad to see its available now.

[Dr.osamA]

installed.
________
MOTORCYCLE TIRES (http://www.motorcycle-tech.com/tires/motorcycle-tires)

[Vaupell]

Ty needed it,, :D when playing with the db etc spam is crazy. .;)

EDIT : currently uninstalled.. didnt stop mail spam from new db errors, while site runs rc1 same with rc2.

[FractalizeR]

This is definitely a candidate for core inclusion.

[Zachery]

Ty needed it,, :D when playing with the db etc spam is crazy. .;)

EDIT : currently uninstalled.. didnt stop mail spam from new db errors, while site runs rc1 same with rc2.
You sure? we've tested this and it was working for us. IF the errors are different, you'll get multiple emails sitll.

[CvP]

Hi Zachery.
Installed on 4.0.2.
but how do I test it? :confused:

[FractalizeR]

Stop your SQL server ;)

[CvP]

Stop your SQL server ;)
lol..yeah..that'd work :eek:

[x1421]

Hi,
it is not working for me.

I get this error:
Fatal error: Class 'SQLiteDatabase' not found in /var/www/...../includes/class_core.php on line 1103

sqlitedberrors.sqlite is uploaded to my base vBulletin directory (domain.com/sqlitedberrors.sqlite) and its given 666 permission.

What is wrong?
(i have vb 4.0.4)

Regards,
x1421

[Zachery]

You don't have SQLite support in your php installation.

[Webbstre]

Thank you for this... I'll have to wait until something else breaks, but hopefully this will take care of my mass e-mail problem when errors happen :P

[Leo777]

This wasn't working for me, the sqlitedberrors file wasn't writeable despite having the correct permissions. Maybe this was due to some obscure setting on our hardened-security server. Fortunately I found the solution by following the advice of another person with the same problem, they posted the solution on the VB3 version of this mod : https://vborg.vbsupport.ru/showpost.php?p=2113289&postcount=47

Using that post's advice, I moved the sqlitedberrors file to a subdirectory and adjusted the path in the code, the problem was immediately solved.

ps. earlier in this thread someone asked about how to test this mod and a suggestion was to stop the SQL server. That would work but for most people it's probably easier to just edit the /includes/config.php and change the database settings to give an incorrect username. Then visit your forum homepage 5 times, if you get more than 1 email then this mod isn't working.

Thanks for the great mod, I'm using it with 4.0.8 and can't believe this STILL hasn't been included in core.

[Zachery]

That does sound like a server security issue. Not sure why it shouldn't work right Glad you found out why.

This was on someones todo list in the early vB3 days (at my suggestion), but never got included.

[cravendale]

Hi,

So do I get any emails at all once this is installed and working?
If not how exactly do I gain the information I need to problem solve potential vital errors?

thanks

[Zachery]

No, you get one per error per 30 min by default. So if you only get one, and 30 min later you dont get another the error has resolved itself. If you continue to get them, you know there is a problem.

So if you got an error about a crashed table, and error 28, you'd get two emails. Instead of 2000 per min for all of your users trying to refresh.

[cravendale]

No, you get one per error per 30 min by default. So if you only get one, and 30 min later you dont get another the error has resolved itself. If you continue to get them, you know there is a problem.

So if you got an error about a crashed table, and error 28, you'd get two emails. Instead of 2000 per min for all of your users trying to refresh.

I have vB 4.0.6 and I ended up having to remove it.

I put the sqlitedberrors.sqlite in the sqlite folder and CHMODD both file and folder to 666 and I got this...

Fatal error: Call to a member function show_chatbox() on a non-object in /home/private/public_html/keptprivate.com/forum/includes/class_bootstrap.php(394) : eval()'d code on line 33


Any ideas?

[Zachery]

That is an issue with annother third party addon. Not sure how our edits c ause a problem with your chatbox, but they shouldn't have. Also, you are likely required to edit the file to 777 so it is writeable.

[Romamo]

I have a more simple decision: http://tracker.vbulletin.com/browse/VBIV-12105
Patch limits email sending to 1 per minute.

[Phaedrus]

So you'd have 60 e-mails if the same error repeated for an hour? I'd prefer two. I'm going to tag this for later use.

Danke.

[screenmates]

There is no reason for not including this in the core - this is less than 15 lines of code. vB can use a config flag set to default false so admins can turn it on if sqlite is supported in the hosting.

[Zachery]

I actually suggested this feature as far back as vB3.0 :) they were always plans, but it never happened.

[Joe Siegler]

I installed this, and have two issues. At the top of my forums, I'm now seeing this text:



It's at the top of every page. This wasn't there until I installed this plugin. Additionally, none of my users' avatars are showing.

I backed the mod out, and the weird characters went away, but some of my users avatars are still not showing.

Using vB 4.15. Any ideas?

[Leo777]

I backed the mod out, and the weird characters went away, but some of my users avatars are still not showing.

It seems extremely unlikely that this mod 'Stop database error spam' is the cause of the problem, as it requires only 1 very small edit and couldn't affect avatars. I am guessing either it was another mod that you recently installed, or another change that you made to your forum (file edit, or template edit), or possibly a virus. But definitely not this mod. IMHO :)

[karel1985]

thanks, installed

[John Lester]

Downloaded but not installed yet. Hectic around here :D

[AusPhotography]

If you need a clean database for this mod this can be used:
(It said mine was corrupt)
Edit the path and run it with "php -f createdb.php" from the command line.

<?php
// createdb.php
// Run once module to create an empty SQLite database for this mod
// (c) Kym Farnik 2014 - permission given to use/modify as needed.

$sqliteerror='';
define('DIR','/home/XXXXXX/public_html/forum'); // Edit path to YOUR forum folder

unlink(DIR .'/sqlitedberrors.sqlite'); // delete existing DB

// Create a new Db and create the table and index
if ($sqlitedb = new SQLiteDatabase(DIR .'/sqlitedberrors.sqlite', 0666, $sqliteerror))
{
$sqlitedb->unbufferedQuery("CREATE TABLE dberrors (
time TIME PRIMARY KEY,
error TEXT,
errorcode INT(10),
ipaddress VARCHAR(39) ,
script VARCHAR(255)
);");
$sqlitedb->unbufferedQuery("CREATE INDEX dberrors_time ON dberrors(time);");
}

?>

[Zachery]

If you need a clean database for this mod this can be used:
(It said mine was corrupt)
Edit the path and run it with "php -f createdb.php" from the command line.

<?php
// createdb.php
// Run once module to create an empty SQLite database for this mod
// (c) Kym Farnik 2014 - permission given to use/modify as needed.

$sqliteerror='';
define('DIR','/home/XXXXXX/public_html/forum'); // Edit path to YOUR forum folder

unlink(DIR .'/sqlitedberrors.sqlite'); // delete existing DB

// Create a new Db and create the table and index
if ($sqlitedb = new SQLiteDatabase(DIR .'/sqlitedberrors.sqlite', 0666, $sqliteerror))
{
$sqlitedb->unbufferedQuery("CREATE TABLE dberrors (
time TIME PRIMARY KEY,
error TEXT,
errorcode INT(10),
ipaddress VARCHAR(39) ,
script VARCHAR(255)
);");
$sqlitedb->unbufferedQuery("CREATE INDEX dberrors_time ON dberrors(time);");
}

?>
I assume you could also reupload a clean version of the file we provided, right?

[AusPhotography]

I assume you could also reupload a clean version of the file we provided, right?

No, the file in the main product fails on our site. :(
Which is why I created a fresh one via the script.
[Also, Yes, I uploaded in binary mode. :) ]

[Zachery]

That is pretty odd, what are the permissions on the file, when you upload it, vs creating it?

[AusPhotography]

That is pretty odd, what are the permissions on the file, when you upload it, vs creating it?

0666 as normal, we are on our own VM, so I have 100% control.

[Zachery]

That is weird, thanks for posting a work around.

[AusPhotography]

A cheap DB dump fragment...

<?php
// dumpdb.php
// Run module to dump SQLite database for this mod
// (c) Kym Farnik 2014 - permission given to use/modify as needed.

header('Content-type: text/csv');
header('Content-disposition: attachment;filename=dumpdb.csv');

$sqliteerror='';
define('DIR','/home/XXXXXX/public_html/forum'); // Edit path to YOUR forum folder

// Open the database
if ($sqlitedb = new SQLiteDatabase(DIR .'/sqlitedberrors.sqlite', 0666, $sqliteerror))
{
$sqlitequery = $sqlitedb->unbufferedQuery("SELECT * FROM dberrors ORDER BY time DESC");
while ($sqliteresult = $sqlitequery->fetch(SQLITE_ASSOC))
{
echo '"'.date("Y-m-d H:i:s",$sqliteresult['time']).'",'.
'"'.$sqliteresult['error'].'",'.
$sqliteresult['errorcode'].','.
'"'.$sqliteresult['ipaddress'].'",'.
'"'.$sqliteresult['script'].'"'."\n";
}
}
else die ($sqliteerror); // the SQLite failed -- yuck

?>

[Mecho]

installed - stopped MySQL to check but lots db errors.

vb 4.2.2
SQLITE3 enabled
sqlitedberrors.sqlite CHMOD is 0666

Thanks in advance

[Zachery]

Are you saying you were getting db errors after installing it? What errors?

[Mecho]

Are you saying you were getting db errors after installing it? What errors?

the normal "mysql server gone" database error. so i meant addon was not working for me for unknown reason and i got lots of that db error in a second.

[Zachery]

What were the specific errors you were getting?

[Mecho]

i have stopped mysql service to check if i get 1 email only or attached by mysql server gone database error emails. i got tons of emails like usual .

[i156]

I added this plugin and it seemed to stop all emails being sent from the site. I removed the code and the file and my site still is not sending any emails (error emails, user registration emails, etc). What can I do to fix this?

[i156]

When I run the email diagnostics I get:

The mailing function returned an error while trying to send the mail.
The following errors were outputted by PHP when attempting to send mail:

Warning: 554 Unexpected response from SMTP server when ending transmission in ..../includes/class_mail.php on line 710

[kh99]

I don't see anything in this mod that could do that. My guess is that it's a coincidence of some kind. Did you try this mod because someone told you you were sending too much email? Maybe your host decided to do something about it on their end?

[i156]

No, I added it to stop cut down on the error emails that come through when mysql goes down. I've switch to sendmail for now which is working.

[i156]

Now what I can't figure out is I have it working with SMTP using my hosts' SMTP. But when I switch back to using Amazon SES which is what I used before I get that error.

[kh99]

Hmm...did you change the email address they are being sent to or from? Sometimes some servers will complain if the 'From' address isn't one they like. I guess there's no more information in the error message when you use the diagnostics?

[i156]

Hmm...did you change the email address they are being sent to or from? Sometimes some servers will complain if the 'From' address isn't one they like. I guess there's no more information in the error message when you use the diagnostics?

That was exactly it. I switched from my domain email to my gmail account. Switched it back and it is working. Thanks.

[kerrghann]

This seems to be exactly what I want. I'll give it a go. Thanks for the mod.

[joyboy2001]

Is this working with 4.2.x ?

[Zachery]

Still works fine.

[Islamic Board]

We have SQLite3 installed on php 5.4 and this mod doesn't work. How can we modify this to work with SQLite3?

[AusPhotography]

SQLite3 requires recoding the plugin slightly.
I've done it - easy

Kym

[Zachery]

Do you have the errors or notes regarding it? I don't have a test bed for this right now but if you let me know we can look into fixing it.

[Islamic Board]

Do you have the errors or notes regarding it? I don't have a test bed for this right now but if you let me know we can look into fixing it.

We have modified it slightly to work with SQLite3. There doesn't seem to be any errors thrown by this script at the moment but when we tested it, we still receive multiple db error emails of the same error message, even within 1 second.

Below is the modified script for SQLite3.
/**********************************************\
|* DB Error Mail Suppressomatic (squall14716) *|
\**********************************************/
if ($sqlitedb = new SQLite3(DIR.'/sqlite3dberrors.sqlite', 0666, $sqliteerror)) {
// Time between errors. 1800 (30 minutes) by default.
// Modify config.php for easy changing of this. For example:
// $config['dbems']['timebetweenerrors'] = 1800;
//($vbulletin->config['dbems']['timebetweenerrors'] ? $timebetweenerrors = $vbulletin->config['dbems']['timebetweenerrors'] : $timebetweenerrors = 60);
$timebetweenerrors = 60; // seconds

// Database opened and ready. Let's check to see if there are recent errors of this type.
$sqlitequery = $sqlitedb->query("SELECT * FROM dberrors WHERE time > ".(TIMENOW - $timebetweenerrors).
" AND errorcode = '".$this->errno."' ORDER BY time DESC");
$suppresserrormail = false;
while ($sqliteresult = $sqlitequery->fetchArray(SQLITE3_ASSOC)) {
if ($sqliteresult['script'] == $scriptpath) {
if ($sqliteresult['errorcode'] == $this->errno) {
// Script is the same and the errorcode is the same. Let's supress this error e-mail.
$suppresserrormail = true;
}
}
if ($sqliteresult['error'] == $this->error) {
// Error message is the same. Supress this error e-mail.
$suppresserrormail = true;
}
}

if ($suppresserrormail == false) {
$sqlitedb->exec("INSERT INTO dberrors (time, error, errorcode, ipaddress, script) VALUES (".TIMENOW.
", '".SQLite3::escapeString($this->error)."', '".$this->errno."', '$ipaddress', '$scriptpath')");
} else {
$technicalemail = "";
}

// Clean the SQLite table of all irrelevant data.
$sqlitedb->exec("DELETE FROM dberrors WHERE time < ".(TIMENOW - $timebetweenerrors));

}
/************************************************** \
|* End DB Error Mail Suppressomatic (squall14716) *|
\************************************************* */


Using the dumpdberror script given in this thread, we can see that there was a single db error written in the database file, only once. It has not updated after that in subsequent tests.