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


  • Developer

    Moving all the posts to this new thread… I might have found what’s causing this but I don’t have the time to verify just now: http://www.garethalexander.co.uk/tech/mysql-5-incorrect-integer-value-column-id-row-1
    @Wayne-Workman I still wonder why you wouldn’t run into this on your fresh 16.04 install. Can you please check SQL_MODE on your server?


  • Developer

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


  • Senior Developer

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


  • Senior Developer

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


  • Developer

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


  • Senior Developer

    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.


  • Moderator

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


  • Senior Developer

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


  • Developer

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


  • Senior Developer

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



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


  • Developer

    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();
    

  • Developer

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


  • Senior Developer

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


  • Developer

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


  • Developer

    @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
    

  • Developer

    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(
    

  • Moderator

    This post is deleted!

  • Developer

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


  • Senior Developer

    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.


 

524
Online

41.9k
Users

12.4k
Topics

116.8k
Posts