Oracle AutoDDL hardcodes foreign keys unsafely
GENERAL
TESTING
GENERAL
TESTING
Description
Attachments
2
- 11 Feb 2011, 08:31 AM
- 11 Feb 2011, 08:31 AM
Activity
Show:
Hui Tsao February 14, 2011 at 9:00 AM
Close this per David's comment.
David Adams February 12, 2011 at 4:20 AM
Karen, current trunk against an empty schema starts successfully and the samigo autoddl code runs fine. Thanks!
Hui Tsao February 11, 2011 at 4:28 PM
Hi David,
I have committed the change in trunk (r88384). Can you please verify?
Thanks,
Karen
David Adams February 11, 2011 at 10:24 AM
I don't believe any conversion script would be required. This issue only affects the initial deployment of Sakai. Existing installations would not be affected.
Anthony Whyte February 11, 2011 at 8:44 AM
Remember to check the "conversion script required" checkbox so that we can pick up this ticket when we review the need for conversion script changes.
Fixed
Details
Details
Priority
BlockerComponents
Assignee
SAMIGO TEAM
SAMIGO TEAMReporter
David Adams
David AdamsConversion Script Required
Yes
Created February 11, 2011 at 8:28 AM
Updated April 17, 2018 at 8:32 AM
Resolved February 14, 2011 at 9:00 AM
The AutoDDL script for the Samigo tables uses a sequence to generate the ID value for the rows inserted into the SAM_ASSESSMENTBASE_T table. When inserting records in other tables that link back to this record via foreign key, however, the script unsafely specifies a value of "1" rather than looking up the actual value was generated by the sequence.
In our environment at least, as of our deployment of Oracle 11gR2, the first record inserted into the SAM_ASSESSMENTBASE_T table receives an ID value of "2", which means that subsequent references to "1" refer to no existing records, causing the rest of the AutoDDL script to fail. Other inserts referring to other SAM_ASSESSMENTBASE_T records correctly look up the ID value as part of the insert, instead of hardcoding it. This practice should also be followed on records tying back to this first base record.
The file in question is found in all versions at samigo-pack/src/sql/oracle/sakai_samigo.sql. I'll be attaching patches which address the problem for the 2.6.1 and 2.7.1 tags where I've verified the fixes. The problem still exists in the 2.8.0-b04 tag. The corresponding files for other database platforms all seem to hardcode the "1" value on the original insert, so avoiding this problem.