MSA Replication behaving differently whilst replicating "select into" statement

[former_member776122]

Hi

I have setup a MSA replication having version 15.7.1/SP121 and also enabled statement level replication to replicate "Select INTO" statement.

but I am getting some weird behavior on replicated repserevr error log when running simple select into statement on source database.

Well, here is the story line,  I have created dbrep using the clauses

replicate DDL

replicate "S"  ---- that's for to replicate 'select int'  and

not replicate system procedures

and corresponding subscription created as well using a clause "subscribe to truncate table"

, added sp_setrepdbrefmode <db_name>,"S","on"

Everything working fine all DML/DDLs statements replicating to target but whilst replicating the "select into" DSI thread is going down with below error message but in parallel repserver replicates the select into table name on target side.

Error Message

==========

T. 2016/05/04 12:50:18. (225): 'if @@error <> 0 rollback transaction'

T. 2016/05/04 12:50:18. (225): Command(s) to 'S*****01.i*****v':

T. 2016/05/04 12:50:18. (225): ' commit transaction'

T. 2016/05/04 12:50:18. (225): Command(s) to 'S*****01.i*****v':

T. 2016/05/04 12:50:18. (225): 'select ae_id=ae_id , ae_code=ae_code , ae_name=ae_name into dbo.hts_ae_codes_HS from dbo.hts_ae_codes '

E. 2016/05/04 12:50:18. ERROR #1028 DSI EXEC(1257(1) S*****01.i*****v) - dsiqmint.c(4719)

  Message from server: Message: 911, State 2, Severity 11 -- 'Attempt to locate entry in sysdatabases for database '

Further when resume the DSI it gives the error message

E. 2016/05/04 12:49:44. ERROR #5152 DSI(1257 'S*****01.i*****v') - dsisched.c(4304)

  There is a system transaction whose state is not known. DSI will be shutdown.

further on resume the DSI with exec tran getting error message

E. 2016/05/04 12:50:18. ERROR #1028 DSI EXEC(1257(1) S*****01.i*****v) - dsiqmint.c(4719)

  Message from server: Message: 2714, State 1, Severity 16 -- 'There is already an object named 'hts_ae_codes_HS' in the database.

I have tried to dump the O/B queue enabled DSI trace flags but not able to find any duplicate statement which is creating the select into table in background

I have tried to enable "full logging for select into" but that's also not working.

Any one please suggest if am skipping something or other way to replicate "select into"

- Hardeep

[former_member776122]

Hi Mark

There is only one target where "select into" replicates.

And thats true Repserver replicates the "select into" statement to the RDB but along with that it  goes down with error message

I run only one statement on PDB

i.e. select * into hts_ae_codes_HS from hts_ae_codes

and replication able to create this table hts_ae_codes_HS on RDB and subsequently DSI goes down with below error message, this behavior i am unable to understand why repserver is not performing rollback if "select into" statement is having issues

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

T. 2016/05/04 12:47:39. (221): 'begin transaction  '

T. 2016/05/04 12:47:39. (221): Command(s) to 'S****D01.img_hk_dev':

T. 2016/05/04 12:47:39. (221): 'execute rs_update_lastcommit @origin = 1259, @origin_qid = 0x00010000c7d5efc501a6f4c7002001a6f4c600450000a5fc00d2b8680000000000000001, @secondary_qid = 0x000000000000000000000000000000000000000000000000000000000000000000000000, @origin_time = '20160504 12:47:12:560', @conn_id = 0'

T. 2016/05/04 12:47:39. (221): Command(s) to 'S****D01.img_hk_dev':

T. 2016/05/04 12:47:39. (221): 'if @@error <> 0 rollback transaction'

T. 2016/05/04 12:47:39. (221): Command(s) to 'S****D01.img_hk_dev':

T. 2016/05/04 12:47:39. (221): ' commit transaction'

T. 2016/05/04 12:48:04. (221): Command(s) to 'S****D01.img_hk_dev':

T. 2016/05/04 12:48:04. (221): 'begin transaction  '

T. 2016/05/04 12:48:04. (221): Command(s) to 'S****D01.img_hk_dev':

