nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Data deploy not updating.. cascade delete not working

Amit_LnT

Hi,
i am running with a issue of update in SQL server 2000.
It works fine for new DCR. But if same DCr is deployed again, it throws a referential integrity error. I tried with, ri-constraint-rule="ON DELETE CASCADE" option for cascade delete. but it still throwed the same error.
Following is hoe my database tag look in dd config file.

user = "$user^"
password = "$psw^"
vendor = "microsoft-inetuna"
timeout = "4"
real-update = "no"
delete-tracker = "yes"
enforce-ri = "yes"
commit-batch-size="1"
schema-helper-cleanup="yes"
>
and following cascade delete entry for child tables.

foreign-key parent-group="ctmContent" ri-constraint-rule="ON DELETE CASCADE"
column-pair parent-column="ContentID" child-column="ContentId"
foreign-key

But got following error.
DBD: ERROR:
java.sql.SQLException: [WNY02KDEV3]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'fkctmContentTaxonomyContentId_ctmContentContendId'. The conflict occurred in database 'TESTDB', table 'ctmContent', column 'ContentId'.
at com.inet.tds.e.a(Unknown Source)
at com.inet.tds.e.a(Unknown Source)
at com.inet.tds.b.new(Unknown Source)
at com.inet.tds.b.executeUpdate(Unknown Source)
at com.interwoven.dd100.dd.TDbSchemaGroupCfg.PerformInsertForColumnArray(TDbSchemaGroupCfg.java:1284)
at com.interwoven.dd100.dd.TDbSchemaGroupCfg.InsertForSingleReplicant(TDbSchemaGroupCfg.java:1523)
at com.interwoven.dd100.dd.TDbSchemaGroupCfg.PerformReplicantInsert(TDbSchemaGroupCfg.java:1339)
at com.interwoven.dd100.dd.TDbSchemaGroupCfg.DoInsert(TDbSchemaGroupCfg.java:1135)
at com.interwoven.dd100.dd.TDbSchemaCfg.InsertWithGroupTree(TDbSchemaCfg.java:456)
at com.interwoven.dd100.dd.TDbSchemaCfg.Insert(TDbSchemaCfg.java:409)
at com.interwoven.dd100.dd.TDbSchemaAgent.BasicWriteTuple(TDbSchemaAgent.java:455)
at com.interwoven.dd100.dd.TDbSchemaAgent.WriteTuple(TDbSchemaAgent.java:334)
at com.interwoven.dd100.dd.TConsumerManager.WriteConsumerInternal(TConsumerManager.java:398)
at com.interwoven.dd100.dd.TConsumerManager.WriteConsumers(TConsumerManager.java:387)
at com.interwoven.dd100.dd.TAgentClient.DoOneTeamSiteSource(TAgentClient.java:974)
at com.interwoven.dd100.dd.TAgentClient.DoTeamSiteSources(TAgentClient.java:474)
at com.interwoven.dd100.dd.TAgentClient.DoOneDeployment(TAgentClient.java:261)
at com.interwoven.dd100.dd.TAgentClient.Go(TAgentClient.java:194)
at com.interwoven.dd100.dd.IWDataDeploy.Go(IWDataDeploy.java:545)
at com.interwoven.dd100.dd.TDASThread.ProcessItem(TDASThread.java:203)
at com.interwoven.dd100.dd.TDASThread.run(TDASThread.java:128)

DBD: ERRORoInsert() failed for group [ctmContentTaxonomylob].
DBD: -- Failed
So i tried to use real-update=yes. But, I read that we can not modify the primary key in that case. And it also throwed the same error.

