MySQL Pipe for Syslog-NG not working
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.
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@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
Thanks Martin, your tips led me to a couple of good things. 1. The system (VM) I'm using has a smaller /var partition so I had symlinked /var/lib/mysql to another location with more space. Turns out I had datadir=/var/lib/mysql/mysql in my my.cnf file which looks to have changed at some point. Anyways, /var/lib/mysql was quite a mess so to be sure I did a clean reinstall. 2. I had log_error in my.cnf but following your suggestion added log=general.log which was much more revealing. Sadly what it revealed though was that nothing was even reaching mysql. I'll reply to Clayton's email as well as that highlighted the fix. -----Original Message----- From: syslog-ng-bounces@lists.balabit.hu on behalf of Martin Holste Sent: Wed 10/28/2009 7:15 PM To: Syslog-ng users' and developers' mailing list Subject: Re: [syslog-ng] MySQL Pipe for Syslog-NG not working 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@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
______________________________________________________________________________ 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 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.
Ok, I'm back @ home. Here are a couple of suggestions: Make sure you set DEBUG to TRUE in logzilla's config.php and restart syslog-ng Check /var/log/logzilla/db_insert.log for an errors Also, if you have the latest version of logzilla (v2.9.9n), there is a selftest option of the db_insert.pl script, just run "./db_insert.pl -s" You may also find the following thread useful: http://groups.google.com/group/php-syslog-ng-support/browse_thread/thread/aa... If you are getting something like this: "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)" Then you will need to modify db_insert.pl and change the connect string to: my $dbh = DBI->connect( "DBI:mysql:$db:mysql_socket=/var/lib/mysql/mysql.sock", $dbuser, $dbpass) I don't know what's causing this, but only people running syslog-ng v3.0 have reported it so far. Please let me know if you get it working. Also, to post to the php-syslog-ng list, please go here: http://groups.google.com/group/php-syslog-ng-support On Wed, Oct 28, 2009 at 6:29 PM, Patrick Smith <patrick@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
-- ______________________________________________________________ Clayton Dukes ______________________________________________________________
Clayton, you were right. With debugging on I was seeing that mysql.sock error and replacing $dbhost in config.php with the mysql_sock line you suggested fixed the issue. My best guess as to why this is happening is a difference between mysql 5.0 and 5.1. Looking at my original post I failed to mention that the Community MySQL version I have on my RHEL4 boxes is MySQL-server-community-5.1.39-0.rhel4, while the RedHat mysql version on my RHEL5 box is mysql-server-5.0.77-3.el5. Thanks! -----Original Message----- From: syslog-ng-bounces@lists.balabit.hu on behalf of Clayton Dukes Sent: Wed 10/28/2009 7:32 PM To: Syslog-ng users' and developers' mailing list Subject: Re: [syslog-ng] MySQL Pipe for Syslog-NG not working Ok, I'm back @ home. Here are a couple of suggestions: Make sure you set DEBUG to TRUE in logzilla's config.php and restart syslog-ng Check /var/log/logzilla/db_insert.log for an errors Also, if you have the latest version of logzilla (v2.9.9n), there is a selftest option of the db_insert.pl script, just run "./db_insert.pl -s" You may also find the following thread useful: http://groups.google.com/group/php-syslog-ng-support/browse_thread/thread/aa... If you are getting something like this: "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)" Then you will need to modify db_insert.pl and change the connect string to: my $dbh = DBI->connect( "DBI:mysql:$db:mysql_socket=/var/lib/mysql/mysql.sock", $dbuser, $dbpass) I don't know what's causing this, but only people running syslog-ng v3.0 have reported it so far. Please let me know if you get it working. Also, to post to the php-syslog-ng list, please go here: http://groups.google.com/group/php-syslog-ng-support On Wed, Oct 28, 2009 at 6:29 PM, Patrick Smith <patrick@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
-- ______________________________________________________________ Clayton Dukes ______________________________________________________________ ______________________________________________________________________________ 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 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.
Glad to hear that fixed it. I just posted a new version of logzilla a few minutes ago with some fixes for the cache update on larger systems. http://php-syslog-ng.googlecode.com/files/logzilla_v2.9.9o.tgz I'll try to address this issue in the next release. Thanks! On Thu, Oct 29, 2009 at 12:42 AM, Patrick Smith <patrick@paypros.com> wrote:
Clayton, you were right. With debugging on I was seeing that mysql.sock error and replacing $dbhost in config.php with the mysql_sock line you suggested fixed the issue.
My best guess as to why this is happening is a difference between mysql 5.0 and 5.1. Looking at my original post I failed to mention that the Community MySQL version I have on my RHEL4 boxes is MySQL-server-community-5.1.39-0.rhel4, while the RedHat mysql version on my RHEL5 box is mysql-server-5.0.77-3.el5.
Thanks!
-----Original Message----- From: syslog-ng-bounces@lists.balabit.hu on behalf of Clayton Dukes Sent: Wed 10/28/2009 7:32 PM To: Syslog-ng users' and developers' mailing list Subject: Re: [syslog-ng] MySQL Pipe for Syslog-NG not working
Ok, I'm back @ home. Here are a couple of suggestions: Make sure you set DEBUG to TRUE in logzilla's config.php and restart syslog-ng Check /var/log/logzilla/db_insert.log for an errors Also, if you have the latest version of logzilla (v2.9.9n), there is a selftest option of the db_insert.pl script, just run "./db_insert.pl -s"
You may also find the following thread useful: http://groups.google.com/group/php-syslog-ng-support/browse_thread/thread/aa...
If you are getting something like this: "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)"
Then you will need to modify db_insert.pl and change the connect string to: my $dbh = DBI->connect( "DBI:mysql:$db:mysql_socket=/var/lib/mysql/mysql.sock", $dbuser, $dbpass)
I don't know what's causing this, but only people running syslog-ng v3.0 have reported it so far.
Please let me know if you get it working. Also, to post to the php-syslog-ng list, please go here: http://groups.google.com/group/php-syslog-ng-support
On Wed, Oct 28, 2009 at 6:29 PM, Patrick Smith <patrick@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
-- ______________________________________________________________
Clayton Dukes ______________________________________________________________ ______________________________________________________________________________ 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 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.
-- ______________________________________________________________ Clayton Dukes ______________________________________________________________
participants (3)
-
Clayton Dukes
-
Martin Holste
-
Patrick Smith