Migration from percona cluster charm in error state after relation of mysql-router

[Diko Parvanov]

Migrating percona cluster to innodb cluster for bionic -> focal upgrade following https://docs.openstack.org/project-deploy-guide/charm-deployment-guide/latest/percona-series-upgrade-to-focal.html

After the add-relation of mysql-router to any application (installing subordinate charm) the R/W innodb unit gets into: hook failed: "db-router-relation-changed"

Using latest router, revision 10, innoodb-cluster revision 8.

Removing the relation and resolve --no-retry to get back into operational state, so relation-departed runs properly.

unit-mysql-innodb-cluster-0: 09:23:50 ERROR unit.mysql-innodb-cluster/0.juju-log db-router:419: Hook error:
Traceback (most recent call last):
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charms/reactive/__init__.py", line 74, in main
    bus.dispatch(restricted=restricted_mode)
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charms/reactive/bus.py", line 390, in dispatch
    _invoke(other_handlers)
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charms/reactive/bus.py", line 359, in _invoke
    handler.invoke()
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charms/reactive/bus.py", line 181, in invoke
    self._action(*args)
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/charm/reactive/mysql_innodb_cluster_handlers.py", line 302, in db_router_respond
    if instance.create_databases_and_users(db_router):
  File "lib/charm/openstack/mysql_innodb_cluster.py", line 1181, in create_databases_and_users
    password = self.configure_db_router(
  File "lib/charm/openstack/mysql_innodb_cluster.py", line 1318, in configure_db_router
    password = rw_helper.configure_router(host, username)
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charmhelpers/contrib/database/mysql.py", line 774, in configure_router
    self.create_router_grant(username, remote_ip, password)
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charmhelpers/contrib/database/mysql.py", line 747, in create_router_grant
    cursor.execute("GRANT CREATE USER ON *.* TO '{}'@'{}' WITH GRANT "
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1785, 'Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.')

unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed Traceback (most recent call last):
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/charm/hooks/db-router-relation-changed", line 22, in <module>
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed main()
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charms/reactive/__init__.py", line 74, in main
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed bus.dispatch(restricted=restricted_mode)
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charms/reactive/bus.py", line 390, in dispatch
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed _invoke(other_handlers)
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charms/reactive/bus.py", line 359, in _invoke
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed handler.invoke()
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charms/reactive/bus.py", line 181, in invoke
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed self._action(*args)
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/charm/reactive/mysql_innodb_cluster_handlers.py", line 302, in db_router_respond
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed if instance.create_databases_and_users(db_router):
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed File "lib/charm/openstack/mysql_innodb_cluster.py", line 1181, in create_databases_and_users
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed password = self.configure_db_router(
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed File "lib/charm/openstack/mysql_innodb_cluster.py", line 1318, in configure_db_router
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed password = rw_helper.configure_router(host, username)
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charmhelpers/contrib/database/mysql.py", line 774, in configure_router
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed self.create_router_grant(username, remote_ip, password)
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charmhelpers/contrib/database/mysql.py", line 747, in create_router_grant
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed cursor.execute("GRANT CREATE USER ON *.* TO '{}'@'{}' WITH GRANT "
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed res = self._query(query)
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed db.query(q)
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed _mysql.connection.query(self, query)
unit-mysql-innodb-cluster-0: 09:23:50 WARNING unit.mysql-innodb-cluster/0.db-router-relation-changed MySQLdb._exceptions.OperationalError: (1785, 'Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.')
unit-mysql-innodb-cluster-0: 09:23:50 ERROR juju.worker.uniter.operation hook "db-router-relation-changed" (via explicit, bespoke hook script) failed: exit status 1

[Dmitrii Shcherbakov (dmitriis)]

A shorter piece of output which is of interest:

  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/charm/reactive/mysql_innodb_cluster_handlers.py", line 302, in db_router_respond
    if instance.create_databases_and_users(db_router):
  File "lib/charm/openstack/mysql_innodb_cluster.py", line 1181, in create_databases_and_users
    password = self.configure_db_router(
  File "lib/charm/openstack/mysql_innodb_cluster.py", line 1318, in configure_db_router
    password = rw_helper.configure_router(host, username)
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charmhelpers/contrib/database/mysql.py", line 774, in configure_router
    self.create_router_grant(username, remote_ip, password)
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charmhelpers/contrib/database/mysql.py", line 747, in create_router_grant
    cursor.execute("GRANT CREATE USER ON *.* TO '{}'@'{}' WITH GRANT "

MySQLdb._exceptions.OperationalError: (1785, 'Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.')

So the issue appears when the charm tries to assign a permission to a user to create new users with the right to delegate this permission.

[Dmitrii Shcherbakov (dmitriis)]

Grant statements are affected by the GTID mechanism:

https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/replication-gtids-lifecycle.html
"Every database change (DDL or DML) that is written to the binary log is assigned a GTID. This includes changes that are autocommitted, and changes that are committed using BEGIN and COMMIT or START TRANSACTION statements. ***A GTID is also assigned to*** the creation, alteration, or deletion of a database, and of a ***non-table database object*** such as a procedure, function, trigger, event, view, user, role, or ***grant***."

The mysql.user table gets modified in the end by grant statements:
https://dev.mysql.com/doc/refman/8.0/en/grant-tables.html#grant-tables-user-db
"The user and db Grant Tables
The server uses the user and db tables in the mysql database at both the first and second stages of access control (see Section 6.2, “Access Control and Account Management”). The columns in the user and db tables are shown here."

As of MySQL 8.0 the grant tables (including the mysql.user) table are transactional:
https://dev.mysql.com/doc/refman/8.0/en/system-schema.html#system-schema-grant-tables - grant system tables.
"As of MySQL 8.0, the grant tables are InnoDB (transactional) tables. Previously, these were MyISAM (nontransactional) tables. The change of grant-table storage engine underlies an accompanying change in MySQL 8.0 to the behavior of account-management statements such as CREATE USER and GRANT. Previously, an account-management statement that named multiple users could succeed for some users and fail for others. The statements are now transactional and either succeed for all named users or roll back and have no effect if any error occurs."

[Dmitrii Shcherbakov (dmitriis)]

The upgrade guide does not mention any steps to change the engine for mysql grant tables: https://docs.openstack.org/project-deploy-guide/charm-deployment-guide/latest/percona-series-upgrade-to-focal.html

Maybe we are missing those steps - need to do some further clarifications.

[Dmitrii Shcherbakov (dmitriis)]

In the meantime, it would be good to get an output of

use mysql;
SHOW TABLE STATUS LIKE 'user';

It should contain something like this (note: Engine -> InnoDB):

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+---------------------------------------+-----------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+---------------------------------------+-----------------------------+
| user | InnoDB | 10 | Dynamic | 21 | 780 | 16384 | 0 | 0 | 4194304 | NULL | 2021-07-13 13:46:37 | 2021-07-13 13:51:24 | NULL | utf8_bin | NULL | row_format=DYNAMIC stats_persistent=0 | Users and global privileges |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+---------------------------------------+-----------------------------+
1 row in set (0.01 sec)

[David Ames (thedac)]

Dmitrii's hint got me to thinking. Was this a full backup of the percona DB? i.e. including mysql and performance_schema?

If so, that may be what is causing the problem. We recommend only backing up (or restoring) the openstack related DBs.

juju run-action --wait percona-cluster mysqldump keystone,nova,nova_api,cinder,glance, .... etc.

Adding our documentation to the bug to make this clearer.

I will also test to validate this.

If this is the case, we might consider rebuilding the mysql-innodb-cluster and restoring *only* openstack related DBs.

[David Ames (thedac)]

Theory confirmed.

Test setup:

* Follow the procedure from documentation [0]
* At mysql dump phase dump the whole DB (including mysql and performance_schema)
* Also dump just keystone and glance DBs
* Restore full mysqldump
  * Errors as expected with: ERROR 3098 (HY000) at line 95: The table does not comply with the requirements by an external plugin.
* Restore just keystone and glance succeeds
* Add keystone-mysql-router keystone relation
  * db-router relation errors as expected with: MySQLdb._exceptions.OperationalError: (1785, 'Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.')

Recovery

* Remove application to app-mysql-rotuer relations
* Blow away the mysql-innodb-cluster
  * juju remove-application --force mysql-innodb-cluster
  * juju remove-unit --force mysql-innodb-cluster/<ID of errored unit>
* Redeploy clean three node mysql-innodb-cluster
* Restore only the OpenStack DBs
* Add application to app-mysql-router relations
* Success

So the issue was the restoration of the mysql and or performance_schema databases that corrupted the innodb cluster. We must start with a clean version of those databases.

We will update documentation to avoid this in the future.

[0] https://docs.openstack.org/project-deploy-guide/charm-deployment-guide/latest/percona-series-upgrade-to-focal.html

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/c/openstack/charm-deployment-guide/+/800839

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/charm-deployment-guide/+/800839
Committed: https://opendev.org/openstack/charm-deployment-guide/commit/2aa69a87a4e9ab9a81e808bd4168144790426c4f
Submitter: "Zuul (22348)"
Branch: master

commit 2aa69a87a4e9ab9a81e808bd4168144790426c4f
Author: David Ames <email address hidden>
Date: Wed Jul 14 14:27:58 2021 -0700

    Document around LP Bug#1936210

    Change-Id: I29323d36ceaa0eaea771a21f10c3bed1d13b2443
    Closes-Bug: #1936210