• Recent
  • Unsolved
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Register
  • Login
  • Recent
  • Unsolved
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Register
  • Login

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

Scheduled Pinned Locked Moved Solved
Bug Reports
4
28
16.3k
Loading More Posts
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • S
    Sebastian Roth Moderator
    last edited by Sebastian Roth Apr 29, 2016, 10:40 PM Apr 27, 2016, 6:20 AM

    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?

    Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

    Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

    1 Reply Last reply Reply Quote 0
    • S
      Sebastian Roth Moderator
      last edited by Apr 26, 2016, 11:21 PM

      Tried to verify but I can’t as there seems to be an issue with the 5.7 mysql DB that I upgraded to to verify Quazz’s problem. In Group Management I say “Create New Group”, give it a name and say “Add” I get the error: Group ID was not set, or unable to be created (nothing in apache error log). Same when trying to create a new host: Host ID was not set, or unable to be created.

      I guess FOG is not compatible with MySQL 5.7 yet. Bummer.

      Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

      Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

      1 Reply Last reply Reply Quote 0
      • S
        Sebastian Roth Moderator
        last edited by Sebastian Roth Apr 26, 2016, 5:39 PM Apr 26, 2016, 11:39 PM

        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?

        Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

        Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

        T 1 Reply Last reply Apr 27, 2016, 12:40 AM Reply Quote 0
        • T
          Tom Elliott @Sebastian Roth
          last edited by Apr 27, 2016, 12:40 AM

          @Sebastian-Roth is this still MySQL 5.7?

          Please help us build the FOG community with everyone involved. It's not just about coding - way more we need people to test things, update documentation and most importantly work on uniting the community of people enjoying and working on FOG! Get in contact with me (chat bubble in the top right corner) if you want to join in.

          Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

          Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

          1 Reply Last reply Reply Quote 0
          • S
            Sebastian Roth Moderator
            last edited by Apr 27, 2016, 6:07 AM

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

            Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

            Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

            1 Reply Last reply Reply Quote 0
            • Q
              Quazz Moderator
              last edited by Quazz Apr 27, 2016, 2:42 AM Apr 27, 2016, 6:48 AM

              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.

              1 Reply Last reply Reply Quote 0
              • S
                Sebastian Roth Moderator
                last edited by Apr 27, 2016, 12:43 PM

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

                Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

                Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

                Q 1 Reply Last reply Apr 27, 2016, 12:45 PM Reply Quote 0
                • Q
                  Quazz Moderator @Sebastian Roth
                  last edited by Apr 27, 2016, 12:45 PM

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

                  1 Reply Last reply Reply Quote 0
                  • W
                    Wayne Workman
                    last edited by Apr 27, 2016, 2:45 PM

                    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.

                    Please help us build the FOG community with everyone involved. It's not just about coding - way more we need people to test things, update documentation and most importantly work on uniting the community of people enjoying and working on FOG!
                    Daily Clean Installation Results:
                    https://fogtesting.fogproject.us/
                    FOG Reporting:
                    https://fog-external-reporting-results.fogproject.us/

                    T 1 Reply Last reply Apr 29, 2016, 11:12 PM Reply Quote 0
                    • T
                      Tom Elliott @Wayne Workman
                      last edited by Apr 29, 2016, 11:12 PM

                      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.

                      Please help us build the FOG community with everyone involved. It's not just about coding - way more we need people to test things, update documentation and most importantly work on uniting the community of people enjoying and working on FOG! Get in contact with me (chat bubble in the top right corner) if you want to join in.

                      Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

                      Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

                      W 1 Reply Last reply May 2, 2016, 1:59 AM Reply Quote 1
                      • S
                        Sebastian Roth Moderator
                        last edited by Apr 30, 2016, 5:59 PM

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

                        Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

                        Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

                        Q 1 Reply Last reply May 1, 2016, 9:07 AM Reply Quote 0
                        • Q
                          Quazz Moderator @Sebastian Roth
                          last edited by May 1, 2016, 9:07 AM

                          This post is deleted!
                          1 Reply Last reply Reply Quote 0
                          • S
                            Sebastian Roth Moderator
                            last edited by Sebastian Roth May 1, 2016, 3:28 AM May 1, 2016, 9:18 AM

                            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(
                            

                            Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

                            Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

                            1 Reply Last reply Reply Quote 0
                            • S
                              Sebastian Roth Moderator
                              last edited by May 1, 2016, 10:07 AM

                              @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
                              

                              Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

                              Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

                              1 Reply Last reply Reply Quote 0
                              • S
                                Sebastian Roth Moderator
                                last edited by Sebastian Roth May 1, 2016, 6:12 AM May 1, 2016, 12:10 PM

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

                                Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

                                Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

                                T Q 2 Replies Last reply May 1, 2016, 1:49 PM Reply Quote 0
                                • T
                                  Tom Elliott @Sebastian Roth
                                  last edited by May 1, 2016, 1:49 PM

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

                                  Please help us build the FOG community with everyone involved. It's not just about coding - way more we need people to test things, update documentation and most importantly work on uniting the community of people enjoying and working on FOG! Get in contact with me (chat bubble in the top right corner) if you want to join in.

                                  Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

                                  Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

                                  1 Reply Last reply Reply Quote 0
                                  • S
                                    Sebastian Roth Moderator
                                    last edited by Sebastian Roth May 1, 2016, 10:04 AM May 1, 2016, 3:59 PM

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

                                    Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

                                    Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

                                    1 Reply Last reply Reply Quote 0
                                    • S
                                      Sebastian Roth Moderator
                                      last edited by May 1, 2016, 4:11 PM

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

                                      Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

                                      Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

                                      T 1 Reply Last reply May 2, 2016, 10:34 AM Reply Quote 0
                                      • W
                                        Wayne Workman @Tom Elliott
                                        last edited by May 2, 2016, 1:59 AM

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

                                        Please help us build the FOG community with everyone involved. It's not just about coding - way more we need people to test things, update documentation and most importantly work on uniting the community of people enjoying and working on FOG!
                                        Daily Clean Installation Results:
                                        https://fogtesting.fogproject.us/
                                        FOG Reporting:
                                        https://fog-external-reporting-results.fogproject.us/

                                        1 Reply Last reply Reply Quote 0
                                        • T
                                          Tom Elliott @Sebastian Roth
                                          last edited by May 2, 2016, 10:34 AM

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

                                          Please help us build the FOG community with everyone involved. It's not just about coding - way more we need people to test things, update documentation and most importantly work on uniting the community of people enjoying and working on FOG! Get in contact with me (chat bubble in the top right corner) if you want to join in.

                                          Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

                                          Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

                                          1 Reply Last reply Reply Quote 0
                                          • 1
                                          • 2
                                          • 1 / 2
                                          1 / 2
                                          • First post
                                            12/28
                                            Last post

                                          143

                                          Online

                                          12.0k

                                          Users

                                          17.3k

                                          Topics

                                          155.2k

                                          Posts
                                          Copyright © 2012-2024 FOG Project