MySQL active connection never bleed off to other pool member

can you share your virtual server and pool configuration? (from tmsh)

Cheers,

Kees

Virtual Server

create ltm virtual /OSTACK/OSTACK_VS_GALERA { destination 172.28.0.9:3306 ip-protocol tcp mask 255.255.255.255 pool /OSTACK/OSTACK_POOL_GALERA profiles replace-all-with { /Common/fastL4 { } }  mirror enabled source-address-translation { pool /OSTACK/OSTACK_SNATPOOL type snat } }

POOL

create ltm pool /OSTACK/OSTACK_POOL_GALERA { load-balancing-mode least-connections-node members replace-all-with { OSTACK_NODE_ostack-infra-02_galera_container-fa5d9e98:3306 { priority-group 100 } OSTACK_NODE_ostack-infra-03_galera_container-eaacd880:3306 { priority-group 95 } OSTACK_NODE_ostack-infra-01_galera_container-6c126d29:3306 { priority-group 90 } } min-active-members 1 service-down-action reset slow-ramp-time 0 monitor /OSTACK/OSTACK_MON_GALERA }

The BIG-IP is sending a TCP Reset the moment the pool member with priority 100 goes offline/down.

Were you expecting the BigIP to automatically connect an existing TCP connection to another host without the client having to participate?

Even for the most basic of protocols (e.g. DNS, SNMP), this won't happen. When the pool member goes down you will (by default) device a RST to indicate that connection is no longer valid. You have the option to send the mid-stream connection to another host, but as that host has no idea of the connection, you will (again) recieve a RST. Now you can (In theory) write an iRule to migrate MySQL connections from one host to another (Triggered when the pool member goes down - this is where you'd normally recieve a RST back to the client). I have done it in the dim dark past for LDAP, but in practice it isn't trivial (And possibly non-practical, but I'd love to see someone do it) You'd have to implement a protocol specific proxy to track what was sent and what was received. For a SQL database you'd have to track transactions and be ready to replay the whole transaction to the second server if you had to migrate it for any reason...

Given it's usually a pretty application specific thing I'd probably suggest altering the app rather than the BigIP to accomplish migrations of MySQL connections). Or running mySQL Cluster which does (apparently) guarantee uninterrupted access from clients... but I've never tried it and I'd suspect it's not cheap either...

After lots of debugging i found following. If i point my application to F5 base LB then i am seeing following error, every minute.

(2006, "MySQL server has gone away (error(104, 'Connection reset by peer'))")

Here is the full output of error

2018-08-19 09:19:50.789 11159 ERROR oslo_db.sqlalchemy.engines [req-aa221914-d720-490c-a8e8-f9d7b780a353 8ec61b0530b94a699c4dcf164115f365 328fc75d4f944a64ad1b8699c02350ca - default default] Database connection was found disconnected; reconnecting: DBConnectionError: (pymysql.err.OperationalError) (2006, "MySQL server has gone away (error(104, 'Connection reset by peer'))") [SQL: u'SELECT 1'] (Background on this error at: http://sqlalche.me/e/e3q8)
2018-08-19 09:19:50.789 11159 ERROR oslo_db.sqlalchemy.engines Traceback (most recent call last):
2018-08-19 09:19:50.789 11159 ERROR oslo_db.sqlalchemy.engines   File "/openstack/venvs/nova-17.0.8/lib/python2.7/site-packages/oslo_db/sqlalchemy/engines.py", line 73, in _connect_ping_listener
2018-08-19 09:19:50.789 11159 ERROR oslo_db.sqlalchemy.engines     connection.scalar(select([1]))

Interesting thing is wen i point my application to haproxy (mysql LB) vip then error disappered, also if i point application directly to Galera mysql node then also error disappered, look like something going on with F5 based VIP

Do you think i should create "standar" VIP instead of "performance layer4" ? I did use persistent source addr but still same error.

In

tcpdump

RST

Ready for FUN after switching from

SNAT

automap

After switching to

automap

RST

You can read about SNAT here https://support.f5.com/csp/article/K7820

Hello satish.txt, maybe the problem is related to PVA acceleration. You can try to configure SNAT and attache new FastL4 profile with PVA acceleration to None or Offload State to EST.

@amintej,

I will try that but curious what is the relation with PVA acceleration?

I have other SNAT running on same F5 they all are working great except this MySQL one.

Current setting is PVA Acceleration = FULL