Re: [ossec-list] ossec-dbd ERROR: Error executing query 'Insert into alert....'
I understand completely, I am not real happy about it either, and I used to work there in support! But that is what your docs say to use, so I did. I was going to install MariaDB and give that a shot as well. thanks, jerry On Wed, Sep 25, 2019, 3:01 PM dan (ddp) wrote: > > > On Wed, Sep 25, 2019 at 4:42 PM Jerry Lowry > wrote: > >> Since my last reply, these are the messages I have received in the mysql >> log: >> 2019-09-25T07:25:47.923547Z 40 [Note] Aborted connection 40 to db: >> 'ossec' user: 'ossecuser' host: 'obed' (Got timeout reading communication >> packets) >> 2019-09-25T17:31:03.941613Z 41 [Note] Aborted connection 41 to db: >> 'ossec' user: 'ossecuser' host: 'obed' (Got an error reading communication >> packets) >> >> Mail log does not show any errors from ossec. >> >> What version of MariaDB are you using? >> > > I think it’s 10.3.18, but I can verify later. I didn’t realize openbsd > still has mysql, so I guess I can try with the official one too (although > I’m not sure how I feel about installing oracle software ;)). > > >> jerry >> >> On Wed, Sep 25, 2019 at 12:40 PM dan (ddp) wrote: >> >>> >>> >>> On Wed, Sep 25, 2019 at 1:52 PM Jerry Lowry >>> wrote: >>> Well, being as I only have two agents installed to test initially and neither one is contacting the server due to email issues, I only have 53 alerts in the table. Just be aware that the database connects and functions fine for ~8 hours. It has failed at 4 in the morning the last time I sent you email. >>> >>> I’ll check my dbd in the morning to see if it’s still running. I’m >>> wondering if you’re having some kind of timeout issue or something. >>> >>> >>> As to the email problem this is what I have in my config file. The node 'cascade (10.20.10.6)' is running a postfix smtp server. The ossec server is in the same subnet. The user ossec is a valid user on the smtp server. yes jlo...@domain.com cascade os...@domain.com 20 I have copied the host file into the /var/ossec directory so it should be doing dns translation. I still get "Mail from not accepted by server" errors, postfix is also configured to accept email from any of the subnets defined. >>> >>> Check your postfix logs for errors. >>> >>> jerry On Wed, Sep 25, 2019 at 9:47 AM dan (ddp) wrote: > On Tue, Sep 24, 2019 at 2:23 PM Jerry Lowry > wrote: > > > > Dan, > > > > the only entries for today are as follows: > > 2019-09-24T08:02:49.637423Z 35 [Note] Aborted connection 35 to db: > 'ossec' user: 'ossecuser' host: 'obed' (Got timeout reading communication > packets) > > 2019-09-24T16:31:32.557059Z 36 [Note] Aborted connection 36 to db: > 'ossec' user: 'ossecuser' host: 'obed' (Got an error reading communication > packets) > > 2019-09-24T16:32:07.253522Z 38 [Note] Aborted connection 38 to db: > 'ossec' user: 'ossecuser' host: 'obed' (Got an error reading communication > packets) > > these errors do not coincide with the error from the dbd process at > 04:07:17 this morning. but then it looks like zulu time! PST is gmt+7. > > > > I have restarted all the ossec processes by hand and setup debugging > on the dbd and mail processes. I also have a tail -f running on the ossec > log. Nothing shows up as failing to connect for either the dbd or mail > process. It just finished the syscheck and rootcheck in the last hour > with > no errors from either process. > > > > The mysql process statistics : > > ps -o etime= -p 12275 > > 11-23:09:07 > > it has been up 11 days +. The only access error in the mysql log > are when I was resetting the host name for the user in the database, > forgot > to change the permissions, it now has been granted everything. > > > > Ok, I setup mariadb a couple of hours ago and started feeding OSSEC > alerts into it. I have a bit over 7000 rows in the alert table. > I haven't seen any issues so far, but my alert volume is pretty small. > How many alerts are you seeing? > I won't have the time to look into dbd for a bit, but I'm sure there > are a lot of improvements that can be made. > > > jerry > > > > > > On Tue, Sep 24, 2019 at 9:39 AM dan (ddp) wrote: > >> > >> On Tue, Sep 24, 2019 at 12:29 PM Jerry Lowry < > michaiah2...@gmail.com> wrote: > >> > > >> > Dan, > >> > So I configured the database to use the host name for the ossec > user. Restarted everything with ossec and it was able to log in initially. > It ran most of the night and then at 4 am this morning it failed with the > same error saying: > >> > > >> > 2019/09/24 04:07:17 ossec-dbd(5203): ERROR: Error executing query > 'INSERT INTO > alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_
Re: [ossec-list] ossec-dbd ERROR: Error executing query 'Insert into alert....'
On Wed, Sep 25, 2019 at 4:42 PM Jerry Lowry wrote:
> Since my last reply, these are the messages I have received in the mysql
> log:
> 2019-09-25T07:25:47.923547Z 40 [Note] Aborted connection 40 to db: 'ossec'
> user: 'ossecuser' host: 'obed' (Got timeout reading communication packets)
> 2019-09-25T17:31:03.941613Z 41 [Note] Aborted connection 41 to db: 'ossec'
> user: 'ossecuser' host: 'obed' (Got an error reading communication packets)
>
> Mail log does not show any errors from ossec.
>
> What version of MariaDB are you using?
>
I think it’s 10.3.18, but I can verify later. I didn’t realize openbsd
still has mysql, so I guess I can try with the official one too (although
I’m not sure how I feel about installing oracle software ;)).
> jerry
>
> On Wed, Sep 25, 2019 at 12:40 PM dan (ddp) wrote:
>
>>
>>
>> On Wed, Sep 25, 2019 at 1:52 PM Jerry Lowry
>> wrote:
>>
>>> Well, being as I only have two agents installed to test initially and
>>> neither one is contacting the server due to email issues, I only have 53
>>> alerts in the table. Just be aware that the database connects and
>>> functions fine for ~8 hours. It has failed at 4 in the morning the last
>>> time I sent you email.
>>>
>>
>> I’ll check my dbd in the morning to see if it’s still running. I’m
>> wondering if you’re having some kind of timeout issue or something.
>>
>>
>> As to the email problem this is what I have in my config file. The node
>>> 'cascade (10.20.10.6)' is running a postfix smtp server. The ossec server
>>> is in the same subnet.
>>> The user ossec is a valid user on the smtp server.
>>> yes
>>> jlo...@domain.com
>>> cascade
>>> os...@domain.com
>>> 20
>>>
>>> I have copied the host file into the /var/ossec directory so it should
>>> be doing dns translation. I still get "Mail from not accepted by server"
>>> errors, postfix is also configured to accept email from any of the subnets
>>> defined.
>>>
>>
>> Check your postfix logs for errors.
>>
>>
>>> jerry
>>>
>>> On Wed, Sep 25, 2019 at 9:47 AM dan (ddp) wrote:
>>>
On Tue, Sep 24, 2019 at 2:23 PM Jerry Lowry
wrote:
>
> Dan,
>
> the only entries for today are as follows:
> 2019-09-24T08:02:49.637423Z 35 [Note] Aborted connection 35 to db:
'ossec' user: 'ossecuser' host: 'obed' (Got timeout reading communication
packets)
> 2019-09-24T16:31:32.557059Z 36 [Note] Aborted connection 36 to db:
'ossec' user: 'ossecuser' host: 'obed' (Got an error reading communication
packets)
> 2019-09-24T16:32:07.253522Z 38 [Note] Aborted connection 38 to db:
'ossec' user: 'ossecuser' host: 'obed' (Got an error reading communication
packets)
> these errors do not coincide with the error from the dbd process at
04:07:17 this morning. but then it looks like zulu time! PST is gmt+7.
>
> I have restarted all the ossec processes by hand and setup debugging
on the dbd and mail processes. I also have a tail -f running on the ossec
log. Nothing shows up as failing to connect for either the dbd or mail
process. It just finished the syscheck and rootcheck in the last hour with
no errors from either process.
>
> The mysql process statistics :
> ps -o etime= -p 12275
> 11-23:09:07
> it has been up 11 days +. The only access error in the mysql log
are when I was resetting the host name for the user in the database, forgot
to change the permissions, it now has been granted everything.
>
Ok, I setup mariadb a couple of hours ago and started feeding OSSEC
alerts into it. I have a bit over 7000 rows in the alert table.
I haven't seen any issues so far, but my alert volume is pretty small.
How many alerts are you seeing?
I won't have the time to look into dbd for a bit, but I'm sure there
are a lot of improvements that can be made.
> jerry
>
>
> On Tue, Sep 24, 2019 at 9:39 AM dan (ddp) wrote:
>>
>> On Tue, Sep 24, 2019 at 12:29 PM Jerry Lowry
wrote:
>> >
>> > Dan,
>> > So I configured the database to use the host name for the ossec
user. Restarted everything with ossec and it was able to log in initially.
It ran most of the night and then at 4 am this morning it failed with the
same error saying:
>> >
>> > 2019/09/24 04:07:17 ossec-dbd(5203): ERROR: Error executing query
'INSERT INTO
alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,alertid,user,full_log,tld)
VALUES ('1', '1002','2','1569323237', '1', '(null)', '0', '(null)', '0',
'1569323234.0', '(null)', 'Sep 24 04:07:14 obed audispd: node=
obed.edt.com type=ANOM_RBAC_INTEGRITY_FAIL
msg=audit(1569323234.455:87010): pid=28134 uid=0 auid=0 ses=2001
msg=`added=43772 removed=17 changed=2021 exe="/usr/sbin/aide" hostname=?
addr=? terminal=? res=failed`','')'. Error: 'MySQL server has gone away'
Re: [ossec-list] ossec-dbd ERROR: Error executing query 'Insert into alert....'
Since my last reply, these are the messages I have received in the mysql
log:
2019-09-25T07:25:47.923547Z 40 [Note] Aborted connection 40 to db: 'ossec'
user: 'ossecuser' host: 'obed' (Got timeout reading communication packets)
2019-09-25T17:31:03.941613Z 41 [Note] Aborted connection 41 to db: 'ossec'
user: 'ossecuser' host: 'obed' (Got an error reading communication packets)
Mail log does not show any errors from ossec.
What version of MariaDB are you using?
jerry
On Wed, Sep 25, 2019 at 12:40 PM dan (ddp) wrote:
>
>
> On Wed, Sep 25, 2019 at 1:52 PM Jerry Lowry
> wrote:
>
>> Well, being as I only have two agents installed to test initially and
>> neither one is contacting the server due to email issues, I only have 53
>> alerts in the table. Just be aware that the database connects and
>> functions fine for ~8 hours. It has failed at 4 in the morning the last
>> time I sent you email.
>>
>
> I’ll check my dbd in the morning to see if it’s still running. I’m
> wondering if you’re having some kind of timeout issue or something.
>
>
> As to the email problem this is what I have in my config file. The node
>> 'cascade (10.20.10.6)' is running a postfix smtp server. The ossec server
>> is in the same subnet.
>> The user ossec is a valid user on the smtp server.
>> yes
>> jlo...@domain.com
>> cascade
>> os...@domain.com
>> 20
>>
>> I have copied the host file into the /var/ossec directory so it should be
>> doing dns translation. I still get "Mail from not accepted by server"
>> errors, postfix is also configured to accept email from any of the subnets
>> defined.
>>
>
> Check your postfix logs for errors.
>
>
>> jerry
>>
>> On Wed, Sep 25, 2019 at 9:47 AM dan (ddp) wrote:
>>
>>> On Tue, Sep 24, 2019 at 2:23 PM Jerry Lowry
>>> wrote:
>>> >
>>> > Dan,
>>> >
>>> > the only entries for today are as follows:
>>> > 2019-09-24T08:02:49.637423Z 35 [Note] Aborted connection 35 to db:
>>> 'ossec' user: 'ossecuser' host: 'obed' (Got timeout reading communication
>>> packets)
>>> > 2019-09-24T16:31:32.557059Z 36 [Note] Aborted connection 36 to db:
>>> 'ossec' user: 'ossecuser' host: 'obed' (Got an error reading communication
>>> packets)
>>> > 2019-09-24T16:32:07.253522Z 38 [Note] Aborted connection 38 to db:
>>> 'ossec' user: 'ossecuser' host: 'obed' (Got an error reading communication
>>> packets)
>>> > these errors do not coincide with the error from the dbd process at
>>> 04:07:17 this morning. but then it looks like zulu time! PST is gmt+7.
>>> >
>>> > I have restarted all the ossec processes by hand and setup debugging
>>> on the dbd and mail processes. I also have a tail -f running on the ossec
>>> log. Nothing shows up as failing to connect for either the dbd or mail
>>> process. It just finished the syscheck and rootcheck in the last hour with
>>> no errors from either process.
>>> >
>>> > The mysql process statistics :
>>> > ps -o etime= -p 12275
>>> > 11-23:09:07
>>> > it has been up 11 days +. The only access error in the mysql log are
>>> when I was resetting the host name for the user in the database, forgot to
>>> change the permissions, it now has been granted everything.
>>> >
>>>
>>> Ok, I setup mariadb a couple of hours ago and started feeding OSSEC
>>> alerts into it. I have a bit over 7000 rows in the alert table.
>>> I haven't seen any issues so far, but my alert volume is pretty small.
>>> How many alerts are you seeing?
>>> I won't have the time to look into dbd for a bit, but I'm sure there
>>> are a lot of improvements that can be made.
>>>
>>> > jerry
>>> >
>>> >
>>> > On Tue, Sep 24, 2019 at 9:39 AM dan (ddp) wrote:
>>> >>
>>> >> On Tue, Sep 24, 2019 at 12:29 PM Jerry Lowry
>>> wrote:
>>> >> >
>>> >> > Dan,
>>> >> > So I configured the database to use the host name for the ossec
>>> user. Restarted everything with ossec and it was able to log in initially.
>>> It ran most of the night and then at 4 am this morning it failed with the
>>> same error saying:
>>> >> >
>>> >> > 2019/09/24 04:07:17 ossec-dbd(5203): ERROR: Error executing query
>>> 'INSERT INTO
>>> alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,alertid,user,full_log,tld)
>>> VALUES ('1', '1002','2','1569323237', '1', '(null)', '0', '(null)', '0',
>>> '1569323234.0', '(null)', 'Sep 24 04:07:14 obed audispd: node=
>>> obed.edt.com type=ANOM_RBAC_INTEGRITY_FAIL
>>> msg=audit(1569323234.455:87010): pid=28134 uid=0 auid=0 ses=2001
>>> msg=`added=43772 removed=17 changed=2021 exe="/usr/sbin/aide" hostname=?
>>> addr=? terminal=? res=failed`','')'. Error: 'MySQL server has gone away'.
>>> >> > 2019/09/24 04:07:17 ossec-dbd(5209): INFO: Closing connection to
>>> database.
>>> >> > 2019/09/24 04:07:17 ossec-dbd(5210): INFO: Attempting to reconnect
>>> to database.
>>> >> > 2019/09/24 04:07:17 ossec-dbd: Connected to database 'ossec' at
>>> 'obed'.
>>> >> > 2019/09/24 04:07:17 ossec-dbd(5204): ERROR: Database error. Unable
>>> to run query.
>>> >> >
>>> >> > A list of
Re: [ossec-list] ossec-dbd ERROR: Error executing query 'Insert into alert....'
On Wed, Sep 25, 2019 at 1:52 PM Jerry Lowry wrote:
> Well, being as I only have two agents installed to test initially and
> neither one is contacting the server due to email issues, I only have 53
> alerts in the table. Just be aware that the database connects and
> functions fine for ~8 hours. It has failed at 4 in the morning the last
> time I sent you email.
>
I’ll check my dbd in the morning to see if it’s still running. I’m
wondering if you’re having some kind of timeout issue or something.
As to the email problem this is what I have in my config file. The node
> 'cascade (10.20.10.6)' is running a postfix smtp server. The ossec server
> is in the same subnet.
> The user ossec is a valid user on the smtp server.
> yes
> jlo...@domain.com
> cascade
> os...@domain.com
> 20
>
> I have copied the host file into the /var/ossec directory so it should be
> doing dns translation. I still get "Mail from not accepted by server"
> errors, postfix is also configured to accept email from any of the subnets
> defined.
>
Check your postfix logs for errors.
> jerry
>
> On Wed, Sep 25, 2019 at 9:47 AM dan (ddp) wrote:
>
>> On Tue, Sep 24, 2019 at 2:23 PM Jerry Lowry
>> wrote:
>> >
>> > Dan,
>> >
>> > the only entries for today are as follows:
>> > 2019-09-24T08:02:49.637423Z 35 [Note] Aborted connection 35 to db:
>> 'ossec' user: 'ossecuser' host: 'obed' (Got timeout reading communication
>> packets)
>> > 2019-09-24T16:31:32.557059Z 36 [Note] Aborted connection 36 to db:
>> 'ossec' user: 'ossecuser' host: 'obed' (Got an error reading communication
>> packets)
>> > 2019-09-24T16:32:07.253522Z 38 [Note] Aborted connection 38 to db:
>> 'ossec' user: 'ossecuser' host: 'obed' (Got an error reading communication
>> packets)
>> > these errors do not coincide with the error from the dbd process at
>> 04:07:17 this morning. but then it looks like zulu time! PST is gmt+7.
>> >
>> > I have restarted all the ossec processes by hand and setup debugging on
>> the dbd and mail processes. I also have a tail -f running on the ossec
>> log. Nothing shows up as failing to connect for either the dbd or mail
>> process. It just finished the syscheck and rootcheck in the last hour with
>> no errors from either process.
>> >
>> > The mysql process statistics :
>> > ps -o etime= -p 12275
>> > 11-23:09:07
>> > it has been up 11 days +. The only access error in the mysql log are
>> when I was resetting the host name for the user in the database, forgot to
>> change the permissions, it now has been granted everything.
>> >
>>
>> Ok, I setup mariadb a couple of hours ago and started feeding OSSEC
>> alerts into it. I have a bit over 7000 rows in the alert table.
>> I haven't seen any issues so far, but my alert volume is pretty small.
>> How many alerts are you seeing?
>> I won't have the time to look into dbd for a bit, but I'm sure there
>> are a lot of improvements that can be made.
>>
>> > jerry
>> >
>> >
>> > On Tue, Sep 24, 2019 at 9:39 AM dan (ddp) wrote:
>> >>
>> >> On Tue, Sep 24, 2019 at 12:29 PM Jerry Lowry
>> wrote:
>> >> >
>> >> > Dan,
>> >> > So I configured the database to use the host name for the ossec
>> user. Restarted everything with ossec and it was able to log in initially.
>> It ran most of the night and then at 4 am this morning it failed with the
>> same error saying:
>> >> >
>> >> > 2019/09/24 04:07:17 ossec-dbd(5203): ERROR: Error executing query
>> 'INSERT INTO
>> alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,alertid,user,full_log,tld)
>> VALUES ('1', '1002','2','1569323237', '1', '(null)', '0', '(null)', '0',
>> '1569323234.0', '(null)', 'Sep 24 04:07:14 obed audispd: node=
>> obed.edt.com type=ANOM_RBAC_INTEGRITY_FAIL
>> msg=audit(1569323234.455:87010): pid=28134 uid=0 auid=0 ses=2001
>> msg=`added=43772 removed=17 changed=2021 exe="/usr/sbin/aide" hostname=?
>> addr=? terminal=? res=failed`','')'. Error: 'MySQL server has gone away'.
>> >> > 2019/09/24 04:07:17 ossec-dbd(5209): INFO: Closing connection to
>> database.
>> >> > 2019/09/24 04:07:17 ossec-dbd(5210): INFO: Attempting to reconnect
>> to database.
>> >> > 2019/09/24 04:07:17 ossec-dbd: Connected to database 'ossec' at
>> 'obed'.
>> >> > 2019/09/24 04:07:17 ossec-dbd(5204): ERROR: Database error. Unable
>> to run query.
>> >> >
>> >> > A list of the mysql daemon process shows that it has been up and
>> running since Sep 12.
>> >> > UIDPID PPID CSZ RSS PSR STIME TTY TIME CMD
>> >> > mysql12275 1 0 378222 204688 3 Sep12 ?00:12:57
>> /usr/sbin/mysqld
>> >> >
>> >> > So mysql has not gone away. I suspect the ossec-dbd process is
>> failing. Is there a way to debug this to a log file? By the way I am
>> running version 3.3.0 on centos 7.6.1810
>> >> >
>> >>
>> >> Are there any corresponding messages in your mysql log files?
>> >>
>> >> > I need this to work soon! How many other users are having this
>> problem with mysql?
>> >> > Is this
Re: [ossec-list] ossec-dbd ERROR: Error executing query 'Insert into alert....'
Well, being as I only have two agents installed to test initially and
neither one is contacting the server due to email issues, I only have 53
alerts in the table. Just be aware that the database connects and
functions fine for ~8 hours. It has failed at 4 in the morning the last
time I sent you email.
As to the email problem this is what I have in my config file. The node
'cascade (10.20.10.6)' is running a postfix smtp server. The ossec server
is in the same subnet.
The user ossec is a valid user on the smtp server.
yes
jlo...@domain.com
cascade
os...@domain.com
20
I have copied the host file into the /var/ossec directory so it should be
doing dns translation. I still get "Mail from not accepted by server"
errors, postfix is also configured to accept email from any of the subnets
defined.
jerry
On Wed, Sep 25, 2019 at 9:47 AM dan (ddp) wrote:
> On Tue, Sep 24, 2019 at 2:23 PM Jerry Lowry
> wrote:
> >
> > Dan,
> >
> > the only entries for today are as follows:
> > 2019-09-24T08:02:49.637423Z 35 [Note] Aborted connection 35 to db:
> 'ossec' user: 'ossecuser' host: 'obed' (Got timeout reading communication
> packets)
> > 2019-09-24T16:31:32.557059Z 36 [Note] Aborted connection 36 to db:
> 'ossec' user: 'ossecuser' host: 'obed' (Got an error reading communication
> packets)
> > 2019-09-24T16:32:07.253522Z 38 [Note] Aborted connection 38 to db:
> 'ossec' user: 'ossecuser' host: 'obed' (Got an error reading communication
> packets)
> > these errors do not coincide with the error from the dbd process at
> 04:07:17 this morning. but then it looks like zulu time! PST is gmt+7.
> >
> > I have restarted all the ossec processes by hand and setup debugging on
> the dbd and mail processes. I also have a tail -f running on the ossec
> log. Nothing shows up as failing to connect for either the dbd or mail
> process. It just finished the syscheck and rootcheck in the last hour with
> no errors from either process.
> >
> > The mysql process statistics :
> > ps -o etime= -p 12275
> > 11-23:09:07
> > it has been up 11 days +. The only access error in the mysql log are
> when I was resetting the host name for the user in the database, forgot to
> change the permissions, it now has been granted everything.
> >
>
> Ok, I setup mariadb a couple of hours ago and started feeding OSSEC
> alerts into it. I have a bit over 7000 rows in the alert table.
> I haven't seen any issues so far, but my alert volume is pretty small.
> How many alerts are you seeing?
> I won't have the time to look into dbd for a bit, but I'm sure there
> are a lot of improvements that can be made.
>
> > jerry
> >
> >
> > On Tue, Sep 24, 2019 at 9:39 AM dan (ddp) wrote:
> >>
> >> On Tue, Sep 24, 2019 at 12:29 PM Jerry Lowry
> wrote:
> >> >
> >> > Dan,
> >> > So I configured the database to use the host name for the ossec user.
> Restarted everything with ossec and it was able to log in initially. It ran
> most of the night and then at 4 am this morning it failed with the same
> error saying:
> >> >
> >> > 2019/09/24 04:07:17 ossec-dbd(5203): ERROR: Error executing query
> 'INSERT INTO
> alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,alertid,user,full_log,tld)
> VALUES ('1', '1002','2','1569323237', '1', '(null)', '0', '(null)', '0',
> '1569323234.0', '(null)', 'Sep 24 04:07:14 obed audispd: node=obed.edt.com
> type=ANOM_RBAC_INTEGRITY_FAIL msg=audit(1569323234.455:87010): pid=28134
> uid=0 auid=0 ses=2001 msg=`added=43772 removed=17 changed=2021
> exe="/usr/sbin/aide" hostname=? addr=? terminal=? res=failed`','')'. Error:
> 'MySQL server has gone away'.
> >> > 2019/09/24 04:07:17 ossec-dbd(5209): INFO: Closing connection to
> database.
> >> > 2019/09/24 04:07:17 ossec-dbd(5210): INFO: Attempting to reconnect to
> database.
> >> > 2019/09/24 04:07:17 ossec-dbd: Connected to database 'ossec' at
> 'obed'.
> >> > 2019/09/24 04:07:17 ossec-dbd(5204): ERROR: Database error. Unable to
> run query.
> >> >
> >> > A list of the mysql daemon process shows that it has been up and
> running since Sep 12.
> >> > UIDPID PPID CSZ RSS PSR STIME TTY TIME CMD
> >> > mysql12275 1 0 378222 204688 3 Sep12 ?00:12:57
> /usr/sbin/mysqld
> >> >
> >> > So mysql has not gone away. I suspect the ossec-dbd process is
> failing. Is there a way to debug this to a log file? By the way I am
> running version 3.3.0 on centos 7.6.1810
> >> >
> >>
> >> Are there any corresponding messages in your mysql log files?
> >>
> >> > I need this to work soon! How many other users are having this
> problem with mysql?
> >> > Is this version 3.3.0 finished with testing or should I drop back a
> version?
> >> >
> >>
> >> 3.3.0 is finished. 3.4.0 is supposed to be out soon-ish, but I don't
> >> think anything changed in the dbd stuff.
> >>
> >> > thanks,
> >> > jerry
> >> >
> >> > On Fri, Sep 20, 2019 at 4:42 AM dan (ddp) wrote:
> >> >>
> >> >> On Thu, Sep 19, 2019 at 3:24 PM Jerry Lowry
>
Re: [ossec-list] ossec-dbd ERROR: Error executing query 'Insert into alert....'
On Tue, Sep 24, 2019 at 2:23 PM Jerry Lowry wrote:
>
> Dan,
>
> the only entries for today are as follows:
> 2019-09-24T08:02:49.637423Z 35 [Note] Aborted connection 35 to db: 'ossec'
> user: 'ossecuser' host: 'obed' (Got timeout reading communication packets)
> 2019-09-24T16:31:32.557059Z 36 [Note] Aborted connection 36 to db: 'ossec'
> user: 'ossecuser' host: 'obed' (Got an error reading communication packets)
> 2019-09-24T16:32:07.253522Z 38 [Note] Aborted connection 38 to db: 'ossec'
> user: 'ossecuser' host: 'obed' (Got an error reading communication packets)
> these errors do not coincide with the error from the dbd process at 04:07:17
> this morning. but then it looks like zulu time! PST is gmt+7.
>
> I have restarted all the ossec processes by hand and setup debugging on the
> dbd and mail processes. I also have a tail -f running on the ossec log.
> Nothing shows up as failing to connect for either the dbd or mail process.
> It just finished the syscheck and rootcheck in the last hour with no errors
> from either process.
>
> The mysql process statistics :
> ps -o etime= -p 12275
> 11-23:09:07
> it has been up 11 days +. The only access error in the mysql log are when I
> was resetting the host name for the user in the database, forgot to change
> the permissions, it now has been granted everything.
>
Ok, I setup mariadb a couple of hours ago and started feeding OSSEC
alerts into it. I have a bit over 7000 rows in the alert table.
I haven't seen any issues so far, but my alert volume is pretty small.
How many alerts are you seeing?
I won't have the time to look into dbd for a bit, but I'm sure there
are a lot of improvements that can be made.
> jerry
>
>
> On Tue, Sep 24, 2019 at 9:39 AM dan (ddp) wrote:
>>
>> On Tue, Sep 24, 2019 at 12:29 PM Jerry Lowry wrote:
>> >
>> > Dan,
>> > So I configured the database to use the host name for the ossec user.
>> > Restarted everything with ossec and it was able to log in initially. It
>> > ran most of the night and then at 4 am this morning it failed with the
>> > same error saying:
>> >
>> > 2019/09/24 04:07:17 ossec-dbd(5203): ERROR: Error executing query 'INSERT
>> > INTO
>> > alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,alertid,user,full_log,tld)
>> > VALUES ('1', '1002','2','1569323237', '1', '(null)', '0', '(null)', '0',
>> > '1569323234.0', '(null)', 'Sep 24 04:07:14 obed audispd: node=obed.edt.com
>> > type=ANOM_RBAC_INTEGRITY_FAIL msg=audit(1569323234.455:87010): pid=28134
>> > uid=0 auid=0 ses=2001 msg=`added=43772 removed=17 changed=2021
>> > exe="/usr/sbin/aide" hostname=? addr=? terminal=? res=failed`','')'.
>> > Error: 'MySQL server has gone away'.
>> > 2019/09/24 04:07:17 ossec-dbd(5209): INFO: Closing connection to database.
>> > 2019/09/24 04:07:17 ossec-dbd(5210): INFO: Attempting to reconnect to
>> > database.
>> > 2019/09/24 04:07:17 ossec-dbd: Connected to database 'ossec' at 'obed'.
>> > 2019/09/24 04:07:17 ossec-dbd(5204): ERROR: Database error. Unable to run
>> > query.
>> >
>> > A list of the mysql daemon process shows that it has been up and running
>> > since Sep 12.
>> > UIDPID PPID CSZ RSS PSR STIME TTY TIME CMD
>> > mysql12275 1 0 378222 204688 3 Sep12 ?00:12:57
>> > /usr/sbin/mysqld
>> >
>> > So mysql has not gone away. I suspect the ossec-dbd process is failing.
>> > Is there a way to debug this to a log file? By the way I am running
>> > version 3.3.0 on centos 7.6.1810
>> >
>>
>> Are there any corresponding messages in your mysql log files?
>>
>> > I need this to work soon! How many other users are having this problem
>> > with mysql?
>> > Is this version 3.3.0 finished with testing or should I drop back a
>> > version?
>> >
>>
>> 3.3.0 is finished. 3.4.0 is supposed to be out soon-ish, but I don't
>> think anything changed in the dbd stuff.
>>
>> > thanks,
>> > jerry
>> >
>> > On Fri, Sep 20, 2019 at 4:42 AM dan (ddp) wrote:
>> >>
>> >> On Thu, Sep 19, 2019 at 3:24 PM Jerry Lowry
>> >> wrote:
>> >> >
>> >> > Dan,
>> >> > Just check the server log again and found this error from the dbd
>> >> > process:
>> >> > 2019/09/19 04:07:04 ossec-dbd(5203): ERROR: Error executing query
>> >> > 'INSERT INTO
>> >> > alert(server_id,rule_id,level,timestamp,location_id,src_ip,src_port,dst_ip,dst_port,alertid,user,full_log,tld)
>> >> > VALUES ('1', '1002','2','1568891224', '1', '(null)', '0', '(null)',
>> >> > '0', '1568891220.0', '(null)', 'Sep 19 04:06:59 obed audispd:
>> >> > node=obed.edt.com type=ANOM_RBAC_INTEGRITY_FAIL
>> >> > msg=audit(1568891219.881:80020): pid=6481 uid=0 auid=0 ses=1145
>> >> > msg=`added=39777 removed=272 changed=2021 exe="/usr/sbin/aide"
>> >> > hostname=? addr=? terminal=? res=failed`','')'. Error: 'MySQL server
>> >> > has gone away'.
>> >> > 2019/09/19 04:07:04 ossec-dbd(5209): INFO: Closing connection to
>> >> > database.
>> >> > 2019/09/19 04:07:04 ossec-dbd(5210): INFO:
