Hi,

After updating to 2.5, I got a mysql syntax error when accessing my view. (I did run update.php, and I did clear my cache).

I'll put the error, the exported view, and the query below. Any help would be appreciated--if it seems caused by another module (I have a few) please let me know and I'll be happy to post in that module's queue. I just need a little direction as to what could be the cause.

Thanks!

-Daniel B.

Here's the error it generated:

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 '= votingapi_cache_node_average.content_id AND (votingapi_cache_node_average.cont' at line 17 query: SELECT users.uid AS uid, users.name AS users_name, votingapi_cache_node_average.value AS votingapi_cache_node_average_value, node_users.type AS node_users_type, node_users.nid AS node_users_nid, node_users.vid AS node_users_vid, node_data_field_tech_other_platform.field_tech_other_platform_value AS node_data_field_tech_other_platform_field_tech_other_platform_value, location.city AS location_city, location.postal_code AS location_postal_code, users.picture AS users_picture, node_data_field_tech_other_platform.field_tech_about_value AS node_data_field_tech_other_platform_field_tech_about_value, node_data_field_tech_other_platform.field_other_payment_options_value AS node_data_field_tech_other_platform_field_other_payment_options_value, node_data_field_tech_other_platform.field_goods_services_value AS node_data_field_tech_other_platform_field_goods_services_value, 'Unknown' AS location_distance_13 FROM users users INNER JOIN node node_users ON users.uid = node_users.uid AND node_users.type = 'technician_registration' LEFT JOIN votingapi_cache votingapi_cache_node_average ON .nid = votingapi_cache_node_average.content_id AND (votingapi_cache_node_average.content_type = 'node' AND votingapi_cache_node_average.function = 'average') LEFT JOIN location_instance location_instance ON users.uid = location_instance.uid LEFT JOIN location location ON location_instance.lid = location.lid INNER JOIN users_roles users_roles ON users.uid = users_roles.uid LEFT JOIN content_type_technician_registration node_data_field_tech_other_platform ON node_users.vid = node_data_field_tech_other_platform.vid WHERE (users.status <> 0) AND (location.lid IS NOT NULL) AND (users_roles.rid = 3) AND (0) LIMIT 0, 15 in C:\Program Files\EasyPHP 2.0b1\www\drupal\sites\all\modules\views\includes\view.inc on line 731.

*******************************************************************

Here's the exported view:

$view = new view;
$view->name = 'Technician_List';
$view->description = 'List of Technicians';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'users';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('relationships', array(
'content_profile_rel' => array(
'label' => 'Content Profile',
'required' => 1,
'type' => 'technician_registration',
'id' => 'content_profile_rel',
'table' => 'users',
'field' => 'content_profile_rel',
'relationship' => 'none',
),
'votingapi_cache' => array(
'label' => 'Voting results',
'required' => 0,
'votingapi' => array(
'value_type' => '',
'tag' => '',
'function' => 'average',
),
'id' => 'votingapi_cache',
'table' => 'node',
'field' => 'votingapi_cache',
'relationship' => 'content_profile_rel',
),
));
$handler->override_option('fields', array(
'name' => array(
'label' => 'Name',
'link_to_user' => 1,
'exclude' => 0,
'id' => 'name',
'table' => 'users',
'field' => 'name',
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
),
'value' => array(
'label' => 'Rating',
'set_precision' => FALSE,
'precision' => 0,
'decimal' => '.',
'separator' => ',',
'prefix' => '',
'suffix' => '',
'appearance' => 'fivestar_views_value_display_handler',
'exclude' => 0,
'id' => 'value',
'table' => 'votingapi_cache',
'field' => 'value',
'relationship' => 'votingapi_cache',
),
'field_tech_platforms_value' => array(
'label' => 'Platforms',
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'group' => 1,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => 0,
),
'exclude' => 0,
'id' => 'field_tech_platforms_value',
'table' => 'node_data_field_tech_platforms',
'field' => 'field_tech_platforms_value',
'relationship' => 'content_profile_rel',
),
'field_tech_other_platform_value' => array(
'id' => 'field_tech_other_platform_value',
'table' => 'node_data_field_tech_other_platform',
'field' => 'field_tech_other_platform_value',
'relationship' => 'content_profile_rel',
),
'city' => array(
'label' => 'City',
'exclude' => 0,
'id' => 'city',
'table' => 'location',
'field' => 'city',
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
),
'postal_code' => array(
'label' => 'Zipcode',
'exclude' => 0,
'id' => 'postal_code',
'table' => 'location',
'field' => 'postal_code',
'override' => array(
'button' => 'Override',
),
'relationship' => 'none',
),
'picture' => array(
'label' => 'Picture',
'exclude' => 0,
'id' => 'picture',
'table' => 'users',
'field' => 'picture',
'relationship' => 'none',
),
'field_tech_services_value' => array(
'label' => 'Services',
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'group' => 1,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => 0,
),
'exclude' => 0,
'id' => 'field_tech_services_value',
'table' => 'node_data_field_tech_services',
'field' => 'field_tech_services_value',
'relationship' => 'content_profile_rel',
),
'field_tech_about_value' => array(
'label' => 'About me',
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'group' => TRUE,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => FALSE,
),
'exclude' => 0,
'id' => 'field_tech_about_value',
'table' => 'node_data_field_tech_about',
'field' => 'field_tech_about_value',
'relationship' => 'content_profile_rel',
),
'field_tech_payment_types_value' => array(
'label' => 'Payment types accepted',
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'group' => 1,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => 0,
),
'exclude' => 0,
'id' => 'field_tech_payment_types_value',
'table' => 'node_data_field_tech_payment_types',
'field' => 'field_tech_payment_types_value',
'relationship' => 'content_profile_rel',
),
'field_other_payment_options_value' => array(
'label' => 'Other payment options',
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'group' => TRUE,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => FALSE,
),
'exclude' => 0,
'id' => 'field_other_payment_options_value',
'table' => 'node_data_field_other_payment_options',
'field' => 'field_other_payment_options_value',
'relationship' => 'content_profile_rel',
),
'field_goods_services_value' => array(
'label' => 'Goods or services I can trade for',
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'group' => TRUE,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => FALSE,
),
'exclude' => 0,
'id' => 'field_goods_services_value',
'table' => 'node_data_field_goods_services',
'field' => 'field_goods_services_value',
'relationship' => 'content_profile_rel',
),
'field_tech_availability_day' => array(
'label' => 'Availability',
'link_to_node' => 0,
'label_type' => 'widget',
'format' => 'default',
'multiple' => array(
'group' => 1,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => 0,
),
'exclude' => 0,
'id' => 'field_tech_availability_day',
'table' => 'node_data_field_tech_availability',
'field' => 'field_tech_availability_day',
'relationship' => 'content_profile_rel',
),
'distance' => array(
'label' => 'Distance',
'origin' => 'tied',
'units' => 'mi',
'latitude' => '',
'longitude' => '',
'exclude' => 0,
'id' => 'distance',
'table' => 'location',
'field' => 'distance',
'relationship' => 'none',
),
));
$handler->override_option('sorts', array(
'distance' => array(
'order' => 'ASC',
'origin' => 'tied',
'units' => 'mi',
'latitude' => '',
'longitude' => '',
'id' => 'distance',
'table' => 'location',
'field' => 'distance',
'relationship' => 'none',
),
));
$handler->override_option('filters', array(
'status' => array(
'operator' => '=',
'value' => 1,
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'status',
'table' => 'users',
'field' => 'status',
'relationship' => 'none',
),
'lid' => array(
'operator' => 'not empty',
'value' => array(
'value' => '',
'min' => '',
'max' => '',
),
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'lid',
'table' => 'location',
'field' => 'lid',
'relationship' => 'none',
),
'rid' => array(
'operator' => 'or',
'value' => array(
'3' => '3',
),
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'rid',
'table' => 'users_roles',
'field' => 'rid',
'relationship' => 'none',
'reduce_duplicates' => 0,
),
'distance' => array(
'operator' => 'mbr',
'value' => array(
'latitude' => '',
'longitude' => '',
'postal_code' => '',
'country' => '',
'search_distance' => '100',
'search_units' => 'mile',
),
'group' => '0',
'exposed' => TRUE,
'expose' => array(
'use_operator' => 0,
'operator' => 'distance_op',
'identifier' => 'distance',
'label' => '',
'optional' => 0,
'remember' => 1,
),
'type' => 'postal_default',
'identifier' => 'dist',
'id' => 'distance',
'table' => 'location',
'field' => 'distance',
'relationship' => 'none',
'override' => array(
'button' => 'Override',
),
),
'field_tech_platforms_value_many_to_one' => array(
'operator' => 'or',
'value' => array(),
'group' => '0',
'exposed' => TRUE,
'expose' => array(
'use_operator' => 0,
'operator' => 'field_tech_platforms_value_many_to_one_op',
'identifier' => 'field_tech_platforms_value_many_to_one',
'label' => 'Platforms',
'optional' => 1,
'single' => 0,
'remember' => 1,
'reduce' => 0,
),
'id' => 'field_tech_platforms_value_many_to_one',
'table' => 'node_data_field_tech_platforms',
'field' => 'field_tech_platforms_value_many_to_one',
'relationship' => 'content_profile_rel',
'reduce_duplicates' => 1,
),
'field_tech_services_value_many_to_one' => array(
'operator' => 'or',
'value' => array(),
'group' => '0',
'exposed' => TRUE,
'expose' => array(
'use_operator' => 0,
'operator' => 'field_tech_services_value_many_to_one_op',
'identifier' => 'field_tech_services_value_many_to_one',
'label' => 'Services',
'optional' => 1,
'single' => 0,
'remember' => 1,
'reduce' => 0,
),
'id' => 'field_tech_services_value_many_to_one',
'table' => 'node_data_field_tech_services',
'field' => 'field_tech_services_value_many_to_one',
'relationship' => 'content_profile_rel',
'reduce_duplicates' => 1,
),
'field_tech_payment_types_value_many_to_one' => array(
'operator' => 'or',
'value' => array(),
'group' => '0',
'exposed' => TRUE,
'expose' => array(
'use_operator' => 0,
'operator' => 'field_tech_payment_types_value_many_to_one_op',
'identifier' => 'field_tech_payment_types_value_many_to_one',
'label' => 'Payment types',
'optional' => 1,
'single' => 0,
'remember' => 1,
'reduce' => 0,
),
'id' => 'field_tech_payment_types_value_many_to_one',
'table' => 'node_data_field_tech_payment_types',
'field' => 'field_tech_payment_types_value_many_to_one',
'relationship' => 'content_profile_rel',
'reduce_duplicates' => 1,
),
));
$handler->override_option('access', array(
'type' => 'none',
));
$handler->override_option('items_per_page', 15);
$handler->override_option('style_options', array(
'grouping' => 'name',
));
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('path', 'location/users');
$handler->override_option('menu', array(
'type' => 'normal',
'title' => 'Technician List',
'description' => '',
'weight' => '0',
'name' => 'navigation',
));
$handler->override_option('tab_options', array(
'type' => 'none',
'title' => '',
'description' => '',
'weight' => 0,
));

