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.
sir, i also have the same problem, but my SQL strict mode already disabled. what i do?, why this happens again?
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.
Eerr network pls righty
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.
Clear the SQL Mode at run-time by putting this code into your PHP script –
mysqli_query($link, "SET SESSION sql_mode = ''");
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
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 –
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 –
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.
Set it to an empty string:
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
sir i am using xampp in which line like :-
“Sql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES” dosen’t exist .what should i do?
Then you should add in line
sql_mode = ""
, to clear out the default value of sql_mode that it takes when not set.Thank you, setting the traditional mode in the php session works for me
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
Congratulations on the explanations helped me a lot in our work.
I was having this same MySql error