Shoretec Solutions

Linkedin

The “Same Server, Same Code… Different Bug?!” — A Sneaky MySQL Trigger Gotcha That Bit Our Live Site

Why a blank date crashed only on LIVE (and how baked‑in SQL_MODE inside triggers caused it)

Symptom: A harmless empty date turns fatal (but only on LIVE)

Front-end behavior: a user leaves a date blank and submits.

Expected: if no date, store “nothing” (NULL) and carry on.

Actual:

  • TEST/local: request succeeds.
  • LIVE: request fails with a fatal SQL error: "Incorrect datetime value: '0000-00-00 00:00:00' for column facture.Jour_j "

Same PHP. Same SQL. Same server. Same database export. Only LIVE fails.

The Rabbit Hole: It looked like a trigger or data issue… but both checked out

We checked the usual suspects:

  • Triggers present? Same set on TEST and LIVE.
  • Trigger bodies identical? Hashes matched (or differed in harmless ways).
  • Zero-dates in source tables? None on LIVE; columns allowed NULL.
  • Restoring LIVE into TEST? Everything worked fine there too.

Something invisible was different…

The Culprit: MySQL triggers bake in SQL_MODE at creation time

In MySQL, each trigger permanently stores the SQL_MODE that was active when it was created.

At runtime the trigger uses its stored mode, not the current session/global one.

Result:

  • LIVE triggers were created under strict modes (e.g., NO_ZERO_DATE, NO_ZERO_IN_DATE, STRICT_TRANS_TABLES) → zero-dates are rejected.
  • TEST triggers were created under a permissive mode (e.g., NO_AUTO_VALUE_ON_ZERO) → zero-dates are tolerated/coerced.

That’s why identical code/data behaved differently across DBs on the same server.

How to confirm quickly

Run this against both schemas to see each trigger’s baked-in SQL_MODE:

SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_OBJECT_TABLE, SQL_MODE, DEFINER, CREATED FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA IN ('walex_live','walex_test') AND ACTION_STATEMENT LIKE '%jour_j%';

Why the front end made it worse (and how to harden it)

A blank form date often becomes an empty string ''. If your SQL uses Date_depart='$value', MySQL may coerce '' → '0000-00-00...' which strict modes reject.

Fix at the edge: send NULL for empty dates.

$Date = ArrangeDateForSave($_POST['input_date'] ?? ''); $DateSql = ($Date and $Date not in ['0000-00-00','0000-00-00 00:00:00']) ? "'$Date'" : "NULL"; // then use $DateSql in your UPDATE UPDATE conteneur SET Date_depart = $DateSql WHERE id_dossier = $dossier_id;

Two practical solutions

Option A — Make LIVE behave like TEST (re-bake trigger SQL_MODE)

// 1) Capture trigger DDL:    SHOW CREATE TRIGGER walex_live.after_update_dossier\G   SHOW CREATE TRIGGER walex_live.before_update_facture\G // 2) Recreate on LIVE with a permissive session mode:   SET @old := @@SESSION.sql_mode;   SET SESSION sql_mode = 'NO_AUTO_VALUE_ON_ZERO';   DROP TRIGGER IF EXISTS walex_live.after_update_dossier;   -- CREATE TRIGGER ... (paste body unchanged)   DROP TRIGGER IF EXISTS walex_live.before_update_facture;   -- CREATE TRIGGER ... (paste body unchanged)   SET SESSION sql_mode = @old; // 3) Verify:   SELECT TRIGGER_NAME, SQL_MODE FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA='walex_live';

Option B — Keep strict mode and normalize dates to NULL

  • Edge (PHP): use NULL for blank dates (snippet above).
  • In triggers: wrap any source date with NULLIF to collapse zero-dates to NULL before assignment: NULLIF(, '0000-00-00') and/or NULLIF(, '0000-00-00 00:00:00').

“But I copied LIVE to TEST—why weren’t they identical?”

Because importing a dump into TEST recreates triggers under TEST’s current session SQL_MODE, not the mode they had on LIVE. You effectively “washed” the strictness during import, so the same data worked on TEST.

To make imports deterministic with the MySQL CLI:

mysql --database=walex_live \ --init-command="SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO'" \ -u root -p < walex_live_dump.sql

Post-import audit:

SELECT TRIGGER_NAME, EVENT_OBJECT_TABLE, SQL_MODE FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA='walex_live' ORDER BY EVENT_OBJECT_TABLE, TRIGGER_NAME;

Copy-paste checklist for your runbook

If a “simple” front-end action fails only on LIVE, check trigger SQL_MODE:

SELECT TRIGGER_SCHEMA, TRIGGER_NAME, SQL_MODE FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA IN ('walex_live','walex_test');

On the edge, send NULL for blank dates.

If keeping strict modes, NULLIF zero-dates inside triggers.

For parity with TEST, recreate triggers on LIVE under the desired mode.

On imports, pin SQL_MODE via --init-command and audit with information_schema.TRIGGERS.

Takeaway

From the UI, this looked trivial: submit a blank date. Under the hood it was a perfect storm of date coercion + trigger chains + baked-in SQL_MODE differences. The ultimate fix was straightforward, but finding it required looking past code/data diffs to the mode each trigger was born into. Next time LIVE behaves differently from TEST for no apparent reason, ask: “What SQL_MODE were these triggers created with?”

Stay Connected with Shoretec — Join the Shoretec Circle

Shoretec Solutions is a fast-growing custom software development company in Canada that specializes in building custom business applications for small and mid-sized organizations. We focus on creating efficient, logic-driven systems—such as ERP, CRM, project management, and internal operations tools—that streamline workflows, improve visibility, and scale with the business.

Get updates on our latest case studies, releases & tech insights. Follow Shoretec online.

Scroll to Top