PDOException: SQLSTATE[42S22], where? [#1213672]

After recent upgrade to new dev, I am getting the following error anytime the biblio listing is visited. Note that all the entries look fine, it just appears in the error log:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'BKD.WORD' in 'where clause': SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {node} n LEFT OUTER JOIN {biblio} b ON n.vid=b.vid INNER JOIN {biblio_types} bt ON b.biblio_type=bt.tid INNER JOIN {biblio_keyword} bk ON n.vid = bk.vid INNER JOIN {biblio_keyword_data} bkd ON bkd.kid = bk.kid INNER JOIN {biblio_contributor} bc ON n.vid = bc.vid WHERE ( SUBSTR(BKD.WORD, 1, 1) = :db_condition_placeholder_0) AND( (bc.cid = :db_condition_placeholder_1) )) subquery; Array ( [:db_condition_placeholder_0] => N [:db_condition_placeholder_1] => 1 ) in PagerDefault->execute() (line 74 of /home/otsameri/public_html/includes/pager.inc).

The solution usually seems to disable, uninstall and reinstall, but I really don't want to lose all my biblio tables (I suppose I can back them up). But is there another solution?

Comments

Comment #1

rjerome commented 9 July 2011 at 11:58

That is strange, I've tested the query on my system and it works fine so the only situation I can see it not working is if the biblio_keyword_data tables does not exist on your system.

By the way, are you using "Views" to generate the page the emits this error? It would appear that you are filtering on keywords that start with N and the author whose cid=1?

Comment #2

mariagwyn commented 10 July 2011 at 03:46

This is actually very odd.

1. biblio_keyword_table exists and has data (columns: kid, word)
2. I am using the default page. Here is the URL which generates the error:
http://otsamerica.org/publications/all?page=1&f%5Bkeyword%5D=Y&f%5Bauthor%5D=1

And this is where it gets strange:
1. Notice that the page=1, which if I recall drupal paging, means that this is actually the 2ND page. However, I have a whopping 6 entries, there is no second page.
2. I also have no filter or url that would create both a keyword and author search.

Yet the error is frequently generated by an anonymous user. It was being generated when I (as admin) visited the page, but now it is not...

okay, modification:
1. I can generate the error if I filter on keywords, and then click a letter that has no corresponding keyword. It is not pretty what happens.
2. The six entries have many keywords. The keyword page however, groups to three key words. Also, the "Export X results" jumps from 6 to 20.

I am totally mystified.

Comment #3

rjerome commented 11 July 2011 at 02:25

