[SOLVED] Cacti Syslog stopped working

[zhenningx]

Our Cacti Syslog seems stopped working a few days ago. I cannot see any syslog entries in the syslogs tab for the last several days. Someone else setup the cacti and he is on vacation. I have no idea how to troubleshoot it. I tried tcpdump on the server and I can see the syslog packets. I rebooted the server still no luck.
syslog-ng seems running:
root 2738 1 2 08:57 ? 00:02:04 /bin/bash /sbin/syslogtomysql
root 2742 1 16 08:57 ? 00:16:18 syslog-ng
root 25167 28201 0 10:35 pts/0 00:00:00 grep syslog
root 25169 2738 0 10:35 ? 00:00:00 mysql -u cactiuser --password=CactiMadeEZ syslog

How can I troubleshoot this syslog issue? Thanks!

Zhenning

[zhenningx]

Can anyone help on this one please???

How can I check the syslog database on the server CLI(not GUI)? Where is the database and syslog log file stored?

[zhenningx]

I tried to restart the syslog: service syslog-ng restart

Then I got lots of errors like:
ERROR 1062 (23000) at line 1: Duplicate entry '4294967295' for key 1

Any ideas?

[TheWitness]

Wow, that's a lot of entries. You need to truncate the specific table or change it to INNODB. What happened is that the auto increment key in MyISAM tables does not "prune" and when you get too many inserts, the table locks up.

I believe that this would affect both the syslog and syslog_incomming tables equally.

Before you switch to INNODB, you should make sure that memory performance is optimized. If you don't have an /etc/my.cnf, you definitely don't want to change until there is one there and you understand how to configure INNODB.

Another option would be to change the auto increment column format from INT to BIGINT. That might keep things going for a few more years

TheWitness

[zhenningx]

Thank you for the suggestion!

I think it is too beyond me to configure InnoDB. Can you please let me how to truncate the table to let the syslog working?

Thanks!

[TheWitness]

Code: Select all

mysql syslog
> truncate table syslog_incoming;
> alter table engin=myisam;
> show create table syslog_incoming;
> quit;

When you do the "show create table syslog_incoming" you should see an autoincrement number, it should be back to some small number. If that is not the case, ping me again.

TheWitness

[zhenningx]

I did and I got one error and also I got 0 rows affected after the first command:

mysql> truncate table syslog_incoming;
Query OK, 0 rows affected (0.03 sec)

mysql> alter table engin=myisam;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=myisam' at line 1
mysql> show create table syslog_incoming;
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| syslog_incoming | CREATE TABLE `syslog_incoming` (
`facility` varchar(10) default NULL,
`priority` varchar(10) default NULL,
`date` date default NULL,
`time` time default NULL,
`host` varchar(128) default NULL,
`message` text,
`seq` int(10) unsigned NOT NULL auto_increment,
`status` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`seq`),
KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

So the syslog_incoming table is empty?

Thank you very much!

Zhenning

[zhenningx]

Also I see a large AUTO_INCREMENT number(but not increasing) in syslog table. Should I truncate the syslog table?

mysql> show create table syslog;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| syslog | CREATE TABLE `syslog` (
`facility` varchar(10) default NULL,
`priority` varchar(10) default NULL,
`date` date default NULL,
`time` time default NULL,
`host` varchar(128) default NULL,
`message` text,
`seq` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`seq`),
KEY `date` (`date`),
KEY `time` (`time`),
KEY `host` (`host`),
KEY `priority` (`priority`),
KEY `facility` (`facility`)
) ENGINE=MyISAM AUTO_INCREMENT=2807185 DEFAULT CHARSET=latin1 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

btw, my mysql version is 5.0.54.

Thank you very much!

[zhenningx]

I truncated the table syslog as well and still no help. And now I see all no hosts in syslogs view. How can I add the hosts back? Please help!!

[zhenningx]

Can anyone help please? Thank you very much!!

[TheWitness]

Truncating the syslog table was a "BIG" mistake. You you screwed up by truncating the "WRONG" table. You need to truncate the syslog_incomming and then "alter table syslog_incoming engine=myisam;". You missed the last "e" on Engine. If you have a backup of the syslog table, you should restore it.

TheWitness

[zhenningx]

Hi TheWitness,

I don't know if the previous admin made the backup for syslog table or not. For now, even we cannot restore the previous table, how can I recreate a new one so it can start to work?

thank you very much!

Zhenning

[TheWitness]

YOU ONLY HAVE TO FIX THE SYSLOG_INCOMING TABLE AS PREVIOUSLY EXPLAINED.

TheWitness

[zhenningx]

I did the following:

mysql> truncate table syslog_incoming;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table syslog_incoming engine=myisam;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table syslog_incoming;
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| syslog_incoming | CREATE TABLE `syslog_incoming` (
`facility` varchar(10) default NULL,
`priority` varchar(10) default NULL,
`date` date default NULL,
`time` time default NULL,
`host` varchar(128) default NULL,
`message` text,
`seq` int(10) unsigned NOT NULL auto_increment,
`status` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`seq`),
KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


But now I am still not able to see any hosts in the syslogs view. Anyway to recreate the syslog table? I am ok to lose the previous logs. Just want to have a working environment. Any documentations to recreate a brand new syslog table to follow?

Thanks!

[TheWitness]

Are you still getting errors in the Cacti log? Is syslog-ng running on the host? Maybe with a Paypal donation, we can do something online?

TheWitness