TEXT column not replicating in RS15.7.1/SP120

[former_member776122]

Hi

After upgrade from RS15.1 to RS1571/SP120, one of  text column of a table is not replicating where prior to upgrade it was fine.

Here are some bullet points to understand the environment.

- Primary Server is on RS15.1 version, Replicated Server upgraded to 1571.

- Repdef is having a searchable column name  "origin_server"

- Subscription is using a where clause for "origin_server" column

- Primary/replicate table is having all three triggers.

- Searchable columns is not the part of primary key

- Data is being inserted using a VIEW defined with text column viz. xml_data but "origin_server" column name is not defined in the view.

- Insert trigger is filling up the "origin_server" value using @@servername

Issue:

----------

1. When inserting the data using view => Text column is NOT replicating to target table and comes as NULL value, but rest all the columns data are populating perfectly.

2. When inserting data directly to the table using same set of values as in View=> Again getting same result as above.

3. When inserting the data directly to the table including column "origin_server" to the insert SQL ==> Text data is replicating properly as replication was working prior to upgrade.

Action Performed:

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

Outbound queue of replicated database reflects the text data values but tracing output is not showing any text data update command (like writetext / readtext ) in first two cases.

I had observed the DSIE rs_counters for replicate RS () like 57013(ExecsWritetext) , 57011(UpdatesRead) etc but counters value are also moving & suggest RS processing text column.

Could you please help me to understand this RS15.7 behavior?

-Hardeep

[former_member182259]

Is the text column marked 'always_replicate' or replicate_if_changed?? 

What is the repdef setting (if any exists)???     Is autocorrection or dsi_command_convert on for this table??

Is the trigger on the table or is an instead of trigger on the view???

I will assume it is a trigger on the table and NOT an instead of trigger on the view.   So what we would have is:

Begin tran

insert into table/view (origin_server=null, texcol=some long string)

exec trigger

update row set origin_server=@@servername

end exec

commit tran

When you insert into the view or base table - without the origin_server - the text column is inserted into the table and replicated as far as RS - but then when it hits the DIST without the origin_server field set, it tosses the row away.

Now then when it hits the row for the update, then it sends the after image of the row - not sure why it goes as insert - that is why the question wrt autocorrection or dsi_command_convert.....

What I think you want is an instead of trigger on the view - you may also want to set a column default of @@servername for origin_server (or better yet, change it to a materialized computed column)

