I got a lot of error messages: “incorrect datetime value in column …”, “incorrect string value in column …” and “incorrect integer value in column …” when I migrate my web application from the staging server to production server.
To solve the issue, you may need to validate all your data before passing to your MySQL database to make sure they match with your predefined data type. Or if you have a root access to the server, follow these steps:
- 1. Open the MySQL configuration file (my.ini in Windows or my.cnf in Linux). Please create a backup for it as always.
- 2. Disable the line:
sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION” by removing it or adding a # at the beginning of the line like this: [text] # Set the SQL mode to strict
#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
[/text] - 3. Restart the MySQL server
Note: you can find the MySQL configuration file by using command in Linux:
[text] whereis my.cnf[/text]
Or if you’re on Windows Server, it could be:
+ C:\Program Files\MySQL\MySQL Server 5.0\bin
+ C:\xampp\mysql\bin
