[syslog-ng] MySQL Pipe for Syslog-NG not working

[Martin Holste]

I'll defer to Clayton for db_insert.pl specifics.  One "trick" I've
learned for all things MySQL is that when you're unsure as to what
your app is doing, turning the general log on really helps pin down
what the DB is actually doing and receiving.  You can do so either in
the my.cnf file or by executing

SET GLOBAL general_log = ON

from the mysql client shell if it's MySQL 5.1 or higher.  For 5.0, you
need to add "--log" to the startup a la
http://dev.mysql.com/doc/refman/5.0/en/query-log.html .  This will
make all queries appear in the log file in
/var/lib/mysql/<hostname>.log.  That should be a good place to start,
and if you're seeing no activity there, then you've got a connection
problem.  The "lsof -p <pid>" command on db_insert.pl can help show
what it's connected to.

Hope that helps a little.

--Martin

On Wed, Oct 28, 2009 at 5:29 PM, Patrick Smith <patrick at paypros.com> wrote:
> Hi, I realize this is a syslog-ng users list but I know many of you are
> using/developing php-syslog-ng/logzilla as well so I thought I’d post the
> question here.  I did a fresh install of syslog-ng on 3 servers, all running
> installed via rpm, but on one server the mysql pipe isn’t working
> successfully.
>
>
>
> 2 servers are 64-bit RHEL4, syslog-ng-3.0.4-1.el4 (rpm), remote MySQL
> community server (mysql pipe works)
>
> 1 is 64-bit RHEL5, syslog-ng-3.0.4-1.el5 (rpm), local RedHat MySQL server
> (mysql pipe is not inserting messages)
>
>
>
> The pipe is taken direct from the php-syslog-ng wiki installation guide:
>
>
>
> destination d_mysql {
>
>   program("/var/www/logzilla/scripts/db_insert.pl"
>
>
> template("$HOST\t$FACILITY\t$PRIORITY\t$LEVEL\t$TAG\t$YEAR-$MONTH-$DAY\t$HOUR:$MIN:$SEC\t$PROGRAM\t$MSG\n")
>
>   );
>
> };
>
>
>
> destination d_localfiles {
>
>         file("/var/log/syslog-ng/$HOST/$PROGRAM-$YEAR-$MONTH-$DAY.log");
>
> };
>
>
>
> log { source(s_remote); filter(f_allsystem); destination(d_mysql); };
>
> log { source(s_remote); filter(f_allsystem); destination(d_localfiles); };
>
>
>
> I have the same syslog-ng.conf file on all 3 hosts.  Everything seems to be
> working normally, the logs just aren’t making it into the RHEL5 mysql db.
>
> - Syslog messages are being received and written to local files.
>
> - Test access to mysql syslog DB as syslogadmin and sysloguser works fine.
> Correct passwds are in config file.
>
> - Test run of DBGen.pl works normally and the log messages are searchable in
> the logzilla UI.
>
> - No error messages in syslog-ng.log, mysqld.log, or logzilla logs.
>
>
>
> Any ideas as to how to further troubleshoot would be appreciated.
>
>
>
> IMPORTANT NOTICE
>
> THIS MESSAGE IS CONFIDENTIAL. This e-mail message and any attachments are
> proprietary and confidential information intended only for the use of the
> recipient(s) named above. If you are not the intended recipient, you may not
> print, distribute, or copy this message or any attachments. If you have
> received this communication in error, please notify the sender by return
> e-mail and delete this message and any attachments from your computer. Learn
> more about Payment Processing's services at www.paypros.com.
> ______________________________________________________________________________
> Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng
> Documentation:
> http://www.balabit.com/support/documentation/?product=syslog-ng
> FAQ: http://www.campin.net/syslog-ng/faq.html
>
>
>