db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tom Deering (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DERBY-5981) Derby INSERT Eats Stack Space, Causes java.lang.StackOverflowError
Date Mon, 05 Nov 2012 15:34:13 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5981?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13490686#comment-13490686
] 

Tom Deering edited comment on DERBY-5981 at 11/5/12 3:33 PM:
-------------------------------------------------------------

Thank you all. I sent this to derby-users as well, but I'm struggling to get bulk-import to
work for my use case.

I have a table that has the following columns:

INTEGER A, INTEGER B, DATE C, FLOAT D, FLOAT E, FLOAT F, FLOAT G, INTEGER H, FLOAT I
A is my primary, auto-generated key
B is a foreign key referencing the primary key of another table.

I have a csv file containing many rows of data for columns C - I that I would like to import
with "CALL SYSCS_UTIL.SYSCS_IMPORT_DATA". All rows of the csv file correspond to the same
value for B. If B is left unspecified, then I get:

Column 'B'  cannot accept a NULL value. 

QUESTION: How can I bulk-import the data for columns C - I while specifying the use of the
same value for column B? The best I can think of is to add the value for B to every row of
the CSV file prior to importing.
                
      was (Author: tdeering):
    Thank you all. I sent this to derby-users as well, but I'm struggling to get bulk-import
to work for my use case.

I have a table that has the following columns:

INTEGER A, INTEGER B, DATE C, FLOAT D, FLOAT E, FLOAT F, FLOAT G, INTEGER H, FLOAT I
A is my primary, auto-generated key
B is a foreign key referencing the primary key of another table.

I have a csv file containing many rows of data for columns C - I that I would like to import
with "CALL SYSCS_UTIL.SYSCS_IMPORT_DATA". All rows of the csv file correspond to the same
value for B. If B is left unspecified, then I get:

Column 'B'  cannot accept a NULL value. 

QUESTION: How can I bulk-import the data for columns C - I while specifying the use of the
same value for column B? The best I can think of is to manually add B to every row of the
CSV file prior to importing.
                  
> Derby INSERT Eats Stack Space, Causes java.lang.StackOverflowError
> ------------------------------------------------------------------
>
>                 Key: DERBY-5981
>                 URL: https://issues.apache.org/jira/browse/DERBY-5981
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.9.1.0
>         Environment: Ubuntu Linux 12.04.1 64-bit, Sun Java 1.7.0_09, Derby 10.9.1.0
>            Reporter: Tom Deering
>            Priority: Minor
>
> Apache Derby 10.9.1.0 causes a java.lang.StackOverflowError with large INSERT statements.
Here, "large" means 5000+ rows. I am using Derby in my Java application with the EmbeddedDriver
in the following way:
> Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
> Connection connection = DriverManager.getConnection("jdbc:derby:<db path>;create=true;upgrade=true")
> String insertString = buildSQLInsertString();
> Statement stmt = connection.createStatement();
> stmt.execute(insertString); // StackOverflowError
> I have tried calling this in different ways, including with PreparedStatement. I have
also tried increasing derby.storage.pageSize, to no effect. If I increase the VM argument
-Xss to something insanely big, then after ~30 seconds I get:
> SQL Error: Statement too complex. Try rewriting the query to remove complexity. Eliminating
many duplicate expressions or breaking up the query and storing interim results in a temporary
table can often help resolve this error.
> My code works fine for smallish inserts, but overflows the stack for anything modestly
large. I can't tell if Derby is using an unreasonably large amount of stack space with INSERT,
or if the size of what I am inserting is unreasonable.
> I'm a newcomer to database development and also to Apache Derby, so suggestions for details
to make this bug report more useful would be welcome.
> EDIT: I updated my code to insert in chunks of at most 1000 rows at a time. This seems
to be a viable workaround... but I'm still interested in inserts using a single statement
and wondering if there is a bug with Derby's INSERT.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message