[wp-trac] [WordPress Trac] #8857: Make WP MySQL strict mode compliant

[WordPress Trac]

#8857: Make WP MySQL strict mode compliant
-------------------------+-----------------------
 Reporter:  ghostks      |       Owner:  (none)
     Type:  enhancement  |      Status:  reopened
 Priority:  lowest       |   Milestone:
Component:  Database     |     Version:  6.4.2
 Severity:  normal       |  Resolution:
 Keywords:               |     Focuses:
-------------------------+-----------------------
Changes (by saurabh.dhariwal):

 * status:  closed => reopened
 * resolution:  invalid =>


Comment:

 Replying to [comment:24 saurabh.dhariwal]:
 > This error message highlights a known compatibility issue between
 **WordPress** and **MySQL** when **strict SQL modes** are enabled —
 specifically when modes like `NO_ZERO_DATE` or `STRICT_ALL_TABLES` are
 active. These modes prevent inserting default date/datetime values like
 `'0000-00-00'` or `'0000-00-00 00:00:00'`, which WordPress still uses in
 some parts of its schema.
 >
 > ✅ Explanation
 > WordPress, by default, uses zero dates like:
 > - `'0000-00-00'` for `DATE`
 > - `'0000-00-00 00:00:00'` for `DATETIME`
 >
 > But **MySQL in strict mode** doesn't allow this anymore, as it's not a
 valid date per SQL standards. When your **MySQL server has strict mode
 enabled**, these invalid defaults cause the WordPress installation to
 fail.
 >
 > ---
 >
 > 🛠️ Solution Options
 >
 > **Option 1: Disable Strict Mode in MySQL**
 > This is the most common workaround.
 >
 > 1. Edit your MySQL config file (`my.cnf` or `my.ini`)
 >    ```ini
 >    [mysqld]
 >    sql_mode=NO_ENGINE_SUBSTITUTION
 >    ```
 > 2. Restart MySQL.
 >
 > Or, dynamically:
 > ```sql
 > SET GLOBAL sql_mode='NO_ENGINE_SUBSTITUTION';
 > ```
 >
 > Check the current mode:
 > ```sql
 > SELECT @@sql_mode;
 > ```
 >
 > > ⚠️ Be careful if you're on a shared host — you may not have access to
 change SQL modes directly.
 >
 > ---
 >
 > **Option 2: Patch WordPress Core (Not Recommended)**
 > Manually update the `wp-admin/includes/schema.php` and change default
 values for `date`/`datetime` columns from `'0000-00-00'` to `NULL`. This
 is **not recommended**, as updates to WordPress will override it.
 >
 > ---
 >
 > **Option 3: Use a MySQL Version with More Lenient Defaults**
 > MySQL 5.6+ tends to enable stricter SQL modes by default. Using MariaDB
 or an older MySQL version can avoid this — but again, not ideal or future-
 proof.
 >
 >
 > ---
 >
 > 💡 Best Practice (for Plugin/Theme Devs)
 > If you're developing plugins or custom database tables:
 > - Avoid using zero dates.
 > - Use `NULL` as the default for `DATE`/`DATETIME` columns.
 > - Always test your plugin/theme on strict MySQL setups.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/8857#comment:25>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform