topic Re: Balance throws SQL error when collecting array data in Active IQ Unified Manager Discussions

Balance throws SQL error when collecting array data

bachman — Thu, 05 Jun 2025 05:55:44 GMT

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

Re: Balance throws SQL error when collecting array data

plauterb — Fri, 16 Aug 2013 20:53:09 GMT

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.

Re: Balance throws SQL error when collecting array data

mkahlke — Wed, 21 Aug 2013 02:27:45 GMT

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.

Re: Balance throws SQL error when collecting array data

bachman — Wed, 21 Aug 2013 12:25:01 GMT

Case #2004507327 opened.

Re: Balance throws SQL error when collecting array data

BMORYRTOGO — Tue, 03 Sep 2013 21:00:54 GMT

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.

Re: Balance throws SQL error when collecting array data

mkahlke — Wed, 04 Sep 2013 02:22:48 GMT

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.

Re: Balance throws SQL error when collecting array data

mkahlke — Wed, 04 Sep 2013 02:39:05 GMT

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.

Re: Balance throws SQL error when collecting array data

BMORYRTOGO — Wed, 04 Sep 2013 02:43:43 GMT

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.

Re: Balance throws SQL error when collecting array data

mkahlke — Wed, 04 Sep 2013 03:38:56 GMT

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

Re: Balance throws SQL error when collecting array data

BMORYRTOGO — Wed, 04 Sep 2013 03:44:32 GMT

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?

Re: Balance throws SQL error when collecting array data

mkahlke — Wed, 04 Sep 2013 03:58:50 GMT

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.

Re: Balance throws SQL error when collecting array data

BMORYRTOGO — Wed, 04 Sep 2013 04:02:32 GMT

Excellent!

Thank you Mike!