Hi there, this is definitely a support request, thank you very much in advance if anyone has the time to help--

I've been running my first drupal site for about a year now and have come along way, but this one I am having trouble with.

drupal 6.13
cck 6.x-2.6
views 6.x-2.x-dev (2010-Jan-27)

After a series of module upgrades (unfortunately I did them all at once) and running update.php, I get this error on all my pages:

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 'DISTINCT(node.nid), node_data_field_afflink.field_afflink_url AS node_data_fi' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node_data_field_afflink.field_afflink_url AS node_data_field_afflink_field_afflink_url, node_data_field_afflink.field_afflink_title AS node_data_field_afflink_field_afflink_title, node_data_field_afflink.field_afflink_attributes AS node_data_field_afflink_field_afflink_attributes, node.language AS node_language, node.type AS node_type, node.vid AS node_vid, node_data_field_afflogo.field_afflogo_fid AS node_data_field_afflogo_field_afflogo_fid, node_data_field_afflogo.field_afflogo_list AS node_data_field_afflogo_field_afflogo_list, node_data_field_afflogo.field_afflogo_data AS node_data_field_afflogo_field_afflogo_data, RAND() AS _random FROM node node LEFT JOIN content_type_affiliations node_data_field_afflogo ON node.vid = node_data_field_afflogo.vid LEFT JOIN files files_node_data_field_afflogo ON node_data_field_afflogo.field_afflogo_fid = files_node_data_field_afflogo.fid LEFT JOIN term_node term_node_value_0 ON node.vid = term_node_value_0.vid AND term_node_value_0.tid = 48 LEFT JOIN term_node term_node_value_1 ON node.vid = term_node_value_1.vid AND term_node_value_1.tid = 47 LEFT JOIN content_type_affiliations node_data_field_afflink ON node.vid = node_data_field_afflink.vid WHERE (node.type in ('affiliations')) AND (term_node_value_0.tid = 48 OR term_node_value_1.tid = 47) AND (node_data_field_afflogo.field_afflogo_list <> 0) GROUP BY nid ORDER BY _random ASC in /home/porterwo/public_html/sites/all/modules/views/includes/view.inc on line 769.

afflink & afflogo are cck fields, but its pointing the error to views.. I'm sure both cck and views were included in the upgrade, should I roll them both back and see if this error goes away?

Thanks again,
Sean

Comments

sean porter’s picture

sean porter commented
Version: 6.x-2.x-dev » 6.x-3.0-alpha2

I tried downgrading to the last non-dev release and had the same error. then I tried going the other way to 6.x-3.0a2 and the error list grew substantially:

# 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 'DISTINCT(node.nid)_1, node_data_field_afflink.field_afflink_url AS node_data_fie' at line 1 query: SELECT COUNT(*) FROM (SELECT DISTINCT(node.nid) AS DISTINCT(node.nid)_1, node_data_field_afflink.field_afflink_url AS node_data_field_afflink_field_afflink_url, node_data_field_afflink.field_afflink_title AS node_data_field_afflink_field_afflink_title, node_data_field_afflink.field_afflink_attributes AS node_data_field_afflink_field_afflink_attributes, node.type AS node_type, node.nid AS nid, node.vid AS node_vid, node_data_field_afflogo.field_afflogo_fid AS node_data_field_afflogo_field_afflogo_fid, node_data_field_afflogo.field_afflogo_list AS node_data_field_afflogo_field_afflogo_list, node_data_field_afflogo.field_afflogo_data AS node_data_field_afflogo_field_afflogo_data, RAND() AS _random FROM node node LEFT JOIN content_type_affiliations node_data_field_afflogo ON node.vid = node_data_field_afflogo.vid LEFT JOIN files files_node_data_field_afflogo ON node_data_field_afflogo.field_afflogo_fid = files_node_data_field_afflogo.fid LEFT JOIN term_node term_node_value_0 ON node.vid = term_node_value_0.vid AND term_node_value_0.tid = 48 LEFT JOIN term_node term_node_value_1 ON node.vid = term_node_value_1.vid AND term_node_value_1.tid = 47 LEFT JOIN content_type_affiliations node_data_field_afflink ON node.vid = node_data_field_afflink.vid WHERE (node.type in ('affiliations')) AND (term_node_value_0.tid = 48 OR term_node_value_1.tid = 47) AND (node_data_field_afflogo.field_afflogo_list <> 0) GROUP BY nid_1 ORDER BY _random ASC ) count_alias in /home/porterwo/public_html/sites/all/modules/views/plugins/views_plugin_pager.inc on line 134.
# 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 'DISTINCT(node.nid)_1, node_data_field_afflink.field_afflink_url AS node_data_fie' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid)_1, node_data_field_afflink.field_afflink_url AS node_data_field_afflink_field_afflink_url, node_data_field_afflink.field_afflink_title AS node_data_field_afflink_field_afflink_title, node_data_field_afflink.field_afflink_attributes AS node_data_field_afflink_field_afflink_attributes, node.type AS node_type, node.nid AS nid, node.vid AS node_vid, node_data_field_afflogo.field_afflogo_fid AS node_data_field_afflogo_field_afflogo_fid, node_data_field_afflogo.field_afflogo_list AS node_data_field_afflogo_field_afflogo_list, node_data_field_afflogo.field_afflogo_data AS node_data_field_afflogo_field_afflogo_data, RAND() AS _random FROM node node LEFT JOIN content_type_affiliations node_data_field_afflogo ON node.vid = node_data_field_afflogo.vid LEFT JOIN files files_node_data_field_afflogo ON node_data_field_afflogo.field_afflogo_fid = files_node_data_field_afflogo.fid LEFT JOIN term_node term_node_value_0 ON node.vid = term_node_value_0.vid AND term_node_value_0.tid = 48 LEFT JOIN term_node term_node_value_1 ON node.vid = term_node_value_1.vid AND term_node_value_1.tid = 47 LEFT JOIN content_type_affiliations node_data_field_afflink ON node.vid = node_data_field_afflink.vid WHERE (node.type in ('affiliations')) AND (term_node_value_0.tid = 48 OR term_node_value_1.tid = 47) AND (node_data_field_afflogo.field_afflogo_list <> 0) GROUP BY nid_1 ORDER BY _random ASC LIMIT 0, 15 in /home/porterwo/public_html/sites/all/modules/views/plugins/views_plugin_query_default.inc on line 1093.
# 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 'DISTINCT(node.nid) AS nid, node.language AS node_language, node_data_field_date.' at line 2 query: SELECT COUNT(*) FROM (SELECT node.title AS node_title, DISTINCT(node.nid) AS nid, node.language AS node_language, node_data_field_date.field_date_value AS node_data_field_date_field_date_value, node_data_field_date.field_date_value2 AS node_data_field_date_field_date_value2, node_data_field_date.delta AS node_data_field_date_delta, node.type AS node_type, node.vid AS node_vid, node.changed AS node_changed FROM node node LEFT JOIN content_field_date node_data_field_date ON node.vid = node_data_field_date.vid WHERE (node.status <> 0) AND ((DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-28800)), '%Y-%m') <= '2010-02' AND DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T'), SEC_TO_TIME(-28800)), '%Y-%m') >= '2010-02')) ) count_alias in /home/porterwo/public_html/sites/all/modules/views/plugins/views_plugin_pager.inc on line 134.
# 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 'DISTINCT(node.nid) AS nid, node.language AS node_language, node_data_field_date.' at line 2 query: SELECT node.title AS node_title, DISTINCT(node.nid) AS nid, node.language AS node_language, node_data_field_date.field_date_value AS node_data_field_date_field_date_value, node_data_field_date.field_date_value2 AS node_data_field_date_field_date_value2, node_data_field_date.delta AS node_data_field_date_delta, node.type AS node_type, node.vid AS node_vid, node.changed AS node_changed FROM node node LEFT JOIN content_field_date node_data_field_date ON node.vid = node_data_field_date.vid WHERE (node.status <> 0) AND ((DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-28800)), '%Y-%m') <= '2010-02' AND DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T'), SEC_TO_TIME(-28800)), '%Y-%m') >= '2010-02')) ORDER BY node_changed ASC LIMIT 0, 10 in /home/porterwo/public_html/sites/all/modules/views/plugins/views_plugin_query_default.inc on line 1093.
# 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 'DISTINCT(node.nid) AS nid, node.language AS node_language, node.type AS node_typ' at line 2 query: SELECT COUNT(*) FROM (SELECT node.title AS node_title, DISTINCT(node.nid) AS nid, node.language AS node_language, node.type AS node_type, node.vid AS node_vid, node.changed AS node_changed FROM node node LEFT JOIN content_field_date node_data_field_date ON node.vid = node_data_field_date.vid WHERE (node.status <> 0) AND ((DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-28800)), '%Y-%m') <= '2010-02' AND DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T'), SEC_TO_TIME(-28800)), '%Y-%m') >= '2010-02')) ) count_alias in /home/porterwo/public_html/sites/all/modules/views/plugins/views_plugin_pager.inc on line 134.
# 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 'DISTINCT(node.nid) AS nid, node.language AS node_language, node.type AS node_typ' at line 2 query: SELECT node.title AS node_title, DISTINCT(node.nid) AS nid, node.language AS node_language, node.type AS node_type, node.vid AS node_vid, node.changed AS node_changed FROM node node LEFT JOIN content_field_date node_data_field_date ON node.vid = node_data_field_date.vid WHERE (node.status <> 0) AND ((DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-28800)), '%Y-%m') <= '2010-02' AND DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T'), SEC_TO_TIME(-28800)), '%Y-%m') >= '2010-02')) ORDER BY node_changed ASC LIMIT 0, 10 in /home/porterwo/public_html/sites/all/modules/views/plugins/views_plugin_query_default.inc on line 1093.

yikes

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Closed (duplicate)

This is the core DISTINCT issue again. Search teh Drupal core queue for db_rewrite_sql DISTINCT -- it's a very large issue.

sean porter’s picture

sean porter commented

thanks merlin,