MySQL Zero-Date Import Errors: Fix Them Safely

By Greg Nowak. Last updated 2026-07-09.

A MySQL import that fails on '0000-00-00', '0000-00-00 00:00:00', or a date like '2012-00-00' is usually not just an import problem. It is a legacy data contract meeting a stricter database. That often appears during host moves, CMS upgrades, agency handovers, and staging restores where an old dump is being loaded onto a newer MySQL server.

The risky fix is to weaken the whole server because one import is blocked. The better fix is to identify the active SQL mode, find the affected columns, and choose between a narrow import workaround and a proper schema cleanup.

Why Zero Dates Fail on Newer MySQL

Current MySQL defaults include strict validation along with NO_ZERO_DATE and NO_ZERO_IN_DATE. Those modes control whether MySQL accepts all-zero dates and dates where the month or day is zero. MySQL still documents these as legacy-compatible values, but the zero-date mode names are deprecated as standalone switches and are expected to be folded into strict behavior in a future release.

For business and operations teams, the useful takeaway is simple: zero dates are usually technical debt. They may have meant “not set yet” in an old application, but newer database defaults treat impossible dates as something that should be made explicit with NULL, a valid date, or a clear business status field.

Check the Mode Before Changing It

Start with the connection that will run the import. The session value is what matters to that connection; the global value only initializes new sessions.

SHOW SESSION VARIABLES LIKE 'sql_mode';
SHOW GLOBAL VARIABLES LIKE 'sql_mode';

SELECT @@SESSION.sql_mode, @@GLOBAL.sql_mode;

This is where many quick fixes go wrong. SET GLOBAL does not repair the client session you already opened, and it can change behavior for unrelated applications that connect later.

Find the Columns That Still Depend on Zero Dates

Before deciding on a fix, audit the schema. This query finds date-like columns that still define all-zero defaults:

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
  AND DATA_TYPE IN ('date','datetime','timestamp')
  AND COLUMN_DEFAULT IN ('0000-00-00', '0000-00-00 00:00:00');

If the result is one or two columns, you may have a contained cleanup. If it appears across many tables, treat it as a migration risk, not a console annoyance.

The Safer Short-Term Import Bridge

If the restore is urgent and cleanup cannot happen first, remove only the zero-date modes for the current session. Run the import from that same session, then put the session mode back.

SET @original_sql_mode := @@SESSION.sql_mode;

SET SESSION sql_mode = TRIM(BOTH ',' FROM REPLACE(REPLACE(
  CONCAT(',', @@SESSION.sql_mode, ','),
  ',NO_ZERO_IN_DATE,', ','),
  ',NO_ZERO_DATE,', ','
));

-- Run the legacy import in this same connection.

SET SESSION sql_mode = @original_sql_mode;

This is safer than pasting a generic SET GLOBAL sql_mode = ... command because it does not change future connections for the whole server. It also preserves the rest of the current session mode instead of replacing it with a guessed list.

There is one practical catch: if your import tool opens a separate connection, the session change must be applied inside that connection. Changing one interactive MySQL client will not affect another process.

The Long-Term Fix: Make “Unknown” Explicit

Where you control the application, replace impossible dates with a real data rule. In many systems, that means making the column nullable and using NULL for “not known yet.” Test this on a copy first, especially if old PHP, CMS, or reporting code compares directly against '0000-00-00'.

ALTER TABLE your_table
  MODIFY your_date_column DATE NULL DEFAULT NULL;

UPDATE your_table
SET your_date_column = NULL
WHERE your_date_column = '0000-00-00';

Use the real column type in your own table. For DATETIME columns, check for '0000-00-00 00:00:00'. If application code used zero dates as a status flag, update that code at the same time instead of preserving the old ambiguity under a new schema.

When a Global Change Is Justified

A global or persisted change can be reasonable for an owned legacy environment, but it should be an infrastructure decision, not an emergency paste. MySQL distinguishes session, global, and persisted system variable changes. Session changes normally affect only the current client. Global changes affect new sessions. Persisted changes survive restarts by writing server configuration.

If you need a temporary server-wide exception, document why it exists, who depends on it, how it was tested, and when it should be removed. That runbook matters because zero-date issues often surface again during the next upgrade, replication change, or vendor handover.

Practical Rule

Use a session-level bridge to get a one-off legacy import unstuck. Use schema and data cleanup when the application will keep running. Treat zero dates as a migration signal, not just a MySQL error message.

If this came up during a client handover, hosting move, or CMS rebuild, Greg can help scope the import path, cleanup work, and rollout plan before it becomes a launch-week surprise.

Related on GrN.dk

• AI Crawler Control for Business Websites: Protect Content Without Sacrificing Search Visibility
• Importing External Data into Drupal: A Practical Migration Plan
• NGINX 1.30 changed upstream connection reuse by default: what to check before you upgrade

Need help with this kind of work?

Plan a safer MySQL migration Get in touch with Greg.

Sources

• MySQL 9.7 Reference Manual: Server SQL Modes
• MySQL 9.7 Reference Manual: SET Syntax for Variable Assignment
• MySQL 9.7 Reference Manual: Date and Time Data Types
• MySQL 9.7 Reference Manual: INFORMATION_SCHEMA COLUMNS Table