You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Mamta A. Satoor (JIRA)" <ji...@apache.org> on 2011/01/07 18:39:45 UTC
[jira] Issue Comment Edited: (DERBY-4874) 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
[ https://issues.apache.org/jira/browse/DERBY-4874?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12978847#action_12978847 ]
Mamta A. Satoor edited comment on DERBY-4874 at 1/7/11 12:39 PM:
-----------------------------------------------------------------
I didn't see these failures when I ran the junit suite on my laptop for both 10.4 and 10.3 before doing the checkin. I had used IBM's JVM1.6 rather than Sun's JVM1.6 which is what Tinderbox is using. The Tinderbox shows the errors in the basicSetup() method, part of which looks as follows. The method first drops the objects and then recreates them. If during the drop there is an exception, we just ignore it. The error encountered on Tinderbox is on the attempt to recreate table table1. This means that there may have been a failure during the drop but since we don't record the exception, I am not sure what the error may have been for the drop table1. The exception probably is logged into the derby,log. Can someone please tell me how to look at derby.log for a failed test on Tinderbox? Also, another thing to note in that the TriggerTest is run twice in junit suite. The first run of the test is fine, it is the second run of the test that runs into failure on Tinderbox.
public void basicSetup() throws SQLException{
Statement s = createStatement();
try {
s.execute("drop table table1");
} catch (SQLException sqle) {}
try {
s.execute("drop table table2");
} catch (SQLException sqle) {}
try {
s.execute("drop table table3");
} catch (SQLException sqle) {}
try {
s.execute("drop trigger trigger1");
} catch (SQLException sqle) {}
try {
s.execute("drop trigger trigger2");
} catch (SQLException sqle) {}
//table1 is the main table on which all the testing is done and it
//uses table2 at times to do DMLs as part of it's trigger action.
s.execute("create table table1 (id int, status smallint, bl blob(2G))");
was (Author: mamtas):
I didn't see these failures when I ran the junit suite on my laptop for both 10.4 and 10.3 before doing the checkin. The Tinderbox shows the errors in the basicSetup() method, part of which looks as follows. The method first drops the objects and then recreates them. If during the drop there is an exception, we just ignore it. The error encountered on Tinderbox is on the attempt to recreate table table1. This means that there may have been a failure during the drop but since we don't record the exception, I am not sure what the error may have been for the drop table1. The exception probably is logged into the derby,log. Can someone please tell me how to look at derby.log for a failed test on Tinderbox? Also, another thing to note in that the TriggerTest is run twice in junit suite. The first run of the test is fine, it is the second run of the test that runs into failure on Tinderbox.
public void basicSetup() throws SQLException{
Statement s = createStatement();
try {
s.execute("drop table table1");
} catch (SQLException sqle) {}
try {
s.execute("drop table table2");
} catch (SQLException sqle) {}
try {
s.execute("drop table table3");
} catch (SQLException sqle) {}
try {
s.execute("drop trigger trigger1");
} catch (SQLException sqle) {}
try {
s.execute("drop trigger trigger2");
} catch (SQLException sqle) {}
//table1 is the main table on which all the testing is done and it
//uses table2 at times to do DMLs as part of it's trigger action.
s.execute("create table table1 (id int, status smallint, bl blob(2G))");
> 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
> 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.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, 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.
-
You can reply to this email to add a comment to the issue online.