Intermittent MySQL errors

There is a side effect of the taxonomy access module that causes views to forget about vocabularies that the current user cannot see when Views goes to refresh its cache. We've never come up with a fix for this.

That query suggests taxonomy_access is installed:

na.realm = 'term_access'

I don't know anything about taxonomy_role, though.

We're having a problem where Drupal will intermittently create an invalid SQL query when displaying one our our views. The view has a Taxonomy field displayed in the last column. What's weird is that the view works immediately after we create it - but then breaks spontaneously without anything changing in the view or underlying data. It can be "fixed" by merely opening the edit dialog and saving the view again, without changing anything, but it soon breaks again.

The SQL query includes a SELECT clause for "term_node_3.name". But, there is no table in our Drupal installation called term_node_3 (there is one called term_node). We found a reference to this table name in view_tablefield. It looks to me that Views is overloading the meaning of the "table" column to append the vocabulary table "vid" of the taxonomy we are displaying. But that is not getting stripped out appropriately.

The bad view is trying to access a non-existant table, "term_node_3". I think this is because of a hack that the Views module uses to encode the table name and the term number, into the table_name column of the view description.

I note that the working view does not even try to query the Taxonomy column - so Drupal must be making an additional query (for every row of my view?) to display the taxonomy column in my table view.

We have version 5.x-1.6 on our site. The error message is:

user warning: Unknown column 'term_node_3.name' in 'field list' query:

SELECT DISTINCT(node.nid), node.title AS node_title, node.changed AS node_changed, node_data_field_service_summary.field_service_summary_value AS node_data_field_service_summary_field_service_summary_value, node_data_field_website.field_website_url AS node_data_field_website_field_website_url, node_data_field_website.field_website_title AS node_data_field_website_field_website_title, node_data_field_website.field_website_attributes AS node_data_field_website_field_website_attributes, users.name AS users_name, users.uid AS users_uid, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, term_node_3.name AS term_node_3_name, term_node_6.name AS term_node_6_name
FROM node node
LEFT JOIN content_type_service node_data_field_service_summary ON node.vid = node_data_field_service_summary.vid
LEFT JOIN content_field_website node_data_field_website ON node.vid = node_data_field_website.vid
INNER JOIN users users ON node.uid = users.uid
LEFT JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid
WHERE (node.type IN ('service')) LIMIT 0, 200

in /home/startpad/public_html/includes/database.mysql.inc on line 172.

Working Query:

SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node_data_field_service_summary.field_service_summary_value AS node_data_field_service_summary_field_service_summary_value, node_data_field_website.field_website_url AS node_data_field_website_field_website_url, node_data_field_website.field_website_title AS node_data_field_website_field_website_title, node_data_field_website.field_website_attributes AS node_data_field_website_field_website_attributes, users.name AS users_name, users.uid AS users_uid, node_comment_statistics.comment_count AS node_comment_statistics_comment_count
FROM {node} node
LEFT JOIN {content_type_service} node_data_field_service_summary ON node.vid = node_data_field_service_summary.vid
LEFT JOIN {content_field_website} node_data_field_website ON node.vid = node_data_field_website.vid
INNER JOIN {users} users ON node.uid = users.uid
LEFT JOIN {node_comment_statistics} node_comment_statistics ON node.nid = node_comment_statistics.nid
WHERE (node.type IN ('service'))

We have exported the view right after it broke, then saved it and exported the newly saved view, and a diff merely gives us this:

user@computer$ diff error fixed
75d74
<       'options' => 'link',

Any ideas?

zzelinski: I can tell you that term_node_3 is a Views alias for the term_node table with a specific join (vid = 3) and the lack of that table indicates you may be having a similar problem where the views data may be dropping out (causing term_node_3 to be unable to be joined in).

I suspect if you clear cache the problem will disappear, but it may reappear again later.

Hi!

Do you have any idea when the problem with taxonomy access control and views could be fixed? Or do you have any suggestions how to prevent this from happening? I'm developing a site which relies heavily on the use of Views and TAC, and views stop working few times a day.

I'm having a very similar problem. All of a sudden my view returns no results. Like the other posters, re-saving the view temporarily fixes the problem. I also have TAC installed and use it extensively on the site in question. I am willing to help fund a fix to this issue.

I haven't done re-saving, but cleared the views cache. This fixes the problem (also temporarily). I was wondering if using a cron job for clearing the cache would be a reasonable solution? Maybe call "views_invalidate_cache()" every 30 minutes or so?

Performing a mass closing of all issues over 180 days old. Please reopen if needed.