WordPress Plugin Development: MySQL strict mode 28. April 2011

When developing WordPress Plugins you should use a local web server installation like XAMPP.

To make sure your plugin does not use depracted functions of WordPress’ API, WP_DEBUG should always be enabled in wp-config.php:

define('WP_DEBUG', true);

If your plugin uses custom MySQL queries, MySQL strict mode should be enabled. If strict mode is enabled MySQL refuses from executing queries containing wrong data types. Heres an example:

The table `files` has a column `online` of type ENUM(’0′,’1′). In non-strict mode the following query is accepted, although its contains an invalid comparison:

SELECT * FROM files WHERE online = 1

This query is invalid since a field of type ENUM is compared with the integer value 1. In this case MySQL returns the result expected, since the ’1′ in the ENUM has the index 1. If the column was decalred ENUM(’1′,’0′) this query would have returned all files that were offline, since ’0′ is at index 1. The correct query is …
SELECT * FROM files WHERE online = '1'
.. since the enum contains a list of strings.

Enabling strict mode adverts the developer makes the developer aware of wrong data type comparisons. Moreover it refuses to executiv INSERT INTO queries filled with invalid data.

To enabled strict mode, open the MySQL configuration file (on windows with xampp this is located at xampp\mysql\bin\my.ini) with a text editor and add the following line to the [mysqld] section:

sql-mode                = "STRICT_ALL_TABLES"

After restarting the MySQL server it runs in strict mode and invalid queries will trigger a warning message.