max_connection_lifetime should be configurable for the mysql backend

[Nicolas Pochet]

As in many of the clouds that we deploy we have a custom wait-timeout value set for the percona-cluster application.
If that value is different from the one that is configured in vault for the max_connection_lifetime parameter (default is 0 -> reuse the same connection indefinitely), we get the following errors from time to time:

mysql packets.go:36 unexpected EOF

In order to avoid such errors, it would be necessary to be able to configure the max_connection_lifetime [0] parameter in vault.

[0] https://www.vaultproject.io/docs/configuration/storage/mysql.html#max_connection_lifetime

[Ryan Beisner (1chb1n)]

FYI This is flagged as wishlist because there is an outstanding feature request in the backlog to implement formal support for mysql/percona back-end. At this time, it is only experimental and untested. Hence, the stance of not modifying something that is not intended to be used, and is not regression tested.

[Ryan Beisner (1chb1n)]

I crossed wires on 2 bugs in comment 1, plz ignore that. Apologies for any confusion.

[James Page (james-page)]

MySQL will close any non-interactive connections after 8 hours (i.e ones that are pooled by the client but have not been used).

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_wait_timeout

[James Page (james-page)]

We should probably set this via the charm to 3600 - this is aligned to OpenStack's idle_timeout configuration default (which is not mutable by charm users).

[James Page (james-page)]

As a wider piece of work the percona-cluster charm should probably propagate the wait-timeout value to consuming services, so they can set an appropriate configuration in the mysql client/pooling software being used.

[James Page (james-page)]

Added bug tasks for core MySQL solution charms - for the InnoDB cluster option this might also need sync/configuration into the mysql-router charm.

[David Ames (thedac)]

This would need to be an addition to the share-db interface [0] and in the case of mysql-innodb-cluster also the db-router interface [1].

For mysql-innodb-cluster the information path will look like this:

mysql-innod-cluster --- db-router relation --- mysql-router --- shared-db relation --- client

[David Ames (thedac)]

Adding the links:

[0] https://github.com/openstack/charm-interface-mysql-shared
[1] https://github.com/openstack/charm-interface-mysql-router

[James Page (james-page)]

Thanks David

I've raised an interim fix for the Vault charm to set the timeout for connections to 3600 seconds inline with OpenStack so we are at least consistent across clients in a deployment.

[James Page (james-page)]

https://review.opendev.org/#/c/702827/

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/720303

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/720305

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/720309

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/720303
Committed: https://git.openstack.org/cgit/openstack/charm-interface-mysql-shared/commit/?id=7c50d9e1dcd8361047a90619588c94756165b318
Submitter: Zuul
Branch: master

commit 7c50d9e1dcd8361047a90619588c94756165b318
Author: David Ames <email address hidden>
Date: Wed Apr 15 15:13:54 2020 -0700

    Pass wait timeout all the way through to clients

    Pass the non-interactive wait timeout value through.

    Change-Id: I25c2e982ebabe09385e52fef0fb8c97cabe60756
    Closes-Bug: #1841063

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/720292
Committed: https://git.openstack.org/cgit/openstack/charm-interface-mysql-router/commit/?id=19ac73ab8081ce24285279f784cf807f92faf044
Submitter: Zuul
Branch: master

commit 19ac73ab8081ce24285279f784cf807f92faf044
Author: David Ames <email address hidden>
Date: Wed Apr 15 14:38:09 2020 -0700

    Pass wait timeout all the way through to clients

    Pass the non-interactive wait timeout value through.

    Closes-Bug: #1841063
    Change-Id: I6ec40bc65f719e1938d9b0ccdb7ad00236f435d0

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/720305
Committed: https://git.openstack.org/cgit/openstack/charm-mysql-innodb-cluster/commit/?id=96ddade852a751f601f98f5d2ac1c600722fc1e2
Submitter: Zuul
Branch: master

commit 96ddade852a751f601f98f5d2ac1c600722fc1e2
Author: David Ames <email address hidden>
Date: Mon Apr 13 15:26:14 2020 -0700

    Pass wait timeout all the way through to clients

    Wait timeout is the non-interactive idle timeout setting. The default of
    3600 is aligned to OpenStack's idle_timeout configuration default.

    Along with changes to the mysql-shared and mysql-router interfaces, this
    change passes the wait timeout value all the way through to the client.

    Depends-On: I6ec40bc65f719e1938d9b0ccdb7ad00236f435d0
    Closes-Bug: #1841063
    Closes-Bug: #1873331
    Change-Id: Ie2b2ad2856cbe1d830f1b7e674a2fb867d81de12

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/720309
Committed: https://git.openstack.org/cgit/openstack/charm-mysql-router/commit/?id=f8f5495b4b690140ae737351f05a8a020861eb2a
Submitter: Zuul
Branch: master

commit f8f5495b4b690140ae737351f05a8a020861eb2a
Author: David Ames <email address hidden>
Date: Wed Apr 15 15:37:02 2020 -0700

    Pass wait timeout all the way through to clients

    Wait timeout is the non-interactive idle timeout setting. The default of
    3600 is aligned to OpenStack's idle_timeout configuration default.

    Proxy this value from the server to the client.

    Depends-On: Ie2b2ad2856cbe1d830f1b7e674a2fb867d81de12
    Change-Id: I0a40a65cdf1b618ec843bf872fe0b39866496e1c
    Closes-Bug: #1841063