3

Question:

Where is the proper WordPress place to add a filter for pre_get_table_charset, prior to it getting executed?

The pre_get_table_charset filter is triggered in wp-db.php::get_table_charset().


Update: Solution

Mark Kaplun pointed me in the right direction, which is to put the filter into a drop-in. Because we're using an MU setup (as evidenced by my table-name), the easiest and simplest drop-in was sunrise.php, which as I understand it, is designed to be loaded super-early anyway.

Update: Caveat

Once you successfully have the filter loaded, you will start getting these errors:

PHP Notice:  Undefined index: wp_2_options in /srv/www/wordpress/web/wp/wp-includes/wp-db.php on line 25xx

This is because wp-db.php doesn't cache the result the same way it would if it had to look it up from the DB, and this cache is used by both wp-db.php::get_table_charset() and and wp-db.php::get_col_charset().

Solution: define a second filter, pre_get_col_charset, with 3 parameters, which returns the same value as your pre_get_table_charset filter.

add_filter('pre_get_col_charset', function($charset, $table, $column) {
    return 'utf8mb4'; 
}, 10, 2);

Background:

WordPress generates a consistently slow query on our server:

SHOW FULL COLUMNS FROM `wp_2_options`

This is generated in wp-db.php::get_table_charset(), and is used to determine the charset for a table. It is, of course, entirely unnecessary, because we know what the charset is. The most recent WordPress update ensured all our tables were utf8mb4, so surely we can hard-code this, and reduce the page-load speed?

Yes, we can. WordPress even provides a filter for it.

    /**
     * Filters the table charset value before the DB is checked.
     *
     * Passing a non-null value to the filter will effectively short-circuit
     * checking the DB for the charset, returning that value instead.
     *....
     */
    $charset = apply_filters( 'pre_get_table_charset', null, $table );

Writing the code for this is trivial:

add_filter('pre_get_table_charset', function($charset, $table) {return 'utf8mb4'; }, 10, 2);

However, this filter is called VERY early in the Wordpress execution stack. It is called before plugins are loaded and before mu-plugins are loaded.

It is called AFTER config/application.php, but at that point, the add_filter() function is not yet defined.

I know there are a billion and one places where I can hack the WordPress core and insert that line, but I'd prefer not to if I can.

Also, we're using a Wordpress Bedrock setup, if that helps at all.

1

Seems like your assessment is correct and that specific function will be called before you will have a chance to add the filter in a traditional way. The related code flow is a call relatively early at boot time to wp_not_installed to check if wordpress is installed which calls in turn is_blog_active which tries to read the siteurl option, and therefor a DB query is made and the validation of table structure is done.

There seems to be two ways around it

  1. Have an object cache. You should in any case, but in this specific case the option will most likely be retrieved from the cache and no DB access will have to be done, at least not that early.

  2. Write a drop-in. Drop-ins are loaded much earlier in boot, and you probably have two options here, to override the DB class with the db.php drop-in or add an advanced-cache drop-in and set your filter there.

While it sound horrible to change the DB driver, in practice it is relatively safe as the drive do not change much between versions. Obviously the easier path is the advanced cache if you don't have object cache.

Obviously you should probably first investigate why is the query slow. from the description it doesn't seem like something that can get slow.

  • Using an object cache is something I piloted. We used Eric Mann's Redis Object Cache, and it went swimmingly in development. However it failed spectacularly in production. Reason being: in dev, the Redis server was on the same server as WP, so all was good. On AWS, Redis was on its own Elasticache server, and WP was on it's own EC2 instance. So for every request, a thousand network requests were made from one server to another, and the network became a massive bottleneck. I'll revisit and re-engineer it at some point. – haz Nov 23 '16 at 21:51
  • #2 seems to be the right option, and the easiest route, was inserting the code into the sunrise.php drop-in, which we already have defined and versioned-controlled. But... (see above) – haz Nov 23 '16 at 22:32
1

Update: After thinking it over I realized that though my approach prooves you can attach a filter at some point, it doesn't rule out the function already running before that point. So, this answer is at least incomplete, but I'll leave it here in case somebody comes up with the same idea.

Interesting question. Made me wonder: why would WordPress insert a filter in a place where it is not yet available? Wouldn't they have tested that? So I wrote a filter for get_table_charset myself and placed it in the functions.php of my development theme.

add_filter('pre_get_table_charset', 'wpse247109_charset', 10, 2);

function wpse247109_charset($charset, $table) {
  var_dump ($table . ' | ');
  return 'utf8mb4';
  }

This got me erratic dumps on the frontend (probably to do with some form of caching), but consistent messages on the backend:

string(15) "myprefix_options | " Notice: Undefined index: myprefix_options in /home/example.com/public_html/install/wp-includes/wp-db.php on line 2569

string(15) "myprefix_options | " Notice: Undefined index: myprefix_options in /home/example.com/public_html/install/wp-includes/wp-db.php on line 2569

string(15) "myprefix_options | "

So it seems the filter is called allright three times. The first two times it apparently cannot find the theme's option table, the third time it can. I also placed the filter in my development plugin, with the same results.

Summing it up: there should be no problem placing the filter in your theme's functions file or the main file of your plugin. You might want to switch off any caching during development.

  • "Wouldn't they have tested that?", it is a rhetorical question, right? Anyway in 4.6 the order of loading was somewhat changed so wp-cli will be able to use filters instead of providing its own settings.php file. Don't remember the details.. – Mark Kaplun Nov 23 '16 at 12:01
  • So for the record, putting it in theme functions.php didn't work for me, BUT... once I got it working, I did get the same error messages as you. There's what I think is a bug in the wp-db.php core, which assumes that if you're going to define the pre_get_table_charset filter, you're also going to define the pre_get_col_charset filter. Defining both will remove the errors. – haz Nov 23 '16 at 22:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.