Logzilla stopped writing into Database

[rblogger]

Hi,
we've currently setup a Ubuntu Logzilla Server.
At installation Date all was working fine after setting up some hosts. But the last Log-Entry in the mysql Database is from 1st June 2010 23:55, and than Logzilla looks like to stop integrate new messages into the mysql Database. If I setup a new host to send its Logmessages to Logzilla it gets an entry in the Hosts Tabel but I haven't any Logmessages in the Database.

To me it looks like there is a problem since the first merge, but I can't find it.

version is 3.0.75

The crontab looks like this :

#####################################################
# Run Sphinx "full" scan 30 minutes after midnight
# in order to create a new index for today.
#
#####################################################
30 0 1 * * /var/www/logzilla/sphinx/indexer.sh full >> /var/log/sphinx_indexer.log 2>&1

#####################################################
# Run Sphinx "delta" scans every 5 minutes throughout
# the day.
# Delta indexing should be very fast but you may need
# to adjust these times on very large systems.
#####################################################
*/5 * * * * /var/www/logzilla/sphinx/indexer.sh delta >> /var/log/sphinx_indexer.log 2>&1

#####################################################
# Run Sphinx "merge" scans every day at midnight
# Merging is much faster than a full scan.
# You may need to adjust these times on very large systems.
#####################################################
0 0 * * * /var/www/logzilla/sphinx/indexer.sh merge >> /var/log/sphinx_indexer.log 2>&1

Thanks.

[cdukes]

This sounds like your partition didn't get created.
Do you have the scheduler enabled as noted in this post?
http://forum.logzilla.info/index.php/topic,71.0.html

[rblogger]

Hi the event_scheduler was OFF and I turned it on, edited the my.cnf as described and did a restart of the machine, but it doesn't help.
Now the select @@event_schedule; shows a ON.
I checked the "logs" table in the mysql database and these are still the last entries :

| 3751981041 | 2010-03-01 00:00:00 |       1 | 2010-06-01 23:50:01 | 2010-06-01 23:50:01 |       |
| 1280 | rbubuntu.HV.LHG.LOCAL | 10       | 6        |  559476044 | pam_unix(cron:session): session closed for user root                                                                                                                                                  | 3751981041 | 2010-03-01 00:00:00 |       1 | 2010-06-01 23:50:01 | 2010-06-01 23:50:01 |       |
| 1281 | rbubuntu.HV.LHG.LOCAL | 10       | 6        |  559476044 | pam_unix(cron:session): session opened for user root by (uid=0)                                                                                                                                       | 3751981041 | 2010-03-01 00:00:00 |       1 | 2010-06-01 23:55:01 | 2010-06-01 23:55:01 |       |
| 1282 | rbubuntu.HV.LHG.LOCAL | 10       | 6        |  559476044 | pam_unix(cron:session): session closed for user root                                                                                                                                                  | 3751981041 | 2010-03-01 00:00:00 |       1 | 2010-06-01 23:55:01 | 2010-06-01 23:55:01 |       |
+------+-----------------------+----------+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------------------+---------+---------------------+---------------------+-------+
1282 rows in set (0.00 sec)

After then there are no new entries, and I have no idea why, there are currently 5 machines sending messages to the server.

[cdukes]

This is because you have no partition created for today (since your event scheduler failed).

To see if you have a missing partition:

Code: [Select]

mysql> show create table logs \G
The output will look something like this:

Code: [Select]

/*!50100 PARTITION BY RANGE ( TO_DAYS( lo ))
(PARTITION p20100603 VALUES LESS THAN (734291) ENGINE = MyISAM,
 PARTITION p20100604 VALUES LESS THAN (734292) ENGINE = MyISAM,
 PARTITION p20100605 VALUES LESS THAN (734293) ENGINE = MyISAM) */
The last partition you see should be for tomorrow, if not, add one like this:

Code: [Select]

alter table logs PARTITION BY RANGE( TO_DAYS( lo ) ) (PARTITION p20100605 VALUES LESS THAN (to_days('2010-06-05')));
Note to anyone searching this thread later on: Replace p20100605 and '2010-06-05' with tomorrow's date.

[rblogger]

Logging now works as expected.

Thank you 4 your support.

Regards,
Ramon

[murphybr]

where is the timeframe set for event scheduler to handle this partitioning?
event_schedule is on, its in my.conf; however we turn off our dev. server at night so it is not running, i would like to reset it to run at 8:30am when the servers are started up...

