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

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