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



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.

kind regards,
Michael



Jean

On Sun, Jan 16, 2011 at 12:45 PM, Michael Friedrich <michael.friedr...@univie.ac.at <mailto:michael.friedr...@univie.ac.at>> 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
    <michael.friedr...@univie.ac.at
    <mailto:michael.friedr...@univie.ac.at>> 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
        <tanguy.von.ste...@gmail.com
        <mailto:tanguy.von.ste...@gmail.com>> 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' ,
            'napar...@gmail.com <mailto:napar...@gmail.com>' , '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
            Shinken-devel@lists.sourceforge.net
            <mailto:Shinken-devel@lists.sourceforge.net>
            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
        Shinken-devel@lists.sourceforge.net  
<mailto:Shinken-devel@lists.sourceforge.net>
        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
        Shinken-devel@lists.sourceforge.net
        <mailto:Shinken-devel@lists.sourceforge.net>
        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
    Shinken-devel@lists.sourceforge.net  
<mailto:Shinken-devel@lists.sourceforge.net>
    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
    Shinken-devel@lists.sourceforge.net
    <mailto:Shinken-devel@lists.sourceforge.net>
    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
Shinken-devel@lists.sourceforge.net
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
Shinken-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/shinken-devel

Reply via email to