|
We are using mobilink for synchronizing a cons. against several remotes. dbmlsync on each remote is called using 'uid=syncusr' mlsrv10 on cons. is called using 'uid=mlsync' for the purpose of anlaysing some bug I need to trace the transactions of each user. question: are these expressions correct? 1- executing 'dbtran -u syncusr' on remote delivers only the transactions coming from cons. (over mobilink)? 2- executing 'dbtran -x mlsync' on cons. delivers all trnsactions except those coming from remotes (over mobilink)? |
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- 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:
question asked: 30 Apr '18, 09:05
question was seen: 1,261 times
last updated: 07 May '18, 12:54
for info, no any other application/job use these credentials (mlsync, syncusr) for connecting to DBs
Is that a different question from that one?
On the remotes: Is the uid "syncusr" only used with dbmlsync, or is it also used for normal connections?
It is the same question, but I need just a confirmation for the above 2 expressions, since I am suspecting that something is going wrong with the trnsaction logs (in terms of allocating transactions to users). I have a feeling that some operation on remote is carried out by our application using 'DBA', nevertheless it is logged in the transaction logs in the name of 'syncusr' (dbmlsync).
Do you think that any combination could lead to such mistake (concurrency problem)? or such a scenario can/has never took place?
Do you happen to use a SETUSER statement, so one user may impersonate another one? (Note, I'm not sure how that will show up in a translated log with DBTRAN -g...)
Furthermore, do you use hook procedures with DBMLSYNC? If so, their actions will also be included with your first Dbtran log.
I never use the SETUSER statement. Also never use the hook procedures. I can upload some snapshots for how like are looking the transaction logs.
The problem is relating a table TABLE1, which has standard mobilink scripts (download_cursor, upload_update, upload_insert, upload_delete, download_delete_cursor)(created with the wizard). The first column of TABLE1 is an abbreviation to the name of the DB, and usually each DB edits its own rows (but it happens also rarely that rem1 edits or adds row/s of rem2). Our application executes several UPDATE statements on an existing row in TABLE1, and sometimes it executes (DELETE + INSERT) instead of UPDATE, and this happens several hunderds of times per day (simultaneously there are many other connections to this DB like dbmlsync and others).
The UPDATE statements are always without problem synchronized to cons, and also the (DELETE + INSERT) statements are w/o problem, because if there is 30x(DELETE + INSERT) (for the same Primary Key) executed on rem1, then the mobilink considers only the first DELETE and the last INSERT. The problem arises once per several weeks, so the upload_insert on cons. detects a Primary Key violation!! In the transaction log files of rem1 I can read that there are (29xDELETE+30xINSERT) statements (for some existing row with same PK) carried out by the user of our application and 1xDELETE statement (for the same PK) carried out by 'syncusr', and this is the main reason for the primary key violation on cons, because the rem1 thinks that the first DELETE statement is coming from cons. and so that it neglects this DELETE statement during the synchronization to cons.
I have analyzed the transaction logs on cons., and there is no such DELETE statement executed locally, so i remain with the mysterious question WHY IS THIS SINGLE 'DELETE' STATEMENT EXECUTED ON REM1 IN THE NAME OF 'syncusr'???
Is the DELETE/INSERT that you execute instead of the UPDATE on the remote database in the same transaction, or is it two separate transactions?
each set of DELETE/INSERT happens in one single connection in a period of 10 Minutes. So each 10 minutes a new connection (user=DBA) is established and this set of statements is executed (i.e. each set of DELETE/INSERT has its own connection-id). The dbmlsync connects also automatically in period of 3 minutes (user=syncusr).
One more question, is it possible to find an allocation between synchronized transactions on cons. and remote DBs? In other words, I can read in the transaction logs of cons. that some transactions are coming from remotes (user='mlsync' which is the user of mlsrv10 in our case), but, is there any means to tell whether this particular transaction is sent from remote1 or remote2 or ...?
That doesn't answer Reg's question... What about transactions, i.e. is there a COMMIT between the DELETE and the INSERT? And given the more frequent sync, could it be a sync is done when the DELETE has taken place but the INSERT has not yet? If so, it might also be an issue of isolation level, i.e. if the DELETE is not yet committed...
Yes, there is always COMMIT between DELETE and INSERT
Still unanswered:
I guess the ML Server verbose log should help to clarify that...
Yes, actually it looks like that the problem occurs when the sync is executed between INSERT and DELETE
Volker is correct, the ML Server verbose log is your best bet here.
The transaction log will have no information about which MobiLink user is synchronizing on the connection from the Mobilink Server unless YOUR synchronization scripts do something to identify which user is synchronizing. For example, a begin_synchronization event that does the following would allow you to see which user is synchronizing in the transaction log:
INSERT INTO sync_status VALUES ( 'begin_sync', {ml s.username} )
Clearly, you would need a sync_status table for this to work.
I have the verbose log of ML Server, but as a said, all transactions coming from remotes are executed in the name of mlsrv10, and there is no any indicator whether it is from remote1 or remote2
Just as a general remark:
As unique PKs play a very important role in a distributed database system like a SQL Remote or MobiLink setup, deleting and re-inserting a row with the same PK (as you describe) is usually not a good idea IMVHO, especially if a sync happens inbetween a DELETE and INSERT (otherwise it would automatically be treated as an UPDATE of the row by ML, which might not lead to the problem you are trying to trace...).
Yes, I'm aware that this sounds like a "know-it-all" response.
even I use SETUSER to the user of dbmlsync by mistake, then this user does not have permission to delete this row. I tried to do it manually, and got a permission error that this user is not allowed to edit TABLE1
A verbose ML log can give you the information that you are trying to isolate. Here is a sample -v+ output showing an upload that is inserting into a table t. Please note that the log entry type/time has been removed and each new log line starts with <1> which is the sync id for a given request on the server.
Snipet of sync request <1>
<1> Request from "Dbmlsync 17.0.9.4793" for: remote ID: 8c1bfbb9-8bbf-4390-947a-c7618840d1af, user name: u, version: v <1> upload_insert t <1> insert into dbschema.t ( uuid, create_user, modify_user, c ) values ( {ml r.uuid}, {ml r.create_user}, {ml r.modify_user}, {ml r.c} ) <1> Translated SQL: <1> insert into dbschema.t ( uuid, create_user, modify_user, c ) values ( ?, ?, ?, ? ) <1> Insert row [t]: <1> 4894c1ba-248f-4334-93fc-16be15a2ed9a <1> dba <1> dba <1> 4From this, we can see that the request was from a dbmlsync client running 17.0.9.4793. The remote ID uniquely identifies the specific remote for the sync request. You can get the remote ID for a remote using the ml_remote_id database option. The log identifes the MobiLink user and script version and shows a uploaded insert into table dbschema.t and the values for the inserted row.