PDO For WordPress and WP > 2.8

it’s been a while since i have had time to update PDO For WordPress.

i have had a preliminary look at things and i believe that the incompatibility for new installs is based in the is_blog_installed() function. this should be fixable but is non-trivial.

for existing installs, the fix should be trivial (indeed, I am typing here on wp2.9 with PDO For WordPress 2.6.1 – i get some errors but nothing fatal).

i am hoping to have a full fix uploaded in the next week.

29 Comments

piotrostrJanuary 11th, 2010 at 9:23 pm

Hi. I use your briliant plugin on my seo installation with hundreds of blogs. Bassicaly in my routine i a install blog, send about 200 posts, and set as draft planned for later publication :) Everything was more than good, was hilarious :D until i found out that in few blogs after some time im getting that kind of error:
Queries made or created this session were

1. Raw query: UPDATE wp_posts SET post_status = ‘publish’ WHERE ID = 12
2. Rewritten: UPDATE wp_posts SET post_status = ‘publish’ WHERE ID = 12
3. With Placeholders: UPDATE wp_posts SET post_status = ? WHERE ID = 12
4. Prepare: UPDATE wp_posts SET post_status = ? WHERE ID = 12
5. Executing: Array ( [0] => publish )

Error occurred at line 459 in Function executeQuery.
Error message was: Error executing query. Error was was database disk image is malformed

PDO_Engine Object
(
[isError] =>
[foundRowsResult] =>
[initialQuery:private] => UPDATE wp_posts SET post_status = ‘publish’ WHERE ID = 12
[rewrittenQuery:private] => UPDATE wp_posts SET post_status = ‘publish’ WHERE ID = 12
[queryType:private] => update
[rewriteEngine:private] => pdo_sqlite_driver Object
(
[ifStatements:private] => Array
(
)

[startingQuery:private] => UPDATE wp_posts SET post_status = ‘publish’ WHERE ID = 12
[_query] => UPDATE wp_posts SET post_status = ‘publish’ WHERE ID = 12
[dateRewrites:private] => Array
(
)

[queryType] => update
)

[needsPostProcessing:private] => 1
[results:private] => Array
(
)

[pdo:private] => PDO Object
(
)

[preparedQuery:private] => UPDATE wp_posts SET post_status = ? WHERE ID = 12
[extractedVariables:private] => Array
(
[0] => publish
)

[errorMessages:private] => Array
(
)

[errors:private] => Array
(
[0] => Array
(
[line] => 459
[function] => executeQuery
)

[1] => Array
(
[line] => 459
[function] => executeQuery
)

[2] => Array
(
[line] => 459
[function] => executeQuery
)

[3] => Array
(
[line] => 459
[function] => executeQuery
)

[4] => Array
(
[line] => 459
[function] => executeQuery
)

[5] => Array
(
[line] => 459
[function] => executeQuery
)

[6] => Array
(
[line] => 459
[function] => executeQuery
)

[7] => Array
(
[line] => 459
[function] => executeQuery
)

)

[queries] => Array
(
[0] => Raw query: UPDATE wp_posts SET post_status = ‘publish’ WHERE ID = 12
[1] => Rewritten: UPDATE wp_posts SET post_status = ‘publish’ WHERE ID = 12
[2] => With Placeholders: UPDATE wp_posts SET post_status = ? WHERE ID = 12
[3] => Prepare: UPDATE wp_posts SET post_status = ? WHERE ID = 12
[4] => Executing: Array
(
[0] => publish
)

)

[dbType:private] => sqlite
[lastInsertID:private] =>
[affectedRows:private] =>
[columnNames:private] => Array
(
)

[numRows:private] =>
[returnValue:private] =>
[startTime:private] =>
[stopTime:private] =>
[_results] => Array
(
)

[statement] => PDOStatement Object
(
[queryString] => UPDATE wp_posts SET post_status = ? WHERE ID = 12
)

)

