AXL 8.5 getUser returns "A subquery has returned not exactly one row&#

Version 1
    This document was generated from CDN thread

    Created by: philipp gantert on 12-01-2011 02:50:57 PM
    Hi
     
    Since the upgrade on 8.5 I have following problem. Endusers are from LDAP.
     
    - getUser returns error "A subquery has returned not exactly one row".
    - this happens only by Endusers which are CUP enabled
    - ohter users without CUP works very well
     
    Is this a BUG?
    listUser returns 2 Endusers with the same userid an uuid:
    Array
    (
        [0] => stdClass Object
            (
                 => philipp.gantert
                 => {DF667DF4-13BE-47B1-9D31-8394228A8ADE}
            )

        [1] => stdClass Object
            (
                 => philipp.gantert
                 => {DF667DF4-13BE-47B1-9D31-8394228A8ADE}
            )

    )
     
    thx phil

    Subject: RE: AXL 8.5 getUser returns "A subquery has returned not exactly one r
    Replied by: David Staudt on 12-01-2011 06:35:31 PM
    Very interesting, could be a defect.  Can you enable the detailed AXL logs (Serviceability admin pages) run the test again and attach the logs?

    Subject: RE: AXL 8.5 getUser returns "A subquery has returned not exactly one r
    Replied by: philipp gantert on 12-01-2011 06:56:40 PM
    Hi
     
    Of course... here is the log
     
    thx for help

    Subject: RE: AXL 8.5 getUser returns "A subquery has returned not exactly one r
    Replied by: David Staudt on 14-01-2011 04:07:09 PM
    From the logs, the problem query appears to be:

    select dnorpattern, fkRoutePartition from numplan where pkid = (select fkNumPlan from enduserNumplanMap where fkEndUser = 'df667df4-13be-47b1-9d31-8394228a8ade')

        where the error is:

    A subquery has returned not exactly one row

    The subquery in that case is:

    select fkNumPlan from enduserNumplanMap where fkEndUser = 'df667df4-13be-47b1-9d31-8394228a8ade'

    I believe this query might return multiple rows if the user is associated with multiple DNs.  If so, then the outer query syntax becomes invalid with multiple DNs, e.g.:
     
    select dnorpattern, fkRoutePartition from numplan where pkid = (1000,1001)

    My bet is that this query is intended to provide the value for <primaryExtension>.  It appears somehow this user has multiple associations.
     
    I'm researching whether this is valid:  if so, then there is a defect in AXL with handling multiple associations; if not, then somehow multiple associations occurred and will need to be removed.
     
    It would be interesting to see what you get from UCM admin when viewing this user, specifically the Primary Extension field.  If you can clear this field it might help.
     
    At a lower level, you can use the UCM CLI to do some SQL work to check on the associations and/or possibly remove them:
     
    >run sql select pkid,fkenduser,fknumplan from endusernumplanmap where fkenduser='df667df4-13be-47b1-9d31-8394228a8ade'
     
    Select a user's pkid which is experiencing the problem.  You will likely see multiple rows.
     
    You can try removing all the user's associations like:
     
    >run sql delete from endusernumplanmap where pkid='df667df4-13be-47b1-9d31-8394228a8ade'

    Subject: RE: AXL 8.5 getUser returns "A subquery has returned not exactly one r
    Replied by: Philipp Gantert on 17-01-2011 03:58:06 AM
    From the logs, the problem query appears to be:
     
    It would be interesting to see what you get from UCM admin when viewing this user, specifically the Primary Extension field.  If you can clear this field it might help.
     



     
    Hi David
     
    You're right... Under "Directory Number Associations" I defined Primary Extension and IPCC Extension at the same time and that's the problem. I can define one of them, but not both at the same time.
     
    My opinion is that's a bug, because both settings have nothing to do with each other.They are needed for different services.
     
    let me up to date.

    Subject: Any updates on this ?
    Replied by: Sue Pasha on 12-10-2011 08:53:43 PM
    There is now a bug entry CSCts08729 for this issue but nothing seems to happen. The Severity of CSCts08729 is set to 6 (enhancement). This should be 2 or 1 as it renders the AXL unusable for any deployments with IPCC. Even on a basic GetUser request it fails. We are requesting to update this. Also, this defect seems to be present in 8.0.3 and likely other 8 version, not only in 8.5(1) as listed in CSCts08729.
     
    In AXL 7.1 there is a IPCCextension field in the getUser() response. This is not anymore present in AXL version 8. Please advise how to retrieve and add/update/del IPCC extension. This is another defect and we request to open another bug for this. It has been present in 7 so it is not a feature enhancement.
     
    To summarize, we ask Cisco for the following:
    1.) Clarification on which versions this problem occurs
    2.) To change severity in CSCts08729 so it gets fixed in next update
    3.) Open a case to add back IPCCextension field to XUser class as it was in AXL 7.1
    4.) Provide workarounds for GetUser(), and how to add/upd/del IPCC extension in user via AXL
    This is a severy bug as it renders AXL not usable for any CUCM cluster with IPCC.

    Subject: Sever bug - any update - still no fix in sight ?
    Replied by: MICHAEL KUNZ on 12-10-2011 08:59:14 PM
    There is now a bug entry CSCts08729 for this issue but nothing seems to happen. The Severity of CSCts08729 is set to 6 (enhancement). This should be 2 or 1 as it renders the AXL unusable for any deployments with IPCC. Even on a basic GetUser request it fails. We are requesting to update this. Also, this defect seems to be present in 8.0.3 and likely other 8 version, not only in 8.5(1) as listed in CSCts08729.
     
    In AXL 7.1 there is a IPCCextension field in the getUser() response. This is not anymore present in AXL version 8. Please advise how to retrieve and add/update/del IPCC extension. This is another defect and we request to open another bug for this. It has been present in 7 so it is not a feature enhancement.
     
    To summarize, we ask Cisco for the following:
    1.) Clarification on which versions this problem occurs
    2.) To change severity in CSCts08729 so it gets fixed in next update
    3.) Open a case to add back IPCCextension field to XUser class as it was in AXL 7.1
    4.) Provide workarounds for GetUser(), and how to add/upd/del IPCC extension in user via AXL
     
    This is a severe bug as it renders AXL not usable for any CUCM cluster with IPCC.

    Subject: RE: AXL 8.5 getUser returns "A subquery has returned not exactly one r
    Replied by: AARON ARCHAMBAULT on 02-01-2012 07:42:34 PM
    This is a bug, it will not allow you to query on a user who is both a IPCC agent and has CUPS enabled.  How can we get around this, as of now we are stuck using older versions of the AXL which would allow this to work.  using a simple AXL getuser errors everytime, unless you do not have IPCC extension, or a primary extension.  
     
     

    Subject: RE: AXL 8.5 getUser returns "A subquery has returned not exactly one r
    Replied by: David Staudt on 04-01-2012 05:59:40 PM
    A workaround could be to use a SQL query against the enduser table itself to get at your needed data.  Note that the enduser table itself contains a lot of fields, but the complete data returned by <getUser> involves retrieving data from several other related tables.  If you need some of this data too, you may need to do some joins in the SQL.
     
    The 'Data Dictionary' documentation is your guide for the UCM tables/fields and table relationships.

    Subject: Re: New Message from David Staudt in Administration XML (AXL) - Administrat
    Replied by: AARON ARCHAMBAULT on 05-01-2012 08:43:08 AM
    that is great except for the fact that the API brings back a bunch of info
    that would require more rewrites of my program than it is worth.  I will
    continue to use 7.1 calls that work fine until it is fixed in future
    releases.


    On Wed, Jan 4, 2012 at 5:59 PM, Cisco Developer Community Forums <
    cdicuser@developer.cisco.com> wrote:

    > David Staudt has created a new message in the forum "Administration XML
    > Questions":
    >
    > --------------------------------------------------------------
    > A workaround could be to use a SQL query against the enduser table itself
    > to get at your needed data.  Note that the enduser table itself contains a
    > lot of fields, but the complete data returned by <getUser> involves
    > retrieving data from several other related tables.  If you need some of
    > this data too, you may need to do some joins in the SQL.
    >
    > The 'Data Dictionary' documentation is your guide for the UCM
    > tables/fields and table relationships.
    > --
    > To respond to this post, please click the following link:
    >
    > <
    > http://developer.cisco.com/web/axl/forums/-/message_boards/view_message/4959127
    > >
    >
    > or simply reply to this email.




    --
    Aaron Archambault
    archama@gmail.com
    cell: (802)488-0601