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

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

[Quazz]

@Sebastian-Roth Unfortunately I have already installed Centos7 as it’s rather busy and we kind of rely on FOG, but if I have some time I could get a VM going.

[Tom Elliott]

I’ve added code that should set the sql_mode to unstrict for FOG’s session within the DB. This “should” allow the fields to be empty even if it’s expecting an int. I don’t like having to make config changes all because of one version, but from what I can tell, this is the simplest way. I don’t know if it will work so testing and reporting will still be needed (as normal).

Thanks.

[Sebastian Roth]

Worked on my 16.04 test VM (beside the ‘‘settingKey’’ fix still missing). Anyone else?

[Tom Elliott]

@Sebastian-Roth settingKey fix should be in the latest. Yep, that means you’re already out of date

[Tom Elliott]

So, is it safe to solve the thread, or should I wait for others having this issue report back – after they update of course?

[Sebastian Roth]

I’m marking this solved. Please feel free to open this again if we run into the issue again.