Ahh, I see one problem. The links in the "alpha line" (ABC...) are not correct, and I just pushed (http://drupalcode.org/project/biblio.git/commit/3edfb01) a fix for that.

I'm guessing that a web crawler (such a Google) might be crawling your site and generating these strange combinations of filters by blindly following links on all the pages.

Comment #4

mariagwyn commented 11 July 2011 at 21:02

great. now, to reveal my ignorance, how do I get that particular commit? by this:

git clone --branch 7.x-1.x http://git.drupal.org/project/biblio.git
cd biblio

I am still getting the hang of pulling from git (I do like it, fast) and making sure I really have the right one (the whole branch thing makes my head spin).

Comment #5

rjerome commented 11 July 2011 at 23:17

Actually that should be in the latest -dev package on the project page. The -dev packages get built from the source repository every 12 hours, and given I pushed that almost 24 hours ago, it's bound to be there. I include those links to the commits in the issues more for my own reference, so if there is a problem in a few days, I will know exactly what I did to create it :-)

Comment #6

mariagwyn commented 12 July 2011 at 23:18

I am pretty sure I updated correctly, still gittin' the error:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'BKD.WORD' in 'where clause': SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {node} n LEFT OUTER JOIN {biblio} b ON n.vid=b.vid INNER JOIN {biblio_types} bt ON b.biblio_type=bt.tid INNER JOIN {biblio_keyword} bk ON n.vid = bk.vid INNER JOIN {biblio_keyword_data} bkd ON bkd.kid = bk.kid INNER JOIN {biblio_contributor} bc ON n.vid = bc.vid WHERE ( SUBSTR(BKD.WORD, 1, 1) = :db_condition_placeholder_0) AND( (bc.cid = :db_condition_placeholder_1) )) subquery; Array ( [:db_condition_placeholder_0] => S [:db_condition_placeholder_1] => 3 ) in PagerDefault->execute() (line 74 of /home/local/public_html/includes/pager.inc).

Comment #7

rjerome commented 13 July 2011 at 02:03

Are you seeing these when you are actually navigating the site, or are they just turning up in the logs?

I can't replicate the issue on my end, so it's a bit more difficult to debug :-( I have tried entering a query (which I know has non-existent values) and it still doesn't generate an error like that.

Have you tried clearing your (Drupal) cache?

Could you give a bit more detail about your setup (OS, Web Server, PHP, Database engine).

Comment #8

mariagwyn commented 13 July 2011 at 17:12

I am seeing them ONLY in the logs. I assume. Thus far, they appear solely with "Guest" visits the page, though given the newness of the site, that might not remain true.

I am on:
Apache version 2.2.19
PHP version 5.3.6
MySQL version 5.1.57-rel12.8-log
Architecture x86_64
Operating system linux

Does that help? I am trying to see if I am getting errors via the server logs, but not finding a file right now. I just don't want to overload my server.

Comment #9

rjerome commented 13 July 2011 at 17:56

Nothing unusual there.

Did you flush the cache on the 'admin/config/development/performance' page?

You could do a reverse lookup on the ip address that's listed in the log to see if it's a web crawler.

Comment #10

mariagwyn commented 13 July 2011 at 19:14

yup, baidu inc. sigh.

Just flushed the cache though I think I have done that. I will get back to you if it appears.

Comment #11

mariagwyn commented 21 July 2011 at 00:46

Okay, this error is appearing all the time. Always anonymous, probably a bot, but I sure would like to stop having my error queue filled up with this error (aside from the load).

Anything else I can explore? More details to give?

Comment #12

rjerome commented 21 July 2011 at 03:17

Could post a few more of the most recent error entries in your logs so I can see what the requested URL looks like.

Comment #13

mariagwyn commented 21 July 2011 at 15:23

With pleasure. I get a similar error every 6-9 minutes, like crazy clockwork.
Some example links:

It just looks to me like they are going through the alphabet. But the site is very new so there just aren't that many publications submitted yet.
Here is a full error table:

Type	php
Date	Thursday, 21 July 2011 - 8:14am
User	Guest (not verified)
Location	http://otsamerica.org/publications/all?f%5Bkeyword%5D=Y&f%5Bauthor%5D=5
Referrer
Message	PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'BKD.WORD' in 'where clause': SELECT COUNT() AS expression FROM (SELECT 1 AS expression FROM {node} n LEFT OUTER JOIN {biblio} b ON n.vid=b.vid INNER JOIN {biblio_types} bt ON b.biblio_type=bt.tid INNER JOIN {biblio_keyword} bk ON n.vid = bk.vid INNER JOIN {biblio_keyword_data} bkd ON bkd.kid = bk.kid INNER JOIN {biblio_contributor} bc ON n.vid = bc.vid WHERE ( SUBSTR(BKD.WORD, 1, 1) = :db_condition_placeholder_0) AND( (bc.cid = :db_condition_placeholder_1) )) subquery; Array ( [:db_condition_placeholder_0] => Y [:db_condition_placeholder_1] => 5 ) in PagerDefault->execute()* (line 74 of /home/otsameri/public_html/includes/pager.inc).
Severity	error
Hostname	66.249.71.89
Operations

Thanks,
Maria

Comment #14

rjerome commented 22 July 2011 at 01:52

This is quite strange... I was just on your site, and clearly you have keywords that start with the letter "A" (http://otsamerica.org/publications/all/keywords?f[keyword]=A), so

http://otsamerica.org/publications/all?f[keyword]=A

should generate a listing of the entries which have keywords starting with "A" , but it generates an error :-( Could you visit the above mentioned URL and tell me what error it's generating?

Ron.

Comment #15

mariagwyn commented 22 July 2011 at 19:29

This is the error:

http://otsamerica.org/publications/all?f[keyword]=A

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'BKD.WORD' in 'where clause': SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {node} n LEFT OUTER JOIN {biblio} b ON n.vid=b.vid INNER JOIN {biblio_types} bt ON b.biblio_type=bt.tid INNER JOIN {biblio_keyword} bk ON n.vid = bk.vid INNER JOIN {biblio_keyword_data} bkd ON bkd.kid = bk.kid WHERE ( SUBSTR(BKD.WORD, 1, 1) = :db_condition_placeholder_0) ) subquery; Array ( [:db_condition_placeholder_0] => A ) in PagerDefault->execute() (line 74 of /home/otsameri/public_html/includes/pager.inc).

Comment #16

mariagwyn commented 22 July 2011 at 19:30

When I click through to the keyword "a" I get this error:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'BKD.WORD' in 'where clause': SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {node} n LEFT OUTER JOIN {biblio} b ON n.vid=b.vid INNER JOIN {biblio_types} bt ON b.biblio_type=bt.tid INNER JOIN {biblio_keyword} bk ON b.vid = bk.vid INNER JOIN {biblio_keyword_data} bkd ON bk.kid = bkd.kid WHERE ( SUBSTR(BKD.WORD, 1, 1) = :db_condition_placeholder_0) ) subquery; Array ( [:db_condition_placeholder_0] => A ) in PagerDefault->execute() (line 74 of /home/otsameri/public_html/includes/pager.inc).

Comment #17

rjerome commented 22 July 2011 at 19:53

This is very strange for a number of reasons,
1) The bkd table is joined in the query so the bkd.word column should exist
2) I've never seen this on my systems, even if I specify a keyword category which I know doesn't exist.

