glassfish
  1. glassfish
  2. GLASSFISH-3611

pagination throws java.lang.OutOfMemoryError

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 9.0pe
    • Fix Version/s: not determined
    • Component/s: entity-persistence
    • Labels:
      None
    • Environment:

      Operating System: Windows XP
      Platform: PC

    • Issuezilla Id:
      3,611

      Description

      The problem is in Java SE with MySQL database.

      I have inserted 1 000 000 rows in a table and then tried to copy all of them to
      another table in different DB and got java.lang.OutOfMemoryError.
      After investigating the issue I found that the pagination does not work.

      Here is the entity mapped to the table:
      --------------------------------------------------
      @Entity
      public class Transaction implements Serializable {

      @Id
      @SequenceGenerator(name = "SEQ_TRANSACTION", sequenceName = "SEQ_TRANSACTION",
      allocationSize = 1)
      @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_TRANSACTION")
      protected long id;

      private String payerIban;

      private String payerBic;

      private String payeeIban;

      private String payeeBic;

      protected BigDecimal ammount;

      protected int journalPeriod;

      @Temporal(TemporalType.DATE)
      protected Date transactionDate;

      @Temporal(TemporalType.TIME)
      protected Date transactionTime;

      // constructors, getters and setters...
      --------------------------------------------------

      The the persistence.xml:
      --------------------------------------------------
      <persistence-unit name="rts_wallet-persistence"
      transaction-type="RESOURCE_LOCAL">
      <provider>
      oracle.toplink.essentials.ejb.cmp3.EntityManagerFactoryProvider
      </provider>
      <class>Transaction</class>
      <properties>
      <property name="toplink.jdbc.url" value="jdbc:mysql://localhost:3306/mpayrts"/>
      <property name="toplink.jdbc.user" value="mydb"/>
      <property name="toplink.jdbc.driver" value="com.mysql.jdbc.Driver"/>
      <property name="toplink.jdbc.password" value="mypass"/>
      <property name="toplink.logging.level" value="FINER"/>
      </properties>
      </persistence-unit>
      --------------------------------------------------

      When I try to execute the following query it works:
      --------------------------------------------------
      int pageSize = 500;
      int offset = 310000;

      List<Transaction> list = rtsEM.createQuery(
      "select t from Transaction t").setFirstResult(offset)
      .setMaxResults(pageSize).getResultList();
      --------------------------------------------------

      But, If it set offset = 320000; ... the result is:
      --------------------------------------------------
      [TopLink Fine]: 2007.09.15
      05:23:58.718-ServerSession(11124894)Connection(11985823)Thread(Thread[main,5,main])-SELECT
      ID, PAYEEBIC, AMMOUNT, PAYERBIC, JOURNALPERIOD, PAYERIBAN, TRANSACTIONDATE,
      PAYEEIBAN, TRANSACTIONTIME FROM TRANSACTION
      Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
      at com.mysql.jdbc.Buffer.getBytes(Buffer.java:198)
      at com.mysql.jdbc.Buffer.readLenByteArray(Buffer.java:318)
      at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1360)
      at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2326)
      at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:436)
      at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2033)
      at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1436)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1770)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:3255)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1293)
      at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1419)
      at
      oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:711)
      at
      oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:486)
      at
      oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:437)
      at
      oracle.toplink.essentials.threetier.ServerSession.executeCall(ServerSession.java:465)
      at
      oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:213)
      at
      oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:199)
      at
      oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:270)
      at
      oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:600)
      at
      oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2207)
      at
      oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2173)
      at
      oracle.toplink.essentials.queryframework.ReportQuery.executeDatabaseQuery(ReportQuery.java:774)
      at
      oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:609)
      at
      oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:677)
      at
      oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:731)
      at
      oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2219)
      at
      oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:937)
      at
      oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:909)
      at
      oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:346)
      at
      oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.getResultList(EJBQueryImpl.java:453)
      at TestPagination.main(TestPagination.java:47)

      --------------------------------------------------

      Note that in MySQL there is a LIMIT clause and one could say:
      select * from transaction LIMIT 320000,500;

      But, as you can see into the log above the query does not look aware of this.

        Activity

        Hide
        bodrin added a comment -

        Created an attachment (id=1146)
        pagination exception

        Show
        bodrin added a comment - Created an attachment (id=1146) pagination exception
        Hide
        gfbugbridge added a comment -

        <BT6605255>

        Show
        gfbugbridge added a comment - <BT6605255>
        Hide
        ijuma added a comment -

        Adding myself to cc list.

        Show
        ijuma added a comment - Adding myself to cc list.
        Hide
        pkrogh added a comment -

        A good enhancement.

        Show
        pkrogh added a comment - A good enhancement.
        Hide
        bodrin added a comment -

        After investigating MySQL query log I have found what is the actual problem.
        Toplink Essentials does not use the LIMIT clause of the SELECT statement, but
        uses OPTION SQL_SELECT_LIMIT.

        So, if you want to select 500 records at offset 320000 Toplink generates the
        following statements:

        2 Query SET OPTION SQL_SELECT_LIMIT=320500
        2 Query SELECT * FROM subscription ORDER BY ID ASC
        2 Query SET OPTION SQL_SELECT_LIMIT=DEFAULT

        This way it selects (offset + pageSize) number of rows always starting from the
        begging and if you select your last page - you actually will select all the
        records.
        So, it seams that this is not the right approach to implement pagination at all.

        Show
        bodrin added a comment - After investigating MySQL query log I have found what is the actual problem. Toplink Essentials does not use the LIMIT clause of the SELECT statement, but uses OPTION SQL_SELECT_LIMIT. So, if you want to select 500 records at offset 320000 Toplink generates the following statements: 2 Query SET OPTION SQL_SELECT_LIMIT=320500 2 Query SELECT * FROM subscription ORDER BY ID ASC 2 Query SET OPTION SQL_SELECT_LIMIT=DEFAULT This way it selects (offset + pageSize) number of rows always starting from the begging and if you select your last page - you actually will select all the records. So, it seams that this is not the right approach to implement pagination at all.
        Hide
        Tom Mueller added a comment -

        Bulk change to set fix version to "not determined" where the issue is open but the value is for a released version.

        Show
        Tom Mueller added a comment - Bulk change to set fix version to "not determined" where the issue is open but the value is for a released version.

          People

          • Assignee:
            tware
            Reporter:
            bodrin
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: