Re: read-only intent with SQL Server availability group

The tds code is currently written to discard ENVCHANGE info types it
doesn't know about, include the routing (20 type). See
https://github.com/FreeTDS/freetds/blob/master/src/tds/token.c#L2219


Here is the output after receiving the LOGIN7 response (I've redacted some
specific info, like hostnames, etc). Let me know if you need more info.

token.c:313:looking for login token, got  e3(ENVCHANGE)
token.c:132:tds_process_default_tokens() marker is e3(ENVCHANGE)
dblib.c:310:db_env_chg(0x558b3a75cfe0, 1, master, **REDACTED***)
token.c:313:looking for login token, got  ab(INFO)
token.c:132:tds_process_default_tokens() marker is ab(INFO)
mem.c:656:tds_free_all_results()
token.c:2355:tds_process_info() reading message 5701 from server
token.c:2427:tds_process_info() calling client msg handler
dbutil.c:75:_dblib_handle_info_message(0x558b3a724410, 0x558b3a75cfe0,
0x7ffd1ca86eb0)
dbutil.c:76:msgno 5701: "Changed database context to '**REDACTED***'."
dblib.c:5936:dbgetuserdata(0x558b3a75c330)
token.c:2444:tds_process_info() returning TDS_SUCCESS
token.c:313:looking for login token, got  e3(ENVCHANGE)
token.c:132:tds_process_default_tokens() marker is e3(ENVCHANGE)
token.c:2187:tds_process_env_chg(): 5 bytes of collation data received
token.c:2188:tds->conn->collation was
0000 00 00 00 00 00         -                        |.....|

iconv.c:784:setting server single-byte charset to "CP1252"
token.c:2198:tds->conn->collation now
0000 09 04 d0 00 34         -                        |....4|

token.c:313:looking for login token, got  e3(ENVCHANGE)
token.c:132:tds_process_default_tokens() marker is e3(ENVCHANGE)
dblib.c:310:db_env_chg(0x558b3a75cfe0, 2, , us_english)
token.c:313:looking for login token, got  ab(INFO)
token.c:132:tds_process_default_tokens() marker is ab(INFO)
mem.c:656:tds_free_all_results()
token.c:2355:tds_process_info() reading message 5703 from server
token.c:2427:tds_process_info() calling client msg handler
dbutil.c:75:_dblib_handle_info_message(0x558b3a724410, 0x558b3a75cfe0,
0x7ffd1ca86eb0)
dbutil.c:76:msgno 5703: "Changed language setting to us_english."
dblib.c:5936:dbgetuserdata(0x558b3a75c330)
token.c:2444:tds_process_info() returning TDS_SUCCESS
token.c:313:looking for login token, got  ad(LOGINACK)
token.c:351:server reports TDS version 74.0.0.4
token.c:353:Product name for 0x74000004 is unknown
token.c:385:Product version 8D000FA1
token.c:313:looking for login token, got  e3(ENVCHANGE)
token.c:132:tds_process_default_tokens() marker is e3(ENVCHANGE)
token.c:313:looking for login token, got  e3(ENVCHANGE)
token.c:132:tds_process_default_tokens() marker is e3(ENVCHANGE)
token.c:2244:changing block size from 4096 to 4096
dblib.c:310:db_env_chg(0x558b3a75cfe0, 4, 4096, 4096)
token.c:313:looking for login token, got  fd(DONE)
token.c:132:tds_process_default_tokens() marker is fd(DONE)
token.c:2087:tds_process_end: more_results = 0
was_cancelled = 0
error = 0
done_count_valid = 0
token.c:2106:                rows_affected = 0
token.c:2109:tds_process_end() state set to TDS_IDLE
token.c:422:tds_process_login_tokens() returning TDS_SUCCESS
mem.c:656:tds_free_all_results()
util.c:165:Changed query state from IDLE to WRITING
packet.c:801:Sending packet
0000 01 01 00 44 00 00 00 00-16 00 00 00 12 00 00 00 |...D.... ........|
0010 02 00 00 00 00 00 00 00-00 00 01 00 00 00 73 00 |........ ......s.|
0020 65 00 74 00 20 00 74 00-65 00 78 00 74 00 73 00 |e.t. .t. e.x.t.s.|
0030 69 00 7a 00 65 00 20 00-36 00 34 00 35 00 31 00 |i.z.e. . 6.4.5.1.|
0040 32 00 20 00            -                        |2. .|

util.c:165:Changed query state from WRITING to PENDING
token.c:532:tds_process_tokens(0x558b3a75cfe0, 0x7ffd1ca86fa0,
0x7ffd1ca86fa4, 0x100)
util.c:165:Changed query state from PENDING to READING
util.c:165:Changed query state from READING to DEAD
util.c:319:tdserror(0x558b3a724410, 0x558b3a75cfe0, 20017, 0)
dblib.c:8102:dbperror(0x558b3a75c330, 20017, 0)
dblib.c:8170:dbperror: Calling dblib_err_handler with msgno = 20017;
msg->msgtext = "Unexpected EOF from the server (**REDACTED***)"
dblib.c:5936:dbgetuserdata(0x558b3a75c330)
dblib.c:8192:dbperror: dblib_err_handler for msgno = 20017; msg->msgtext =
"Unexpected EOF from the server (***REDACTED***)" -- returns 2 (INT_CANCEL)
util.c:349:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:372:tdserror: returning TDS_INT_CANCEL(2)
packet.c:542:Read attempt when state is TDS_DEAD
token.c:547:processing result tokens.  marker is  0()
token.c:132:tds_process_default_tokens() marker is 0()
token.c:135:leaving tds_process_default_tokens() connection dead
dblib.c:1264:tdsdbopen: tds_connect_and_login failed for "**REDACTED***"!
dblib.c:1477:dbclose(0x558b3a75c330)
dblib.c:241:dblib_del_connection(0x7f8986bdd900, 0x558b3a75cfe0)
mem.c:656:tds_free_all_results()
dblib.c:288:dblib_release_tds_ctx(1)
dblib.c:6037:dbfreebuf(0x558b3a75c330)
dblib.c:774:dbsetlname(0x558b3a7b6860, , 14)
dblib.c:749:dbloginfree(0x558b3a7b6860)


On Thu, Nov 16, 2017 at 8:32 AM, Frediano Ziglio <[email protected]> wrote:

> 2017-11-16 2:28 GMT+00:00 Joshua Lang <[email protected]>:
> > When attempting to connect to SQL Server 2016 availability group
> listener,
> > I'm seeing a "Unexpected EOF from the server" error if read-only intent
> is
> > specified (this can be done via the freetds.conf file, for example). This
> > is using the latest dev1.00.308 code and an availability group with a
> > single secondary replica.
> >
> > The failure seems to come from the listener closing the connection after
> > sending an ENVCHANGE routing message (type 20) message to the client. See
> > https://msdn.microsoft.com/en-us/library/dd303449.aspx
> >
> > From my reading of the TDS protocol specification, it seems as though a
> TDS
> > 7.1 client and later should handle the ENVCHANGE routing message and
> > reconnect to the host specified in the routing message when the read-only
> > intent flag is set.
> >
> > Isn't the whole point of the read-only intent flag so that clients can be
> > routed to read-only replicas when possible? Note that FreeTDS does
> connect
> > successfully to the secondary (read-only) replica, and writes to it fail
> as
> > one would expect.
>
>
> Not implemented at all... but I don't think is hard to implement it,
> just a kind of
> reconnecting if you see the environment.
> Can you post a TDSDUMP (even privately) ?
>
> Frediano
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> https://lists.ibiblio.org/mailman/listinfo/freetds
>