Do u have any idea how to get this fixed or prevent it in any way in the future?
Thanks a lot for great plugin and ur work
Sincerly

JustinJanuary 11th, 2010 at 10:27 pm

Hi
you should try running a vacuum on the database. from the reported error your database has become corrupted due to a file-system problem. potentially a power cut or server crash could cause this.

piotrostrJanuary 12th, 2010 at 8:25 pm

Hi.
Thanks Justin im gonna try this and then let u know is that work, for future readers.

JustinJanuary 14th, 2010 at 1:37 pm

PDO For WordPress 2.7.0 has now been uploaded to WP.org. It is (should be) compatible with 2.9.1 and below.

RequiredJanuary 19th, 2010 at 11:53 pm

Hello. Sorry for my english)) I find a small, but very bad bug. When WP working with sqlite, dont showing navigation in categories and tags pages. May be my information will be interesting. If look result of query in wp-includes\query.php on line 2276
$this->found_posts = $wpdb->get_var( $found_posts_query );
in mysql version showing really posts amount, but in sqlite version only one. May be this trouble will fixed in next versions of plugin?

JustinJanuary 20th, 2010 at 9:25 am

I will take a look. I am assuming that you are using version 2.7.0 of the plugin.
the latest version of wordpress starts using a lot of mysql specific functions; which is a terrible idea for portability. I have also found other bugs that require fixing (for example with role_scoper).
can you send your wp-content/database/debug.txt file to me to check the offending query? best if you delete the file, run the commands that you think produce the wrong results, and then send the newly generated file through. jpadie@gmail.com.

D_mannJanuary 22nd, 2010 at 12:56 am

I am having a couple of issues with the plugin, one which I reported with the earlier version. If you have the characters “)”, without the “” around the ) in a post it can not be edited. The error is – “Fatal error: Maximum execution time of 30 seconds exceeded in ***\***\blog\wp-includes\plugin.php on line 302 Importing articles that have the “)”, in the post do not import either. Also, the links section does not work correctly. The edit links section does not show any links. Links can be added, but they do not appear. The WP install is 2.8.5 and the pdo plugin was upgraded from 2.6.1. Thanks for the plugin and all of your work.

D_mannJanuary 22nd, 2010 at 1:00 am

As a note, if I post a comment on this site with “)”, without the “” in the comment I get the following error – “Error 404 – Not Found” from the link http://rathercurious.net/comment-page-#comment-

JustinJanuary 22nd, 2010 at 11:31 am

you are right. I noticed this in a recent post myself just after I upgraded to wp2.9.1 + plugin 2.7.0. i am part way through a fix but actually it’s more complex than it should be … because of recursion, you can’t use pattern matching to extract variables from queries. i am working on a parser method instead. not straightforward at all.

ChrisFebruary 3rd, 2010 at 8:43 pm

I can’t seem to get this to work on my server. The error I receive is:

Your PHP installation appears to be missing the PDO extension which is required for this version of WordPress.

This is supposed to make wordpress work even though my server doesn’t have MySQL, right?

JustinFebruary 4th, 2010 at 6:56 am

Hi Chris
You are correct that with this plugin you are able to use wordpress on a server installation that does not have MySQL.
Of course you still need PDO and the PDO_SQLITE extensions.
PDO is an abstraction layer that is shipped with PHP and allows PHP to talk to a bunch of different databases using the same commands.
PDO_SQLITE is the library that allows PDO to talk to SQLite databases. Similar ‘drivers’ exist for many other databases.
You should check your phpinfo() and your server to determine whether PDO and PDO_SQLITE are installed (but from the error message it seems that they are not). Your server admin should be able to do the necessary for you.

Joseph Mark JarvisFebruary 11th, 2010 at 12:54 am

Justin,

1. Thank you for the hard work. Great plugin.

2. New WP 2.9.1 + PDO 2.7 setup. Receive following error during WP installation:

http://example.com/wp-admin/setup-config.php?step=2
Invalid or missing PHP Extensions.
Your PHP installation appears to be missing the PDO extension which is required for this version of WordPress.

