RC11 group membership

[Wayne Workman]

@ablohowiak Can you run these queries and give us the output?

mysql -D fog
SELECT `hostID`, `hostName` FROM `hosts` WHERE `hostID` = '0';
SELECT * FROM `hostMAC` WHERE hmID = '0' OR `hmHostID` = '0';
SELECT * FROM `groupMembers` WHERE `gmID` = '0' OR `gmHostID` = '0' OR `gmGroupID` = '0';
SELECT * FROM `snapinGroupAssoc` WHERE `sgaID` = '0' OR `sgaSnapinID` = '0' OR `sgaStorageGroupID` = '0';
SELECT * from `snapinAssoc` WHERE `saID` = '0' OR `saHostID` = '0' OR `saSnapinID` = '0'; 
quit

[ablohowiak]

@Wayne-Workman
A few results.

[Wayne Workman]

@ablohowiak Those returns from the previous statements are bad, and shouldn’t be there. you can change those selects to deletes to delete them. Like this:

DELETE FROM `hosts` WHERE `hostID` = '0';
DELETE FROM `hostMAC` WHERE hmID = '0' OR `hmHostID` = '0';
DELETE FROM `groupMembers` WHERE `gmID` = '0' OR `gmHostID` = '0' OR `gmGroupID` = '0';
DELETE FROM `snapinGroupAssoc` WHERE `sgaID` = '0' OR `sgaSnapinID` = '0' OR `sgaStorageGroupID` = '0';
DELETE FROM `snapinAssoc` WHERE `saID` = '0' OR `saHostID` = '0' OR `saSnapinID` = '0'; 

Can you run these now?

SELECT * FROM `hosts` WHERE `hostID` NOT IN (SELECT `hmhostID` FROM `hostMAC`);
SELECT * FROM `hostMAC` WHERE `hmhostID` NOT IN (SELECT `hostID` FROM `hosts`);
SELECT * FROM `snapinAssoc` WHERE `saHostID` NOT IN (SELECT `hostID` FROM `hosts`);
SELECT * FROM `groupMembers` WHERE `gmHostID` NOT IN (SELECT hostID FROM hosts);

—Fixed

[ablohowiak]

@Wayne-Workman There are more results than this.

[Tom Elliott]

Please try to create a backup then run:

DELETE FROM `hosts` WHERE `hostID` NOT IN (SELECT `hmhostID` FROM `hostMAC`);
DELETE FROM `hostMAC` WHERE `hmhostID` NOT IN (SELECT `hostID` FROM `hosts`);
DELETE FROM `snapinAssoc` WHERE `saHostID` NOT IN (SELECT `hostID` FROM `hosts`);
DELETE FROM `groupMembers` WHERE `gmHostID` NOT IN (SELECT `hostID` FROM `hosts`);

----Fixed

[ablohowiak]

@Tom-Elliott
Should it be ‘gmHostID’ in the last line?

74 rows deleted. Membership is still behaving the same with the same error.

[Tom Elliott]

@ablohowiak yes. Gm I copied from Wayne sorry lol

[Wayne Workman]

dang it, typos…

Well, do things work now?

–Edit, I didn’t see where you said it didn’t help you. Even after correcting the last SQL query and running it? I’m assuming you did this.

@Tom-Elliott any ideas?

[Tom Elliott]

@Wayne-Workman I suspect it’s the group thing and will be fixed. I say this as I cannot replicate even with a group of 12k hosts

[ablohowiak]

@Tom-Elliott I did run the query with the correction. Most of our groups are already built from this summer. RC11 has been very stable and responsive. I’ve reduced the client communication time from 900 to 300 with no impact to performance.

I think your team is in the “home stretch” with 1.3!

[ablohowiak]

@Wayne-Workman I’ve had to rerun the delete query.

[Tom Elliott]

I’m solving for now in that the group bad entries should he corrected for. I won’t know for sure until I get a bit more of a testbed but I still have had 0 luck in replicating since this was brought up and RC 11 was pushed out.