You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ma...@apache.org on 2011/03/16 18:51:11 UTC
svn commit: r1082235 - in /db/derby/code/branches/10.7/java:
engine/org/apache/derby/iapi/sql/dictionary/
engine/org/apache/derby/impl/sql/catalog/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: mamta
Date: Wed Mar 16 17:51:10 2011
New Revision: 1082235
URL: http://svn.apache.org/viewvc?rev=1082235&view=rev
Log:
DERBY-5121 Data corruption when executing an UPDATE trigger
This commit 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 commit, 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 a
re 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 commit, 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.
Modified:
db/derby/code/branches/10.7/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
Modified: db/derby/code/branches/10.7/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java?rev=1082235&r1=1082234&r2=1082235&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java (original)
+++ db/derby/code/branches/10.7/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java Wed Mar 16 17:51:10 2011
@@ -352,8 +352,7 @@ public class TriggerDescriptor extends T
//will then get updated into SYSSTATEMENTS table.
if((!actionSPS.isValid() ||
(actionSPS.getPreparedStatement() == null)) &&
- isRow &&
- referencedColsInTriggerAction != null)
+ isRow && (referencingOld || referencingNew))
{
SchemaDescriptor compSchema;
compSchema = getDataDictionary().getSchemaDescriptor(triggerSchemaId, null);
Modified: db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=1082235&r1=1082234&r2=1082235&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original)
+++ db/derby/code/branches/10.7/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Wed Mar 16 17:51:10 2011
@@ -4652,8 +4652,7 @@ public final class DataDictionaryImpl
boolean createTriggerTime
) throws StandardException
{
- boolean in10_7_orHigherVersion =
- checkVersion(DataDictionary.DD_VERSION_DERBY_10_7,null);
+ boolean in10_7_orHigherVersion = false;
StringBuffer newText = new StringBuffer();
int start = 0;
@@ -4937,6 +4936,14 @@ public final class DataDictionaryImpl
newText.append(triggerDefinition.substring(start, tokBeginOffset-actionOffset));
int colPositionInRuntimeResultSet = -1;
ColumnDescriptor triggerColDesc = triggerTableDescriptor.getColumnDescriptor(colName);
+ //DERBY-5121 We can come here if the column being used in trigger
+ // action is getting dropped and we have come here through that
+ // ALTER TABLE DROP COLUMN. In that case, we will not find the
+ // column in the trigger table.
+ if (triggerColDesc == null) {
+ throw StandardException.newException(
+ SQLState.LANG_COLUMN_NOT_FOUND, tableName+"."+colName);
+ }
int colPositionInTriggerTable = triggerColDesc.getPosition();
//This part of code is little tricky and following will help
Modified: db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java?rev=1082235&r1=1082234&r2=1082235&view=diff
==============================================================================
--- db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java (original)
+++ db/derby/code/branches/10.7/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java Wed Mar 16 17:51:10 2011
@@ -576,6 +576,34 @@ public class TriggerTest extends BaseJDB
JDBC.assertFullResultSet(rs, result);
}
+ public void testDERBY5121() throws SQLException
+ {
+ Statement s = createStatement();
+
+ s.executeUpdate("CREATE TABLE T1 (A1 int)");
+ s.executeUpdate("CREATE TABLE T2 (B1 int, B2 int, B3 int)");
+ s.executeUpdate("CREATE TRIGGER t2UpdateTrigger "+
+ "after UPDATE of b1 on t2 " +
+ "referencing new row as nr for each ROW " +
+ "insert into t1 values ( nr.b3 ) ");
+ s.executeUpdate("INSERT INTO T2 VALUES(0,0,0)");
+ s.executeUpdate("update t2 set b1 = 100 , b2 = 1");
+ ResultSet rs =s.executeQuery("SELECT * FROM T1");
+ JDBC.assertFullResultSet(rs, new String[][] {{"0"}});
+
+ s.executeUpdate("CREATE TABLE T3 (A1 int)");
+ s.executeUpdate("CREATE TABLE T4 (B1 int, B2 int, B3 int)");
+ s.executeUpdate("CREATE TRIGGER t4UpdateTrigger "+
+ "after UPDATE of b1 on t4 " +
+ "referencing new table as nt for each STATEMENT " +
+ "insert into t3 select b3 from nt");
+ s.executeUpdate("INSERT INTO T4 VALUES(0,0,0)");
+ s.executeUpdate("update t4 set b1 = 100 , b2 = 1");
+ rs =s.executeQuery("SELECT * FROM T3");
+ JDBC.assertFullResultSet(rs, new String[][] {{"0"}});
+
+ }
+
/**
* Test for DERBY-3238 trigger fails with IOException if triggering table has large lob.
*