How to Fix MySQL Error 1365: Division by Zero

ERROR 1365 (22012): Division by 0 is raised when a division or modulo operation has a divisor of zero and MySQL is running in strict SQL mode. The error symbol is ER_DIVISION_BY_ZERO.

Impact

When this error occurs inside a SELECT statement, the query is aborted and returns no results. Inside a INSERT, UPDATE, or stored procedure, the statement rolls back (if inside a transaction) or halts execution at the offending row. The severity is higher in strict mode because MySQL treats the condition as an error rather than silently substituting NULL.

In non-strict mode (i.e., when ERROR_FOR_DIVISION_BY_ZERO is absent from sql_mode), MySQL returns NULL for the expression and issues a warning instead. Applications that were developed or tested against a permissive MySQL configuration may therefore work fine locally but fail in production environments where strict mode is enabled — a common source of environment-specific bugs.

Common Causes

Dividing a column value by another column that can be zero. For example, computing a per-unit cost as total_price / quantity where quantity is zero for some rows.
Dividing by a calculated expression that evaluates to zero. Aggregations such as SUM(), COUNT(), or DATEDIFF() can produce zero even when individual values are non-zero.
Using the modulo operator (% or MOD()) with a zero divisor. 10 % 0 and MOD(10, 0) trigger the same error as integer division.
Stored procedures or generated columns that contain arithmetic without guards. Logic written when strict mode was off may silently return NULL in development but raise 1365 after a MySQL upgrade or configuration change that enables STRICT_TRANS_TABLES or STRICT_ALL_TABLES.
ORM-generated queries. Frameworks like Django, SQLAlchemy, or Hibernate can emit arithmetic expressions (e.g., for percentage calculations in annotations or raw SQL fragments) that do not account for zero denominators.

Troubleshooting and Resolution Steps

Identify the offending expression. Reproduce the query and check SHOW WARNINGS immediately after:
```
SHOW WARNINGS;
```
The warning (in non-strict mode) or error (in strict mode) will reference the column position or expression.
Check the current SQL mode. Determine whether ERROR_FOR_DIVISION_BY_ZERO is active:
```
SELECT @@sql_mode;
-- or
SHOW VARIABLES LIKE 'sql_mode';
```
Guard the divisor with NULLIF(). The idiomatic MySQL fix is to wrap the denominator in NULLIF(expr, 0), which returns NULL when the value is zero — causing the division to yield NULL rather than an error:
```
-- Instead of:
SELECT total_price / quantity FROM orders;

-- Use:
SELECT total_price / NULLIF(quantity, 0) FROM orders;
```

Use CASE for more descriptive fallback values. If you want a specific default instead of NULL:

SELECT
  CASE
    WHEN quantity = 0 THEN 0
    ELSE total_price / quantity
  END AS unit_price
FROM orders;

Handle modulo by zero the same way:

SELECT value % NULLIF(divisor, 0) FROM my_table;
-- or
SELECT MOD(value, NULLIF(divisor, 0)) FROM my_table;

Fix the underlying data if zero is invalid. If zero should never appear in the denominator column, add a constraint:
```
ALTER TABLE orders ADD CONSTRAINT chk_quantity_positive CHECK (quantity > 0);
```
Temporarily disable the error (use with caution). For a migration or one-off data repair, you can remove ERROR_FOR_DIVISION_BY_ZERO from sql_mode for the current session:
```
SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'ERROR_FOR_DIVISION_BY_ZERO', '');
```
Do not remove it permanently from the server configuration without understanding which queries may silently return NULL as a result.

Additional Information

SQLSTATE 22012 is the standard SQL state for "division by zero" and is shared across database engines; the same state appears in PostgreSQL as well.
Related SQL mode flags: ERROR_FOR_DIVISION_BY_ZERO is automatically included when you set STRICT_TRANS_TABLES or STRICT_ALL_TABLES. Prior to MySQL 5.7.5 it was a separate flag; from 5.7.5 onward it is bundled into the default strict mode configuration.
MySQL 5.7 vs 8.0: MySQL 8.0 enables strict mode by default. Databases migrated from MySQL 5.6 or earlier (which shipped with a permissive default) often surface 1365 errors for the first time after upgrading.
ORMs: In Django, use NullIf from django.db.models.functions. In SQLAlchemy, use func.nullif(col, 0). In Hibernate/JPQL, use a CASE WHEN expression or a native query fragment.
Stored procedures and triggers: If the procedure was created when strict mode was off, it may still execute with the creator's sql_mode snapshot. Use SHOW CREATE PROCEDURE proc_name to inspect the sql_mode line at the top of the stored routine definition.

Frequently Asked Questions

Why does the query work in development but fail in production? The most common reason is that the development MySQL instance has strict mode disabled (or uses an older default configuration from MySQL 5.6), while production has STRICT_TRANS_TABLES enabled. Check @@sql_mode on both instances and align them, then fix the arithmetic expression with NULLIF().

Does NULLIF() affect performance? No measurably. NULLIF(expr, 0) is a lightweight conditional that the query optimizer handles inline. It does not prevent index use on the surrounding query.

Is it safe to just turn off ERROR_FOR_DIVISION_BY_ZERO? Only as a short-term workaround. Disabling it means MySQL silently returns NULL for division-by-zero expressions, which can propagate unexpected NULLs through calculations and produce incorrect results without any visible error. It is always better to guard the expression explicitly.

What is the difference between ERROR 1365 and a warning for division by zero? They describe the same event — a divisor of zero — but whether MySQL raises an error (1365) or a warning depends on the ERROR_FOR_DIVISION_BY_ZERO flag in sql_mode. With the flag set, you get the error and the statement stops. Without it, you get a warning in SHOW WARNINGS and the expression evaluates to NULL.