T. 2016/05/04 12:48:04. (221): 'execute rs_update_lastcommit @origin = 1259, @origin_qid = 0x00010000c7d5f00901a6f4c8003601a6f4c800300000a5fc00d2f42c0000000000000001, @secondary_qid = 0x00010000c7d5f00901a6f4c8003601a6f4c800300000a5fc00d2f42c0000000000000001, @origin_time = '20160504 12:48:03:560', @conn_id = 0'

T. 2016/05/04 12:48:04. (221): Command(s) to 'S****D01.img_hk_dev':

T. 2016/05/04 12:48:04. (221): 'if @@error <> 0 rollback transaction'

T. 2016/05/04 12:48:04. (221): Command(s) to 'S****D01.img_hk_dev':

T. 2016/05/04 12:48:04. (221): ' commit transaction'

T. 2016/05/04 12:48:04. (221): Command(s) to 'S****D01.img_hk_dev':

T. 2016/05/04 12:48:04. (221): 'select ae_id=ae_id , ae_code=ae_code , ae_name=ae_name into dbo.hts_ae_codes_HS from dbo.hts_ae_codes '

E. 2016/05/04 12:48:04. ERROR #1028 DSI EXEC(1257(1) S****D01.img_hk_dev) - dsiqmint.c(4719)

  Message from server: Message: 911, State 2, Severity 11 -- 'Attempt to locate entry in sysdatabases for database '

Invalid pointer param number 2, pointer value 0x0x100000018

' by name failed - no entry found under that name. Make sure that name is entered properly.'.

H. 2016/05/04 12:48:04. THREAD FATAL ERROR #5049 DSI EXEC(1257(1) S****D01.img_hk_dev) - dsiqmint.c(4732)

  The DSI thread for database 'S****D01.img_hk_dev' is being shutdown. DSI received data server error #911 which is mapped to STOP_REPLICATION. See logged data server errors for more information. The data server error was caused by output command #0 mapped from input command #0 of the failed transaction.

I. 2016/05/04 12:48:04. The DSI thread for database 'S****D01.img_hk_dev' is shutdown.

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

[Mark_A_Parsons]

A 'select/into' is actually a DDL command (create table) combined with a DML command (insert). ("Duh, Mark!" ?)

And since a 'select/into' cannot be run from within a transaction, I'd also expect the DSI to issue the 'select/into' outside of a transaction (ie, there's nothing to rollback).

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

As for the error message ... it seems to think that the database name is:

     "Invalid pointer param number 2, pointer value 0x0x100000018"

Obviously that's not a valid database name, but it does look like part of an ASE error message.  So there are 2 issues we can see from this invalid database name ...

1 - the 'select/into' is generating an error message, most likely during the DML part of the operation (ie, the DDL - create table - portion *is* creating the table)

2 - and there's a syntax/structure error in how the ASE error message is being processed by the DSI

I'd follow-up with the following:

1 - try to manually run the 'select/into' in the RDB (preferably using the same login used by the DSI) and see what, if any, error messages are generated; objective being to troubleshoot any resulting error messages [Hopefully an error will be generated thus highlighting an issue with your RDB ... and thus the error message being received by the DSI; this would also be nice to see (an error) since you seem to have indicated in your original post that the (same?) 'select/into' is being successfully replicated elsewhere]

2 - open a case with tech support to see if there are any known issues with the way the DSI and RDS are communicating (related to 'select/into' errors) for your particular RS/ASE version combination;

[former_member776122]

Thanks Again Mark

if "select into" is a DDL command then it should replicate with dbrepdef having clause replicate DDL which not happening even after turn on the "full logging select into" dboption,

As it wasn't hence i altered my dbrepdef with "replicate 'S' and land upwith this situation.

I could successfully run the select into command on RDB using db_maint account

i.e. select * into hts_ae_codes_HS from hts_ae_codes

but replication server executes the command as below and failing to looking for database name viz 'dbo'

select * into dbo.hts_ae_codes_HS from dbo.hts_ae_codes

1> select * into dbo.hts_ae_codes_HS from dbo.hts_ae_codes

2> go

Msg 911, Level 11, State 2

