Posted on

WordPress dbDelta Better In 4.2, Not Yet Perfect

WP dbDelta Bug Report Banner

There have been a LOT of changes in WordPress Core including updates to the dbDelta() method in the upgrade function set.   What is dbDelta()?  dbDelta is the primary function used by WordPress to determine if the structure of data tables needs to be augmented between releases.   It works great for upgrading core WordPress data tables.    Where it has problems is with developer-specific SQL statements used to create secondary tables in plugins and themes.    Store Locator Plus, for example, uses a secondary table to storing basic location data.

Unfortunately dbDelta has always had issues (see running commentary on Trac ticket 10404), especially when it comes to indices on a MySQL table.   Most well-designed data tables will have primary and secondary indexes on the table to improve query performance.    dbDelta has a hard time figuring out when an index already exists and has not been modified.   If you did not craft your CREATE TABLE command EXACTLY the way dbDelta wants to see it, the dbDelta method will create new indexes.    This can cause performance degradation and unnecessary overhead in the MySQL tables when a data column ends up with 10, 20 , 30+ copies of the index; one created every time the plugin or theme is updated.

Many plugins and themes suffer from this malady.   Over two years ago I dug into dbDelta and learned the syntax secrets to get WordPress to stop creating duplicate indexes.   Today I found a site that was back to its old tricks running the latest version of Store Locator Plus and WordPress 4.2.2.   As it turns out the rules for dbDelta have changed and thus the syntax for creating a “dbDelta friendly” SQL statement has changed as well.    With dbDelta a behind-the-scenes feature it is never broadcast to the general public that it has changed and thus goes undetected for a while when something goes awry.

Turns out the changes are not new.  They go back to at least WordPress 4.1.    The last time I dug into this issue was WordPress 3.8.   Guess it is time for some phpUnit testing on dbDelta within my own plugin suite testing.   If things are running properly the dbDelta command for an existing installation of Store Locator Plus 4.2 should yield NO QUERY UPDATES when activating/installing a newer version of the plugin.

WP 4.1 dbDelta Results

Source SQL:

dbDelta SQL: CREATE TABLE wp_store_locator ( sl_id mediumint(8) unsigned NOT NULL auto_increment, sl_store varchar(255) NULL, sl_address varchar(255) NULL, sl_address2 varchar(255) NULL, sl_city varchar(255) NULL, sl_state varchar(255) NULL, sl_zip varchar(255) NULL, sl_country varchar(255) NULL, sl_latitude varchar(255) NULL, sl_longitude varchar(255) NULL, sl_tags mediumtext NULL, sl_description text NULL, sl_email varchar(255) NULL, sl_url varchar(255) NULL, sl_hours varchar(255) NULL, sl_phone varchar(255) NULL, sl_fax varchar(255) NULL, sl_image varchar(255) NULL, sl_private varchar(1) NULL, sl_neat_title varchar(255) NULL, sl_linked_postid int NULL, sl_pages_url varchar(255) NULL, sl_pages_on varchar(1) NULL, sl_option_value longtext NULL, sl_lastupdated timestamp NOT NULL default current_timestamp, PRIMARY KEY (sl_id), KEY sl_store (sl_store), KEY sl_longitude (sl_longitude), KEY sl_latitude (sl_latitude) ) DEFAULT CHARACTER SET utf8

dbDelta Updates List:

QUERY: ALTER TABLE wp_store_locator CHANGE COLUMN sl_linked_postid sl_linked_postid int NULL
QUERY: ALTER TABLE wp_store_locator CHANGE COLUMN sl_lastupdated sl_lastupdated timestamp NOT NULL default current_timestamp
QUERY: ALTER TABLE wp_store_locator ADD PRIMARY KEY (sl_id)
QUERY: ALTER TABLE wp_store_locator ADD KEY sl_store (sl_store)
QUERY: ALTER TABLE wp_store_locator ADD KEY sl_longitude (sl_longitude)
QUERY: ALTER TABLE wp_store_locator ADD KEY sl_latitude (sl_latitude)
for update

Update Results:

[wp_store_locator.sl_linked_postid] => Changed type of wp_store_locator.sl_linked_postid from int(11) to int
[wp_store_locator.sl_lastupdated] => Changed type of wp_store_locator.sl_lastupdated from timestamp to
[0] => Added index wp_store_locator PRIMARY KEY (sl_id)
[1] => Added index wp_store_locator KEY sl_store (sl_store)
[2] => Added index wp_store_locator KEY sl_longitude (sl_longitude)
[3] => Added index wp_store_locator KEY sl_latitude (sl_latitude)

My WP 4.2 dbDelta Results

Better, but still not quite right.

Source SQL:

