Posted on

WordPress wpdb Update/Insert Null Problems

WP Core Ticket 15158 Banner

While doing additional testing for upcoming Tagalong features I discovered some odd behavior with the featured and rank elements of Enhanced Results which I had left activated on my development system.    It turns out that when you edit a location some odd things start happening in the data.    Further research has uncovered another WordPress wpdb bug (ticket #15158) that impacts Store Locator Plus.

The bug , or “feature request” (depending whom you ask), has Store Locator Plus working differently when adding a location versus editing a location.    For the sake of discussion I will focus on the featured and rank extended data fields provided in Enhanced Results.

The wpdb insert command will accept a blank field and store it in the database with a NULL value.
Note: After further research this is not always the value.  MOST of the time the data goes in as a zero.  In some cases, I have yet to discover when/why, data goes in as a NULL value.   

The wpdb update command will accept a blank field and convert it to either a blank string or a ZERO if it is updating an integer field such as featured or rank.

In MySQL a NULL value and a 0 (or blank) are VERY different things.

What this means for Store Locator Plus is that adding a location and not putting anything into the rank field or leaving the featured box unchecked writes a record to the database where featured = NULL and rank = NULL.  When updating that same location and leaving those two elements untouched, the save location changes the featured = 0 and rank = 0.

This becomes a BIG problem when sorting results on output, especially with Enhanced Results.     One of the common settings that people are using is order by “Featured, Then Rank, Then Distance”.     In MYSQL this *WAS* being written as a ORDER BY featured DESC, rank ASC, distance ASC sort command on the returned data.

Why is this an issue?

Consider the case where two locations have been created.  Neither location has been setup to use either the featured or rank field.   You start with data like this:

Locatien A  : Featured NULL : Rank NULL : Distance 3 miles
Location B : Featured NULL : Rank NULL : Distance 1 mile

 

Before editing location A the locations were shown on the map search with Location B first then Location A.  That makes sense.   B is closer and since neither was set as featured or ranked it should take precedence.

However location A was edited to fix a typo and location B was not.  You end up with something like this:

Location A  Edited : Featured 0 : Rank 0 : Distance 3 miles
Location B New : Featured NULL : Rank NULL : Distance 1 mile

SLP Featured Rank Null Sort
A real-world example of editing a location changing featured, rank, distance sort order.

Why did featured and rank get set to 0?  Because of the wpdb update bug and how it handles null fields.   This has a drastic change on the search results.    Now when you perform the same exact search you get location A coming up before location B.

Why did the sort order change?

For MySQL 0 has more weight than null.     As such the ORDER BY featured DESC statement will put Location A before Location B because featured = 0 is higher than featured = NULL on the sorting algorithm used by MySQL.

Patches In Progress : Featured

Enhanced Results has a patch in progress that will address the FEATURED field.   That is fairly easy by changing the ORDER BY featured DESC clause to a slightly more complex variant that forces NULL and 0 settings to be equivalently weighted:

ORDER BY case featured is null return 0 case featured is not null return featured end DESC

In other words for sorting purposes turn null into a 0, then sort by that.

This patch works great for featured which is a checkbox.  The only values allowed are 0 (or null) and 1.   Since and unchecked box is always going to be 0 or null we can force it to a specific value.

Patches For Rank

I’m not sure yet how to deal with rank.  While I can force a null rank to be 0 that is not a great idea.    That means all locations will start with a 0 rank.  When you order by rank ASC the zero rank will ALWAYS come first.   That means leaving rank empty and setting other locations to “1”, “2”, and “3” rank values will put them AFTER all the empty/blank ranked locations.

That is not how people will expect that to work.

I’m working through various iterations outside of submitting another patch to WordPress core.  In my experience core patches take far too long to be implemented.  My last patch for dbDelta was submitted nearly a year ago and has yet to be incorporated into core.  The core team has too many other fixes to implement that affect far more people.      Since waiting a year or more for a fix is not acceptable to my user base I need to work around WordPress core shortcomings.

For now the featured entry patch is coming today.   The rank issue needs further investigation.

Update : LEFT JOIN

Turns out a bigger issue with this problem is the default values inserted into a LEFT JOIN if a record does not exist in the extended data table.    I need to either add a record to the extended data table when there is not extended data for a location or find a way to get LEFT JOIN to return ‘0’ instead of NULL on joined fields.

Posted on

Videos and SLP 3.9.6

Two quick announcements.

SLP 3.9.4/3.9.5 Bug

First, and most important, I am testing Store Locator Plus 3.9.6. A significant bug was found today that breaks the WordPress add new plugins system when any of the add-on packs are enabled. This has been fixed and is being tested now. This patch will be out tonight or first thing tomorrow at the latest. WordPress is caching the bad plugin data and it may take up to 12 hours for the bad data to clear your system after installing SLP 3.9.6. You can clear out the poptags transient in the wp_options table, but if you do it wrong you can break your system. If you are comfortable with manipulating the WordPress options table there is a forum post with the specific.

Videos

Second, and far less important, is just to let people know that I’ve been recording some very rudimentary videos on Store Locator Plus.  You fill find these videos scattered on pages throughout the site.   Hopefully in places where they make sense… at least to me.

With any luck, my video skills will improve over time and the videos will be less painful and less boring (maybe) to watch. For now I’m focusing on test runs, DevGru stuff for 3rd party add-on developers, and basic features. However it may be useful to new customers that are just starting to explore the product. As I get better at this video thing I’ll post more advanced topics. I’m also going to try to post a few videos every month… more often if time allows. If you have a suggestion for a “how do I do this?” video, please let me know.

I’m also open to any suggestions on how to make “using software” videos less boring.    Light the monitor on fire and try to finish the video before it melts?   That would require an external camera versus the screen cast setup… but I bet it would get more views on YouTube!

Back to testing…

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