Error 'Incorrect integer value' when inserting an empty string into INT field in MySQL 5.7

[Sebastian Roth]

Strange. Enabled mysql query logging and saw this:

INSERT INTO groups (`groupName`,`groupDesc`,`groupCreateBy`,`groupDateTime`,`groupBuilding`,`groupKernel`,`groupKernelArgs`,`groupPrimaryDisk`)
    VALUES ('linux','','fog','2016-04-26 23:33:29','','','','')
    ON DUPLICATE KEY UPDATE `groupName`='linux',`groupDesc`='',`groupCreateBy`='fog',`groupDateTime`='2016-04-26 23:33:29',`groupBuilding`='',`groupKernel`='',`groupKernelArgs`='',`groupPrimaryDisk`=''

Trying to run this by hand gives me ERROR 1366 (HY000): Incorrect integer value: '' for column 'groupBuilding' at row 1 but nothing about ID being wrong or missing… Do you have an idea @Tom-Elliott?

[Tom Elliott]

@Sebastian-Roth is this still MySQL 5.7?

[Sebastian Roth]

@Tom-Elliott Yes, all on MySQL 5.7 - while I am not sure if it’s really caused by the newer version it definitely feels like it. Was pretty late and I probably need to look into this again. Have you ever had a chance to test on 5.7?

[Quazz]

Wouldn’t resolving this be as simple as setting SQL_MODE to ’ '?

edit: After trying this, the answer appears to be no, no it will not.

[Sebastian Roth]

@Quazz Are you seeing the exact same issue on your 16.04 updated system?

[Quazz]

@Sebastian-Roth No, details (a lot of them I know) are found in the other thread.

Before I purged php and apache I could simply login and didn’t get the error. (after using the command you provided) However this was after updating ubuntu and a relatively clean mysql install so not sure if that affects it at all.

I am unable to create new tasks and such, same as what you noted in the other thread.

[Wayne Workman]

said in Error 'Incorrect integer value' when inserting an empty string into a MySQL INT field:

@Wayne-Workman I still wonder why you wouldn’t run into this on your fresh 16.04 install.

I’ve not installed fog on 16.04 yet - yesterday all I did was hammer out the partitioning - and made a video about it.

[Tom Elliott]

While I understand the “bug” in the info here, is there anything I can do to fix this?

I am not running 5.7 currently (sorry all) just focused on getting things that MOST will be using as functional as possible. I don’t know where to begin in knowing what type of data a field is to expect without essentially re-writing the entire database layout which would be a TON of work. (Especially seeing as there’s 2101 lines if data to work within.)

Maybe, if somebody else wants to take the reigns on overhauling the current schema and adjusting so we COULD use Innodb and proper foreign key I’d be FAR more than appreciative (and I think the rest of the FOG Community would be too.) (@george1421).

Sorry george, I figure if I call you out (as you have a lot of SQL experience) maybe you’d be willing? LOL.

At any rate, this is open for anybody.

[Sebastian Roth]

Trying to get all the information together: https://wiki.fogproject.org/wiki/index.php?title=Ubuntu_16.04#MySQL_5.7_issue

Would be great if someone is willing to help in getting FOG ready for mysql 5.7 (or maybe configure mysql 5.7 to host FOG as is).

[Quazz]

This post is deleted!

[Sebastian Roth]

Great stuff, Tom has worked on this already and a lot of the issues are solved! One minor new problem I was able to solve:

diff --git a/packages/web/commons/schema.php b/packages/web/commons/schema.php
index dbf40db..5d8a847 100644
--- a/packages/web/commons/schema.php
+++ b/packages/web/commons/schema.php
@@ -101,7 +101,7 @@ KEY `new_index4` (`taskType`)
 KEY `new_index` (`uName`),
 KEY `new_index1` (`uPass`)
     ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1",
-    "INSERT IGNORE INTO `".DATABASE_NAME."`.`users` VALUES  ('','fog', MD5('password'),'0000-00-00 00:00:00','')",
+    "INSERT IGNORE INTO `".DATABASE_NAME."`.`users` VALUES  ('','fog', MD5('password'),NOW(),'')",
     "INSERT IGNORE INTO `".DATABASE_NAME."`.`supportedOS` VALUES  ('','"._("Windows XP")."', '1')",
     "INSERT IGNORE INTO `".DATABASE_NAME."`.`schemaVersion` VALUES  ('','1')"
 );

