You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Lars Gråmark (JIRA)" <de...@db.apache.org> on 2006/07/10 23:51:29 UTC
[jira] Created: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Attempt to modify an identity column error after resetting identity column
--------------------------------------------------------------------------
Key: DERBY-1495
URL: http://issues.apache.org/jira/browse/DERBY-1495
Project: Derby
Type: Bug
Components: SQL
Versions: 10.2.0.0
Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
Reporter: Lars Gråmark
Priority: Minor
When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
The following statements will reproduce the error.
CREATE TABLE MYTABLE (
id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
,col2 INT NOT NULL
)
-- Insert using an explicit value on the ID-field
INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
-- Reset the identity field
ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
-- Try inserting another explicit value and the error below is thrown.
INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
-- Although, this works fine
INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Kristian Waagan (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=comments#action_12449992 ]
Kristian Waagan commented on DERBY-1495:
----------------------------------------
Should the Fix Version be updated to reflect that the fix is in the 10.2 branch as well?
If so, this goes for the related Jira issue too.
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Assigned To: Bryan Pendleton
> Priority: Minor
> Fix For: 10.3.0.0
>
> Attachments: preserveColDefault_v1.diff
>
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=comments#action_12449781 ]
Bryan Pendleton commented on DERBY-1495:
----------------------------------------
Hi Rajesh, I think that is a good suggestion. I will investigate merging this fix to 10.2.
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Assigned To: Bryan Pendleton
> Priority: Minor
> Fix For: 10.3.0.0
>
> Attachments: preserveColDefault_v1.diff
>
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Rajesh Kartha (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=comments#action_12449774 ]
Rajesh Kartha commented on DERBY-1495:
--------------------------------------
Seems like a useful fix for the 10.2 branch also, is any committer planning to port the fix to 10.2 ?
-Rajesh
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Assigned To: Bryan Pendleton
> Priority: Minor
> Fix For: 10.3.0.0
>
> Attachments: preserveColDefault_v1.diff
>
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Bryan Pendleton (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=comments#action_12436243 ]
Bryan Pendleton commented on DERBY-1495:
----------------------------------------
Thanks Kristian for investigating this problem. My hunch is that all the fun happens
in AlterTableConstantAction.modifyColumnDefault. The ALTER TABLE statement
doesn't provide all the information about the column's auto-increment behaviors so
the modifyColumnDefault() method has to merge the new behaviors specified on
the ALTER TABLE statement with the existing behaviors that the column already has.
My guess is that the problem lies in this merging process; the modifyColumnDefault()
method is not being careful enough to use the previous column behaviors in those
cases where the ALTER TABLE statement has not provided new behaviors.
Hope this helps.
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Priority: Minor
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Kristian Waagan (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=comments#action_12449313 ]
Kristian Waagan commented on DERBY-1495:
----------------------------------------
I have tested/reviewed the patch, and was unable to find any problems. More specifically, I did not see the error I saw when trying to fix the bug. Although my approach were pretty much the same, I suspect I might have done what Bryan did somewhere else in the code path (and in that way made something go wrong).
I ran derbyall and suites.All with both Java SE 5 and 6. Only saw three known problems in derbyall (Compatibility and VerifySignatures).
Patch is small and clean, everything seems to work and testcases have been added.
+1 to commit
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Assigned To: Bryan Pendleton
> Priority: Minor
> Attachments: preserveColDefault_v1.diff
>
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=all ]
Bryan Pendleton resolved DERBY-1495.
------------------------------------
Fix Version/s: 10.3.0.0
Resolution: Fixed
Derby Info: (was: [Patch Available])
Kristian, thank you very much for the review and the testing.
I committed the patch to subversion as revision 474502.
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Assigned To: Bryan Pendleton
> Priority: Minor
> Fix For: 10.3.0.0
>
> Attachments: preserveColDefault_v1.diff
>
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=all ]
Bryan Pendleton updated DERBY-1495:
-----------------------------------
Derby Info: [Patch Available]
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Assigned To: Bryan Pendleton
> Priority: Minor
> Attachments: preserveColDefault_v1.diff
>
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Suresh Thalamati (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=comments#action_12430083 ]
Suresh Thalamati commented on DERBY-1495:
-----------------------------------------
I came across this issue while doing a buddy testing for alter table with RESTART . It is really irritating for an user to try alter table with restart on GENERATED BY DEFAULT COLUMN and find , he/she can not insert values manually. I think , this bug should get fixed for 10.2 , if possible .
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Priority: Minor
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-1495) Attempt to modify an identity
column error after resetting identity column
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> Is the error in this method, or does it happen earlier so the input is not as expected?
> What step have I forgotten to do?
You may have already figured this out, or not, but if not, here's a suggestion:
Post a patch with the code that you've tried so far, and the test cases that
you're running. That will make it easier for others of us to offer suggestions
and ideas.
thanks,
bryan
[jira] Commented: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Kristian Waagan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=comments#action_12436264 ]
Kristian Waagan commented on DERBY-1495:
----------------------------------------
Thanks Bryan.
I have been able to determine what seems to be causing the problem, but have not yet understood how to fix it. This is my first venture into the this area of the code, so things are very unclear to me.
First, after studying the various variables describing an identity column, I noticed that if the DefaultInfo assoiciated with an identity column is null, the column is said to be "generated always as identity". In effect, the column definition is changed after the restart/increment operation, because the defaultInfo is set to null. This can be observed with the new describe command in ij.
Bryan's guess seems to be valid, but if I add the old DefaultInfoImpl instance to the new one, I get a NPE when doing a drop on the table the next time I boot the database. It happens for 'BasicDependencyManager.clearDependencies'. It also happens if I create a new instance (new DefaultInfoImpl(true,null,null)) and add it to the newly created ColumnDescriptor. This piece of the code is in AlterTableConstantAction.modifyColumnDefault, as Bryan mentioned.
Is the error in this method, or does it happen earlier so the input is not as expected?
What step have I forgotten to do?
I'll keep looking into this, but any piece of further advice is appreciated!
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Priority: Minor
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=all ]
Bryan Pendleton reassigned DERBY-1495:
--------------------------------------
Assignee: Bryan Pendleton
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Assigned To: Bryan Pendleton
> Priority: Minor
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=all ]
Bryan Pendleton updated DERBY-1495:
-----------------------------------
Attachment: preserveColDefault_v1.diff
Attached is preserveColDefault_v1.diff, a proposed patch to solve this problem,
as well as to solve DERBY-1645, which is closely related.
Kristian, I was not able to reproduce the problems that you had in the
dependency system when dropping the table. Perhaps you could experiment
with this patch and see if you can reproduce those problems?
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Assigned To: Bryan Pendleton
> Priority: Minor
> Attachments: preserveColDefault_v1.diff
>
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=comments#action_12449870 ]
Bryan Pendleton commented on DERBY-1495:
----------------------------------------
The merge to 10.2 was clean, and my testing was successful. Submitted the merged
change to the 10.2 branch as revision 475081
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Assigned To: Bryan Pendleton
> Priority: Minor
> Fix For: 10.3.0.0
>
> Attachments: preserveColDefault_v1.diff
>
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Bryan Pendleton (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=comments#action_12438134 ]
Bryan Pendleton commented on DERBY-1495:
----------------------------------------
Possible duplicate of DERBY-1645?
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.5
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Priority: Minor
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Kristian Waagan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=comments#action_12436538 ]
Kristian Waagan commented on DERBY-1495:
----------------------------------------
I'm going on vacation for a week, so I won't be doing anything on this issue for a while.
Anyone should feel free to take over ;)
The problem should be easy to find if you follow the hint Bryan gave in a comment above.
I don't have a preliminary patch I can post at this time, but what I found out is described in the comment above.
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Priority: Minor
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Alan Baldwin (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=comments#action_12436262 ]
Alan Baldwin commented on DERBY-1495:
-------------------------------------
Note:
The same thing is happening in Derby 10.1.x when you try to change Set Increment By. Please reference issue: http://issues.apache.org/jira/browse/DERBY-1645.
There is no With Restart command in 10.1.x, but I assume the behavior is related.
Like the previous poster, I also had to jump through some hoops to get around this issue.
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Priority: Minor
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=all ]
Bryan Pendleton updated DERBY-1495:
-----------------------------------
Fix Version/s: 10.2.1.8
Adding 10.2.1.8 Fix Version to reflect the merging of this fix to 10.2 branch. Thanks Kristian for catching this.
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Assigned To: Bryan Pendleton
> Priority: Minor
> Fix For: 10.3.0.0, 10.2.1.8
>
> Attachments: preserveColDefault_v1.diff
>
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Kristian Waagan (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=comments#action_12436230 ]
Kristian Waagan commented on DERBY-1495:
----------------------------------------
I'm taking a brief look at this. If someone with more knowledge wants to help, or fix the bug, let me know :)
Note that the same problem occur if you try to change the increment of the identity column.
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Priority: Minor
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Jimisola Laursen (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=comments#action_12449788 ]
Jimisola Laursen commented on DERBY-1495:
-----------------------------------------
Backporting the fix to 10.2 would be highly appreciated.
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Assigned To: Bryan Pendleton
> Priority: Minor
> Fix For: 10.3.0.0
>
> Attachments: preserveColDefault_v1.diff
>
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-1495) Attempt to modify an identity column
error after resetting identity column
Posted by "Jimisola Laursen (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1495?page=comments#action_12436168 ]
Jimisola Laursen commented on DERBY-1495:
-----------------------------------------
Seems as if a lot of people have came across this issue. It was reported more than two months ago and still no action - not even a comment from the developer team.
This issue has minor priority, but it should be considered MAJOR. We currently have to copy the Derby database back-and-forth due to this issue.
I sincerely hope that this will get into 10.2. Especially, since Derby 10.2 will be the JavaDB shipped with the JDK (if am not mistaking).
> Attempt to modify an identity column error after resetting identity column
> --------------------------------------------------------------------------
>
> Key: DERBY-1495
> URL: http://issues.apache.org/jira/browse/DERBY-1495
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.0
> Environment: Java 1.5.0_06-b05 on Linux Ubuntu 5.10. Derby version 10.2.0.3-412239
> Reporter: Lars Gråmark
> Priority: Minor
>
> When an identity counter is altered using the ALTER TABLE statement below, it seems as if the GENERATED BY DEFAULT behavior is lost.
> The following statements will reproduce the error.
> CREATE TABLE MYTABLE (
> id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
> ,col2 INT NOT NULL
> )
> -- Insert using an explicit value on the ID-field
> INSERT INTO MYTABLE(ID, COL2) VALUES(2, 2)
> -- Reset the identity field
> ALTER TABLE mytable ALTER COLUMN id RESTART WITH 3
> -- Try inserting another explicit value and the error below is thrown.
> INSERT INTO MYTABLE(ID, COL2) VALUES(4, 4)
> Error: SQL Exception: Attempt to modify an identity column 'ID'. , SQL State: 42Z23, Error Code: 30000
> -- Although, this works fine
> INSERT INTO MYTABLE(COL2) VALUES(4)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira