MySQL wait_timeout and Transaction Errors about barnyard2

[binf]

On Tue, Feb 12, 2013 at 11:38 PM, netsiphon [email protected] wrote:

I recently experienced unexplained transaction errors, ending up with barnyard quitting after the default transaction error threshold, which didn't fit any of the well known issues like duplicate sensors, permission issues, etc.

After debugging to follow the function path I realized that my problem was with my mysql config having a wait_timeout of 300 seconds(default is 28800)
and my low event rate. As you would expect, whenever the events would be spaced more than 300 seconds apart there would be a transaction error
and then in the next transaction the event would be inserted. Leaving to the default wait_timeout would of course fix it or just setting a more reasoned
value would fix it as well, as long as the event rate was higher than that value.

As far as i know MySQL wait_timeout is a server parameter, you can
probably also set it on client basis but by default we do not support
this option,
you could create a feature request so that in the barnyard2 2.x
version the mysql output plugin would support more mysql options (same
goes for other DBMS).

And if you want a quick patch you could probably patch the connection
code so that each connection set its own timeout option for the
socket.

http://dev.mysql.com/doc/refman/5.6/en/mysql-options.html

see MYSQL_OPT_CONNECT_TIMEOUT

And you could patch it right in the code of the by2 function
MYSQL_ManualConnect in src/output-plugins/spo_database.c below or
before

if (mysql_options(dbdata->m_sock, MYSQL_OPT_RECONNECT, &dbdata->dbRH[dbdata->dbtype_id].mysql_reconnect) != 0) { LogMessage("database: Failed to set reconnect option: %s\n", mysql_error(dbdata->m_sock)); return 1; }

The reason it even matters I guess is that the transaction error threshold never gets reset with a successful transaction now(2-1.11).
I understand that it may have caused loops but it is conceivable for there to be transaction errors that over a a period of time kill the
process that don't have to be due to the wait_timeout.

Unfortunately it has been like this since the new plugin has been out,
the main reason is that this can enable you to diagnose some issue.

You can allways change the value adding the connection_limit argument
to the database output and using a large value
#define UINT_MAX 4294967295U (since its a 32bit unsigned integer).

Also I think(correct me if I'm wrong) the transaction error threshold only applies to events and not to processing signature information or other db related functions.
So in the case of processing a never before seen signature the process will immediately quit as the transaction error occurred during the signature cache update(again correct me if I'm wrong).

This will happen on any type of SQL operation, since before any
SELECT,UPDATE,INSERT there is a connection descriptor check, and if
the connection_limit is reached
the process will stop.

Let us know what you think about those.

-elz

[netsiphon]

I'm ok with upping the connection_limit per database output, and I did so just to test that the option would work, but subsequently the application quit even though I could be pretty certain that the connection_limit wasn't reached.

That is why I was asking for clarification that indeed the transaction error threshold and connection limit applies to all SQL operations and that there aren't any special cases where a single error would completely bypass the retry mechanism.

If you believe there isn't I will just retest and confirm whether my errors are..."erroneous"...lol

[ovidiustanila]

I faced the same issue: I was getting a lot of insert errors which caused Barnyard to stop/halt. After looking around noticed this response and tried using MYSQL_OPT_CONNECT_TIMEOUT, as suggested by binf. But that didn't fix the issue, the connection was getting lost after wait_timeout value was reached. I managed to fix it by setting wait_timeout when the session was started:
mysql_options(data->m_sock, MYSQL_INIT_COMMAND, "/*!40101 set @@session.wait_timeout=92000 */");
This seems to work for me so I've added a configuration variable "mysql_wait" which can be set to a value that suits the environment on which Barnyard is running.

I've created a pull request with the patch I used (done against bug-fix-release branch), maybe it helps someone else too:
binf#1