Jackrabbit Content Repository
  1. Jackrabbit Content Repository
  2. JCR-3261

Problems with BundleDbPersistenceManager getAllNodeIds

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.4
    • Fix Version/s: 2.4.1, 2.5
    • Component/s: None
    • Labels:
      None

      Description

      When using MySQL:
      The problem arises when the method parameter maxcount is less than the total amount of records in the bundle table.

      First of all I found out that mysql orders the nodeid objects different than jackrabbit does. The following test describes this idea:

      public void testMySQLOrderByNodeId() throws Exception {
      NodeId nodeId1 = new NodeId("7ff9e87c-f87f-4d35-9d61-2e298e56ac37");
      NodeId nodeId2 = new NodeId("9fd0d452-b5d0-426b-8a0f-bef830ba0495");

      PreparedStatement stmt = connection.prepareStatement("SELECT NODE_ID FROM DEFAULT_BUNDLE WHERE NODE_ID = ? OR NODE_ID = ? ORDER BY NODE_ID");

      Object[] params = new Object[]

      { nodeId1.getRawBytes(), nodeId2.getRawBytes() }

      ;
      stmt.setObject(1, params[0]);
      stmt.setObject(2, params[1]);

      ArrayList<NodeId> nodeIds = new ArrayList<NodeId>();
      ResultSet resultSet = stmt.executeQuery();
      while(resultSet.next())

      { NodeId nodeId = new NodeId(resultSet.getBytes(1)); System.out.println(nodeId); nodeIds.add(nodeId); }

      Collections.sort(nodeIds);
      for (NodeId nodeId : nodeIds)

      { System.out.println(nodeId); }

      }

      Which results in the following output:

      7ff9e87c-f87f-4d35-9d61-2e298e56ac37
      9fd0d452-b5d0-426b-8a0f-bef830ba0495
      9fd0d452-b5d0-426b-8a0f-bef830ba0495
      7ff9e87c-f87f-4d35-9d61-2e298e56ac37

      Now the problem with the getAllNodeIds method is that it fetches an extra 10 records on top of maxcount (to avoid a problem where the first key is not the one you that is wanted). Afterwards it skips a number of records again, this time using nodeid.compareto. This compareto statement returns true unexpectedly for mysql because the code doesn't expect the mysql ordering.

      I had the situation where I had about 17000 records in the bundle table but consecutively getting the ids a thousand records at a time returned only about 8000 records in all.

        Activity

        Jukka Zitting made changes -
        Fix Version/s 2.5 [ 12319280 ]
        Fix Version/s 2.6 [ 12319480 ]
        Alex Parvulescu made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Bart van der Schans made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        Bart van der Schans made changes -
        Fix Version/s 2.6 [ 12319480 ]
        Bart van der Schans made changes -
        Assignee Bart van der Schans [ schans ]
        Unico Hommes made changes -
        Description When using MySQL:
        The problem arises when the method parameter maxcount is less than the total amount of records in the bundle table.

        First of all I found out that mysql orders the nodeid objects different than jackrabbit does. The following test describes this idea:

            public void testMySQLOrderByNodeId() throws Exception {
                NodeId nodeId1 = new NodeId("7ff9e87c-f87f-4d35-9d61-2e298e56ac37");
                NodeId nodeId2 = new NodeId("9fd0d452-b5d0-426b-8a0f-bef830ba0495");

                PreparedStatement stmt = connection.prepareStatement("SELECT NODE_ID FROM DEFAULT_BUNDLE WHERE NODE_ID = ? OR NODE_ID = ? ORDER BY NODE_ID");

                Object[] params = new Object[] { nodeId1.getRawBytes(), nodeId2.getRawBytes() };
                stmt.setObject(1, params[0]);
                stmt.setObject(2, params[1]);

                ArrayList<NodeId> nodeIds = new ArrayList<NodeId>();
                ResultSet resultSet = stmt.executeQuery();
                while(resultSet.next()) {
                    NodeId nodeId = new NodeId(resultSet.getBytes(1));
                    System.out.println(nodeId);
                    nodeIds.add(nodeId);
                }
                Collections.sort(nodeIds);
                for (NodeId nodeId : nodeIds) {
                    System.out.println(nodeId);
                }
            }

        Which results in the following output:

        7ff9e87c-f87f-4d35-9d61-2e298e56ac37
        9fd0d452-b5d0-426b-8a0f-bef830ba0495
        9fd0d452-b5d0-426b-8a0f-bef830ba0495
        7ff9e87c-f87f-4d35-9d61-2e298e56ac37


        Now the problem with the getAllNodeIds method is that it fetches an extra 10 records on top of maxcount (to avoid a problem where the first key is not the one you that is wanted). Afterwards it skips a number of records again, this time using nodeid.compareto. This compareto statement returns true unexpectedly for mysql because the code doesn't expect the mysql ordering.

        I had the situation where I had about 17000 records in the bundle table but consecutively getting the ids a thousand records at a time returned only about 8000 records in all.

        With Derby DB there is an infinite loop when it is used as it is for instance by ConsistencyCheckerImpl. This is because of the bundleSelectAllIdsFromSQL sql query that is done in the case of SM_LONGLONG_KEYS which is used in the case of Derby. Compare for instance that statement with the corresponding statement for SM_BINARY_KEYS. You will see that the former uses >= while the latter only >. The latter is correct. We want only records that are bigger than the passed in parameter, not bigger or equal.
        When using MySQL:
        The problem arises when the method parameter maxcount is less than the total amount of records in the bundle table.

        First of all I found out that mysql orders the nodeid objects different than jackrabbit does. The following test describes this idea:

            public void testMySQLOrderByNodeId() throws Exception {
                NodeId nodeId1 = new NodeId("7ff9e87c-f87f-4d35-9d61-2e298e56ac37");
                NodeId nodeId2 = new NodeId("9fd0d452-b5d0-426b-8a0f-bef830ba0495");

                PreparedStatement stmt = connection.prepareStatement("SELECT NODE_ID FROM DEFAULT_BUNDLE WHERE NODE_ID = ? OR NODE_ID = ? ORDER BY NODE_ID");

                Object[] params = new Object[] { nodeId1.getRawBytes(), nodeId2.getRawBytes() };
                stmt.setObject(1, params[0]);
                stmt.setObject(2, params[1]);

                ArrayList<NodeId> nodeIds = new ArrayList<NodeId>();
                ResultSet resultSet = stmt.executeQuery();
                while(resultSet.next()) {
                    NodeId nodeId = new NodeId(resultSet.getBytes(1));
                    System.out.println(nodeId);
                    nodeIds.add(nodeId);
                }
                Collections.sort(nodeIds);
                for (NodeId nodeId : nodeIds) {
                    System.out.println(nodeId);
                }
            }

        Which results in the following output:

        7ff9e87c-f87f-4d35-9d61-2e298e56ac37
        9fd0d452-b5d0-426b-8a0f-bef830ba0495
        9fd0d452-b5d0-426b-8a0f-bef830ba0495
        7ff9e87c-f87f-4d35-9d61-2e298e56ac37


        Now the problem with the getAllNodeIds method is that it fetches an extra 10 records on top of maxcount (to avoid a problem where the first key is not the one you that is wanted). Afterwards it skips a number of records again, this time using nodeid.compareto. This compareto statement returns true unexpectedly for mysql because the code doesn't expect the mysql ordering.

        I had the situation where I had about 17000 records in the bundle table but consecutively getting the ids a thousand records at a time returned only about 8000 records in all.
        Unico Hommes made changes -
        Attachment bdpm_allids2.patch [ 12518805 ]
        Unico Hommes made changes -
        Attachment bdpm_allids2.patch [ 12518805 ]
        Unico Hommes made changes -
        Summary BundleDbPersistenceManager getAllIds gives wrong amount of results for MySQL Problems with BundleDbPersistenceManager getAllNodeIds
        Description The problem arises when the method parameter maxcount is less than the total amount of records in the bundle table.

        First of all I found out that mysql orders the nodeid objects different than jackrabbit does. The following test describes this idea:

            public void testMySQLOrderByNodeId() throws Exception {
                NodeId nodeId1 = new NodeId("7ff9e87c-f87f-4d35-9d61-2e298e56ac37");
                NodeId nodeId2 = new NodeId("9fd0d452-b5d0-426b-8a0f-bef830ba0495");

                PreparedStatement stmt = connection.prepareStatement("SELECT NODE_ID FROM DEFAULT_BUNDLE WHERE NODE_ID = ? OR NODE_ID = ? ORDER BY NODE_ID");

                Object[] params = new Object[] { nodeId1.getRawBytes(), nodeId2.getRawBytes() };
                stmt.setObject(1, params[0]);
                stmt.setObject(2, params[1]);

                ArrayList<NodeId> nodeIds = new ArrayList<NodeId>();
                ResultSet resultSet = stmt.executeQuery();
                while(resultSet.next()) {
                    NodeId nodeId = new NodeId(resultSet.getBytes(1));
                    System.out.println(nodeId);
                    nodeIds.add(nodeId);
                }
                Collections.sort(nodeIds);
                for (NodeId nodeId : nodeIds) {
                    System.out.println(nodeId);
                }
            }

        Which results in the following output:

        7ff9e87c-f87f-4d35-9d61-2e298e56ac37
        9fd0d452-b5d0-426b-8a0f-bef830ba0495
        9fd0d452-b5d0-426b-8a0f-bef830ba0495
        7ff9e87c-f87f-4d35-9d61-2e298e56ac37


        Now the problem with the getAllNodeIds method is that it fetches an extra 10 records on top of maxcount (to avoid a problem where the first key is not the one you that is wanted). Afterwards it skips a number of records again, this time using nodeid.compareto. This compareto statement returns true unexpectedly for mysql because the code doesn't expect the mysql ordering.

        I had the situation where I had about 17000 records in the bundle table but consecutively getting the ids a thousand records at a time returned only about 8000 records in all.

        I'll attach a patch that fixes the problem.
        When using MySQL:
        The problem arises when the method parameter maxcount is less than the total amount of records in the bundle table.

        First of all I found out that mysql orders the nodeid objects different than jackrabbit does. The following test describes this idea:

            public void testMySQLOrderByNodeId() throws Exception {
                NodeId nodeId1 = new NodeId("7ff9e87c-f87f-4d35-9d61-2e298e56ac37");
                NodeId nodeId2 = new NodeId("9fd0d452-b5d0-426b-8a0f-bef830ba0495");

                PreparedStatement stmt = connection.prepareStatement("SELECT NODE_ID FROM DEFAULT_BUNDLE WHERE NODE_ID = ? OR NODE_ID = ? ORDER BY NODE_ID");

                Object[] params = new Object[] { nodeId1.getRawBytes(), nodeId2.getRawBytes() };
                stmt.setObject(1, params[0]);
                stmt.setObject(2, params[1]);

                ArrayList<NodeId> nodeIds = new ArrayList<NodeId>();
                ResultSet resultSet = stmt.executeQuery();
                while(resultSet.next()) {
                    NodeId nodeId = new NodeId(resultSet.getBytes(1));
                    System.out.println(nodeId);
                    nodeIds.add(nodeId);
                }
                Collections.sort(nodeIds);
                for (NodeId nodeId : nodeIds) {
                    System.out.println(nodeId);
                }
            }

        Which results in the following output:

        7ff9e87c-f87f-4d35-9d61-2e298e56ac37
        9fd0d452-b5d0-426b-8a0f-bef830ba0495
        9fd0d452-b5d0-426b-8a0f-bef830ba0495
        7ff9e87c-f87f-4d35-9d61-2e298e56ac37


        Now the problem with the getAllNodeIds method is that it fetches an extra 10 records on top of maxcount (to avoid a problem where the first key is not the one you that is wanted). Afterwards it skips a number of records again, this time using nodeid.compareto. This compareto statement returns true unexpectedly for mysql because the code doesn't expect the mysql ordering.

        I had the situation where I had about 17000 records in the bundle table but consecutively getting the ids a thousand records at a time returned only about 8000 records in all.

        With Derby DB there is an infinite loop when it is used as it is for instance by ConsistencyCheckerImpl. This is because of the bundleSelectAllIdsFromSQL sql query that is done in the case of SM_LONGLONG_KEYS which is used in the case of Derby. Compare for instance that statement with the corresponding statement for SM_BINARY_KEYS. You will see that the former uses >= while the latter only >. The latter is correct. We want only records that are bigger than the passed in parameter, not bigger or equal.
        Unico Hommes made changes -
        Field Original Value New Value
        Attachment bdbpm_allids.patch [ 12518675 ]
        Unico Hommes created issue -

          People

          • Assignee:
            Bart van der Schans
            Reporter:
            Unico Hommes
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development