SQL Syntax Error

I don't have a restore option because I started all over from a clean database and still get the same syntax errors. Any manipulation of content gives me MySQL syntax errors related to category tables so long as category is enabled. Attached is example of errors on content deletion.

• 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 '(n.nid), n.title FROM category c INNER JOIN category_node r ON c.cid = r.cid INN' at line 1 query: SELECT c.*, r.DISTINCT(n.nid), n.title FROM category c INNER JOIN category_node r ON c.cid = r.cid INNER JOIN category cn ON c.cnid = cn.cid INNER JOIN node n ON c.cid = n.nid INNER JOIN node cnn ON cn.cid = cnn.nid WHERE n.status = 1 AND n.moderate = 0 ORDER BY cn.weight, cnn.title, c.weight, n.title in /var/www/html/doadance/drupal/includes/database.mysql.inc on line 120.
• 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 '(n.nid), n.title FROM category c INNER JOIN category_node r ON c.cid = r.cid INN' at line 1 query: SELECT c.*, r.DISTINCT(n.nid), n.title FROM category c INNER JOIN category_node r ON c.cid = r.cid INNER JOIN category cn ON c.cnid = cn.cid INNER JOIN node n ON c.cid = n.nid INNER JOIN node cnn ON cn.cid = cnn.nid WHERE n.status = 1 AND n.moderate = 0 ORDER BY cn.weight, cnn.title, c.weight, n.title in /var/www/html/doadance/drupal/includes/database.mysql.inc on line 120.

More. I again started from scratch and began enabling modules one at a time. I began with the category module, then:

taxonomy
taxonomy_access
flexinode
views
views_ui

I noted that I could create content without error UNTIL I activated taxonomy_access. I verified that taxonomy wrapper was currently installed.

Is this a bug, or category does not work with taxonomy_access and views, or have I missed some configuration step somewhere?

-ron

p.s. In case this helps:

When I loaded cac_lite, I get this error at administer >> access_control >> cac_lite:

warning: Invalid argument supplied for foreach() in /var/www/html/doadance/drupal/includes/form.inc on line 637.

Trying to troubleshoot: I downloaded Drupal-4.7.1 and installed on Linux server running MySQL 4.1 database. Enabled taxonomy, taxomony_access. Then category. Created test page and got this:

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 '(n.nid), n.title FROM category c INNER JOIN category_node r ON c.cid = r.cid INN' at line 1 query: SELECT c.*, r.DISTINCT(n.nid), n.title FROM category c INNER JOIN category_node r ON c.cid = r.cid INNER JOIN category cn ON c.cnid = cn.cid INNER JOIN node n ON c.cid = n.nid INNER JOIN node cnn ON cn.cid = cnn.nid WHERE n.status = 1 AND n.moderate = 0 ORDER BY cn.weight, cnn.title, c.weight, n.title in /var/www/html/doadance/drupal01/includes/database.mysql.inc on line 120.

Does taxonomy_access even work with category?

Any ETA for this fix because not only can I not use taxonomy_access, but just discovered to day that there is a problem with forums and elements of the category module.

When I click on Forums (with category module installed), I get this error:

warning: Invalid argument supplied for foreach() in /var/www/html/doadance/drupal/modules/category/category.module on line 489.

I got rid of the error by disabling: category_export, category_legacy, and category_transform.

Thanks!

-ron

"Fix for this committed to HEAD and 4.7."

Could someone please tell me what this means and what I do about it? I mean, I know I'm supposed to download something, but what? And, since I have a site running now that I've put quite a few hours into, how do I implement the update? I'm a little new to all this.

Oh, and thanks to Jaza for the fix!

-ron

Download the gzip, unzip and untar it, and simply copy it over my existing category directory.

Correct?

Also, what's the date to look for?

Thanks!

-ron

Yes, I found and downloaded category-4.7.0 which had date of 2006-05-28. Simply copied this directory over existing directory (not, of course, before making backup). Enabled and activated taxonomy_access. Entered content, no complaints (which was the original problem).

Haven't done anything beyond this yet, but I too appreciate the fast turnaround. Thanks so much!

-ron

Uh, oh! This just might be a configuration issue that I don't understand, but I've run into a problem with the new category module.

I downloaded category update and replaced the existing one. I also enabled and activated taxonomy_access (the source of the original conflict). I no loger get sql warnings.

However, authenticated and anyonymous users can no longer access content located under category hiearchies (like the one you create in the tutorial). I can access the content while logged in as administrator, but not if I am logged out, or logged in as a regular authenticated user. For example, if I have a story content type page located under articles / news / stories, anonymous and authenticated users get a permission denied error when trying to access the page.

When I turn off (deactivate in settings) taxonomy_access, the problem goes away. Please forgive me if this is what is supposed to happen, because if it is, then does that mean I can't use taxnomy_access and category at the same time?

Please advise. Thanks.

-ron

Thank you for the link. I read up on it and found this:

"Taxonomy access is the recommended access module to be used in conjunction with category"

Unless the behaviour I reported is true for all, I suspect that you are right that it is either a configuration issue or another bug that has cropped up. I'm just trying to eliminate the possibilities. So, if it is a configuration issue, what should I be looking at to resolve it?

Thanks.

-ron

Thanks.

I did locate this thread: http://drupal.org/node/49715

This fellow had the same exact problem with taxonomy_access, although it must have been with a different access module. What resolved his issue was the following sql:

truncate table node_access;
insert into node_access VALUES(0, 0, 'all', 1, 0, 0);

I don't know beans about the inner workings of this code, so does this sound to anyone who does know like a reasonable thing to try and resolve this?

-ron

Ok, I did make some progress.

I went to administer > views and "enabled" taxnonomy_term.

I was still having problems accessing content as anonymous. So, I went to administer > access_control > category_permissions, and voila, there it was. The ability to determine what role has what access to content. So, I set the access to "List" for the content in question, and that has resolved the issue.

I'm sorry for dragging this out, but just didn't have a clue. So many nooks and crannies.

So, I think all my issues with respect to Category and taxonomy_access have been resolved.

Thanks so much to Jaza and all the others for your help and guidance. I've learned a lot!

-ron

There is one more small problem, and I don't know whether it is a bug or a feature:

When I turn on the taxonomy wrapper, the taxnonomy "categories" menu item which has the "edit vocabulary" tab goes away and is replaced by the category "categories" page which has only list/add containers and bulk edit. At this point, I cannot add or edit taxonomy vocabularies.

Is this supposed to happen?