Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

During reload pgbouncer throws error "unexpected response from login query" #220

Closed
landreville opened this issue Jul 7, 2017 · 5 comments

Comments

@landreville
Copy link

@landreville landreville commented Jul 7, 2017

When reloading Postgres with SIGHUP to reload the config file, if a new connection from pgbouncer to postgres is initiated during the reload, then pgbouncer throws an error "unexpected response from login query".

No other database client has an issue connecting during this period and no connections are disconnected.

@markokr

This comment has been minimized.

Copy link
Contributor

@markokr markokr commented Jul 9, 2017

Seems duplicate of #161, fixed by 9f2b3c8.

Please test current master and reopen if still happens

@markokr markokr closed this Jul 9, 2017
@mnencia

This comment has been minimized.

Copy link
Contributor

@mnencia mnencia commented Dec 28, 2018

Hi, It is not the same issue of #161 and I have been able to reproduce it on my machine while I was dumping the connection with tcpdump. The issue is that, after a reload, the backend could send some ParameterStatus messages together with the login query response.

here is my tcpdump output:

19:54:30.760270 IP 127.0.0.1.51801 > 127.0.0.1.5496: Flags [P.], seq 1900049:1900163, ack 3340633, win 7261, options [nop,nop,TS val 990321985 ecr 990321985], length 114
	0x0000:  4500 00a6 0000 4000 4006 0000 7f00 0001  E.....@.@.......
	0x0010:  7f00 0001 ca59 1578 d449 0117 e7a0 2247  .....Y.x.I...."G
	0x0020:  8018 1c5d fe9a 0000 0101 080a 3b07 1d41  ...]........;..A
	0x0030:  3b07 1d41 5000 0000 4500 7365 6c65 6374  ;..AP...E.select
	0x0040:  202a 2046 524f 4d20 6765 745f 7061 7373  .*.FROM.get_pass
	0x0050:  7764 2824 3129 2061 7320 6628 7573 656e  wd($1).as.f(usen
	0x0060:  616d 6520 6e61 6d65 2c20 7061 7373 7764  ame.name,.passwd
	0x0070:  2074 6578 7429 3b00 0000 4200 0000 1500  .text);...B.....
	0x0080:  0000 0000 0100 0000 0570 6970 706f 0000  .........pippo..
	0x0090:  4400 0000 0650 0045 0000 0009 0000 0000  D....P.E........
	0x00a0:  0053 0000 0004                           .S....
