SQL error syntax triggered by Monthly view

[aafhhl]

Display of the page which is displaying monthly view calendar is triggering an SQL error syntax in log file.

This error is added in log file at every call of this monthly view calendar;
apparently on field name is unknown or badly spelt …

[Thu May 19 01:44:52.114080 2016] [:error] [pid 19931] [client 192.168.0.216:48663]
Erreur de la base de donn\xc3\xa9es WordPress
Unknown column ‘home_postmeta.meta_value’ in ‘field list’ pour la requ\xc3\xaate

SELECT DISTINCT home_posts.*, MIN(home_postmeta.meta_value) as EventStartDate, MIN(tribe
_event_end_date.meta_value) as EventEndDate
FROM home_posts
INNER JOIN home_term_relationships ON (home_posts.ID = home_term_relationships.object_id)
LEFT JOIN home_postmeta as tribe_event_end_date ON ( home_posts.ID = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = ‘_EventEndDate’ )
WHERE 1=1 AND ( \n home_term_relationships.term_taxonomy_id IN (67)\n) AND home_posts.post_type = ‘tribe_events’ AND (home_posts.post_status = ‘
publish’ OR home_posts.post_status = ‘complete’ OR home_posts.post_status = ‘paid’ OR home_posts.post_status = ‘confirmed’ OR home_posts.post_status =
‘unpaid’ OR home_posts.post_status = ‘pending-confirmation’ OR home_posts.post_status = ‘cancelled’)
GROUP BY home_posts.ID ORDER BY EventStartDate DESC, home_posts.post_date DESC LIMIT 0, 1
faite par require(‘wp-blog-header.php’), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts

[aafhhl]

This reply is private.

[Brook]

Howdy aafhhl,

I would love to help you with this.

It looks like another plugin is interfering with the SQL query. I’m assuming the \n that is twice present in that query is also present in your error message. It seems the likely cause of this SQL error.

In order to proceed we will need to test if a “conflict” is happening, and if so narrow it down so we can find a fix for it. This guide walks you through how to test for a conflict, and then identify what is conflicting. I realize you have a lot of plugins and this test might be a pain. If I were in your shoes I would first try disabling any plugins that impact things sitewide. Like WooCommerce ones are usually quite isolated to the shop pages, whereas security plugins and such often make sitewide changes. Further you might focus on anything that has to do categories or taxonomies since the \n surrounds a category/taxonomy section. But if disabling all of the more suspect plugins does not cause this to go away we will need to try disabling all of them and the theme as the guide outlines.

Once you have done that test, what’s the result? Is there a plugin I can research to see if we can make it compatible with The Events Calendar ?

Cheers!

– Brook

 

[aafhhl]

Hi,

I had more investigation in the SQL request code and found the problem origin :

In field list of the SQL request, your code is using real table name (home_postmeta) instead of its alias table (tribe
_event_end_date) as this alias is generated in left outer join clause :
LEFT JOIN home_postmeta as tribe_event_end_date

To fix the code, just replace :
SELECT DISTINCT home_posts.*, MIN(home_postmeta.meta_value) as EventStartDate, MIN(tribe
_event_end_date.meta_value) as EventEndDate

by this :
SELECT DISTINCT home_posts.*, MIN(tribe_event_end_date.meta_value) as EventStartDate, MIN(tribe
_event_end_date.meta_value) as EventEndDate

And request is well executed in PHPmyadmin

Next step is now for your dev team :
Find where is generated this SQL request and fix the generation code

[Brook]

Howdy aafhhl,

I appreciate your digging into this. I don’t think that will do quite what you expect, you are swapping the start date for the end date in your replacement code. Further we have not seen this happen on any of our test systems thus far. It would seem likely that something unique about your setup is causing the query to go awry, and usually the thing most unique to a given site is its combination of plugins and theme. That’s why I was asking you to do a conflict test.

I too researched this on my end, examined the query and toyed with it until I got it to work. But it is still quite unclear as to why it is getting malformed in the first place. Hence why I think our logical next step is the conflict test.

Cheers!

– Brook

[aafhhl]

I cannot have an advice about functional aspects of this request (End date vs Begin date)

But what is sure is that using a real table name when alias have been declared for all tables included in a SQL request always leads to syntax error when using the field name prefixed by real table name
And this is exactly what is done in this request.

