ClassFormatError while performing a SQL insert

[Leslie Software <le...@yahoo.com>]

I received a ClassFormatError today while trying execute some insert SQL.  The SQL is quite long and abridged versions succeed.  I have not examined my data closely yet but I will.  Here is the full story:

In my database loading code I am working on updating a table (named ruling).  To do that the code drops the table and then creates it again followed by inserting the data (as read from an xml file).  The log contains the following

2006-09-07 13:21:26.067 GMT Thread[main,5,main] (XID = 5935), (SESSIONID = 1), drop table ruling ******* null
2006-09-07 13:21:27.082 GMT Thread[main,5,main] (XID = 5935), (SESSIONID = 1), create table ruling (ruling_id INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT ruling_pk PRIMARY KEY, card_id INT, ruling_date DATE, ruling VARCHAR (1024), CONSTRAINT ruling_card_fk FOREIGN KEY (card_id) REFERENCES card) ******* null
2006-09-07 13:21:28.911 GMT Thread[main,5,main] Wrote class org.apache.derby.exe.ac08264012x010dx8870x7dfdx0000000be0501 to file ac08264012x010dx8870x7dfdx0000000be0501.class. Please provide support with the file and the following exception information: java.lang.ClassFormatError: Invalid method Code length 69936 in class file org/apache/derby/exe/ac08264012x010dx8870x7dfdx0000000be0501
 
I have stepped through my code and the exception comes after I attempt to execute the insert sql that looks like this:
insert into ruling (card_id, ruling_date, ruling) values (1423,'2006-02-01','If your opponent discards any cards during his or her cleanup step to get down to the maximum hand size, the ability will trigger and players will receive priority. After that cleanup step ends, a new one will begin and Abyssal Nocturnus’s effect will end. It won’t carry over into the next turn.'), 
(1423,'2006-02-01','If your opponent discards multiple cards to a single effect, this will trigger multiple times.'), 
(1587,'2006-07-15','Adarkar Valkyrie’s ability can target a token creature, but since token creatures cease to exist when they leave play, it won’t be returned to play.'), 
(956,'2005-10-01','Attacking creatures (including Agrus Kos) that are both red and white get +2/+2 until end of turn.'), 
(674,'2004-12-01','The “play this ability only once each turn” restriction applies even if the creature changes controllers.'), 
(349,'2005-06-01','Akuta must already be in your graveyard as your upkeep begins in order for its ability to trigger.'), 
(349,'2005-06-01','In multiplayer games, it checks for each opponent.'), 
(1589,'2006-07-15','You may pay the alternative cost rather than the card’s mana cost. Any additional costs are paid as normal.'), 
(1589,'2006-07-15','Paying the alternative cost doesn’t change when you can play the spell. A creature spell you play this way, for example, can still only be played during your main phase while the stack is empty.'), 
(1589,'2006-07-15','If you don’t have two cards of the right color in your hand, you can’t choose to play the spell using the alternative cost.'), 
...
(346,'2005-06-01','You choose the mode on announcement.'), 
(1424,'2006-02-01','AEtherplasm will return to your hand before any combat damage is assigned.'), 
(1424,'2006-02-01','If you want, you can return AEtherplasm to your hand and not put anything into play. The attacking creature will still be considered blocked, so it won’t be able to deal combat damage to you unless it has trample or a similar ability.'), 
(1424,'2006-02-01','The creature you put into play may be the AEtherplasm you just returned to your hand. This allows it to dodge abilities likeDeathgazer’s.'), 
(1424,'2006-02-01','The creature you put into play from your hand is blocking the attacking creature, even if the block couldn’t legally be declared (for example, if that creature comes into play tapped, or it can’t block, or the attacking creature has protection from it).'), 
(1424,'2006-02-01','Putting a blocking creature into play doesn’t trigger “When this creature blocks” and “When this creature becomes blocked” abilities. It also won’t check blocking restrictions, costs, or requirements.')

the total sql statement contains 1742 lines

When I create the sql statement with the shortest 150 lines (only those lines where the ruling text is < 80) instead all is well.  The maximum length of the ruling text is 700 characters so I am well under the 1024 limit on the column.

Is this a known issue?  Is my SQL too long?  I plan to look closely at my data but at 1741 lines of values that will take a while.  

Any help or advice would be appreciated.

Thanks,

Ian


--
Ian Leslie - Shareware Author (mailto:lesliesoftware@yahoo.com)

Re: ClassFormatError while performing a SQL insert

[Daniel John Debrunner <dj...@apache.org>]

Bryan Pendleton wrote:

>> java.lang.ClassFormatError: Invalid method Code length 69936 in class 
> 
> 
> There is a hard limit in the JDK class-file format of 64K

Just to be clear, one of the limits in the Java class file format is 64k
bytes of byte code instructions per-method. A large String constant in a
method does *not* contribute to this limit. There is a limit on *number*
of constants in a java class file (65535) but not the size of those
constants. What makes up a entry in what is called the "constant pool"
is more complicated than a 1-1 mapping with literals in the Java
language, e.g. class names and descriptions of methods take up entries,
a String literal will take up two entries, a integer constant can take
up no entries.

For bedtime reading see

http://java.sun.com/docs/books/vmspec/

(section 4.10 in the *first* edition contains the limitations of the
class file format).

Dan.

Re: ClassFormatError while performing a SQL insert

[Daniel John Debrunner <dj...@apache.org>]

Michael Segel wrote:

> Outch. Missed the first part of Derby's statement.
> 
> Ok.
> 
> Doesn't make sense though. 
> His insert is a batch insert? 

No, a single INSERT with a VALUES clause that has multiple rows (I think
he said around 1700).

> Imagine if you had a table where you were inserting a record that was larger
> than 64KB. (Not including blobs) Are you saying that Derby would have failed
> prior to your fix?

No. The record size is not a factor here. It's the size of the generated
 code in a single method. The byte code required to assemble those rows
at runtime from the VALUES clause. The size of any individual value has
no real effect on the size of the byte code, the same amount of byte
code is required to create 'Fred' as 'the complete work of Shakespeare
follows ....'.

Dan.

RE: ClassFormatError while performing a SQL insert

[Michael Segel <ms...@segel.com>]

Outch. Missed the first part of Derby's statement.

Ok.

Doesn't make sense though. 
His insert is a batch insert? 
Imagine if you had a table where you were inserting a record that was larger
than 64KB. (Not including blobs) Are you saying that Derby would have failed
prior to your fix?

Within the limitations of Derby and (Java), Leslie's app design doesn't make
sense to begin with.



> -----Original Message-----
> From: Daniel John Debrunner [mailto:djd@apache.org]
> Sent: Thursday, September 07, 2006 1:33 PM
> To: Derby Discussion
> Subject: Re: ClassFormatError while performing a SQL insert
> 
> derby@segel.com wrote:
> 
> > But his issue isn't with Derby.
> >
> > He'd have the same problem with any Java app and a JDBC connection.
> > We know this because Leslie wrote that if he inserted just a subset of
> his
> > data, it would work. ;-)
> 
> Nope, the error Leslie reported is for a class generated by Derby:
> 
> Leslie's error:
> > org.apache.derby.exe.ac08264012x010dx8870x7dfdx0000000be0501 to file
> ac08264012x010dx8870x7dfdx0000000be0501.class.
> >java.lang.ClassFormatError: Invalid method Code length 69936 in class
> file
> > org/apache/derby/exe/ac08264012x010dx8870x7dfdx0000000be0501
> 
> The problem is that with a large SQL INSERT statement with many rows,
> all values as literals the generated class size is a factor of the
> number of rows. Reducing the number of rows reduces the generated
> method's size and hence it works, increasing the number of rows
> increases the method's size beyond the 64k limit. Changes in 10.2 allow
> the generated code to be split over many methods, working around the
> per-method limit.
> 
> If it was an issue due to his application class being too big, it would
> have failed when he compiled it, not at run time.
> 
> But hey what do I know, I just spent six months fixing the very issue
> that Leslie is running into. :-)
> 
> Dan.

Re: ClassFormatError while performing a SQL insert

[Daniel John Debrunner <dj...@apache.org>]

derby@segel.com wrote:

> But his issue isn't with Derby.
> 
> He'd have the same problem with any Java app and a JDBC connection.
> We know this because Leslie wrote that if he inserted just a subset of his
> data, it would work. ;-)

Nope, the error Leslie reported is for a class generated by Derby:

Leslie's error:
> org.apache.derby.exe.ac08264012x010dx8870x7dfdx0000000be0501 to file ac08264012x010dx8870x7dfdx0000000be0501.class.
>java.lang.ClassFormatError: Invalid method Code length 69936 in class file
> org/apache/derby/exe/ac08264012x010dx8870x7dfdx0000000be0501

The problem is that with a large SQL INSERT statement with many rows,
all values as literals the generated class size is a factor of the
number of rows. Reducing the number of rows reduces the generated
method's size and hence it works, increasing the number of rows
increases the method's size beyond the 64k limit. Changes in 10.2 allow
the generated code to be split over many methods, working around the
per-method limit.

If it was an issue due to his application class being too big, it would
have failed when he compiled it, not at run time.

But hey what do I know, I just spent six months fixing the very issue
that Leslie is running into. :-)

Dan.

RE: ClassFormatError while performing a SQL insert

[de...@segel.com]

But his issue isn't with Derby.

He'd have the same problem with any Java app and a JDBC connection.
We know this because Leslie wrote that if he inserted just a subset of his
data, it would work. ;-)

But hey! What do I know? (I've never worked in support. ;-)

> -----Original Message-----
> From: Daniel John Debrunner [mailto:djd@apache.org]
> Sent: Thursday, September 07, 2006 12:30 PM
> To: Derby Discussion
> Subject: Re: ClassFormatError while performing a SQL insert
> 
> Leslie Software wrote:
> > Interesting.  I though that was a strange error to be getting when
> running SQL.  Does that mean that Derby is generating Java code from the
> SQL to do the actual work?
> >
> 
> Yes, you are correct. Derby generates a Java class file for most SQL
> statements, the exceptions being DDL statements like "CREATE TABLE".
> 
> See the "Internals of Derby" presentation here:
> 
> http://db.apache.org/derby/papers/MiscPresentations.html#Colorado+Software
> +Summit+2004
> 
> Dan.

Re: ClassFormatError while performing a SQL insert

[Leslie Software <le...@yahoo.com>]

Interesting presentation I am going to enjoy reading the rest of it.

Thank you.

Ian

----- Original Message ----
From: Daniel John Debrunner <dj...@apache.org>
<snip>Yes, you are correct. Derby generates a Java class file for most SQL
statements, the exceptions being DDL statements like "CREATE TABLE".

See the "Internals of Derby" presentation here:

http://db.apache.org/derby/papers/MiscPresentations.html#Colorado+Software+Summit+2004

Dan.

Re: ClassFormatError while performing a SQL insert

[Daniel John Debrunner <dj...@apache.org>]

Leslie Software wrote:
> Interesting.  I though that was a strange error to be getting when
running SQL.  Does that mean that Derby is generating Java code from the
SQL to do the actual work?
>

Yes, you are correct. Derby generates a Java class file for most SQL
statements, the exceptions being DDL statements like "CREATE TABLE".

See the "Internals of Derby" presentation here:

http://db.apache.org/derby/papers/MiscPresentations.html#Colorado+Software+Summit+2004

Dan.

RE: ClassFormatError while performing a SQL insert

[de...@segel.com]

> -----Original Message-----
> From: Leslie Software [mailto:lesliesoftware@yahoo.com]
> Sent: Thursday, September 07, 2006 12:07 PM
> To: Derby Discussion
> Subject: Re: ClassFormatError while performing a SQL insert
> 
> Iteresting.  I though that was a strange error to be getting when running
> SQL.  Does that mean that Derby is generating Java code from the SQL to do
> the actual work?
> 
> Ian
> 
No.
It means that your java code has a method that is longer than 64K.
Note: It's not the class but the method which is limited to the 64K.

I think Daniel pointed you in the right direction. That is, that you should
consider using a prepared statement to perform the insert in a loop.

Looking back at your initial post, it looks like you're hard coding the
insert manually in your code. That is to say, with all of your data manually
hardwired in to the method, the method exceeds 64KB in length.

This is *not* a good idea. (Imagine if you need to change a value being
inserted. You'll then have to open up the source code, modify and then
recompile.)

Just a simple suggestion, you may want to consider putting your values in to
a character delimited flat file. Then in your code, you open the file, read
line by line in to a buffer, parse the buffer and insert the data via a
prepared statement.

Note: if you use commas to separate your fields, you'll have to track your
quotations. Try using a '|' (pipe character) as your delimeter.

Its just a suggestion which will make your code a tad easier to maintain.

But hey! What do I know? ;-)