And another one:

diff --git a/packages/web/commons/schema.php b/packages/web/commons/schema.php
index dbf40db..c32bcf0 100644
--- a/packages/web/commons/schema.php
+++ b/packages/web/commons/schema.php
@@ -1821,7 +1821,7 @@ $this->schema[] = array(
 );
 // 179
 $this->schema[] = array(
-    "ALTER TABLE `" . DATABASE_NAME ."`.`hosts` ADD COLUMN `hostSecTime` TIMESTAMP NOT NULL",
+    "ALTER TABLE `" . DATABASE_NAME ."`.`hosts` ADD COLUMN `hostSecTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP",
 );
 // 180
 $this->schema[] = array(

[Sebastian Roth]

@Tom-Elliott Here is another one to fix this issue:

diff --git a/packages/web/commons/schema.php b/packages/web/commons/schema.php
index dbf40db..583dc8c 100644
--- a/packages/web/commons/schema.php
+++ b/packages/web/commons/schema.php
@@ -1084,12 +1084,8 @@ $this->schema[] = array(
 // 36
 // Blackout - 12:18 PM 4/05/2012
 $this->schema[] = array(
-    "CREATE TABLE `".DATABASE_NAME."`.`groupMembersTemp` LIKE `".DATABASE_NAME."`.`groupMembers`",
-    "INSERT `".DATABASE_NAME."`.`groupMembersTemp` SELECT * FROM `".DATABASE_NAME."`.`groupMembers`",
-    "delete from `".DATABASE_NAME."`.`groupMembers` where gmID in (select gmID from `".DATABASE_NAME."`.`groupMembersTemp` group by gmHostID, gmGroupID having count(*) > 1)",
-    "drop table `".DATABASE_NAME."`.`groupMembersTemp`",
+    $tmpSchema->drop_duplicate_data(DATABASE_NAME,array('groupMembers',array('gmHostID','gmGroupID')),true),
     "ALTER TABLE `".DATABASE_NAME."`.`groups` ADD UNIQUE ( `groupName` )",
-    "ALTER TABLE `".DATABASE_NAME."`.`groupMembers` ADD UNIQUE ( `gmHostID`, `gmGroupID` )"
 );
 // 37
 // Blackout - 6:12 PM 5/05/2012

[Sebastian Roth]

@Tom-Elliott And here is the rest: https://gist.github.com/Sebastian-Roth/e0241106a9df3c006a91997850d9b23d

Sent you a message on slack to explain what this patch is about. Hope you don’t mind me sending this as a gist file instead of a pull request but I feel this part is really important and this way you really need to look into what I changed. Possibly you’ll find a problem in that code that you wouldn’t have seen when just accepting my pull request.

@Quazz Would be great if you could test on Ubuntu 16.04 after Tom has added all those.

[Tom Elliott]

@Sebastian-Roth I didn’t see anything wrong, so I made the changes as you suggested in the gist and the patched elements as you commented earlier in this posting. Thanks for the assist thus far.

[Sebastian Roth]

@Tom-Elliott Two of my changes seem to miss in current trunk (causing schema update error on fresh install):

index 4f7fc2b..243f067 100644
--- a/packages/web/commons/schema.php
+++ b/packages/web/commons/schema.php
@@ -1603,7 +1603,7 @@ $this->schema[] = array_merge(array(
     ) ENGINE=MyISAM;",
     "INSERT IGNORE INTO `".DATABASE_NAME."`.`imageGroupAssoc` (`igaImageID`,`igaStorageGroupID`) SELECT `imageID`,`imageNFSGroupID` FROM `".DATABASE_NAME."`.`images` WHERE `imageNFSGroupID` IS NOT NULL",
     "ALTER TABLE `".DATABASE_NAME."`.`images` DROP COLUMN `imageNFSGroupID`"),
-    $tmpSchema->drop_duplicate_data(DATABASE_NAME,array('imageGroupAssoc',array('igaImageID','igaImageID'),'igaImageID'),true)
+    $tmpSchema->drop_duplicate_data(DATABASE_NAME,array('imageGroupAssoc',array('igaImageID','igaImageID')),true)
 );
 // 137
 $this->schema[] = array(
@@ -1913,7 +1913,7 @@ $this->schema[] = array(
 );
 // 189
 $this->schema[] = array_merge(
-    $tmpSchema->drop_duplicate_data(DATABASE_NAME,array('globalSettings',array('settingKey'),'settingKey'),true),
+    $tmpSchema->drop_duplicate_data(DATABASE_NAME,array('globalSettings',array('settingKey','settingKey'),'settingKey'),true),
     array("DELETE FROM `".DATABASE_NAME."`.`globalSettings` WHERE `settingKey`='FOG_WOL_PATH'",
     "DELETE FROM `".DATABASE_NAME."`.`globalSettings` WHERE `settingKey`='FOG_WOL_HOST'",
     "DELETE FROM `".DATABASE_NAME."`.`globalSettings` WHERE `settingKey`='FOG_WOL_INTERFACE'")

After that fix we are back to the initially posted issue - neither hosts nor groups can be created (error: xxx ID was not set, or unable to be created.)

[Sebastian Roth]

Simple fix for the “add host/group/image” problem might be the following change. Although I am not sure about the consequences:

diff --git a/packages/web/lib/fog/fogcontroller.class.php b/packages/web/lib/fog/fogcontroller.class.php
index e38d01f..d3aafa1 100644
--- a/packages/web/lib/fog/fogcontroller.class.php
+++ b/packages/web/lib/fog/fogcontroller.class.php
@@ -12,7 +12,7 @@ abstract class FOGController extends FOGBase {
     protected $databaseFieldClassRelationships = array();
     protected $loadQueryTemplateSingle = "SELECT * FROM %s %s WHERE %s='%s' %s";
     protected $loadQueryTemplateMultiple = 'SELECT * FROM %s %s WHERE %s %s';
-    protected $insertQueryTemplate = "INSERT INTO %s (%s) VALUES ('%s') ON DUPLICATE KEY UPDATE %s";
+    protected $insertQueryTemplate = "INSERT IGNORE INTO %s (%s) VALUES ('%s') ON DUPLICATE KEY UPDATE %s";
     protected $destroyQueryTemplate = "DELETE FROM %s WHERE %s='%s'";
     public function __construct($data = '') {
         parent::__construct();

[Wayne Workman]

@Tom-Elliott said in Error 'Incorrect integer value' when inserting an empty string into INT field in MySQL 5.7:

Maybe, if somebody else wants to take the reigns on overhauling the current schema and adjusting so we COULD use Innodb and proper foreign key I’d be FAR more than appreciative (and I think the rest of the FOG Community would be too.)

I’ve actually wanted to do that for quite a while. I can take a stab at it one of these weekends, I can convert everything over and create all the necessary constraints. But if someone wants to do it now, go right ahead.

[Tom Elliott]

@Sebastian-Roth I believe the “consequences” would be, the DUPLICATE KEY UPDATE part wouldn’t be able to run. This is because the ignore statements tells the query to only add if there is no duplicate. Of course I could be sorely wrong and I can add it if you think it’d be worth it.

[Sebastian Roth]

@Tom-Elliott You are absolutely right! It works when adding new entries but would fail on updating. So the fix will be a lot more complex I suppose.

Should we try to just get this fixed for MySQL 5.7’s stricter sql_mode options (like converting empty string to zero for integer fields) or shall we really do a complete DB restructure for 1.3.0 I wonder?!?

[Tom Elliott]

@Sebastian-Roth I think fixing, for now, the issue as presented. Seeing as the 2.0 code will be more adaptable and forgiving than the current, and we’ve been nearly 2 years out since a stable release was last pushed, I think fixing the issues at hand would be the “quickest”, but it’s still “dirty”.

I think a restructure would be beneficial but could add substantial extra time to the release of 1.3.0.