Server 'S****D01', Line 1

Attempt to locate entry in sysdatabases for database '' by name failed - no entry found under that name. Make sure that name is entered properly.

Msg 911, Level 11, State 2

Server 'S****D01', Line 1

Attempt to locate entry in sysdatabases for database '' by name failed - no entry found under that name. Make sure that name is entered properly.

And similar error message am getting in repserver logs

Moreover pointers error message are related to select statement when you run it using table columns

1> select ae_id=ae_id , ae_code=ae_code , ae_name=ae_name into dbo.hts_ae_codes_HS from dbo.hts_ae_codes

2> go

Msg 911, Level 11, State 2

Server 'S****D01', Line 1

Attempt to locate entry in sysdatabases for database '

Invalid pointer param number 2, pointer value 0x0x100000018

' by name failed - no entry found under that name. Make sure that name is entered properly.

Msg 911, Level 11, State 2

Server 'S****D01', Line 1

Attempt to locate entry in sysdatabases for database '

Invalid pointer param number 2, pointer value 0x0x100000018

' by name failed - no entry found under that name. Make sure that name is entered properly.

Msg 911, Level 11, State 2

Server 'S****D01', Line 1

Attempt to locate entry in sysdatabases for database '

Invalid pointer param number 2, pointer value 0x0x100000018

' by name failed - no entry found under that name. Make sure that name is entered properly.

Msg 911, Level 11, State 2

Server 'S****D01', Line 1

Attempt to locate entry in sysdatabases for database '' by name failed - no entry found under that name. Make sure that name is entered properly.

Msg 911, Level 11, State 2

Server 'S****D01', Line 1

Attempt to locate entry in sysdatabases for database '' by name failed - no entry found under that name. Make sure that name is entered properly.

[Mark_A_Parsons]

While I've used SQL statement replication in the past, I've limited my usage to strictly DML statements (ie, insert, delete, update).  I don't recall ever wanting/trying to replicate 'select/into' so fwiw ...

Since the SQL generated by the DSI, when manually executed by you in the RDB, is generating errors, I'd want to open a case with tech support to see if a) this is a bug with Repserver or b) you're missing a configuration setting that's causing the DSI to generate an invalid command.

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

Still curious as to whether or not you've got the 'select/into' working for a different RDB (per your original post).  If you *do* have 'select/into' being replicated into a different RDB then I'd want to start comparing RS/ASE versions and configurations to see where there are differences that might explain the problematic 'select/into' replication.

[former_member776122]

I believe there is some misunderstanding, I have 1o1 replication and "select into" is working on RDB BUT next moment DSI goes down with error message as pasted in original post right above. Here below the dump of OB 100          19          0 begin transaction                                                                                                                1257          0      30827          36          1        352        1259 Jan  1 1900 12:00AM 0x00010000c7d6074001a6f50c003201a6f50c002d0000a5fd00053fa00000000000000002 NULL                          NULL                          0x000000000000000000000000000000000000000000000000000000000000786b00240001  1142949888 0x00010000c7d60740002d53414153484b443036696d675f686b5f646576000004eb000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000                  0        1100        258          0 exec rs_sqldml @rs_operator='S', @rs_object='hts_ae_codes_HS', @rs_owner='dbo', @rs_status=58, @rs_set=NULL , @rs_where=NULL , @rs_select='ae_id=ae_id , ae_code=ae_code , ae_name=ae_name', @rs_from='dbo.hts_ae_codes', @rs_insert_column=NULL , @rs_rowcount         1257          0      30827          36          1        352        1259 Jan  1 1900 12:00AM 0x00010000c7d6074001a6f50c003201a6f50c002d0000a5fd00053fa00000000000000002 NULL                          NULL                          0x000000000000000000000000000000000000000000000000000000000000786b00240001  1142949888 0x00010000c7d60740002d53414153484b443036696d675f686b5f646576000004eb000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000                  0        1100        258          1 =2                                                                                                                                1257          0      30827          36          2        200        1259 May  5 2016 12:19AM 0x00010000c7d6074001a6f50c003201a6f50c002d0000a5fd00053fa00000000000000003 NULL                          NULL                          0x000000000000000000000000000000000000000000000000000000000000786b00240002    67108865 0x00010000c7d60740002d53414153484b443036696d675f686b5f646576000004eb000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000                  0        1100          20          0 commit transaction

