cancel
Showing results for 
Search instead for 
Did you mean: 

Mobilink (SQL10) loses Savepoints

Baron
Participant
0 Kudos
1,327

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:

server error: Message: ODBC: [Sybase][ODBC Driver][SQL Anywhere]Savepoint 'it3' not found (ODBC State = HY000, Native error code = -220). Table Name:....

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?

Accepted Solutions (1)

Accepted Solutions (1)

Baron
Participant

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.

Breck_Carter
Participant
0 Kudos

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".

Breck_Carter
Participant
0 Kudos

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.

VolkerBarth
Contributor
0 Kudos

"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.

VolkerBarth
Contributor
0 Kudos

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.

Breck_Carter
Participant
0 Kudos

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.

Answers (1)

Answers (1)

regdomaratzki
Product and Topic Expert
Product and Topic Expert

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.
Looking at this log, there's a solid chance we're doing a COMMIT in the admin_ui stored procedure, so looking at the SQL for this stored procedure might answer it. My repro is quite trivial, as I've embedded a COMMIT in the stored procedure.

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

Baron
Participant
0 Kudos

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:

select * from ml_script where script like '%it3%'

select * from sysprocedure where proc_defn like '%it3%'

select * from systriggers where trigdefn like '%it3%'

Where else should I look for it?

regdomaratzki
Product and Topic Expert
Product and Topic Expert

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.

Breck_Carter
Participant

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.

VolkerBarth
Contributor
0 Kudos

True — But would you expect a DDL statement within a ML script?

Breck_Carter
Participant

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.

Baron
Participant
0 Kudos

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?

VolkerBarth
Contributor

From the docs:

COMMIT and ROLLBACK are not allowed in a trigger or in any procedures called by a trigger.

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.

Baron
Participant
0 Kudos

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

VolkerBarth
Contributor
0 Kudos

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...

Baron
Participant
0 Kudos

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:

<95> System event: savepoint it3

.........

<96> Request from "Dbmlsync Version...."

.........

<96> System event: savepoint it3

.........

<96> System event: release savepoint it3

.........

<95> System event: release savepoint it3

<95> [-10002] Consolidated database server or ODBC error: ODBC: [Sybase][ODBC Driver][SQL Anywhere]Savepoint 'it3' not found (ODBC State = HY000, Native error code = -220)

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

Baron
Participant
0 Kudos

@Reg Domaratzki what means the number between angle brackets below in Bold?

I. 2020-09-03 10:05:43. <1> COMMIT Transaction: Begin synchronization

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?

Baron
Participant
0 Kudos

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

VolkerBarth
Contributor
0 Kudos

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.