max_connect_errors too low for trusty deployments

[Andreas Hasenack]

Using cs:trusty/percona-cluster-178.

I have an openstack deployment where 2 out of 3 cinder units do not have their cinder services running. Their logs show this:

2016-03-30 20:47:37.581 4298 ERROR cinder DBConnectionError: (pymysql.err.OperationalError) (2003, 'Can\'t connect to MySQL server on \'10.245.202.30\' ((1129, u"Host \'node-8.vmwarestack\' is blocked because of many connection errors; unblock with \'mysqladmin flush-hosts\'"))')

The max_connection_errors setting in percona-cluster is:
mysql> show variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 10 |
+--------------------+-------+
1 row in set (0.01 sec)

According to http://dev.mysql.com/doc/refman/5.6/en/blocked-host.html:
"""
By default, mysqld blocks a host after 100 connection errors (10 before MySQL 5.6.6).
"""

Maybe we should bump the default to 100, or offer this as a knob in the charm configuration for percona?

The percona/mysql unit has these versions:
$ dpkg -l|grep -E "(mysql|percona)"
ii libdbd-mysql-perl 4.025-1 amd64 Perl5 database interface to the MySQL database
ii libmysqlclient18:amd64 5.5.47-0ubuntu0.14.04.1 amd64 MySQL database client library
ii mysql-common 5.5.47-0ubuntu0.14.04.1 all MySQL database common files, e.g. /etc/mysql/my.cnf
ii percona-xtrabackup 2.1.8-1 amd64 Open source backup tool for InnoDB and XtraDB
ii percona-xtradb-cluster-client-5.5 5.5.37-25.10+dfsg-0ubuntu0.14.04.1 amd64 Percona Server database client binaries
ii percona-xtradb-cluster-common-5.5 5.5.37-25.10+dfsg-0ubuntu0.14.04.1 all Percona Server database common files
ii percona-xtradb-cluster-galera-2.x 165-0ubuntu1 amd64 Synchronous multi-master replication plugin for transactional applications
ii percona-xtradb-cluster-server-5.5 5.5.37-25.10+dfsg-0ubuntu0.14.04.1 amd64 Percona Server database server binaries
ii python-mysqldb 1.2.3-2ubuntu1 amd64 Python interface to MySQL

[Andreas Hasenack (ahasenack)]

Added a landscape task to update the stable charm revision if this gets addressed in a new charm version.

[Andreas Hasenack (ahasenack)]

This is what it looked like on cinder/1:
2016-03-30 20:01:18.200 542 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 10 attempts left.
2016-03-30 20:01:28.219 542 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 9 attempts left.
2016-03-30 20:01:38.285 542 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 8 attempts left.
2016-03-30 20:01:48.302 542 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 7 attempts left.
2016-03-30 20:01:58.308 542 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 6 attempts left.
2016-03-30 20:02:01.068 8920 WARNING cinder.api [-] The v1 api is deprecated and will be removed in the Liberty release. You should set enable_v1_api=false and enable_v2_api=true in your cinder.conf file.
2016-03-30 20:02:01.377 8920 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 10 attempts left.
2016-03-30 20:02:11.385 8920 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 9 attempts left.
2016-03-30 20:02:21.403 8920 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 8 attempts left.
2016-03-30 20:02:31.444 8920 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 7 attempts left.
2016-03-30 20:02:41.453 8920 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 6 attempts left.
2016-03-30 20:02:51.469 8920 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 5 attempts left.
2016-03-30 20:03:01.481 8920 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 4 attempts left.
2016-03-30 20:03:15.649 23889 WARNING cinder.api [-] The v1 api is deprecated and will be removed in the Liberty release. You should set enable_v1_api=false and enable_v2_api=true in your cinder.conf file.
2016-03-30 20:03:16.331 23889 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 10 attempts left.
2016-03-30 20:03:26.350 23889 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 9 attempts left.
2016-03-30 20:03:36.374 23889 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 8 attempts left.
2016-03-30 20:03:46.408 23889 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 7 attempts left.
2016-03-30 20:03:56.423 23889 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 6 attempts left.
2016-03-30 20:04:06.437 23889 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 5 attempts left.
2016-03-30 20:04:16.445 23889 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 4 attempts left.
2016-03-30 20:04:26.453 23889 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 3 attempts left.
2016-03-30 20:04:36.455 23889 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 2 attempts left.
2016-03-30 20:04:41.929 9820 WARNING cinder.api [-] The v1 api is deprecated and will be removed in the Liberty release. You should set enable_v1_api=false and enable_v2_api=true in your cinder.conf file.
2016-03-30 20:04:42.308 9820 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 10 attempts left.
2016-03-30 20:04:52.317 9820 WARNING oslo_db.sqlalchemy.engines [-] SQL connection failed. 9 attempts left.
2016-03-30 20:05:02.326 9820 WARNING oslo_db.sqlalc...

[Adam Collard (adam-collard)]

There's nothing for Landscape to do (yet... reopen when there is)

[James Page (james-page)]

https://review.openstack.org/440533

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/440533
Committed: https://git.openstack.org/cgit/openstack/charm-percona-cluster/commit/?id=8a6c49f249bc6d85728bfc857871d318c2b67f40
Submitter: Jenkins
Branch: master

commit 8a6c49f249bc6d85728bfc857871d318c2b67f40
Author: James Page <email address hidden>
Date: Thu Mar 2 12:56:28 2017 +0000

    Increase max_connect_errors for older PXC releases

    Ubuntu 14.04 ships with PXC 5.5, which has a lower default
    value for max_connect_errors (10 vs 100); bump this value
    inline with later 5.6 release to avoid issues with connections
    from clients getting blacklisted due to connection problems.

    (NOTE: my.cnf is used on trusty, mysqld.cnf on xenial or later).

    Change-Id: I5475c4e1b10cb638d0694c380714c468f8d78d46
    Closes-Bug: 1564083