path: root/modules/apps/mariadb/default.nix

{ config, pkgs, lib, ... }:
let
  inherit (builtins)
    attrNames filter isBool isInt isList isPath isString length replaceStrings
    toString ;

  inherit (lib)
    mkOption mkEnableOption mkIf types toUpper unique
    optionalString hasPrefix concatStringsSep splitString flatten
    concatMapStrings concatMapStringsSep concatStrings mapAttrsToList filterAttrs;

  inherit (types)
    attrsOf either int lines listOf package str submodule ;

  cfg = config.nixsap.apps.mariadb;

  getDirs = l: filter (p: p != null) l;
  dirsOf = l: map dirOf (getDirs l);
  mydirs = with cfg.mysqld; unique ([
          datadir
        ] ++ dirsOf [
          log_bin
          relay_log
        ] ++ getDirs [
          tokudb_data_dir
          tokudb_log_dir
          tokudb_tmp_dir
        ]);

  explicit = filterAttrs (n: v: n != "_module" && v != null);
  hasMasters = (explicit cfg.replicate) != {};
  concatNonEmpty = sep: list: concatStringsSep sep (filter (s: s != "") list);

  # XXX /run/mysqld/mysqld.sock is the default socket
  rundir   = "/run/mysqld";
  initFile = pkgs.writeText "init" ''
    CREATE USER IF NOT EXISTS '${cfg.user}'@'localhost' IDENTIFIED VIA unix_socket;
    GRANT ALL ON *.* TO '${cfg.user}'@'localhost' WITH GRANT OPTION;
  '';

  mkIgnoreTablesList = quotes: { databases, ignore-tables, ... }:
    let
      q = optionalString quotes "`";
      hasDot = t: 2 == length (splitString "." t);
      all-tbl = filter (t: ! hasDot t) ignore-tables;
      db-tbl = (filter hasDot ignore-tables) ++
                flatten (map (t: map (d: "${q}${d}${q}.${q}${t}${q}") databases) all-tbl);
    in unique db-tbl;

  mkEntry = name: value:
    let
      showList = l: concatMapStringsSep "," (toString) (unique l);
      optimizer_switch = a:
        showList (mapAttrsToList (n: v:
            "${n}=${if v then "on" else "off"}"
          ) (explicit a));
      plugin_load_add = aa: concatMapStringsSep "\n" (l: "plugin_load_add = ${l}") (unique aa);
    in if hasPrefix "skip" name then (optionalString value name)
       else if name == "optimizer_switch" then "${name} = ${optimizer_switch value}"
       else if name == "plugin_load_add" then plugin_load_add value
       else if isBool value then "${name} = ${if value then "ON" else "OFF"}"
       else if isInt value then "${name} = ${toString value}"
       else if isList value then "${name} = ${showList value}"
       else if isString value then "${name} = ${value}"
       else abort "Unrecognized option ${name}";

  show = n: v:
         if isBool v then (if v then "1" else "0")
    else if isInt v then toString v
    else if isString v then "'${v}'"
    else if isPath v then "'${v}'"
    else abort "Unrecognized option ${n}";

  mkReplOpt = ch: args@{databases, ignore-databases, ...}:
    let wild_do_table = concatMapStringsSep "\n" (d:
                    "${ch}.replicate_wild_do_table = ${d}.%"
                   ) databases;
        ignore_table = concatMapStringsSep "\n" (t:
                    "${ch}.replicate_ignore_table = ${t}"
                   ) (mkIgnoreTablesList false args);
        ignore_db = concatMapStringsSep "\n" (d:
                    "${ch}.replicate_ignore_db = ${d}"
                   ) ignore-databases;
    in ''
      ${ignore_db}
      ${ignore_table}
      ${wild_do_table}
    '';

  mkDynamicReplOpt = ch: args@{databases, ignore-databases, ...}:
    ''
      SET default_master_connection = "${ch}";
      SET GLOBAL replicate_ignore_db = "${concatStringsSep "," ignore-databases}";
      SET GLOBAL replicate_wild_do_table = "${concatMapStringsSep "," (d: "${d}.%") databases}";
      SET GLOBAL replicate_ignore_table = "${concatMapStringsSep "," (t: "${t}") (mkIgnoreTablesList false args)}";
    '';

  replCnf = pkgs.writeText "mysqld-repl.cnf" ''
      [mysqld]
      ${concatNonEmpty "\n" (mapAttrsToList mkReplOpt (explicit cfg.replicate))}
    '';

  mysqldCnf =
    if hasMasters && (cfg.mysqld.server_id == null || cfg.mysqld.server_id < 1)
    then throw "Misconfigured slave: server_id was not set to a positive integer"
    else pkgs.writeText "mysqld.cnf" ''
      [mysqld]
      basedir     = ${cfg.package}
      init_file   = ${initFile}
      pid_file    = ${rundir}/mysqld.pid
      plugin_load = auth_socket

      ${concatNonEmpty "\n" (mapAttrsToList mkEntry (explicit cfg.mysqld))}
      ${optionalString hasMasters "!include ${replCnf}"}
    '';

  await = pkgs.writeBashScript "await" ''
    count=0
    while ! ${cfg.package}/bin/mysql -e ';' 2>/dev/null; do
      if ! (( count % 60 )); then
        ${cfg.package}/bin/mysql -e ';'
      fi
      sleep 5s
      (( ++count ))
    done
  '';

  conf = pkgs.writeBashScriptBin "mariadb-conf"
    ''
      set -euo pipefail
      trap "" SIGHUP
      ${await}
      ${optionalString (cfg.configure' != "") ''
        tmp=$(mktemp)
        trap 'rm -f "$tmp"' EXIT
        ${cfg.package}/bin/mysql -N mysql < ${pkgs.writeText "mariadb-make-conf2.sql" cfg.configure'} > "$tmp"
        ${cfg.package}/bin/mysql -v mysql < "$tmp"
      ''}
      ${cfg.package}/bin/mysql -v mysql < ${pkgs.writeText "mariadb-conf.sql" cfg.configure}
    '';

  maintenance = pkgs.writeBashScriptBin "mariadb-maint" ''
    set -euo pipefail
    trap "" SIGHUP
    ${await}
    ${optionalString hasMasters "${cfg.package}/bin/mysql -e 'STOP ALL SLAVES SQL_THREAD'"}
    ${cfg.package}/bin/mysql_upgrade --user=${cfg.user}
    ${cfg.package}/bin/mysql_tzinfo_to_sql "$TZDIR" | ${cfg.package}/bin/mysql mysql
    ${cfg.package}/bin/mysql mysql < ${./procedures.sql}

    cat <<'__SQL__' | ${cfg.package}/bin/mysql -N mysql | ${cfg.package}/bin/mysql -v mysql
    SELECT CONCAT("DROP USER IF EXISTS '", User, "'@'", Host, "';")
    FROM user
    WHERE User IN ('root', ${"''"})
       OR (User='${cfg.user}' AND Host <> 'localhost')
       ;
    __SQL__

    cat <<'__SQL__' | ${cfg.package}/bin/mysql -v mysql
    DROP DATABASE IF EXISTS test;
    ${concatMapStrings (db: ''
    CREATE DATABASE IF NOT EXISTS `${db}`;
    '') cfg.databases}
    __SQL__

    ${optionalString hasMasters "${cfg.package}/bin/mysql -e 'START ALL SLAVES'"}
  '';

  changeMaster =
    let
      do = ch: opts:
        let
          masterOptions = filterAttrs (n: _: n != "password-file") (explicit opts.master);
          masterOptionName = n: ''MASTER_${toUpper (replaceStrings ["-"] ["_"] n)}'';
          changeMaster = "CHANGE MASTER '${ch}' TO " + (concatStringsSep ", " (mapAttrsToList (n: v:
              "${masterOptionName n}=${show n v}") masterOptions)) + ";";
        in pkgs.writeBashScript "change-master-${ch}" ''
          cat <<'__SQL__'
          ${changeMaster}
          ${mkDynamicReplOpt ch opts}
          __SQL__
          ${optionalString (opts.master.password-file != null) ''
            pwd=$(cat '${opts.master.password-file}')
            echo "CHANGE MASTER '${ch}' TO MASTER_PASSWORD='$pwd';"''}
        '';

    in pkgs.writeBashScript "changeMaster" (
      concatStringsSep "\n" (mapAttrsToList (ch: opts: ''
        [ "$1" = ${ch} ] && exec ${do ch opts}
      '') (explicit cfg.replicate))
    );

  importDump =
    let
      do = ch: opts:
        let
          cnf = "${rundir}/master-${ch}.cnf";
          mysqldumpOptions = filterAttrs (n: _: n != "password-file" && n != "path")
            (explicit opts.mysqldump);
          binary = if opts.mysqldump.path != null then opts.mysqldump.path else "${cfg.package}/bin/mysqldump";
          mysqldump = concatStringsSep " " (
              [ binary "--defaults-file=${cnf}" "--skip-comments" "--force" ]
              ++ mapAttrsToList (n: v: "--${n}=${show n v}") mysqldumpOptions);
          databases = concatStringsSep " " ([ "--databases" ] ++ opts.databases);
          ignore-tables = concatMapStringsSep " " (t: "--ignore-table=${t}") (mkIgnoreTablesList false opts);
        in pkgs.writeBashScript "import-${ch}" ''
          set -euo pipefail
          touch '${cnf}'
          trap "rm -f '${cnf}'" EXIT
          trap "exit 255" TERM INT
          chmod 0600 '${cnf}'
          ${optionalString (opts.mysqldump.password-file != null) ''
            printf '[client]\npassword=' > '${cnf}'
            cat '${opts.mysqldump.password-file}' >> '${cnf}'
          ''}
          echo 'SET default_master_connection="${ch}";'
          ${optionalString (!cfg.mysqld.log_slave_updates) "echo 'SET sql_log_bin=0;'"}
          ${mysqldump} --master-data=0 --no-data ${databases}
          ${mysqldump} --master-data=1 ${ignore-tables} ${databases}
        '';
    in pkgs.writeBashScript "importDump" (
      concatStringsSep "\n" (mapAttrsToList (ch: opts: ''
        [ "$1" = ${ch} ] && exec ${do ch opts}
      '') (explicit cfg.replicate))
    );

  watchdog = pkgs.writeBashScript "slave-watchdog"
    (import ./slave-watchdog.nix {inherit cfg importDump changeMaster;});

  slaves =
    let
      channels = attrNames (explicit cfg.replicate);
      truncate = ch: concatMapStringsSep "\n"
        (t: "TRUNCATE TABLE ${t};") (mkIgnoreTablesList true cfg.replicate.${ch});
      truncateIgnored = pkgs.writeText "truncate.sql"
        (concatMapStringsSep "\n" truncate channels);
      old = "${rundir}/channels";
      new = pkgs.writeText "channels.new" (concatMapStringsSep "\n"
        (ch: "${ch}:${cfg.replicate.${ch}.master.host}") channels);
    in pkgs.writeBashScriptBin "mariadb-slaves" ''
      set -euo pipefail
      rm -f ${rundir}/*.lock
      ${await}
      touch ${old}
      chmod 0600 ${old}
      trap 'rm -f ${old}' EXIT
      ${cfg.package}/bin/mysql -e 'SHOW ALL SLAVES STATUS\G' \
        | awk '/Connection_name:/ {printf $2 ":"}; /Master_Host:/ {print $2}' \
        | sort > ${old}
      obsolete=$(comm -23 ${old} ${new} | cut -d: -f1)
      for ch in $obsolete; do
        echo "Deleting obsolete slave $ch"
        ${cfg.package}/bin/mysql -e "CALL mysql.resetSlave('$ch')"
      done
      ${optionalString hasMasters ''
        ${cfg.package}/bin/mysql -f < ${truncateIgnored} || echo '(errors ignored)' >&2
        export PARALLEL_SHELL=${pkgs.bash}/bin/bash
        export HOME='${rundir}'
        {
          while true; do
            printf "${concatStringsSep "\\n" channels}\n"
            sleep 10m
          done
        } | parallel \
            --halt-on-error 0 \
            --jobs '${toString cfg.slaveWatchdogs}' \
            --line-buffer \
            --no-notice \
            --tagstr '* {}:' \
            'flock -E 0 -n ${rundir}/master-{}.lock ${watchdog} {}'
        ''
      }
    '';

  all-keys = flatten (
      mapAttrsToList (ch: {master, mysqldump, ...}:
        [ master.password-file
          master.ssl-key
          mysqldump.password-file
          mysqldump.ssl-key
        ]) (explicit cfg.replicate)
    ) ++ [ cfg.mysqld.ssl_key ];

in {

  imports = [ ./roles.nix ];

  options.nixsap = {
    apps.mariadb = {
      enable = mkEnableOption "MySQL";

      user = mkOption {
        description = "User to run as";
        default = "mariadb";
        type = str;
      };

      package = mkOption {
        description = "MariaDB Package (10.1.x)";
        type = package;
        default = pkgs.mariadb;
      };

      replicate = mkOption {
        type = attrsOf (submodule (import ./replicate.nix));
        default = {};
        description = "Replication channels";
      };

      slaveWatchdogs = mkOption {
        type = either str int;
        default = "80%";
        description = ''
          Number of parallel slave monitoring and recovery processes.
          In the format of GNU Parallel, e. g. "100%", -1. +3, 7, etc.
        '';
      };

      mysqld = mkOption {
        type = submodule (import ./mysqld.nix);
        default = {};
        description = "mysqld options";
      };

      databases = mkOption {
        description = "Databases to create if not exist";
        type = listOf str;
        default = [];
      };

      configure = mkOption {
        type = lines;
        default = "";
        description = ''
          Any SQL statements to execute, typically GRANT / REVOKE etc.
          This is executed in contect of the `mysql` database.
        '';
        example = ''
          CREATE USER IF NOT EXISTS 'icinga'@'%' IDENTIFIED BY PASSWORD '*AC8C3BDA823EECFF90A8381D554232C7620345B3';
          GRANT USAGE ON *.* TO 'icinga'@'%' REQUIRE SSL;
          REVOKE ALL, GRANT OPTION FROM 'icinga'@'%';
          GRANT PROCESS, REPLICATION CLIENT, SHOW DATABASES ON *.* TO 'icinga'@'%';
          GRANT SELECT ON mysql.* TO 'icinga'@'%';
        '';
      };

      configure' = mkOption {
        type = lines;
        default = "";
        internal = true;
        description = ''
          SQL statements that generate other SQL statements to be executed.
          Those generated statements will be executed before `configure`.
        '';
        example = ''
          SELECT CONCAT('GRANT SELECT ON `', table_schema, '`.`', table_name, '` TO \'_oms_package_vn\';')
          FROM information_schema.tables WHERE
          table_schema LIKE '%oms_live_vn' AND
          table_name LIKE 'oms_package%';
        '';
      };
    };
  };

  config = mkIf cfg.enable {
    environment.systemPackages = [ cfg.package ];
    nixsap.system.users.daemons = [ cfg.user ];
    nixsap.deployment.keyrings.${cfg.user} = all-keys;

    nixsap.apps.mariadb.configure = concatMapStringsSep "\n"
      (n: ''
        CREATE USER IF NOT EXISTS '${n}'@'localhost' IDENTIFIED VIA unix_socket;
        REVOKE ALL, GRANT OPTION FROM '${n}'@'localhost';
        GRANT SELECT, EXECUTE ON mysql.* TO '${n}'@'localhost';
        GRANT PROCESS, REPLICATION CLIENT, SHOW DATABASES, SHOW VIEW ON *.* TO '${n}'@'localhost';
        '') config.nixsap.system.users.sysops;

    systemd.services.mariadb-slaves = {
      description = "MariaDB slaves watchdog";
      requires = [ "mariadb.service" ];
      after = [ "mariadb.service" "mariadb-maintenance.service" ];
      wantedBy = [ "multi-user.target" ];
      path = with pkgs; [ gnused gawk utillinux parallel ];
      serviceConfig = {
        ExecStart = "${slaves}/bin/mariadb-slaves";
        User = cfg.user;
      } // (if hasMasters
        then {
          Restart = "always";
        }
        else {
          Type = "oneshot";
        });
    };

    systemd.services.mariadb-maintenance = {
      description = "MariaDB maintenance";
      after = [ "mariadb.service" ];
      wantedBy = [ "multi-user.target" ];
      serviceConfig = {
        ExecStart = "${maintenance}/bin/mariadb-maint";
        User = cfg.user;
        Type = "oneshot";
        RemainAfterExit = true;
      };
    };

    systemd.services.mariadb-conf = {
      description = "MariaDB configuration";
      after = [ "mariadb.service" "mariadb-maintenance.service" ];
      wantedBy = [ "multi-user.target" ];
      serviceConfig = {
        ExecStart = "${conf}/bin/mariadb-conf";
        User = cfg.user;
        Type = "oneshot";
        RemainAfterExit = true;
      };
    };

    systemd.services.mariadb = {
      description = "MariaDB server";
      wantedBy = [ "multi-user.target" ];
      wants = [ "keys.target" ];
      after = [ "keys.target" "network.target" "local-fs.target" ];
      path = [ pkgs.inetutils ];
      environment = {
        UMASK = "0640";
        UMASK_DIR = " 0750";
      };
      preStart = ''
        mkdir -p '${rundir}'
        chmod 0700 '${rundir}'
        mkdir -p ${concatMapStringsSep " " (d: "'${d}'") mydirs}
        if [ ! -f '${cfg.mysqld.datadir}/mysql/user.MYI' ]; then
          rm -rf '${cfg.mysqld.datadir}/mysql'
          ${cfg.package}/bin/mysql_install_db --defaults-file=${mysqldCnf} --basedir='${cfg.package}'
        fi
        chown -Rc '${cfg.user}':$(id -g -n '${cfg.user}') '${rundir}' ${concatMapStringsSep " " (d: "'${d}'") mydirs}
        chmod -Rc u=rwX,g=rX,o= ${concatMapStringsSep " " (d: "'${d}'") mydirs}
        chmod 0755 '${rundir}'
      '';

      serviceConfig = {
        ExecStart = "${cfg.package}/bin/mysqld --defaults-file=${mysqldCnf}";
        PermissionsStartOnly = true;
        User = cfg.user;
        Restart = "always";
        TimeoutSec = 0; # XXX it can take hours to shutdown, and much more to start if you kill shutdown :-D
        LimitNOFILE = "infinity";
        LimitMEMLOCK = "infinity";
        OOMScoreAdjust = -1000;
      };
    };
  };
}