SAP SQL Anywhere

Question

I'm in the process of sending a new trigger to a table on our replicating databases.

Environment. SQL Anywhere 12.0.1 Build 3942

The only job of this trigger is to make sure if it gets back a '' blank value in a specific column, to change it to null.

Made change on consolidated database. No issues. Trigger functioned just like it should.

Here is my passthrough...

passthrough only for subscription to pa.intracompany;
CREATE TRIGGER "tua_unhide_company_null" before update order 1 on
pa.companies
referencing old as old_row new as new_row
for each row
when(new_row.com_hide_ind = '' and old_row.com_hide_ind = 'H')
begin
  if current remote user is null then
    --
    --  If the com_hide_ind goes to blank, we need to enforce that it gets set back to null
    set new_row.com_hide_ind=null   
  end if
end
GO
passthrough stop;

In the trigger I'm using the "if current remote user is null then" test, so triggers don't fire triggers. So I'm not hitting a loop.

However, right before I sent this passthrough into the queue, I cleaned up 11 rows in the companies table that had a blank value instead of a null value. I believe I was connected as dba when I did that.

This ran correctly on the consolidated side, and when I ran replication, it ran correctly on that side as well.

However, when it got to creating the trigger on the replication database side, I started getting the following in the log created on the replication side...

I. 2016-08-01 16:57:07. create trigger tua_unhide_company_null before update order 1 on

                        pa.companies

                        referencing old as old_row new as new_row

                        for each row

                        when(new_row.com_hide_ind = '' and old_row.com_hide_ind = 'H')

                        begin

                          if current remote user is null then

                            --

                            --  If the com_hide_ind goes to blank, we need to enforce that it gets set back to null

                            set new_row.com_hide_ind = null

                          end if

                        end
E. 2016-08-01 16:57:07. SQL statement failed: (-210) User 'DBA' has the row in 'companies' locked
E. 2016-08-01 16:57:07. Transaction failed
E. 2016-08-01 16:57:07. Retrying:
E. 2016-08-01 16:57:07. create trigger tua_unhide_company_null before update order 1 on

                        pa.companies

                        referencing old as old_row new as new_row

                        for each row

                        when(new_row.com_hide_ind = '' and old_row.com_hide_ind = 'H')

                        begin

                          if current remote user is null then

                            --

                            --  If the com_hide_ind goes to blank, we need to enforce that it gets set back to null

                            set new_row.com_hide_ind = null

                          end if

                        end
I. 2016-08-01 16:57:08. Applying message from "frunner_pub" (0-01116735527-01116774265-0)
I. 2016-08-01 16:57:08. create trigger tua_unhide_company_null before update order 1 on

                        pa.companies

                        referencing old as old_row new as new_row

                        for each row

                        when(new_row.com_hide_ind = '' and old_row.com_hide_ind = 'H')

                        begin

                          if current remote user is null then

                            --

                            --  If the com_hide_ind goes to blank, we need to enforce that it gets set back to null

                            set new_row.com_hide_ind = null

                          end if

                        end
E. 2016-08-01 16:57:08. SQL statement failed: (-210) User 'DBA' has the row in 'companies' locked
E. 2016-08-01 16:57:08. Transaction failed
E. 2016-08-01 16:57:08. Retrying:
E. 2016-08-01 16:57:08. create trigger tua_unhide_company_null before update order 1 on

                        pa.companies

                        referencing old as old_row new as new_row

                        for each row

                        when(new_row.com_hide_ind = '' and old_row.com_hide_ind = 'H')

                        begin

                          if current remote user is null then

                            --

                            --  If the com_hide_ind goes to blank, we need to enforce that it gets set back to null

                            set new_row.com_hide_ind = null

                          end if

                        end
I. 2016-08-01 16:57:10. Applying message from "frunner_pub" (0-01116735527-01116774265-0)

So now I have this passthrough, along with two other passthroughs stuck in the queue.

Are there any way to tell the databases to ignore anything after a particular offset?

Any suggestions would be greatly appreciated. As you can tell, I followed all the correct rules and ran my passthrough a test machine first.... NOT!!!!

So now I've gummed up the works.

Thoughts on direction anybody would be appreciated!

TIA

Jeff Gibson
Intercept Solutions
Nashville, TN

Answer 1

1

Jeff, I'd initially thought that maybe the clean-up just before the passthrough might be holding the

I can’t see the same problem you’re seeing. I’d initially thought that maybe the deletes on the table in the same session could be holding the lock, but I think the attached repro does the same thing you did to the Child table in my sample, and it run with no issues. Does this repro do the same thing you did? To run the repro, unzip the contents of the ZIP into an EMPTY directory, open a DOS prompt, CD to the directory where the files exist, make sure SQL Anywhere v12 is in your path and then type “rep”.

If you can repro this, I’d love to be able to turn on request level logging for the database engine before SQL Remote runs so we can look back in the log and see who might have acquired a lock on the table in the process. Getting the output from sa_conn_info() when the error occurs would also be handy, but I recognize that would be tricky since you'd need to execute it at the time SQL Remote was runnign and got the error.

Reg

permanent link

answered 02 Aug '16, 14:26

Reg Domaratzki
7.7k●3●43●118
accept rate: 37%

Reg. I fired up sa_conn_info while replication was running. It obviously fails, then keeps trying to apply the trigger. These are the active connections.

17 No Name gibsonj FETCH LastReqTime keeps updating

4 SYNCH DBA GET_OPTION LastReqTime 2016-08-02 16:18:12.751

3 Apply DBA ROLLBACK LastReqTime keeps updating

2 get_remote_option DBA EXEC LastReqTime 2016-08-02 16:18:12.704

1 No Name DBA EXEC LastReqTime 2016-08-02 16:18:12.782

17 was my login when I called the sa_conn_info() procedure.

An additional connection would pop up and go away when the error was trapped that dba had the table locked.

(02 Aug '16, 18:01) Jeff Gibson

Replies hidden

I was hoping to see which connection (likely the "Apply" connection) was blocked on which other connection and then look back in the request level logging to see what the "other connection" had done. It's not obvious to me which connection is doing the blocking from your output, and there's no request level log to look at.

Another way to figure out which connection is doing the blocking would be to call sa_locks() and and look for rows where table_name is 'companies', but again, after finding out the connection number, we're going to need to take that information and go back to the request level log to see exactly what that connection did to hold the lock in question.

Reg

(03 Aug '16, 16:22) Reg Domaratzki

flat view

SAP SQL Anywhere

Need Help with a blocked Passthrough

Follow this question

Related questions