EDM SQL syntax error

[Nick_Elliott]

I have a customer that upgraded their WFA instance to 4.0.  Since then, they have received the following error message on an xlsx data source that had been working prior to the upgrade:

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 'primary FROM dsny' at line 1
Query is:
SHOW FULL COLUMNS FROM primary FROM dsny

I assume the correct query should be 

show full COLUMNS from dsny.primary;

I have made no changes to the original delivered code which was working in WFA 3.x.

Anyone experince a problem like this with the EDM pack and WFA before?  Any ideas how to clear it?

[Nick_Elliott · Accepted solution]

Discovered issue is with use of a SQL reserved word in the delivered xlsx sheet and the dictonary.  Reserved word was "primary" which didn't cause problems prior to WFA 4.x.

Case sensitivity or MySql upgrade cause break?

[dkorns]

Hi Nick,

I just saw this ... I guess 'primary' was a bad name choice. I don't have a WFA 4.0 system but I do have a 4.1rc1 system and I get the exact same error from the datasource.

You've probably figured this out by now, but rename the 'primary' tab in .xlsx and the 'primary' dictionary to something like 'primary_env'. That gets the datasource working properly. Then there is changing all other references to that table. You can use the upper search box in WFA to search for 'primary env' and you'll get some workflows, and filters that reference that old 'primary' table which will need chnaged. Oddly enough, in the case of filters, they run error-free but return no results ... until the SQL is updated from 'primary' to 'primary_env' (or similar), then they work again

From looking at WFA-3.1p2 and WFA-4.1rc1 I see these MySql versions  ... according to the select version(); command (using MySql Workbench) I used to check this:

• wfa 3.1p2 comes with MySql 5.6.26
• wfa 4.1rc1 comes with MySql 5.6.34

I could not determine if/when 'primary' became a reserved word. From what I looked at it was reserved in 5.6 in general (docs where not specific to 5.6.nn) ... so sort of surprised in worked in wfa-3.x