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.

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

ismail says:

June 18, 2016 at 11:11 am

sir, i also have the same problem, but my SQL strict mode already disabled. what i do?, why this happens again?

Reply
1. admin says:
  
  June 20, 2016 at 10:02 pm
  
  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.
  
  Reply
  1. Anonymous says:
    
    May 31, 2017 at 3:58 am
    
    Eerr network pls righty
    
    Reply
Guy ZINGUI says:

October 19, 2016 at 7:55 pm

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.

Reply
1. admin says:
  
  October 20, 2016 at 2:18 am
  
  Clear the SQL Mode at run-time by putting this code into your PHP script –
  mysqli_query($link, "SET SESSION sql_mode = ''");
  
  Reply
  1. vio says:
    
    January 20, 2017 at 7:27 pm
    
    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
    
    Reply
    1. admin says:
      
      January 21, 2017 at 12:35 am
      
      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); ?>
      
      Reply
      1. Ebra Lash says:
        
        December 24, 2018 at 11:00 pm
        
        Thank You, It solved my Problem
Bruno Divino says:

January 24, 2017 at 7:18 pm

Hello my.ini file already looks like this:
Sql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

what do I do ? Because the error still persists.

Reply
1. admin says:
  
  January 25, 2017 at 1:06 am
  Set it to an empty string:
```
sql_mode = ""
```
  The above will clear SQL_MODE to remove all the new default values that are set…
  
  https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode
```
MySQL >= 5.7.8
Default Value:
ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION
```
  Reply
anonymous says:

May 28, 2017 at 11:01 am

sir i am using xampp in which line like :-
“Sql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES” dosen’t exist .what should i do?

Reply
1. admin says:
  
  May 29, 2017 at 9:12 pm
  
  Then you should add in line sql_mode = "", to clear out the default value of sql_mode that it takes when not set.
  
  Reply
Moises Urias says:

August 3, 2017 at 6:26 pm

Thank you, setting the traditional mode in the php session works for me

Reply
batman says:

September 6, 2017 at 9:19 pm

I had the same problem even though the cause it was completely different
in my case the file exported contained 2 tables and I was trying to import into one table, after a certain number of row I got teh same error… hope this can help someone

Reply
Marllos Kratka says:

April 2, 2018 at 2:20 pm

Congratulations on the explanations helped me a lot in our work.

I was having this same MySql error

Reply
1. Anonymous says:
  
  April 18, 2019 at 6:04 am
  
  where can i found that my.ini file
  
  Reply
  1. Anonymous says:
    
    November 27, 2019 at 9:57 am
    
    xampp/mysql/bin/
    
    Reply
Nat says:

October 9, 2018 at 11:55 am

thank you

Reply
fred says:

April 5, 2019 at 4:11 pm

thanks admin

this works……mysqli_query($link, “SET SESSION sql_mode = ””);

Reply
ikudaisi dehinde says:

June 16, 2019 at 11:34 am

It has been a huge stress on me to have gone this far. Many teacher knows it but dont know how to impact the required knowledge. I want to update my cart in php myadming and it is giving me incorrect integer value error ‘::1′.
I thank you for this great information to reset my sql_mode to “”
I have ben stuck for the past 4 months now. Thank you and God bless you richly

Reply
Sanctus Aura says:

May 5, 2020 at 5:18 am

You saved my day, thank you very much

Reply
diana says:

May 22, 2020 at 10:13 am

sir, i have a problem on my project can you help me? the problem like thiss “Data truncation: Incorrect integer value: ‘shfhsfg’ for column ‘Contact’ at row 1
Data truncation: Incorrect integer value: ‘shfhsfg’ for column ‘Contact’ at row 1
BUILD SUCCESSFUL (total time: 17 seconds)”

Reply
ND says:

May 29, 2020 at 11:08 am

Thanks a lot. It’s working.

Reply

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

Leave a Reply to Bruno Divino Cancel reply