Derby
  1. Derby
  2. DERBY-3645

Insert into selecting BLOB column twice leads to SQLException: Restore of a serializable or SQLData object of class error selecting from the table

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.3.3.0, 10.4.2.0, 10.5.1.1
    • Fix Version/s: 10.5.3.1, 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Normal
    • Issue & fix info:
      High Value Fix, Repro attached
    • Bug behavior facts:
      Data corruption

      Description

      The following code which inserts into a table by selecting a blob column twice from another table, causes SQLException: Restore of a serializable or SQLData object of class error selecting from the table. See attached program DoubleInsertInto for full repro. Stack trace is below. Verified back to 10.3 but probably goes back further.

      s.executeUpdate("CREATE TABLE T_MAIN(" +
      "ID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, " +
      "V BLOB(590473235) )");

      String ins1 = "INSERT INTO T_MAIN(V) VALUES ";

      PreparedStatement ps;
      ps = c.prepareStatement(ins1);
      byte[] bytes = new byte[35000];
      for (int i = 0; i < 35000; i++)
      bytes[i] = (byte) i ;
      ps.setBytes(1, bytes);
      ps.executeUpdate();
      ps.close();

      s.executeUpdate("CREATE TABLE T_COPY ( V1 BLOB(2M), V2 BLOB(2M))");

      Statement stmt = c.createStatement();
      stmt.executeUpdate("INSERT INTO T_COPY SELECT V, V FROM T_MAIN");
      ResultSet rs = stmt.executeQuery("SELECT * FROM T_COPY");
      rs.next();
      String v1 = rs.getString(1);
      String v2 = rs.getString(2);
      System.out.println("v1:" + v1);
      System.out.println("v2:" + v2);
      System.out.println("I am done");

      Exception in thread "main" java.sql.SQLException: Restore of a serializable or SQLData object of class , attempted to re
      ad more data than was originally stored
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)
      at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:87)
      at org.apache.derby.impl.jdbc.Util.seeNextException(Util.java:223)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:398)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
      at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2125)
      at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
      at org.apache.derby.impl.jdbc.EmbedResultSet.closeOnTransactionError(EmbedResultSet.java:4320)
      at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:463)
      at org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:367)
      at DoubleInsertInto.main(DoubleInsertInto.java:47)
      Caused by: java.sql.SQLException: Restore of a serializable or SQLData object of class , attempted to read more data tha
      n was originally stored
      at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:11
      9)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
      ... 10 more
      Caused by: java.sql.SQLException: Java exception: ': java.io.EOFException'.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:11
      9)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
      at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:87)
      at org.apache.derby.impl.jdbc.Util.javaException(Util.java:244)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:403)
      ... 8 more
      Caused by: java.io.EOFException
      at org.apache.derby.iapi.types.SQLBinary.readBinaryLength(SQLBinary.java:350)
      at org.apache.derby.iapi.types.SQLBinary.readExternalFromArray(SQLBinary.java:328)
      at org.apache.derby.impl.store.raw.data.StoredPage.readRecordFromArray(StoredPage.java:5568)
      at org.apache.derby.impl.store.raw.data.StoredPage.restoreRecordFromSlot(StoredPage.java:1497)
      at org.apache.derby.impl.store.raw.data.BasePage.fetchFromSlot(BasePage.java:459)
      at org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(GenericScanController.java:75
      9)
      at org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(HeapScan.java:324)
      at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(BulkTableScanResultSet.java:327)
      at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(BulkTableScanResultSet.java:282)
      at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(BasicNoPutResultSetImpl.java:460)
      at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:423)
      ... 2 more

        Issue Links

          Activity

          Kathey Marsden created issue -
          Kathey Marsden made changes -
          Field Original Value New Value
          Attachment DoubleInsertInto.java [ 12380951 ]
          Kathey Marsden made changes -
          Link This issue relates to DERBY-3646 [ DERBY-3646 ]
          Kathey Marsden made changes -
          Link This issue relates to DERBY-2349 [ DERBY-2349 ]
          Hide
          Kathey Marsden added a comment -

          Interestingly, if I change the copy table to have the same size BLOB. I get wrong results instead.
          s.executeUpdate("CREATE TABLE T_COPY ( V1 BLOB(590473235), V2 BLOB(590473235))");
          v1:000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f....
          v2:9e9fa0a1a2a3a4a5a6a7a8a9aaabacadaeafb0b1b2b3b4b5b6b7b8b9ba

          This is more similar to the DERBY-2349 case.

          Show
          Kathey Marsden added a comment - Interestingly, if I change the copy table to have the same size BLOB. I get wrong results instead. s.executeUpdate("CREATE TABLE T_COPY ( V1 BLOB(590473235), V2 BLOB(590473235))"); v1:000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f.... v2:9e9fa0a1a2a3a4a5a6a7a8a9aaabacadaeafb0b1b2b3b4b5b6b7b8b9ba This is more similar to the DERBY-2349 case.
          Kathey Marsden made changes -
          Derby Categories [Data corruption, High Value Fix]
          Myrna van Lunteren made changes -
          Affects Version/s 10.5.1.1 [ 12313771 ]
          Affects Version/s 10.5.0.0 [ 12313010 ]
          Dag H. Wanvik made changes -
          Issue & fix info [High Value Fix]
          Hide
          Knut Anders Hatlen added a comment -

          Triaged for 10.5.2.

          Show
          Knut Anders Hatlen added a comment - Triaged for 10.5.2.
          Knut Anders Hatlen made changes -
          Urgency Normal
          Issue & fix info [High Value Fix] [High Value Fix, Repro attached]
          Description The following code which inserts into a table by selecting a blob column twice from another table, causes SQLException: Restore of a serializable or SQLData object of class error selecting from the table. See attached program DoubleInsertInto for full repro. Stack trace is below. Verified back to 10.3 but probably goes back further.



                  s.executeUpdate("CREATE TABLE T_MAIN(" +
                          "ID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, " +
                          "V BLOB(590473235) )");

                  String ins1 = "INSERT INTO T_MAIN(V) VALUES (?)";
                  
                  PreparedStatement ps;
                  ps = c.prepareStatement(ins1);
          byte[] bytes = new byte[35000];
          for (int i = 0; i < 35000; i++)
          bytes[i] = (byte) i ;
          ps.setBytes(1, bytes);
                  ps.executeUpdate();
                  ps.close();

          s.executeUpdate("CREATE TABLE T_COPY ( V1 BLOB(2M), V2 BLOB(2M))");

          Statement stmt = c.createStatement();
          stmt.executeUpdate("INSERT INTO T_COPY SELECT V, V FROM T_MAIN");
          ResultSet rs = stmt.executeQuery("SELECT * FROM T_COPY");
          rs.next();
          String v1 = rs.getString(1);
          String v2 = rs.getString(2);
          System.out.println("v1:" + v1);
          System.out.println("v2:" + v2);
          System.out.println("I am done");

          Exception in thread "main" java.sql.SQLException: Restore of a serializable or SQLData object of class , attempted to re
          ad more data than was originally stored
                  at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)
                  at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:87)
                  at org.apache.derby.impl.jdbc.Util.seeNextException(Util.java:223)
                  at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:398)
                  at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
                  at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2125)
                  at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
                  at org.apache.derby.impl.jdbc.EmbedResultSet.closeOnTransactionError(EmbedResultSet.java:4320)
                  at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:463)
                  at org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:367)
                  at DoubleInsertInto.main(DoubleInsertInto.java:47)
          Caused by: java.sql.SQLException: Restore of a serializable or SQLData object of class , attempted to read more data tha
          n was originally stored
                  at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
                  at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:11
          9)
                  at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
                  ... 10 more
          Caused by: java.sql.SQLException: Java exception: ': java.io.EOFException'.
                  at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
                  at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:11
          9)
                  at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
                  at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:87)
                  at org.apache.derby.impl.jdbc.Util.javaException(Util.java:244)
                  at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:403)
                  ... 8 more
          Caused by: java.io.EOFException
                  at org.apache.derby.iapi.types.SQLBinary.readBinaryLength(SQLBinary.java:350)
                  at org.apache.derby.iapi.types.SQLBinary.readExternalFromArray(SQLBinary.java:328)
                  at org.apache.derby.impl.store.raw.data.StoredPage.readRecordFromArray(StoredPage.java:5568)
                  at org.apache.derby.impl.store.raw.data.StoredPage.restoreRecordFromSlot(StoredPage.java:1497)
                  at org.apache.derby.impl.store.raw.data.BasePage.fetchFromSlot(BasePage.java:459)
                  at org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(GenericScanController.java:75
          9)
                  at org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(HeapScan.java:324)
                  at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(BulkTableScanResultSet.java:327)
                  at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(BulkTableScanResultSet.java:282)
                  at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(BasicNoPutResultSetImpl.java:460)
                  at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:423)
                  ... 2 more































































          The following code which inserts into a table by selecting a blob column twice from another table, causes SQLException: Restore of a serializable or SQLData object of class error selecting from the table. See attached program DoubleInsertInto for full repro. Stack trace is below. Verified back to 10.3 but probably goes back further.



                  s.executeUpdate("CREATE TABLE T_MAIN(" +
                          "ID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, " +
                          "V BLOB(590473235) )");

                  String ins1 = "INSERT INTO T_MAIN(V) VALUES (?)";
                  
                  PreparedStatement ps;
                  ps = c.prepareStatement(ins1);
          byte[] bytes = new byte[35000];
          for (int i = 0; i < 35000; i++)
          bytes[i] = (byte) i ;
          ps.setBytes(1, bytes);
                  ps.executeUpdate();
                  ps.close();

          s.executeUpdate("CREATE TABLE T_COPY ( V1 BLOB(2M), V2 BLOB(2M))");

          Statement stmt = c.createStatement();
          stmt.executeUpdate("INSERT INTO T_COPY SELECT V, V FROM T_MAIN");
          ResultSet rs = stmt.executeQuery("SELECT * FROM T_COPY");
          rs.next();
          String v1 = rs.getString(1);
          String v2 = rs.getString(2);
          System.out.println("v1:" + v1);
          System.out.println("v2:" + v2);
          System.out.println("I am done");

          Exception in thread "main" java.sql.SQLException: Restore of a serializable or SQLData object of class , attempted to re
          ad more data than was originally stored
                  at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)
                  at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:87)
                  at org.apache.derby.impl.jdbc.Util.seeNextException(Util.java:223)
                  at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:398)
                  at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
                  at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2125)
                  at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
                  at org.apache.derby.impl.jdbc.EmbedResultSet.closeOnTransactionError(EmbedResultSet.java:4320)
                  at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:463)
                  at org.apache.derby.impl.jdbc.EmbedResultSet.next(EmbedResultSet.java:367)
                  at DoubleInsertInto.main(DoubleInsertInto.java:47)
          Caused by: java.sql.SQLException: Restore of a serializable or SQLData object of class , attempted to read more data tha
          n was originally stored
                  at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
                  at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:11
          9)
                  at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
                  ... 10 more
          Caused by: java.sql.SQLException: Java exception: ': java.io.EOFException'.
                  at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
                  at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:11
          9)
                  at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
                  at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:87)
                  at org.apache.derby.impl.jdbc.Util.javaException(Util.java:244)
                  at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:403)
                  ... 8 more
          Caused by: java.io.EOFException
                  at org.apache.derby.iapi.types.SQLBinary.readBinaryLength(SQLBinary.java:350)
                  at org.apache.derby.iapi.types.SQLBinary.readExternalFromArray(SQLBinary.java:328)
                  at org.apache.derby.impl.store.raw.data.StoredPage.readRecordFromArray(StoredPage.java:5568)
                  at org.apache.derby.impl.store.raw.data.StoredPage.restoreRecordFromSlot(StoredPage.java:1497)
                  at org.apache.derby.impl.store.raw.data.BasePage.fetchFromSlot(BasePage.java:459)
                  at org.apache.derby.impl.store.access.conglomerate.GenericScanController.fetchRows(GenericScanController.java:75
          9)
                  at org.apache.derby.impl.store.access.heap.HeapScan.fetchNextGroup(HeapScan.java:324)
                  at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.reloadArray(BulkTableScanResultSet.java:327)
                  at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.getNextRowCore(BulkTableScanResultSet.java:282)
                  at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(BasicNoPutResultSetImpl.java:460)
                  at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:423)
                  ... 2 more
          Dag H. Wanvik made changes -
          Link This issue is related to DERBY-4477 [ DERBY-4477 ]
          Hide
          Kristian Waagan added a comment -

          Fixed by the combination of DERBY-4477 and DERBY-4520.
          Verified against trunk revision 980933, but it would be nice if the reporter verified the fix as well.

          I do not plan to back-port the fix(es) due to the rather signficant changes involved, but it may be technically possible.

          Show
          Kristian Waagan added a comment - Fixed by the combination of DERBY-4477 and DERBY-4520 . Verified against trunk revision 980933, but it would be nice if the reporter verified the fix as well. I do not plan to back-port the fix(es) due to the rather signficant changes involved, but it may be technically possible.
          Kristian Waagan made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Fix Version/s 10.6.0.0 [ 12313727 ]
          Resolution Fixed [ 1 ]
          Kathey Marsden made changes -
          Link This issue is required by DERBY-4728 [ DERBY-4728 ]
          Hide
          Kathey Marsden added a comment -

          reopen for 10.5 back port

          Show
          Kathey Marsden added a comment - reopen for 10.5 back port
          Kathey Marsden made changes -
          Resolution Fixed [ 1 ]
          Status Resolved [ 5 ] Reopened [ 4 ]
          Hide
          Kathey Marsden added a comment -

          assigning to myself for back port to 10.5

          Show
          Kathey Marsden added a comment - assigning to myself for back port to 10.5
          Kathey Marsden made changes -
          Assignee Kathey Marsden [ kmarsden ]
          Hide
          Kathey Marsden added a comment -

          This was back ported with the fix for DERBY-3646 to 10.5
          Resolving and assigning to Dag.

          Show
          Kathey Marsden added a comment - This was back ported with the fix for DERBY-3646 to 10.5 Resolving and assigning to Dag.
          Kathey Marsden made changes -
          Fix Version/s 10.5.3.1 [ 12314182 ]
          Resolution Fixed [ 1 ]
          Status Reopened [ 4 ] Resolved [ 5 ]
          Assignee Kathey Marsden [ kmarsden ] Dag H. Wanvik [ dagw ]
          Kathey Marsden made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Gavin made changes -
          Workflow jira [ 12429853 ] Default workflow, editable Closed status [ 12799926 ]

            People

            • Assignee:
              Dag H. Wanvik
              Reporter:
              Kathey Marsden
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development