Skip to content

nixos/mysql: add statements option to replace initialDatabases, ensureDatabases, and ensureUsers options#94048

Closed
aanderse wants to merge 2 commits intoNixOS:masterfrom
aanderse:mysql-statements
Closed

nixos/mysql: add statements option to replace initialDatabases, ensureDatabases, and ensureUsers options#94048
aanderse wants to merge 2 commits intoNixOS:masterfrom
aanderse:mysql-statements

Conversation

@aanderse
Copy link
Copy Markdown
Member

@aanderse aanderse commented Jul 28, 2020 •
edited
Loading

Motivation for this change

I would like to proceed with #84146 but the mysql module is more pressing to my immediate needs. Specifically I can't move ahead with #87712 using the ensureDatabase options as there is no ability to specify character sets, etc...

If this is merged I would follow up by rebasing #87712 and getting it in shape for merge as well.

NOTE: I haven't written release notes for this yet, waiting on general approval of this PR first.

Things done
  • Tested using sandboxing (nix.useSandbox on NixOS, or option sandbox in nix.conf on non-NixOS linux)
  • Built on platform(s)
    • NixOS
    • macOS
    • other Linux distributions
  • Tested via one or more NixOS test(s) if existing and applicable for the change (look inside nixos/tests)
  • Tested compilation of all pkgs that depend on this change using nix-shell -p nixpkgs-review --run "nixpkgs-review wip"
  • Tested execution of all binary files (usually in ./result/bin/)
  • Determined the impact on package closure size (by running nix path-info -S before and after)
  • Ensured that relevant documentation is up to date
  • Fits CONTRIBUTING.md.

@ofborg ofborg bot added 6.topic: nixos Issues or PRs affecting NixOS modules, or package usability issues specific to NixOS 8.has: module (update) This PR changes an existing module in `nixos/` 10.rebuild-darwin: 0 This PR does not cause any packages to rebuild on Darwin. 10.rebuild-linux: 1-10 This PR causes between 1 and 10 packages to rebuild on Linux. 10.rebuild-linux: 1 This PR causes 1 package to rebuild on Linux. labels Jul 28, 2020
@aanderse
Copy link
Copy Markdown
Member Author

aanderse commented Jul 28, 2020

@GrahamcOfBorg test mysql mysql-replication
@GrahamcOfBorg test mysql-autobackup mysql-backup
@GrahamcOfBorg test mariadb-galera-mariabackup mariadb-galera-rsync

@aanderse
Copy link
Copy Markdown
Member Author

aanderse commented Jul 28, 2020

@GrahamcOfBorg test engelsystem gitea.mysql limesurvey
@GrahamcOfBorg test mediawiki moodle
@GrahamcOfBorg test redmine.mysql sympa
@GrahamcOfBorg test wordpress zoneminder

@aanderse aanderse marked this pull request as ready for review July 28, 2020 12:30
@Izorkin
Copy link
Copy Markdown
Contributor

Izorkin commented Jul 30, 2020

With this configuration not automatic loading auth_socket plugin.

{ config, pkgs, lib, ... }:
{
  services.mysql = {
    enable = true;
    package = pkgs.mysql57;
    dataDir = "/var/data/db/mysql";
    statements =
      let
        unix_socket = if (lib.getName config.services.mysql.package == "mariadb-server") then "unix_socket" else "auth_socket";
      in ''
        CREATE USER 'passworduser'@'localhost' IDENTIFIED with ${unix_socket};
    '';
  };
}

May leave this option?

diff --git a/nixos/modules/services/databases/mysql.nix b/nixos/modules/services/databases/mysql.nix
index b13185d3939..91bfbcc470e 100644
--- a/nixos/modules/services/databases/mysql.nix
+++ b/nixos/modules/services/databases/mysql.nix
@@ -246,6 +246,9 @@ in
         server-id = cfg.replication.serverId;
         binlog-ignore-db = [ "information_schema" "performance_schema" "mysql" ];
       })
+      (mkIf (!isMariaDB) {
+        plugin-load-add = "auth_socket.so";
+      })
     ];

     users.users.mysql = {

How to simplify configuration to:

{ config, pkgs, lib, ... }:
{
  services.mysql = {
    enable = true;
    package = pkgs.mysql57;
    dataDir = "/var/data/db/mysql";
    statements = ''
        CREATE USER 'passworduser'@'localhost' IDENTIFIED with ${socketAuth};
    '';
  };
}

and automatic replace ${socketAuth} to "unix_socket" or "auth_socket"

@aanderse
Copy link
Copy Markdown
Member Author

aanderse commented Aug 1, 2020

@Izorkin thanks for catching that. It was a mistake that the plugin-load-add line was removed. I think most simple way to handle "auth_socket" vs "unix_socket" is let unix_socket = if (lib.getName config.services.mysql.package == "mariadb-server") then "unix_socket" else "auth_socket"; in ... 🤷‍♂️

@Izorkin
Copy link
Copy Markdown
Contributor

Izorkin commented Aug 3, 2020

This code:

let
  unix_socket = if (lib.getName config.services.mysql.package == "mariadb-server") then "unix_socket" else "auth_socket";
in

is it possible to move this code to nixos/modules/services/databases/mysql.nix?
So that as a result in nixos/modules/services/backup/automysqlbackup.nix it would be something like this:

      statements = ''
          create database if not exists `${cfg.database.name}`;
          create user if not exists '${cfg.database.user}'@'localhost' identified with ${unix_socket};
          grant all privileges on `${cfg.database.name}`.* to '${cfg.database.user}'@'localhost';
        '';

@aanderse
Copy link
Copy Markdown
Member Author

aanderse commented Aug 3, 2020

@Izorkin you can't scope a let across modules like that or provide a variable to mysql/mariadb for authentication plugin types so I don't see a great way to achieve this... only several less than ideal ways.

@aanderse aanderse mentioned this pull request Aug 3, 2020
Closed
10 tasks
@aanderse aanderse marked this pull request as draft August 7, 2020 12:52
@aanderse aanderse closed this Aug 13, 2020
@aanderse aanderse mentioned this pull request Dec 21, 2020
Closed
10 tasks
@aanderse aanderse deleted the mysql-statements branch December 26, 2023 16:09
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@florianjacob florianjacob Awaiting requested review from florianjacob

@Izorkin Izorkin Awaiting requested review from Izorkin

@flokli flokli Awaiting requested review from flokli

@grahamc grahamc Awaiting requested review from grahamc

@Ma27 Ma27 Awaiting requested review from Ma27

@talyz talyz Awaiting requested review from talyz

@danbst danbst Awaiting requested review from danbst

@aszlig aszlig Awaiting requested review from aszlig

@jtojnar jtojnar Awaiting requested review from jtojnar

@peterhoeg peterhoeg Awaiting requested review from peterhoeg

Assignees

No one assigned

Labels

6.topic: nixos Issues or PRs affecting NixOS modules, or package usability issues specific to NixOS 8.has: module (update) This PR changes an existing module in `nixos/` 10.rebuild-darwin: 0 This PR does not cause any packages to rebuild on Darwin. 10.rebuild-linux: 1-10 This PR causes between 1 and 10 packages to rebuild on Linux. 10.rebuild-linux: 1 This PR causes 1 package to rebuild on Linux.

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

2 participants

@aanderse @Izorkin