db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] Updated: (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
Date Tue, 16 Nov 2010 05:39:15 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4874?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Mamta A. Satoor updated DERBY-4874:
-----------------------------------

    Attachment: DERBY4874_not_ready_for_commit_stat_ver2.txt
                DERBY4874_not_ready_for_commit_diff_ver2.txt

Knut, thanks a bunch for taking the time to review the patch. In the attached patch, I have
taken care of some of your comments. I am continuing to narrow down lang/predicatesIntoViews.sql
to find out why wrong trigger action sql is getting regenerated as I mentioned in my comment
on Nov 11th. The repro is pretty short compared to original lang/predicatesIntoViews.sql.
I hope to narrow it down completely soon to be able to debug it. 

The attached patch still has duplication of code. After I debug the lang/predicatesIntoViews.sql
problem, I will next work on putting as much common code as possible in DataDictionary rather
than having it in two places which is CreateTriggerNode and TriggerDescriptor. I will also
add more comments to the code where applicable and add test cases for this jira.

I took care of following feeback from you.
1) I saw your comment about sharing code with CreateTriggerNode instead of duplicating it...
Yes, please! :) 
*** I will work on this after taking care of lang/predicatesIntoViews.sql problem.

2) The changes in BaseMonitor don't seem to be related to this issue. 
*** Thanks for catching it. I have removed that change from my codeline.

3) The new method sortRefs() looks unnecessarily complex. In particular: 
  a) The code that copies the Vector to an array, could be replaced with a one-liner that
just called toArray(). 
*** I tried using toArray() but got class cast exception. Didn't spend enough time on this
yet but I have the code I tried commented out. Do you see something wrong with it?

  b) Using java.util.Collections.sort() sounds cleaner and less error-prone than reimplementing
bubble sort. 
*** This code has been in CreateTriggerNode but sometime and I am not sure why it was implemented
this way. I will try to take a look at this after tackling the items mentioned above.

4) I find the added code in getActionSPS() a bit hard to follow. It may help if some short
comments were added explaining the purpose of the various if statements and for loops. (For
example, the part of the JIRA comment from Nov 10 that says "we need to regenerate the trigger
action sql if 1) ... 2) ... 3) ..." would be a helpful comment for the outer if statement.)

*** I have adding comments in my todo list to make the code more readable but I did copy the
reasons for regeneration into the code.

5) In getActionSPS(), the code that sets all elements of triggerColsAndTriggerActionCols to
-1 could be simplified by using 
java.util.Arrays.fill(). 
*** Thanks for this tip. I use now java.util.Arrays.fill() to do the array filling.

6) In getActionSPS(), (referencedColsInTriggerAction != null) is checked both in the outer
and the inner if statement, so one of the checks is redundant and could be removed. 
*** Thanks for noticing this, I have taken care of this.

7) The new variables in getActionSPS() are only used if the trigger needs to be recompiled.
It would be good to move the declarations inside the if statement so that we don't need to
create a StringBuilder and check the version of the data dictionary in the common case. 
*** I took care of this.

8) This code in getActionSPS() looks a bit suspicious: 
+ DataDictionary dd = getDataDictionary(); 
+ 
+ if (((org.apache.derby.impl.sql.catalog.DataDictionaryImpl) dd).readOnlyUpgrade) 
+ return actionSPS; 
Even if readOnlyUpgrade is false, it will still return actionSPS on the next line, so this
code appears to have no purpose. 
*** You are right about this. I was experimenting updating SYSSTATEMENTS here but found that
it is already being done as part of trigger invalidation. I have removed the code that you
pointed out.


> 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.0
>            Reporter: Kathey Marsden
>            Assignee: Mamta A. Satoor
>         Attachments: 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.


Mime
View raw message