Skip to main content

The future of WPDB

Lately I’m busy with exploring the future of WPDB. Ticket number #21663 on Trac has a lot of discussions and Kurt Payne started with redoing the code. From his code I built a plugin that can be found on WordPress.org. This allows you to use PDO, MySQLi and MySQL functions. It will choose the one that fits best. I think the basic is robust and everything should work fine now. So when you use a decent plugin that uses WPDB you should have no extra issues with it.

So everything seems fine right? Not really since there are a 1722 plugins on WordPress.org that are making raw calls to mysql_* functions. What is around 7.4% of all plugins. Obviously some of them can be really old and maybe not even used. Also plugins like WP DB Driver are totally fine to call mysql_* functions directly. So how do we deal with plugins that doing things wrong.

Solutions

Plugin developers need to adjust their plugins

Forcing plugin developers to adjust their plugins is probably the option but as always we do care a lot of the user experience. They don’t know that their code is crappy and when they upgrade WordPress things break without really knowing why it breaks.

A few weeks ago I found $_page = mysql_real_escape_string( strtolower( trim( strip_tags( $_REQUEST['page'] ) ) ) ); inside the framework of a large theme shop. First of all the code is stupid since since do so much things on checking the current page isn’t really needed but also the direct call to a mysql function is wrong. Their answer on this bug was:

Although, this is not a bug, and just a different way of doing things, there is a valid point in your suggestion, so we will put this under review.

I didn’t respond back on that since it is a bug and doing if a different way is fine but that code is just doing it wrong (it isn’t database data). Two days later I did got a second response that they will implement it. If a lot of developers will think like this maybe it’s fine to just let developers change their code. Sometimes people learn when things really break. Also obviously that this case is different then using mysql_query().

Start loading PDO/MySQLi at certain PHP versions

This solution can be that we only start loading PDO/MySQL when the user runs PHP 5.5 or higher. This way we can slowly release this feature since not a lot of sites run PHP 5.5. The reason I choose 5.5 is because then you start seeing deprecation notices but we can easily say PHP 5.4 to target a bigger group.

What is cool when only select PHP 5.5 is that we then can return a doing_it_wrong message. Since all mysql_* methods will return a E_DEPRECATED warning. The following code shows an error handler WordPress can use for it. In this case it always work since most of the MySQL errors will return an error when we use PDO or MySQLi. The Error context can also be E_ALL ^ E_NOTICE. It all depends on what the need would be.

function wp_set_error_handler() {
	if ( defined( 'E_DEPRECATED' ) )
			$errcontext = E_WARNING | E_DEPRECATED;
		else
			$errcontext = E_WARNING;

	set_error_handler( function( $errno, $errstr, $errfile ) {
		if ( 'wp-db.php' !== basename( $errfile ) ) {
			if ( preg_match( '/^(mysql_[a-zA-Z0-9_]+)/', $errstr, $matches ) ) {
				_doing_it_wrong( $matches[1], __('Please talk to the database using $wpdb' ), '3.7' );

				return apply_filters( 'wpdb_drivers_raw_mysql_call_trigger_error', true );
			}
		}

		return apply_filters( 'wp_error_handler', false, $errno, $errstr, $errfile );
	}, $errcontext );
}

wp_set_error_handler();

Always use MySQL functions

Currently the code will first try to use PDO or MySQLi but we can also always check if we can use mysql_*() functions. So everything should still work as it was. But when you want to use a different driver you can still specify that in your config file: define( ‘WPDB_DRIVER’, ‘pdo_mysql’ );. We can still use the error handler to throw doing it wrong messages on PHP 5.5 and higher and when mysql_*() functions are really removed then looking at PDO and MySQLi again.

Backwards compatibility

So when we don’t use mysql_*() functions things will break. We can catch the errors with our own error handler and throwing doing it wrong messages but most people will not see those. Throwing fatals is probably a bit to much but I guess it depends on the use case.

Always use mysql_connect

This one was mentioned in the Trac ticket. The idea of it is even when you use PDO or MySQLi that we still setup a MySQL connection with mysql_connect()/mysql_select_db(). But that sounds just wrong to me. It will slow down WordPress a bit since I doubt that PHP isn that smart to see there is a same connection. Also it’s stupid for a lot of sites doing things right. We shouldn’t not punish them for carefully choose their plugins.

Lazy load connection

This is a thing I’m now playing with and see how far we can go. One of the things I played with was setting the default MySQL information but the problem is that you can’t select the default database so this doesn’t really work. For example mysql_query() will not show a PHP error anymore but you get the error “No database selected” from mysql_error().

if( 'mysql' != $driver ) {
	ini_set( 'mysql.default_host', DB_HOST );
	ini_set( 'mysql.default_user', DB_USER );
	ini_set( 'mysql.default_password', DB_PASSWORD );
}

A thing that can be tried is to expand the error handler and try to do a mysql_connect() on errors from mysql_*() function. It will most likely not fix the first error but if there are more mysql_*() function calls then those will work. I’m not sure however what kind of implications this can have for plugins. For example the first query can setup important things and without a decent error check things can really break.