Finaly, after getting frustrated with all this, I went to execute a store proc to delete the entry from DB before actual insert in DB.
but it too gave following error
DBD: SQLException occured in TDbAgent::ExecuteProcedure():EXECUTE BEGIN
DELETE FROM ctmcontenttaxonomy where ContentId = '888B0096-B30C-465A-AC53-52ECDA8E14E5'
END
DBD: Exception Message: [WNY02KDEV3]Incorrect syntax near the keyword 'BEGIN'.
DBD: Vendor Error Code: 156
DBD: SQL state: 01000
DBD: *******************************************************
DBD:
DBD: *******STACK TRACE*************
DBD: ERROR:
java.sql.SQLException: [WNY02KDEV3]Incorrect syntax near the keyword 'BEGIN'.
at com.inet.tds.e.a(Unknown Source)
at com.inet.tds.g.if(Unknown Source)
at com.inet.tds.g.a(Unknown Source)
at com.inet.tds.g.for(Unknown Source)
at com.inet.tds.b.new(Unknown Source)
at com.inet.tds.b.execute(Unknown Source)
at com.interwoven.dd100.dd.TDbAgent.ExecuteProcedure(TDbAgent.java:3390)
at com.interwoven.dd100.dd.TDbAgent.DoSql(TDbAgent.java:3072)
at com.interwoven.dd100.dd.TCommand.Do(TCommand.java:244)
at com.interwoven.dd100.dd.TAgentClient.Go(TAgentClient.java:147)
at com.interwoven.dd100.dd.IWDataDeploy.Go(IWDataDeploy.java:545)
at com.interwoven.dd100.dd.TDASThread.ProcessItem(TDASThread.java:203)
at com.interwoven.dd100.dd.TDASThread.run(TDASThread.java:128)
DBD: Error occured in TDbAgent::ExecuteProcedure()
DBD: Error occured in area outside TDbAgent consumer (explicit rollback initiated)

following is the entry in dd config file

area="$area^" area-type="ts-filesystem">

user = "$user^"
password = "$psw^"
vendor = "microsoft-inetuna"
table = "dummy_table"
>

BEGIN
DELETE FROM ctmcontenttaxonomy where ContentId = '888B0096-B30C-465A-AC53-52ECDA8E14E5'
END

I am getting mad with this..
if i do not get any solution out of this, i have to go for delete and insert SQL statements for update in perl script, then the questions come why to use DD?
I can drop it and user only sql statement for complete deployment.
or am i missing something ?
help is appreciated....
ENV: TS 6.5 ,OD 6.1.1 on windows server 2003 DB: SQL server 2003

thanks in advance,
amit

Find more posts tagged with

Comments

RajatChopra

Well DD can be very frustrating at times. How i typically start troubleshooting such quirky errors is to copy paste the statements DD is trying to run into Enterprise Manager or SQL Query.
See if they run properly with valid data. and in the sequence DD is trying to do it.

It seems it is trying to insert values into the second table before inserting into the reference table.

Also, can you paste your dbschema sections? Which is your root group?

Amit_LnT

Hi rajat,
thanks for replying..
i have attached the dd config xml...
i found one option in sql server for allowing cascade delete. i checked that too.. dont have any clue.. y its still says.. error for inserting in child table..
as per my understanding.. DD updates the table as we ordered them in config file... so i gave main table, and then child table...
so, it should first delete it from parent table and as cascade delete is on, it should delete it from child table too.. after deletion, it should insert row in same order.. first in parent, then child.. so there should not be any problem for foreign key constraint.. still getting that....
is my understanding correct?

thanks again,
amit

RajatChopra

I cannot see anything wrong with the deployment descriptor. Anyhow, can you try with real-updates='yes'.

According to Admin Guide:
[HTML]
By executing a series of UPDATE SQL statements. That method is referred to as “real updates.”
[/HTML]

Read page 102 of the ddadmin guide, 'Specifying How Tables are Updated'.

Also post your log file as an attachment.

HTH

-Rajat

Amit_LnT

I found few things in the log file which i do not understand.. like a there is a statement in log file
Performing delete+insert
After this log, is shows several select statement from main table, child table, like
SELECTELECT ContentID, SummaryHeadline,Summary,Headline,Body,ExpirationDate,EffectiveDate,CreationDate,TopicID,TypeID FROM CTMCONTENT WHERE ContentID = ?
SELECTELECT CTMCONTENTTAXONOMY.ContentId,CTMCONTENTTAXONOMY.TaxonomyId FROM CTMCONTENTTAXONOMY,CTMCONTENT WHERE CTMCONTENTTAXONOMY.ContentId = CTMCONTENT.ContentID AND CTMCONTENT.ContentID IN (?)
Then it showed, delete statements for child table, but not for parent table.
DELETE: DELETE FROM CTMCONTENTTAXONOMY WHERE ContentId IN ( ?) AND TaxonomyId IN ( ?,?).
After delete statements of all child table, there is a
DeleteForUpdate: skipping root group
Why does it skip the root group ?
then a delete statement for main table with 'and' condition for all table columns.
DELETE FROM CTMCONTENT WHERE ContentID = ? AND SummaryHeadline = ? AND Summary = ? AND Headline = ? AND Body = ? AND ExpirationDate = ? AND EffectiveDate = ? AND CreationDate = ? AND TopicID = ? AND TypeID = ?
and finally insert statement in child table.
INSERT:INSERT INTO CTMCONTENTTAXONOMY(ContentId,TaxonomyId) VALUES (?,?)
i did not find the insert statement for the main table ctmcontent.
I have attached the log file for the detail...

apprecitae ur any help....

thanks in advance,
amit

RajatChopra

Performing delete+insert

This is the reason I was asking you to switch Real-Updates='YES'.

That way it will not DELETE rows.

EDIT -
clean your tables,
move to real-update='yes' ,
deploy all records again,
edit and redeploy records.

HTH

-Rajat

Amit_LnT

well.. i tried that before posting and also after you suggested that..
but i dont think its updating the record. I still see delete and insert statement in the log file. I have attached the log and dd config file...
Am i doing something wrong in the xml?
Not find a single update command in log.
Is there any setting, i have to do to enable the real update in DD.
OR is there any setting in SQL server which i m missing..

feeling helpless...

thanks,
amit

RajatChopra

well.. i tried that before posting and also after you suggested that..
but i dont think its updating the record. I still see delete and insert statement in the log file. I have attached the log and dd config file...
Am i doing something wrong in the xml?

thanks,
amit

I think the way it takes effect is a little tricky. Read that page i suggested. Its not as simple as changing it to 'yes'. It needs to be set to blank;deploy records; clean records; set to 'yes';deploy records.

The extra tables that DD creates might have some information regarding these. I hope you can delete those too when you change these parameters.[IWDELTRACKER, IWIDMAPS, etc]

If not, then try the 'GUIDE' method.

That's what I was able to understand from:
[HTML]
Two attributes in the database element in database deployment configuration files
enable you to specify which kind of update OpenDeploy performs:
--> enforce-ri
--> real-update
If you need to modify such fields, you must clear the value, then save and deploy the
DCR. Then insert the new value, save the DCR, and deploy it. Databases report
constraint violation errors if child tables reference the field values you are deleting.
Therefore, you must also delete the corresponding rows in child tables. To do that
automatically, set the ri-constraint-rule attribute to "ON DELETE CASCADE". Recreate
the rows when you insert the new value for the parent table.

[/HTML]

Amit_LnT

hi rajat..
seems DD dont want to work for me.. did all the cleaning stuff and tried deployment.. but colud you please explain, the process... why is it inserting in the child table before inserting in the main table.. this is how the DD works..?
i did not get information to update the twp param you mentioned.. iwdeleteracker
and did not understand the GUIDE method..

thanks
amit

RajatChopra

Well I dont have a clue as to why it is doing that.

Ultimately you will have to raise an issue with support.

I am certain you have tried few other workarounds, but how about removing the referential integrity constraints from DD script? You have it enforced on the DB I assume.

Remove all foreign key references in the DD script, keep only PK references. Then give it a shot with Real-Updates=yes

Amit_LnT

removing referential integrity it works fine.
But PROBLEM.. it does not enter any row in main table..(ctmcontent)
I have opened a ticket with support. lets hope for some resoultion.

meanitime have developed a perl script to do that.. (delete+insert)..
It does much better than DD at this time...

thanks,
amit

RajatChopra

Are your DCR's IWOV type? Is there a specific reason for that?
How come no content is inserted into the main table?

You should try out the entire deployment with only your Base Table. See if it still doesn't deploy.

IWOV type DCR's are now legacy. You should try doing all new development on XML type.