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] Issue Comment Edited: (DERBY-5121) Data corruption when executing an UPDATE trigger
Date Wed, 16 Mar 2011 05:55:29 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5121?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13007356#comment-13007356
] 

Mamta A. Satoor edited comment on DERBY-5121 at 3/16/11 5:54 AM:
-----------------------------------------------------------------

Th attached patch(derby5121_patch1_diff.txt) will disable the selective column reading for
row level triggers which was introduced by DERBY-1482. As a result of this, Derby will be
required to read all the columns from the trigger table. The generated trigger action sql's
columns will refer to the columns using theie actual column positions in the trigger table.
I have disabled the selective colunm reading by simply assuming in the create trigger code
that we are dealing with pre-10.7 database(as if we are in soft upgrade mode from pre-10.7).
During the soft-upgrade mode with 10.7 and higher releases, we do not do column reading optimization
because the system tables in pre-10.7 are not equipped to keep additional information about
trigger action columns. 

This code change was done in DataDictionaryImpl.getTriggerActionString and looks as follows
		boolean in10_7_orHigherVersion = false;
In addition to the above change, I also had to catch the column missing error in this same
method. This can happen if ALTER TABLE DROP COLUMN is dropping a column from the trigger table
and that column is getting referenced in the trigger action sql. This scenario currently in
the 10.7 and 10.8 codelines get caught when we find that the column being dropped is getting
used in trigger action's referenced column list and if so, then we go ahead and drop that
trigger if we are doing alter table drop column cascade or we throw an error for trigger dependency
if the alter table drop column restrict is being performed. But since with this patch, we
do not keep the trigger action's referenced column list anymore, we can't catch the drop column's
dependency in the trigger action's referenced column list. Because of this, I have to see
if the trigger action column is not found, then I should throw a column not found exception.
The catch of that exception will drop the trigger is we are dealing with alter table drop
column cascade or it will throw a trigger dependency exception if we are dealing with alter
table drop column restrict.

In addition to the above 2 changes, I had to make following change in TriggerDescriptor.getActionSPS.
The if condition used to be
		if((!actionSPS.isValid() ||
				 (actionSPS.getPreparedStatement() == null)) && 
				 isRow && 
				 referencedColsInTriggerAction != null) 
But now with this patch, we don't maintain the information in list referencedColsInTriggerAction
and because of that, the above if would always be false. We want to catch triggers that are
using REFERENCING clause and because of this, the new if condition will look as follows
		if((!actionSPS.isValid() ||
				 (actionSPS.getPreparedStatement() == null)) && 
				 isRow && (referencingOld || referencingNew))

In addition to the above three changes, I have added new test to incorporate Rick's reproducible
case.

I have not changed the comments in the code yet because I hope to work on the fix that I proposed
earlier in the jira. Once I have that fix in, I can go ahead and change the comments to match
that fix.

derbyall and junit suite ran fine with my changes. If no one has any comments, I can go ahead
and checkin this fix Wednesday evening.


      was (Author: mamtas):
    This patch will disable the selective column reading for row level triggers which was
introduced by DERBY-1482. As a result of this, Derby will be required to read all the columns
from the trigger table. The generated trigger action sql's columns will refer to the columns
using theie actual column positions in the trigger table. I have disabled the selective colunm
reading by simply assuming in the create trigger code that we are dealing with pre-10.7 database(as
if we are in soft upgrade mode from pre-10.7). During the soft-upgrade mode with 10.7 and
higher releases, we do not do column reading optimization because the system tables in pre-10.7
are not equipped to keep additional information about trigger action columns. 

This code change was done in DataDictionaryImpl.getTriggerActionString and looks as follows
		boolean in10_7_orHigherVersion = false;
In addition to the above change, I also had to catch the column missing error in this same
method. This can happen if ALTER TABLE DROP COLUMN is dropping a column from the trigger table
and that column is getting referenced in the trigger action sql. This scenario currently in
the 10.7 and 10.8 codelines get caught when we find that the column being dropped is getting
used in trigger action's referenced column list and if so, then we go ahead and drop that
trigger if we are doing alter table drop column cascade or we throw an error for trigger dependency
if the alter table drop column restrict is being performed. But since with this patch, we
do not keep the trigger action's referenced column list anymore, we can't catch the drop column's
dependency in the trigger action's referenced column list. Because of this, I have to see
if the trigger action column is not found, then I should throw a column not found exception.
The catch of that exception will drop the trigger is we are dealing with alter table drop
column cascade or it will throw a trigger dependency exception if we are dealing with alter
table drop column restrict.

In addition to the above 2 changes, I had to make following change in TriggerDescriptor.getActionSPS.
The if condition used to be
		if((!actionSPS.isValid() ||
				 (actionSPS.getPreparedStatement() == null)) && 
				 isRow && 
				 referencedColsInTriggerAction != null) 
But now with this patch, we don't maintain the information in list referencedColsInTriggerAction
and because of that, the above if would always be false. We want to catch triggers that are
using REFERENCING clause and because of this, the new if condition will look as follows
		if((!actionSPS.isValid() ||
				 (actionSPS.getPreparedStatement() == null)) && 
				 isRow && (referencingOld || referencingNew))

In addition to the above three changes, I have added new test to incorporate Rick's reproducible
case.

I have not changed the comments in the code yet because I hope to work on the fix that I proposed
earlier in the jira. Once I have that fix in, I can go ahead and change the comments to match
that fix.

derbyall and junit suite ran fine with my changes. If no one has any comments, I can go ahead
and checkin this fix Wednesday evening.

  
> Data corruption when executing an UPDATE trigger
> ------------------------------------------------
>
>                 Key: DERBY-5121
>                 URL: https://issues.apache.org/jira/browse/DERBY-5121
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.7.1.1, 10.8.0.0
>            Reporter: Rick Hillegas
>            Assignee: Mamta A. Satoor
>              Labels: derby_triage10_8
>         Attachments: DummyProc.java, derby5121_patch1_diff.txt, triggerBug.sql, triggerBug.sql,
triggeredBug2.sql, triggeredCorruption.sql
>
>
> When executing an UPDATE trigger, the following error is raised. I will attach a test
case:
> ERROR XCL12: An attempt was made to put a data value of type 'org.apache.derby.impl.jdbc.EmbedClob'
into a data value of type 'INTEGER'.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message