Posted on

WordPress Database Error MySQL Server Has Gone Away 4.3 Cron

For nearly a week now my WordPress site has been running extremely slowly.   My initial thought was that the simultaneous release of a major update to all of the Store Locator Plus add-on packs coinciding with the WordPress 4.3 release was the culprit.    My CPU was maxing out and I was getting hourly warnings of system overloads.   A brief pause over the past weekend and I quickly upgraded my AWS instance to something twice as big.   Something that could handle 190 simultaneous web connections.   That should do it.

Within days the server was at a crawl again.  No CPU resource errors but pages were loading as slow as molasses.

I dug deeper.   The RDS instance was not even breathing hard.  25% average CPU and 60% average RAM consumption.   Only a couple of active connections at any given time.    Cache seems to be working well.

Checked my Apache profile.  No issues there, but I did tweak the spare servers to allow 40 instant connections with no delay.

Then I re-checked the log files I had looked at last week.   There were THOUSANDS of entries like this (truncated here, but also truncated mid-packet in the log files):

[Sun Aug 30 03:09:19 2015] [error] [client 192.88.134.3] WordPress database error MySQL server has gone away for query UPDATE `wp_options` SET `option_value` = ‘a:150:{i:1440663673;a:1:{s:26:\\”action_scheduler_run_queue\\”;a:1:{s:32:\\”40cd750
bba9870f18aada2478b24840a\\”;a:3:{s:8:\\”schedule\\”;s:12:\\”every_minute\\”;s:4:\\”args\\”;a:0:{}s:8:\\”interval\\”;i:60;}}}i:1440663761;a:1:{s:18:\\”vp_scan_next_batch\\”;a:1:{s:32:\\”40cd750bba9870f18aada2478b24840a\\”;a:3:{s:8:\\”schedule\\
“;s:21:

NOT GOOD.  That was NOT there last time.   Some forensic research indicates the MySQL connection was dying, triggering a log file overload, which obliterated working RAM and CPU on the web server and the log was never written.  Upgrading the server gave enough breathing room on the web server to log the DB error.

Turns out SOMETHING is trying to update the wp_options table on a regular basis.    I’m not sure if it was because it could not write the original packet, but it looks like this data I/O request was running every minute and failing.   For nearly TWO WEEKS now.    Tons of CPU, memory, and disk I/O on the web server and giving the DB server a little extra work along the way.

The culprit?

This wp_options update is exceeding the default packet size for a MySQL request.  By default MySQL servers prevent packets over 16MiB in size to prevent injection attacks or malformed SQL commands.    Something wants to write something a lot bigger than that for some reason.  As I later learned it it a bug in WordPress 4.3 core which already has a patch “waiting in the wings”.

The Quick fix?

Easy?   Update max_allowed_packet_size in the my.cnf file.   On an Amazon RDS server this is easily done by editing your parameter group and changing that setting on the RDS server.    32MiB should suffice.

The REAL fix…

After some digging it turns out the option name ‘cron’ has THOUSANDS of entries due to a but in WordPress 4.3.

You can read about the bug here:

https://core.trac.wordpress.org/ticket/33463

To employ the fix you will need to hack WordPress core files, or wait for 4.3.01 to be released.

This is the main file you need to fix.  If this doesn’t make any sense to you, DO NOT SCREW WITH IT.  You can break your WordPress install very quickly:

trunk/src/wp-includes/taxonomy.php

r33619 r33646
4446 4446 function _wp_check_for_scheduled_split_terms() {
4447 4447     if ( ! get_option( ‘finished_splitting_shared_terms’ ) && ! wp_next_scheduled( ‘wp_batch_split_terms’ ) ) {
4448         wp_schedule_single_event( ‘wp_batch_split_terms’, time() + MINUTE_IN_SECONDS );
4448         wp_schedule_single_event( time() + MINUTE_IN_SECONDS, ‘wp_batch_split_terms’ );
4449 4449     }
4450 4450 }

You can then clear out the over-sized ‘cron’ options setting with this code:

$cron = get_option("cron");
unset($cron["wp_batch_split_terms"]);
update_option("cron", $cron);

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

WordPress dbDelta Too Many Keys Specified

dbDelta Ticket 10404

I just submitted a patch to WordPress Core for ticket #10404 which is related to the built-in dbDelta function and how it manages the creation of keys in a new data table.  Since the Store Locator Plus and the new Super Extendo add-on packs both use external tables with indices attached, the dbDelta function is used on a regular basis.    Here are some things I learned about the dbDelta function and how it handles key creation that other plugin developers may find useful.

First, the dbDelta method uses TWO SETS of information to decide what to do with key management.  First it strips out all of the key creation commands into a “user provided SQL that builds indexes” array.   This is an array that contains all of the “KEY … blah…. ” lines out of your SQL command to create a table.   Second, it runs the MySQL SHOW INDEX command for the table that is being created and uses the data from the SHOW INDEXES return results to build a separate set of “metadata-derived index related SQL” statements, also in an array.

As a side note, the way in which the CREATE TABLE SQL command is processed is fairly simplistic and is one reason why each of your field definitions and your KEY statements should be on separate lines with no extra whitespace.  The process is doing nothing more than breaking up the string based on newline characters.    If you have extra whitespace before or after your field or index definitions the algorithm will never match the existing data and start creating and re-creating your table structure.

Once dbDelta has built both index lists it then compares the two.   If the index strings that are build from the metadata from the MySQL SHOW INDEX command matches EXACTLY with the string in your CREATE TABLE command, all is well and the commands to create a new index are removed from the user provided list of SQL commands to build indices.   The dbDelta method then loops through all of the remaining create index commands that are left.

In simpler terms:

dbDelta builds a list of create index commands based on what is already in the WordPress database for your new data table.

dbDelta compares that list of create index commands to your create table command and deletes those entries that already exists.

In other words “hey, we already have that index for that table, so we are going to skip it”.

However, as many have learned, the dbDelta function can appear to “go crazy” with the index building and create the same index over-and-over.   By default most servers running MySQL set a limit of 64 indices on a single table.   Also, by design, the common practice when adding or modifying a table in WordPress is to hook to the “upgrade and/or activate plugin” methods in WordPress.   In the simplest setup that means your create table command would only be run ONCE PER UPGRADE or ACTIVATION of your plugin.  If your plugin is only creating a single index. on an ID field for example, that means someone would need to upgrade or activate your plugin SIXTY THREE TIMES before you started seeing any indication of trouble.

The problem is that the other 62 times are significantly slowing down the website that your plugin lives on whenever your data table is getting a new record written to it or having an existing record updated.  The reason is that MySQL does a lot of overhead and extra data I/O for each index on your table.  It is the reason you don’t want to index every field and only want to index fields where there is a definitive performance benefit in doing so.

By the time you see the first “Too Many Indexes” warnings your plugin has already been slowing down servers for quite some time.     This is why I first rewrote the Store Locator Plus data indices nearly a year ago.   It can be a notable burden on sites that routinely update data and with 30,000+ installs accounting for MILLIONS of data records, that is a LOT of extra processing going on out there.

However there are ways to prevent the duplicate index from occurring by understanding HOW dbDelta builds the comparison string and using the same syntax in your CREATE TABLE commands that you pass to dbDelta.  Here are the hints:

  • Always use an uppercase KEY directive.This is something that one of my patches to the dbDelta 10404 ticket resolves if accepted to core.
  • Always include an index name in lowercase.This is something that one of my patches to the dbDelta 10404 ticket resolves if accepted to core.
  • Always include your field list in lowercase.
  • Do not add field lengths to the keys.
  • When creating compound keys always eliminate whitespace. The means no spaces before or after parens or around commas.

Here are a few examples of the right and wrong syntax.  Doing it wrong will create a duplicate index on the data table every time your dbDelta command is run.  Hopefully that command is only run when upgrading/activating a plugin:


WRONG:
key ( id , slid )

RIGHT:
KEY mycompoundkey (id,slid)

 


WRONG:
KEY (id)

RIGHT:
KEY id (id)


WRONG:
KEY fldname (fldname(250))

RIGHT:
KEY fldname (fldname)

Make sure you do not have ANY extra whitespace in your commands.    No space before the KEY directive.   Only a single space before and after the index name.   No spaces before or after parens or commas in compound fields.

This will hopefully help avoid some extra index creation commands from executing on your tables.

If the patches I submitted to core are accepted the case sensitivity issue should go away and you may get away with the shorthand version where the index name and key are the same (using KEY (id) would be valid).

Posted on

WordPress Core Wishlist – Admin Tables UI

WordPress Core banner

A few weeks ago I proposed the addition of infinite scroll to the WordPress admin table UI.   While working on testing and refining the upcoming Store Locator Plus 4 release I have a couple of other suggestions for the WordPress Core team to consider for inclusion in future releases.  These features are both related to the admin UI tables interface and are especially useful for sites with larger data sites.

With Store Locator Plus it is very easy to create thousands of custom post types with the click of a button.   Managing those custom posts can be a chore via the default WordPress interface.   A few simple changes to the admin tables UI can alleviate some of the problem areas and should be relatively easy to implement.

Bulk Actions / Delete Permanently

Provide an option to skip the “move to trash” step.   Adding “Delete Permanently” to the top-level drop down can eliminate a 2-step process for site administrators.    A simple “Are you sure?” confirmation can provide some level of protection against errant menu selection with products like VaultPress backups providing a fall-back safety net.

Admin Table Page Length

The default page length of an admin table is 20 items.    For sites that have thousands of pages or posts of any type this limitation presents a notable challenge to bulk management of those objects.    With just 75 items the above “delete this custom page type permanently” process takes 8 drop down selections and 8 button clicks to permanently remove just 75 items.    16 actions to manipulate 75 items.

Giving users the ability to set their admin table page length and remembering that selection for future site visits can significantly reduce the UI interaction.  Setting the admin table object count to show 100 items and the above process becomes 2 drop down selections and 2 mouse clicks.      Implement the “delete permanently” short cut above and it becomes 1 selection and 2 mouse clicks.

16 actions down to 3.    Sounds like a nice feature to me.

Here is a 3 minute YouTube video on the process and how I’ve implemented these 2 features in my custom admin UI for managing locations in Store Locator Plus:

Posted on

WordPress 3.6 Testing and QuotePress

I found a bug in WordPress Core while installing Debug Bar tonight.  It is actually a bug I’ve seen a few times in the past month but ignored mostly because it did not impact my plugins.   However I decided I’d go looking “under the covers” this time around versus ignoring a warning message I’ve been seeing regularly.

My WordPress Core Patch

I found the problem, a class that extends the base upgrade class in WordPress was not using the same parameter list as the parent.  Thus PHP throws out warnings about that sort of thing when you are running in strict mode.   Not a critical bug, but something that will dump extra lines into log files on servers running strict mode.  I don’t like that sort of thing as it is a performance hit while the server runs the disc I/O to write the warning to log.  Luckily this only happens when installing or upgrading plugins, so it is not going to be an “on every page load” issue.

However, while submitting the bug I decided I might as well submit the patch.   A few extra characters in 4 lines makes the warning go away.  Small. Simple.  Easy to explain.  Good candidate for a patch.    However, to submit the patch you need to grab the latest code from trunk and run the diff on that, NOT the production 3.5.1 release that I am developing my plugins with.     So I grab 3.6 and write the patch and submit it with my ticket.

We’ll see if they accept the patch, but I think it would be cool to think a little bit of code I wrote were to live in WordPress core.

My WordPress 3.6 Experiment

OK… so I have 3.6 on my server in a new directory.    I decide to point a local host name (wpdev) over to my 3.6 setup, while keeping 3.5.1 intact.  Both to test my new patch and to see what the guys (and gals) over at WordPress have in store for us in the next month or so.     Turns out A LOT.

The base theme is quite different from many of the past iterations.  I’m not sure if I like it yet, but it certainly makes you think about the user experience (UX) in a different way.

WordPress 3.6 preview
WordPress 3.6 preview

However the other big thing I noticed right away is how different the default edit post screen is with the new version.   There is a series of tabs across the top of the page that puts a lot more emphasis on the different object types that WordPress supports.    It includes these defaults:

  • Standard
  • Aside
  • Chat
  • Gallery
  • Link
  • Image
  • Quote
  • Status
  • Video
  • Audio
WordPress 3.6 Post Edit Screen
WordPress 3.6 Post Edit Screen

I also noticed the “list posts” page now has a column telling you what type of post it is.

I guess my thoughts of moving Store Locator Plus locations from a custom database table to a 100% custom post type structure for SLP version 4.0 is the right move.  It will let me completely integrate locations into the UX that WordPress appears to be heading for.    Think “locations” added to that list of other built-in support types with nearly infinite extensibility.  Coolness.

Bye Bye QuotePress

However, one thing that is certain.   The QuotePress project I brought over from Cyber Sprocket just to give it a “home”… that is pretty much dead with the arrival of WordPress 3.6.    That plugin is rudimentary and adds nothing  special beyond what WordPress has not built in.

I’m not sad about that though.  The plugin has been ignored and is in need of care and I have a TON of features and updates to do for both Store Locator Plus and the MoneyPress Master Edition that I can’t seem to get enough time for.

I’m liking where WordPress is going and what the core team is doing over there.   Can’t wait for 3.6 to roll out.  Now to test my plugin and some of my tools with the new WordPress codebase…


###

 Alexa Traffic Rank: 245,777
United States Flag Traffic Rank in US: 98,910
link-icon Sites Linking In: 664

Authority Technorati
Auth: 111


16478.

moved up 461 places

Site thumbnail