Only one computer in group
-
Hi,
Maybe this can help you. I tried again and same problem. This time, I took only two computers and tried from the HostManagement.
160510 14:38:33 179 Change user root@localhost on 179 Query SET NAMES utf8 179 Init DB fog 179 Query SET SESSION sql_mode='' 179 Query SELECT `vValue` FROM `schemaVersion` 179 Query SET SESSION sql_mode='' 179 Query SELECT COUNT(`hosts`.`hostID`) AS `total` FROM `hosts` LIMIT 1 179 Query SET SESSION sql_mode='' 179 Query SET SESSION group_concat_max_len=(1024 * 10) 179 Query SET SESSION sql_mode='' 179 Query SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'fog' AND ENGINE != 'MyISAM' 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `plugins` WHERE `plugins`.`pInstalled`='1' AND `plugins`.`pState`='1' ORDER BY LOWER(`plugins`.`pName`) ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `globalSettings` WHERE `globalSettings`.`settingKey`='FOG_VIEW_DEFAULT_SCREEN' ORDER BY LOWER(`globalSettings`.`settingKey`) ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `globalSettings` WHERE `globalSettings`.`settingKey`='FOG_FTP_IMAGE_SIZE' ORDER BY LOWER(`globalSettings`.`settingKey`) ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT COUNT(`hosts`.`hostID`) AS `total` FROM `hosts`WHERE `hosts`.`hostPending`='1' LIMIT 1 179 Query SET SESSION sql_mode='' 179 Query SELECT COUNT(`hostMAC`.`hmID`) AS `total` FROM `hostMAC`WHERE `hostMAC`.`hmPending`='1' LIMIT 1 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `globalSettings` WHERE `globalSettings`.`settingKey`='FOG_DATA_RETURNED' ORDER BY LOWER(`globalSettings`.`settingKey`) ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT COUNT(`users`.`uId`) AS `total` FROM `users` LIMIT 1 179 Query SET SESSION sql_mode='' 179 Query SELECT COUNT(`groups`.`groupID`) AS `total` FROM `groups` LIMIT 1 179 Query SET SESSION sql_mode='' 179 Query SELECT COUNT(`images`.`imageID`) AS `total` FROM `images` LIMIT 1 179 Query SET SESSION sql_mode='' 179 Query SELECT COUNT(`snapins`.`sID`) AS `total` FROM `snapins` LIMIT 1 179 Query SET SESSION sql_mode='' 179 Query SELECT COUNT(`printers`.`pID`) AS `total` FROM `printers` LIMIT 1 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `globalSettings` WHERE `globalSettings`.`settingKey`='FOG_HOST_LOOKUP' ORDER BY LOWER(`globalSettings`.`settingKey`) ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `globalSettings` WHERE `globalSettings`.`settingKey`='FOG_MEMORY_LIMIT' ORDER BY LOWER(`globalSettings`.`settingKey`) ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `globalSettings` WHERE `globalSettings`.`settingKey`='FOG_FORMAT_FLAG_IN_GUI' ORDER BY LOWER(`globalSettings`.`settingKey`) ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `globalSettings` WHERE `globalSettings`.`settingKey`='FOG_SNAPINDIR' ORDER BY LOWER(`globalSettings`.`settingKey`) ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `globalSettings` WHERE `globalSettings`.`settingKey`='FOG_REPORT_DIR' ORDER BY LOWER(`globalSettings`.`settingKey`) ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `globalSettings` WHERE `globalSettings`.`settingKey`='FOG_TZ_INFO' ORDER BY LOWER(`globalSettings`.`settingKey`) ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `globalSettings` WHERE `globalSettings`.`settingKey`='FOG_TZ_INFO' ORDER BY LOWER(`globalSettings`.`settingKey`) ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM users WHERE uId='1' 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `globalSettings` WHERE `globalSettings`.`settingKey`='FOG_INACTIVITY_TIMEOUT' ORDER BY LOWER(`globalSettings`.`settingKey`) ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `globalSettings` WHERE `globalSettings`.`settingKey`='FOG_REGENERATE_TIMEOUT' ORDER BY LOWER(`globalSettings`.`settingKey`) ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `globalSettings` WHERE `globalSettings`.`settingKey`='FOG_ALWAYS_LOGGED_IN' ORDER BY LOWER(`globalSettings`.`settingKey`) ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `globalSettings` WHERE `globalSettings`.`settingKey`='FOG_THEME' ORDER BY LOWER(`globalSettings`.`settingKey`) ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `globalSettings` WHERE `globalSettings`.`settingKey`='FOG_PLUGINSYS_ENABLED' ORDER BY LOWER(`globalSettings`.`settingKey`) ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM taskTypes WHERE ttID='2' 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM taskTypes WHERE ttID='1' 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM taskTypes WHERE ttID='8' 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM groups WHERE groupID='1' 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `groupMembers` WHERE `groupMembers`.`gmGroupID`='1' ORDER BY `groupMembers`.`gmID` ASC 179 Query SET SESSION sql_mode='' 179 Query INSERT INTO groups (`groupID`,`groupName`,`groupDesc`,`groupCreateBy`,`groupDateTime`,`groupBuilding`,`groupKernel`,`groupKernelArgs`,`groupPrimaryDisk`) VALUES ('1','Deploy Don Vista FR','','fog','2016-05-10 07:38:27','0','','','') ON DUPLICATE KEY UPDATE `groupID`='1',`groupName`='Deploy Don Vista FR',`groupDesc`='',`groupCreateBy`='fog',`groupDateTime`='2016-05-10 07:38:27',`groupBuilding`='0',`groupKernel`='',`groupKernelArgs`='',`groupPrimaryDisk`='' 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `groupMembers` WHERE `groupMembers`.`gmGroupID`='1' ORDER BY `groupMembers`.`gmID` ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `hosts` LEFT OUTER JOIN `hostMAC` ON `hostMAC`.`hmHostID`=`hosts`.`hostID` LEFT OUTER JOIN `images` ON `images`.`imageID`=`hosts`.`hostImage` WHERE `hostMAC`.`hmPrimary` = '1' ORDER BY LOWER(`hosts`.`hostName`) ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `groupMembers` WHERE `groupMembers`.`gmGroupID`='1' ORDER BY `groupMembers`.`gmID` ASC 179 Query SET SESSION sql_mode='' 179 Query SELECT * FROM `hosts` LEFT OUTER JOIN `hostMAC` ON `hostMAC`.`hmHostID`=`hosts`.`hostID` LEFT OUTER JOIN `images` ON `images`.`imageID`=`hosts`.`hostImage` WHERE `hosts`.`hostID` IN ('3') AND `hostMAC`.`hmPrimary` = '1' ORDER BY LOWER(`hosts`.`hostName`) ASC 179 Query SET SESSION sql_mode='' 179 Query INSERT INTO groupMembers (`gmHostID`,`gmGroupID`) VALUES ('3','1') ON DUPLICATE KEY UPDATE `gmHostID`='3',`gmGroupID`='1'
-
@Zaarin There’s probably something in your DB that is breaking the process then. If you can use something like HeidiSQL or phpMyAdmin to put eyes on the actual data, maybe you’ll find something strange?
-
I’m moving this post to FOG Problems as this is not a bug.
My guess @Zaarin, your going to find your groupMembers table has a few either: gmID = 0, or gmGroupID = 0.
-
Sorry, I don’t know how to put a screenshot. A Show create table is better
CREATE TABLE `groupMembers` ( `gmID` int(11) NOT NULL AUTO_INCREMENT, `gmHostID` int(11) NOT NULL, `gmGroupID` int(11) NOT NULL, PRIMARY KEY (`gmID`), UNIQUE KEY `gmHostID` (`gmGroupID`), UNIQUE KEY `gmHostID_2` (`gmHostID`,`gmGroupID`), KEY `gmHostID_3` (`gmHostID`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
This index seems to be wrong: UNIQUE KEY
gmHostID
(gmGroupID
)Is it the problem? I can remove it and test it but I wait your answer.
Thanks,
Zaza.
-
@Zaarin I don’t think it’s anything to do with the unique key. The idea of the one you believe is conflicting is that there only be one Host ID with a single group id. This way you don’t have
2 2
2 2
2 1Pairings.
-
@Zaarin Further to add on, this is applied to the DB and I’m not seeing the same issue.
I really believe if you run:
SELECT * FROM `fog`.`groupMembers` WHERE `gmID`=0 OR `gmHostID`=0 OR `gmGroupID`=0 \G
Print the output of that here, and we should be able to see what’s going on a little better.
-
@Zaarin The problem is likely the data in the tables, not the tables themselves.
-
I removed the \G. Empy result is returned.
-
@Zaarin Why’d you remove the \G? That’s valid. And prints data much nicer especially when the output to the screen.
Can you get us a copy of the Statement with the \G AND without the WHERE clause(s)?
-
I agree with you for the unique key gmHostID_2 but not for the index: gmHostID. With that index only one group is allowed with one member.
\G is not recognized in phpmyadmin. I took the mysql console. Here is what you want.
mysql> SELECT * FROM `fog`.`groupMembers` \G *************************** 1. row *************************** gmID: 1 gmHostID: 6 gmGroupID: 1 *************************** 2. row *************************** gmID: 3 gmHostID: 7 gmGroupID: 3 *************************** 3. row *************************** gmID: 4 gmHostID: 2 gmGroupID: 4 3 rows in set (0.00 sec)
Thanks for your time,
Zaza.
-
@Zaarin No, it’s not. If it were the case, ALL users of trunk would be seeing EXACTLY the same problems.
Maybe the issue is the schema that performed didn’t update properly? I don’t know.
You can try to alter the unique indexes if you want though. I don’t know what you’d do. Maybe for now just remove the unique index’s and keys?
-
@Zaarin What version of MySQL are you running?
-
5.5.49-0ubuntu0.14.04.1
I had problem with database update but it was some version before. I agree with you that all users will see exactly the same problem.
The only way to check is to compare with yours: Do you have the same structure?
Once again thanks for all your time,
Zaza from Luxembourg.
-
CREATE TABLE `groupMembers` ( `gmID` int(11) NOT NULL AUTO_INCREMENT, `gmHostID` int(11) NOT NULL, `gmGroupID` int(11) NOT NULL, PRIMARY KEY (`gmID`), UNIQUE KEY `gmHostID` (`gmHostID`,`gmGroupID`), UNIQUE KEY `gmHostID_2` (`gmHostID`,`gmGroupID`), KEY `new_index` (`gmHostID`), KEY `new_index1` (`gmGroupID`) ) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
-
@Tom-Elliott The only difference I see is the KEY’s aren’t adjusted for in yours where mine is.
-
I alter the table indexes like yours. It works like a charm now. I have my 7 computers in one group.
This topic can be closed.
Thanks you,
Zaza.
-
@Tom-Elliott said in Only one computer in group:
Why’d you remove the \G? That’s valid.
Not on every system. I’ve had it throw syntax errors before.
-
I’ve been getting the same problem with a fresh install on an ubuntu 14.04.3 x64. I used the git trunk version.
The only way I find to get rid of it is to delete the table groupMembers and recreate it with the create table find here. The other way for a fresh install is to install the stable 1.2.0 version first (which create the database) and installing the trunk version after. There is something with the database creation of the git trunk version. I repeated it today and I got the same issue. -
@lemxseb thanks a lot for testing and reporting. May I ask you some question:
I repeated it today and I got the same issue.
Did you remove the DB completely (purge the package plus delete the DB files in /var/lib/)?
-
We are having the same issues here, on Trunk version 7843.
We’ve just updated and did not yet perform a removal of the dbase