SQL Error on fresh installation

Zaarin

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

Quazz

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.

Zaarin

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.

Quazz

@Zaarin The error is mentioned in the thread somewhere, it has to do with the new mysql-server version doing things a bit differently.

Sebastian Roth

@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")?

Zaarin

@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.

Sebastian Roth

@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…

Wayne Workman

@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).

Zaarin

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

Sebastian Roth

@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”?!?

Quazz

@Sebastian-Roth Stearing people clear from ubuntu 16.04 seems like the best bet right now.

Zaarin

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.

Quazz

@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.

Zaarin

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.

Sebastian Roth

@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.

Zaarin

Hi,

I let you know. I install the trunk version.

Thanks for your help,

Zaza.

SQL Error on fresh installation

218

11.7k

17.1k

154.7k