The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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

asked 01 Aug '16, 19:12

Jeff%20Gibson's gravatar image

Jeff Gibson
1.3k274352
accept rate: 22%

1

As to the SQLCODE -210 error, I would think this is a temporary problem, i.e. the CREATE TRIGGER fails because it requires an exclusive lock on the according table, and that lock could not be established due to another connection with locks on the table.

Are other connections active while you run SQL Remote at the remotes? Can you make SQL Remote run when no other connections will use that table?


Note, I'm not sure from your description whether the operation is skipped (so it could be repeated as a CREATE OR REPLACE TRIGGER statement) or whether it prevents all further messages to be applied...

(02 Aug '16, 06:37) Volker Barth
Replies hidden

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.

FWIW, I don't think that's fully true. IMHO, the trigger will not fire repeatedly because the update in the first trigger call assures that the WHEN condition is not true anymore for further trigger calls - that's the reason, not the "if current remote user is null" test... In contrast, the "if current remote user is null" test prevents the trigger's action from being repeated by the SQL Remote message agent.

(02 Aug '16, 06:41) Volker Barth

When we run replication, there are zero connections to the local database on their machine. They have shut down the application (I did this myself). The only connection is coming from the dbremote connection to the database. That's what is throwing me.

Now, I'll add a possibility.

In our options, we have set the login_procedure to pa.pr_login_environment. It is a procedure we use to set a slew of connection level variables within the database. I'm beginning to wonder if a lock is being thrown on the pa.companies table because of selects.

I'm going to temporarily set that option back to call the sp_login_environment system procedure (that we call from within our procedure). If by not setting those keys we may keep from throwing the lock on the table.

I will advise on my results.

Jeff Gibson
Intercept Solutions
Nashville, TN

(02 Aug '16, 12:59) Jeff Gibson

Are there any scheduled events?

I know the message says "row locked" but I keep thinking about those dastardly schema locks obtained by SELECT statements that prevent all sorts of DDL statements.

(02 Aug '16, 14:17) Breck Carter

Negative. Zero scheduled events. Not using them in this environment.

(02 Aug '16, 17:49) Jeff Gibson

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%20Domaratzki's gravatar image

Reg Domaratzki
4.8k33269
accept rate: 41%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×73
×37
×30
×17

question asked: 01 Aug '16, 19:12

question was seen: 155 times

last updated: 03 Aug '16, 16:22