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

asked 06 May '14, 21:19

Breck%20Carter's gravatar image

Breck Carter
25.7k428592852
accept rate: 20%

edited 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

(07 May '14, 07:50) Martin
Replies hidden
1

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.

(07 May '14, 09:59) Breck Carter

FWIW, the docs seem to tell a little bit more on TO and mirroring:

When using TCP/IP on the server, this protocol option specifies how long to wait for:

  • The UDP broadcast response when searching for mirroring or diagnostic servers to make connections to.

  • The TCP/IP connect call when making mirroring or diagnostic connections to other database servers.

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

(07 May '14, 10:14) Volker Barth
1

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

(07 May '14, 10:16) Volker Barth
1

a great question on its own: is mirroring using only one connection?

(07 May '14, 11:31) Martin
Comment Text Removed
2

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

alt text

Here's what the connections look like on the mirror; 12 mostly-idle bots plus one Foxhound connection:

alt text

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 :)

(07 May '14, 16:43) Breck Carter
showing 2 of 6 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • 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:

×53

question asked: 06 May '14, 21:19

question was seen: 5,149 times

last updated: 17 May '14, 09:04