Ran the mysql tests successfully, mysqlReplication is broken since December 2018 and reported here: #61523

Splitted the changes into multiple commits as suggested by @aanderse over IRC.

I did some deeper digging into the remaining pidDir TODO, and noticed this was only used in the wordpress module (to wait for mysql to boot up).

As we can simply wait for the mysql socket file to appear too, I dropped pidDir, further simplifying the mysql module.

Also added release notes for both changes.

Checked on mariadb-galera server- worked. Thanks.

Rebased on latest master, tests still pass.

@thorstenweber83 we have a nixos test using services.mysql.ensure(Users,Databases), which didn't break after this PR, so I guess this should work :-)

Or did i miss something?

@thorstenweber83 Yes. The part you missed is that mysql on NixOS is insanely insecure out of the box and allows anyone to authenticate as the root mysql user. If you were to secure your mysql setup, which I hope you and everyone else has, this change will then likely (depending on how you secured) cause breakage for ensureUsers and ensureDatabases as you suggested.

I have been meaning to address the overall issue of an insecure mysql setup out of the box on NixOS for a while. I'll discuss with @flokli and will report back.

@aanderse you're right, I use a postStart script to secure access like mysql_secure_installation does and set up socket authentication for root:

systemd.services.mysql.postStart = ''
    ( echo "ALTER USER root@localhost IDENTIFIED WITH unix_socket;"
      echo "ALTER USER root@'127.0.0.1' IDENTIFIED WITH unix_socket;"
      echo "ALTER USER root@'::1' IDENTIFIED WITH unix_socket;"

      echo "DELETE FROM mysql.user WHERE User=''';"
      echo "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');"
      echo "DROP DATABASE IF EXISTS test;"
      echo "DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';"
      echo "FLUSH PRIVILEGES;"
    ) | ${mysql}/bin/mysql -u root -N
  '';

  echo "ALTER USER root@'127.0.0.1' IDENTIFIED WITH unix_socket;"

@thorstenweber83 I wouldn't have thought that to make sense as mysql/mariadb can only do socket authentication on localhost.

@aanderse yeah you're right, this is a bit nonsensical.

Partial revert. I don't have the time/energy to go any further on this at the moment. Important to get this fixed now, though.

See 4243732 for proposal. Note that this is based on #63786 which I'll wait to be merged before going ahead with this.

ping @flokli and @thorstenweber83