**************************************************************
...and the Query it shows is:

SELECT users.uid AS uid,
users.name AS users_name,
votingapi_cache_node_average.value AS votingapi_cache_node_average_value,
node_users.type AS node_users_type,
node_users.nid AS node_users_nid,
node_users.vid AS node_users_vid,
node_data_field_tech_other_platform.field_tech_other_platform_value AS node_data_field_tech_other_platform_field_tech_other_platform_value,
location.city AS location_city,
location.postal_code AS location_postal_code,
users.picture AS users_picture,
node_data_field_tech_other_platform.field_tech_about_value AS node_data_field_tech_other_platform_field_tech_about_value,
node_data_field_tech_other_platform.field_other_payment_options_value AS node_data_field_tech_other_platform_field_other_payment_options_value,
node_data_field_tech_other_platform.field_goods_services_value AS node_data_field_tech_other_platform_field_goods_services_value,
(IFNULL(ACOS(0.88280952152919*COS(RADIANS(location.latitude))*(0.13127714731229*COS(RADIANS(location.longitude)) + -0.99134570690226*SIN(RADIANS(location.longitude))) + 0.46973114512176*SIN(RADIANS(location.latitude))), 0.00000)*6373399.971531) AS location_distance_13
FROM users users
INNER JOIN node node_users ON users.uid = node_users.uid AND node_users.type = 'technician_registration'
LEFT JOIN votingapi_cache votingapi_cache_node_average ON .nid = votingapi_cache_node_average.content_id AND (votingapi_cache_node_average.content_type = 'node' AND votingapi_cache_node_average.function = 'average')
LEFT JOIN location_instance location_instance ON users.uid = location_instance.uid
LEFT JOIN location location ON location_instance.lid = location.lid
INNER JOIN users_roles users_roles ON users.uid = users_roles.uid
LEFT JOIN content_type_technician_registration node_data_field_tech_other_platform ON node_users.vid = node_data_field_tech_other_platform.vid
WHERE (users.status <> 0) AND (location.lid IS NOT NULL) AND (users_roles.rid = 3) AND (location.latitude > 26.570070475415 AND location.latitude < 29.463621524585 AND location.longitude > -84.095480225279 AND location.longitude < -80.817719774721)
ORDER BY location_distance_13 ASC

*******************************************************

Comments

daneyuleb’s picture

daneyuleb commented

A little more info. The problem seems to be this segment:
LEFT JOIN votingapi_cache votingapi_cache_node_average ON .nid =

