initial value reset when modifying tables with identity columns?

yzguy · March 13, 2020 5:27PM

if you have a table with an identity column, and have data in it, then later need to modify that table, the script created to deploy the table, creates a new one, inserts the correct records, with the correct id's, but because it is a new table, with a "new" identity column, the system sequence behind the column is still at 1. Then does renames so that the new table is named the same as the old one. This means that after the deploy with this script, the next insert into this table will fail, due to a PK error (assuming you are using this identity column as the PK, which is typical) because you already have records in there with the id value = 1. What should happen, is that after this new table has data inserted (or when it is created), the system sequence needs to have the initial value of the MAX() +1 of the current data from the current table, or the next value from the current sequence linked to the current column, so that the next insert will succeed.

This is an example deploy script to modify a table called BATCH, but if there is data in that table, after this deployment, the next insert fails, as it tries to insert 1 into batch_id, which already exists.

WHENEVER SQLERROR EXIT SQL.SQLCODE

--

-- Script generated by Schema Compare for Oracle 5.2.3.1222 on 04/03/2020 12:16:12

--

SET DEFINE OFF

CREATE TABLE espsvc_owner.rg_temp_602116336_0 (

batch_id NUMBER GENERATED BY DEFAULT AS IDENTITY,

coor_id VARCHAR2(50 BYTE) NOT NULL,

stts VARCHAR2(30 BYTE),

batch_dt DATE,

crt_nm VARCHAR2(256 BYTE) DEFAULT USER,

crt_dtm TIMESTAMP DEFAULT SYSTIMESTAMP,

last_udt_usr_nm VARCHAR2(256 BYTE) DEFAULT USER,

last_udt_dtm TIMESTAMP DEFAULT SYSTIMESTAMP,

CONSTRAINT RG_TEMP_602116336_3 PRIMARY KEY (batch_id) USING INDEX (CREATE UNIQUE INDEX espsvc_owner.rg_temp_602116336_4 ON espsvc_owner.rg_temp_602116336_0(batch_id) )

);

INSERT INTO espsvc_owner.rg_temp_602116336_0(batch_id,coor_id,stts,batch_dt,crt_nm,crt_dtm,last_udt_usr_nm,last_udt_dtm) SELECT batch_id,coor_id,stts,batch_dt,crt_nm,crt_dtm,last_udt_usr_nm,last_udt_dtm FROM espsvc_owner."BATCH";

DROP TABLE espsvc_owner."BATCH";

ALTER TABLE espsvc_owner.rg_temp_602116336_0 RENAME TO "BATCH";

ALTER TABLE espsvc_owner."BATCH" RENAME CONSTRAINT RG_TEMP_602116336_3 TO batch_pk;

ALTER INDEX espsvc_owner.rg_temp_602116336_4 RENAME TO BATCH_PK;

COMMENT ON COLUMN espsvc_owner."BATCH".coor_id IS 'Surrogate key and coorelation identifer';

COMMENT ON COLUMN espsvc_owner."BATCH".stts IS 'Status';

COMMENT ON COLUMN espsvc_owner."BATCH".batch_dt IS 'Batch Date Time';

COMMENT ON COLUMN espsvc_owner."BATCH".crt_nm IS 'Created By username';

COMMENT ON COLUMN espsvc_owner."BATCH".crt_dtm IS 'Created date time';

COMMENT ON COLUMN espsvc_owner."BATCH".last_udt_usr_nm IS 'Last Update username';

COMMENT ON COLUMN espsvc_owner."BATCH".last_udt_dtm IS 'Last Update date time';

Eddie D · March 20, 2020 10:58AM

Hi, thank you for your forum post.

I am surprised to not have had this reported previously.

So I can produce an accurate reproduction of the problem,
would it be possible to obtain the DDL of the table prior to change? Or details on what column or columns were dropped or modified that required the rebuild process to take place?

Many Thanks
Eddie

yzguy · March 23, 2020 10:10PM

I did some more digging. At first I thought this was a general issue that would happen any time a table was recreated by schema compare (like when you have column order enforced and add a column in the middle), but then when I tried to reproduce it on a different server, I was unable to. I was actually pretty surprised at how well it did handle it (so good job there

).

So here is what I learned:
I am pretty sure the issue is that schema compare is NOT able to correctly determine that the column is already an identity in the db. We are comparing a script location to a live db, then generating the release script as part of our release process. The script generated is what I posted above, where the table is recreated, data inserted, and sequence not updated to start at a higher number.

Our release process ALSO compares the db back to the script location, to create a "backout" script. This back out script looks like this:

----------------------------

WHENEVER SQLERROR EXIT SQL.SQLCODE

--

-- Script generated by Schema Compare for Oracle 5.2.3.1222 on 13/03/2020 15:56:52

--

SET DEFINE OFF

ALTER TABLE espsvc_owner."BATCH" MODIFY batch_id DROP IDENTITY;

ALTER TABLE espsvc_owner."BATCH" MODIFY (batch_id NUMBER DEFAULT espsvc_owner."ISEQ$$_23832".nextval NOT NULL);

------------------------------

This tells me that schema compare thinks that the db column is NOT an identity, and just that the default value is the sequence.nextval.

This same process will happen over and over. If the release script runs, and redgate makes the table what it thinks it needs, the next release of the SAME table code, will generate the SAME release script.

I'm pretty sure it is comparing a column that is identity in the scripts folder, to the db, and thinking that the db column is NOT an identity (when in fact it is, schema compare made the script to ensure that last release), and EVERY release results in the same script that messes up the sequence number.

Now I'm pretty sure there is something not quite right in our db (at least this one, I am not able to reproduce this issue in another), but I don't know exactly what I am looking for. Can you tell me what schema compare is looking at to determine a columns properties, like is it an identity? So we can check that on this db, to see if we have a db issue that needs to be addresses so that schema compare can properly read the table structure?

Eddie D · March 25, 2020 4:21PM

Hi, thank you for your reply.

In the background, the comparison engine reads various Oracle data dictionaries and system table to identify the object, the type of object, how it is created and the dependencies to and from other objects.

From the information you have provided, it looks like the batch_id column was originally created as follows:

CREATE TABLE espsvc_owner."BATCH" (
batch_id NUMBER DEFAULT espsvc_owner."ISEQ$$_23832".nextval NOT NULL,

and its been modified to become

CREATE TABLE espsvc_owner."BATCH" (

batch_id NUMBER GENERATED BY DEFAULT AS IDENTITY,

I will perform a quick test to see if I have reproduce the problem and update this post.

Many Thanks
Eddie

yzguy · March 25, 2020 4:50PM

Not exactly. The table was NEVER created with a default value and was ALWAYS an identity. We can recreate this on one db we have with a new table. We create the table, with identity, then use that script in a schema folder to generate an update script (which should do nothing) and it generates the script it thinks it needs, which is to change it TO an identity, but it already WAS an identity, it always was.

The issue here is that schema compare is not detecting the column correctly when reading from the db. The issue is only reproduce able on one server that we have tried, it works fine on another. There is something different on the one server, but I have no idea what, or what to even look for. That is why I asked what specifically is schema compare looking for to detect if something is an identity column. It would give me a place to start looking.

Eddie D · March 25, 2020 5:16PM

I can reproduce the problem.
After deploying the change to the target database, on attempting to add a new row of data to the target table, experience the unique constraint violation error.

I was able to resolve the problem, disabling the Ignore sequence current value option, Edit project ->Options tab ->Ignore section ->12th Option ensured that sequence current value was unchecked or turned off.

Repeated the previous steps. Ensure that the sequence associated with the table was included in the deployment.

When I next attempted to insert a new row into the target table, the insert was successful and committed.

Many thanks
Eddie

yzguy · March 25, 2020 5:21PM

Not exactly, you did reproduce something similar, but not what we did.
You created a sequence and set it as a default value for a column, that is NOT what we did.
We created a table with an identity column, and the SYSTEM created the sequence.
This is NOT our issue.

Our issue is trying to resolve why schema compare is not properly identifying an identity column in our db, as an identity. It tries to MAKE it an identity on EVERY release, even though there should be no change.

There is likely a db issue, or oracle issue, but something is preventing schema compare from properly identifying this column as an identity in the db, so it repeatedly tries to make it an identity, every single time.

yzguy · March 25, 2020 9:32PM

let me see if I can simplify this. We start by running this statement on our db (drop the table, nothing existing):

CREATE TABLE BATCH (

batch_id NUMBER GENERATED BY DEFAULT AS IDENTITY,

coor_id VARCHAR2(50 BYTE) NOT NULL,

stts VARCHAR2(30 BYTE),

batch_dt DATE,

crt_nm VARCHAR2(256 BYTE) DEFAULT USER,

crt_dtm TIMESTAMP DEFAULT SYSTIMESTAMP,

last_udt_usr_nm VARCHAR2(256 BYTE) DEFAULT USER,

last_udt_dtm TIMESTAMP DEFAULT SYSTIMESTAMP

);

then we SAVE that script in a script location, then use schema compare to compare the script location to our db, and it "sees" a difference between the script location and the db table (even though there is not any). Schema compare thinks it needs to make the db table an identity, when in fact it ALREADY is. The problem here is schema compares ability to determine that the column is an identity is not working, on this db.

This works fine on another db, and I suspect will work fine for you. I suspect there is something wrong with one of our db's (or rights missing, or something different) as this works fine on another. My problem is that I don't know what schema compare is looking at, so I don't know where to start looking for problems.

initial value reset when modifying tables with identity columns?

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk