Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 18631 invoked from network); 6 Jan 2011 14:19:10 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 6 Jan 2011 14:19:10 -0000 Received: (qmail 38368 invoked by uid 500); 6 Jan 2011 14:19:10 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 38248 invoked by uid 500); 6 Jan 2011 14:19:10 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 38235 invoked by uid 99); 6 Jan 2011 14:19:09 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 06 Jan 2011 14:19:09 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.22] (HELO thor.apache.org) (140.211.11.22) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 06 Jan 2011 14:19:08 +0000 Received: from thor (localhost [127.0.0.1]) by thor.apache.org (8.13.8+Sun/8.13.8) with ESMTP id p06EImrJ022191 for ; Thu, 6 Jan 2011 14:18:48 GMT Message-ID: <7747387.188741294323528236.JavaMail.jira@thor> Date: Thu, 6 Jan 2011 09:18:48 -0500 (EST) From: "Mamta A. Satoor (JIRA)" To: derby-dev@db.apache.org Subject: [jira] Commented: (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 In-Reply-To: <23021464.129771288310122354.JavaMail.jira@thor> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/DERBY-4874?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12978331#action_12978331 ] Mamta A. Satoor commented on DERBY-4874: ---------------------------------------- Kristian, I was wondering if you or anyone else had any feedback on Kathey's comment about synchronization (her comment copied below) I thought I might look at the sychronization issue while Mamta is out, but am a little confused about what activity needs to be synchronized and wonder if just synchronizing getText and setText is enough or helpful as that would not prevent concurrent callers to the new TriggerDescriptor.getActionSPS() or prevent the text being changed during compilation, etc. Any helpful pointers and usage cases we are trying to protect are welcome. > 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.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.