Irregular MySQL error
| Project: | Views |
| Version: | 5.x-1.6 |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Jump to:
I have a view that sorts nodes by date, votingapi vote average, and workflow state.
The view shows it's "empty text" from time to time. My logs also register the following MySQL error during these periods:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '>= '4') AND (node.created > 1196877340 + -604800) ORDER BY DESC, DESC, node_c' at line 1 query: SELECT DISTINCT(node.nid), value, node.created AS node_created_created, votingapi_cache_vote_percent_average.value AS votingapi_cache_vote_percent_average_value, votingapi_cache_vote_percent_count.value AS votingapi_cache_vote_percent_count_value, node.title AS node_title, node.changed AS node_changed, workflow_states.state AS workflow_states_state, node.created AS node_created FROM node node LEFT JOIN workflow_node workflow_node ON node.nid = workflow_node.nid LEFT JOIN workflow_states workflow_states ON workflow_node.sid = workflow_states.sid WHERE (workflow_node.sid IN ('7','2')) AND (.value >= '4') AND (node.created > 1196877340 + -604800) ORDER BY DESC, DESC, node_created_created DESC, votingapi_cache_vote_percent_average_value DESC LIMIT 0, 1 in /[my base directory]/includes/database.mysql.inc on line 166.
I am running Drupal 5.3 I have the latest releases of Voting API, Fivestar, Workflow (regular, not NG), and Views. I'm also running Memcache. I am running MySQL 5.0.45.
Does anyone see an obvious error in the syntax? Any clue as to what module is causing the problem?
Thank you everyone for your help.
#1
Obvious errors:
(.value >= '4')This happens when a table isn't in the views data and ends up translating to a NULL and a bad query gets formed because the table name doesn't appear to exist.
ORDER BY DESC, DESCSimilar to above.
What this usually indicates is that the views data is changing around the view somehow. I've heard of this kind of thing happening, but I haven't ever seen it personally and I don't know what causes it, unfortunately.
#2
More than six months later, I'm still having this issue. More recently, the error looks like this:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '>= '5') ORDER BY DESC, node_created_created DESC LIMIT 0, 10' at line 1 query: SELECT DISTINCT(node.nid), value, node.created AS node_created_created, votingapi_cache_vote_percent_average.value AS votingapi_cache_vote_percent_average_value, votingapi_cache_vote_percent_count.value AS votingapi_cache_vote_percent_count_value, node.title AS node_title, node.changed AS node_changed, node.created AS node_created FROM node node WHERE (node.created > 1217019494 + -604800) AND (.value >= '5') ORDER BY DESC, node_created_created DESC LIMIT 0, 10 in /[my base directory]/includes/database.mysql.inc on line 166.
Anybody have any idea? Is there someone I could hire to look at this specifically? I really would like to see this solved.
#3
Usually this happens when some module has their views stuff in a .inc file, and the views cache is rebuilt at an odd time and that .inc file hasn't been included. What module is supplying that missing field? All I can see is the .value but since the table isn't there during the query I can't tell where it comes from. Is it a workflow field? If that's the case, it may be that workflow needs to adjust the inclusion of its .inc file to happen earlier.
#4
I strongly suspect its Votingapi, but I don't know for sure.
Is there something I can do when things are "normal" to see the exact query?
#5
If you use devel.module and enable the query logging features, you can get a list of all queries run at the end of the page. You can usually pick out a views query visually once you know what they look like, and it'll be pretty similar to the bad query.