I move one site to a new server with Windows 2003.
It is a MySQL database. I use PHP script to connect the database file and query it.
I run the same script more than 5 years on the old server. After I move it to new one, it gives me the error as subject.
I did a lot of Googling. Found the answer at this blog(the link is not working any more), but now it is not available. So, I quoted the most important part from the Google Cached page.
This is an sql-mode issue, the mode defines what SQL syntax should be supported and what kind of data validation should be performed. In my problem MySQL is trying to assign an empty string to an auto-increment INT field and, as we should all know, strings into INTs don’t go. Cue errors and the script dies.
Longer term I am going to have to re-work my code to fix this issue, but in the short term, I am going to reduce the sensitivity of the control. To lower the level of data validation we can set the sql-mode to a lower level or comment it out altogether.
Solution is below:
Edit the my.cnf (my.ini in windows) file and find and comment out the line:
#sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
With this line commented out, STRICT mode is no longer applied and MySQL should now insert adjusted values for invalid or missing values and more specifically, it should allow my empty string to be put into the int column.
I follow this solution and everything is OK for MySQL installation.
Related posts:
