Derby
  1. Derby
  2. DERBY-3523

sql states (X0Y63, X0Y63, X0Y63.S) related to nulls in unique constraints are associated with wrong message texts

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.4.1.3, 10.5.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Low

      Description

      There are three messages which after Derby-3330 checkin now giving wrong information. These are

      42831:'

      {0}' cannot be a column of a primary key or unique key because it can contain null values.
      42Z20:Column '{0}

      ' cannot be made nullable. It is part of a primary key or unique constraint, which cannot have any null able columns.
      X0Y63.S:The command on table '

      {0}

      ' failed because null data was found in the primary key or unique constraint/index column(s). All columns in a primary or unique index key must not be null.

      1. derby-3523-upgradetest_v2.diff
        4 kB
        Anurag Shekhar
      2. derby-3523-upgradetest.diff
        4 kB
        Anurag Shekhar
      3. derby-3523v3.diff
        14 kB
        Anurag Shekhar
      4. derby-3523v2.diff
        16 kB
        Anurag Shekhar
      5. derby-3523v1.diff
        16 kB
        Anurag Shekhar

        Issue Links

          Activity

          Hide
          Dag H. Wanvik added a comment -

          Unassigning this, as there has been no activity on it for quite some time, so others can pick it up.

          Show
          Dag H. Wanvik added a comment - Unassigning this, as there has been no activity on it for quite some time, so others can pick it up.
          Hide
          Kathey Marsden added a comment -

          No I don't think this is ready to be resolved. It looks like I committed a patch and then backed it out and then there was another one posted that hasn't been committed. Maybe it should be marked patch available and we can see if the patch still applies.

          Show
          Kathey Marsden added a comment - No I don't think this is ready to be resolved. It looks like I committed a patch and then backed it out and then there was another one posted that hasn't been committed. Maybe it should be marked patch available and we can see if the patch still applies.
          Hide
          Tiago R. Espinha added a comment -

          Triaged for 10.5.2.
          It seems like this issue is fixed.

          Kathey, can you resolve if it is?

          Show
          Tiago R. Espinha added a comment - Triaged for 10.5.2. It seems like this issue is fixed. Kathey, can you resolve if it is?
          Hide
          Anurag Shekhar added a comment -

          In Derby 10.0 the syntax for creating primary key along with table creation was

          create table t1 (i integer not null primary key)

          in latter version not null wasn't mandatory. In my previous patch there was no 'not null' and thats why there were failure.
          I had tested it with upgrades from 10.3 only. Sorry for the trouble.

          In my new patch I have added 'not null; in create table statement and also checking if the version is at least 10.2 while trying "alter table alter column i null" as this was not
          available in 10.2.

          I have tested upgrade tests with following version in release directory

          10.0.2.1
          10.1.1.0
          10.1.2.1
          10.1.3.1
          10.2.1.6
          10.2.2.0
          10.3.1.4
          10.3.2.1

          Show
          Anurag Shekhar added a comment - In Derby 10.0 the syntax for creating primary key along with table creation was create table t1 (i integer not null primary key) in latter version not null wasn't mandatory. In my previous patch there was no 'not null' and thats why there were failure. I had tested it with upgrades from 10.3 only. Sorry for the trouble. In my new patch I have added 'not null; in create table statement and also checking if the version is at least 10.2 while trying "alter table alter column i null" as this was not available in 10.2. I have tested upgrade tests with following version in release directory 10.0.2.1 10.1.1.0 10.1.2.1 10.1.3.1 10.2.1.6 10.2.2.0 10.3.1.4 10.3.2.1
          Hide
          Kathey Marsden added a comment -

          Backed out the change to the upgrade tests. It was causing failures in the
          tinderbox run.
          http://dbtg.thresher.com/derby/test/tinderbox_trunk16/jvm1.6/testing/Limited/testSummary-641405.html

          Show
          Kathey Marsden added a comment - Backed out the change to the upgrade tests. It was causing failures in the tinderbox run. http://dbtg.thresher.com/derby/test/tinderbox_trunk16/jvm1.6/testing/Limited/testSummary-641405.html
          Hide
          Kathey Marsden added a comment -

          Committed revision 641398 for the upgrade tests.

          Show
          Kathey Marsden added a comment - Committed revision 641398 for the upgrade tests.
          Hide
          Anurag Shekhar added a comment -

          upgrade tests for modified error messages.

          Show
          Anurag Shekhar added a comment - upgrade tests for modified error messages.
          Hide
          Anurag Shekhar added a comment -

          thanks kathey for committing the patch.
          I will upload the followup patch with updated upgrade tests in a days time.

          Show
          Anurag Shekhar added a comment - thanks kathey for committing the patch. I will upload the followup patch with updated upgrade tests in a days time.
          Hide
          Kathey Marsden added a comment -

          Thanks Anurag, the v3 patch looks good. I verified the soft upgrade messages manually and am running tests and then will commit.
          It would be nice to have upgrade tests in a follow-up patch.

          Show
          Kathey Marsden added a comment - Thanks Anurag, the v3 patch looks good. I verified the soft upgrade messages manually and am running tests and then will commit. It would be nice to have upgrade tests in a follow-up patch.
          Hide
          Anurag Shekhar added a comment -

          I have taken off the new method from standardException and moved the code
          to select mesage id inline derby-3523v3.diff.

          I don't any strong feeling about either of the approach. Based on
          discussion either v2 (with new method in StandardException) and v3 (inline
          message id selection) can be used.

          Show
          Anurag Shekhar added a comment - I have taken off the new method from standardException and moved the code to select mesage id inline derby-3523v3.diff. I don't any strong feeling about either of the approach. Based on discussion either v2 (with new method in StandardException) and v3 (inline message id selection) can be used.
          Hide
          V.Narayanan added a comment - - edited

          >The new method in StandardException seems a little overkill for what really could be a ? : operator in the calling code.

          > String sqlState = checkVersion(....) ? SQLState.XXX : SQLState.YYY;

          > If the method is to be kept, it might be clearer to have its name reflect what it is doing.

          I agree that using ? : would be a better way of implementing this and also that the name of the method
          could probably be changed to reflect what it is doing.

          But I do not agree that it is overkill to factor out logic to choose a SQLState based on whether it is soft or
          hard upgrade.

          Why would it be wrong to put in a utility method in StandardException that chooses one of the two
          SQLStates based on a boolean if a valid case for its usage could be justified (e.g. choosing one
          based on if it is hard or soft upgrade) ?

          This is not a case that is specific to the class using this logic but a generic case that could
          occur in multiple parts of the codebase.

          Show
          V.Narayanan added a comment - - edited >The new method in StandardException seems a little overkill for what really could be a ? : operator in the calling code. > String sqlState = checkVersion(....) ? SQLState.XXX : SQLState.YYY; > If the method is to be kept, it might be clearer to have its name reflect what it is doing. I agree that using ? : would be a better way of implementing this and also that the name of the method could probably be changed to reflect what it is doing. But I do not agree that it is overkill to factor out logic to choose a SQLState based on whether it is soft or hard upgrade. Why would it be wrong to put in a utility method in StandardException that chooses one of the two SQLStates based on a boolean if a valid case for its usage could be justified (e.g. choosing one based on if it is hard or soft upgrade) ? This is not a case that is specific to the class using this logic but a generic case that could occur in multiple parts of the codebase.
          Hide
          Daniel John Debrunner added a comment -

          The new method in StandardException seems a little overkill for what really could be a ? : operator in the calling code.

          String sqlState = checkVersion(....) ? SQLState.XXX : SQLState.YYY;

          If the method is to be kept, it might be clearer to have its name reflect what it is doing. Just reading the calling code in its current from
          might make the reader think that it returns an exception with two SQLStates, e.g. one nested within the other.

          Having the ? : code in-line I think would be clearer code.

          Show
          Daniel John Debrunner added a comment - The new method in StandardException seems a little overkill for what really could be a ? : operator in the calling code. String sqlState = checkVersion(....) ? SQLState.XXX : SQLState.YYY; If the method is to be kept, it might be clearer to have its name reflect what it is doing. Just reading the calling code in its current from might make the reader think that it returns an exception with two SQLStates, e.g. one nested within the other. Having the ? : code in-line I think would be clearer code.
          Hide
          Kathey Marsden added a comment -

          Thank you for the patch. It looks good to me except I am a bit unsure about the new method in StandardException. It seems specialized for the single argument case and a little out of place. I think I would rather see the logic in the calling code, but I realize this was discussed earlier and perhaps this was the consensus that was reached. If I am the only one that is uncomfortable with it I will go ahead and commit the patch as is tomorrow.

          Should we have tests for the soft upgrade case?

          Kathey

          Show
          Kathey Marsden added a comment - Thank you for the patch. It looks good to me except I am a bit unsure about the new method in StandardException. It seems specialized for the single argument case and a little out of place. I think I would rather see the logic in the calling code, but I realize this was discussed earlier and perhaps this was the consensus that was reached. If I am the only one that is uncomfortable with it I will go ahead and commit the patch as is tomorrow. Should we have tests for the soft upgrade case? Kathey
          Hide
          Anurag Shekhar added a comment -

          Thanks Jorgen for pointing out.

          Attaching new version (derby-3523v2.diff) with new ids (severity set to S)

          Show
          Anurag Shekhar added a comment - Thanks Jorgen for pointing out. Attaching new version (derby-3523v2.diff) with new ids (severity set to S)
          Hide
          Jørgen Løland added a comment -

          I think the severity-level ('S') is mandatory if the SQL state is more than 5 characters long. Thus, I think you need to use 42831.S.1 instead of 42831.1. I might be wrong, but at least that's how I interpret the SQLState class javadoc:

          "The SQL state of an error message dictates the error's severity.
          The severity is determined from the first two characters of the
          state if the state is five characters long, otherwise the state
          is expected to be 7 characters long and the last character determines
          the state. If the state is seven characters long then only the first
          five will be seen by the error reporting code and exception."

          Show
          Jørgen Løland added a comment - I think the severity-level ('S') is mandatory if the SQL state is more than 5 characters long. Thus, I think you need to use 42831.S.1 instead of 42831.1. I might be wrong, but at least that's how I interpret the SQLState class javadoc: "The SQL state of an error message dictates the error's severity. The severity is determined from the first two characters of the state if the state is five characters long, otherwise the state is expected to be 7 characters long and the last character determines the state. If the state is seven characters long then only the first five will be seen by the error reporting code and exception."
          Hide
          Anurag Shekhar added a comment -

          This patch () adds new messages for X0Y63, X0Y63 and X0Y63.S

          I have added new message ids X0Y63.1, X0Y63.1, X0Y63.S.1 and assigned
          new messages to them. I have also introduced new constraints in SQLState to point to these message ids.

          A new method in StandardException (newException (string, string, boolean, Object),
          based on 3rd parameter, decides weather to use existing message id or the newer
          one.

          Modified files

          java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj

          Modified existing checkVersion method to return boolean (same as DataDictionary.checkVersion).

          Updated two instances where X0Y63 was thrown to use new method from StandardExcetion to use message based on mode its running under.

          java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
          java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java

          Updated to choose between older and newer message using StandardExcetion.

          java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java

          Added additional identify SQLState.LANG_NULL_DATA_IN_PRIMARY_KEY as case
          where setting not null is possible because of constraint.

          java/engine/org/apache/derby/iapi/error/StandardException.java
          Added a new method which accepts two message id and decides which one to use based on third parameter (boolean).

          java/engine/org/apache/derby/loc/messages.xml

          added new messages and message id.

          java/shared/org/apache/derby/shared/common/reference/SQLState.java
          added constant for new message ids.

          java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
          java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
          java/testing/org/apache/derbyTesting/functionTests/master/nulls.out

          updated tests results to expect new message.

          Show
          Anurag Shekhar added a comment - This patch () adds new messages for X0Y63, X0Y63 and X0Y63.S I have added new message ids X0Y63.1, X0Y63.1, X0Y63.S.1 and assigned new messages to them. I have also introduced new constraints in SQLState to point to these message ids. A new method in StandardException (newException (string, string, boolean, Object), based on 3rd parameter, decides weather to use existing message id or the newer one. Modified files java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Modified existing checkVersion method to return boolean (same as DataDictionary.checkVersion). Updated two instances where X0Y63 was thrown to use new method from StandardExcetion to use message based on mode its running under. java/engine/org/apache/derby/impl/sql/compile/TableElementList.java java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java Updated to choose between older and newer message using StandardExcetion. java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java Added additional identify SQLState.LANG_NULL_DATA_IN_PRIMARY_KEY as case where setting not null is possible because of constraint. java/engine/org/apache/derby/iapi/error/StandardException.java Added a new method which accepts two message id and decides which one to use based on third parameter (boolean). java/engine/org/apache/derby/loc/messages.xml added new messages and message id. java/shared/org/apache/derby/shared/common/reference/SQLState.java added constant for new message ids. java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out java/testing/org/apache/derbyTesting/functionTests/master/altertable.out java/testing/org/apache/derbyTesting/functionTests/master/nulls.out updated tests results to expect new message.
          Hide
          V.Narayanan added a comment -

          >I don't think adding information about specific sql states StandardException is a good idea.

          Sorry about the ambiguity in the previous comment. I did not mean to ask you to push this specific
          SQLState creation into StandardException.

          If you were to following my suggestion you would have one method in StandardException whose
          "first three" parameters would probably be (String SQLState1, String SQLState2, <type of upgrade>).

          In the simplest case You could choose the type of upgrade to be a boolean called hardupgrade, where
          it being,
          false - would indicate that SQLState1 is to be used
          true - would indicate that SQLState2 is to be used

          In the above case you would be retaining the logic for determining if it is a hard or a soft upgrade
          in the calling method.

          You could even decide on pushing the type of upgrade deciding logic into StandardException based on
          if you think it would be appropriate.

          Ofcourse I must say I do agree with

          "StanduardException does very specialized job of creating SQLException and filling it with SQLState and messages.
          IMO Its better if we don't add too many functionality to it."

          you have a strong case and your argument does leave me in a dilemna as to what would be most appropriate here.

          Sorry about the confusion,

          <Narayanan tells himself not to write comments on JIRA issues when it is past
          bed-time >

          Show
          V.Narayanan added a comment - >I don't think adding information about specific sql states StandardException is a good idea. Sorry about the ambiguity in the previous comment. I did not mean to ask you to push this specific SQLState creation into StandardException. If you were to following my suggestion you would have one method in StandardException whose "first three" parameters would probably be (String SQLState1, String SQLState2, <type of upgrade>). In the simplest case You could choose the type of upgrade to be a boolean called hardupgrade, where it being, false - would indicate that SQLState1 is to be used true - would indicate that SQLState2 is to be used In the above case you would be retaining the logic for determining if it is a hard or a soft upgrade in the calling method. You could even decide on pushing the type of upgrade deciding logic into StandardException based on if you think it would be appropriate. Ofcourse I must say I do agree with "StanduardException does very specialized job of creating SQLException and filling it with SQLState and messages. IMO Its better if we don't add too many functionality to it." you have a strong case and your argument does leave me in a dilemna as to what would be most appropriate here. Sorry about the confusion, <Narayanan tells himself not to write comments on JIRA issues when it is past bed-time >
          Hide
          Mike Matrigali added a comment -

          thanks for everyone's input on this, I didn't realize we had this flexibility with the messages so now definitely don't think we should create new states for these messsages. Given the existing support seems like best solution would be to return same error number but different message. I will leave it to Anurag whether to inline the code that figures out which message, or
          to create a utility routine somewhere.

          Show
          Mike Matrigali added a comment - thanks for everyone's input on this, I didn't realize we had this flexibility with the messages so now definitely don't think we should create new states for these messsages. Given the existing support seems like best solution would be to return same error number but different message. I will leave it to Anurag whether to inline the code that figures out which message, or to create a utility routine somewhere.
          Hide
          Anurag Shekhar added a comment -

          I don't think adding information about specific sql states StandardException is a good idea.

          Its not very frequently where error messages needs modification. I prefer keeping information about it in the respective classes only. These classes are anyway aware about the soft upgrade as they allow or disallow creation of unique constraint based on it.

          StanduardException does very specialized job of creating SQLException and filling it with SQLState and messages. IMO Its better if we don't add too many functionality to it.

          Show
          Anurag Shekhar added a comment - I don't think adding information about specific sql states StandardException is a good idea. Its not very frequently where error messages needs modification. I prefer keeping information about it in the respective classes only. These classes are anyway aware about the soft upgrade as they allow or disallow creation of unique constraint based on it. StanduardException does very specialized job of creating SQLException and filling it with SQLState and messages. IMO Its better if we don't add too many functionality to it.
          Hide
          V.Narayanan added a comment -

          You could consider introducing a newException method in the StandardException class
          that would accept two SQLStates (one for hard, one for soft upgrade), the data dictionary
          version.

          This method would use the appropriate SQLState depending on whether hard upgrade
          or soft upgrade is being done for the mentioned data dictionary version.

          Show
          V.Narayanan added a comment - You could consider introducing a newException method in the StandardException class that would accept two SQLStates (one for hard, one for soft upgrade), the data dictionary version. This method would use the appropriate SQLState depending on whether hard upgrade or soft upgrade is being done for the mentioned data dictionary version.
          Hide
          Anurag Shekhar added a comment -

          I am planning to introduce similar to the following

          String errorState = null;
          if (getLanguageConnectionContext().getDataDictionary()
          .checkVersion(DataDictionary.DD_VERSION_DERBY_10_4,
          null))

          { errorState = SQLState.LANG_MODIFY_COLUMN_EXISTING_PRIMARY_KEY; }

          else

          { //soft upgrade mode errorState = SQLState.LANG_MODIFY_COLUMN_EXISTING_CONSTRAINT; }

          throw StandardException.newException(errorState, name);

          List of effected files
          java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
          java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
          java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
          java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
          java/shared/org/apache/derby/shared/common/reference/SQLState.java
          java/engine/org/apache/derby/loc/messages.xml

          In addition to these, out files of few harness tests will also need modifications.

          Show
          Anurag Shekhar added a comment - I am planning to introduce similar to the following String errorState = null; if (getLanguageConnectionContext().getDataDictionary() .checkVersion(DataDictionary.DD_VERSION_DERBY_10_4, null)) { errorState = SQLState.LANG_MODIFY_COLUMN_EXISTING_PRIMARY_KEY; } else { //soft upgrade mode errorState = SQLState.LANG_MODIFY_COLUMN_EXISTING_CONSTRAINT; } throw StandardException.newException(errorState, name); List of effected files java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj java/engine/org/apache/derby/impl/sql/compile/TableElementList.java java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java java/shared/org/apache/derby/shared/common/reference/SQLState.java java/engine/org/apache/derby/loc/messages.xml In addition to these, out files of few harness tests will also need modifications.
          Hide
          V.Narayanan added a comment -

          Anurag says -

          thanks every one for pitching.
          Let me try to summarize it

          Derby doesn;t directly supports choosing message based on
          the type of run (soft upgrade more or a regular mode), but it
          does allow associating multiple messages to a single sql state
          bye adding "." extension (sqlstate.1, sqltate.2 etc).
          From the code this can be used to throw correct error message
          after detecting the mode engine is running under (use existing sql
          states during soft upgrade and newer once during regular operation).

          The extensions of the sql state is dropped while setting the sql
          state to SQLException so user won't experience any difference
          during soft upgrade and will get newer message with same sql state
          during regular operation.

          Show
          V.Narayanan added a comment - Anurag says - thanks every one for pitching. Let me try to summarize it Derby doesn;t directly supports choosing message based on the type of run (soft upgrade more or a regular mode), but it does allow associating multiple messages to a single sql state bye adding "." extension (sqlstate.1, sqltate.2 etc). From the code this can be used to throw correct error message after detecting the mode engine is running under (use existing sql states during soft upgrade and newer once during regular operation). The extensions of the sql state is dropped while setting the sql state to SQLException so user won't experience any difference during soft upgrade and will get newer message with same sql state during regular operation.
          Hide
          Jørgen Løland added a comment - - edited

          (Attaching mail thread)

          Narayanan wrote:
          > Jørgen Løland wrote:
          >> Mike Matrigali wrote:
          >> > It does seem like the best case would be for the error message
          >> system to
          >> > somehow return a different error message for the same number if it is
          >> > in soft upgrade vs. hard upgrade. Does the error message system
          >> support
          >> > such a thing?
          >>
          >> Yes, it does. If you want multiple messages with the same SQL state,
          >> you need to add the ".<severity>.<#>" information to the error state
          >> in SQLState.java. E.g, all these messages have SQL State 08004:
          >>
          >> ----- snip from SQLState.java ----
          >> String LOGIN_FAILED = "08004";
          >> String NET_CONNECT_AUTH_FAILED = "08004.C.1";
          >> String NET_DATABASE_NOT_FOUND = "08004.C.2";
          >> String AUTH_DATABASE_CONNECTION_REFUSED = "08004.C.3";
          >> String AUTH_SHUTDOWN_NOT_DB_OWNER = "08004.C.4";
          >> String AUTH_ENCRYPT_NOT_DB_OWNER = "08004.C.5";
          >> ...
          >> ----- snip -------
          >>
          >> If, e.g., both these messages
          >>
          >> '

          {0}' cannot be a column of a primary key or unique key because it can
          >> contain null values.
          >> '{0}

          ' cannot be a column of a primary key because it can contain null
          >> values.
          >>
          >> ... should have the same 42831 error code, you would need something
          >> like this in the SQLState.java file:
          >>
          >> String LANG_DB2_ADD_UNIQUE_OR_PRIMARY_KEY_ON_NULL_COLS = "42831.S.1";
          >> String LANG_DB2_ADD_PRIMARY_KEY_ON_NULL_COLS = "42831.S.2";
          >>
          >> 'S' because 42831 is statement severity level (see class javadoc in
          >> SQLState.java), while 1 and 2 are used to tell the messages apart.
          >> messages.xml also needs to be updated, of course.
          >>
          > But how will this help in printing different error messages in hard and
          > soft upgrade? I thought this would be useful
          > in grouping together the same family of messages, but can this act as a
          > switch for messages that need to be printed
          > in hard and soft upgrade?
          >
          > Narayanan
          >

          I misinterpreted Mike's question. The code where these exceptions are thrown needs to explicitly use the correct exception. However, this is the same thing that must be done if fix suggestion 2 is chosen.

          I think suggestion 2 should be modified like this:

          2b: Introduce new message without changing the state and use old messages only during soft upgrade run.

          Show
          Jørgen Løland added a comment - - edited (Attaching mail thread) Narayanan wrote: > Jørgen Løland wrote: >> Mike Matrigali wrote: >> > It does seem like the best case would be for the error message >> system to >> > somehow return a different error message for the same number if it is >> > in soft upgrade vs. hard upgrade. Does the error message system >> support >> > such a thing? >> >> Yes, it does. If you want multiple messages with the same SQL state, >> you need to add the ".<severity>.<#>" information to the error state >> in SQLState.java. E.g, all these messages have SQL State 08004: >> >> ----- snip from SQLState.java ---- >> String LOGIN_FAILED = "08004"; >> String NET_CONNECT_AUTH_FAILED = "08004.C.1"; >> String NET_DATABASE_NOT_FOUND = "08004.C.2"; >> String AUTH_DATABASE_CONNECTION_REFUSED = "08004.C.3"; >> String AUTH_SHUTDOWN_NOT_DB_OWNER = "08004.C.4"; >> String AUTH_ENCRYPT_NOT_DB_OWNER = "08004.C.5"; >> ... >> ----- snip ------- >> >> If, e.g., both these messages >> >> ' {0}' cannot be a column of a primary key or unique key because it can >> contain null values. >> '{0} ' cannot be a column of a primary key because it can contain null >> values. >> >> ... should have the same 42831 error code, you would need something >> like this in the SQLState.java file: >> >> String LANG_DB2_ADD_UNIQUE_OR_PRIMARY_KEY_ON_NULL_COLS = "42831.S.1"; >> String LANG_DB2_ADD_PRIMARY_KEY_ON_NULL_COLS = "42831.S.2"; >> >> 'S' because 42831 is statement severity level (see class javadoc in >> SQLState.java), while 1 and 2 are used to tell the messages apart. >> messages.xml also needs to be updated, of course. >> > But how will this help in printing different error messages in hard and > soft upgrade? I thought this would be useful > in grouping together the same family of messages, but can this act as a > switch for messages that need to be printed > in hard and soft upgrade? > > Narayanan > I misinterpreted Mike's question. The code where these exceptions are thrown needs to explicitly use the correct exception. However, this is the same thing that must be done if fix suggestion 2 is chosen. I think suggestion 2 should be modified like this: 2b: Introduce new message without changing the state and use old messages only during soft upgrade run.
          Hide
          Kathey Marsden added a comment -

          I see examples in messages.xml, such as 8006 where we have more than one instance of a message with the same SQLState but different messages. Is that a possibility.

          Show
          Kathey Marsden added a comment - I see examples in messages.xml, such as 8006 where we have more than one instance of a message with the same SQLState but different messages. Is that a possibility.
          Hide
          Anurag Shekhar added a comment -

          There can be two ways to fix this problem

          1. Change the message to remove unique constraint from the message.

          Problem
          During soft upgrade run unique constraint can't have null able columns.
          While using the data base throwing new message may give wrong information
          to the user claiming that he was using primary key when actually he was using
          unique constraint.

          2. Introduce new message with a new state and use older state only during soft upgrade run.

          Problem
          There will be impact of existing application if they are relying on
          sql state to detect the cause of failure in creating primary key.

          Show
          Anurag Shekhar added a comment - There can be two ways to fix this problem 1. Change the message to remove unique constraint from the message. Problem During soft upgrade run unique constraint can't have null able columns. While using the data base throwing new message may give wrong information to the user claiming that he was using primary key when actually he was using unique constraint. 2. Introduce new message with a new state and use older state only during soft upgrade run. Problem There will be impact of existing application if they are relying on sql state to detect the cause of failure in creating primary key.

            People

            • Assignee:
              Unassigned
              Reporter:
              Anurag Shekhar
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:

                Development