cloudfoundry/pxc-release

Missing 'LOCK TABLES' privilege to database user

aqan213 opened this issue · 2 comments

aqan213 commented

Feature Request Info

Problem you are trying to solve

I'm trying to use the pxc-release in app-autoscaler-release, I added a database 'autoscaler' and username is 'autoscaler',

seeded_databases:
    - name: autoscaler
      password: ((database_password))
      username: autoscaler

but the user has no 'LOCK TABLES' privilege and it failed to deploy the app-autoscaler release.
Is it possible to add the 'LOCK TABLES' privilege to the database users or is there a user having
the 'LOCK TABLES' privilege?

cf-gitbot commented

We have created an issue in Pivotal Tracker to manage this:

https://www.pivotaltracker.com/story/show/171177794

The labels on this github issue will be updated when the story is started.

abg commented

In the seeded_databases implementation, pxc-release intentionally leave off the "LOCK TABLES" privilege to discourage users from relying on potentially broken behavior. Locks are not distributed in a Galera cluster and relying on table-level locks can lead to buggy application behavior in the event of a failover / switchover.

In Percona XtraDB Cluster 8.0 (used in pxc-release v1.0+), pxc-strict-mode defaults to "ENFORCING" which also disables lock tables for similar reasons.