If you search Drupal.org for that error (PDOException: SQLSTATE[42S22]: Column not found: 1054) you will get a lot of hits, so I have a feeling there is an issue with the way the pager query builds it's query, but I will keep looking...

Comment #18

mariagwyn commented 22 July 2011 at 22:49

ok. keep me posted. I just only get it on biblio, so I started here rather than all those other hits, which I noticed. I wonder if the problem is because there isn't a second page? I mean, I don't have enough entries right now for a second page.

Thanks for any help you have. I am going away for a week, no internet, so no hurry.

Maria

Comment #19

WhiplashInfo commented 13 August 2011 at 10:48

Version:	7.x-1.x-dev	» 7.x-1.0-beta3
Priority:	Normal	» Critical

Well - I runned into this problem also.

The problem is so sever, I can't practiycally do nonthing at the site, due to this error. Deactivating the Biblio module fix the issue.

This is my unstalled modules:
-------------------------------------------------------------
Drupal core

No available releases found
drupal 7.7
Includes: Bartik, Block, Blog, Color, Comment, Contextual links, Dashboard, Database logging, Field, Field SQL storage, Field UI, File, Filter, Forum, Help, Image, List, Locale, Menu, Node, Number, Options, Overlay, Path, RDF, Search, Seven, Shortcut, System, Taxonomy, Text, Toolbar, Update manager, User
Modules

No available releases found
admin_menu 7.x-3.0-rc1
Includes: Administration Development tools, Administration menu, Administration menu Toolbar style, Administration views
No available releases found
advanced_forum 7.x-2.0-alpha3
Includes: Advanced Forum
No available releases found
biblio 7.x-1.0-beta3
Includes: Biblio, Biblio - EndNote Tagged, Biblio - EndNote XML, Biblio - RTF
No available releases found
cck 7.x-2.x-dev (2011-Jul-30)
Includes: CCK
No available releases found
context 7.x-3.0-beta1
Includes: Context, Context UI, Context layouts
No available releases found
ctools 7.x-1.0-rc1
Includes: Bulk Export, Chaos tools, Custom content panes, Custom rulesets, Page manager, Stylizer, Views content panes
Up to date
Entity API 7.x-1.0-beta10
Includes: Entity API, Entity tokens
Up to date
IMCE 7.x-1.x-dev (2011-Jul-30)
Recommended version: 7.x-1.4 (2011-Jun-06)
Download
Release notes
Includes: IMCE
Up to date
Localization update 7.x-1.0-beta2
Includes: Localization update
Up to date
Pathauto 7.x-1.0-rc2
Includes: Pathauto
Up to date
Token 7.x-1.0-beta3
Includes: Token
Up to date
Views 7.x-3.0-rc1
Includes: Views, Views UI
Up to date
Views Bulk Operations (VBO) 7.x-3.0-beta1
Includes: Actions permissions, Views Bulk Operations
No available releases found
wysiwyg 7.x-2.0
Includes: Wysiwyg

Error
Error messagePDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'n.type' in 'where clause': SELECT COUNT(*) AS expression FROM {comment} c WHERE (c.nid = :db_condition_placeholder_0) AND (c.status = :db_condition_placeholder_1) AND (n.type <> :db_condition_placeholder_2) ; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => 1 [:db_condition_placeholder_2] => biblio ) in PagerDefault->execute() (line 74 of /home/myaccount/domains/mywebb/public_html/d/includes/pager.inc).
The website encountered an unexpected error. Please try again later.
-------------------------------------------------------------
I have this error showned within every dispaled side or page.
Here is a copy from my log:

