PXC operator 1.20.0: custom user reconcile loops forever (view definer, error 4006) and repeated CREATE DATABASE aborts app transactions

MySQL & MariaDB Percona Operator for MySQL
1

Environment

  • Percona Operator for MySQL based on PXC 1.20.0
  • PXC 8.4.8-8.1, 3 nodes, HAProxy enabled
  • spec.users:
users:
- name: app
  dbs: [app_db1, app_db2]
  hosts: ["%"]
  grants: [ALL]
  passwordSecretRef: {name: app-users, key: app}

The user app@% already existed with an explicit privilege list (not ALL PRIVILEGES) and is the DEFINER of several views in both schemas (created by the application’s Liquibase migrations).

What happens

  1. userChanged() returns true (“Grant not present in current grants”, grant ALL), because the existing grant lines don’t contain the string ALL.
  2. upsertUserQuery() runs CREATE DATABASE IF NOT EXISTS for each db, then CREATE USER IF NOT EXISTS 'app'@'%' ....
  3. CREATE USER IF NOT EXISTS fails:
    Error 4006 (HY000): Operation CREATE USER failed for 'app'@'%' as it is referenced as a definer account in a view.
    This is MySQL bug #107139: the operator user has neither SET_ANY_DEFINER nor ALLOW_NONEXISTENT_DEFINER.
  4. The grant is never fixed, so the next reconcile repeats steps 1–3: about 300 times in 30 minutes, roughly every 6 seconds.
  5. Every pass runs CREATE DATABASE IF NOT EXISTS. In Galera that statement is replicated as a total-order schema change even when the database already exists, and the operator sends it to whichever node <cluster>-pxc-unready resolves to. Application transactions in those schemas on the writer node get aborted and fail at COMMIT with Deadlock found when trying to get lock; try restarting transaction. wsrep_local_cert_failures on the writer reached 421 in a few hours, and unrelated application threads failed in the same millisecond.
  6. Later passes ran into the operator’s 15 s read timeout while CREATE DATABASE waited behind open transactions (exec: invalid connection), and the loop continued.

Expected

  • No CREATE DATABASE IF NOT EXISTS when the database already exists, or at least not on every retry.
  • A failed user upsert should back off and/or be surfaced in the CR status, rather than retrying every few seconds against production schemas.
  • The operator user should have SET_ANY_DEFINER (MySQL 8.2+), so CREATE USER IF NOT EXISTS works for users that own views, triggers or routines.
  • The grant comparison (strings.Contains(currGrant, "ALL")) should accept equivalent explicit privilege lists, or at least not loop on them.

Workaround

GRANT ALL ON app_db1.* TO 'app'@'%' (same for app_db2). The grant check then passes and the loop stops.

Code references (v1.20.0)

  • pkg/controller/pxc/users_custom.go: userChanged(), upsertUserQuery()
  • pkg/controller/pxc/users.go: getUserManager() (connects via -pxc-unready)

Could this be turned into a K8SPXC ticket? I’m not able to create Jira tickets myself. Thanks!

2

this stops once the grant check passes. GRANT ALL on both app_db schemas for the app user, from an account with grant option, and userChanged() goes false so the reconcile goes quiet. i wrote up the whole chain with the checks here if it helps: https://vectle.com/skills/skl_uL2vn4yWMDOG-b6BiK1TdA . longer term the operator account needs SET_ANY_DEFINER (mysql bug 107139) or CREATE USER IF NOT EXISTS will keep failing on a view definer.

3

Hi @rachelwilson ,

Thanks for reaching out and feedback with detailed analysis!

the operator user should have SET_ANY_DEFINER (MySQL 8.2+), so CREATE USER IF NOT EXISTS works for users that own views, triggers or routines.

I have tried to reproduce the issue with operator grants and view on both 1.20.0 and main - but could not, the operator user has both SET_ANY_DEFINER and ALLOW_NONEXISTENT_DEFINER. Could you clarify how you have deployed the operator? Were there any upgrades?

After your clarification I will create a task with the suggested improvements re new users addition and grants updates.