[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:  closed
 Priority:  lowest       |   Milestone:
Component:  Database     |     Version:  6.4.2
 Severity:  normal       |  Resolution:  invalid
 Keywords:               |     Focuses:
-------------------------+----------------------
Changes (by saurabh.dhariwal):

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


Comment:

 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:24>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform