Re: [Shinken-devel] MySQL Warning while broker daemon init
Status: Beta
Brought to you by:
naparuba
From: Michael F. <mic...@un...> - 2011-01-18 17:40:55
|
On 17.01.2011 16:39, nap wrote: > 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. without having looked at the code - how is this 'clean all' being done? is it threadsafe - meaning there's a lock somewhere to provent asynchronous inserts then? maybe you can draw a small picture, without python, only sql's and such ;-) kind regards, Michael > > > Jean > > On Mon, Jan 17, 2011 at 4:11 PM, Tanguy von Stebut > <tan...@gm... <mailto: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... > <mailto:tan...@gm...>> > > > > 2011/1/17 nap <nap...@gm... <mailto:nap...@gm...>> > > > > On Sun, Jan 16, 2011 at 11:10 PM, Michael Friedrich > <mic...@un... > <mailto: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... >> <mailto: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... >>> <mailto: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... >>>> <mailto: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... >>>> <mailto: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... >>>> <mailto: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... <mailto: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... >>> <mailto: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... <mailto: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... >> <mailto: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... <mailto: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... > <mailto: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... > <mailto: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... > <mailto: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 |