Hi Ohad,

I have finally had some more time to look deeper into the problem and am hoping this will determine how we can ensure the problem is fixed in our production environment.

To recap the problem, all our Hostgroup definitions lost all their configuration information except their parameters. You could see the Hostgroups from the web ui (More -> Hostgroups), but when you clicked on a hostgroup and navigated to "Puppet Classes" it asked to "select and environment first"... Clicking the other tabs in the hostgroup menu also confirmed that their associated references were missing... Except, the parameter fields relationships remained intact.

Looking deeper into the problem, I logged into the Mysql database, set the database to "use foreman" and began peeking into the the 'hostgroups' table. What I saw each defined entries in the 'hostgroups' table for each hostgroup had the following columns all set to NULL:
environment_id
operatingsystem_id
architecture_id
puppetmaster_name
vm_defaults

Before doing anything else, I was able to determine the relationships by looking at the 'hostgroups_puppetclasses' table and cross referencing those relationships to the 'puppetclasses' table and the 'hostgroups' tables. I manually wrote down each of those relationships to determine what puppetclasses needed to be applied back to each hostgroup.

This is where I got confused... What controls these relationships and is there a method for restoring the puppetclass relationships back to the hostgroup?

is there any chance that #1477 is the real cause for the problem?

It is certainly possible, however I am still concerned that the root cause of the issue is still unknown. In my testing, I did come accross the issue that is described in #1477.

In our environment, the only ones that have access to the foreman are controlled via ACL policies in our network which is a total of three of us. We have ~30 different hostgroups and would require someone to manually change that.

I believe I have found that the issue happened on Jan 18th at 5:00 (mst denver) while manually combing throubh the audit log in the database. Luckily we have a database backup so we stopped foreman, droped the database and then restored from backup. We then, restarted mysql and apache (enabled with mod passenger for foreman) and confirmed the issue still existed.

I believe I have found a fix for the issue which involved manually manipulating the msyql database. It is kind of hard to explain so I am going to do my best to provide the examples.

As the user that has the ability to login to mysql I logged in and set the database to foreman. For example sake, I am just going to show one row instead off all of our objects in each table to try and illustrate the problem.

mysql
use foreman;

mysql> select * from hostgroups whereid='2';      
 +----+-----------------------------------+---------------------+---------------------+----------------+--------------------+-----------------+-----------+-----------+-----------+-------------------+----------------+-----------+------------+----------+--------------------------+-----------+-----------+
| id | name                              | created_at          | updated_at          | environment_id | operatingsystem_id | architecture_id | medium_id | ptable_id | root_pass | puppetmaster_name | puppetproxy_id | use_image | image_file | ancestry | vm_defaults              | subnet_id | domain_id |
+----+-----------------------------------+---------------------+---------------------+----------------+--------------------+-----------------+-----------+-----------+-----------+-------------------+----------------+-----------+------------+----------+--------------------------+-----------+-----------+
|  2 | Server Common                | 2009-12-29 23:59:49 | 2009-12-29 23:59:49 |           NULL |               NULL |            NULL |      NULL |      NULL | NULL      | NULL              |           NULL |      NULL | NULL       | NULL     | NULL                     |      NULL |      NULL | 

Now, I can cross reference this id with the hostgroups_puppetclasses to determine which puppetclasses I need to add back to this hostgroup.

 mysql> select * from hostgroups_puppetclasses where hostgroup_id='2';
 +--------------+----------------+
 | hostgroup_id | puppetclass_id | 
 +--------------+----------------+
 |            2 |             20 | 
 |            2 |             23 | 
 |            2 |             25 | 
 +--------------+----------------+
 3 rows in set (0.00 sec)

Now, again cross reference the information above to the puppetclasses tabase

 mysql> select * from puppetclasses where id='20' or id='23' or id='25';
 +----+-----------------------------+---------------------+---------------------+
 | id | name                        | created_at          | updated_at          |
 +----+-----------------------------+---------------------+---------------------+
 | 20 | Dummy puppet class 1 | 2010-02-02 18:59:33 | 2010-02-02 19:00:47 | 
 | 23 | Dummy puppet class 2 | 2010-02-02 23:14:53 | 2010-02-02 23:14:53 | 
 | 25 | Dummy puppet class 3 | 2010-03-01 20:30:56 | 2010-03-01 20:30:56 |  
 +----+-----------------------------+---------------------+---------------------+
 3 rows in set (0.00 sec)

At this point, I built another foreman and pupet environment and began comparing the results in the databases between known good configuration and the information provided above.

The fix to the problem after extensive testing and troubleshooting was easier than I thought and I confirmed that this worked in our environment....

First, I needed to the id for the environment

mysql> select * from environments;
 ---------------+---------------------+---------------------+
| id | name        | created_at          | updated_at          |
 ---------------+---------------------+---------------------+
|  1 | production  | 2009-12-17 22:08:23 | 2009-12-17 22:08:23 | 
|  2 | development | 2009-12-17 23:48:59 | 2009-12-17 23:48:59 | 
 ---------------+---------------------+---------------------+
 2 rows in set (0.00 sec)

Second, I set the environment_id=1 in the hostgroups table and wallah the puppet classes are restored.
mysql> update hostgroups set environment_id='1' where id='2';

Third, made sure the value took in the hostgroups table

mysql> select * from hostgroups where id=2;
----+-----------------------------+---------------------+---------------------+----------------+--------------------        -----------------+-----------+-----------+-----------+-------------------+----------------+-----------+------------  - ---------+-------------+-----------+-----------+
| id | name                        | created_at          | updated_at          | environment_id | operatingsystem_id | architecture_id | medium_id |  ptable_id | root_pass | puppetmaster_name | puppetproxy_id | use_image | image_file | ancestry | vm_defaults | subnet_id | domain_id |
+----+-----------------------------+---------------------+---------------------+----------------+-------------------- +-----------------+-----------+-----------+-----------+-------------------+----------------+-----------+------------ +----------+-------------+-----------+-----------+
|  2 | Servier Common | 2009-12-30 00:00:39 | 2010-02-02 23:14:29 |              1 |               NULL |            NULL |      NULL |      NULL | NULL         | NULL              |           NULL |      NULL | NULL       | NULL     | NULL        |      NULL |      NULL |  
+----+-----------------------------+---------------------+---------------------+----------------+--------------------    +-----------------+-----------+-----------+-----------+-------------------+----------------+-----------+------------    +----------+-------------+-----------+-----------+
1 row in set (0.00 sec)

And then I confirmed that I could see the puppet classes in foreman. Weird?????