MySQL Error “Incorrect integer value ” for column ‘name’ at row 1″

If a PHP script tries to set a string value, such as an empty string, into a MySQL integer data type or column, you will likely see this error message:

Incorrect integer value ” for column ‘name’ at row 1

In this situation, what is happening is that the script is attempting to insert or update a row with the integer entry taking on either the default value (e.g., of 0 or NULL) or the next AUTO_INCREMENT value.

But MySQL is not interpreting this action as valid due to its SQL_MODE being in STRICT mode.

In this case, to not treat this action as an error, edit my.ini, find this line –

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

And set to to an empty string –

sql_mode=""

Save my.ini, restart MySQL.

Afterwards, the value of '' (empty string) will just default to whatever:

A. Is specified as the default value of the column (set when the table was created).

B. Is the underlining designated default value for the integer data type.

If this does not help, then the script needs to be searched (in your editor of choice) for the column name, and the PHP code or the SQL statements on the found lines edited to set either a proper value, or the column name removed from those statements.

11 thoughts on “MySQL Error “Incorrect integer value ” for column ‘name’ at row 1″”

    1. Hi,

      You need to check MySQL’s error log –
      \WampDeveloper\Logs\Mysql\errorlog.txt

      And also the website’s and/or general PHP error log files.

      This could be related to another issue.

  1. Hello,
    I also face the same issue, but I can’t have access to the file my.ini cause my website is hosted on Godaddy’s server. How do you think I can solve that problem?
    Thx.

    1. Clear the SQL Mode at run-time by putting this code into your PHP script –
      mysqli_query($link, "SET SESSION sql_mode = ''");

      1. Hello,
        I have Godaddy’s shared server and I can not change the “mode_sql = STRICT”
        I wanted to test your solution, but I do not know where I need to add this line:
        mysqli_query($link, "SET SESSION sql_mode = ''");

        Thank you

        1. If you cannot modify MySQL’s my.ini file to set SQL_MODE, you have to set it via code at runtime globally or per-session…

          If you are running your own coded script, place the code into the PHP file that establishes the MySQL connection, right after the line that makes the MySQL connection – using the existing connection’s link –

          <?php
          ...
          $link = mysqli_connect(...);
          mysqli_query($link, "SET SESSION sql_mode = ''");
          ...
          ?>

          Or if you are using a webapp, use its API or provided global object variable to get the existing connection, and place the code into the webapp’s configuration file.

          Otherwise, set SQL_MODE globally by running this one time (and before anything is called) – or at the top of index.php. Note that this will persist until MySQL is restarted –

          <?php
          $link = mysqli_connect("127.0.0.1", "user-with-SUPER-privilege", "user-password");
          
          if (!$link) {
              echo "Error: Unable to connect to MySQL." . PHP_EOL;
              echo "Error code from last connect call: " . mysqli_connect_errno() . PHP_EOL;
              echo "Error description from last connect error: " . mysqli_connect_error() . PHP_EOL;
              exit;
          }
          
          mysqli_query($link, "SET GLOBAL sql_mode = ''");
          mysqli_query($link, "SET SESSION sql_mode = ''");
          
          mysqli_close($link);
          ?>
  2. sir i am using xampp in which line like :-
    “Sql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES” dosen’t exist .what should i do?

    1. Then you should add in line sql_mode = "", to clear out the default value of sql_mode that it takes when not set.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>