[Mark_A_Parsons]

From a syntax perspective I'm not generating any errors for the following select/into commands when run under ASE 15.7 SP135:

======================

1> use tempdb

2> go

1> select id=id,name=name,crdate=crdate into t1 from sysobjects

2> go

(32 rows affected)

1> select id=id,name=name,crdate=crdate into dbo.t2 from dbo.sysobjects

2> go

(33 rows affected)

1> select * into dbo.t3 from dbo.sysobjects

2> go

(34 rows affected)

======================

So I'm wondering if there's an issue with some aspect of the hts_ae_codes table ... proxy table? view? column-level encryption enabled? any computed columns and/or indexes?  What's the complete output from 'sp_help hts_ae_codes'?

[Mark_A_Parsons]

So you're saying the target table is created in the RDB and it's populated with all the expected rows?

[former_member776122]

Now seems like its table issues this table have some userdefined datatypes

I am also using ASE version -15.7/EBF 24754 SMP SP135

here below is the table details

Name         Owner Object_type Object_status        Create_date

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

hts_ae_codes dbo   user table  keep first text page May  3 2016  9:40PM

(1 row affected)

Column_name Type        Length Prec Scale Nulls Not_compressed Default_name Rule_name Access_Rule_name Computed_Column_object Identity

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

ae_id       id_t             4 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0

ae_code     shrt_name_t      4 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0

ae_name     comment_t       21 NULL  NULL     0              0 NULL         NULL      NULL             NULL                            0

Object does not have any indexes.

No defined keys for this object.

name         type       partition_type partitions partition_keys

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

hts_ae_codes base table roundrobin              1 NULL

partition_name          partition_id compression_level pages row_count segment create_date

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

hts_ae_codes_1488825435   1488825435 none                  1         2 default May  3 2016  9:40PM

Partition_Conditions

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

NULL

Avg_pages   Max_pages   Min_pages   Ratio(Max/Avg)       Ratio(Min/Avg)

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

           1           1           1             1.000000             1.000000

Table LOB compression level 0

Lock scheme Datarows

The 'ascinserts' attribute is not applicable to tables with datarow or datapage lock schemes.

exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ascinserts

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

            0              0          0                 0            0           0

(1 row affected)

concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg

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

                        15                     0                   0

(return status = 0)

1> sp_help id_t

2> go

Type_name Storage_type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Identity

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

id_t      int               4 NULL  NULL     0 NULL         NULL      NULL                      0

(1 row affected, return status = 0)

1> sp_help shrt_name_t

2> go

Type_name   Storage_type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Identity

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

shrt_name_t varchar           4 NULL  NULL     0 NULL         NULL      NULL                      0

(1 row affected, return status = 0)

1> sp_help comment_t

2> go

Type_name Storage_type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Identity

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

comment_t varchar          21 NULL  NULL     0 NULL         NULL      NULL                      0

(1 row affected, return status = 0)

[former_member776122]

Yes, "select into" table is being created along with DSI thread down.

Though i run the "select into" manually ... here below.

[54] 1> select * from hts_ae_codes_HS

[54] 2> go

Msg 208, Level 16, State 1

Server 'S****D01', Line 1

hts_ae_codes_HS not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

[55] 1> select * into dbo.hts_ae_codes_HS from dbo.hts_ae_codes

[55] 2> go

Msg 911, Level 11, State 2

Server 'S****D01', Line 1

Attempt to locate entry in sysdatabases for database '' by name failed - no entry found under that name. Make sure that name is entered properly.

Msg 911, Level 11, State 2

Server 'S****D01', Line 1

Attempt to locate entry in sysdatabases for database '' by name failed - no entry found under that name. Make sure that name is entered properly.

Msg 911, Level 11, State 2

Server 'S****D01', Line 1

Attempt to locate entry in sysdatabases for database '' by name failed - no entry found under that name. Make sure that name is entered properly.

