[
https://issues.apache.org/jira/browse/DERBY-4874?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12990144#comment-12990144
]
Knut Anders Hatlen commented on DERBY-4874:
-------------------------------------------
Has anyone looked more into the failures on 10.3 and 10.4 after the
back-porting of this issue? Is the current hypothesis that the lock timeouts
happen because the post-commit work in the background thread takes a long time
while holding locks on the table that is about to be dropped?
The three second lock timeout in this test may come from ResultSetMiscTest and
SetTransactionIsolationTest, which reduce the lock timeout to three seconds.
Before DERBY-4273 they didn't restore the original lock timeout on completion,
but now they do. DERBY-4273 is fixed on the 10.5 branch, which may explain why
we only see this issue on 10.4 and 10.3. It doesn't explain why we haven't seen
this issue before, though.
> Trigger does not recognize new size of VARCHAR column expanded with ALTER
> TABLE. It fails with ERROR 22001: A truncation error was encountered trying
> to shrink VARCHAR
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-4874
> URL: https://issues.apache.org/jira/browse/DERBY-4874
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.2.1, 10.3.3.1, 10.4.2.1, 10.5.3.0, 10.6.2.1,
> 10.7.1.1
> Reporter: Kathey Marsden
> Assignee: Mamta A. Satoor
> Fix For: 10.3.3.1, 10.4.2.1, 10.5.3.2, 10.6.2.3, 10.7.1.3,
> 10.8.0.0
>
> Attachments: DERBY4874_106_backport_diff_ver4.txt,
> DERBY4874_106_backport_stat_ver4.txt,
> DERBY4874_comments_and_test_missing_diff_ver3.txt,
> DERBY4874_comments_and_test_missing_stat_ver3.txt,
> DERBY4874_not_ready_for_commit_diff.txt,
> DERBY4874_not_ready_for_commit_diff_ver2.txt,
> DERBY4874_not_ready_for_commit_stat.txt,
> DERBY4874_not_ready_for_commit_stat_ver2.txt, derby.log, droptable.diff,
> releaseNote.html, trigAlterColumn.sql
>
>
> Trigger created before VARCHAR column is expanded with ALTER TABLE does not
> recognize new size and fails with:
> ERROR 22001: A truncation error was encountered trying to shrink VARCHAR
> '012345
> 678901234567890123456789001234567890' to length 30.
> CREATE TABLE tab (
> element_id INTEGER NOT NULL,
> altered_id VARCHAR(30) NOT NULL,
> counter SMALLINT NOT NULL DEFAULT 0,
> timets TIMESTAMP NOT NULL
> );
> 0 rows inserted/updated/deleted
> ij> -- Create a trigger against the table
> CREATE TRIGGER mytrig
> AFTER UPDATE ON tab
> REFERENCING NEW AS newt OLD AS oldt
> FOR EACH ROW MODE DB2SQL
> UPDATE tab set tab.counter = CASE WHEN (oldt.counter < 32767) THEN
> (oldt.count
> er + 1) ELSE 1 END
> WHERE ((newt.counter is null) or (oldt.counter = newt.counter))
> AND newt.element_id = tab.element_id
> AND newt.altered_id = tab.altered_id;
> 0 rows inserted/updated/deleted
> ij> -- Alter the table to increase column
> ALTER TABLE tab ALTER altered_id SET DATA TYPE VARCHAR(64);
> 0 rows inserted/updated/deleted
> ij> -- insert the data
> insert into tab values (99,
> '012345678901234567890123456789001234567890',1,CURRE
> NT_TIMESTAMP);
> 1 row inserted/updated/deleted
> ij> -- update and reproduce the issue
> update tab set timets = CURRENT_TIMESTAMP where ELEMENT_ID = 99;
> ERROR 22001: A truncation error was encountered trying to shrink VARCHAR
> '012345
> 678901234567890123456789001234567890' to length 30.
> java.sql.SQLDataException: A truncation error was encountered trying to
> shrink V
> ARCHAR '012345678901234567890123456789001234567890' to length 30.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLE
> xceptionFactory40.java:79)
> at
> org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:256)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException
> (TransactionResourceImpl.java:391)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Tr
> ansactionResourceImpl.java:346)
> at
> org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConne
> ction.java:2269)
> at
> org.apache.derby.impl.jdbc.ConnectionChild.handleException(Connection
> Child.java:81)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedState
> ment.java:1321)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java
> :625)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java
> :555)
> at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:367)
> at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:521)
> at
> org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:3
> 63)
> at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:261)
> at org.apache.derby.impl.tools.ij.Main.go(Main.java:229)
> at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184)
> at org.apache.derby.impl.tools.ij.Main.main(Main.java:75)
> at org.apache.derby.tools.ij.main(ij.java:59)
> Caused by: java.sql.SQLException: A truncation error was encountered trying
> to s
> hrink VARCHAR '012345678901234567890123456789001234567890' to length 30.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExc
> eptionFactory.java:45)
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransport
> AcrossDRDA(SQLExceptionFactory40.java:119)
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLE
> xceptionFactory40.java:70)
> ... 16 more
> Caused by: ERROR 22001: A truncation error was encountered trying to shrink
> VARC
> HAR '012345678901234567890123456789001234567890' to length 30.
> at
> org.apache.derby.iapi.error.StandardException.newException(StandardEx
> ception.java:343)
> at
> org.apache.derby.iapi.types.SQLChar.hasNonBlankChars(SQLChar.java:176
> 6)
> at org.apache.derby.iapi.types.SQLChar.setWidth(SQLChar.java:1840)
> at
> org.apache.derby.exe.ac0b5b0099x012bxf542xab11x0000001bd2983.e2(Unkno
> wn Source)
> at
> org.apache.derby.impl.services.reflect.DirectCall.invoke(ReflectGener
> atedClass.java:143)
> at
> org.apache.derby.impl.sql.execute.GenericQualifier.getOrderable(Gener
> icQualifier.java:96)
> at
> org.apache.derby.impl.sql.execute.NoPutResultSetImpl.clearOrderableCa
> che(NoPutResultSetImpl.java:313)
> at
> org.apache.derby.impl.sql.execute.TableScanResultSet.openScanControll
> er(TableScanResultSet.java:350)
> at
> org.apache.derby.impl.sql.execute.TableScanResultSet.openCore(TableSc
> anResultSet.java:262)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(P
> rojectRestrictResultSet.java:174)
> at
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(P
> rojectRestrictResultSet.java:174)
> at
> org.apache.derby.impl.sql.execute.NormalizeResultSet.openCore(Normali
> zeResultSet.java:146)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.setup(UpdateResultS
> et.java:344)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSe
> t.java:263)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Generi
> cPreparedStatement.java:436)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.executeSubStatemen
> t(GenericPreparedStatement.java:306)
> at
> org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(G
> enericTriggerExecutor.java:173)
> at
> org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(RowT
> riggerExecutor.java:111)
> at
> org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(T
> riggerEventActivator.java:278)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.fireAfterTriggers(U
> pdateResultSet.java:817)
> at
> org.apache.derby.impl.sql.execute.UpdateResultSet.open(UpdateResultSe
> t.java:280)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Generi
> cPreparedStatement.java:436)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPre
> paredStatement.java:317)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedState
> ment.java:1232)
> ... 10 more
> ij>
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