No mysql_*() support

Soon all this functions will get removed from the core of PHP. Maybe already in PHP 5.6 and things will break. Pretty sure a lot of hostings will get this functions back but that isn’t something WordPress can expect. Just like it doesn’t expect that json_encode/json_decode is installed. The problem here is that mysql_query() will return a MySQL resource. This shouldn’t be really an issue since it will be passed into other mysql functions. So this is something that we need to play with.

Conclusion

There is a major progress made to build the next version of WPDB but there still things that need to be checked to give the best user experience we can deliver. We need to check if we can lazy load a MySQL connection if needed and see how we can deliver MySQL support when mysql_*() don’t exists any more. I think in the month this will get a some shape and hopefully it can be added in core really soon. Maybe in WordPress 3.9 or 4.0 what can been seen as a real major version.

One thing I do want to do is contact all the plugin developers on WordPress.org and see if we can reduce the amount of direct calls and move them to WPDB. This will take a lot of work but I think this is something that need to be done. Also curious if we can add a SVN hook that checks code that got committed on the use of mysql_*() functions and can be disabled with sending a mail to the plugins team when the use case is valid.

11 Replies to “The future of WPDB”

  1. Terence -

    Marko,

    Have you been taking notice of the WordPress Pods framework for creating, managing, and deploying customized content types and fields? No? You should. I think you like it.

    Terence.

    Reply

    Marko Heijnen -

    I have and worked with him to improve Tabify edit screen. Not sure how this involves this blog posts.

    Reply

  • Matt -

    What do you think hosting providers are going to do if/when it’s removed? Will they just compile it back in so their customers don’t break?

    Reply

    Marko Heijnen -

    It’s hard to say but I think they are forced to compile it back. The popular systems like WordPress will do their job but the rest probably don’t follow the progress of PHP and see the deprecated messages in PHP 5.5. Till WordPress 3.6 we also didn’t show those messages. So when PHP removes MySQL_*() functions things will break without any warning. So it’s a tricky situation.

    Maybe hosting companies need to scan all their sites and warn users who still using mysql_*() functions. This is something they can start with in a few years when they do support PHP 5.5 them self. But this issue isn’t there yet and maybe doesn’t exists for ten years since hosting companies do move slowly when it comes to PHP updates.

    Reply

  • Jason Burnett -

    I was really hoping this was going to be an article about future plans to encapsulate the data-retrieval methods of Word Press in order to facilitate the customization of database engines. Rather than expecting developers to change their plugins to accommodate new Word Press methods (which will one day become obsolete as well) and rather than describing out a plan for increasing the legacy-burdened structure of Word Press moving forward, it would be nice to see a system by which one day we could use Mongo DB or GoogleSQL (I know it doesn’t exist…yet) or whatever engine works best for your environment. In fact, by encapsulating the data-interchange methods for WP, you could easily handle the deprecation of old SQL methods by implementing an updated SQL-based engine without breaking the data-interchange methods.

    It would be a big task to facilitate the decoupling of Word Press Core and MySQL, but it might be easier to do it now than it will be in five years. Undoubtedly providing backward compatibility for 10 years of Word Press history will be easier than 15.

    Still, I appreciate the information and enjoyed your post. Thanks.
    Jase

    Reply

    Marko Heijnen -

    You need to start somewhere and to me it’s having plugin developers using the current implementation of WPDB and not calling mysql_query() for example since we can’t control that. Also WPDB is some kind of encapsulation. But most likely not something you like or think is good enough ;). Also in general you don’t need to use queries. WordPress has it’s classes and functions you can use but again this is not that kind of encapsulation you probably would expect.

    Did you looked into my plugin “WP DB Drivers”? That one is using drivers and can switch between PDO, MySQLi and MySQL. It’s a try to update the codebase without breaking stuff. If you have another logic in mind I would love to hear that. This doesn’t help when you want to use Mongo DB since there isn’t a driver for that (yet). I do know there is an existing MSSQL plugin that allows you to setup that kind of connections through PDO and does need some query rewriting. So there are ways to do it without breaking things.

    Do note that I haven’t studied computer science so I can be wrong in naming things.

    Reply

    Jan -

    A collegue of my has used the plugin “Supra MongoDB Manager” to connect to a MongoDB database. He has successfully integrated a WordPress site using MySQL with pages showing and updating MongoDB data. We didn’t find any support to exchange MySQL with MongoDB completely.

    Reply

  • Bart -

    I guess it would be a great time to think about implementing something like http://www.doctrine-project.org which would solve some of the near future problems and also give the whole thing the right direction to connect to other datasources like mongodb or so…

    Reply

    Phan mem -

    I think Doctrine is too much for WordPress. We only need a lightweight library for this task. I agree with Marko, the current $wpdb is out of date and it affects website performance.

    Reply

  • Leave a Reply

    Your email address will not be published. Required fields are marked *