[jey]

It seems like that usage isn't supported by upstream MySQL either. Quoting from https://dev.mysql.com/doc/refman/8.0/en/grant-tables.html:

> Direct modification of grant tables using statements such as INSERT, UPDATE, or DELETE is discouraged and done at your own risk. The server is free to ignore rows that become malformed as a result of such modifications.

[hellcow]

Discouraged, but not broken--except in GCP after a silent update. GCP now blocks changes from being made to those tables entirely, breaking with MySQL's behavior, as well as GCP's own earlier behavior.

We have battle-tested tooling that makes this work brilliantly in all other environments. Updating the _priv tables directly is the only way to modify privileges within a transaction in MySQL.

[stingraycharles]

Sounds like you took a calculated risk and it backfired. Google Cloud SQL even has it in their documentation that they automatically do these upgrades, and you were relying on unsupported behavior of the database. Especially when it’s security related, I can easily imagine something breaking in a minor release.

You’re using unsupported functionality that may break at any time, and you are using a managed database service that automatically updates. I don’t think you would have had a better time with any of the other cloud providers.

[hellcow]

It's not unsupported. It's literally documented by the MySQL project, existed for more than 5 years, was supported by Google, and continues to be supported by MySQL.

Google broke it without warning. That's a breaking change to your database, in production, 5 years after v5.7's release when you're fully locked in without so much as even a version bump.

Microsoft didn't do this. AWS didn't do this. Google did this.

Let this be a lesson to everyone that Google can and will break your critical production systems even years after they're operating perfectly, and they'll provide no warning, no explanation, and no fix.

[stingraycharles]

If it's specifically a change Google did, and upstream MySQL still supports this behavior, I'm inclined to agree with you that this is a surprise.

Having said that, it is worth emphasizing that you relied on unsupported / discouraged behavior that "is at your own risk". This isn't just any random feature Google disabled, it's security-related behavior that is discouraged to rely upon by MySQL themselves. This is a big caveat, and is something that I would never recommend anyone to do, especially not when you're planning on outsourcing the management + maintenance of your database.

[hrktb]

I still find it's an interesting aspect of using GCP. For instance I am no Postgres expert, and while I don't directly do anything fancy with it, I have no guarantee that some library I use doesn't rely on some "at your own risk" feature.

What this means is, while thorough testing can give confidence it works at some point, there is no real guarantee it will continue working even without any changes on our end.

[simonebrunozzi]

> Having said that, it is worth emphasizing that you relied on unsupported / discouraged behavior that "is at your own risk".

Absolutely this. GCP has no responsibility here.

[sjtindell]

Yeah regardless of these other points about the risk you took, the lack of response or initial communication is not good at all. Seems to be a pattern with Google. Sorry to hear you sunk so much time in chasing that change down.

[ericpauley]

> Updating the _priv tables directly is the only way to modify privileges within a transaction in MySQL.

Out of genuine curiosity, why do you want to do that? Is the transactional consistency of the applied permissions critical?

[hellcow]

Yes. When I'm applying large changes to permissions across many users and many tables/columns, which happens after every migration, then I absolutely need transaction guarantees. I cannot risk having a half-applied change leaving the database and its users in an unknown state.

[ericpauley]

Could you write your permission changes/rollback logic to be idempotent? In this case you could always push the changes to completion, and reason about whether each intermediate state is safe.

[dekhn]

An alternative to this is to snapshot the database, and test the permissions change on the snapshot. Not as good as a transaction, but often good enough to proceed with the prod operation.

[dvasdekis]

I thought Flyway did this? Have you tried it?

[logicchains]

>GCP now blocks changes from being made to those tables entirely, breaking with MySQL's behavior, as well as GCP's own earlier behavior.

There seems to be a common pattern across Google's services of trying to force best practices on users at the expense of breakages. I suspect though that users are more likely to think "screw you Google, stop breaking my stuff" than "thank you Google for forcing me to follow best practices".