|
Update: The original question was stated "What might cause a mirror to disconnect during a long ALTER TABLE?" The following excerpt from the console log for a 12.0.1.3797 mirror database shows activity during an ALTER TABLE on the primary that ran for about two minutes to add a column to a 1.5 million row table. The mirror became disconnected from the primary for some unknown reason, then it appears it couldn't become the primary itself, then it restarted itself, then it became the mirror again. In the meantime the primary kept chugging away, although Sybase Central became unresponsive. I. 05/05 02:47:49. Starting checkpoint of "xxx" (xxx.db) at Mon May 05 2014 02:47 I. 05/05 02:47:50. Finished checkpoint of "xxx" (xxx.db) at Mon May 05 2014 02:47 I. 05/05 02:54:24. Database "xxx" mirroring: disconnected from partner "ppp" I. 05/05 02:54:24. Database "xxx" mirroring: becoming primary server ... I. 05/05 02:59:20. Database "xxx" mirroring: neither partner nor arbiter confirmed this server could become primary I. 05/05 02:59:20. Database "xxx" mirroring: restarting to determine mirror role I. 05/05 02:59:23. Database "xxx" mirroring: disconnected from arbiter "aaa" I. 05/05 02:59:23. Database "xxx" (xxx.db) stopped at Mon May 05 2014 02:59 I. 05/05 02:59:23. Starting database "xxx" (c:\xxx\xxx.db) at Mon May 05 2014 02:59 ... I. 05/05 02:59:23. Database recovery in progress I. 05/05 02:59:23. Last checkpoint at Mon May 05 2014 02:47 I. 05/05 02:59:23. Checkpoint log... I. 05/05 02:59:28. ... recovery 13% complete ... I. 05/05 02:59:58. ... recovery 92% complete I. 05/05 03:00:02. Opening dbspace ccc ... ... I. 05/05 03:00:02. Opening dbspace ddd ... ... I. 05/05 03:00:07. ... recovery 12% complete ... I. 05/05 03:00:38. ... recovery 87% complete I. 05/05 03:01:47. Transaction log: xxx.log... I. 05/05 03:07:16. Database "xxx" mirroring: local status for this server: role=mirror, state=synchronizing, sequence=52, yielding=N I. 05/05 03:07:16. Database "xxx" mirroring: determining mirror role ... I. 05/05 03:07:16. Database "xxx" mirroring: connected to partner "ppp" I. 05/05 03:07:16. Database "xxx" mirroring: partner status for this server: role=mirror, state=synchronizing, sequence=52, yielding=N I. 05/05 03:07:17. Database "xxx" mirroring: connected to arbiter "aaa" I. 05/05 03:07:17. Database "xxx" mirroring: arbiter status for this server: role=mirror, state=synchronizing, sequence=52, yielding=N I. 05/05 03:07:17. Database "xxx" mirroring: synchronizing ... I. 05/05 03:07:17. Database "xxx" (xxx.db) started as mirror at Mon May 05 2014 03:07 ... I. 05/05 03:07:30. Starting checkpoint of "xxx" (xxx.db) at Mon May 05 2014 03:07 I. 05/05 03:09:09. Finished checkpoint of "xxx" (xxx.db) at Mon May 05 2014 03:09 Could this be caused by the tcpip timeout=2 in the mirror configuration file? # dbsvc.exe parameters... -o zzz\dbsvc_create_service_log.txt -y -as -i -s Automatic -t Network -w ppp "C:\Program Files\SQL Anywhere 12\Bin64\dbsrv12.exe" # dbsrv12.exe parameters... -n sss -o ... .txt -su nfr -x tcpip(port=55500;dobroadcast=no) -xf ... .txt -gl All -ti 0 ... xxx.db -sn ccc -sm ddd -xp partner=(eng=ppp;links=tcpip(host=192.168.0.20;port=55500;timeout=2));mode=sync;autofailover=YES;auth=...;arbiter=(eng=aaa;links=tcpip(host=192.168.0.30;port=55501;timeout=2)) |
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: 06 May '14, 21:19
question was seen: 6,533 times
last updated: 17 May '14, 09:04
The conclusion would be, that any long running statement might lead the mirror to react, if the tcp timeout is too low
Is that fact or speculation? ...just asking, since the docs are silent on the effect of TO on a running HA system. In fact, the docs say TO "Specifies the length of time, in seconds, to wait for a response when establishing communications (TCP/IP)." which seems to exclude any effect on a connection AFTER it has been established. The docs DO talk about LTO but in this case liveness checking is turned off (-ti 0).
I do fervently wish that someone (the oft-mentioned "they" :) would copy some of the wealth of information from case express and other sources into the docs... alas, "they" are in short supply.
FWIW, the docs seem to tell a little bit more on TO and mirroring:
(Though I don't know whether there is only one continuous mirroring connection between two partners after they have made their initial connection? In that case, the help won't tell more than you've already stated...)
Newer 12.0.1 EBFs contain a bunch of topics on HA (though not necessarily related to your symptoms...). You might give that a try - unless "they" tell you beforehand it won't help here...
a great question on its own: is mirroring using only one connection?
> is mirroring using only one connection?
No... the mirror will have at least one connection for every connection that exists on the primary. Here's an example where the primary has 12 connections (ten happy users, one administrator, and one Foxhound connection)...
Here's what the connections look like on the mirror; 12 mostly-idle bots plus one Foxhound connection:
If connections disappear from the primary, their doppelgangers on the mirror hang around for a while (presumably in case they are needed again) before they disappear.
I believe this one-for-one connection scheme also applies when the engine applies a transaction log in forward recovery mode... which makes sense, since High Availability is really a fancy term for database recovery :)