GIT4798/4804 database problem (IDs of certain Tables increase)

[tian]

There seems to be bug that increases the IDs of the following tables constantly:
groupMembers (gmID)
hostMAC (hmID)
moduleStatusByHost (msID)
snapinAssoc (saID)

The IDs of the table elements are increasing - the number of rows keep being the same (the previous IDs are not there anymore).

What I did before (because of IDs in these 4 tables had a higher value than the corresponding Auto_increment values …/ the IDs and auto_increment values have been from around 196k to 681k …):

• I completely cleared (by trucnate) the database from hosts, group and snapin association tables. -> this also reset all the the auto_increment to 1
  – (groupMembers, hostMAC, hosts, inventory, ipxeTable, moduleStatusByHost, SnapinAssociation, snapinGroupAssoc, snapinJobs, scheduledTasks, taskLog, tasks, snapinTaks, multicastSessionAssoc, imagingLog) - I just kept the tables groups, images and snapins (where the IDs/auto_increment and data still are fine)
• after that I re-imported the hosts by a csv file (21 hosts, just mac and name in csv)
• associated the hosts to the groups
• associated the image, services and AD join data
• (the hosts have been turned off the whole day)

now (around 1h after re-adding the 21 hosts) the values for auto_increment and IDs reach from 236 to 1007 again - without changing anything on the web interface and even being logged out.

During cleaning the database I was on GIT4798 and updated to GIT4804 with the same behavior. I’m on Ubuntu 14.04 with mysql 5.5.44.
Fog seems to work fine even with this bug - Imaging, Snapins, AD join work with GIT4798 yesterday.

Is there anything more I can try or provide?

[Tom Elliott]

This is normal and expected behavior.

While these 21 systems that weren’t doing anything should not have had anything updated, the systems that were still in the system and pinging FOG would. This is because:

The system initially destroy’s ALL associations of a specified Object (Host with Snapins, Printers, MAC’s, etc…). This way the updates you perform that remove an element, that element is removed, and new entries (of the items you require for that object) are created even though they existed before. This “autoincrements” the fields continuously.

[tian]

@Tom-Elliott said:

This is normal and expected behavior.

While these 21 systems that weren’t doing anything should not have had anything updated, the systems that were still in the system and pinging FOG would. This is because:

The system initially destroy’s ALL associations of a specified Object (Host with Snapins, Printers, MAC’s, etc…). This way the updates you perform that remove an element, that element is removed, and new entries (of the items you require for that object) are created even though they existed before. This “autoincrements” the fields continuously.

Thanks Tom for the explanation.

Tomorrow I’ll try if imaging and everything else is working with the re-imported hosts.

Just to clarify - I didn’t update the hosts anymore after associating the group, snapins, images, services and ad join information to them hours ago. That it is still increasing every few minutes is normal behavior?

If that is normal there is something that bothers me:
Currently there are just 21 hosts and 1-2 snapins connected to each host for testing. If more hosts and snapins will be added and associated to the hosts, the increase of the values will be faster. At the moment the auto_increment values / IDs range from ~1200 to 4800 again.

• What happens if auto_increment hits its limit - will it begin from 1 again?
• And if it begins from 1 again - is it for sure that all the IDs will be incremented at any time without forgetting some that will remain at some lower value then? (-> double IDs?)

[Tom Elliott]

See, these tables update because something is doing something with the host. The best example of the issue is particularly present with the new client as every time the client checks in with the server, the host is updated to ensure the AD Password field is not static. (This is intentionally placed for security reasons). I don’t know the exact way to describe in a more suitable fashion.

[tian]

@Tom-Elliott Thanks for your patience and this great piece of software. I understood that by changing, deleting , … an object in fog causes deleting and re-entering the database entries (a still existing fog 0.32 server also has relatively high IDs there - but these are still much lower -> a few thousand compared to a few 100k on the test-server for current svn).

I just wondered about the loop of deleting and re-entering, even with the computers turned off the whole time (so no AD join or other fog-client actions could be there - and me is the only one on this fog server for testing)

