Hi,

My apologies if this is a known issue; I had a look but was unable to spot any obvious issues covering this (there are a lot though!)

I upgraded yesterday to 6.x-2.8 from the previous version, presumably 2.7, and started seeing errors on every page, with my custom view having vanished too.

The error message is as follows:

-snip-
user warning: Unknown column 'node_book.bid' in 'on clause' query: SELECT node.nid AS nid, node_revisions.teaser AS node_revisions_teaser, node_revisions.format AS node_revisions_format FROM node node LEFT JOIN book book ON node.nid = book.nid INNER JOIN node node_book ON book.bid = node_book.bid LEFT JOIN menu_links book_menu_links ON book.mlid = book_menu_links.mlid LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE (node.promote <> 0) AND (node.status <> 0) AND (book_menu_links.depth > 1) LIMIT 0, 1 in /drupal/sites/all/modules/views/includes/view.inc on line 765.
-snip-

The exported view is as follows:

-snip-
$view = new view;
$view->name = 'featuredcasestudy';
$view->description = 'Featured Case Study';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('relationships', array(
'bid' => array(
'label' => 'Book',
'required' => 0,
'id' => 'bid',
'table' => 'book',
'field' => 'bid',
'relationship' => 'none',
),
));
$handler->override_option('fields', array(
'view_node' => array(
'label' => 'Link',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'text' => '',
'exclude' => 0,
'id' => 'view_node',
'table' => 'node',
'field' => 'view_node',
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
),
'teaser' => array(
'label' => 'Teaser',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'strip_tags' => 0,
'html' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'exclude' => 0,
'id' => 'teaser',
'table' => 'node_revisions',
'field' => 'teaser',
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
),
));
$handler->override_option('filters', array(
'promote' => array(
'operator' => '=',
'value' => '1',
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'promote',
'table' => 'node',
'field' => 'promote',
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
),
'status' => array(
'operator' => '=',
'value' => '1',
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'status',
'table' => 'node',
'field' => 'status',
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
),
'depth' => array(
'operator' => '>',
'value' => array(
'value' => '1',
'min' => '',
'max' => '',
),
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'depth',
'table' => 'book_menu_links',
'field' => 'depth',
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
),
));
$handler->override_option('access', array(
'type' => 'none',
));
$handler->override_option('cache', array(
'type' => 'none',
));
$handler->override_option('items_per_page', 1);
$handler = $view->new_display('block', 'Block', 'block_1');
$handler->override_option('relationships', array(
'bid' => array(
'label' => 'Case studies',
'required' => 1,
'id' => 'bid',
'table' => 'book',
'field' => 'bid',
'override' => array(
'button' => 'Use default',
),
'relationship' => 'none',
),
));
$handler->override_option('title', 'Featured Case Study');
$handler->override_option('block_description', '');
$handler->override_option('block_caching', -1);
-snip-

After some experimentation, the error goes away if I remove the relationship "Book: Top level book".

Please let me know if you need any further information or debug information.

Thanks in advance.

CommentFileSizeAuthor
#9 650152-top-level-book-relationship-broken.patch625 bytesmerlinofchaos

Comments

afeijo’s picture

afeijo
Primary language Portuguese, Brazil
Location Porto Alegre
commented

I have the same problem after updating to views 6.x-2.8

webwriter’s picture

webwriter commented

I am having the same issue, although mine is a modified version of the default taxonomy view and the arguments are:

Taxonomy: Term ID (with depth)
Taxonomy: Term ID depth modifier

webwriter’s picture

webwriter commented

Upgrade to 6.x-2.x-dev of Dec 03 does not fix the issue, FYI.

dagmar’s picture

dagmar
Primary language Spanish
Location Argentina
commented
Status: Active » Postponed (maintainer needs more info)

@webwriter: Can you please test this patch http://drupalcode.org/viewvc/drupal/contributions/modules/views/includes... maybe it is related to a complex issue with relationships.

I cannot guaranteed that fix the problem, but it can be helpful to see if it is or not related to this issue.

webwriter’s picture

webwriter commented
Status: Postponed (maintainer needs more info) » Active

Thanks, will test it now and let you know.

webwriter’s picture

webwriter commented

Sorry dagmar, no change.

I don't know if it helps, but when logged in, I get the errors AND the data, when logged out I get the errors but no data returned.

webwriter’s picture

webwriter commented

I guess I should post the exact error message just in case it's something different than the OP:

