3 Replies Latest reply on Oct 12, 2017 6:15 PM by Christina Cho-Oracle

    Unique index WWV_FLOW_WS_PIVOT_IDX3 violated APEX 5.1

    Kim Mertens

      Hi all,

       

      One of our users gets this Unique index violation everytime he opens a page containing an Interactive Report.

      The user has saved several containing pivot and non-pivot private reports.

      He never gets to see the report, the error immediately shows on the screen.

       

      Already found that this bug should've been fixed in patchset 5.0.3 with bug number 22160678

      Unfortunately we are still experiencing this issue in version 5.1.0.00.45.

       

      The index itself is on table WWV_FLOW_WORKSHEET_PIVOT and on the following columns:

      WORKSHEET_ID

      WEBSHEET_ID

      REPORT_ID

       

      A temporary fix that seems to work, no idea why tho, is the following:

      1. Change owner of the private reports from user X to user Y with statement
        update wwv_flow_worksheet_rpts
        set application_user = <USER_Y>
        where id = <report_id>
      2. Let user X start the page, which works now.
      3. Set the private reports back to user X with the same statement statement as mentioned in step 1.

      Doing this makes the page works for a few times and after that it's messed up again

      Anyone have an idea what the reason could be or how to solve this ?

      Above mentioned bug number doesn't give any more information..

       

      Thanks in advance,

       

      Kim

        • 1. Re: Unique index WWV_FLOW_WS_PIVOT_IDX3 violated APEX 5.1
          Christina Cho-Oracle

          Hi Kim,

           

          I have some questions to help analyze your issue further:

          1. Is your application ID different between development and production app where user experiences wwv_flow_ws_pivot_idx3 error?

          2. Did you upgrade APEX to 5.1.0.00.45 from the previous version?

          3. What are the results when you run the following 2 queries?

          select *

          from wwv_flow_worksheet_pivot

          where worksheet_id not in (select id from wwv_flow_worksheets)

          and report_id in (select report_id

                            from wwv_flow_worksheet_pivot

                            group by report_id

                            having count(*) > 1);

           

          select *

          from wwv_flow_worksheet_pivot

          where report_id in (select report_id

                            from wwv_flow_worksheet_pivot

                            group by report_id

                            having count(*) > 1);

           

          Regards,

          Christina

          • 2. Re: Unique index WWV_FLOW_WS_PIVOT_IDX3 violated APEX 5.1
            Kim Mertens

            Hi Christina,

             

            1. Is your application ID different between development and production app where user experiences wwv_flow_ws_pivot_idx3 error?

            I have tried to simulate the issue with my user account ( also in the production environment ) but couldn't reproduce it.

            In this case the application ID is identical since it's the same application I tried to simulate it in.

            The application ID in the test environment is different from production.

            2. Did you upgrade APEX to 5.1.0.00.45 from the previous version?

            The previous version that was installed was 5.0.1.00.06.

            3. What are the results when you run the following 2 queries?

            select *

            from wwv_flow_worksheet_pivot

            where worksheet_id not in (select id from wwv_flow_worksheets)

            and report_id in (select report_id

            from wwv_flow_worksheet_pivot

            group by report_id

            having count(*) > 1);

            This statement returns 2 records. Both for the page that is failing and the user that gets the error.

            select *

            from wwv_flow_worksheet_pivot

            where report_id in (select report_id

            from wwv_flow_worksheet_pivot

            group by report_id

            having count(*) > 1);

            This statement returns 4 records. This is for the page and user that gets the error.

             

            Kind regards,

             

            Kim

            • 3. Re: Unique index WWV_FLOW_WS_PIVOT_IDX3 violated APEX 5.1
              Christina Cho-Oracle

              Hi Kim,

               

              Thank you for the additional information.  Since there are some rows returned with query #3, there is definitely an issue with orphan pivot.  However, I am puzzled how orphan pivot record got created since you stated the application IDs are same.  The bug 22160678 is about orphan pivot getting created if application ID changes between the instance which results in wwv_flow_ws_pivot_idx3 error when user session report gets initiated.

               

              I need one more information from you.  Can you please run the following queries?

               

              select flow_id, page_id, worksheet_id, report_id

              from wwv_flow_worksheet_pivot

              where worksheet_id in (select id from wwv_flow_worksheets)

              and report_id in (select report_id

              from wwv_flow_worksheet_pivot

              group by report_id

              having count(*) > 1);

               

              select flow_id, page_id, session_id, base_report_id

              from wwv_flow_worksheet_rpts

              where id in (select report_id

              from wwv_flow_worksheet_pivot

              where worksheet_id in (select id from wwv_flow_worksheets)

              and report_id in (select report_id

              from wwv_flow_worksheet_pivot

              group by report_id

              having count(*) > 1));

               

              Regards,

              Christina