[cdukes]

where is the timeframe set for event scheduler to handle this partitioning?
event_schedule is on, its in my.conf; however we turn off our dev. server at night so it is not running, i would like to reset it to run at 8:30am when the servers are started up...

You'll have to modify the events in mysql.
Take a look here for help:
http://nms.gdd.net/index.php/Upgrade_Procedures_for_Logzilla_3.0#Upgrading_the_Events.2FPartitioning_Schema

[murphybr]

thank you. i set it for 9am since our servers shut down in dev. nightly. I shall test it monday morning.

[sancmk]

I had the same problem and I want to contribute with this information:

My LogZilla stopped writing into Database:

-03-01 00:00:00 |       1 | 2010-06-23 21:40:03 | 2010-06-23 21:40:03 |       |
| 101 | logzilla.lantest.net.br | 3        | 6        | 1430955586 | Leaving mDNS multicast group on interface eth0.IPv4 with address 10.161.0.61.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | 3751981041 | 2010-03-01 00:00:00 |       1 | 2010-06-23 21:40:03 | 2010-06-23 21:40:03 |       |
| 102 | logzilla.lantest.net.br | 10       | 6        | 3917628041 | Received signal 15; terminating.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | 3751981041 | 2010-03-01 00:00:00 |       1 | 2010-06-23 21:40:17 | 2010-06-23 21:40:17 |       |



I did the procedure:

# mysql -u root -p
mysql> use syslog;
mysql> show create table logs \G
*************************** 1. row ***************************
       Table: logs
Create Table: CREATE TABLE `logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `host` varchar(128) NOT NULL,
  `facility` enum('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','100','101','102','103') NOT NULL,
  `severity` enum('0','1','2','3','4','5','6','7') NOT NULL,
  `program` int(10) unsigned NOT NULL,
  `msg` varchar(2048) NOT NULL,
  `mne` int(10) unsigned NOT NULL,
  `suppress` datetime NOT NULL DEFAULT '2010-03-01 00:00:00',
  `counter` int(11) NOT NULL DEFAULT '1',
  `fo` datetime NOT NULL,
  `lo` datetime NOT NULL,
  `notes` varchar(255) NOT NULL,
  PRIMARY KEY (`id`,`lo`),
  KEY `facility` (`facility`),
  KEY `severity` (`severity`),
  KEY `mne` (`mne`),
  KEY `program` (`program`),
  KEY `suppress` (`suppress`)
) ENGINE=MyISAM AUTO_INCREMENT=103 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( TO_DAYS( lo ))
(PARTITION p20100623 VALUES LESS THAN (734311) ENGINE = MyISAM,
 PARTITION p20100624 VALUES LESS THAN (734312) ENGINE = MyISAM) */
1 row in set (0.02 sec)

mysql> alter table logs PARTITION BY RANGE( TO_DAYS( lo ) ) (PARTITION p20100629 VALUES LESS THAN (to_days('2010-06-29')));
Query OK, 102 rows affected (0.16 sec)
Records: 102  Duplicates: 0  Warnings: 0

mysql> show create table logs \G
*************************** 1. row ***************************
       Table: logs
Create Table: CREATE TABLE `logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `host` varchar(128) NOT NULL,
  `facility` enum('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','100','101','102','103') NOT NULL,
  `severity` enum('0','1','2','3','4','5','6','7') NOT NULL,
  `program` int(10) unsigned NOT NULL,
  `msg` varchar(2048) NOT NULL,
  `mne` int(10) unsigned NOT NULL,
  `suppress` datetime NOT NULL DEFAULT '2010-03-01 00:00:00',
  `counter` int(11) NOT NULL DEFAULT '1',
  `fo` datetime NOT NULL,
  `lo` datetime NOT NULL,
  `notes` varchar(255) NOT NULL,
  PRIMARY KEY (`id`,`lo`),
  KEY `facility` (`facility`),
  KEY `severity` (`severity`),
  KEY `mne` (`mne`),
  KEY `program` (`program`),
  KEY `suppress` (`suppress`)
) ENGINE=MyISAM AUTO_INCREMENT=103 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( TO_DAYS( lo ))
(PARTITION p20100629 VALUES LESS THAN (734317) ENGINE = MyISAM) */
1 row in set (0.00 sec)

mysql> exit


The LogZilla is writing into Database again:

