Re: [Sqlgrey-users] #Error Messages in Log

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

On 2014-01-06 09:57, Grooz, Marc (regio iT) wrote:
> Hi Dan,
>
> After the change the error looks like this:
>
> sqlgrey: dbaccess: warning: couldn't do query:#012INSERT INTO from_awl (sender_name, sender_domain, src, first_seen, last_seen) VALUES('abc','example.com','ip','2014-01-06 09:10:19',NOW()):#012Duplicate entry 'ipabc' for key 'PRIMARY', reconnecting to DB
>
> and
>
> sqlgrey: dbaccess: warning: couldn't do query:#012INSERT INTO domain_awl (sender_domain, src, first_seen, last_seen) VALUES('example.com','ip','2014-01-06 08:07:11',NOW()):#012Duplicate entry 'ipexample.com' for key 'PRIMARY', reconnecting to DB
Hi Marc..

I'm running out of ideas.
The last thing i can think of, is that you run more sqlgrey servers
against the same DB and "example.com" hits more than one sqlgrey server
in the same second, thus causing both of them to want to insert, but
only one can.
If so, this should occur less and less the more you get your *_awl lists
filled.

Either way, i dont think this should cause problems in production, as
all that happens, AFAIK, is a mysql reconnect.

- Dan

>
> kind regards
>
> Marc
>
>
> -----Ursprüngliche Nachricht-----
> Von: Grooz, Marc (regio iT) [mailto:Mar...@re...] 
> Gesendet: Montag, 6. Januar 2014 08:29
> An: SQLgrey users mailing-list
> Betreff: Re: [Sqlgrey-users] #Error Messages in Log
>
>
> Hi Dan,
>
> I modified the original 1.8 code, so you can see the exact error Message. I found that in a sqlgrey forum -> http://sourceforge.net/p/sqlgrey/bugs/16/.
> After reinstall sqlgrey without the cluster-code the modification was lost. I applied it again and show you the output.
>
> There is no Firewall between the sqlgrey server and the db server ans the db connection ist established during the error.
>
> Kind regards
>
> Marc
>
> -----Ursprüngliche Nachricht-----
> Von: da...@ha... [mailto:da...@ha...]
> Gesendet: Sonntag, 5. Januar 2014 10:35
> An: SQLgrey users mailing-list
> Cc: Grooz, Marc (regio iT)
> Betreff: Re: [Sqlgrey-users] #Error Messages in Log
>
> On 2014-01-02 13:28, Grooz, Marc (regio iT) wrote:
>> Hi Dan,
>>
>> the error still appear after switching from cluster to dbi support:
>>
>> sqlgrey: warning: Use of uninitialized value $DBI::errstr in concatenation (.) or string at /usr/sbin/sqlgrey line 246.
>> sqlgrey: dbaccess: warning: couldn't do query:#012INSERT INTO 
>> domain_awl (sender_domain, src, first_seen, last_seen)
>> VALUES('example.com','1.2.3','2013-12-11 03:20:02',NOW()):#012, 
>> reconnecting to DB
> But this isn't the same error. The original error was "duplicate entry".
> The actual mysql-error message should be at the end of the line, between
> #012 and ", reconnecting".
> It would appear that this one has no actual sql-error message (which also explains the warning above about "$DBI:errstr" being "uninitialized".)
>
> Im guessing it means that the connection actually was lost to the db, eg. mysql killed the connection due to idle-timeout, someone restarted the mysql server, some firewall dropped the state. In that case, its not even an error, more an information to let you know that we are reconnecting to the sql-server..
>
> This can happen in very low-traffic sites, where there can be so long between receiving emails, that you reach the configured idle-timeout in the sql server or a state timeout on a firewall along the way.
>
> But i am fairly sure that the issue is external to sqlgrey and is either something like the above, or something configured oddly.
>
> - Dan
>
>
>
>> kind regards
>>
>> Marc
>>
>> -----Ursprüngliche Nachricht-----
>> Von: Grooz, Marc (regio iT)
>> Gesendet: Freitag, 20. Dezember 2013 13:21
>> An: 'SQLgrey users mailing-list'
>> Betreff: AW: [Sqlgrey-users] #Error Messages in Log
>>
>> Hi Dan,
>>
>> #(i assume you put the "x" into the triplet, to hide the ip?)
>>
>> Yes, you are right.
>>
>> # First, if i understand you correctly, you ONLY have one database-server?
>> Yes, again
>>
>> # So with only one mysql-server, i would completly disable the dbcluster option, just to lower the complexity of debugging.
>> I try that and come later on to you again and report if the error is still there.
>>
>> Kind regards
>>
>> Marc
>>
>> -----Ursprüngliche Nachricht-----
>> Von: da...@ha... [mailto:da...@ha...]
>> Gesendet: Donnerstag, 19. Dezember 2013 20:35
>> An: SQLgrey users mailing-list
>> Betreff: Re: [Sqlgrey-users] #Error Messages in Log
>>
>> Hi Marc.
>>
>> Well the error message your'e getting, specifically says "Duplicate entry 'x--undef---undef-'".
>> The from AWL table has a primary-key that is made up of the fields src + sender_domain + sender_name. Lets call this a "triplet".
>>
>> (i assume you put the "x" into the triplet, to hide the ip?)
>>
>> Primary keys must be unique. So inserting with a key value with the triplet of '192.168.0' + '-undef-' + '-undef-' is fine the first time, but inserting the exact same values will yield a "Duplicate entry" error.
>>
>> Now, sqlgrey shouldnt never have to insert the same keys twice, but i can think of a couple of scenarios where they in theory could happen..
>>
>> First, if i understand you correctly, you ONLY have one database-server?
>> If this is correct, you do not need to enable db-clustingering. I wrote the db-clustering code specifically for having multiple server, where you write to one "master" and read from a lot of slaves. (slaves as in MySQL replication slaves).
>> Also i never tested this code on PostGRES and im unsure how that would work.
>>
>> So with only one mysql-server, i would completly disable the dbcluster option, just to lower the complexity of debugging.
>>
>>
>> If you ARE running a mysql setup with a master and a slave, this error could in theory occur if your MySQL replication has stopped, failed, is slow or way behind. Because, if the new triplets arent copied to the slave, it wont be there when you read from your read_host. Thus, sqlgrey thinks this is a new triplet and gladly tries to insert into the master, where it actually already existed and boom-goes-the-dynamite.... you get the Duplicate entry error.
>>
>> Regards
>> - Dan
>>
>>
>>
>> On 2013-12-19 11:45, Grooz, Marc (regio iT) wrote:
>>> Hi Karl,
>>>
>>> I always see only one Connection to the database?! So if you're right there must be a query limit per connection or something.
>>>
>>> Kind regards
>>>
>>> Marc
>>>
>>>
>>> -----Ursprüngliche Nachricht-----
>>> Von: Karl O. Pinc [mailto:ko...@me...]
>>> Gesendet: Mittwoch, 18. Dezember 2013 17:32
>>> An: SQLgrey users mailing-list
>>> Betreff: Re: [Sqlgrey-users] #Error Messages in Log
>>>
>>> Hi Marc,
>>>
>>> Then there's a db connectivity problem of some sort.
>>> Something that prevents the db from even seeing the connection 
>>> attempt.  (Unless your db is so lame it's not logging something.)
>>>
>>> By the by, I don't believe that these messages are in indication that any mail is lost, etc.
>>> They are notifications of an unexpected condition.
>>>
>>> Perhaps your db has concurrency problems?  (I always use Postgres 
>>> because it's always been most featurefull and caused the fewest
>>> headaches.)
>>>
>>> On 12/18/2013 10:25:51 AM, Grooz, Marc (regio iT) wrote:
>>>> Hi Karl,
>>>>
>>>> the syslog, db log and db error log are empty of error messages in 
>>>> that time.
>>>>
>>>> Kind regards
>>>>
>>>> Marc
>>>>
>>>> -----Ursprüngliche Nachricht-----
>>>> Von: Karl O. Pinc [mailto:ko...@me...]
>>>> Gesendet: Dienstag, 17. Dezember 2013 16:48
>>>> An: SQLgrey users mailing-list
>>>> Betreff: Re: [Sqlgrey-users] #Error Messages in Log
>>>>
>>>> On 12/17/2013 01:53:14 AM, Grooz, Marc (regio iT) wrote:
>>>>> Hi,
>>>>>
>>>>> i configure tree servers with dbcluster support. They only use one 
>>>>> Database as readhost and dbhost. Sometimes I get these error
>>>>> messages:
>>>>>
>>>>> sqlgrey: dbaccess: warning: couldn't do query:#012INSERT INTO
>>>> from_awl
>>>>> (sender_name, sender_domain, src, first_seen, last_seen)
>>>>> VALUES('-undef-','-undef-','x','2013-12-16
>>>>> 08:40:16',NOW()):#012Duplicate entry 'x--undef---undef-' for key 
>>>>> 'PRIMARY', reconnecting to DB
>>>>>
>>>>> any idea?
>>>> Always the usual to check first.  (And always easy to forget.) What 
>>>> do the db logs and the os logs say?
>>>>
>>>>
>>>> Karl <ko...@me...>
>>>> Free Software:  "You don't pay back, you pay forward."
>>>>                  -- Robert A. Heinlein
>>>>
>>>> --------------------------------------------------------------------
>>>> -
>>>> -
>>>> -------- Rapidly troubleshoot problems before they affect your 
>>>> business. Most IT organizations don't have a clear picture of how 
>>>> application performance affects their revenue. With AppDynamics, you 
>>>> get 100% visibility into your Java,.NET, & PHP application. Start 
>>>> your 15-day FREE TRIAL of AppDynamics Pro!
>>>> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/
>>>> ostg.clktrk
>>>> _______________________________________________
>>>> Sqlgrey-users mailing list
>>>> Sql...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>>>>
>>>> --------------------------------------------------------------------
>>>> -
>>>> -
>>>> -------- Rapidly troubleshoot problems before they affect your 
>>>> business. Most IT organizations don't have a clear picture of how 
>>>> application performance affects their revenue. With AppDynamics, you 
>>>> get 100% visibility into your Java,.NET, & PHP application. Start 
>>>> your 15-day FREE TRIAL of AppDynamics Pro!
>>>> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/
>>>> ostg.clktrk
>>>> _______________________________________________
>>>> Sqlgrey-users mailing list
>>>> Sql...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>>>>
>>>>
>>>
>>> Karl <ko...@me...>
>>> Free Software:  "You don't pay back, you pay forward."
>>>                  -- Robert A. Heinlein
>>>
>>> ---------------------------------------------------------------------
>>> -
>>> -------- Rapidly troubleshoot problems before they affect your 
>>> business. Most IT organizations don't have a clear picture of how application performance affects their revenue. With AppDynamics, you get 100% visibility into your Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
>>> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.
>>> c lktrk _______________________________________________
>>> Sqlgrey-users mailing list
>>> Sql...@li...
>>> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>>>
>>> ---------------------------------------------------------------------
>>> -
>>> -------- Rapidly troubleshoot problems before they affect your 
>>> business. Most IT organizations don't have a clear picture of how 
>>> application performance affects their revenue. With AppDynamics, you 
>>> get 100% visibility into your Java,.NET, & PHP application. Start 
>>> your 15-day FREE TRIAL of AppDynamics Pro!
>>> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.
>>> c lktrk _______________________________________________
>>> Sqlgrey-users mailing list
>>> Sql...@li...
>>> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>> ----------------------------------------------------------------------
>> -------- Rapidly troubleshoot problems before they affect your 
>> business. Most IT organizations don't have a clear picture of how application performance affects their revenue. With AppDynamics, you get 100% visibility into your Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
>> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.c
>> lktrk _______________________________________________
>> Sqlgrey-users mailing list
>> Sql...@li...
>> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>>
>> ----------------------------------------------------------------------
>> -------- Rapidly troubleshoot problems before they affect your 
>> business. Most IT organizations don't have a clear picture of how 
>> application performance affects their revenue. With AppDynamics, you 
>> get 100% visibility into your Java,.NET, & PHP application. Start your 
>> 15-day FREE TRIAL of AppDynamics Pro!
>> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.c
>> lktrk _______________________________________________
>> Sqlgrey-users mailing list
>> Sql...@li...
>> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>
> ------------------------------------------------------------------------------
> Rapidly troubleshoot problems before they affect your business. Most IT organizations don't have a clear picture of how application performance affects their revenue. With AppDynamics, you get 100% visibility into your Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>
> ------------------------------------------------------------------------------
> Rapidly troubleshoot problems before they affect your business. Most IT organizations don't have a clear picture of how application performance affects their revenue. With AppDynamics, you get 100% visibility into your Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>
> ------------------------------------------------------------------------------
> Rapidly troubleshoot problems before they affect your business. Most IT 
> organizations don't have a clear picture of how application performance 
> affects their revenue. With AppDynamics, you get 100% visibility into your 
> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users