[Original] Solutions to PostgreSQL database problems using WordPress3.9 _ MySQL

[Original] WordPress 3.9 solutions to PostgreSQL database problems

I have never liked mysql. I have mentioned this many times on my blog. Some of the wordpress instances I deployed use the postgresql database.

The biggest advantage of replacing mysql with pgsql is that it saves a lot of resources. Even if mysql only uses the MyISAM engine, the memory occupied is much larger than the standard pgsql-when running the same WP application. Not to mention, pgsql provides more functions than mysql's InnoDB-though not available in WP.

However, because WP directly uses mysql in the code, a plug-in is required for database replacement: PG4WP. However, its latest version is 1.3.0 two years ago, which is quite OUT, but there is no way, there is no other choice.

The implementation principle of this plug-in is: dynamically replace the content of the wp-db.php, replace the mysql API with pgsql API (actually replace with a set of intermediate API, then you can choose to use mysql or pgsql through configuration), and finally run the replaced wp-db.php through eval.

Eval ...... This ...... It is indeed simple and crude, but it is effective directly, and the performance is basically not affected (pgsql's performance advantage on mysql is enough to compensate for this performance loss of PHP ).

However, after updating WordPress 3.9 to the latest version yesterday, I found that the website could not be opened. I checked the log and found the following error:

2014/05/04 22:16:22 [error] .... FastCGI sent in stderr: "PHP message: PHP Warning: Missing argument 3 for wpsql_result(), called in /.../wp-content/pg4wp/core.php(32) : eval()'d code on line 755 and defined in /../wp-content/pg4wp/driver_pgsql.php on line 58PHP message: PHP Warning: pg_fetch_result() expects parameter 1 to be resource, boolean given in /.../wp-content/pg4wp/driver_pgsql.php on line 59PHP message: PHP Fatal error: Call to undefined function wpsql_errno() in /.../wp-content/pg4wp/core.php(32) : eval()'d code on line 1531" while reading response header from upstream, client: xx.xx.xx.xx, server: xxxxxx, request: "GET / HTTP/1.1", upstream: "xxxxx", host: "xxxxxx"

There are two basic reasons:

1. the parameters that call mysql_result () in the wp-db.php are inconsistent with the pg_fetch_result () requirement (wpsql_result is one of the intermediate API functions of pg4wp packaging), the specific 755th rows in the wp-db.php, only two parameters are used here, but wpsql_result requires three parameters, and wpsql_result does not process the case where $ result is FALSE.

2. the wp-db.php calls mysql_errno (), but the intermediate API of pg4wp package does not implement this function, the specific 1,531st rows in the wp-db.php.

Find the solution and find the two posts on the plug-in Forum:

Http://wordpress.org/support/topic/not-working-with-39-44
Http://wordpress.org/support/topic/not-working-with-39

The first post is a good solution to the first problem, but the second post is not a good solution to the second problem. Because this method is to directly modify the wp-db.php, if the WP upgrade later, this modification will be overwritten, so it is better to modify in pg4wp.

The final solution is to find the wpsql_result function in driver_pgsql.php, and then modify it as follows:

function wpsql_result($result, $i, $fieldname = null) {if (is_resource($result)) {if ($fieldname) {return pg_fetch_result($result, $i, $fieldname);} else {return pg_fetch_result($result, $i);}}}function wpsql_errno($conn) {if (pg_last_error===FALSE) {return 0;} else {return -1;}}

No error occurred when saving and accessing the website again.

The traditional mysql API has been identified as obsolete and cannot be used since PHP5.5, but the latest WordPress version 3.9 is still bound to mysql. Although it is said that it has been replaced with PDO, and only uses the old API without PDO. In addition, in earlier versions of WP that do not support PDO, you can also use the wp db Driver plug-in to support PHP5.5. However, this is not a long-term plan, and this product only supports PDO-mysql and mysqli, PDO cannot be used to drive other databases (such as SQLite or pgsql ).

According to WP's official statement 10 years ago, Using Alternative Databases does not consider Databases other than mysql because it has different SQL syntax except interfaces. However, the problem is that we didn't see any advanced mysql technology used by WP. The simple and crude method of pg4wp can be solved. The only reason we can think of is:

Compatibility with plug-ins and other databases cannot be guaranteed.