Balance throws SQL error when collecting array data

[bachman]

I have a customer running a Balance POC that has hit a problem with Balance collecting data. Below is his explanation.

My first guess is that the "@" sign in the lun and qtree names is causing a problem, but I'm looking for anyone who may have encountered this before. Any ideas?

Phil

We finally managed  to get the OnCommand Balance appliance installed.

We don’t seem to be able to get very far with the product however.

We have been using Syncsorts backup product since the beginning of this year and it likes to create some … verbose(?) LUN and QTREE names.

Here are a couple of examples:

Syncsort created LUN path:

                /vol/dpx_vm12/[dpx_vm12]srv-vcenter5@3AFD8130/6000C29a-08b6-0958-8231-8add9383f756.LUN

Syncsort created Qtree:

                [dpx_vm11]srv-vcenter5@5696C992

These names seem to be giving the OnCommand Balance SQL a bit of a hard time and Balance does not seem to be able to ignore or get around the issue, so it cannot collect any data from the NetApp Storage arrays.

OnCommand Balance Collection Error:

Storage System Summary: sef-netapp01   

Open console  Refresh       Edit 

Storage System Nickname: 

sef-netapp01

       FQDN/IP Address:     10.126.10.20

Storage System Type: NetApp FAS    Partners IP Address: 10.126.10.22

Credentials:  NETAPP-cred-10.126.10.20   Statistics Collection:     Enabled

Using SSL:    true        

Discovery:    Error:Unknown Collection Error

Last Success:

Last Attempt: Today @ 4:34pm

Collection Service failed to process discovery collection. PreparedStatementCallback; bad SQL grammar select * from Element where elementTypeId=? and elementRelationshipKey regexp ^192.168.1.184=.*:/VOL/DCP01SQL_BU/ DPX_DCP01SQL SRV-DCP01SQL03@{96E2F5D7}$|^192.168.2.50=.*:/VOL/DCP01SQL_BU/ DPX_DCP01SQL SRV-DCP01SQL03@{96E2F5D7}$|^10.126.10.20=.*:/VOL/DCP01SQL_BU/ DPX_DCP01SQL SRV-DCP01SQL03@{96E2F5D7}$|^192.168.0.120=.*:/VOL/DCP01SQL_BU/ DPX_DCP01SQL SRV-DCP01SQL03@{96E2F5D7}$|^192.168.250.1=.*:/VOL/DCP01SQL_BU/ DPX_DCP01SQL SRV-DCP01SQL03@{96E2F5D7}$|^192.168.252.11=.*:/VOL/DCP01SQL_BU/ DPX_DCP01SQL SRV-DCP01SQL03@{96E2F5D7}$|^10.100.2.240=.*:/VOL/DCP01SQL_BU/ DPX_DCP01SQL SRV-DCP01SQL03@{96E2F5D7}$ and elementOpStatus <> 5 ; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Got error invalid repetition count(s) from regexp

Statistics:   OK

Last Success: Today @ 4:46pm

Last Attempt: Today @ 4:46pm

[plauterb]

EPIC SQL FAIL!

That does not look pretty at all.

There are no obvious known issues. Rather than speculate, a customer support case may yield the truth.

[bachman]

Case #2004507327 opened.

[mkahlke]

You appear to have whitespace in your mountpoint names which would confuse mysql into interpreting a mountpoint as several tokens instead of a single token.  Can you confirm that this is indeed the case?

In any event if you can open a support case then we can look at the logs and raw data and go from there.

[BMORYRTOGO]

There is no whitespace in the qtree paths. The whitespace you see exists only within the OnCommand Balance generated error.

The actual path on the controller is: /vol/dcp01sql_bu/[dpx_dcp01sql]SRV-DCP01SQL03@{96E2F5D7}

The path as shown in the Balance error message: /VOL/DCP01SQL_BU/ DPX_DCP01SQL SRV-DCP01SQL03@{96E2F5D7}

The Balance error output has replaced the square brackets with spaces.

[mkahlke]

I see the problem then.  The '[' and ']' characters are being treated as part of a regular expression pattern match, instead of literal characters.  I'll go ahead and put in code to escape these special characters if they appear in the path, that should take care of this problem.

Code freeze for the 4.1.1.1 patch release is COB tomorrow (Sept 4th), and we plan to release around Sept 19th at which point you can download from the NOW site.

[mkahlke]

PS: I created BURT 751191 (http://burtweb-prd.eng.netapp.com/burt/burt-bin/start?burt-id=751191) with Phil Bachman as the submitter and myself as the owner.  I'll use this BURT to check the fix in.

[BMORYRTOGO]

That is great news! Thank You!

We are eager to see what the Balance product can provide as we have a VM environment of around 500 machines.

[mkahlke]

It's a good thing you provided the qtree path name, the curly braces also needed to be escaped.

[BMORYRTOGO]

Yes, I did try to provide as much as I could. Syncsort seems to create those names with too many ‘questionable’ characters for my liking.

I also wondered about the “@” symbol for SQL. Isn’t that for SQL variable expansion?

[mkahlke]

Yes it is, although in the example above it isn't treated as a session variable in the context given.  Still it can't hurt to be safe so I've gone ahead and escape the '@' symbol as well.  I ran a test SQL query escaping all five of these characters against the example you provided and it works, so we'll go with that.

[BMORYRTOGO]

Excellent!

Thank you Mike!