Double oops.

The SQL problems a couple of users were reporting are probably not because of they are using an old version of MySQL after all.  As I was wondering if there was an easy way to support users on MySQL 4.0.x, I noticed something I should have seen before — I had hardcoded the database table names I added to the SQL query to obtain the post ids to be excluded from the index.  Elementary mistake — my bad.

Hopefully the new version should work for those of you who had problems on v0.6 and v0.6.1.  If it doesn’t then please let me know which version of MySQL you are using, because users of MySQL 0.4.x will still have problems with the exclude feature.

Oops.  I just installed AZIndex 0.6 on my own blog and then when I tried to publish a new post, got a nasty error message that never appeared in all the testing I did in the past few days.

Fortunately it was obvious what the problem was and I quickly fixed the issue, and hence the new version — 0.6.1. Nothing else has changed from v0.6.

Well, it seems that in the latest version of AZIndex I might have inadvertently used a feature of MySQL that was not introduced until MySQL version 4.1.  When you use the new “exclude categories/tags” feature, the AZIndex plugin constructs a gnarley SQL query that includes something called a “subquery”, and subqueries did not exist in MySQL 4.0.x.

I actually did check to make sure that subqueries were supported in MySQL 4.x, but I didn’t realize that WordPress officially supports all the way back to MySQL 4.0.x.

So… if you find that adding an excluded category or tag to your index causes a nasty SQL error, it is very likely that your server is running a version of MySQL 4.0.x.  Now, MySQL 4.1 was released in October 2004, which is almost four years ago, so I can’t imagine that there are very many web hosts who still use an even older version.  If you are in that unfortunate situation, it is worth checking with your web host to see if they offer the option of upgrading to a newer version of MySQL.  Usually the upgrade process is virtually automatic.  I know that my cheap shared web host provides that option, and I am sure that others do also.

I would rather not have to rewrite the AZIndex code to avoid using subqueries if possible.  The SQL logical is complicated enough without having to deal with even more cases, so I need to know if there are enough users still on MySQL 4.0.x without the option to upgrade to see if it is worth the risk of complexifying (that’s a word, right?) the code logic even more.

Please feel free to chime in.