-------------------------------------------------------------
TYPE
DATE
MESSAGE
USER
OPERATIONS
php 08/13/2011 - 08:58 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/13/2011 - 08:57 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/13/2011 - 08:57 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/13/2011 - 08:57 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/13/2011 - 08:56 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/13/2011 - 08:55 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
file 08/13/2011 - 08:55 The file public://ctools/css was not deleted, because... admin
php 08/13/2011 - 08:55 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/13/2011 - 08:50 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/13/2011 - 08:50 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/13/2011 - 08:50 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/13/2011 - 08:50 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/13/2011 - 08:50 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/13/2011 - 08:49 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/12/2011 - 18:48 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
file 08/12/2011 - 18:48 The file public://ctools/css was not deleted, because... admin
php 08/12/2011 - 18:46 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/12/2011 - 18:45 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/12/2011 - 18:45 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/12/2011 - 18:44 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/12/2011 - 18:44 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
locale 08/12/2011 - 18:44 Updated JavaScript translation file for the language... admin
php 08/12/2011 - 18:44 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/12/2011 - 18:44 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/12/2011 - 18:44 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/12/2011 - 18:43 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
php 08/12/2011 - 18:37 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
system 08/12/2011 - 13:15 wysiwyg module enabled. admin
system 08/12/2011 - 13:15 wysiwyg module installed. admin
php 08/12/2011 - 13:14 Notice: Undefined variable: finished in _batch... admin
php 08/12/2011 - 13:08 PDOException: SQLSTATE[42S22]: Column not found: 1054... Anonymous (not verified)
content 08/12/2011 - 13:08 page: updated Om Försäkringsmedicinska kollegiet. admin visa
content 08/12/2011 - 13:08 page: updated Om Försäkringsmedicinska kollegiet. admin visa
locale 08/12/2011 - 13:07 Updated JavaScript translation file for the language... admin
locale 08/12/2011 - 13:07 Updated JavaScript translation file for the language... admin
content 08/12/2011 - 13:07 page: added Om Försäkringsmedicinska kollegiet. admin visa
locale 08/12/2011 - 13:05 Updated JavaScript translation file for the language... admin
cron 08/12/2011 - 12:26 Cron run completed. Anonymous (not verified)
cron 08/12/2011 - 12:12 Cron run completed. Anonymous (not verified)
cron 08/12/2011 - 12:11 Cron run completed. Anonymous (not verified)
cron 08/12/2011 - 12:11 Cron run completed. Anonymous (not verified)
locale 08/12/2011 - 12:10 Imported /tmp/translation-s4mIN8 into sv: 83 new... admin
l10n_update 08/12/2011 - 12:10 Successfully downloaded http://ftp.drupal.org/files... admin
-------------------------------------------------------------

Thanks / Tomas

Comment #20

rjerome commented 14 August 2011 at 14:33

Strangely, that query isn't even a "biblio" query it's generated by the "comment" module and it's looking for comments on "biblio" node types. It would go away when you disabled the biblio module because the "biblio" node type would no longer be searched for.

I think you will find that a search of Drupal.org with "PDOException: SQLSTATE[42S22]" will yield results spanning a broad spectrum of modules, so (and I'm not trying to shift the blame here) I think there may be a deeper problem with D7.

Comment #21

WhiplashInfo commented 14 August 2011 at 16:46

Well - you could be correct. Look at this thread (issue):
http://drupal.org/node/1248254

I you can see I had the same problem with the module "User Revision"

Comment #22

rjerome commented 16 August 2011 at 03:25

Status:

Active

» Fixed

My bad, this was my fault. It is fixed in -beta4

Comment #23

mariagwyn commented 18 August 2011 at 15:19

I upgraded to beta4 yesterday. I am still receiving the error, though it appears somewhat less frequently. However, I am getting MANY of this error:

Notice: Undefined variable: output in biblio_handler_citation->render() (line 71 of /home/site/public_html/sites/all/modules/biblio/views/biblio_handler_citation.inc).

Comment #24

rjerome commented 20 August 2011 at 13:44

This issue has turned into multiple issues, but to clarify, the fix referred to in #22 fixes the issue in #19, not necessarily the original issue (which I think is different).

I have also fixed (http://drupalcode.org/project/biblio.git/commit/7cee200) the "views" issue mentioned in #23 (which is also not related to the original issue).