MySQL configuration is not idempotent

[jwyuen]

Hello! Just wanted to express some concern over the mysql configuration (at least in a flake) where the settings are not idempotent which can cause issues if you want to use the flake as the source of configuration truth. This is also especially true given the context that this is normally how configuration works in Nix itself.

At the same time I can see two sides of this where ppl using it as purely for dev purposes would find it more useful to have the settings be idempotent vs others who might (wrongly?) use this in production and would not want to accidentally blow away database settings. Perhaps a flag can be set to switch behavior between the two.

As for reproducing this situation, you can see the problem rearing itself when attempting to modify user permissions from the flake when I've already ran the server before and the user already has some permissions set.

[sandydoo]

you can see the problem rearing itself when attempting to modify user permissions from the flake when I've already ran the server before and the user already has some permissions set.

Could you provide an example? I minimal example that we can run to reproduce the issue would be ideal.

What I think you're saying is that the new permissions are not applied, correct? In other words, we do not track the current database state, w.r.t. databases, users, permissions, and do not apply the difference.

[jwyuen]

Sure, I have a flake that has the following mysql config:

services.mysql = {
                enable = true;
                package = pkgs.mysql84;
                settings = {
                  mysqld = {
                    #innodb_force_recovery = 6;
                  };
                };
                initialDatabases = [
                  {
                    name = "somedb";
                  }
                ];
                ensureUsers = [
                  {
                    name = "someuser";
                    password = "somepassword";
                    ensurePermissions = {
                      "somedb" = "SELECT, LOCK TABLES";
                      "*.*" = "SELECT, LOCK TABLES";
                    };
                  }
                ];
              };

Say you run this once and your mysql db is set up. Then you decide you want to give your "someuser" different privileges such as ALL PRIVILEGES so under "ensurePermissions" you change the next line to:

"somedb" = "ALL PRIVILEGES";

You run the flake again and check but it appears the updated permissions will fail to apply...