Ansible and MySQL 8

I am trying to setup a simple installation using MySQL version 8 through Ansible. However, this simple setup is balking at me and failing to create databases or users, even though I use a simple root-no-password-default-socket setup.

After going through pages of similar issues, I decided to delve into the Ansible module and eventually in the PyMySQL code that actually does the connect.

Apparently the PyMySQL connect tries to evaluate the MySQL server character set and that default character set has changed. The value returned by MySQL 8.0.1 is now, by default, utf8mb4. And apparently, this is not recognised by the default PyMySQL code. I probably need to install a newer version of PyMySQL to match the MySQL server version, but instead I opted for adding the following to the /etc/mysql/mysql.conf.d/mysqld.conf:

      character_set_server = latin1

And now my ansible script happily creates databases again. Of course, I also need to specify a valid socket file, because the default location of PyMySQL is somewhere in /var/lib and Debian uses /var/run. So I add this to every mysql_db and mysql_user module use in Ansible (to avoid having to use a username/password):

    login_unix_socket: /var/run/mysqld/mysqld.sock

Of course, this is not the end of my problems. Apparently MySQL 8 has changed the privileges format and now uses backticks instead of apostrophs to separate fields. So where you would see 'db'.'table'@'host' the new MySQL now returns `db`.`table`@`host`. This is not fixed with some option, unfortunately, because a regexp inside Ansible is failing here. I had to go into /usr/lib/python2.7/dist-packages/ansible/modules/database/mysql/mysql_user.py and adjust the regexp around line 430 to read:

    re.match("GRANT (.+) ON (.+) TO ['].*[']@['].*[']( IDENTIFIED BY PASSWORD '.+')? ?(.*)", grant[0])