SQL errors in Project Nami/MSSQL

[Andrew McChesney]

I am trying to run The Events Calendar v4.6 on Project Nami v1.6.2, which uses WordPress v4.8.2. Project Nami uses MS SQL instead of MySQL. My install is hosted on Microsoft Azure.

I have disabled all plugins except The Events Calendar, and switched to the latest version of the Twenty Fourteen Theme (also tried with with the 2016 theme). I have turned on WP_DEBUG and WP_DEBUG_DISPLAY in wp-config.php. I see one SQL error when viewing any page in the admin:

WordPress database error: [[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid column name 'wp_postmeta'. Code - 207]
SELECT DISTINCT wp_posts.*, CASE WHEN wp_posts.post_type = 'tribe_events' THEN wp_postmeta.meta_value ELSE wp_posts.post_date END AS post_date , wp_posts.post_date FROM wp_posts LEFT JOIN wp_postmeta as wp_postmeta on wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = '_EventStartDate' WHERE 1=1 AND wp_posts.post_name = 'events' AND wp_posts.post_type IN ('post', 'page', 'attachment', 'tribe_events', 'tribe-ea-record') AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'inherit' OR wp_posts.post_status = 'private')) ORDER BY post_date, wp_postmeta DESC

If I go to the “view all events” page in the admin, I see a 2nd error as well:

WordPress database error: [[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]ORDER BY items must appear in the select list if SELECT DISTINCT is specified. Code - 145]
SELECT DISTINCT wp_posts.ID FROM wp_posts 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' ) LEFT JOIN wp_postmeta AS tribe_event_start_date ON wp_posts.ID = tribe_event_start_date.post_id AND tribe_event_start_date.meta_key = '_EventStartDate' WHERE 1=1 AND wp_posts.post_type = 'tribe_events' AND ((wp_posts.post_status = 'tribe-ignored')) ORDER BY tribe_event_start_date.meta_value DESC, tribe_event_end_date.meta_value DESC, wp_posts.post_date DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Can you advise on how to resolve these errors?

[Barry]

Hi Andrew,

Thanks for contacting us.

Officially, we support the same stack as WordPress itself and – at this time – that does not include MS SQL.

I cannot replicate those same errors on a standard installation and, given your notes, that makes me think that perhaps the complicating factor is the translation layer provided by Project Nami rather than a bug within The Events Calendar (if you’ve got any thoughts to the contrary or can guide me in a way that would allow me to reproduce on a standard installation running on top of MySQL, though, definitely let me know).

We’re certainly not closed to the idea of improving compatibility, but at this time I’m not sure we see quite enough demand for this to invest in it ourselves.

There are a few ways forward from here:

• You could submit a request on our UserVoice page for Project Nami compatibility – as others can up-vote requests this is really helpful in terms of letting us gauge demand
• If you have specific ideas as to how we could change our code to make it compatible with Project Nami (bearing in mind, it would need to be something that is feasible for us as a MySQL-focused team to maintain) then we’d be open to discussing a pull request should you wish to submit one
• It looks like your purchase of an Events Calendar PRO license was very recent: if you need to make things work in the short term and cannot move to a MySQL-based stack, we’d certainly be happy to provide you with a refund should you wish to re-invest in a different solution that better meets your needs

We’d be sorry to lose you, but realistically I’m not sure how much we can do for you at this point. I hope the above at least provides you with some options you can take forward, though 🙂

[Andrew McChesney]

Thank you for your reply. I understand MSSQL is not entirely supported or widely used for WordPress, but I’m required to use it given my company’s IT infrastructure requirements.

It appears that the SQL error displayed on all pages of the WordPress admin is generated by the “tribe_notice” function called in line 687 of src/Tribe/Main.php. If I comment out lines 687-689 (in The Events Calendar v4.6.1) those errors go away. I believe that normally those lines cause the display of an admin notice about slug conflicts. There are comments in the code about “@todo remove this safety check when we’re confident the risk has diminished”. Perhaps the risk has diminished enough at this point to remove these lines from the next release?

The 2nd SQL error that appears in the admin when viewing the “All Events” screen is:
SELECT DISTINCT wp_posts.ID FROM wp_posts 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' ) LEFT JOIN wp_postmeta AS tribe_event_start_date ON wp_posts.ID = tribe_event_start_date.post_id AND tribe_event_start_date.meta_key = '_EventStartDate' WHERE 1=1 AND wp_posts.post_type = 'tribe_events' AND ((wp_posts.post_status = 'tribe-ignored')) ORDER BY tribe_event_start_date.meta_value DESC, tribe_event_end_date.meta_value DESC, wp_posts.post_date DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

The error is due to:
[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]ORDER BY items must appear in the select list if SELECT DISTINCT is specified. Code - 145.

I would rewrite the query, except I can’t figure out where it’s being generated from. The WP debug bar provides this info below the query:

WP_List_Table->views, apply_filters('views_edit-tribe_events'), WP_Hook->apply_filters, Tribe__Events__Ignored_Events->filter_views, Tribe__Events__Ignored_Events->has_ignored_posts, Tribe__Events__Ignored_Events->get_query, WP_Query->__construct, WP_Query->query, WP_Query->get_posts

Disabling the “views_edit-tribe_events” filter doesn’t solve the problem.

I added a Project Nami Support suggestion to the UserVoice page… hopefully other users who are required to use MSSQL will vote it up, and Nami compatibility will be added to your test suite.

Thanks for making a great events calendar plugin.

• This reply was modified 8 years, 7 months ago by Andrew McChesney.

[Barry]

Thanks for the notes, Andrew.

Perhaps the risk has diminished enough at this point to remove these lines from the next release?

To clarify this a little: the to-do item related to the extra safety check. If we do revisit and decide there’s no longer any risk, then we would still make the tribe_notice() call (it just wouldn’t be “wrapped” up within an if block).

I would rewrite the query, except I can’t figure out where it’s being generated from

A large number of the queries that run are composed dynamically and I’m unable to point you to a piece of source code where you’d see it expressed in a single block, because that’s not quite how it works.

You could probably capture and alter each query before it’s executed (I imagine this is what Project Nami itself does, to translate between SQL dialects) but that’s far beyond what we can help you with.

I added a Project Nami Support suggestion to the UserVoice page… hopefully other users who are required to use MSSQL will vote it up, and Nami compatibility will be added to your test suite.

Fantastic, thanks for doing that. It will be interesting to keep an eye on that and build a sense of how many users out there might also like to see this.

For the time being, though, I’m sorry to say we can’t assist further with Nami/MS SQL specific issues.

Thanks again and I do wish you luck with your project.

[Support Droid]

Hey there! This thread has been pretty quiet for the last three weeks, so we’re going to go ahead and close it to avoid confusion with other topics. If you’re still looking for help with this, please do open a new thread, reference this one and we’d be more than happy to continue the conversation over there.

Thanks so much!
The Events Calendar Support Team

[Author]

Posts