* user warning: Unknown column 'node.nid' in 'on clause' query: SELECT COUNT(*) FROM (SELECT node.nid AS nid FROM node node LEFT JOIN content_field_event_date node_data_field_event_date ON node.vid = node_data_field_event_date.vid LEFT JOIN content_type_directory node_data_field_websnapr ON node.vid = node_data_field_websnapr.vid LEFT JOIN content_field_address node_data_field_address ON node.vid = node_data_field_address.vid LEFT JOIN content_field_address_2 node_data_field_address_2 ON node.vid = node_data_field_address_2.vid LEFT JOIN content_field_city node_data_field_city ON node.vid = node_data_field_city.vid LEFT JOIN content_field_state node_data_field_state ON node.vid = node_data_field_state.vid LEFT JOIN content_field_postal node_data_field_postal ON node.vid = node_data_field_postal.vid LEFT JOIN content_field_phone node_data_field_phone ON node.vid = node_data_field_phone.vid LEFT JOIN content_type_event node_data_field_event_description ON node.vid = node_data_field_event_description.vid LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid LEFT JOIN content_field_review node_data_field_review ON node.vid = node_data_field_review.vid LEFT JOIN content_field_web_site_link node_data_field_web_site_link ON node.vid = node_data_field_web_site_link.vid WHERE ((node.status <> 0 OR (node.uid = 0 AND 0 <> 0) OR 0 = 1) AND (node.vid IN ( SELECT tn.vid FROM term_node tn INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'domain_site') OR (na.gid = 3 AND na.realm = 'domain_id'))) AND ( tn.tid = 222 ))) AND ((DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_event_date.field_event_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-18000)), '%Y-%m-%d\T%H') >= '2009-12-03T14') OR (node_data_field_event_date.field_event_date_value IS NULL)) )) count_alias in /var/www/vhosts/domain.org/httpdocs/sites/all/modules/views/includes/view.inc on line 739.

* user warning: Unknown column 'node.nid' in 'on clause' query: SELECT node.nid AS nid, node_data_field_websnapr.field_websnapr_url AS node_data_field_websnapr_field_websnapr_url, node_data_field_websnapr.field_websnapr_title AS node_data_field_websnapr_field_websnapr_title, node_data_field_websnapr.field_websnapr_attributes AS node_data_field_websnapr_field_websnapr_attributes, node.type AS node_type, node.vid AS node_vid, node.title AS node_title, node_data_field_address.field_address_value AS node_data_field_address_field_address_value, node_data_field_address_2.field_address_2_value AS node_data_field_address_2_field_address_2_value, node_data_field_city.field_city_value AS node_data_field_city_field_city_value, node_data_field_state.field_state_value AS node_data_field_state_field_state_value, node_data_field_postal.field_postal_value AS node_data_field_postal_field_postal_value, node_data_field_phone.field_phone_value AS node_data_field_phone_field_phone_value, node_data_field_event_date.field_event_date_value AS node_data_field_event_date_field_event_date_value, node_data_field_event_date.field_event_date_value2 AS node_data_field_event_date_field_event_date_value2, node_data_field_event_date.field_event_date_rrule AS node_data_field_event_date_field_event_date_rrule, node_data_field_event_date.delta AS node_data_field_event_date_delta, node_data_field_event_description.field_event_description_value AS node_data_field_event_description_field_event_description_value, node_data_field_event_description.field_event_description_format AS node_data_field_event_description_field_event_description_format, node_revisions.body AS node_revisions_body, node_revisions.format AS node_revisions_format, node_data_field_review.field_review_value AS node_data_field_review_field_review_value, node_data_field_review.field_review_format AS node_data_field_review_field_review_format, node_data_field_web_site_link.field_web_site_link_url AS node_data_field_web_site_link_field_web_site_link_url, node_data_field_web_site_link.field_web_site_link_title AS node_data_field_web_site_link_field_web_site_link_title, node_data_field_web_site_link.field_web_site_link_attributes AS node_data_field_web_site_link_field_web_site_link_attributes, node.sticky AS node_sticky FROM node node LEFT JOIN content_field_event_date node_data_field_event_date ON node.vid = node_data_field_event_date.vid LEFT JOIN content_type_directory node_data_field_websnapr ON node.vid = node_data_field_websnapr.vid LEFT JOIN content_field_address node_data_field_address ON node.vid = node_data_field_address.vid LEFT JOIN content_field_address_2 node_data_field_address_2 ON node.vid = node_data_field_address_2.vid LEFT JOIN content_field_city node_data_field_city ON node.vid = node_data_field_city.vid LEFT JOIN content_field_state node_data_field_state ON node.vid = node_data_field_state.vid LEFT JOIN content_field_postal node_data_field_postal ON node.vid = node_data_field_postal.vid LEFT JOIN content_field_phone node_data_field_phone ON node.vid = node_data_field_phone.vid LEFT JOIN content_type_event node_data_field_event_description ON node.vid = node_data_field_event_description.vid LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid LEFT JOIN content_field_review node_data_field_review ON node.vid = node_data_field_review.vid LEFT JOIN content_field_web_site_link node_data_field_web_site_link ON node.vid = node_data_field_web_site_link.vid WHERE ((node.status <> 0 OR (node.uid = 0 AND 0 <> 0) OR 0 = 1) AND (node.vid IN ( SELECT tn.vid FROM term_node tn INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'domain_site') OR (na.gid = 3 AND na.realm = 'domain_id'))) AND ( tn.tid = 222 ))) AND ((DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_event_date.field_event_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-18000)), '%Y-%m-%d\T%H') >= '2009-12-03T14') OR (node_data_field_event_date.field_event_date_value IS NULL)) )ORDER BY node_sticky DESC, node_type ASC, node_data_field_event_date_field_event_date_value ASC LIMIT 0, 25 in /var/www/vhosts/domain.org/httpdocs/sites/all/modules/views/includes/view.inc on line 765.

