“disk I/O error” when the size of the ResultSet exceeds ~2GB and “ORDER BY” is used

[GoogleCodeExporter]

I am getting a “disk I/O error” when the size of the ResultSet exceeds 
~2GB and “ORDER BY” is used:
java.sql.SQLException: disk I/O error
at org.sqlite.DB.execute(DB.java:366)
at org.sqlite.Stmt.exec(Stmt.java:56)
at org.sqlite.Stmt.executeQuery(Stmt.java:90)
. . .

I am using sqlite-jdbc-3.6.16.jar on Linux 2.6.9-78.0.0.8.ELsmp (Red Hat 
Enterprise Linux WS release 4 (Nahant Update 7)) with Java 6 update 15 (64 
bit).

The error does not occur if:
1) I remove the “ORDER BY” clause
2) I break up the query to give ResultSet that does not exceed 2 GB in size
3) I run the query from sqlite3 command line interface

These symptoms suggest an integer wrap-around or other 32 bit defect on 
the Java side related to sorting.

Original issue reported on code.google.com by kaipf...@gmail.com on 15 Aug 2009 at 11:02

[GoogleCodeExporter]

The size of ResultSet exceeding 2GB means many rows in a table or each row has 
a huge 
volume of column data? 

Disk I/O error message in SQLite mixes up several types of errors described in 
http://sqlite.org/c3ref/c_ioerr_access.html

So the cause of your error might be "out of memory" (using -Xmx512m JVM option 
might 
solve the problem), or the SQLite engine failed to create a temporary file 
larger 
than 2GB for sorting table data, etc. 

However, the current implmentation of SQLite JDBC does not report such an 
detailed 
error code, so I have to modify the code to clarify the cause of the error.

Original comment by taroleo on 16 Aug 2009 at 7:38

• Changed state: Started
• Added labels: Project-sqlite-jdbc, OpSys-Linux, Version-3.6.16
• Removed labels: Project-xerial-core, OpSys-Windows, Version-1.0

[GoogleCodeExporter]

ResultSet and table exceed 2 GB.  Tables which have less that 2GB of data do 
not have
the problem.

I do not believe it is memory problem.  I am using -Xmx7000m on an 8 GB machine 
with
a 64 bit JVM.

I do not get the error when executing the same query using the sqlite3 command 
line
interface, so I'm not sure it is even an SQLite problem.  Is there anything 
going on
in the Java side of the jdbc driver related to sorting (ORDER BY), eg using an 
int
where a long should be used, and then passing it to the native interface?

Original comment by kaipf...@gmail.com on 16 Aug 2009 at 9:48

[GoogleCodeExporter]

I built a new version 3.6.16.1, which is changed to report an error message 
with an 
error code when SQLException occurred.

http://www.xerial.org/maven/repository/artifact/org/xerial/sqlite-jdbc/3.6.16.1/

Could you try this new version, and show me the reported error message?

SQLite JDBC uses integer number to count rows in a ResultSet, so if the number 
of 
result rows exceeds 2^31-1, some strange behavior might happens. However, the 
reported exception point is simply calling SQLite's C API, and when the 
returned 
status code shows some error, an SQLException will be thrown.  

Original comment by taroleo on 16 Aug 2009 at 10:45

[GoogleCodeExporter]

Original comment by taroleo on 10 Sep 2009 at 5:56

• Added labels: Milestone-sqlite-jdbc-3.6.18

[GoogleCodeExporter]

The result when these errors occur is a few million rows, so row count integer 
wrap-
around doesn't seem to be a factor.  However, each row of the result includes a 
BLOB 
of about ~1700 bytes, so the total size of the result, together with using 
"ORDER 
BY", seems to be the relevant factors; results less than ~ 1 million rows don't 
give 
the error.

I've worked around this problem by redesigning the schema and queries to so 
that 
"ORDER BY" is no longer needed and the query results are also smaller, and I 
haven't 
had a chance to test the 3.6.16.1 version yet.

Original comment by kaipf...@gmail.com on 10 Sep 2009 at 6:19

[GoogleCodeExporter]

java.sql.SQLException: [SQLITE_IOERR]  Some kind of disk I/O error occurred 
(disk I/O
error)
    at org.sqlite.DB.newSQLException(DB.java:352)
    at org.sqlite.DB.execute(DB.java:331)
    at org.sqlite.Stmt.exec(Stmt.java:56)
    at org.sqlite.Stmt.executeQuery(Stmt.java:90)
    at com.aramco.swb.control.SQLiteTest.main(SQLiteTest.java:168)

Original comment by kaipf...@gmail.com on 13 Sep 2009 at 3:59

Attachments:

• SQLiteTest.java

[GoogleCodeExporter]

I created a test and ran it using 3.6.16.1, the error is similar (see previous 
comment).
The test ran ok with 1 million 2KB byte arrays, and failed with 2 million 2KB 
byte
arrays (blobs).

Original comment by kaipf...@gmail.com on 13 Sep 2009 at 4:01

[GoogleCodeExporter]

I executed the same query from the command line, and this time a disk I/O error
occurred, so it does not appear to JDBC specific after all:

56% sqlite3 SQLiteTest.db
SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select OBJECT from TLM order by UMAPID desc, BINID desc, MODIFIED desc;
SQL error: disk I/O error
sqlite>                

Original comment by kaipf...@gmail.com on 13 Sep 2009 at 6:26

[GoogleCodeExporter]

Thanks for the detailed reports. This error should be reported to the original
SQLite's issue tracker.

Now that this error was found not specific to the JDBC driver, I will change the
status of this issue to WontFix.

Original comment by taroleo on 13 Sep 2009 at 12:04

• Changed state: WontFix