Using GRANT for creating new user is deprecated

[Celia Wang]

Error MSG:
2019-12-16 23:28:49 DEBUG install /var/lib/juju/agents/unit-percona-cluster-0/charm/charmhelpers/contrib/database/mysql.py:164: Warning: (1287, 'Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement.')

Steps:
1. juju deploy percona-cluster
2. juju deploy -n 3 keystone
3. juju deploy hacluster
4. juju add-relation keystone:shared-db percona-cluster:shared-db
5. juju add-relation hacluster:ha keystone:ha
6. juju config keystone vip='10.5.0.77'

Topo:
$ juju status
Model Controller Cloud/Region Version SLA Timestamp
zaza-a899aced92a5 ziyiwang-serverstack serverstack/serverstack 2.6.9 unsupported 01:44:49Z

App Version Status Scale Charm Store Rev OS Notes
hacluster active 3 hacluster local 0 ubuntu
keystone 16.0.0 active 3 keystone jujucharms 468 ubuntu
percona-cluster 5.7.20 active 1 percona-cluster jujucharms 356 ubuntu

Unit Workload Agent Machine Public address Ports Message
keystone/0* active idle 1 10.5.0.19 5000/tcp Unit is ready
  hacluster/0* active idle 10.5.0.19 Unit is ready and clustered
keystone/1 active idle 2 10.5.0.9 5000/tcp Unit is ready
  hacluster/1 active idle 10.5.0.9 Unit is ready and clustered
keystone/2 active idle 3 10.5.0.20 5000/tcp Unit is ready
  hacluster/2 active idle 10.5.0.20 Unit is ready and clustered
percona-cluster/0* active idle 0 10.5.0.7 3306/tcp Unit is ready

Machine State DNS Inst id Series AZ Message
0 started 10.5.0.7 50cdac06-f5fc-43b0-bb27-7feb23b6ad1a bionic nova ACTIVE
1 started 10.5.0.19 031204a7-66d1-425a-8a51-144e40a9158a bionic nova ACTIVE
2 started 10.5.0.9 97af15bb-c74d-4172-bcff-5e91495570bb bionic nova ACTIVE
3 started 10.5.0.20 e34d756f-2506-46b5-8b47-4b568a3497f5 bionic nova ACTIVE

[Alex Kavanagh (ajkavanagh)]

TRIAGED: confirmed.

The offending code is in hooks/percona_utils.py at:

303:
SQL_SST_USER_SETUP = ("GRANT {permissions} ON *.* "
                      "TO 'sstuser'@'localhost' IDENTIFIED BY '{password}'")

SQL_SST_USER_SETUP_IPV6 = ("GRANT {permissions} "
                           "ON *.* TO 'sstuser'@'ip6-localhost' IDENTIFIED "
                           "BY '{password}'")

1440:

def create_replication_user(slave_address, master_password):
    """ Create replication user (lp1776171)

    Grants access for MySQL asynchronous replication slave.

    :param slave_address: slave IP address
    :type slave_address: str
    :param master_password: replication password
    :type master_password: str
    :raises: OperationalError
    """
    m_helper = get_db_helper()
    try:
        m_helper.connect(password=m_helper.get_mysql_root_password())
    except OperationalError:
        log("Could not connect to db", level=DEBUG)
        return
    m_helper.execute(("GRANT REPLICATION SLAVE ON *.* TO ""'replication'@'{}' "
                      "IDENTIFIED BY '{}';").format(slave_address,
                                                    master_password))

FIX:

Replace grants with two SQL commands; one to create the user and one to grant the grant.