on 2014 Dec 22 8:59 AM
I've been testing a mirror system set up in the usual manner which is working really well. The one problem I'm having is that for legacy reasons I'm using some "shadow tables" to do pseudo application-side auto inc columns. Each shadow table has no rows but the application uses the GET_IDENTITY function to get the next id in the sequence. The incrementing number is not being replicated to the mirror which is causing some problems.
The following example shows how it occurs:
The primary, secondary and arbiter are working normally.
The primary goes offline.
The secondary takes over from the primary but the Ids on the shadow tables are now out of sync.
The primary comes back up, synchronises and takes back the role of primary.
The out of sync Ids are written into the primary db so the primary is now out of sync with its (old) self.
Any help would be appreciated.
I can write a procedure to correct the Ids when the Db starts up but that doesn't feel like a very good solution.
I could set a trigger to insert/update or delete a row from the shadow table to make it replicate but that doesn't feel right either.
Thanks for your help.
Sam
SQL Anywhere 11 11.0.1.2837
I've added this line to the function that returns the shadow table id
EXECUTE('UPDATE "' || @TableName || '" SET IdValue = ' || @ReturnValue || ' WHERE IdValue = (SELECT MAX(IdValue) FROM "' || @TableName || '")')
This forces the table to update and thus send the data to the mirror at the next checkpoint (or right away if I COMMIT it). It keeps one row in each Shadow Table continually updated with the last value returned by GET_IDENTITY. I might work on the slightly clunky query though, even though there will only ever be one row in those tables so it shouldn't slow it down too much.
It's working great even in situation where the primary and secondary are going up and down repeatedly.
Thanks to Breck and Volker for clearing up how this all works.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Something about how triggers don't replicate....
Write the data to a local table first, then code an event that fires frequently which copies the data to your replicated table.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
In spite of the word "replication" being used in the original question (now fixed), this is NOT a question about SQL Remote or MobiLink, it is about High Availability... so the fine points of trigger action replication don't apply.
User | Count |
---|---|
64 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.