Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6884

SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE can't import more than Integer.MAX_VALUE bytes of blob data



    • Bug
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • SQL
    • None
    • Normal
    • Newcomer, Repro attached
    • Seen in production


      Using SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE to export a table containing a blob column, SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE will fail with a NumberFormatException if the offset for a blob record is > Integer.MAX_VALUE. This is because ImportReadData.initExternalLobFile() is parsing the offset as an Integer.

      The stack trace and a program to reproduce are below.

      java.lang.NumberFormatException: For input string: "2147483770"
      at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) ~[na:1.8.0_45]
      at java.lang.Integer.parseInt(Integer.java:583) ~[na:1.8.0_45]
      at java.lang.Integer.parseInt(Integer.java:615) ~[na:1.8.0_45]
      at org.apache.derby.impl.load.ImportReadData.initExternalLobFile(Unknown Source) ~[derby-]
      at org.apache.derby.impl.load.ImportReadData.getBlobColumnFromExtFile(Unknown Source) ~[derby-]
      at org.apache.derby.impl.load.ImportAbstract.getBlob(Unknown Source) ~[derby-]
      at org.apache.derby.impl.load.Import.getBlob(Unknown Source) ~[derby-]
      at org.apache.derby.iapi.types.SQLBlob.setValueFromResultSet(Unknown Source) ~[derby-]
      at org.apache.derby.impl.sql.execute.VTIResultSet.populateFromResultSet(Unknown Source) ~[derby-]
      at org.apache.derby.impl.sql.execute.VTIResultSet.getNextRowCore(Unknown Source) ~[derby-]
      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown Source) ~[derby-]
      at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(Unknown Source) ~[derby-]
      at org.apache.derby.impl.sql.execute.NoPutResultSetImpl.getNextRowFromRowSource(Unknown Source) ~[derby-]
      at org.apache.derby.impl.store.access.heap.HeapController.load(Unknown Source) ~[derby-]
      at org.apache.derby.impl.store.access.heap.Heap.load(Unknown Source) ~[derby-]
      at org.apache.derby.impl.store.access.RAMTransaction.loadConglomerate(Unknown Source) ~[derby-]
      at org.apache.derby.impl.store.access.RAMTransaction.recreateAndLoadConglomerate(Unknown Source) ~[derby-]
      at org.apache.derby.impl.sql.execute.InsertResultSet.bulkInsertCore(Unknown Source) ~[derby-]
      at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source) ~[derby-]
      at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source) ~[derby-]
      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source) ~[derby-]
      ... 36 common frames omitted

      package blob;

      import java.io.BufferedInputStream;
      import java.io.File;
      import java.io.FileInputStream;
      import java.io.IOException;
      import java.sql.*;

      public final class DerbyIssue {
      // derby url
      public static final String DBURL = "jdbc:derby:testdb;create=true";
      // any random binary file such as a large image or document
      public static final String BLOB_DATA_FILE = "...";
      public static final String EXPORT_TABLE_FILE = "table-data";
      public static final String EXPORT_BLOB_FILE = "blob-data";

      public static void main(String... args) throws Exception

      { final DerbyIssue test = new DerbyIssue(); test.run(); }

      public void run() throws Exception {

      try(final Connection con = DriverManager.getConnection(DBURL)) {
      try (final Statement stmt = con.createStatement())

      { stmt.execute("CREATE TABLE TESTBLOB(id BIGINT, content BLOB)"); }

      System.out.printf("inserting test data%n");

      try (final PreparedStatement pstmt = con.prepareStatement("INSERT INTO TESTBLOB (id, content) VALUES (?, ?)")) {
      long id = 1;
      long byteCount = 0;
      final File content = new File(BLOB_DATA_FILE);
      while (byteCount < Integer.MAX_VALUE) {
      insertBlob(pstmt, id, content);
      byteCount += content.length();
      if (id % 100 == 0)

      { System.out.printf("%d%n", byteCount); }

      insertBlob(pstmt, id, content);
      byteCount += content.length();

      System.out.printf("%d bytes written to testblob table%n", byteCount);

      final File exportFile = new File(EXPORT_TABLE_FILE);
      final File blobFile = new File(EXPORT_BLOB_FILE);
      try (final CallableStatement stmt = con.prepareCall(
      "CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE (null, ?, ?, null, null, null, ?)"))

      { stmt.setString(1, "TESTBLOB"); stmt.setString(2, exportFile.toString()); stmt.setString(3, blobFile.toString()); stmt.execute(); }

      System.out.printf("testblob table exported%n");

      try (final Statement stmt = con.createStatement())

      { stmt.execute("TRUNCATE TABLE TESTBLOB"); }

      System.out.printf("testblob table truncated%n");

      try (final CallableStatement stmt = con.prepareCall(
      "CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE_LOBS_FROM_EXTFILE (null, ?, ?, null, null, null, 0)"))

      { stmt.setString(1, "TESTBLOB"); stmt.setString(2, exportFile.toString()); stmt.execute(); }

      System.out.printf("testblob data imported%n");

      private void insertBlob(PreparedStatement pstmt, long id, File content) throws IOException, SQLException {
      try(BufferedInputStream contentStream = new BufferedInputStream(new FileInputStream(content)))

      { pstmt.setLong(1, id); pstmt.setBinaryStream(2, contentStream); pstmt.executeUpdate(); }



        1. JustChangeOffset.diff
          9 kB
          Bryan Pendleton
        2. testForLargeDataSuite.diff
          7 kB
          Bryan Pendleton
        3. firstTryAtTest.diff
          3 kB
          Bryan Pendleton
        4. trivial.diff
          2 kB
          Bryan Pendleton
        5. DerbyIssue.java
          3 kB
          Bryan Pendleton



            bryanpendleton Bryan Pendleton
            ehowe Edward Howe
            0 Vote for this issue
            4 Start watching this issue