Re: [Shinken-devel] MySQL Warning while broker daemon init

[nap <nap...@gm...>]

Duplicate on nagios_hosts? That seems very very strange, there is a clean
all before all inserts. I don't see how it can happened. If you can, launch
the broker in debug mode and send us this log when it occurs again.


Jean

On Mon, Jan 17, 2011 at 4:11 PM, Tanguy von Stebut <
tan...@gm...> wrote:

> Hello again,
>
> I've been working further on my centreon + shinken prototype, and I
> encountered some real problems due to integrity violation in tables
> nagios_hosts and nagios-hostgroups (duplicates again), but I didn't keep the
> log, sorry !!
> I had to reload my VM template to recover a nice configuration. As I'm in
> prototyping phase, this doesn't matter, but I doubt (:-D) this could be in
> production (:/)...
>
> So the (wrong) behavior was that I couldn't add a targetted host to
> monitoring, huh... well I think that's critical.That would be such a shame
> in production, don't yall think so ?
>
> Hope I'll get some more support here, as I'm starting desperating
> myself....
>
> Best regards,
>
> Tanguy
>
>
>
>
> 2011/1/17 Tanguy von Stebut <tan...@gm...>
>
>
>>
>> 2011/1/17 nap <nap...@gm...>
>>
>>
>>>
>>>  On Sun, Jan 16, 2011 at 11:10 PM, Michael Friedrich <
>>> mic...@un...> wrote:
>>>
>>>>  On 16.01.2011 13:57, nap wrote:
>>>>
>>>> But the unique need a select before, so it's too performance impacting,
>>>> especially with "this" database schema. In fact the only part where we have
>>>> duplication are the "common parts" : at the begining of all "configuration"
>>>> incorporation, there are a big "clean all my instance" pass, so the only
>>>> part that do not have a instance_id will have problem, like contacts or
>>>> groups if I'm not wrong. So the insert with duplication are really the same
>>>> value, so insert/delete or replace are the same thing. Of source only for
>>>> mysql, other will still have problems for this.
>>>>
>>>>
>>>> the unique doesn't necessarily need a select before, you can also
>>>> rebuild the logic of the 'on duplicate key' feature from mysql, basically
>>>> sort of 'upsert'. this has been discussed all over the place on postgres
>>>> mailinglists, and also oracle (but oracle is kind of 'special'). on oracle
>>>> you might find the merge trick, which idoutils is using currently (where
>>>> doing a merge on a single table, matching on the unique constraint and doing
>>>> an insert or update either way). on postgresql you are required to build
>>>> your own logic - or risk two queries. i have that currently implemented the
>>>> way: try an update, if no rows affected, do an insert (which is rather bad
>>>> in that situation).
>>>> better would be a stored procedure for that, doing the exact same thing
>>>> as mysql does internally when hitting the insert ... on duplicate key update
>>>> ...
>>>> i've been keeping something like that on my todo list for a while now,
>>>> but not yet implemented. https://dev.icinga.org/issues/146
>>>>
>>> Thanks for this. So in Mysql it's trivial, and in others it's rather
>>> complex :)
>>>
>>
>> Indeed
>>
>>
>>>
>>> I'll look at your code to see the tricks, sounds quite very good :)
>>>
>>>
>>>
>>>>
>>>>
>>>>
>>>>
>>>> For Centreon I think it will have an impact for this, after all, here
>>>> the problem is delete/insert or replace the same data, it's not like
>>>> anothers values are impacted, so there won't be (bad) impacts, just less
>>>> warning for the more common case (mysql), and others (Oracle, Postgresql)
>>>> won't change (do they support ON DUPLICATE ?).
>>>>
>>>>
>>>> If postgres/oracle have the unique constraint set like mysql, they will
>>>> surely spit out the same errors on unique constraint violation then. it
>>>> depends how you handle that over there, as mentioned above with an upsert
>>>> logic.
>>>>
>>> Hopefully here the duplicate is not a "huge problem" because it's really
>>> the same data, so it's jsut notice instead of warning. But if we can manage
>>> it in a clean way it's better of course :)
>>>
>> Thanky, I knew it wouldn't to bothering at last ;)
>>
>>
>>>  Thanks for all the tips :)
>>>
>>
>> I'm feeling impressed. Further, I would appreciate to see them applied :D
>> Real thanks,
>>
>> Tanguy
>>
>>
>>>  Jean
>>>
>>>
>>>>
>>>> kind regards,
>>>> Michael
>>>>
>>>>
>>>>
>>>> Jean
>>>>
>>>> On Sun, Jan 16, 2011 at 12:45 PM, Michael Friedrich <
>>>> mic...@un...> wrote:
>>>>
>>>>>  On 16.01.2011 08:33, nap wrote:
>>>>>
>>>>> Oh thanks a lot, so I'll make it mysql backend only :)
>>>>>
>>>>>
>>>>> you might check the unique key instead and adapt the schema a bit then
>>>>> (not causing duplicate key violations), if it's not up to major changes/many
>>>>> rows then. but i am not sure how the centreon select logic works, so i'd
>>>>> vote for a clean (old grown compatible) solution then.
>>>>>
>>>>> kind regards,
>>>>> Michael
>>>>>
>>>>>
>>>>>
>>>>> Jean
>>>>>
>>>>> On Sat, Jan 15, 2011 at 6:04 PM, Michael Friedrich <
>>>>> mic...@un...> wrote:
>>>>>
>>>>>>  On 15.01.2011 13:31, nap wrote:
>>>>>>
>>>>>> I think I'll change the insert into replace. There are some tables
>>>>>> that don't have an id for the configuration instance, and so duplicate
>>>>>> cannot be avoid.
>>>>>>
>>>>>>
>>>>>> consider that replace into is not available for postgres/oracle.
>>>>>> you'll need kind of an upsert logic either realized by merge in oracle, or
>>>>>> straight upsert procedures. mysql is simple with the insert on duplicate key
>>>>>> update statement as an alternative, but this is non sql standard.
>>>>>>
>>>>>> kind regards,
>>>>>> Michael
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> Jean
>>>>>>
>>>>>> On Fri, Jan 14, 2011 at 5:05 PM, Tanguy von Stebut <
>>>>>> tan...@gm...> wrote:
>>>>>>
>>>>>>> Hello folks,
>>>>>>>
>>>>>>> I've been trying to use Shinken with Centreon 2 as it is actually
>>>>>>> "wired" with a NdoDB over MySQL by default, and I also needed to
>>>>>>> produce a PoC of Shinken with a DB broker. For sure Centreon is still
>>>>>>> attractive for my boss...
>>>>>>>
>>>>>>> So, I configured the broker to connect to a Centreon - ndo - db that
>>>>>>> resides on an other VM.
>>>>>>> I could verify a simple command line connection with the user
>>>>>>> centreon.
>>>>>>>
>>>>>>> But now, when looking at the broker' logs, I can see Database
>>>>>>> integrity Warning returned by the remote MySQL DB.
>>>>>>>
>>>>>>> Ok, staying positive, I can say the broker' conf is good, but I'm
>>>>>>> worrying about the do-ability of the solution. Please let me know if
>>>>>>> you have some clue :)
>>>>>>>
>>>>>>> Here comes an extract of the broker o' log :
>>>>>>>
>>>>>>> {code}[CODE]
>>>>>>> IOError: [Errno 2] No such file or directory:
>>>>>>> '/usr/local/shinken/var/archives/nagios-01-14-2011-
>>>>>>> 00.log'
>>>>>>>
>>>>>>> /usr/lib/python2.6/site-packages/Shinken-0.4-py2.6.egg/shinken/db_mysql.py:58:
>>>>>>> Warning: Out of ra
>>>>>>> nge value adjusted for column 'last_command_check' at row 1
>>>>>>>  self.db_cursor.execute(query)
>>>>>>>
>>>>>>> /usr/lib/python2.6/site-packages/Shinken-0.4-py2.6.egg/shinken/db_mysql.py:58:
>>>>>>> Warning: Out of ra
>>>>>>> nge value adjusted for column 'last_log_rotation' at row 1
>>>>>>>  self.db_cursor.execute(query)
>>>>>>> [MysqlDB] Warning : a query raise an integrity error : INSERT INTO
>>>>>>> nagios_contacts  (instance_id , alias , contact_object_id ,
>>>>>>> host_notifications_enabled , pager_address ,
>>>>>>> service_notifications_enabled , email_address , contact_id  ) VALUES
>>>>>>> ('2' , 'none' , '2' , '1' , 'none' , '1' , 'nap...@gm...' ,
>>>>>>> '2'
>>>>>>> ), (1062, "Duplicate entry '2' for key 1")
>>>>>>> [MysqlDB] Warning : a query raise an integrity error : INSERT INTO
>>>>>>> nagios_contactgroups  (contactgroup_id , instance_id , config_type ,
>>>>>>> alias , contactgroup_object_id  ) VALUES ('1' , '2' , '0' , 'admins'
>>>>>>> ,
>>>>>>> '1'  ), (1062, "Duplicate entry '1' for key 1")
>>>>>>> [MysqlDB] Warning : a query raise an integrity error : INSERT INTO
>>>>>>> nagios_hostgroups  (instance_id , alias , config_type , hostgroup_id
>>>>>>> ,
>>>>>>> hostgroup_object_id  ) VALUES ('2' , 'Serveurs de la bulle M' , '0' ,
>>>>>>> '1' , '75'  ), (1062, "Duplicate entry '1' for key 1")
>>>>>>> [/CODE]{code}
>>>>>>>
>>>>>>> Regards,
>>>>>>>
>>>>>>> Tanguy von Stebut
>>>>>>>
>>>>>>>
>>>>>>> ------------------------------------------------------------------------------
>>>>>>> Protect Your Site and Customers from Malware Attacks
>>>>>>> Learn about various malware tactics and how to avoid them. Understand
>>>>>>> malware threats, the impact they can have on your business, and how
>>>>>>> you
>>>>>>> can protect your company and customers by using code signing.
>>>>>>> http://p.sf.net/sfu/oracle-sfdevnl
>>>>>>> _______________________________________________
>>>>>>> Shinken-devel mailing list
>>>>>>> Shi...@li...
>>>>>>> https://lists.sourceforge.net/lists/listinfo/shinken-devel
>>>>>>>
>>>>>>
>>>>>>
>>>>>> ------------------------------------------------------------------------------
>>>>>> Protect Your Site and Customers from Malware Attacks
>>>>>> Learn about various malware tactics and how to avoid them. Understand
>>>>>> malware threats, the impact they can have on your business, and how you
>>>>>> can protect your company and customers by using code signing.http://p.sf.net/sfu/oracle-sfdevnl
>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> Shinken-devel mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/shinken-devel
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> ------------------------------------------------------------------------------
>>>>>> Protect Your Site and Customers from Malware Attacks
>>>>>> Learn about various malware tactics and how to avoid them. Understand
>>>>>> malware threats, the impact they can have on your business, and how
>>>>>> you
>>>>>> can protect your company and customers by using code signing.
>>>>>> http://p.sf.net/sfu/oracle-sfdevnl
>>>>>> _______________________________________________
>>>>>> Shinken-devel mailing list
>>>>>> Shi...@li...
>>>>>> https://lists.sourceforge.net/lists/listinfo/shinken-devel
>>>>>>
>>>>>>
>>>>>
>>>>> ------------------------------------------------------------------------------
>>>>> Protect Your Site and Customers from Malware Attacks
>>>>> Learn about various malware tactics and how to avoid them. Understand
>>>>> malware threats, the impact they can have on your business, and how you
>>>>> can protect your company and customers by using code signing.http://p.sf.net/sfu/oracle-sfdevnl
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> Shinken-devel mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/shinken-devel
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> ------------------------------------------------------------------------------
>>>>> Protect Your Site and Customers from Malware Attacks
>>>>> Learn about various malware tactics and how to avoid them. Understand
>>>>> malware threats, the impact they can have on your business, and how you
>>>>> can protect your company and customers by using code signing.
>>>>> http://p.sf.net/sfu/oracle-sfdevnl
>>>>> _______________________________________________
>>>>> Shinken-devel mailing list
>>>>> Shi...@li...
>>>>> https://lists.sourceforge.net/lists/listinfo/shinken-devel
>>>>>
>>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> Protect Your Site and Customers from Malware Attacks
>>>> Learn about various malware tactics and how to avoid them. Understand
>>>> malware threats, the impact they can have on your business, and how you
>>>> can protect your company and customers by using code signing.http://p.sf.net/sfu/oracle-sfdevnl
>>>>
>>>>
>>>> _______________________________________________
>>>> Shinken-devel mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/shinken-devel
>>>>
>>>>
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> Protect Your Site and Customers from Malware Attacks
>>>> Learn about various malware tactics and how to avoid them. Understand
>>>> malware threats, the impact they can have on your business, and how you
>>>> can protect your company and customers by using code signing.
>>>> http://p.sf.net/sfu/oracle-sfdevnl
>>>> _______________________________________________
>>>> Shinken-devel mailing list
>>>> Shi...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/shinken-devel
>>>>
>>>>
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Protect Your Site and Customers from Malware Attacks
>>> Learn about various malware tactics and how to avoid them. Understand
>>> malware threats, the impact they can have on your business, and how you
>>> can protect your company and customers by using code signing.
>>> http://p.sf.net/sfu/oracle-sfdevnl
>>> _______________________________________________
>>> Shinken-devel mailing list
>>> Shi...@li...
>>> https://lists.sourceforge.net/lists/listinfo/shinken-devel
>>>
>>>
>>
>>
>> --
>> Tanguy von Stebut
>>
>> 4 rue d'Aligre
>> 75012 PARIS
>>
>> 06 60 90 21 03
>>
>> This message is for the designated recipient only and may contain
>> privileged, proprietary, or otherwise private information. If you have
>> received it in error, please notify the sender immediately and delete the
>> original. Any other use of the email by you is prohibited.
>>
>> Ce message s'adresse aux destinataires désignés uniquement car il est
>> susceptible de contenir des informations à caractère privé, propriétaires,
>> privilégiées, ou autre. Si vous l'avez reçu par erreur, merci d'en informer
>> l'émetteur immédiatement et d'en supprimer l'original. Toute autre
>> utilisation de ce courriel par vous est interdit.
>>
>>
>>
>>
>
>
> ------------------------------------------------------------------------------
> Protect Your Site and Customers from Malware Attacks
> Learn about various malware tactics and how to avoid them. Understand
> malware threats, the impact they can have on your business, and how you
> can protect your company and customers by using code signing.
> http://p.sf.net/sfu/oracle-sfdevnl
> _______________________________________________
> Shinken-devel mailing list
> Shi...@li...
> https://lists.sourceforge.net/lists/listinfo/shinken-devel
>
>