-03-01 00:00:00 |       1 | 2010-06-23 21:40:17 | 2010-06-23 21:40:17 |       |
| 103 | logzilla.lantest.net.br | 5        | 5        | 2602577109 | syslog-ng starting up; version='2.1.4'                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | 3751981041 | 2010-03-01 00:00:00 |       1 | 2010-06-28 22:30:30 | 2010-06-28 22:30:30 |       |
| 104 | logzilla.lantest.net.br | 10       | 6        | 3917628041 | pam_unix(sshd:session): session closed for user alessandro                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | 3751981041 | 2010-03-01 00:00:00 |       1 | 2010-06-28 22:31:19 | 2010-06-28 22:31:19 |       |



More information:

I saw my "event_scheduler" and was "ON":

mysql> SELECT @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON                |
+-------------------+
1 row in set (0.00 sec)


at the /etc/my.cnf, also is "ON":

vim /etc/my.cnf
----------------------------------------------------------
.
..
...
event_scheduler=ON
...
..
.
|-------^ File:  /etc/my.cnf ------------------------

The problem was occurred when I was turned off my server.

Then, what I did that the problem was occurred and what can I do to not occur again?


Thank you for the post.

[murphybr]

sancmk I followed the link above my post about upgrading procedure, here is what i did to remedy the situation (like you my development servers get shut off nightly)...

The partitioning occurs (default) around midnight. I went into mysql

mysql> use information_schema;
mysql> select * from events;
you will see 3 jobs set in the events: logs_del_partition, log_add_archive, and logs_add_partition (all set for around midnight)

you will delete then re-add the events with corrected times as needed I did the following:

mysql> DROP EVENT IF EXISTS logs_add_partition;
mysql> DROP EVENT IF EXISTS logs_add_archive;
mysql> DROP EVENT IF EXISTS logs_del_partition;
mysql> CREATE EVENT logs_add_partition ON SCHEDULE EVERY 1 DAY STARTS '2010-06-26 09:00:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL logs_add_part_proc();
mysql> CREATE EVENT logs_add_archive ON SCHEDULE EVERY 1 DAY STARTS '2010-06-26 09:10:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL logs_add_archive_proc();
mysql> CREATE EVENT logs_del_partition ON SCHEDULE EVERY 1 DAY STARTS '2010-06-26 09:25:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL logs_delete_part_proc();

Since I start my server up at 8am, it allows me plenty of time to get it up and running before the jobs run. If this is incorrect cdukes will let us all know.lol but i hope it helps.

[diablo62]

Hello

I have the same problem on ubuntu 10.04 and mysql 5.1.41-3ubuntu12.3.
I have add event_scheduler=ON on my my.cnf but when i restart the daemon the event_scheduler is always off
I have done all of this forum but the syslog message don't appear in mysql and logzilla.

Anyone have a solution ? I have tried to install logzilla on debian but debian don't have the good version of mysql package.

ps : sorry for the english

[cdukes]

Hello

I have the same problem on ubuntu 10.04 and mysql 5.1.41-3ubuntu12.3.
I have add event_scheduler=ON on my my.cnf but when i restart the daemon the event_scheduler is always off

Try using 1 instead:
event_scheduler=1

Also, try Google

I have done all of this forum but the syslog message don't appear in mysql and logzilla.

Have you followed *every* step outlined in the install guide?
http://nms.gdd.net/index.php/Install_Guide_for_LogZilla_v3.0

[anis135]

I had the same problem, but i think worse than you all
From the first time installing I don't get my database inserted from the first time
I tried to copy the log file the result from my syslog-ng it works like the template
HOST   PRI PROGRAMS MSG

it works, and  i set the debug level to 5 and nothing error occured,
now i tried to set the partition like the example above,
my question is , do i need to wait for tomorrow to see if my database is inserted, or it will automatically inserts my database
after i edit the partition in mysql?

Please help me, I had made a thread but no one seems want to reply it,
maybe because it re-posting?
Thanks before,,

[cdukes]

It will work immediately.
Which thread did you make? I must have missed it. Sorry

[anis135]

I made it on installing section, but no problem as long you want to reply it here 

So what do you think i missed here?
I tried all the possibility that make my installation didn't work

1. The syslog user in mysql -> privileges done;
2. i create dump log -> success
3 tail -f /var/log/logzilla/db_insert.pl --> message keep coming

so i really don't know what to do?
oh, i forgot to mention that when i installed the logzilla, my server don't use logrotate, but has its own script developed by my friend,
is that matter?