I am now using the most recent dev (dec 03) and have patched the query.inc file as requested.

Thanks for any help!

dagmar’s picture

dagmar
Primary language Spanish
Location Argentina
commented

@webwriter: thanks. Well we can discard a cause now.

@merlinofchaos: I think that this is probably related to this commit http://drupal.org/cvs?commit=283236, where two kind of issues were committed but only one was documented into the change-log. At least this was one of the last time that book.views.inc was modified.

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Needs review
StatusFileSize
new 650152-top-level-book-relationship-broken.patch625 bytes

Yep, that commit (which was related to #584880: Relationships defined using wrong base field but didn't get logged correctly) did cause the problem. This simple patch fixes it.

merlinofchaos’s picture

merlinofchaos commented
Status: Needs review » Fixed

Fixed in all branches; you can use this patch as an emergency fix on your sites if necessary.

bpapa9013’s picture

bpapa9013 commented

I'm encountering this warning in Open Atrium 1.0-Beta3.2, I checked out this patch vs. the install file and this patch had already been applied to the book.views.inc file as of when I installed. I installed the patch anyway just to be sure and no change...

This is in reference to the originally posted error message (Unknown column 'node_book.bid' in 'on clause' query) NOT the DIFFERENT error posted in comment #7. I was confused as to which error the patch was supposed to fix, I presumed any posted fixes would be relative to the OP...

Thanks in advance, and feel free to ignore this if OA issues are out of scope for this forum, have posted on the OA community site, but no responses after three days so I am looking for assistance wherever I may be able to find it...

mnoyes’s picture

mnoyes commented
Status: Fixed » Active

I got the same error message but the views book.views.inc seems to include the patch. What could be the problem?

* user warning: Unknown column 'node_book.bid' in 'on clause' query: SELECT COUNT(*) FROM (SELECT node.nid AS nid FROM node node LEFT JOIN book book ON node.nid = book.nid INNER JOIN node node_book ON book.bid = node_book.bid LEFT JOIN menu_links book_menu_links ON book.mlid = book_menu_links.mlid LEFT JOIN book book_parent ON book_menu_links.plid = book_parent.mlid LEFT JOIN node node_book_parent ON book_parent.nid = node_book_parent.nid LEFT JOIN book node_book__book ON node_book.nid = node_book__book.nid LEFT JOIN book node_book_node_book__book ON node_book.nid = node_book_node_book__book.nid LEFT JOIN menu_links node_book_book_menu_links ON node_book__book.mlid = node_book_book_menu_links.mlid WHERE (node.status <> 0) AND (book_menu_links.depth = 3) AND (node.type in ('audrstory', 'book', 'bookreview')) AND (node_book.nid = 18) ) count_alias in /home/.../modules/views/includes/view.inc on line 739.
* user warning: Unknown column 'node_book.bid' in 'on clause' query: SELECT node.nid AS nid, node.title AS node_title, node.language AS node_language, node.created AS node_created, node_book_book_menu_links.p1 AS node_book_book_menu_links_p1, node_book_book_menu_links.p2 AS node_book_book_menu_links_p2, node_book_book_menu_links.p3 AS node_book_book_menu_links_p3, node_book_book_menu_links.p4 AS node_book_book_menu_links_p4, node_book_book_menu_links.p5 AS node_book_book_menu_links_p5, node_book_book_menu_links.p6 AS node_book_book_menu_links_p6, node_book_book_menu_links.p7 AS node_book_book_menu_links_p7, node_book_book_menu_links.p8 AS node_book_book_menu_links_p8, node_book_book_menu_links.p9 AS node_book_book_menu_links_p9, book_menu_links.weight AS book_menu_links_weight FROM node node LEFT JOIN book book ON node.nid = book.nid INNER JOIN node node_book ON book.bid = node_book.bid LEFT JOIN menu_links book_menu_links ON book.mlid = book_menu_links.mlid L in /home/..../modules/views/includes/view.inc on line 765.

chawl’s picture

chawl commented

HEAD, 6.x-2.x-dev, 6.x-2.8 still have this error.

Top level book relationship is not working.

Drupal 6.15 by the way. Tx.

stacysimpson’s picture

stacysimpson commented

Also, confirming that the patch from #9 doesn't resolve the original, reported issue for Drupal 6.15 and Views 6.x-2.8. We are still seeing the 'Unknown column 'node_book.bid' in 'on clause' query'.

bramface’s picture

bramface commented

subscribing - I turned tail and went back to 6.14 with open atrium and the problem persists - but I ran the updates beforehand....should have backed up the DB!

bramface’s picture

bramface commented

Here's what solved this for me:

When I saw the new Drupal upgrade, I decided to finesse with simlinks so that my Open Atrium install would share the same core as the rest of my sites
In so doing, I inadvertently made it possible for two versions of Spaces to be found.

When I deleted one of the versions, the problem went away!

Consult your oracle, the system table, and see where your version of Spaces is being kept - and then find the other one with grep -R 'spaces' sites/all

bramface’s picture

bramface commented
Status: Active » Closed (fixed)
chawl’s picture

chawl commented
Status: Closed (fixed) » Active

Don't close, this problem is not specific to Open Atrium, and the problem still persists; at least in my case (#13).

mnoyes’s picture

mnoyes commented

Me too, on Drupal 6.14.

Short term: Is there any way to make the error message visible only to the admin?

mr.andrey’s picture

mr.andrey commented

Experiencing this problem as well. Don't even know what Open Atrium is. The patch fixed it, just had to empty cache afterwards.

Andrey.

chawl’s picture

chawl commented

Strange, problem disappeared by itself. I have tried to empty cache with no success yesterday. In fact 2.x-dev does include the patch as merlin has said and should not have this problem.

Probably (readonly) Xcache has used stale opcodes for some time, for some reason, though files had changed.

Another parameter to check anyway.

shadysamir’s picture

shadysamir commented

I'm not using open atrium. Upgrading to 6.x-2.8 caused this problem. Clearing the cache, refreshing the view by updating and saving, and recreating the view all did not help. I upgraded to 6.15 today as well, problem persists

shadysamir’s picture

shadysamir commented

Since patch was applied to branches, i just downloaded 6.x-2.x-dev, cleared the cache and problem was solved

mnoyes’s picture

mnoyes commented

Worked for me too: upgraded to 6.15, downloaded 6.x-2.x-dev, cleared cache and all looks good. Any performance issues with the dev version that I should be aware of?

chawl’s picture

chawl commented

None I've observed.

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Fixed

Most people who've reported still having the problem have come back and reported it fixed; at least one person hasn't, but their report isn't complete so until I get something concrete, returning this to fixed.

stacysimpson’s picture

stacysimpson commented

@merlinofchaos, clearing the cache (after installing the patch) resolved it for us. Thanks!

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

mkorukmez’s picture

mkorukmez commented

Can anyone tell me how did you apply the patch ? Maybe it is a silly question but i get blank page.

Thanks

jacobson’s picture

jacobson commented

Go to the modules subdirectory of the views directory of your drupal install. (Probably sites/all/modules/views/modules or wherever you put your views module.) Save the patch file to that directory. Then, at the shell prompt, just patch < patchfilename.patch.

HAJ

jacobson’s picture

jacobson commented

-dev version plus clearing the cache solved the problem for me.

HAJ