Hello Michael. First, let me apologize for the broken formatting of Outlook for responses. I will try to correct this manually ... For the impatient ones, please scroll to the end of the mail for my conclusion. See the red marked chapters for more information. The rest is debugging ☺ …
Von: icinga-users [mailto:[email protected]] Im Auftrag von Michael Friedrich Gesendet: Montag, 16. Juni 2014 13:57 An: [email protected] Betreff: Re: [icinga-users] broken ido DB?! On 16.06.2014 13:37, [email protected]<mailto:[email protected]> wrote: Hi @ll. I have a problem with my icinga-web view when opening the tab with “Open problems”. It always shows “No data”, regardless if there are unhandled host or service problems or not. This happened after an update of icinga with problems updating the database. We had to try and handle the database problems manually. Well, it works, except the described issue. Is it possible to solve this without resetting the complete DB? There are several acknowledgements and scheduled downtimes that will get lost if we have to reset the database … Post the test output from https://wiki.icinga.org/display/testing/Icinga+Web+Testing [Dreyer, Jan] Thanks, I didn’t even knew about this page ☹ +----+--------------------+---------------------+---------------------+ | id | version | modified | created | +----+--------------------+---------------------+---------------------+ | 1 | icinga-web/v1.10.1 | 2014-05-12 09:50:11 | 2014-05-12 09:50:11 | +----+--------------------+---------------------+---------------------+ +--------------+----------+---------+---------------------+---------------------+ | dbversion_id | name | version | create_time | modify_time | +--------------+----------+---------+---------------------+---------------------+ | 1 | idoutils | 1.11.0 | 2014-04-10 13:30:26 | 2014-04-10 13:30:26 | +--------------+----------+---------+---------------------+---------------------+ We don’t have a “icinga” DB user, but are using separate users for ido-DB and icinga_web-DB: --- snipp --- root@icinga-web-host icinga-web/conf.d# grep localhost database-* database-ido.xml: <ae:parameter name="dsn">mysql://icinga-idoutils:XXXXXXX@localhost/icinga</ae:parameter> database-web.xml: <ae:parameter name="dsn">mysql://icinga_web:XXXXXXX@localhost/icinga_web</ae:parameter> --- snapp --- I “XXXX”-ed the password. Check: --- snipp --- root@icinga-web-host ~# mysql icinga -u icinga-idoutils -p -e 'select * from icinga_contacts;'>/dev/null; echo $? Enter password: 0 root@icinga-web-host ~# mysql icinga_web -u icinga_web -p -e 'select * from cronk;'>/dev/null; echo $? Enter password: 0 --- snapp --- Verify that Icinga IDOUtils<https://wiki.icinga.org/display/testing/Icinga+IDOUtils+Testing> is operational * idomod and ido2db socket type and location must match – check! * According to /var/log/syslog idomod is loaded when starting icinga (found “ido2db: Successfully connected to mysql database”) * Count of ido2db processes is equal to “2” – check! * (it’s a debian system) - IDO2DB=yes in /etc/default/icinga – check! # grep broker_module /etc/icinga/icinga.cfg broker_module=/usr/lib/icinga/idomod.so config_file=/etc/icinga/idomod.cfg # grep modules /etc/icinga/icinga.cfg [ only comments ] # grep idomod /etc/icinga/modules/idoutils.cfg grep: /etc/icinga/modules/idoutils.cfg: No such file or directory Check IDOUtils DB * Check the credentials defined in databases.xml – check! (see above) * check status, config tables – according to the tests on https://wiki.icinga.org/display/testing/Special+IDOUtils+Queries everything is OK. check if idomod is connected * ido2db will log to syslog when a client is connected – check! (See above) * ido2db will insert a successful connection attempt from idomod into the conninfo table. * There are two entries, with agent_names “IDOMOD” and “IDO2DB Trimming Thread” * Agent “IDOMOD” has a recent last_checkin_time, the checkin from Trimming Thread correlates to the startup time of icinga (today). check that ido2db processes data * set debug_level=-1 in ido2db.cfg and restart ido2db and icinga (in this order) – check! * tail the debug log file (location in ido2db.cfg) * creates a lot of data. Switched debug back to zero. Check Icinga Web logs * check icinga-web/log/* for logs, increase debug levels, and post that demanding you to * I see a lot of SQL queries. When trying to rebuild them on the DB there are errors. See for example http://pastebin.com/ykrB3HqJ. There are missing commas and even after adding these, mysql (5.5.37) sees an error near “where”. The string is declared as “DQL” not “SQL”. My knowledge about databases ends here ☹ Configs * it's essential that a config object is marked as is_active=1 in the objects table – check! * select * from icinga_hosts join icinga_objects on host_object_id=object_id where name1='myhostname' and is_active=1; * returns 1 row * select * from icinga_services join icinga_objects on service_object_id=object_id where name1='yourhostname' and name2='yourservicedesc' and is_active=1; o returns 1 row * we only use one (default) instance. Status * Active Host Status by yourhostname * One row * Active Service Status by yourhostname and yourservicedesc * One row * Program Status by yourinstancename * One row (“default”) Debug the Cronks * https://icinga-web-host/icinga-web/modules/cronks/viewproc/icinga-open-problems-template/json * Shows "success":true,"total":0,"rows":[] Conclusion I assume there is either something broken with the queries (I don’t think so, as I installed my (debian-)packages from debmon.org: --- snipp --- ii icinga 1.11.1-1~debmon70+1 amd64 host and network monitoring system - metapackage ii icinga-cgi 1.11.1-1~debmon70+1 amd64 host and network monitoring system - CGI scripts ii icinga-cgi-bin 1.11.1-1~debmon70+1 amd64 host and network monitoring system - CGI scripts ii icinga-common 1.11.1-1~debmon70+1 all host and network monitoring system - support files ii icinga-core 1.11.1-1~debmon70+1 amd64 host and network monitoring system - core files ii icinga-idoutils 1.11.1-1~debmon70+1 amd64 host and network monitoring system - icinga-dataobjects support ii icinga-web 1.11.0-1~debmon70+1 all host and network monitoring system - modern web interface ii icinga-web-lconf 1.3.2-1~preview+2~debmon70+1 all LConf web interface as a module for Icinga Web ii icinga-web-pnp 1.11.0-1~debmon70+1 all host and network monitoring system - PNP4Nagios web plugin --- snapp --- The other possible problem is – as I already assumed – that my “repairing” after upgrading to 1.11.1 has led to a defective DB. If no one here comes with a solution to this I will delete the databases and create fresh ones instead. Losing schedules and acknowledgements is bad but not more than half a day’s work … As a side note: I upgraded due to timezone issues. I now have a misalignment of two hours: Scheduled a downtime for a host yesterday. “DOWNTIMESTART” mail as well as “…END” mail were sent 2 hours after my schedule. Regards Jan Dreyer
_______________________________________________ icinga-users mailing list [email protected] https://lists.icinga.org/mailman/listinfo/icinga-users
