The Migration HeadacheIt is a classic scenario: your legacy app works perfectly on the old server but crashes immediately on a fresh MySQL 8.0 instance. This error usually crops up when moving PHP 5.6 or Python 2.7 codebases to modern stacks. While the code functioned fine for years, your INSERT or UPDATE queries now hit a wall with this message:
ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1
The root of the problem is simple. Your application is trying to save an empty string ('') into an INT or BIGINT column. Older MySQL versions were more forgiving; they would catch an empty string, shrug, and silently convert it to 0. Modern versions refuse to guess.
The Cause: Strict ModeThis behavior stems from a setting called STRICT_TRANS_TABLES. Ever since MySQL 5.7.5, strict mode has been enabled by default. When active, the database stops performing "implicit data conversions" that could lead to data loss. Since an empty string is not a valid number, MySQL throws a hard error instead of a warning.
Check your current configuration with a quick query in the MySQL terminal:
SELECT @@sql_mode;
If the results include STRICT_TRANS_TABLES or STRICT_ALL_TABLES, the database is enforcing rigid type safety.
Solution 1: Fix the Application Logic (The Right Way)This is the most robust, long-term solution. If a numeric field is optional, your code should send a NULL or an actual 0 rather than an empty string.
Example in PHPAvoid passing raw, unvalidated input directly into your SQL strings. If a POST value is empty, explicitly set it to NULL:
// Instead of sending an empty string...
$age = ($_POST['age'] === '') ? "NULL" : (int)$_POST['age'];
$sql = "INSERT INTO users (age) VALUES ($age)";
Standard SQL ApproachRefactor your manual queries to ensure they follow these patterns:
-- This fails in strict mode
INSERT INTO products (stock) VALUES ('');
-- These will succeed
INSERT INTO products (stock) VALUES (NULL);
INSERT INTO products (stock) VALUES (0);
Solution 2: The Quick Fix: Session-Level ChangeIf you are in a pinch and need the app running immediately, you can relax the rules for the current database connection. This acts as a temporary bandage for legacy scripts while you work on a permanent code fix.
Execute this query right after your application establishes a database connection:
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
By stripping away the strict mode flags, MySQL reverts to its legacy behavior, converting empty strings to 0 while issuing a minor warning in the background.
Solution 3: Permanent Server-Wide AdjustmentIf you manage the infrastructure and need to support an older CMS or ERP system that cannot be easily patched, you can disable strict mode globally.
On Linux (Ubuntu/Debian)- Open your configuration file: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf.- Locate the [mysqld] block.- Add or update the sql_mode definition:```
[mysqld]
sql_mode = "NO_ENGINE_SUBSTITUTION"
```- Restart the service to apply changes: sudo systemctl restart mysql### On Windows (XAMPP)- Open the XAMPP Control Panel and click Config next to MySQL.- Select my.ini from the list.- Find the sql-mode line and change it to sql-mode="NO_ENGINE_SUBSTITUTION".- Restart the MySQL module.## VerificationAlways verify the change. Check the global variable first:
SELECT @@GLOBAL.sql_mode;
Confirm that STRICT_TRANS_TABLES is gone. Finally, run a manual test to ensure the error is resolved:
INSERT INTO your_table (your_int_column) VALUES ('');
If you see "1 row affected" followed by a warning, the fix worked. You can run SHOW WARNINGS; to see exactly how MySQL handled the conversion.