Troubleshooting:
php -v reports PHP version 5.2.12.
php -m reports PDO and PDO_SQLITE modules installed.
db.php and pdo/ are in wp-content
added define(‘DB_TYPE’, ‘sqlite’); to wp-config-sample.php
wp-content is chmod -R 777

What am I missing? Thanks.

twFebruary 17th, 2010 at 7:58 am

Like this plugin.
Could you answer a few questions please?

1. I think running wordpress on sqlite is faster than mysql, at least for small to medium sites. Is it correct?

2. Right now I am using some plugins that shows in the widgets the “most viewed posts”, “related posts” etc. However, after using sqlite backend, these functions stopped working. Is there any way to fix it? For example, this one: http://wordpress.org/extend/plugins/efficient-related-posts/

JustinFebruary 18th, 2010 at 2:31 pm

@Joseph

Hi Joseph
this message means that you have not loaded the relevant PDO extensions in your installation of php. check out the manual at php.net/pdo for the installation steps.

JustinFebruary 18th, 2010 at 2:43 pm

@tw

1. sqlite is not great in multi-access environments. and a full answer to your question would need to take account of the latency between your mysql server and your webserver.
however, a low-medium traffic site should do as well using sqlite if not better than mysql
but this is not the case with my extension and WP because you have to take into account the overhead of recasting the queries to sqlite lingo rather than mysql. this will almost certainly render the site slower. sqlite does some funky cacheing though. the first page load might take a while but subsequent pages should be pretty zippy.

2. if a plugin does not use the wp-db abstraction layer then my plugin will not be able to handle it. it’s bad coding on the part of the plugin author (and by bad I mean non-standards compliant rather than anything qualitative).
I do not know what the specific issue might be with this plugin. a brief glance at the code suggests that it does use the abstraction layer when needed. are you getting error messages?

bvandrunenFebruary 19th, 2010 at 12:50 am

I am sort of newish at the whole database system…However I am trying to move from mySQL -> Microsoft SQL server and found this plugin. I have gotten it to the point where it asks me to install it. I put my “blog title” etc and it says that it installed correctly and give me the “password” and when I click “login” with my new auto-generated pwd and “admin” it goes back to the “install.php” screen. It is an endless loop of installs and re-installs.

I have followed every tip I have seen so far. I am wondering if I have just missed something simple. I have changed all the permissions etc. Any help would be greatly appreciated.

Thanks for the plugin so far.

JustinFebruary 19th, 2010 at 12:25 pm

Hi
if i follow your comment correctly, you are trying to use WP with an MS sql server back end. whilst it is technically possible to use PDO with MS Sql server, you will need to create a driver for use with the PDO For WordPress plugin, that supports MS SQL Server. I have not developed one. I have a version that a user of my plugin has developed but I have never tested it so cannot vouchsafe that it works.

bvandrunenFebruary 19th, 2010 at 5:16 pm

Thank you for your response…do you have a link to the other users plugin? I would love to try it out…we have a dev server and I would love to get my head around this. Thanks

JustinFebruary 19th, 2010 at 7:47 pm

I don’t think that he ever published it.
it was not a plugin as such but a first stab of an MS Sql driver for my plugin.

unless you are a database guru, i would counsel against MS Sql. Apart from the fact that it is terrifyingly expensive and needs a heavyweight server to power it, why would you want it to power a simple blog?

if you can run mysql then i’d stay with it. if you want the benefits of an encapsulated system like sqlite, then most probably your php installation already supports it. all you need to do is set the DB_TYPE definition to ‘sqlite’ and everything should work.

i keep promising to have a go porting to mssql but inertia always defeats me. I don’t have a windows system to test on, nor do I have ms sql (although i guess i could use sql express or whatever the free version is called). there are several inconsistencies with sql ansi that would annoy me and, regrettably, the client libraries for php< ->mssql are really not great. very buggy with big fields. could get around it, perhaps, with odbc but then the odbc client libraries are hardly paragons of virtue themselves…