This SQL request is generated and fired each time the monthly view af calendar is displayed,
then can you point me on the module where this SQL request is generated and executed.

I will have more investigation.
If I don’t find the root cause there, I will go to the conflict study you suggest

Thanks for cooperation

[Brook]

Absolutely! That specific portion of the SQL likely stems from: /the-events-calendar/src/Tribe/Query.php . Around line 429 you will see the method Tribe__Events__Query::posts_fields() . But of course as with any portion of a WP_Query  there are a million different actions that other plugins will tap into and alter things, so it could even be coming from a different plugin altogether which is overriding our own. You will note that at times the variable $postmeta_table is an alias, and neither myself nor our senior dev was able to identify anytime this function would run when that variable is not an alias.

And I agree that this table should generally be aliased. But, in our tests it is always an alias. We are not in the habit of implement changes without fully understanding a problem, especially because changes can often introduce new issues for other configurations. In this case we need to know specifically what plugin is causing this code to run in an unintended fashion, then we can start searching for why. Once we know both of those things we can investigate fixing it. Until then there is not much we can do. Hence why I was interested in having you do a conflict test. If you do not wish to do one that is totally fine, I don’t mean to force your hand here. But, it is unlikely we will ever be able to release updated code for that function without first examining the exact circumstance that cause it to malfunction.

Thanks for your understanding Aafhhl. As always let me know if you have any questions. Cheers!

– Brook

[aafhhl]

Hi,

I had more investigations and identified the root cause of this problem.

The faulty request, see end of this post for example, is generated by this module :

wp-content/plugins/the-events-calendar/src/Tribe/Query.php

The faulty request field :
MIN(home_postmeta.meta_value) as EventStartDate,

is generated by this line of code :
$fields[‘event_start_date’] = “MIN({$postmeta_table}.meta_value) as EventStartDate”;

When you read the whole request, you can see than the home_postmeta table is only declared in the FROM clause
to be used for EventEndDate extraction
not for EventStartDate extraction
and then as a consequence the reference to home_postmeta in field name is related to an undeclared table

I suspect than you miss a second instance of this home_postmeta table to be declared as LEFT JOIN
with a condition built to target this _EventStartDate

You clearly have a problem in the logic generation of this SQL request.
and for me this is clearly a nasty BUG

==> Please forward to you DEV Team ==> Please forward to you DEV Team ==> Please forward to you DEV Team

——————————————————————————————————————-

SELECT DISTINCT
home_posts.*,
MIN(home_postmeta.meta_value) as EventStartDate,
MIN(tribe_event_end_date.meta_value) as EventEndDate
FROM home_posts
LEFT JOIN home_postmeta as tribe_event_end_date
ON ( home_posts.ID = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = ‘_EventEndDate’ )
WHERE 1=1 AND home_posts.post_type = ‘tribe_events’
AND (home_posts.post_status = ‘publish’ OR home_posts.post_status = ‘complete’ OR home_posts.post_status = ‘paid’ OR home_posts.post_status = ‘confirmed’
OR home_posts.post_status = ‘unpaid’ OR home_posts.post_status = ‘pending-confirmation’ OR home_posts.post_status = ‘cancelled’)
ORDER BY EventStartDate DESC, home_posts.post_date DESC LIMIT 0, 1

• This reply was modified 9 years, 11 months ago by aafhhl.

[Brook]

Howdy Aafhhl,

Sorry if I was unclear before. That is certainly true, that is what I was trying to explain to you yesterday:

You will note that at times the variable $postmeta_table is an alias, and neither myself nor our senior dev was able to identify anytime this function would run when that variable is not an alias.And I agree that this table should generally be aliased. But, in our tests it is always an alias.

Now we need to figure out why it is misbehaving. In your query it should not be “home_postmeta” it should be the alias “tribe_event_postmeta” set by the dynamic variable $postmeta_table. Clearly something on your site is causing that variable to be set wrong. We have no idea what is causing that to be wrong. Until we do know we can not fix it.

I am a developer of 9 years myself and that is what I discovered. But just in case I was wrong I still asked a senior dev to have a looksie at this thread and my discovery and he agreed. Until we know the cause of the problem, the circumstances required to reproduce it, there is not much more we can do here.

Cheers!

– Brook

[aafhhl]

