|
Simpler workaround provided by Daniel John Debrunner <djd@debrunners.com> in <42AB428F.1000002@debrunners.com>:
Or simpler, just create the schema using CREATE SCHEMA. CREATE SCHEMA user SET SCHEMA user CREATE TRIGGER... Full example provided by Mamta Satoor <msatoor@gmail.com> in <d9619e4a05061122157335250d@mail.gmail.com>:
Actually, in addition to Dan's suggestion of CREATE SCHMEA, you have to next do SET SCHEMA, and then you don't need to create a dummy table in the schema. To be specific, following eg worked in ij ij> connect 'jdbc:derby:c:/dellater/db1;create=true;user=mamta;password=mamta'; ij> create table itko.t1 (i int); 0 rows inserted/updated/deleted ij> create schema mamta; 0 rows inserted/updated/deleted ij> set schema mamta; 0 rows inserted/updated/deleted ij> create trigger itko.trig1 after update on itko.t1 for each row mode db2sql select * from sys.systables; 0 rows inserted/updated/deleted ij> exit; The javadoc for the CreateTriggerConstantAction constructor seems to confirm that the default schema is used if no other compile schema is provided:
spsCompSchemaId - the compilation schema for the action and when spses. If null, will be set to the current default schema The code confirms this: return getGenericConstantActionFactory().getCreateTriggerConstantAction( triggerSchemaDescriptor.getSchemaName(), getRelativeName(), triggerEventMask, isBefore, isRow, isEnabled, triggerTableDescriptor, (UUID)null, // when SPSID whenText, (UUID)null, // action SPSid actionText, (actionCompSchemaId == null) ? compSchemaDescriptor.getUUID() : actionCompSchemaId, (Timestamp)null, // creation time referencedColInts, I tried to add: if (SanityManager.DEBUG) { SanityManager.ASSERT((actionCompSchemaId != null || compSchemaDescriptor.getUUID()!= null), "No valid compile schema"); } return getGenericConstantActionFactory().getCreateTriggerConstantAction(... and this shows that both are null in this case: dt136804@atum10~/java$ run-app.sh Try Try starting in embedded mode. TRACE: Class.forName(driver).newInstance(); @Try.java.m4:89 TRACE: conn = DriverManager.getConnection(protocol + db + ";create=true", props); @Try.java.m4:105 TRACE: conn.setAutoCommit(false); @Try.java.m4:107 TRACE: Statement s = conn.createStatement(); @Try.java.m4:113 s=org.apache.derby.impl.jdbc.EmbedStatement@126e85f @Try.java.m4:114 conn=0 @Try.java.m4:115 TRACE: s.execute("create table itko.t1 (i int)"); @Try.java.m4:121 TRACE: s.execute("create schema USER1"); @Try.java.m4:122 TRACE: s.execute("create trigger ikto.trig1 after update on itko.t1 for each row mode db2sql select * from sys.systables"); @Try.java.m4:128 exception thrown: SQL Exception: Java exception: 'ASSERT FAILED No valid compile schema: org.apache.derby.iapi.services.sanity.AssertFailure'. SQLState: XJ001 MessageId: XJ001.U org.apache.derby.iapi.services.sanity.AssertFailure: ASSERT FAILED No valid compile schema at org.apache.derby.iapi.services.sanity.SanityManager.ASSERT(SanityManager.java:121) at org.apache.derby.impl.sql.compile.CreateTriggerNode.makeConstantAction(CreateTriggerNode.java:728) at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:459) at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:107) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:688) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:501) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:475) at Try.go(Try.java:128) at Try.main(Try.java:73) null Try finished The patch appears to fix the problem and passes derbyall (see attached derbyall_report.txt), but I'm a bit unsure if this is the best way to fix the problem.
It would be really nice if a committer could look at it, and perhaps give some feedback. Some comments to the patch: - The compile schema is needed in the CREATE TRIGGER statement in order to fill in column 8 of the SYSSTATEMENTS table - The CREATE TRIGGER code uses LanguageConnectionContext.getDefaultSchema() as a fallback when no other compile schema is specified. The object returned by this method has a null oid unless one has done SET SCHEMA. But a SchemaDescriptor containing a valid oid can be obtained by calling getSchemaDescriptorForCreate(DataDictionary, Activation, String). The patch uses this method to get the oid as the ultimate fallback. Looking at the code again, I wondering if it would be better to use DataDictionary.getSchemaDescriptor(...) rather than DDLConstantAction.getSchemaDescriptorForCreate(...) since we just want to look at the Descriptor object...
The patch itself looks good. I'm running derbyall now. The patch, however, needs to add a regression test case to one of the language tests to verify that the bug is fixed.
Derbyall passed. When a new patch is submitted including a regression test, I'll just run the appropriate suite.
What is the status of this fix? From the comments it looked like it was pretty close to complete.
Thanks Kathey Status? If you look at the dates you'll see that I have not looked at this for about 5 months, so I don't really know. I'm actually surprised that Rick was able to apply the patch without problems.
If am supposed to create a regression test for this; is there any chance that I can do that as JUnit test? And, can someone please comment on my question from 21 June? About which method to use to obtain a schema descriptor? I don't think I understand the June 21 question about which schema descriptor method to call. DDLConstantAction.getSchemaDescriptorForCreate() will persistently create the schema if it doesn't already exist. I think you need to do that before you create any objects in the schema. I don't think that the DataDictionary methods, by themselves, will create any persistent objects. So the method you are calling looks like the right one to me.
This is my attempt at creating a JUnit test for
test harness (I could not find any info on how to do that). I have run the test using junit.swingui.TestRunner and that seems to work. If someone would like to review it that would be great. New patch with Junit test case.
Note that I had to modify the SecurityManager policy to grant more privileges to derbyTesting.jar and junit.jar Anyone interested SecurityManager issues may want to review those changes carefully. Also note that the current patch does NOT use DDLConstantAction.getSchemaDescriptorForCreate() , but accesses the dictionary directly. This works fine because the trigger is NOT created in this schema. You ONLY need the oid of the default schema to fill in a column in the SYSSTATEMENTS table, (described in more detail in earlier comments). lang/closed.java failed when I ran derbyall, but I don't think this is related to the patch (see New patch based on the latest trunk. The patch is as before, but the test case is now a simple ij-test, that does not cause any SecurityManager problems. Hopefully this issue can be committed soon, (if I scratch this itch any more I'm going to get gangrene).
Looks like this patch keeps dropping off the radar... Dyre, why exactly are you adding a new test script with one simple test? Could it be possible to add this test to existing trigger tests? It is possible to get a connection to 'someUser' in the middle of an existing test.
Hi Sateesh, thank you so much for taking the time to look at the patch! Is it really possible to get a different connection inside an ij-script? Wow, I did not know that, how do you do that? Do you have an example?
Which test script should this go into? I find the following trigger-related test scripts in the lang directory: triggerBeforeTrig.sql triggerRefClause.sql triggerGeneral.sql triggerRefClause_app.properties triggerGeneral_app.properties triggerStream.java triggerGeneral_derby.properties triggerStream_app.properties triggerRecursion.sql triggerStream_derby.properties triggerRecursion_derby.properties My money would be on trggerGeneral.sql, but since the "common sense" approach keeps letting me down, I thought I had better ask :) New patch addressing Sateesh concerns about the regression test being a separate test script. Please review. Thanks.
Thanks for addressing review comments. I have submitted this fix. Please resolve the fix (with fix version of 10.2) and then close after verifying the fix.
Sending java\engine\org\apache\derby\impl\sql\execute\CreateTriggerConstantAction.java Sending java\testing\org\apache\derbyTesting\functionTests\master\triggerGeneral.out Sending java\testing\org\apache\derbyTesting\functionTests\tests\lang\triggerGeneral.sql Transmitting file data ... Committed revision 378109. Committed by Sateesh Bandaram
Sending java\engine\org\apache\derby\impl\sql\execute\CreateTriggerConstantAction.java Sending java\testing\org\apache\derbyTesting\functionTests\master\triggerGeneral.out Sending java\testing\org\apache\derbyTesting\functionTests\tests\lang\triggerGeneral.sql Transmitting file data ... Committed revision 378109. I think it would be great to port this fix to 10.1 branch. I have seen some users hit this problem. Dyre, do you happen to have itch to port this to 10.1?
Setting fixin for merge to 10.1.3
svn merge -r 378108:378109 trunk 10.1@397791M was clean
Ran derbyall after the merge with 3 failures, which I don't think are related (see attached report file for details). Committed to 10.1 branch with revision 398036.
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
Bug
Closed
Major
NPE when creating a trigger on a table and default schema doesn't exist.
inserted in a table in the default schema. This oid is referenced when
creating a trigger, even if that trigger doesn't touch the default schema.
One can work around it by having a non-empty table in the default
schema:
s.execute("create table itko.t1 (i int)");
// Workaround
s.execute("create table def (i int)");
s.execute("insert into def values (0), (1), (2)");
s.execute("create trigger ikto.trig1 after update on itko.t1 for each row mode db2sql select * from sys.systables");
I THINK that when creating a trigger, the code needs a "compile schema" and it
assumes that it is safe to use the default schema, which it isn't in
this case.
So I guess you could attack this problem in (at least) two ways:
1) Make the trigger code use a schema that is known to be ok
2) Ensure that the default schema always is valid.