|
We are using a multi tyre installation in which thre databases A,B,C are synchronising on thre levels, so A is only consolidate, C in only remote and B is remote and consolidate. We have a problem that from time to time get an error on C that there was problem by the server B during upload process. The exact error is:
As hint, we start dbmlsync on B with -X and we set the option of 'delete_old_logs' on 'ON' so that the transaction logs are truncated and deleted, but this is not the reason, because I can see the transaction logs still existing at the time of the error. Is there any experience, hint with this? |
|
The reason for the problem was, that the same synchronisation channel were synchronising 2 tables (TABLE1, TABLE2), where TABLE2 had an after insert trigger which writes the corresponding line in TABLE1 (PK-FK relationship). Our problem was that this trigger was also active in the cons. (but it should be only active on the remote), in other words the same row was tried to be inserted 2X in TABLE1 (once from the trigger in remote and once from the same trigger on cons.). Savepoint it0 was used for TABLE1 and savepoint it3 was used for TABLE2, and at the end we had always problem with savepoint it3. Actually I didnt find any relationship between this explanation and the error message (Savepoint 'it3' not found), but at the end dropping this trigger on the cons. has solved the problem. The Help says this: Savepoints that are established while a trigger or atomic compound statement is executing are automatically released when the atomic operation ends. That statement might be interpreted to say this: "Savepoints that exist when a trigger or atomic compound statement is executed are automatically released when the atomic operation ends." Or, it might mean any savepoint which exists at the point an atomic operation ends is automatically released, simply because a subsequent RELEASE SAVEPOINT might try to partially roll back the changes made by an atomic operation (e.g., a trigger) and that would violate the definition of "atomic".
(15 Sep '20, 09:30)
Breck Carter
Replies hidden
The Help also says this: Savepoints (see Savepoints within transactions) can be used within a procedure or trigger, but a ROLLBACK TO SAVEPOINT statement can never refer to a savepoint before the atomic operation started. Also, all savepoints within an atomic operation are released when the atomic operation completes. Note that triggers are atomic, since the triggering operations (UPDATE etc) are by definition atomic.
(15 Sep '20, 09:49)
Breck Carter
"Atomic compound statements" (aka "BEGIN ATOMIC"...) are made atomic simply by internally using savepoints, as was explained a while ago by Bruce, so the docs may partially relate to these particular kind of stealth savepoints... FWIW, we do use (named) savepoints within transactions, and those are not rolled back by triggers that are executed within the atomic statements (INSERT/UPDATE/DELETE) that are executed within those named savepoints. In contrast, they are rolled back either by their according ROLLBACK TO SAVEPOINT statement or when they are contained in further savepoints tbat are rolled back or when the whole transaction is rolled back. In my experience, they pretty much work as expected.
(15 Sep '20, 10:04)
Volker Barth
As stated below, I'm quite sure both interpretations are not correct. In my understanding, this only relates to savepoints that are created during the atomic operation itself (either because code within a trigger calls SAVEPOINT... or because a BEGIN ATOMIC statement is used). It should not relate to savepoints that have been created before the atomic operation - they should stay unchanged unless they are explicitly released/rolledback within the atomic operation.
(15 Sep '20, 10:11)
Volker Barth
Testing confirms Volker's experience: If a SAVEPOINT is created before a trigger starts executing, a ROLLBACK TO SAVEPOINT will execute successfully if it is executed after the trigger finishes. However, if a SAVEPOINT statement is executed inside a trigger, an attempt to ROLLBACK TO SAVEPOINT outside the trigger, after it finishes, will raise SQLCODE -220 Savepoint 's1' not found. So, it remains a mystery how a trigger can interfere with a savepoint in MobiLink... unless perhaps some piece of MobiLink code that creates a SAVEPOINT is called from inside a trigger.
(15 Sep '20, 15:42)
Breck Carter
|
|
This error has nothing to do with transaction logs. The first thing to check when you get a "Savepoint not found" error is to verify that your synchronization scripts do not execute a COMMIT or an implicit COMMIT. You might be able to sort out from the MobiLink Log where the commit is. Here's an example showing a portion of the MobiLink log where the ML Server was started with -vces verbosity that shows the error : I. 2020-09-03 10:05:43. <1> COMMIT Transaction: Begin synchronization
I. 2020-09-03 10:05:43. <1> System event on synchronization connection:
I. 2020-09-03 10:05:43. <1> { CALL ml_lock_rid( ?, ?, ? ) }
I. 2020-09-03 10:05:43. <1> begin_upload <connection> (no script)
I. 2020-09-03 10:05:43. <1> handle_UploadData <connection> (no script)
I. 2020-09-03 10:05:43. <1> System event on synchronization connection:
I. 2020-09-03 10:05:43. <1> savepoint it0
I. 2020-09-03 10:05:43. <1> upload_insert Admin
I. 2020-09-03 10:05:43. <1> call admin_ui( {ml r.admin_id},{ml r.data} )
I. 2020-09-03 10:05:43. <1> System event on synchronization connection:
I. 2020-09-03 10:05:43. <1> release savepoint it0
E. 2020-09-03 10:05:43. <1> [-10002] Consolidated database server or ODBC error: ODBC: [SAP][ODBC Driver][SQL Anywhere]Savepoint 'it0' not found (ODBC State = HY000, Native error code = -220)
I. 2020-09-03 10:05:43. <1> end_upload <connection> (no script)
I. 2020-09-03 10:05:43. <1> ROLLBACK Transaction: Upload
You can see in this log that we've executed a "savepoint it0" when we start applying the upload stream for table "Admin", but when we try to "release savepoint it0" we throw the error you'll seen. create procedure admin_ui ( in @id bigint, in @data varchar(64) ) begin insert into Admin values (@id,@data,DEFAULT); commit; end; Hope that helps, Reg Thanks for the hint, yes, it sounds very logic Do you think that it3 is a savepoint written from us, or it comes from some standard procedures (written by Sybase)? I tried looking for it3 in the 3 possible locations but found nothing:
Where else should I look for it?
(03 Sep '20, 11:00)
Baron
Replies hidden
2
The savepoint command is executed by the MobiLink Server, as you can see in the log snippet I posted, which indicates that it is a system event (i.e. executed by the MobiLink Server) being executed on the synchronization connection. I. 2020-09-03 10:05:43. <1> System event on synchronization connection: I. 2020-09-03 10:05:43. <1> savepoint it0 I wasn't suggesting you go looking for the source of the savepoint command. I was trying to suggest that you look for the possibility of a COMMIT being executed by a user defined synchronization script between the "savepoint" and "release savepoint" commands that are written to the MobiLink Log when you run with at least -vces verbosity.
(03 Sep '20, 11:13)
Reg Domaratzki
1
In addition to Reg's discussion, many other SQL statements have an "automatic commit" as a side effect; e.g., ALTER TABLE. Any of these could accidentally cause this symptom.
(03 Sep '20, 13:29)
Breck Carter
Replies hidden
True — But would you expect a DDL statement within a ML script?
(03 Sep '20, 14:31)
Volker Barth
2
when someone is working down inside a stored procedure it's easy to forget how it's called... easy to code a TRUNCATE TABLE, for example. Or a LOAD TABLE. Neither is DDL, but both have automatic commit as a side effect. Sometimes it's easy to forget that DDL statements which have absolutely no effect on data, like ALTER SERVER, also cause COMMIT as a side effect. Or ALTER INDEX, since indexes are logically invisible in a relational database.
(03 Sep '20, 14:53)
Breck Carter
The user defined synchronization scripts are created by the wizard, and they are usual (INSERT INTO, UPDATE) Statement, none of them include an explicit (or implicit) commit. I checked the triggers on the synchronising tables, and those are also commit free!! There are another connections connecting to database B while database c is uploading to B, but I consider any possible commits from other connections will not be relevant in this case (so only the commits coming from the same connection of mobilink server could rise this problem) Am I correct?
(04 Sep '20, 02:21)
Baron
1
From the docs:
That's because triggers are executed as part of atomic operations (INSERT, UPDATE, DELETE) and as such cannot close transactions themselves, so by definition your triggers must be "commit-free":) Savepoints are some kind of sub-transactions, so they cannot be started or closed by another transaction, and therefore certainly cannot be started or closed not be another connection.
(04 Sep '20, 03:13)
Volker Barth
Oje, you have right, I dont need to look in the triggers. So the question is, why this error comes? The user defined scripts of mobilink are really commit-free, and they are generated by the wizard
(04 Sep '20, 03:54)
Baron
Well, I can't tell - but have you followed Reg's advice to log the sync via the mentioned flags – in my understanding, that might (or might not) give a clue which script leads to the error...
(04 Sep '20, 04:32)
Volker Barth
Yes I followed, between savepoint it3 and release savepoint it3 there is only one statement being executed from an upload_script of one of our tables. The upload_script in turn is created with the wizard, so it doesn't call another procedure, and I can confirm that itself doesnt cause any comment. One more point, the lowest level in our database hierarchy consists of several databases, so that we have C1, C2...C10 and all synchronise to datbase B at the same time! From the log files, I can distinguish these connections witht he preceeding number, something like:
Could this nesting be a problem? I see the connections 95 has created a savepoint it3 and then 96 has created also a another savepoint it3 and released it before trying 95 to release its savepoint it3
(07 Sep '20, 02:54)
Baron
@Reg Domaratzki what means the number between angle brackets below in Bold?
For my understanding it is a remote identifier, since several remote databases (in our case C1,C2,...C10) can synchronize with the server simultaneously, and this above number is for sure not the connection ID, since there is only one instance of mlsrv10.exe running against cons. databae (in our case database B), so all transactions coming from remote databases come with the same connection ID. Now the question, why several remotes (sharing the same connection ID) use the same savepoint name it3? Isn't this the reason for our problem?
(08 Sep '20, 02:16)
Baron
Is there a way to list all the currently saved SAVEPOINTs in the database? When I count the number of it3s in the log file there are always odd occurrences! 50% occurrences for release savepoint it3 50%+1 occurrences for savepoint it3
(09 Sep '20, 04:55)
Baron
As to the nesting: As stated, savepoints can be nested within transactions. But different connections do use different transactions, so a savepoint on one transaction is completely separated from a savepoint on another transaction.
(09 Sep '20, 05:24)
Volker Barth
|
