MySQL error in VotingAPI
vitovonantwon - December 15, 2007 - 05:19
| Project: | Fivestar |
| Version: | 5.x-1.9 |
| Component: | Code |
| Category: | support request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | closed |
Jump to:
Description
recent posts will display if you disable the 5 star module. WHen enabling it seems to work.
But after a few minutes I start getting this message.
user warning: 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 'ASC LIMIT 200, 25' at line 1 query: SELECT node.nid, node_comment_statistics.last_comment_timestamp AS node_comment_statistics_last_comment_timestamp_last_comment_timestamp, value, node.type AS node_type, node.title AS node_title, node.changed AS node_changed, users.name AS users_name, users.uid AS users_uid, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, node_comment_statistics.last_comment_timestamp AS node_comment_statistics_last_comment_timestamp, votingapi_cache_vote_percent_average.value AS votingapi_cache_vote_percent_average_value FROM node node LEFT JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid INNER JOIN users users ON node.uid = users.uid WHERE (node.status = '1') ORDER BY node_comment_statistics_last_comment_timestamp_last_comment_timestamp DESC, ASC LIMIT 200, in /home/pojack1/public_html/nyclightwave/includes/database.mysql.inc on line 172.I disable the module and the column that has the votes displays a numeric value.
Antonio
#1
I uninstalled the 5 star module, and then re-enabled it.
So far the error went away. IF I don't post in a few days, this issue can be closed.
Antonio
#2
Ok it happened again.
I'm not sure what's causing the problem.
I fix it by uninstalling the module and re-installing it.
It seems to happen after editing a post.
Antonio
#3
Some more info. I am using views and created a view with some criteria to only display a page with nodes that have 50% or better rating.
After the sql error I went back to the views and all the voting api and 5 star items were gone from the choice list.
After disabling the 5 star module, I was able to see the voting api items.
Something i think in the 5star causes a conflict.
Antonio
#4
Hey Antonio, until this can be reproduced from a stock Drupal 5 install I doubt I'll be able to pin it down. Fivestar has very little views code, but there's a small possibility it's causing the problem. Please continue to try and track it down.
#5
Ok.
Ya I have a ton of modules. It seems that the problem happens when you edit an existing node. I rated the node and it was fine. I edited the same node and added a tag. Then the problem showed up in the tracker (recent_posts view) and View TopVote block(a view I created to display only nodes that have a 50% or better value). I went to views and cleared cache, no luck. Disabled the module, cleared cache and no more error. Also the node I rated didn't have a rating showing on my recent tracker views page(recent Posts). So I went back and re-rated the node. Interesting enough it still had the correct rating in the node page. Went back the tracker view page(recent posts) and it showed up correctly.
Kinda of weird.
The only page having the problems are the views page and block.
#6
I'll try the same set up on a new drupal install. I'll post back here with results.
Antonio
#7
user warning: 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 '>= '50') ORDER BY ASC LIMIT 0, 15' at line 1 query: SELECT DISTINCT(node.nid), value, node.title AS node_title, node.changed AS node_changed, votingapi_cache_vote_percent_average.value AS votingapi_cache_vote_percent_average_value FROM node node WHERE (node.status = '1') AND (.value >= '50') ORDER BY ASC LIMIT 0, 15 in /home/pojack1/public_html/learntheinternet/youcan/includes/database.mysql.inc on line 172Ok I was able to reproduce this error on another site. I think I know what it is now.
The above error started to happen after I added a new category of "tags". I allowed it to use terms from forums and images.
After that, I started getting the mysql error.
So I think this error will happen if you do the following.
Install druapl 5.5
Modules:
5 star, voting api, views
create some content.
Rate it.
Create a view using the 5 star content to show only nodes that have a 50 or better score.
Mysql error happens in the block or view I named 'topvote'
here is the code exported from views.
$view = new stdClass();$view->name = 'topvote';
$view->description = 'Posts are Listed in the order of Greatest Users Votes on Posts';
$view->access = array (
);
$view->view_args_php = '';
$view->page = TRUE;
$view->page_title = 'TopVotes';
$view->page_header = '';
$view->page_header_format = '4';
$view->page_footer = '';
$view->page_footer_format = '4';
$view->page_empty = '';
$view->page_empty_format = '4';
$view->page_type = 'list';
$view->url = 'TopVote';
$view->use_pager = TRUE;
$view->nodes_per_page = '30';
$view->block = TRUE;
$view->block_title = '';
$view->block_header = '';
$view->block_header_format = '4';
$view->block_footer = '';
$view->block_footer_format = '4';
$view->block_empty = '';
$view->block_empty_format = '4';
$view->block_type = 'list';
$view->nodes_per_block = '15';
$view->block_more = FALSE;
$view->block_use_page_header = FALSE;
$view->block_use_page_footer = FALSE;
$view->block_use_page_empty = FALSE;
$view->sort = array (
array (
'tablename' => 'votingapi_cache_vote_percent_average',
'field' => 'value',
'sortorder' => 'ASC',
'options' => '',
),
);
$view->argument = array (
);
$view->field = array (
array (
'tablename' => 'node',
'field' => 'title',
'label' => '',
'handler' => 'views_handler_field_nodelink',
'options' => 'link',
),
array (
'tablename' => 'votingapi_cache_vote_percent_average',
'field' => 'value',
'label' => '',
'handler' => 'fivestar_views_value_display_handler',
),
);
$view->filter = array (
array (
'tablename' => 'node',
'field' => 'status',
'operator' => '=',
'options' => '',
'value' => '1',
),
array (
'tablename' => 'votingapi_cache_vote_percent_average',
'field' => 'value',
'operator' => '>=',
'options' => '',
'value' => '50',
),
);
$view->exposed_filter = array (
);
$view->requires = array(votingapi_cache_vote_percent_average, node);
$views[$view->name] = $view;
#8
Thanks, I've been able to import your view but it displays fine. Could you detail what versions of VotingAPI, Views, and Fivestar you're using? I'm suspecting the VotingAPI views implementation or Views itself.
In the query you have above
(.value >= '50')should be
(votingapi_cache_vote_percent_average.value >= '50')Why the table name is missing is the problem we need to solve.
#9
Views 5.x-1.6
Views Bonus Pack 5.x-1.1
Views Tabs 5.x-1.x-dev (2007-Aug-08)
Voting API 5.x-1.5
Token 5.x-1.9
Fivestar 5.x-1.11-beta2
Also to replicate the problem Try this.
Goto Admin/category Add in a category of tags. Then allow these settings.
On my other site I didn't get any errors until I added this Admin/category. Tags.
I enabled several Types like forum and image.
Hierarchy:Multiple
Enabled:
Related terms
Free tagging
Multiple select
After that I the view stopped working. I would reset the cache and the error goes away.
But adding new content, or editing older content would cause the problem again.
Hope that helps.
Antonio
#10
"Why the table name is missing is the problem we need to solve."
While there is a mysql error if I go back to views and edit the TOPVOte view I created, the voting api is no longer a choice.
I could not if I wanted to create that view from scratch because the voting api choices have disappeared.
If I clear the cache, then it's all back to normal, and I see the voting api choices again.
Antonio
#11
I still can't reproduce this problem. I've setup a site with all the module versions you report, but the problem does not show up. You'll need to give me literally a step-by-step from a stock Drupal install to get this fixed, otherwise it seems like it's a problem unique to your scenario as we haven't had any supporting posts that others have run into this same problem.
#12
I've not gotten the error in a week. I've periodically updated all the modues on my site. Maybe one of the updates fixed the problem.
I'm not going to say it's fixed since there arn't a lot of posts on my site. I've tried to edit posts, add ratings etc. Can't get the error again. (which is good.) I'll keep on testing the other site now. (Which gets even less traffic)
I have a third site that I ran the view on and it hasn't had the error ever. I'm trying to mabye track down what's different.
It might be awhile though. I will try to see about it this weekend. Thanks for looking into this on for me.
Antonio
#13
Possibly related:
I have Views 5.x-1.6, Fivestar 5.x-1.11-beta2, Voting API 5.x-1.5. Whenever I add a taxonomy term (see #5), I get the following error message:
When I go to edit the view, the 'VotingAPI percent vote value' field is not shown in the enabled fields, nor can it be selected from the form, but when I save the view the field reappears enabled, with the handler set to 'Raw value' rather than 'Fivestar rating'. Save the view again, and everything's fine until the next taxonomy term is added. This is obviously a major problem on a site with freetagging.
#14
In fact, any taxonomy change, even editing a vocabulary's help text appears to trigger this. And a correction to the above, the field does not disappear entirely from the edit view form, just all of it's values ('name', 'label', 'handler', etc.) are blank.
#15
My problem appears to have been caused by this bug in VotingAPI. The patch supplied there did the trick for me.
#16
Matthew Davidson -
I think this is it, thanks for letting me know, It's been driving me mad this problem. How did you apply the patch. I'm not sure how to do that. I ussually grab the update when update status shows the module is out of date. Never used the patchs..although I've seen them now. I know I have to cut it and paste it into the module, but where.
Antonio
#17
See the handbook page on applying patches.
#18
Thanks.
Antonio
#19
I have exactly the same problem as you guys. I've applied the patch and cleared my voting api cache and I'll see if the problem re-occurs.
#20
Seems pretty clear that this is not a problem caused by Fivestar now. I'm going to go ahead and close.
#21
Yeah, that patch for VotingAPI fixed everything. I haven't had the problem since applying it.
Thanks quicksketch for all your hard work on the Fivestar module.