19:54:30.760284 IP 127.0.0.1.5496 > 127.0.0.1.51801: Flags [.], ack 1900163, win 5270, options [nop,nop,TS val 990321985 ecr 990321985], length 0
	0x0000:  4500 0034 0000 4000 4006 0000 7f00 0001  E..4..@.@.......
	0x0010:  7f00 0001 1578 ca59 e7a0 2247 d449 0189  .....x.Y.."G.I..
	0x0020:  8010 1496 fe28 0000 0101 080a 3b07 1d41  .....(......;..A
	0x0030:  3b07 1d41                                ;..A
19:54:30.768950 IP 127.0.0.1.5496 > 127.0.0.1.51801: Flags [P.], seq 3340633:3340852, ack 1900163, win 5270, options [nop,nop,TS val 990321992 ecr 990321985], length 219
	0x0000:  4500 010f 0000 4000 4006 0000 7f00 0001  E.....@.@.......
	0x0010:  7f00 0001 1578 ca59 e7a0 2247 d449 0189  .....x.Y.."G.I..
	0x0020:  8018 1496 ff03 0000 0101 080a 3b07 1d48  ............;..H
	0x0030:  3b07 1d41 5300 0000 1963 6c69 656e 745f  ;..AS....client_
	0x0040:  656e 636f 6469 6e67 0055 5446 3800 5300  encoding.UTF8.S.
	0x0050:  0000 1744 6174 6553 7479 6c65 0049 534f  ...DateStyle.ISO
	0x0060:  2c20 444d 5900 5300 0000 1954 696d 655a  ,.DMY.S....TimeZ
	0x0070:  6f6e 6500 4575 726f 7065 2f52 6f6d 6500  one.Europe/Rome.
	0x0080:  3100 0000 0432 0000 0004 5400 0000 3900  1....2....T...9.
	0x0090:  0275 7365 6e61 6d65 0000 0000 0000 0000  .usename........
	0x00a0:  0000 1300 40ff ffff ff00 0070 6173 7377  ....@......passw
	0x00b0:  6400 0000 0000 0000 0000 0019 ffff ffff  d...............
	0x00c0:  ffff 0000 4400 0000 3600 0200 0000 0570  ....D...6......p
	0x00d0:  6970 706f 0000 0023 6d64 3535 6133 3834  ippo...#md55a384
	0x00e0:  3963 6537 6663 3439 3338 3735 3233 6130  9ce7fc49387523a0
	0x00f0:  3338 3231 6463 3635 3631 3343 0000 000d  3821dc65613C....
	0x0100:  5345 4c45 4354 2031 005a 0000 0005 49    SELECT.1.Z....I

As you can see after a reload some parameter have changed and postgres notified the client about it, but that confuses the login query. I think that we could simply ignore that messages with the following patch:

diff --git a/src/client.c b/src/client.c
index 2a46e0d..dfe856b 100644
--- a/src/client.c
+++ b/src/client.c
@@ -374,6 +374,8 @@ bool handle_auth_response(PgSocket *client, PktHdr *pkt) {
                break;
        case '2':       /* BindComplete */
                break;
+       case 'S': /* ParameterStatus */
+               break;
        case 'Z':       /* ReadyForQuery */
                sbuf_prepare_skip(&client->link->sbuf, pkt->len);
                if (!client->auth_user) {
@PJMODOS

This comment has been minimized.

Copy link
Contributor

@PJMODOS PJMODOS commented Dec 29, 2018

@mnencia Sounds about right (the ParameterStatus was processed already so we should just ignore it as you say). Wanna turn the patch into pull request?

@mnencia

This comment has been minimized.

Copy link
Contributor

@mnencia mnencia commented Dec 29, 2018

I've created a pull request.

mnencia added a commit to mnencia/pgbouncer that referenced this issue Dec 29, 2018
After a PostgreSQL reload, the backend could inform the frontend about
configuration changes by sending some ParameterStatus messages
together with the usual login query response. These messages can be
safely ignored in the login query response handler.

Closes: pgbouncer#220
mnencia added a commit to mnencia/pgbouncer that referenced this issue Dec 29, 2018
After a PostgreSQL reload, the backend could inform the frontend about
configuration changes by sending some ParameterStatus messages
together with the usual login query response. These messages can be
safely ignored in the login query response handler.

Closes: pgbouncer#220
@PJMODOS PJMODOS closed this in #356 Dec 30, 2018
@PJMODOS

This comment has been minimized.

Copy link
Contributor

@PJMODOS PJMODOS commented Dec 30, 2018

Merged, thanks

netbsd-srcmastr pushed a commit to NetBSD/pkgsrc that referenced this issue Aug 25, 2019
Changes since 1.9.0

2019-07-01 - PgBouncer 1.10.0 - "Afraid of the World"

    Features
        Add support for enabling and disabling TLS 1.3. (TLS 1.3 was
        already supported, depending on the OpenSSL library, but now the
        configuration settings to pick the TLS protocol versions also
        support it.)
    Fixes
        Fix TLS 1.3 support. This was broken with OpenSSL 1.1.1 and
        1.1.1a (but not before or after).
        Fix a rare crash in SHOW FDS
        (pgbouncer/pgbouncer#311).
        Fix an issue that could lead to prolonged downtime if many cancel
        requests arrive
        (pgbouncer/pgbouncer#329).
        Avoid "unexpected response from login query" after a postgres
        reload
        (pgbouncer/pgbouncer#220).
        Fix idle_transaction_timeout calculation
        (pgbouncer/pgbouncer#125). The
        bug would lead to premature timeouts in specific situations.
    Cleanups
        Make various log and error messages more precise.
        Fix issues found by Coverity (none had a significant impact in
        practice).
        Improve and document all test scripts.
        Add additional SHOW commands to the documentation.
        Convert the documentation from rst to Markdown.
        Python scripts in the source tree are all compatible with Python 3
        now.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
4 participants
You can’t perform that action at this time.