looking forward to hearing why you want ms sql.

bvandrunenFebruary 19th, 2010 at 9:19 pm

Well here is the situation, hopefully you can understand it: Right now I am working on automating wordpress -> meaning when I click “install” in our software it runs a script which buys a domain, installs wordpress, sets up database and sets up our customization. Right now it works great and we use a XML feed to implement our data for each site for our customers.

As of right now we are using an XML website that was created (not on wordpress) But we would like to use wordpress because it will allow our customers to create blogs for themselves if they like. We get about 20-30 new customers a day.

This is where the problem comes in: the database we use right now for all of our customers is based in MS SQL and we have been running it for 6-7 years so we aren’t about to change it. But we want to use all of our variable such as (@businessname etc) for our database instead of XML feeds – faster, easier to maintain etc.

Therefore the biggest reason I want to get wordpress on MS SQL is so that we can use our current database with wordpress without having to move it to MYSQL (which won’t happen). We also would rather use a database instead of XML feeds.

Hopefully this helps you out. Currently my system works but we have to maintain 2 databases (MYSQL and MS SQL) and have to use XML feeds instead of database variables. Any suggestions on how to make this situation would be greatly appreciated. Thanks

Joseph Mark JarvisFebruary 23rd, 2010 at 3:20 am

@Justin

* Which are the relevant PDO extensions?
* How does one verify they’re loaded?

Thanks.

JustinFebruary 23rd, 2010 at 7:36 am

Joseph
in order to use PDO_SQLITE you need the pdo library and the pdo_sqlite library.
different platforms load these in different manners as is normal for php.
You can find more about them here: http://fr.php.net/manual/en/pdo.installation.php

you can verify whether they are loaded by examining the output of phpinfo()

Joseph Mark JarvisFebruary 26th, 2010 at 11:24 pm

@Justin

phpinfo() reports pdo and pdo_sqlite are loaded:

PDO
PDO support: enabled
PDO drivers: sqlite, mysql, sqlite2

pdo_sqlite
PDO Driver for SQLite 3.x: enabled
PECL Module version (bundled) 1.0.1 $Id: pdo_sqlite.c 272374 2008-12-31 11:17:49Z sebastian $
SQLite Library: 3.3.7

KarelNovember 12th, 2010 at 2:08 pm

Hi Justin,

I love SQLite and i’m using your fantastic plugin with WP 3.0.1. It seems to work fine but there are errors appearing in my error_log… Here is what happened:

1. I uploaded WP 3.0.1 + PDO driver
2. Run install and during install errors appeared in error_log
(I would like to post the errors but I think it will be to much text maybe I can email it to you?)
3. Install completed…
4. Check email, login to WP and more errors appeared in error_log

In debug.txt appeared the following:
Line 431, Function: prepareQuery, Message: Problem preparing the PDO SQL Statement. Error was no such table: modTimes
Line 431, Function: prepareQuery, Message: Problem preparing the PDO SQL Statement. Error was no such table: wp_options
Line 431, Function: prepareQuery, Message: Problem preparing the PDO SQL Statement. Error was no such column: FALSE
Line 473, Function: executeQuery, Message: Error executing query. Error was was bind or column index out of range
Line 431, Function: prepareQuery, Message: Problem preparing the PDO SQL Statement. Error was near “7″: syntax error
Line 473, Function: executeQuery, Message: Error executing query. Error was was bind or column index out of range

I tried the following suggestion but errors keep comming…
http://wordpress.org/support/topic/plugin-pdo-sqlite-for-wordpress-fixed-a-small-bug-in-the-plugin-appeared-under-wordpress-301

WP 3.0.1. works fine (as far I can see) Every time I press the “dashboard” button the page is displayed correctly but many errors keep appearing in the error_log

Any idea how to resolve this problem?

Kind regards,
Karel