But the good new is that I found out what was causing the loop. The mysql services was running several processes (mysqld_safe, mysqld with some parameters, mysqld without parameters).
Running dpkg-reconfigure for the mysql server solved this and now just one mysqld process without parameters is running.

It seems to run stable now - for testing i:

• removed a snapin from a group of 19 -> The IDs/auto_increment of groupMembers get up by 19, the IDs of snapinAssoc get up by 18
• added the same snapin again -> The IDs/auto_increment of groupMembers get up by 19, the IDs of snapinAssoc get up by 37
  (a second snapin is associated to only 18 of the 19 group members, so the 18 and 37 from the snapinAssoc should be correct)

The reason why I didn’t check the mysqld service directly was that everything seemed to run correctly - imaging was working, the web interface was ok, the database-update when updating the svn version was returning a OK message, … . Even when installing a new svn over a existing svn version there was no errors on the mysql actions displayed.

Think I really have to check every time after each Ubuntu update/restart and fog svn update, if the myqld service is running just one process and check the database more often. (mysqld and other services are already delayed at startup)

[Tom Elliott]

@tian Then I’m going to solve this thread. FOG does increase the IDs, and I can work on finding the original ID’s and only update things rather than always remove and readd. This should limit the number of times things are in and out of the DB, which in turn would make things less prone to losing data.

[Tom Elliott]

I’m making an initial attempt for this. All elements that used to update constantly should no longer do so. hopefully it works. It’s an initial attempt, and while I did test everything I could think of, I’m by far from perfect.

[tian]

@Tom-Elliott Thanks a lot for the change.

I now had time to test again (with git5028 - I just verified the problematic tables groupMembers, hostMAC, moduleStatusByHost and SnapinAssoc) and can give some feedback now:

• removing/adding a snapin from a group
  – only the hostMAC-Table increases the “Auto_increment” value by the number of hosts the snapin is removed added (in my case 19 in both cases)
• removing/adding computers from/to a group
  – removing hosts did not increase the "Auto_increment"value of these tables, adding the same 2 hosts to the same group again increases the “Auto_increment” value of hostMAC by 2. The “Auto_increment” value of the 3 other tables are not increased

This behavior seems to be fine

But one thing is still there:

• no changes by the web interface are done/no user is logged in to the web interface
• host computers with client 0.95 are started to windows 7 - a user logged in or not doesn’t make a difference
  – The result of this setup is that the hostMAC entry of the computers that are turned on is changed/refreshed around every 2 minutes for every computer
  With the “change” (where -however- nothing seems to be changed at all, excepet the hmID) the “Auto_increment” value of hostMAC also increases by 1 for every computer every 2 minutes. (so 2 computers update the hostMAC database every minute by one entry in average)

Can this be reduced too, or is this necessary for other fog processes?
If it is important - the Computers only have 1 NIC and a virtual NIC (I blocked these from being pending MAC addresses by using FOG_QUICKREG_PENDING_MAC_FILTER)

[tian]

@Tom-Elliott somewhere between git5122 and git 5135 seemed to have an effect on the hostMAC table:
With git5135 the hostMAC changes the following:

• Windows login screen/no user logged in on host: avg. ~1.5 changes per minute/client
• user logged in to Windows: avg. ~2.5 changes per minute/client

With each change/rewrite of the hostMAC entry of the client computer turned on the hmID and Auto_increment rises even more fast than before now.

I found out that the Hostname Changer service could be involved - because for one host the service wasn’t activated. The client computer only changed the hmID/Auto_Increment only once when windows started up - not anymore when just running.
After activating the Hostname Changer service for this host the hmID for this host was increased immediately like described above.

[tian]

Some change between git5221 and git5283 seemed to have solved the hostMAC table problem.

I cant see the increase anymore in git5283. Since the hostname changer service seemed to be related to it I tested the AD join and it still seems to work fine.

I’ll test some more, but it looks good now.

[Wayne Workman]

@tian Thank you for reporting back! Posts like these make all the difference to future readers!