If autocorrection is on or dsi_command_convert is set to u2di, then you need to mark text columns as 'always_replicate'.   Without this, you would get missing text data because any update (such as the triggered origin_server modification would delete the existing row and reinsert the new row....but the after row image would not have any text in it if the marking was replicate_if_changed.    Consequently, it would look like the row/text was there - but then the delete would remove it and you would have a null text field.   That *could* explain why the counters are moving but the text is missing.

You may also want to open a message and add your company to list for CR's 684820/ 684821 - which allows repdefs to include system variables (such as rs_origin_server) and allow subscriptions on them.

[former_member776122]

Text column marked 'always replicate'

dsi_command_convert is none and No customized table-level configuration for any table

Autocorrection is also OFF

All triggers are defined on the table itself

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

View is not having origin_server column name so  the  insert SQL is format is like

Begin tran insert into view ( Col1, Col2, Col3 , texcol=some long string)

exec trigger update row set origin_server=@@servername

end exec

commit tran 

-> Above SQL is not replicating TXT column

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

Similarly , if inserting directly to the table like

Begin tran insert into table ( Col1, Col2, Col3 , texcol=some long string)

exec trigger update row set origin_server=@@servername

end exec

commit tran 

-> Again above SQL is not replicating TXT column

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

But when explicitly passing origin_server  to the

insert SQL like Begin tran insert into table ( Col1, Col2, Col3 , texcol=some long string, origin_server=@@servername)

exec trigger update row set origin_server=@@servername

end exec commit tran 

-> TXT column do replicate for above insert

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

Is there is any change to RS1571/Sp120 text processing or change in dsi_isolation_level because similar replication was working perfectly between RS15.1 to RS15.1 but won’t from RS151 to RS157

Any thoughts?

Well, am able to see the TXT column update statement in replicated OutBound Q means DIST is able to process the TXT column and could be an issue or some changes has been made to DSIE module of RS1571  which required further attention to fix the issue.

I would like to mention target server is only upgraded to RS157 that receives data probably from 4 other host repservers and similar behaviour has been found for other replication channels too.

Replication setup something like

RS1 (151)  ---------------> RS_Main (RS15.7)

RS2 (151)  ---------------> RS_Main (RS15.7)

RS2 (151)  ---------------> RS_Main (RS15.7)

RS4 (151)  ---------------> RS_Main (RS15.7)

[former_member182259]

Did you check the repdef settings???

You might also want to turn on the RepAgent traces to see if the text is being sent to RS to start with - then we can focus on whether it is subscription resolution issue or a RepAgent marking problem.  You might need to stop the RepAgent before attempting...

sp_config_rep_agent <db_name>, “trace_log_file”, “<filepathname>”

go

sp_config_rep_agent <db_name>, “traceon”, “9201”

go

-- run insert with view
sp_config_rep_agent <db_name>, “traceoff”, “9204”

go

[former_member776122]

repdef is defined with

with all tables named "table_name"

searchable column

replicate minimal columns only.

I had tried to enable the RS tracing for 9201 suggest TXT is being processed by RA but didnlt try 9204 as of now.

[former_member182259]

ERK!!!  My bad - should be 9201 in both places (LTL tracing).    If you did this before, did the text show up in the trace when inserting into the view???

[former_member776122]

yes able to see the TXT column in tracing output when inserting using view.

[former_member182259]

Was it there twice??

Once for the original insert...  and then it *should* have been there for the update in the trigger as well (and was the update there)?

[former_member182259]

Either way, I think there is a flaw in your implementation logic.   You should either have origin_server at the primary have a default - or ensure that it is a materialized computed column (this will take a bit more setup, so the default may be best).    If the default, in the trigger, check to see if the value is already @@servername and if it is, don't bother with the update.

[former_member776122]

TXT appears only once in tracing and trigger is not inserting any text column to the table.

Also, TXT do appear only once even when inserting using origin_server and TXT columns replicate perfectly

On your second reply:

Why we have encountered this design flaw after upgrade to RS1571 ?

[former_member182259]

That's not right - please post the output of sp_setreptable and sp_setrepcol.    If the text is marked as 'always replicate', then the text should be replicated with EVERY DML change on that table.   So it should be there for both the insert and the triggered update.

Can you also post the output of rs_help on the repdef???    Trying to see if there was a change in the default for text handling - e.g. it may have defaulted to always replicate for RS 15.1 but no may default to replicate if changed....and you may need to alter the repdef to make sure the text col is set to always replicate (this should match the sp_setrepcol value).

[former_member776122]

I wonder why TXT values will be appeared as when triggered update whereas "minimum columns" clause is there.

Do I send you any attached file  to you for these commands. unable to get the attach file link

[former_member776122]

Attached the repdef details

repdef details.txt.zip

[former_member182259]

You can send them directly to jeff.tallman@sap.com.    That is an interesting question about the minimal columns - I had thought of that as a factor, but not from the RepAgent side.   The RepAgent (for ASE) doesn't know about the minimal columns as it doesn't access the RSSD.   Consequently, like I said, the RepAgent *should* send the text data with each DML operation on the table (whether via view, trigger, whatever). 

One aspect about the minimal columns is that perhaps it could strip the text if it compared before/after image they were same, but remember, we don't have a before/after image for text - we only have text datarows.   So, it should affect it either.....but wondered if it was no-op'ing the update and since the original insert didn't have the origin_server, the subscription wouldn't have any effect and therefore no data would be replicated at all....but then you are seeing the datarow - just no text.....

[former_member182259]

Weird, the rs_help output didn't do what I want (show repdef states for replicate_if_changed)....can you do the following from RS:

connect to RSSD

go

select objname, attributes from rs_objects where objname='rates_xml_rdef'

go

select colname, status from rs_columns

     where objid=(select objid from rs_objects where objname='rates_xml_repdef')

         and colname='xml_data'

go

disconnect

go

.....and post the output???  

[former_member776122]

I tried to remove the 'where clause'  from  that subscription  and TXT column start replicating even for inserting using view.

And still my repdef is having searchable column. Bizarre stuff.

[former_member776122]

Least impact workaround, I have defined default for searchable column "origin_server" and TXT column start replicating.

[former_member776122]

SAP identified as bug and raised  KBA#2121720  and CR #777950