Eric PNovember 25th, 2010 at 2:35 pm

Love, Love, Love PDO For WordPress and using SQLite. We have set up a system whereby we can create brand new sites with just one click of a button. That site can be zipped and deployed anywhere in minutes (with one click). Awesome!!

We just upgraded WP from 2.9.x to 3.0.1 running with SQLite. Amazingly, the upgrade (using the WordPress Automatic upgrade) went off “nearly” flawlessly. Coolness!

But, there was one minor problem:

We go the error : Warning: implode() [function.implode]: Invalid arguments passed in ../wp-includes/post.php on line 2558 (when editing pages).

Thankfully a quick Google search provided a link to the site of Christoph Ruttkies, who had a quick fix for this bug/issue here:

http://users.informatik.uni-halle.de/~ruttkies/RforRocks/2010/10/pdo-sqlite-bug-in-wordpress-6/

Summary of his fix:

In this file:

wp-content/pdo/db.php

Change this:


function escape($string) {
return addslashes($string);
}

To this:

function escape( $data ) {
if ( is_array( $data ) ) {
foreach ( (array) $data as $k => $v ) {
if ( is_array( $v ) )
$data[$k] = $this->escape( $v );
else
$data[$k] = $this->_weak_escape( $v );
}
} else {
$data = $this->_weak_escape( $data );
}
return $data;
}

He does a good job of explaining how he found the issue, and the explanation of the fix.

So, now we are running WordPress 3.0.1 with SQLite. It took all of 5 minutes to upgrade from 2.9.x to 3.0.1. So far, so good!

adminNovember 25th, 2010 at 2:40 pm

@Eric P: thanks for the comments.

in fact there are quite a few inconsistencies between WP 3+ and my plugin. date handling etc.
I have been trying to find time to fix it up but …

the other slight issue is that WP Core are starting to use a number of mysql specific functions (such as concat) which will be difficult to work around.

adminNovember 25th, 2010 at 2:42 pm

@Karel:
it looks like the table creation routines failed.
the easiest workaround is for you to create the tables manually and then upload the created database.
i am trying to find time to work on PDO for wordpress and the SQLite driver but it’s not easy!

BertrandJune 28th, 2011 at 5:59 pm

‘llo Justin …
I thinj there is a problem with backtick in post.
first i think it was a wp-syntax but on my test blog wp3.1.3 without pdo its’ok
on my main blog 2.9.2 with pdo and the same plugins the backtick disappear

is it an escaping problem ?

NoraJuly 18th, 2011 at 7:31 am

Hi Justin,

I’m a little bit desperate trying to install your plugin. I was trying to get some help at the WordPress forum but nobody was answering me so I hope you could help me with that. I’m not good with SQL or PHP I just want to install WordPress on a webspace where I unfortunately have not much influence. That means I can not really see any php logs so I can not pin down the problem. Here is my issue:

I installed the PDO plugin und modifies the wp-config.php and when I run the wp-admin/install.php I get the normal first page with the Blog name and the Passwort etc. But when I then continue the next page only shows code. Something like that:

installing the database
$wp_queries = “CREATE TABLE $wpdb->terms (
term_id bigint(20) unsigned NOT NULL auto_increment,
name varchar(200) NOT NULL default ”,
slug varchar(200) NOT NULL default ”,
term_group bigint(10) NOT NULL default 0,
PRIMARY KEY (term_id),
UNIQUE KEY slug (slug),
KEY name (name)
) $charset_collate;
CREATE TABLE $wpdb->term_taxonomy (
term_taxonomy_id bigint(20) unsigned NOT NULL auto_increment,

I have a php 5.1.6 I think but I’m using older WordPress versions which should be compatible with this. (I tried 3.1.2 and 3.0.2 and non of them worked). Concluding from the first sentence I would guess it has something to do with the plugin trying to write the database and then messing something up in the php code but I have no clue.

Can you help me? That would be very very nice of you.
Thank you.

Nora

Leave a comment

Your comment