[
https://issues.apache.org/jira/browse/DERBY-1482?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-1482:
-----------------------------------
Attachment: TriggerTests_ver1_stat.txt
TriggerTests_ver1_diff.txt
The attached patch, TriggerTests_ver1_diff.txt, includes junit test which has
few test fixtures to test triggers on table with LOB columns. Each and every
one of those tests run into OOM probably because we are streaming the before
and after values of LOB columns even when they are not being referenced by the
trigger action. More code investigation is needed to identify different trigger
cases where we should not stream the LOBs.
I have added this junit test, TriggerTests.java, in
org.apache.derbyTesting.functionTests.tests.memory package. It has been added
to the _Suite in that package but has been commented for now with the following
comments.
//Disable following TriggerTests until DERBY-1482 has been fixed.
//Without that fix, the test will run into OOM errors for all
//the test fixtures. This test is written for triggers defined
//on table with LOB columns. No matter whether the LoB columns
//are touched in the trigger action, it appears that Derby is
//streaming the before and after values of LOB columns. Once
//the streaming problem has been resolved, we should be able
//to uncomment the following test.
//suite.addTest(TriggerTests.suite());
The org.apache.derbyTesting.functionTests.tests.memory._Suite gets run as part
of the lowmem suite. lowmem suite runs all the tests in it with 16M heap. I
have tweaked the page cache size for TriggerTests to 100 pages. The reason for
this is that the test deals with pretty big LOBs(320M each) and we will soon
run into OOM because of no memory left for page cache if we ran with the
default page cache of 1000 against the 16M heap.
For now, this suite runs only in embedded mode. When I ran with network server
mode, I got following error. But for now, I just want to focus on the OOM
failures. Once they are fixed, we can probably look at enabling this test for
network server mode.
java.sql.SQLNonTransientConnectionException: Network protocol exception: actual
code point, 4,692, does not match expected code point, 9,224. The connection
has been terminated.
One interesting exception I have noticed for few of the test fixtures (in
addition to OOM) looks something like following.
java.io.FileNotFoundException: system\wombat\log\log211.dat (The system cannot
find the file specified.)
at java.io.FileInputStream.open(Native Method)
at java.io.FileInputStream.<init>(FileInputStream.java:112)
at
org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.copySingleFile(PrivilegedFileOpsForTests.java:245)
at
org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.recursiveCopy(PrivilegedFileOpsForTests.java:222)
at
org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.access$000(PrivilegedFileOpsForTests.java:47)
at
org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests$6.run(PrivilegedFileOpsForTests.java:184)
at
java.security.AccessController.doPrivileged(AccessController.java:251)
at
org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.copy(PrivilegedFileOpsForTests.java:182)
at
org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.recursiveCopy(PrivilegedFileOpsForTests.java:220)
at
org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.access$000(PrivilegedFileOpsForTests.java:47)
at
org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests$6.run(PrivilegedFileOpsForTests.java:184)
at
java.security.AccessController.doPrivileged(AccessController.java:251)
at
org.apache.derbyTesting.functionTests.util.PrivilegedFileOpsForTests.copy(PrivilegedFileOpsForTests.java:182)
at
org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:133)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at
org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.textui.TestRunner.doRun(TestRunner.java:116)
at junit.textui.TestRunner.start(TestRunner.java:172)
at junit.textui.TestRunner.main(TestRunner.java:138)
I do not see this exception for first test fixtures. The reason behind these
additional exceptions may be that we are running into OOM and jvm can start
behaving unpredictably after running into OOMs. To verify that the problem is
not with my tests, I changed the LOB size to 60K from 320M and ran with default
heap and default page cache size and didn't run into above exceptions. Of
course there were no OOMs either because we have enough heap memory to be able
to stream the LOBs. In the patch attached, I have reverted my junit test back
to use 320M LOB data, page cache size of 100 and limited heap of 16M enforced
by the lowmem suite.
The typical OOM exception for the test looks as follows
7)
test1InsertBeforeTrigger(org.apache.derbyTesting.functionTests.tests.memory.TriggerTests)java.sql.SQLException:
Java exception: ': java.lang.OutOfMemoryError'.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:142)
at org.apache.derby.impl.jdbc.Util.javaException(Util.java:299)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:403)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
at
org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2269)
at
org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
at
org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1321)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1673)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPreparedStatement.java:303)
at
org.apache.derbyTesting.functionTests.tests.memory.TriggerTests.runInsertTriggerTest(TriggerTests.java:1479)
at
org.apache.derbyTesting.functionTests.tests.memory.TriggerTests.test1InsertBeforeTrigger(TriggerTests.java:541)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:48)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
at
org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.extensions.TestSetup.run(TestSetup.java:23)
at
org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
at junit.extensions.TestSetup.run(TestSetup.java:23)
Caused by: java.sql.SQLException: Java exception: ':
java.lang.OutOfMemoryError'.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:119)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
... 38 more
Caused by: java.lang.OutOfMemoryError
at
org.apache.derby.iapi.types.SQLBinary.readExternal(SQLBinary.java:421)
at org.apache.derby.iapi.types.SQLBinary.getValue(SQLBinary.java:244)
at org.apache.derby.iapi.types.SQLBinary.loadStream(SQLBinary.java:686)
at
org.apache.derby.impl.sql.execute.DMLWriteResultSet.objectifyStreams(DMLWriteResultSet.java:156)
at
org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:135)
at
org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:494)
at
org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:436)
at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:317)
at
org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1232)
... 31 more
In order to run the junit test to repro the problem, first you will need to
uncomment the test from
org.apache.derbyTesting.functionTests.tests.memory._Suite and compile the code.
Next, you can run the lowmem suite one of the following 2 ways
1)You can run it as an ant target
ant junit-lowmem
2)java -Xmx16M junit.textui.TestRunner
org.apache.derbyTesting.functionTests.tests.memory._Suite
Either one of above runs should show you OOMs for each of the test fixtures in
TriggerTests.java
Following documents the different trigger and LOB combinations that are being
tested in TriggerTests.java. It also shows the test fixture name for that
trigger and LOB test combination
NO REFERENCE CLAUSE in trigger
definition
AFTER INSERT trigger test5InsertAfterTriggerNoReferencingClause
BEFORE INSERT trigger test5InsertBeforeTriggerNoReferencingClause
AFTER DELETE trigger test5DeleteAfterTriggerNoReferencingClause
BEFORE DELETE trigger test5DeleteBeforeTriggerNoReferencingClause
AFTER UPDATE trigger test5UpdateAfterTriggerNoReferencingClause
BEFORE UPDATE trigger test5UpdateBeforeTriggerNoReferencingClause
tirgger created on non-LOB column and no LOB is
referenced in trigger action(*)
AFTER INSERT trigger test1InsertAfterTrigger
BEFORE INSERT trigger test1InsertBeforeTrigger
AFTER DELETE trigger test1DeleteAfterTrigger
BEFORE DELETE trigger test1DeleteBeforeTrigger
AFTER UPDATE trigger test1UpdateAfterTrigger
BEFORE UPDATE trigger test1UpdateBeforeTrigger
tirgger created on non-LOB column and LOB is accessed in
trigger action(+)
AFTER INSERT trigger test2InsertAfterTriggerAccessLOB
BEFORE INSERT trigger test2InsertBeforeTriggerAccessLOB
AFTER DELETE trigger test2DeleteAfterTriggerAccessLOB
BEFORE DELETE trigger test2DeleteBeforeTriggerAccessLOB
AFTER UPDATE trigger test2UpdateAfterTriggerAccessLOB
BEFORE UPDATE trigger test2UpdateBeforeTriggerAccessLOB
tirgger created on non-LOB column and LOB is getting UPDATEed
in trigger action(+)
AFTER INSERT trigger test2InsertAfterTriggerUpdatedLOB
BEFORE INSERT trigger No test - BEFORE triggers do not allow
INSERT/UPDATE/DELETE in trigger action
AFTER DELETE trigger No test - the row has been deleted and hence no way
for trigger action to modify the LOB of the deleted row
BEFORE DELETE trigger No test - BEFORE triggers do not allow
INSERT/UPDATE/DELETE in trigger action
AFTER UPDATE trigger test2UpdateAfterTriggerUpdatedLOB
BEFORE UPDATE trigger No test - BEFORE triggers do not allow
INSERT/UPDATE/DELETE in trigger action
tirgger created on LOB column and no LOB is referenced
in trigger action (*)
AFTER INSERT trigger No test because INSERT trigger is defined on a row,
not a column. test1InsertAfterTrigger will suffice.
BEFORE INSERT trigger No test because INSERT trigger is defined on a row,
not a column. test1InsertBeforeTrigger will suffice.
AFTER DELETE trigger No test because DELETE trigger is defined on a row,
not a column. test1DeleteAfterTrigger will suffice.
BEFORE DELETE trigger No test because DELETE trigger is defined on a row,
not a column. test1DeleteBeforeTrigger will suffice.
AFTER UPDATE trigger test3UpdateAfterTrigger
BEFORE UPDATE trigger test3UpdateBeforeTrigger
tirgger created on LOB column and LOB is accessed in
trigger action(+)
AFTER INSERT trigger No test because INSERT trigger is defined on a row,
not a column. test2InsertAfterTriggerAccessLOB will suffice.
BEFORE INSERT trigger No test because INSERT trigger is defined on a row, not
a column. test2InsertBeforeTriggerAccessLOB will suffice.
AFTER DELETE trigger No test because DELETE trigger is defined on a row,
not a column. test2DeleteAfterTriggerAccessLOB will suffice.
BEFORE DELETE trigger No test because DELETE trigger is defined on a row, not
a column. test2DeleteBeforeTriggerAccessLOB will suffice.
AFTER UPDATE trigger test4UpdateAfterTriggerAccessLOB
BEFORE UPDATE trigger test4UpdateBeforeTrigger
tirgger created on LOB column and LOB is getting
UPDATEed in trigger action(+)
AFTER INSERT trigger No test because INSERT trigger is defined on a row, not
a column. test2InsertAfterTriggerUpdatedLOB will suffice.
BEFORE INSERT trigger No test - BEFORE triggers do not allow
INSERT/UPDATE/DELETE in trigger action
AFTER DELETE trigger No test - the row has been deleted and hence no way for
trigger action to modify the LOB of the deleted row
BEFORE DELETE trigger No test - BEFORE triggers do not allow
INSERT/UPDATE/DELETE in trigger action
AFTER UPDATE trigger test4UpdateAfterTriggerUpdatedLOB
BEFORE UPDATE trigger No test - BEFORE triggers do not allow
INSERT/UPDATE/DELETE in trigger action
(*) Has two versions of this trigger+LOB combination. The trigger action is
coded as either a stored procedure call or as a direct SQL statement.
(+)These test cases do not have stored procedure version of them. The reason is
that the trigger action references LOB columns and at this point, there is no
way to pass LOB parameters to a stored procedure
> Update triggers on tables with blob columns stream blobs into memory even
> when the blobs are not referenced/accessed.
> ---------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1482
> URL: https://issues.apache.org/jira/browse/DERBY-1482
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Reporter: Daniel John Debrunner
> Priority: Minor
> Attachments: derby1482DeepCopyAfterTriggerOnLobColumn.java,
> derby1482Repro.java, derby1482ReproVersion2.java, TriggerTests_ver1_diff.txt,
> TriggerTests_ver1_stat.txt
>
>
> Suppose I have 1) a table "t1" with blob data in it, and 2) an UPDATE trigger
> "tr1" defined on that table, where the triggered-SQL-action for "tr1" does
> NOT reference any of the blob columns in the table. [ Note that this is
> different from DERBY-438 because DERBY-438 deals with triggers that _do_
> reference the blob column(s), whereas this issue deals with triggers that do
> _not_ reference the blob columns--but I think they're related, so I'm
> creating this as subtask to 438 ]. In such a case, if the trigger is fired,
> the blob data will be streamed into memory and thus consume JVM heap, even
> though it (the blob data) is never actually referenced/accessed by the
> trigger statement.
> For example, suppose we have the following DDL:
> create table t1 (id int, status smallint, bl blob(2G));
> create table t2 (id int, updated int default 0);
> create trigger tr1 after update of status on t1 referencing new as n_row
> for each row mode db2sql update t2 set updated = updated + 1 where t2.id =
> n_row.id;
> Then if t1 and t2 both have data and we make a call to:
> update t1 set status = 3;
> the trigger tr1 will fire, which will cause the blob column in t1 to be
> streamed into memory for each row affected by the trigger. The result is
> that, if the blob data is large, we end up using a lot of JVM memory when we
> really shouldn't have to (at least, in _theory_ we shouldn't have to...).
> Ideally, Derby could figure out whether or not the blob column is referenced,
> and avoid streaming the lob into memory whenever possible (hence this is
> probably more of an "enhancement" request than a bug)...
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