-G


> ----- Original Message ----
> From: Bryan Pendleton <bp...@amberpoint.com>
> 
> > java.lang.ClassFormatError: Invalid method Code length 69936 in class
> 
> There is a hard limit in the JDK class-file format of 64K, and there are
> some known issues in Derby which can cause the generated code to exceed
> these limits.
> 
> <snip>
> 
> 

Re: ClassFormatError while performing a SQL insert

[Leslie Software <le...@yahoo.com>]

Iteresting.  I though that was a strange error to be getting when running SQL.  Does that mean that Derby is generating Java code from the SQL to do the actual work?

Ian
 
----- Original Message ----
From: Bryan Pendleton <bp...@amberpoint.com>

> java.lang.ClassFormatError: Invalid method Code length 69936 in class 

There is a hard limit in the JDK class-file format of 64K, and there are
some known issues in Derby which can cause the generated code to exceed
these limits.

<snip>

Re: ClassFormatError while performing a SQL insert

[Bryan Pendleton <bp...@amberpoint.com>]

> java.lang.ClassFormatError: Invalid method Code length 69936 in class 

There is a hard limit in the JDK class-file format of 64K, and there are
some known issues in Derby which can cause the generated code to exceed
these limits.

Tremendous improvements have been made in this area over recent months;
are you able to try your test with the latest trunk code or with the
10.2 release beta? We'd love to have more testing of the beta release!

http://wiki.apache.org/db-derby/TenTwoRelease

thanks,

bryan

Re: ClassFormatError while performing a SQL insert

[Leslie Software <le...@yahoo.com>]

Thanks.  I'll either break it up into smaller chunks or use the prepared statement approch.

To beta test the 10.2 can I replace my current jars and just use a copy of an old database as a test or do I need to start with a new one.  I would like to give it a try.

It sounds like I would be better off inserting in a different way though.

Ian
 
--
Ian Leslie - Shareware Author (mailto:lesliesoftware@yahoo.com)


----- Original Message ----
From: Daniel John Debrunner <dj...@apache.org>
<snip>> Is this a known issue?  Is my SQL too long? 

Some of this is DERBY-766/DERBY-176 that have been fixed in 10.2, now in
beta and available for testing:

   http://wiki.apache.org/db-derby/TenTwoRelease

Note that beta jars not suitable for production use and create databases
that cannot be upgraded to GA versions.

Most likely that will fix your problem. There is another optimisation
(that I can't find the Jira number for) that will resolve any issues you
may hit with a large number of character constants in a statement (as in
your case). I think the number of rows you have mean you won't hit the
issue.

The other option is use to a single SQL statement per row inserted and
use PreparedStatements. You could use a loop with ps.executeUpdate() to
insert many rows, or ps.addBatch().

insert into ruling (card_id, ruling_date, ruling) values (?,?,?)

Dan.

Re: ClassFormatError while performing a SQL insert

[Daniel John Debrunner <dj...@apache.org>]

Leslie Software wrote:
[snip ClassFormatError with long SQL]


> Is this a known issue?  Is my SQL too long? 

Some of this is DERBY-766/DERBY-176 that have been fixed in 10.2, now in
beta and available for testing:

   http://wiki.apache.org/db-derby/TenTwoRelease

Note that beta jars not suitable for production use and create databases
that cannot be upgraded to GA versions.

Most likely that will fix your problem. There is another optimisation
(that I can't find the Jira number for) that will resolve any issues you
may hit with a large number of character constants in a statement (as in
your case). I think the number of rows you have mean you won't hit the
issue.

The other option is use to a single SQL statement per row inserted and
use PreparedStatements. You could use a loop with ps.executeUpdate() to
insert many rows, or ps.addBatch().

insert into ruling (card_id, ruling_date, ruling) values (?,?,?)

Dan.