title: No Historical Data in IDO DB with Icinga2 Web2
body: <h1>Context</h1>
  
  <p>I'm working on provisioning <code>Icinga2</code> <code>Web2</code> with <code>IDO PostgreSQL</code> and <code>IDO MySQL</code> using <code>Ansible</code> on <code>CentOS 7</code>. </p>
  
  <p>I base my work on the great <a href="https://github.com/Icinga/icinga2-ansible" rel="nofollow">https://github.com/Icinga/icinga2-ansible</a> roles. I have forked the project (mickael-ange/icinga2-ansible) to added more automation and support such as IDO PostgreSQL. I'm using PostgreSQL server 9.4.</p>
  
  <p>Well, I ends up with a version of <code>icinga2-ansible-web2-ui</code> role which installs Icinga2 Web2 with IDO PostgreSQL or IDO MySQL automatically <strong>without wizard</strong>.</p>
  
  <h1>My Problem</h1>
  
  <p>The server seems to work properly at the exception that I cannot figure out how to get data in Icinga2 Web2 interface such as:</p>
  
  <ul>
  <li><code>Comments</code> and <code>Downtimes</code> from <code>Overview</code> menu</li>
  <li><code>Event Grid</code>, <code>Event Overview</code>, <code>Notifications</code>, <code>Timeline</code> from <code>History</code> menu</li>
  </ul>
  
  <p>I can create <code>Comments</code> and <code>Schedule Downtimes</code>, but they don't get listed in the UI. Downtimes are processed correctly.</p>
  
  <p>I can send <code>Notifications</code> but they don't get listed in the UI as well. Notifications are sent out when SELinux is in permissive mode.</p>
  
  <p>My questions is: <strong>is there any IDO or Icinga2 Web2 configuration to get the database populate with Comments, Downtimes, etc..?</strong></p>
  
  <p>My <code>/etc/icinga2/features-enabled/ido-pgsql.conf</code></p>
  
  <pre><code>library "db_ido_pgsql"
  
  object IdoPgsqlConnection "ido-pgsql" {
    user = "icinga"
    password = "icinga"
    host = "localhost"
    database = "icinga"
    table_prefix = "icinga_"
    instance_name = "icinga2"
    instance_description = "icinga2 instance"
  
    cleanup = {
      downtimehistory_age = 48h
      logentries_age = 31d
    }
  
    categories = DbCatConfig | DbCatState
  }
  </code></pre>
  
  <h1>Notes</h1>
  
  <h2>Note 1</h2>
  
  <p>I have already searched in most of the documentation of Icinga2 project and forums I could find but no cigar. I'm surprise none gets this problem before so it is probably a problem on my side.</p>
  
  <h2>Note 2</h2>
  
  <p>After chatting on Icinga IRC channel and doing more tests I figure out that <strong>my problem only happens with PostgreSQL backend</strong>. It is working as expected with MySQL backend. </p>
  
  <h1>Debugging Sessions</h1>
  
  <h2>Debugging session 1</h2>
  
  <p>I have gathered more info/logs about my environments.</p>
  
  <h3>PostgreSQL Permissions</h3>
  
  <p>I checked my PostgreSQL permissions for icinga user:</p>
  
  <ul>
  <li><code>TABLE</code> permissions are set to <code>SELECT</code>,<code>INSERT</code>,<code>UPDATE</code>,<code>DELETE</code></li>
  <li><code>SEQUENCE</code> permissions are set to <code>USAGE</code></li>
  </ul>
  
  <p>I even tried configuring <code>postgres</code> as user to access to the backend (<code>icinga_ido</code> and <code>icingaweb_ido</code> resources) but I'm still not getting any <code>Comments</code> listed in the UI (after having created a comment).</p>
  
  <p>Here are the PosgreSQL permissions listed by <code>\dp</code> command:</p>
  
  <pre><code>icinga=&gt; \dp
                                                               Access privileges
   Schema |                              Name                               |   Type   |     Access privileges     | Column access privileges 
  --------+-----------------------------------------------------------------+----------+---------------------------+--------------------------
   public | icinga_acknowledgements                                         | table    | postgres=arwdDxt/postgres+| 
          |                                                                 |          | icinga=arwd/postgres      | 
   public | icinga_acknowledgements_acknowledgement_id_seq                  | sequence | postgres=rwU/postgres    +| 
          |                                                                 |          | icinga=U/postgres         | 
   public | icinga_commands                                                 | table    | postgres=arwdDxt/postgres+| 
          |                                                                 |          | icinga=arwd/postgres      | 
   public | icinga_commands_command_id_seq                                  | sequence | postgres=rwU/postgres    +| 
          |                                                                 |          | icinga=U/postgres         | 
   public | icinga_commenthistory                                           | table    | postgres=arwdDxt/postgres+| 
          |                                                                 |          | icinga=arwd/postgres      | 
   public | icinga_commenthistory_commenthistory_id_seq                     | sequence | postgres=rwU/postgres    +| 
          |                                                                 |          | icinga=U/postgres         | 
  &lt;truncated&gt;
  </code></pre>
  
  <p>Furthermore, I have no error in PostgreSQL logs (e.g. /var/lib/pgsql/9.4/data/pg_log/postgresql-Thu.log) when creatinh <code>Comment</code> from Icinga UI.</p>
  
  <p>However, I figured out there are errors when trying to browse <code>SEQUENCE</code> from <code>pgAdmin</code> UI using <code>icinga</code> user (no error with <code>postgres</code> user):</p>
  
  <pre><code>&lt; 2016-01-21 03:55:52.436 GMT &gt;STATEMENT:  SELECT last_value, min_value, max_value, cache_value, is_cycled, increment_by, is_called
        FROM icinga_acknowledgements_acknowledgement_id_seq
  </code></pre>
  
  <p>But I'm not sure if it makes sense or not as <code>icinga</code> user has only <code>USAGE</code> permission on <code>SEQUENCE</code>.</p>
  
  <h3>Comparing PostgreSQL and MySQL Backends' Debug Logs</h3>
  
  <p>So I ends up with comparing Icinga debug logs on both sides (MySQL IDO and PostgreSQL IDO) when I creates a comment. I can see that only with MySQL backend there are the SQL statements I was looking for, whom insert data in <code>icinga_externalcommands</code>, <code>icinga_comments</code>,  and <code>icinga_commenthistory</code> tables.</p>
  
  <p>I don't have enough reputation to provide more than 2 links (e.g. pastebin). So here are the logs.</p>
  
  <p><strong>Adding a <code>Comment</code> with PostgreSQL backend:</strong></p>
  
  <pre><code>[2016-01-21 04:14:00 +0000] information/ExternalCommandListener: Executing external command: [1453349640] ADD_HOST_COMMENT;icinga2-web2-postgres;1;icingaadmin;dededewdwwewdew
  [2016-01-21 04:14:00 +0000] debug/DbEvents: add external command history
  [2016-01-21 04:14:00 +0000] notice/ExternalCommandProcessor: Creating comment for host icinga2-web2-postgres
  [2016-01-21 04:14:00 +0000] information/ConfigCompiler: Compiling config file: /var/lib/icinga2/api/packages/_api/icinga2-web2-postgres-1453347670-1/conf.d/comments/icinga2-web2-postgres!icinga2-web2-postgres-1453349640-1.conf
  [2016-01-21 04:14:00 +0000] information/ConfigItem: Committing config items
  [2016-01-21 04:14:00 +0000] warning/ApplyRule: Apply rule 'satellite-host' (in /etc/icinga2/conf.d/satellite.conf: 29:1-29:41) for type 'Dependency' does not match anywhere!
  [2016-01-21 04:14:00 +0000] warning/ApplyRule: Apply rule '' (in /etc/icinga2/conf.d/services.conf: 57:1-57:65) for type 'Service' does not match anywhere!
  [2016-01-21 04:14:00 +0000] warning/ApplyRule: Apply rule '' (in /etc/icinga2/conf.d/services.conf: 65:1-65:53) for type 'Service' does not match anywhere!
  [2016-01-21 04:14:00 +0000] information/ConfigItem: Instantiated 1 Comment.
  [2016-01-21 04:14:00 +0000] information/ConfigItem: Triggering Start signal for config items
  [2016-01-21 04:14:00 +0000] information/ConfigItem: Activated all objects.
  [2016-01-21 04:14:00 +0000] notice/Comment: Added comment 'icinga2-web2-postgres!icinga2-web2-postgres-1453349640-1'.
  [2016-01-21 04:14:01 +0000] debug/IdoPgsqlConnection: Query: COMMIT
  [2016-01-21 04:14:01 +0000] debug/IdoPgsqlConnection: Query: BEGIN
  </code></pre>
  
  <p><strong>Adding a <code>Comment</code> with MySQL backend:</strong></p>
  
  <pre><code>[2016-01-21 04:12:49 +0000] information/ExternalCommandListener: Executing external command: [1453349569] ADD_HOST_COMMENT;icinga2-web2-mysql;1;icingaadmin;yggygyyj
  [2016-01-21 04:12:49 +0000] debug/DbEvents: add external command history
  [2016-01-21 04:12:49 +0000] notice/ExternalCommandProcessor: Creating comment for host icinga2-web2-mysql
  [2016-01-21 04:12:49 +0000] information/ConfigCompiler: Compiling config file: /var/lib/icinga2/api/packages/_api/icinga2-web2-mysql-1453347256-1/conf.d/comments/icinga2-web2-mysql!icinga2-web2-mysql-1453349569-0.conf
  [2016-01-21 04:12:49 +0000] information/ConfigItem: Committing config items
  [2016-01-21 04:12:49 +0000] warning/ApplyRule: Apply rule 'satellite-host' (in /etc/icinga2/conf.d/satellite.conf: 29:1-29:41) for type 'Dependency' does not match anywhere!
  [2016-01-21 04:12:49 +0000] warning/ApplyRule: Apply rule '' (in /etc/icinga2/conf.d/services.conf: 57:1-57:65) for type 'Service' does not match anywhere!
  [2016-01-21 04:12:49 +0000] warning/ApplyRule: Apply rule '' (in /etc/icinga2/conf.d/services.conf: 65:1-65:53) for type 'Service' does not match anywhere!
  [2016-01-21 04:12:49 +0000] information/ConfigItem: Instantiated 1 Comment.
  [2016-01-21 04:12:49 +0000] information/ConfigItem: Triggering Start signal for config items
  [2016-01-21 04:12:49 +0000] information/ConfigItem: Activated all objects.
  [2016-01-21 04:12:49 +0000] notice/Comment: Added comment 'icinga2-web2-mysql!icinga2-web2-mysql-1453349569-0'.
  
  [2016-01-21 04:12:50 +0000] debug/IdoMysqlConnection: Query: INSERT INTO icinga_externalcommands (command_args, command_name, command_type, endpoint_object_id, entry_time, instance_id) VALUES ('icinga2-web2-mysql;1;icingaadmin;yggygyyj', 'ADD_HOST_COMMENT', '1', 1, FROM_UNIXTIME(1453349569), 1)
  [2016-01-21 04:12:50 +0000] debug/IdoMysqlConnection: Query: INSERT INTO icinga_comments (author_name, comment_data, comment_source, comment_time, comment_type, endpoint_object_id, entry_time, entry_time_usec, entry_type, expires, instance_id, internal_comment_id, is_persistent, name, object_id) VALUES ('icingaadmin', 'yggygyyj', '1', FROM_UNIXTIME(1453349569), '2', 1, FROM_UNIXTIME(1453349569), '947563', '1', '0', 1, '1', '1', 'icinga2-web2-mysql!icinga2-web2-mysql-1453349569-0', 68)
  [2016-01-21 04:12:50 +0000] debug/IdoMysqlConnection: Query: INSERT INTO icinga_commenthistory (author_name, comment_data, comment_source, comment_time, comment_type, endpoint_object_id, entry_time, entry_time_usec, entry_type, expires, instance_id, internal_comment_id, is_persistent, name, object_id) VALUES ('icingaadmin', 'yggygyyj', '1', FROM_UNIXTIME(1453349569), '2', 1, FROM_UNIXTIME(1453349569), '947563', '1', '0', 1, '1', '1', 'icinga2-web2-mysql!icinga2-web2-mysql-1453349569-0', 68)
  [2016-01-21 04:12:50 +0000] debug/IdoMysqlConnection: Query: COMMIT
  [2016-01-21 04:12:50 +0000] debug/IdoMysqlConnection: Query: BEGIN
  </code></pre>
  
  <p><strong>Restarting Icinga2 with PostgreSQL and MySQL Backends</strong></p>
  
  <p>When restarting Icinga2 with both PostgreSQL and MySQL Backends, I have pretty much the same logs which sounds good!</p>
  
  <pre><code># Icinga2 restart with PostgreSQL
  
  [2016-01-21 05:02:11 +0000] information/Application: Received request to shut down.
  [2016-01-21 05:02:11 +0000] information/Application: Shutting down...
  [2016-01-21 05:02:11 +0000] information/CheckerComponent: Checker stopped.
  [2016-01-21 05:02:11 +0000] information/DbConnection: Resuming IDO connection: ido-pgsql
  [2016-01-21 05:02:11 +0000] information/ConfigItem: Activated all objects.
  [2016-01-21 05:02:11 +0000] information/ConfigCompiler: Compiling config file: /var/lib/icinga2/modified-attributes.conf
  [2016-01-21 05:02:11 +0000] information/IdoPgsqlConnection: pgSQL IDO instance id: 1 (schema version: '1.14.0')
  
  # Icinga2 restart with MySQL
  
  [2016-01-21 05:03:20 +0000] information/Application: Received request to shut down.
  [2016-01-21 05:03:20 +0000] information/Application: Shutting down...
  [2016-01-21 05:03:20 +0000] information/CheckerComponent: Checker stopped.
  [2016-01-21 05:03:20 +0000] information/DbConnection: Resuming IDO connection: ido-mysql
  [2016-01-21 05:03:20 +0000] information/ConfigItem: Activated all objects.
  [2016-01-21 05:03:20 +0000] information/ConfigCompiler: Compiling config file: /var/lib/icinga2/modified-attributes.conf
  [2016-01-21 05:03:20 +0000] information/IdoMysqlConnection: MySQL IDO instance id: 1 (schema version: '1.14.0')
  </code></pre>
  
  <p><strong>Packages Version</strong></p>
  
  <ul>
  <li>icinga2-2.4.1-1.el7.centos.x86_64</li>
  <li>icinga2-ido-pgsql-2.4.1-1.el7.centos.x86_64</li>
  <li>postgresql94-9.4.5-1PGDG.rhel7.x86_64</li>
  <li>postgresql94-server-9.4.5-1PGDG.rhel7.x86_64</li>
  </ul>
  
  <h1>How to Reproduce this Issue</h1>
  
  <p>For those who has the energy to reproduce the problem you can clone <code>mickael-ange/icinga2-ansible</code> Github repo (branch: Issue-No-Historical-Data-in-Icinga2-Web2-and-DB) and follow instructions at <a href="https://github.com/mickael-ange/icinga2-ansible/blob/Issue-No-Historical-Data-in-Icinga2-Web2-and-DB/doc/icinga2-web2-vagrant.md" rel="nofollow">icinga2-we2-vagrant.md</a>. You need to install the required tools if it is not already the case. Then install role dependencies with librarian-ansible. Finally, I wrote a section (#ido-no-historical-data-in-icinga2-web2-and-db) to reproduce the problem.</p>
  
  <p>Thank you in advance to take the time read until here!
  Mickael</p>