Before updating views, that segment was:
LEFT JOIN votingapi_cache votingapi_cache_node_average ON node_users.nid =

The "node_users.nid" was truncated to just ".nid".

Any idea what would cause this?

I will post at the votingapi issue queue too, since it seems to be associated with votingapi, but I did not update that module, so I'm not sure why it would occur now. Any suggestions on the cause, even if to confirm it's a votingapi thing, would be greatly appreciated.

dawehner’s picture

dawehner
Primary language German
commented
Status: Active » Closed (duplicate)

so this is part of votingapi.

you posted the same request again so this is marked as duplicate

daneyuleb’s picture

daneyuleb commented

>> so this is part of votingapi.

Sorry. Are you stating this after looking at the error information I posted above, or are you just reacting to the fact that I posted in the VotingAPI thread? Based on the terse phrasing, I'm not sure which applies.

I was hoping for some confirmation that indeed, based on my previous posting of query and error info, votingapi is most likely the culprit. I'm by no means sure since I got the problem after updating views from 2.3 to 2.5, not after updating votingapi or any other modules.

daneyuleb’s picture

daneyuleb commented
Status: Closed (duplicate) » Active

Hello again,

I have narrowed down the change in views that preceded my problem to be in the 2.3 to 2.4 update, specifically in the query.inc file, in the add_relationships function. For whatever reason, the below change to that inc file caused the sql error I mentioned in my first post in this thread. Changing the below snippit back to the 2.3 incarnation makes the error go away. I would appreciate any informed opinion as to what problem the below change might have revealed. While I have posted a mention of this in votingapi's issue queue (with no response) I'm posting here as I'm simply not sure votingapi is the issue. I'm therefore asking for some clear guidance as to if that module is likely the problem. As such, this is not a duplicate issue--although if you think I should be posting in another module's issue queue, please let me know what leads to that conclusion.

The key seems to be the if statement ( if ($alias = $this->add_table($join->table, $link_point, $join, $alias)) that was removed.

# The 2.4 query inc made the following change from 2.3--(basically, adding the $this->table_queue assignment, and removing the if statement from the $this->relationships assignment):

// Add the table directly to the queue to avoid accidentally marking
// it.
$this->table_queue[$alias] = array(
'table' => $join->table,
'num' => 1,
'alias' => $alias,
'join' => $join,
'relationship' => $link_point,
);

$this->relationships[$alias] = array(
'link' => $link_point,
'table' => $join->table,
'base' => $base,
);
return $alias;

return $alias;

/*
if ($alias = $this->add_table($join->table, $link_point, $join, $alias)) {
}
*/

####### Putting the original if statement before all of the modified code corrects the problem I'm having:

if ($alias = $this->add_table($join->table, $link_point, $join, $alias)) {
// Add the table directly to the queue to avoid accidentally marking
// it.
$this->table_queue[$alias] = array(
'table' => $join->table,
'num' => 1,
'alias' => $alias,
'join' => $join,
'relationship' => $link_point,
);

$this->relationships[$alias] = array(
'link' => $link_point,
'table' => $join->table,
'base' => $base,
);
return $alias;
}

dargrego’s picture

dargrego commented

Hi,

I have similar problem after updating Views 2.3 to 2.4 and then to 2.5 and even to latest dev version:

INNER JOIN node node_users ON .uid = node_users.uid AND node_users.type = 'customer_data' (posted here) - adding relationship breaks my view. I didn't change or update anything else, only Views.

daneyuleb’s picture

daneyuleb commented

Hi dargod,

Have you tried the modification I mentioned in #4? And if so, did it get rid of the problem for you, too?

dargrego’s picture

dargrego commented

Hello daneyuleb,

I made this modification as you suggested, upgraded to 2.5 and it seems to work - my views with relationships work again. I want to test it more carefully tomorrow, I write you about it.

daneyuleb’s picture

daneyuleb commented
Title: Error after updating to 2.5 » Error after updating from 2.3 to 2.4 (or 2.5)

Cool. That at least lets me know it's likely not limited to a votingapi problem--either the Views code change itself is the problem, or more than one module's problems with query generation is being outed by that change.

(Changed the title of this issue to reflect the problem was first seen in the 2.4 update)

dargrego’s picture

dargrego commented

Hello again daneyuleb,

I can confirm, your tip repaired my views. It does not relate to votingapi only, I have not votingapi intalled. I have a view with relationships to Signup:Node, Signup:User and
my problem occured after adding relationship to content_profile - (Signup user) Node: Content Profile.

Thank you for your help, I hope your suggestion will be taken into consideration in next version.

Flying Drupalist’s picture

Flying Drupalist commented

Isn't this a bug report?

Junro’s picture

Junro commented
Category: support » bug
Priority: Normal » Critical

Sure it is!

Subscribe, critical because I had to come back to 2.3 version. :)

