Active IQ Unified Manager Discussions

EDM SQL syntax error

Nick_Elliott
2,877 Views

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?

1 ACCEPTED SOLUTION

Nick_Elliott
2,858 Views

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?

View solution in original post

2 REPLIES 2

Nick_Elliott
2,859 Views

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
2,824 Views

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

 

Public