SQL Error on fresh installation
-
Hi all,
I used a lot FOG 0.32 but the server crashed with no backup for the settings. The image disk is OK, yes!
On the new server, I manage to install the version 7332 on Ubuntu server 16.04 with PHP 7.
Unfortunately, I got a lot of error on database update.
To be sure, I dropped the database FOG and I tried a second installation. Same result.
Could you solve this please?
Thx,
Zaza
Install/Upgrade Successful! The following errors occured Update ID: 1 - 10 Database Error: Incorrect integer value: '' for column 'uId' at row 1 Database SQL: INSERT INTO `fog`.`users` VALUES ('','fog', MD5('password'),'0000-00-00 00:00:00','') Update ID: 1 - 11 Database Error: Incorrect integer value: '' for column 'osID' at row 1 Database SQL: INSERT INTO `fog`.`supportedOS` VALUES ('','Windows XP', '1') Update ID: 1 - 12 Database Error: Incorrect integer value: '' for column 'vID' at row 1 Database SQL: INSERT INTO `fog`.`schemaVersion` VALUES ('','1') Update ID: 2 - 0 Database Error: Incorrect integer value: '' for column 'osID' at row 1 Database SQL: INSERT INTO `fog`.`supportedOS` VALUES ('','Windows Vista', '2') Update ID: 5 - 2 Database Error: Incorrect integer value: '' for column 'osID' at row 1 Database SQL: INSERT INTO `fog`.`supportedOS` VALUES ('','Other', '99') Update ID: 16 - 9 Database Error: Field 'ngmKey' doesn't have a default value Database SQL: INSERT INTO `fog`.nfsGroupMembers (ngmMemberName, ngmMemberDescription, ngmIsMasterNode, ngmGroupID, ngmRootPath, ngmIsEnabled, ngmHostname, ngmMaxClients, ngmUser, ngmPass) VALUES ('DefaultMember', 'Auto generated fog nfs group member', '1', '1', '/images/', '1', '172.30.254.3', '10', 'fog', 'd4Z/GvhRGbcJ/NEKQM8tq1HYDVlL4+x+Xk1yoUDfE7E=') Update ID: 36 - 2 Database Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fog.groupMembersTemp.gmID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by Database SQL: delete from `fog`.`groupMembers` where gmID in (select gmID from `fog`.`groupMembersTemp` group by gmHostID, gmGroupID having count(*) > 1) Update ID: 122 - 1 Database Error: Field 'pxeParams' doesn't have a default value Database SQL: INSERT INTO `fog`.`pxeMenu` (`pxeID`,`pxeName`,`pxeDesc`,`pxeDefault`,`pxeRegOnly`,`pxeArgs`) VALUES (1, 'fog.local', 'Boot from hard disk', '1','2',NULL), (2, 'fog.memtest', 'Run Memtest86+', '0','2',NULL), (3, 'fog.reginput', 'Perform Full Host Registration and Inventory','0','0','mode=manreg'), (4, 'fog.keyreg', 'Update Product Key', '0','1',NULL), (5, 'fog.reg', 'Quick Registration and Inventory', '0','0','mode=autoreg'), (6, 'fog.quickimage', 'Quick Image', '0', '1',NULL), (7, 'fog.multijoin', 'Join Multicast Session', '0','1',NULL), (8, 'fog.quickdel', 'Quick Host Deletion','0','1',NULL), (9, 'fog.sysinfo', 'Client System Information (Compatibility)','0','2','mode=sysinfo'), (10, 'fog.debug', 'Debug Mode','0','3','mode=onlydebug'), (11, 'fog.advanced', 'Advanced Menu','0','4',NULL), (12, 'fog.advancedlogin', 'Advanced Menu','0','5',NULL); Update ID: 129 - 0 Database Error: Field 'pxeParams' doesn't have a default value Database SQL: INSERT INTO `fog`.`pxeMenu` (`pxeID`,`pxeName`,`pxeDesc`,`pxeDefault`,`pxeRegOnly`,`pxeArgs`) VALUES (13, 'fog.approvehost', 'Approve This Host','0','6',NULL); Update ID: 130 - 8 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`hostMAC` ADD UNIQUE INDEX `hmHostID` (`hmMAC`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`hostMAC` ADD UNIQUE INDEX `hmHostID` (`hmMAC`) Update ID: 136 - 4 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`imageGroupAssoc` ADD UNIQUE INDEX `igaImageID` (`igaImageID`' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`imageGroupAssoc` ADD UNIQUE INDEX `igaImageID` (`igaImageID`) Update ID: 138 - 0 Database Error: Can't DROP 'igaImageID'; check that column/key exists Database SQL: ALTER TABLE `fog`.`imageGroupAssoc` DROP INDEX `igaImageID` Update ID: 140 - 3 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`snapinGroupAssoc` ADD UNIQUE INDEX `sgaSnapinID` (`sgaSnapin' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`snapinGroupAssoc` ADD UNIQUE INDEX `sgaSnapinID` (`sgaSnapinID`) Update ID: 140 - 4 Database Error: Can't DROP 'sgaSnapinID'; check that column/key exists Database SQL: ALTER TABLE `fog`.`snapinGroupAssoc` DROP INDEX `sgaSnapinID` Update ID: 161 - 0 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`greenFog` ADD UNIQUE (`gfHostID`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`greenFog` ADD UNIQUE (`gfHostID`) Update ID: 161 - 1 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`groupMembers` ADD UNIQUE (`gmHostID`,`gmGroupID`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`groupMembers` ADD UNIQUE (`gmHostID`,`gmGroupID`) Update ID: 161 - 2 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`groups` ADD UNIQUE (`groupName`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`groups` ADD UNIQUE (`groupName`) Update ID: 161 - 3 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`hostAutoLogOut` ADD UNIQUE (`haloHostID`,`haloTime`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`hostAutoLogOut` ADD UNIQUE (`haloHostID`,`haloTime`) Update ID: 161 - 4 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`hostMAC` ADD UNIQUE (`hmHostID`,`hmMAC`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`hostMAC` ADD UNIQUE (`hmHostID`,`hmMAC`) Update ID: 161 - 5 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`hosts` ADD UNIQUE (`hostName`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`hosts` ADD UNIQUE (`hostName`) Update ID: 161 - 6 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`hostScreenSettings` ADD UNIQUE (`hssHostID`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`hostScreenSettings` ADD UNIQUE (`hssHostID`) Update ID: 161 - 7 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`imageGroupAssoc` ADD UNIQUE (`igaImageID`,`igaStorageGroupID' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`imageGroupAssoc` ADD UNIQUE (`igaImageID`,`igaStorageGroupID`) Update ID: 161 - 8 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`imagePartitionTypes` ADD UNIQUE (`imagePartitionTypeName`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`imagePartitionTypes` ADD UNIQUE (`imagePartitionTypeName`) Update ID: 161 - 9 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`images` ADD UNIQUE (`imageName`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`images` ADD UNIQUE (`imageName`) Update ID: 161 - 10 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`imageTypes` ADD UNIQUE (`imageTypeValue`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`imageTypes` ADD UNIQUE (`imageTypeValue`) Update ID: 161 - 11 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`inventory` ADD UNIQUE (`iHostID`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`inventory` ADD UNIQUE (`iHostID`) Update ID: 161 - 12 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`modules` ADD UNIQUE (`short_name`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`modules` ADD UNIQUE (`short_name`) Update ID: 161 - 13 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`moduleStatusByHost` ADD UNIQUE (`msHostID`,`msModuleID`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`moduleStatusByHost` ADD UNIQUE (`msHostID`,`msModuleID`) Update ID: 161 - 14 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`multicastSessionsAssoc` ADD UNIQUE (`msID`,`tID`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`multicastSessionsAssoc` ADD UNIQUE (`msID`,`tID`) Update ID: 161 - 15 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`nfsFailures` ADD UNIQUE (`nfNodeID`,`nfHostID`,`nfTaskID`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`nfsFailures` ADD UNIQUE (`nfNodeID`,`nfHostID`,`nfTaskID`) Update ID: 161 - 16 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`nfsGroupMembers` ADD UNIQUE (`ngmMemberName`,`ngmGroupID`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`nfsGroupMembers` ADD UNIQUE (`ngmMemberName`,`ngmGroupID`) Update ID: 161 - 17 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`nfsGroups` ADD UNIQUE (`ngName`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`nfsGroups` ADD UNIQUE (`ngName`) Update ID: 161 - 18 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`os` ADD UNIQUE (`osName`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`os` ADD UNIQUE (`osName`) Update ID: 161 - 19 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`oui` ADD UNIQUE (`ouiMACPrefix`,`ouiMan`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`oui` ADD UNIQUE (`ouiMACPrefix`,`ouiMan`) Update ID: 161 - 20 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`plugins` ADD UNIQUE (`pName`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`plugins` ADD UNIQUE (`pName`) Update ID: 161 - 21 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`printerAssoc` ADD UNIQUE (`paHostID`,`paPrinterID`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`printerAssoc` ADD UNIQUE (`paHostID`,`paPrinterID`) Update ID: 161 - 22 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`printers` ADD UNIQUE (`pAlias`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`printers` ADD UNIQUE (`pAlias`) Update ID: 161 - 23 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`snapinAssoc` ADD UNIQUE (`saSnapinID`,`saHostID`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`snapinAssoc` ADD UNIQUE (`saSnapinID`,`saHostID`) Update ID: 161 - 24 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`snapinGroupAssoc` ADD UNIQUE (`sgaStorageGroupID`,`sgaSnapin' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`snapinGroupAssoc` ADD UNIQUE (`sgaStorageGroupID`,`sgaSnapinID`) Update ID: 161 - 25 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`snapins` ADD UNIQUE (`sName`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`snapins` ADD UNIQUE (`sName`) Update ID: 161 - 26 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`supportedOS` ADD UNIQUE (`osName`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`supportedOS` ADD UNIQUE (`osName`) Update ID: 161 - 27 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`taskStates` ADD UNIQUE (`tsName`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`taskStates` ADD UNIQUE (`tsName`) Update ID: 161 - 28 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`taskTypes` ADD UNIQUE (`ttName`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`taskTypes` ADD UNIQUE (`ttName`) Update ID: 162 - 0 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`snapinTasks` ADD UNIQUE (`stJobID`,`stSnapinID`)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`snapinTasks` ADD UNIQUE (`stJobID`,`stSnapinID`) Update ID: 173 - 0 Database Error: Can't DROP 'gfHostID'; check that column/key exists Database SQL: ALTER TABLE `fog`.`greenFog` DROP INDEX `gfHostID` Update ID: 179 - 0 Database Error: Invalid default value for 'hostSecTime' Database SQL: ALTER TABLE `fog`.`hosts` ADD COLUMN `hostSecTime` TIMESTAMP NOT NULL Update ID: 189 - 0 Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE `fog`.`globalSettings` ADD UNIQUE INDEX (settingKey)' at line 1 Database SQL: ALTER IGNORE TABLE `fog`.`globalSettings` ADD UNIQUE INDEX (settingKey) Update ID: 189 - 1 Database Error: Can't DROP 'settingKey'; check that column/key exists Database SQL: ALTER TABLE `fog`.`globalSettings` DROP INDEX settingKey
-
This is a known issue (see https://forums.fogproject.org/topic/7268/access-denied-for-root-localhost/14)
Your best bet would be to install a previous ubuntu version right now. 16.04 changed a lot of stuff that FOG does not yet account for.
-
Thanks for the link but I don’t think it’s the same problem. For instance the first error is a pure sql syntax :
Update ID: 1 - 10 Database Error: Incorrect integer value: ‘’ for column ‘uId’ at row 1 Database SQL: INSERT INTO fog.users VALUES (’’,‘fog’, MD5(‘password’),‘0000-00-00 00:00:00’,’’) I did a describe users in the database and I saw that uId is auto increment so the right syntax is : INSERT INTO `fog`.`users` (uName, uPass, uCreatedDate, uCreateBy) VALUES ('fog', MD5('password'),'0000-00-00 00:00:00','')
I do step by step since this morning, maybe I will have more problem later but I want inform you that the SQL have some errors. I can correct myself some but not all because I cannot guess some default value.
Unfortunately, install another ubuntu version is not an option. Sorry.
Zaza.
-
@Zaarin The error is mentioned in the thread somewhere, it has to do with the new mysql-server version doing things a bit differently.
-
@Zaarin A couple of your error messages are the same as in the thread Quazz mentioned (like
Incorrect integer value
) but there are other errors as well. We definitely need to look into this. On the other hand I am wondering how @Wayne-Workman was able to bring up a 16.04 server without any issues yesterday?!? Different mysql-server version (dpkg -l | grep -e "mysql" -e "maria"
)? -
@Sebastian-Roth Here is the answer of dpkg:
ii mysql-client 5.7.12-0ubuntu1 all MySQL database client (metapackage depending on the latest version) ii mysql-client-5.7 5.7.12-0ubuntu1 amd64 MySQL database client binaries ii mysql-client-core-5.7 5.7.12-0ubuntu1 amd64 MySQL database core client binaries ii mysql-common 5.7.12-0ubuntu1 all MySQL database common files, e.g. /etc/mysql/my.cnf ii mysql-server 5.7.12-0ubuntu1 all MySQL database server (metapackage depending on the latest version) ii mysql-server-5.7 5.7.12-0ubuntu1 amd64 MySQL database server binaries and system database setup ii mysql-server-core-5.7 5.7.12-0ubuntu1 amd64 MySQL database server binaries ii php-mysql 1:7.0+36+deb.sury.org~xenial+1 all MySQL module for PHP [default] ii php7.0-mysql 7.0.5-3+donate.sury.org~xenial+1 amd64 MySQL module for PHP
Thanks for taking time.
Zaza.
-
@Zaarin Sorry, didn’t mean you with the mysql version but it’s still good to know. Please give us a little more time to look into this. Feels like this might be quite a big issue, @Tom-Elliott…
PS: I put your long post into a code box so we don’t need to scroll all that much to see the new answers…
-
@Sebastian-Roth said in SQL Error on fresh installation:
On the other hand I am wondering how @Wayne-Workman was able to bring up a 16.04 server without any issues yesterday?!?
to be fair, I haven’t gotten to the fog installation part. I was just nailing down the partitioning.
Let me try here at work real fast (I’ve got far, far, far more powerful servers to play with here).
-
Hello,
To help you a little bit more, here is what I found for some errors:
http://dev.mysql.com/doc/refman/5.7/en/alter-table.html
As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and its use produces an error.
Unfortunately, the 5.7.12 is the default version on Ubuntu 16.04.
Zaza
-
@Zaarin Good catch but this is not our only issue with mysql 5.7.x I am afraid… I am still not sure if this is something we will be able to fix “soon”?!?
-
@Sebastian-Roth Stearing people clear from ubuntu 16.04 seems like the best bet right now.
-
Not really a problem if it’s not fix soon. You know now.
I spoke with my boss and it’s OK to switch back to an former version of Ubuntu. As I cannot found (or I need glasses) the information about compatibility, what is the last version of Ubuntu supported? I will use the fog 0.32 to be able to deploy without any registration.
Thanks for your help,
Zaza.
-
@Zaarin 15.10 should work fine, alternatively you could install something like CentOS 7 or such instead if you feel up to that. It should run into less of such issues.
You can deploy without registration in trunk as well, by the way.
-
Hi,
Interesting. This morning I switched back to the 14.04 because I cannot see your post due to an error on the forum. Sorry.
My network is strangely configured because I have a tftp server on windows, a computer database on another and the fog server who only serves to send the packet.
I used that link:
https://wiki.fogproject.org/wiki/index.php/Bypass_Host_Registration
I have an application to manage the database, the tftp config file and the wol. So I run the multicast process in fog and then my application for the tftp and the wol. All is fine without any user action.
I don’t use the fog server as a tftp because I don’t want any menu on the pxe boot in a normal use. Students are ingenious to lose time when they start the computer.
Is that possible with the trunk version? If not, don’t worry but I have no time to change my work immediately. (100 pc to install and 100 old to remove, office 2016 to test and to install, alone…).
Thanks for your help,
Zaza.
-
@Zaarin As you managed to configure your services with an older version of FOG I don’t see why this shouldn’t work with FOG trunk as well. I am pretty sure it will. If not out of the box I am sure we can make it work without too much of a hassle.
-
Hi,
I let you know. I install the trunk version.
Thanks for your help,
Zaza.