Thanks for your answer.

But this is not consistent,
even if the $postmeta_table variable was populated with the correct alias (tribe_event_postmeta) by the function postmeta_table

This alias supposed to be used (tribe_event_postmeta) would not be associated to any table in the FROM clause of the SQL request
so this will lead to SQL syntax error
As a demonstration, I replaced
MIN(home_postmeta.meta_value) as EventStartDate,
by
MIN(tribe_event_postmeta.meta_value) as EventStartDate,
and submitted the whole request to my SQL console and obviously got a syntax error on this field name

So the next question is :
by which part of the Query.php code is supposed to be generated in the SQL request
the missing FROM declaration part corresponding to this tribe_event_postmeta alias table declaration ?

If this question is the good one, we are going back to my previous suspicion of missing table declaration in this request …

PS : I do respect you seniority,
if you want to know about me as Datawarehouse and Business Intelligence Expert :
https://www.linkedin.com/in/jrboulet

I just try to help to fix this bug because bugs have been invented to be fixed !!!
😉

[Brook]

by which part of the Query.php code is supposed to be generated in the SQL request
the missing FROM declaration part corresponding to this tribe_event_postmeta alias table declaration ?

In this case it is likely our JOIN code that is not running, as you suspected. This would be inserted by Tribe__Events__Query::posts_join() and it would look something like this in typical queries:

INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )

For example here is a similar query which runs the post_join method:

SELECT 
 SQL_CALC_FOUND_ROWS DISTINCT wp_posts.*, 
 MIN(wp_postmeta.meta_value) as EventStartDate, 
 MIN(
 tribe_event_end_date.meta_value
 ) as EventEndDate 
FROM 
 wp_posts 
 INNER JOIN wp_postmeta ON (
 wp_posts.ID = wp_postmeta.post_id
 ) 
 LEFT JOIN wp_postmeta as tribe_event_end_date ON (
 wp_posts.ID = tribe_event_end_date.post_id 
 AND tribe_event_end_date.meta_key = '_EventEndDate'
 ) 
WHERE 
 1 = 1 
 AND (
 wp_postmeta.meta_key = '_EventStartDate'
 ) 
 AND wp_posts.post_type = 'tribe_events' 
 AND (
 wp_posts.post_status = 'publish' 
 OR wp_posts.post_status = 'private'
 ) 
 AND (
 wp_postmeta.meta_value >= '2016-06-01 15:00:06' 
 OR (
 wp_postmeta.meta_value <= '2016-06-01 15:00:06' 
 AND tribe_event_end_date.meta_value >= '2016-06-01 15:00:06'
 )
 ) 
GROUP BY 
 wp_posts.ID 
ORDER BY 
 EventStartDate ASC, 
 wp_posts.post_date ASC 
LIMIT 
 0, 15

But you will note some substantial deviations between that query and your own. Indeed we are not completely sure that the query you have shared is even started by our plugin. There are so many things about it that are odd and not consistent with our usual queries.

At the outset you said this happens simply by visiting Month view on your site. When we visit month view no query like this runs. Barry thought maybe you had some widgets on your site that were causing this to happen, but from the link you shared I am not seeing any.

I know you are puzzled by this and we are too. We are less puzzled by the fact that the query is not working, that is easily explained. What puzzles us is that it is running at all, and further what is it trying to accomplish? It is very likely that another plugin is either initiating this query or changing enough query vars that our Tribe__Events__Query::posts_fields() is running when it should not. Or perhaps something has simply dehooked or overridden our post_join filter. Either way it will be nigh impossible to fix without knowing what is causing this, and even then the only fix might be to disable the conflicting code.

PS : I do respect you seniority,
if you want to know about me as Datawarehouse and Business Intelligence Expert :
https://www.linkedin.com/in/jrboulet

I just try to help to fix this bug because bugs have been invented to be fixed !!!

I really appreciate this. I was not trying to brag but I did want to let you know I was not ignoring your request to consult the devs even though I am actually one of the plugin devs myself. We definitely want to patch any bugs we can and I am stoked you are going through such great lengths to try and help us find one.

Cheers!

– Brook

[Support Droid]

This topic has not been active for quite some time and will now be closed.

If you still need assistance please simply open a new topic (linking to this one if necessary)
and one of the team will be only too happy to help.

[Author]

Posts