Msg 911, Level 11, State 2

Server 'S****D01', Line 1

Attempt to locate entry in sysdatabases for database '' by name failed - no entry found under that name. Make sure that name is entered properly.

Msg 911, Level 11, State 2

Server 'S****D01', Line 1

Attempt to locate entry in sysdatabases for database '' by name failed - no entry found under that name. Make sure that name is entered properly.

Msg 911, Level 11, State 2

Server 'S****D01', Line 1

Attempt to locate entry in sysdatabases for database '' by name failed - no entry found under that name. Make sure that name is entered properly.

(2 rows affected)

[56] 1> select * from hts_ae_codes_HS

[56] 2> go

ae_id       ae_code ae_name

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

           0 A       American

           1 E       European

(2 rows affected)

[57] 1>

[Mark_A_Parsons]

I created the same 3 user-defined datatypes and then created 2 tables ... first time with the same user-defined datatypes, second time with the system data types:

=================

A - create table hts_ae_codes (ae_id id_t not null, ae_code shrt_name_t not null, ae_name comment_t not null) lock datarows

B - create table hts_ae_codes_2 (ae_id int not null, ae_code varchar(4) not null, ae_name varchar(21) not null) lock datarows

-- for both tables I created 2 rows:

insert hts_ae_codes values (1,'name1','comment1')

insert hts_ae_codes values (2,'name2','comment2')

=================

For both tables I ran a series of 4 select/into queries representing the 4 combinations of including/excluding the 'dbo.' prefix for the source and target tables:

=================

1 - select ae_id=ae_id , ae_code=ae_code , ae_name=ae_name into hts_ae_codes_HS from hts_ae_codes

2 - select ae_id=ae_id , ae_code=ae_code , ae_name=ae_name into dbo.hts_ae_codes_HS from dbo.hts_ae_codes

3 - select ae_id=ae_id , ae_code=ae_code , ae_name=ae_name into hts_ae_codes_HS from dbo.hts_ae_codes

4 - select ae_id=ae_id , ae_code=ae_code , ae_name=ae_name into dbo.hts_ae_codes_HS from hts_ae_codes

=================

Test results:

A.1 - table created/populated; no errors

A.2 - table created/populated; 'Msg 911' errors generated

A.3 - table created/populated; no errors

A.4 - table created/populated; 'Msg 911' errors generated

B.1 - table created/populated; no errors

B.2 - table created/populated; no errors

B.3 - table created/populated; no errors

B.4 - table created/populated; no errors

All tests successfully created and populated the table; the 2x tests that generated the 'Msg 911' errors were those where the target table included the 'dbo.' prefix and user-defined datatypes were involved.

NOTE: I got the same test results in ASE 15.0.3 ESD 4.1, and ASE 15.7 SP135.

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

At this point it looks like the DSI is submitting a valid SQL statement but there's a bug in ASE when a) the target of the 'select/into' includes the 'dbo.' prefix (just 'dbo' or any user?) and b) user-defined datatypes are involved.

Possible workarounds ...

- don't use user-defined datatypes in conjunction with replicated 'select/into' commands

- configure the DSI connection to ignore 'Msg 911' (could be problematic since 911 is a nasty error that could occur under other circumstances ... circumstances you really wouldn't want to ignore)

- just resume/skip these transactions and hope they don't happen very often

- add logic to your monitoring script to automatically issue a 'resume/skip' when this combo occurs: select/into + user-defined datatypes => generates 'Msg 911' errors

[former_member776122]

Thanks Mark

We hit a bug

See SAP reply

================

Hi Hardeep, This is a known bug, CR 572789, which is not yet fixed in any version.
The listed workaround is to remove the owner name prefix from
the table in the query.  I've published KBA 2314036 for this CR,
you can subscribe to the KBA to be automatically notified when it
is fixed - though I currently don't expect that to be anytime soon.
If the workaround isn't sufficient for your needs, let me know
and I can try to increase the priority of the CR in engineering. Cheers,
Bret Halford
SAP Product Support

==============================================

Now, thing is how to remove owner name from RepServer transactions  where MSA do replicates with owner name only.

-Hardeep