CREATE TABLE wp_store_locator ( sl_id mediumint(8) unsigned NOT NULL auto_increment, sl_store varchar(255) NULL, sl_address varchar(255) NULL, sl_address2 varchar(255) NULL, sl_city varchar(255) NULL, sl_state varchar(255) NULL, sl_zip varchar(255) NULL, sl_country varchar(255) NULL, sl_latitude varchar(255) NULL, sl_longitude varchar(255) NULL, sl_tags mediumtext NULL, sl_description text NULL, sl_email varchar(255) NULL, sl_url varchar(255) NULL, sl_hours varchar(255) NULL, sl_phone varchar(255) NULL, sl_fax varchar(255) NULL, sl_image varchar(255) NULL, sl_private varchar(1) NULL, sl_neat_title varchar(255) NULL, sl_linked_postid int NULL, sl_pages_url varchar(255) NULL, sl_pages_on varchar(1) NULL, sl_option_value longtext NULL, sl_lastupdated timestamp NOT NULL default current_timestamp, PRIMARY KEY (sl_id), KEY sl_store (sl_store), KEY sl_longitude (sl_longitude), KEY sl_latitude (sl_latitude) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

dbDelta Updates List:

QUERY: ALTER TABLE wp_store_locator CHANGE COLUMN sl_linked_postid sl_linked_postid int NULL
QUERY: ALTER TABLE wp_store_locator CHANGE COLUMN sl_lastupdated sl_lastupdated timestamp NOT NULL default current_timestamp
QUERY: ALTER TABLE wp_store_locator ADD PRIMARY KEY (sl_id)

Update Results:

[wp_store_locator.sl_linked_postid] => Changed type of wp_store_locator.sl_linked_postid from int(11) to int
[wp_store_locator.sl_lastupdated] => Changed type of wp_store_locator.sl_lastupdated from timestamp to
[0] => Added index wp_store_locator PRIMARY KEY (sl_id)

WP 4.2 dbDelta SQL Best Practices

Some of these carry over from the prior dbDelta comments, some are new with later versions of dbDelta and MySQL.   The code is fairly robust, but if your MySQL installation comes back with a different format for the describe command this can wreak havoc on dbDelta().     I’ve presented things to look out for when crafting your dbDelta SQL statements as well as possible improvements in WP Core for dbDelta.    I may submit patches once again, but it has proven nearly impossible to get patches reviewed and approved for dbDelta.   Makes sense given the HUGE impact this function has on the WordPress Core engine, but there are thousands of plugins and themes wreaking havoc on WordPress performance due to some of these issues.    Maybe a detailed review like this will help get some updates into core.

Table Creation / Modification Commands

dbDelta() uses the MySQL DESCRIBE <table> command to fetch data about the table name passed in to dbDelta().  It is parsed with a regular expression out of the SQL statement looking for CREATE TABLE <…> , CREATE DATABASE <…> , INSERT INTO <..>, and UPDATE <…>  where the first “word” in the <…> part is assumed to be the table name.

Notes

  • The process appears to be case sensitive.  Use the commands as noted above in all caps.
  • Make sure you have no extra spaces, newlines, or other whitespace in between CREATE and TABLE or the other two-word directives noted here.
  • Make sure your table name has a leading space before and trailing space after.

Possible Improvements

  • Consider make thing preg_match() case insensitive.
  • Consider changing the single-space in commands like INSERT INTO into multi-whitespace regex i.e. INSERT\s+INTO

Defining Column Types

On some servers, such as my CentOS 7 development box, MySQL Ver 15.1 Distrib 5.5.41-MariaDB, has decided that post types of INT really mean INT(11).   Different variations may post different results back from the DESCRIBE command, but here is what I find working on MOST servers including my Linux dev box:

  • Always specify the LENGTH for each field type that accepts a length.  For most fields use the default length.
    • int(11)
    • mediumint(8)
    • varchar(1) to varchar(255), 255 is the default.
  • Fields without lengths:
    • longtext
    • mediumtext
    • text
  • Do not put spaces between the field type and the parens or the number in parens for the length.
  • User lowercase for field name and the field type.
  • Use uppercase for the NULL and NOT NULL specification.
  • Do NOT have extra spaces between the field name, the field type, and any of the additional definition parameters.

Examples

global $wpdb;
$sql =
'CREATE TABLE my_table (
my_id mediumint(8) unsigned NOT NULL auto_increment,
my_first_field varchar(1) NULL,
my_second_field varchar(255) NULL,
my_third_field int(11) NULL,
my_lastupdate timestamp NOT NULL default CURRENT_TIMESTAMP
)
DEFAULT CHARACTER SET ' . $wpdb->charset;

Things the cause problems in my setup:

  • Two spaces after the field name my_lastupdate and the word timestamp.
  • Using int instead of int(11) on my_third_field.
  • Using lowercase CURRENT_TIMESTAMP on my_lastupdate.

Possible Improvements

  • Consider make thing preg_match() case insensitive.
  • Make the comparison on the default case insensitive, or shift the inbound query uppercase.
  • Consider changing the single-space in commands into multi-whitespace regex tests.

 

Defining Keys (Indices)