gunzip’s picture

gunzip commented

duplicate of http://drupal.org/node/442776

dawehner’s picture

dawehner
Primary language German
commented
Status: Active » Closed (duplicate)

marking this a duplicate

@gunzip you can set the status to duplicate, to one of the two issues, thx for helping out :)!!

Flying Drupalist’s picture

Flying Drupalist commented

How does this duplicate thing work? This issue is older, longer, and is more informative. Why is this the duplicate?

Junro’s picture

Junro commented

Yep, it shouldn't be a duplicate, Merlinthemagician or others views devellopers will not check it if is a duplicate I think... to much issues, so much work :)

The duplicate is the newest issue created...

The oldest issue is the good one, but there are not rules written about it lol

:)

gunzip’s picture

gunzip commented
Status: Closed (duplicate) » Needs work

i agree with #14 so i mark the other issue as a duplicate (i opened that one) and as this contains the fix set this one as "needs work"

dww’s picture

dww
we/he/they
commented
Title: Error after updating from 2.3 to 2.4 (or 2.5) » SQL error from missing table alias (".nid") after upgrading to 2.4 or 2.5
Status: Needs work » Closed (duplicate)

@all:

1) When you post queries or exported views, please wrap them inside <code> tags, so that the formatting is preserved.

2) Yes, the oldest issue that reports a problem should be the active issue, all later issues should be marked as duplicates.

3) Please use descriptive, specific titles for the issue. "Error running 2.5" is pretty useless when skimming the issue queue looking for known bugs. Say what *kind* of error you're having, and it's more likely other people will find the same issue and add more information, instead of having 5 or 6 duplicates of "error".

4) When you're referring to other issues, it's easier and better to use this:

#442776: views 2.5 sql error "ON .uid = node_users.uid" ... has empty table.

which is automatically converted into

#442776: views 2.5 sql error "ON .uid = node_users.uid" ... has empty table.

instead of just:

http://drupal.org/node/442776

See how much more information and more useful that is?

5) #433290: Fatal SQL errors caused by broken table aliases in some cases in add_relationships() is older (and now explains the source of the problem), so that's the "real" issue for this bug, and this one is also duplicate... ;)

daneyuleb’s picture

daneyuleb commented

Thanks for the 'code' tip. Is there a place where those kind of rules are put together in one place on drupal.org, or is this just something that must be gleaned over time?

3) Please use descriptive, specific titles for the issue. "Error running 2.5" is pretty useless when skimming the issue queue looking for known bugs. Say what *kind* of error you're having, and it's more likely other people will find the same issue and add more information, instead of having 5 or 6 duplicates of "error".

Of course you should be informative as possible. The problem is, often the "kind" of error is often not readily known. I had no idea it had anything to do with broken table aliases or user relationships. I suppose I should have said "MySql" error in the original title, rather than just "Error when updating to Views 2.4" but other than that, more specificity would have been a complete shot in the dark. And if I had made assumptions about what it seemed like at first (ie"Voting API query error after updating to 2.4") I'd have misled everyone. Some generality (within reason) in titles can be a good thing until the cause is narrowed down. In fact, #433290: Fatal SQL errors caused by broken table aliases in some cases in add_relationships() with its original title of "Bad Table Name on Left Join" seems to be an example of someone trying to be too specific. I know I didn't find that post in my search for similar problems because that title didn't seem to relate to me at all.

karens’s picture

karens commented

No one expects you to know the source of the problem, but the point is that the issue queues are far easier to manage (and it's easier for other people to see if they have the same problem) if they have titles that give some clue to what kind of problems they address. You have two choices that don't require you to solve your own problem but still provide useful information:

1) Include a meaningful snippet from the error message. It doesn't have to be the whole error message, that would be too long. In this case there wasn't a lot to use, but you could have used something like 'error in SQL syntax...'. If the error mentions a file name and line number, adding that info can be useful.

- AND/OR -

2) Include some meaningful information about what you were doing when the error occurred. In this case you could have used 'when accessing my view after upgrading Views'

Putting them together without knowing anything about how to solve the problem, a good descriptive title would have been:

'Error in SQL syntax when accessing view after upgrading Views to 2.5'

Other people who *do* know more about what might behind your error can then follow behind and further adapt the title to be more specific. In this case, dww had an idea what the real problem was and could change the title appropriately.

It would be great to have a document somewhere about 'best practices when reporting issues in the issue queue'. Maybe you can see if there already is one, and if not, add one, using what you've learned here :)