|
Justin
|
August 22, 2023 at 12:22 pm
Hi there, our events page is missing events, and we’re getting the following error in our PHP logs:
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts INNER JOIN wp_esp_datetime ON ( wp_posts.ID = wp_esp_datetime.EVT_ID )
WHERE 1=1 AND ((wp_posts.post_type = 'espresso_events' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'limited' OR wp_posts.post_status = 'cancelled' OR wp_posts.post_status = 'postponed' OR wp_posts.post_status = 'sold_out'))) AND wp_esp_datetime.DTT_EVT_end > '2023-08-20 05:27:41'
GROUP BY wp_posts.ID
ORDER BY event_start_date DESC
LIMIT 0, 10
This is only happening on our production site, not our development environments, even with the same plugins activated.
When I look at the SQL being run on our dev environment, the issue is pretty clear: wp_posts.*, wp_esp_datetime.* , MIN( wp_esp_datetime.DTT_EVT_start ) as event_start_date is getting transformed to wp_posts.ID somehow.
I spent at least a couple hours debugging, and have searched our codebase for manipulations of the posts_fields filter, but it appears that Event Espresso is the only plugin doing changes there. Can you help me figure out what troubleshooting I should be doing next?
SELECT SQL_CALC_FOUND_ROWS wp_posts.*, wp_esp_datetime.* , MIN( wp_esp_datetime.DTT_EVT_start ) as event_start_date
FROM wp_posts
INNER JOIN wp_esp_datetime
ON ( wp_posts.ID = wp_esp_datetime.EVT_ID )
WHERE 1=1
AND wp_posts.post_type = 'espresso_events'
AND ((wp_posts.post_status = 'publish'))
AND wp_esp_datetime.DTT_EVT_end > '2023-08-22 17:58:07'
GROUP BY wp_posts.ID
ORDER BY event_start_date ASC
LIMIT 0, 9
|
|
Justin
|
August 23, 2023 at 11:12 am
Just realized I failed to include the error message itself:
NOTICE: PHP message: WordPress database error Unknown column 'event_start_date' in 'order clause' for query
|
|
Brent Christensen
|
August 28, 2023 at 1:39 pm
Hi Justin,
I’m Event Espresso’s lead developer. We don’t use SQL_CALC_FOUND_ROWS anywhere in any of our plugins’ code. I’m pretty sure something else is hijacking that query and replacing the SELECT clause with SQL_CALC_FOUND_ROWS and thus removing where we define MIN( wp_esp_datetime.DTT_EVT_start ) as event_start_date. This is why you are getting the Unknown column 'event_start_date' in 'order clause' for query error.
I have over 100 plugins installed on my local WordPress development site, and the only one that uses SQL_CALC_FOUND_ROWS is Gravity Forms. Is that active on your site? The only other place I know of that uses SQL_CALC_FOUND_ROWS is WordPress Multi-site installations, but I’m going to assume that is not the case with your site. Are you able to deactivate other plugins one by one until you find the one that is hijacking our query?
|
|
Justin
|
August 28, 2023 at 2:15 pm
Hi Brian, thanks for looking at this. My understanding is that this behavior is baked into WP_Query(). get_posts() automatically does this unless you set 'no_found_rows'. https://github.com/WordPress/WordPress/blob/2213e52fd868c93c150b866c11a96b5c1e4a2282/wp-includes/class-wp-query.php#L3105C28-L3105C28
|
|
Brent Christensen
|
August 28, 2023 at 2:21 pm
who’s Brian? 😉
you are correct that it is baked into WP_Query, but notice that it is using !
if ( ! $q['no_found_rows'] && ! empty( $limits ) ) {
$found_rows = 'SQL_CALC_FOUND_ROWS';
}
therefore $q['no_found_rows'] must be set to false to trigger that.
I could be wrong, but the only place that I know of that sets that to false for the main query is multi-site installations.
|
|
Brent Christensen
|
August 28, 2023 at 2:32 pm
I missed that the first sentence of your initial post included a link to your events page so I only now visited your site to have a look.
I can see in the source code that Gravity Forms is running and that is the only plugin I know of that uses SQL_CALC_FOUND_ROWS. Have you tried deactivating Gravity Forms to see if that fixes the issue? If the problem only happens on your production site, you can do this to minimize any disruptions:
– open the WordPress plugins admin page in one tab in your browser
– in another browser tab, open your events list page
– deactivate the Gravity Forms
– switch to the other tab and refresh your events list page
– switch back to the plugins page and re-activate Gravity Forms
did your events list load correctly after doing the above?
|
|
Justin
|
August 28, 2023 at 3:20 pm
Whoops, my eyes must’ve glazed over and read “Brent” as “Brian”. Apologies.
I’ve tried deactivating Gravity Forms, and that did not solve the issue.
I think you’re getting the no_found_rows thing backwards. It defaults to false, which means it adds the SQL_CALC_FOUND_ROWS by default. It only skips SQL_CALC_FOUND_ROWS if no_found_rows is set to true.
|
|
Justin
|
August 28, 2023 at 3:23 pm
That doesn’t mean WP Core is responsible for mangling the query in my case, just that the culprit could be anything that filters on posts_fields.
|
|
Brent Christensen
|
August 28, 2023 at 5:31 pm
my apologies, you’re correct that the default value for $q['no_found_rows'] is indeed false. I was searching for places where it was being set within the query vars being passed to WP_Query. Makes sense that it’s mostly only ever set to true to override that default value.
Now that said, WP_Query does not replace the incoming post fields with SQL_CALC_FOUND_ROWS but instead prepends that to the query string. Both Tony and I have tried to recreate this locally and neither the Event Custom Post type archive page, nor the [ESPRESSO_EVENTS] shortcode result in the post fields getting replaced by SQL_CALC_FOUND_ROWS. I see the following in both cases:
SELECT SQL_CALC_FOUND_ROWS wp_posts.*, wp_esp_datetime.* , MIN( wp_esp_datetime.DTT_EVT_start ) as event_start_date
Something else on your site is causing the above to get changed to:
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
whether that is some other plugin or an unconventional configuration of the Event Espresso plugin (because otherwise this would be more easily reproducible) remains to be seen.
Have you tried deactivating other plugins one by one and retesting your events list?
Do you have any custom code active on your site?
I think Tony is likely going to need access to your site to do some more poking around.
|