The key comparison system is just as strict with whitespaces and case sensitivity as the column comparison routine in dbDelta.   You MUST have TWO spaces after the keywords “PRIMARY KEY” before defining your index field but only ONE SPACE after all other keys.

  •  ALWAYS declare the column names for every key type, including primary key.
  • ALWAYS put your column names inside parenthesis with no spaces.
  • PRIMARY KEY should NEVER have a key name.
  • PRIMARY KEY is always followed by two spaces then the parenthesis then the column name and closing parenthesis.
  • KEY is always followed by a single space, the column name, then a single space then parenthesis around the column name.
  • They keyword PRIMARY KEY must be uppercase.

Examples

global $wpdb;
$sql =
'CREATE TABLE my_table (
my_id mediumint(8) unsigned NOT NULL auto_increment,
my_first_field varchar(1) NULL,
my_second_field varchar(255) NULL,
my_third_field int(11) NULL,
my_lastupdate timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY  (my_id),
KEY third_key (my_third_field)
)
DEFAULT CHARACTER SET ' . $wpdb->charset;

Things the cause problems in my setup:

  • Missing the second space after the PRIMARY KEY definition.

Possible Improvements

  • Consider changing the single-space in commands into multi-whitespace regex tests.
  • Make the indices incoming text for the PRIMARY and KEY keywords force to uppercase.
Posted on

WordPress dbDelta Bug Affects SLP

There is a bug that remains open in the WordPress Core tracking system that impacts Store Locator Plus database tables.  The bug will degrade database performance after each consecutive update to the Store Locator Plus base plugin.    The problem and resolution are described below.

The Problem

There is a glitch in a core WordPress function, dbDelta(), that creates duplicate table indexes.    Store Locator Plus uses 3 indexes on the locations table.  Indexes significantly increase the performance of database searches when used properly.  For Store Locator Plus an index exists on the store name, the latitude, and the longitude.   The latter two mean that whenever someone does a location search on your website the data returned from the locations table is found much more quickly than if the data index was not in place.  On large installs, those with hundreds or thousands of locations, the difference is notable.

To put the index and any other data table elements in place, SLP uses the built-in dbDelta function from WordPress.  This is the recommended method for managing table and index creation as well as structure updates.  It works great.  Most of the time.

The problem is that dbDelta() has a glitch that creates a new index every time the Store Locator Plus version changes.    It should be looking for the store, lat, and long indexes and if not there it will create them.  Instead if it finds an existing index it creates a new index and adds a numeric suffix.    After a number of upgrades to SLP you end up with a database that has multiple indexes like this:

  • sl_store
  • sl_store_1
  • sl_store_2
  • sl_latitude
  • sl_latitude_1
  • sl_latitude_2
  • sl_longitude
  • sl_longitude_1
  • sl_longitude_2

Those _# indexes are IDENTICAL to the non-suffixed version.

This is bad.   Any time a record is updated or added in the locations table it is MUCH slower as every index needs to be updated.  After just 3 updates MySQL is performing 9 index updates on every record operation versus 3.

It can also confuse and SLOW DOWN record searches as the MySQL engine will take longer to determine which is the best index to use as it has to load the entire index directory.  I assume MySQL is smart enough to drop after the “first perfect match”, but that is purely conjecture.   If that is true the search impact would be minimal.  If MySQL is not that intelligent and it looks at ALL INDEXES before selecting the “best match” during a search the impact here can be notable as well, though not nearly as dramatic as a updating or inserting new data.

Do You Have A Problem?

Unfortunately there are only 2 ways to tell.

First – your WordPress site starts throwing errors (usually hidden in the error log) that are coming from MySQL and tell you your index limit of 64 entries has been exceeded.    You shouldn’t need to increase this limit, it is per-table and there are very few (if any) cases where 64 indexes on a single table is helpful.

Second – use the MySQL command line tool or phpMyAdmin and look at the wp_store_locator table.   If it has _# indexes they need to go.

The Manual Fix

The way to fix installations by hand is to use the MySQL command line tool, or PHPMyAdmin and drop the _# indexes from the wp_store_locator table.   Since Store Locator Plus is not the only plugin affected you may want to check for any other tables that are created by plugins and see if they suffer from the same issue.  Contact the plugin author before randomly deleting indexes.

From the MySQL command line you can execute the DROP INDEX command as often as necessary based on how many duplicate indexes appear:

mysql> DROP INDEX sl_store_2 ON wp_store_locator;
mysql> DROP INDEX sl_latitude_2 ON wp_store_locator;
mysql> DROP INDEX sl_longitude_2 ON wp_store_locator;

You should leave the indexes without the _# intact.

The Automated Fix

I am looking into ways to detect and auto-delete the secondary indexes in Store Locator Plus in the 3.9.X+ updates, however there is a danger of bloating the update/install process which I want to avoid.    I am also looking for ways to prevent the problem from happening in the first place and have been active with the reported bug to see if there is a way to get WordPress Core developers to patch the problem.

If a workaround is found it will be noted in the updates road map for SLP.

###

Alexa Rank: 264,930 / 89,424 / 611

Technorati Rank: 27347