Query returns no results in WFA but returns correct result when run directly against the DB

[MATTHEWKOOSWN]

Hi there,

I am running the following SQL query to automatically determine the correct value for an input parameter.  The query will discover the Netapp cluster based on a selected VM datastore.  The query runs fine when run directly against the database, however, when run from WFA it returns no results.  Is there a character limit in WFA that may be preventing it from getting the correct result?  The query runs fine in WFA only if the datastore name is shorter than the one in the example case I have pasted below.

SELECT

    a.name

from

    cm_storage.cluster a

inner join

    (

        SELECT

            a.name,

            a.cluster_id

        FROM

            cm_storage.vserver a

        inner join

            (

                SELECT

                    a.name,

                    a.vserver_id

                FROM

                    cm_storage.volume a

                inner join

                    (

                        SELECT

                            DISTINCT REPLACE(b.share_path,

                            '/',

                            '') as share,

                            a.name

                        FROM

                            vc.data_store a

                        join

                            vc.nas_share b

                                ON a.id = b.data_store_id

                        ) b

                            on a.name = b.share

                    where

                        b.name = 'WDGT1OS01SRM_clone_mkoo_ssumner'

                    ) b

                        on a.id = b.vserver_id

                where

                    a.name not like '%DRN0%'

            ) b

                on a.id = b.cluster_id

[coreywanless]

Within WFA, are you passing a user input variable in your "where b.name" statement?  If so, what is the query for that statement? 

[MATTHEWKOOSWN]

Yes, the datastore is a user selectable list that is built by simply querying for all the datastores that are not clones:

SELECT
name
FROM
vc.data_store
where
type = 'NFS'
and name like '%clone%'
order by
name;

That is cascaded into the parameter in question as '${datastore}';  I replaced the variable with the actual value and it returns no results in WFA but returns correctly in MySQL workbench running the query against the DB.  When I modify the query to specify a datastore with a shorter name it succeeds in returning the correct result in WFA. 

[sinhaa]

Matt,

Mysql clients sometimes resolve some things which a native mysql query would not be able to. Not this case, but I've seen other cases where mysql client return different data as  they can resolve some things. Thats why sometimes some things work on clients like mysql workbench but not at WFA end.

Let's see what's happening here. I want to execute the query keeping WFA and Mysql Clients aside. Just a native code to run query. It has no built-in intelligence to do anything.

Can you try the following:

1.  On your WFA windows server open a powershell ISE console.

Import-module 'C:\Program Files\NetApp\WFA\PoSH\profile.ps1'

If you have default installation else edit it accordingly. Now define variable for query

$query="SELECT
a.name
from
cm_storage.cluster a
inner join
(
SELECT
a.name,
a.cluster_id
FROM
cm_storage.vserver a
inner join
(
SELECT
a.name,
a.vserver_id
FROM
cm_storage.volume a
inner join
(
SELECT
DISTINCT REPLACE(b.share_path,
'/',
'') as share,
a.name
FROM
vc.data_store a
join
vc.nas_share b
ON a.id = b.data_store_id
) b
on a.name = b.share
where
b.name = 'WDGT1OS01SRM_clone_mkoo_ssumner'
) b
on a.id = b.vserver_id
where
a.name not like '%DRN0%'
) b
on a.id = b.cluster_id"

Execute the query.

Invoke-MySqlQuery -Query $query

If you are getting 0 results, then there is something wrong with your query which somehow Mysql workbench is able to resolve. If you get data same as mysql workbench and still nothing at WFA, WFA needs to be debugged.

Waiting for your response.

sinhaa

[MATTHEWKOOSWN]

Hi Sinhaa,

When running the query from powershell on the WFA server it does return the correct value, the result is the same when I run the query from MySQL workbench:

PS C:\Users\koomadm> $query="SELECT a.name from cm_storage.cluster a inner join (SELECT a.name, a.cluster_id FROM cm_sto
rage.vserver a inner join (SELECT a.name, a.vserver_id FROM cm_storage.volume a inner join ( SELECT DISTINCT REPLACE(b.s
hare_path, '/', '') as share, a.name FROM vc.data_store a join vc.nas_share b ON a.id = b.data_store_id ) b on a.name =
b.share where b.name = 'WDGT1OS01SRM_clone_mkoo_ssumner' ) b on a.id = b.vserver_id where a.name not like '%DRN0%' ) b o
n a.id = b.cluster_id"
PS C:\Users\koomadm> Invoke-MySqlQuery -Query $query
1

name
----
HOSTOCLPDN01

When I run this query via WFA it returns no results.  However, if I replace the datastore name with another that is shorter then WFA will return the correct result.

[sinhaa]

Matt,

      Okay so now that it works fine outside of WFA/mysql clients, the issue has to be with WFA. I would suggest you open a NetApp support case, provide your AutoSupport data.

We can debug this there on. We will get back as  soon as possible.

Till then your inconvinience is regretted.

sinhaa

[MATTHEWKOOSWN]

OK, just opened case 2006192616 and sent autosupport data.

---

@sinhaa wrote:

Matt,

      Okay so now that it works fine outside of WFA/mysql clients, the issue has to be with WFA. I would suggest you open a NetApp support case, provide your AutoSupport data.

We can debug this there on. We will get back as  soon as possible.

Till then your inconvinience is regretted.

sinhaa

---