puppetlabs-mysql: change the @HOST for the 'root'@'localhost'

[comport3]

Using the latest Puppet 5.5+ and puppetlabs/mysql module 5.4.0, is there a way to change the 'localhost' host portion of the root user?

Post deployment, I can use the following MySQL commands to change it, but it breaks the Puppet module:

UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='root'; FLUSH PRIVILEGES;

However this breaks the Puppet run with the following output (with --debug arguments)

Debug: Prefetching mysql resources for mysql_user

Debug: Executing: '/usr/bin/mysql --defaults-extra-file=/root/.my.cnf -NBe SELECT CONCAT(User, '@',Host) AS User FROM mysql.user'

Debug: Executing: '/usr/bin/mysql --defaults-extra-file=/root/.my.cnf -NBe SELECT MAX_USER_CONNECTIONS, MAX_CONNECTIONS, MAX_QUESTIONS, MAX_UPDATES, SSL_TYPE, SSL_CIPHER, X509_ISSUER, X509_SUBJECT, AUTHENTICATION_STRING, PLUGIN FROM mysql.user WHERE CONCAT(user, '@', host) = 'root@%''

Debug: Executing: '/usr/bin/mysql --defaults-extra-file=/root/.my.cnf -NBe SELECT MAX_USER_CONNECTIONS, MAX_CONNECTIONS, MAX_QUESTIONS, MAX_UPDATES, SSL_TYPE, SSL_CIPHER, X509_ISSUER, X509_SUBJECT, AUTHENTICATION_STRING, PLUGIN FROM mysql.user WHERE CONCAT(user, '@', host) = 'backup@localhost''

Debug: Executing: '/usr/bin/mysql --defaults-extra-file=/root/.my.cnf -NBe SELECT MAX_USER_CONNECTIONS, MAX_CONNECTIONS, MAX_QUESTIONS, MAX_UPDATES, SSL_TYPE, SSL_CIPHER, X509_ISSUER, X509_SUBJECT, AUTHENTICATION_STRING, PLUGIN FROM mysql.user WHERE CONCAT(user, '@', host) = 'mysql.session@localhost''

Debug: Executing: '/usr/bin/mysql --defaults-extra-file=/root/.my.cnf -NBe SELECT MAX_USER_CONNECTIONS, MAX_CONNECTIONS, MAX_QUESTIONS, MAX_UPDATES, SSL_TYPE, SSL_CIPHER, X509_ISSUER, X509_SUBJECT, AUTHENTICATION_STRING, PLUGIN FROM mysql.user WHERE CONCAT(user, '@', host) = 'mysql.sys@localhost''

Debug: Executing: '/usr/bin/mysql --defaults-extra-file=/root/.my.cnf --database=mysql -e CREATE USER 'root'@'localhost' IDENTIFIED BY PASSWORD '*93F220D3C07B130C09DBA0C962F71213F3D0B8D6''

Error: Execution of '/usr/bin/mysql --defaults-extra-file=/root/.my.cnf --database=mysql -e CREATE USER 'root'@'localhost' IDENTIFIED BY PASSWORD '*93F220D3C07B130C09DBA0C962F71213F3D0B8D6'' returned 1: ERROR 1396 (HY000) at line 1: Operation CREATE USER failed for 'root'@'localhost'

Error: /Stage[main]/Mysql::Server::Root_password/Mysql_user[root@localhost]/ensure: change from 'absent' to 'present' failed: Execution of '/usr/bin/mysql --defaults-extra-file=/root/.my.cnf --database=mysql -e CREATE USER 'root'@'localhost' IDENTIFIED BY PASSWORD '*93F220D2C07B130C09DBA0C962F61213F3D1B8F6'' returned 1: ERROR 1396 (HY000) at line 1: Operation CREATE USER failed for 'root'@'localhost'

Notice: /Stage[main]/Mysql::Server::Root_password/File[/root/.my.cnf]: Dependency Mysql_user[root@localhost] has failures: true

Warning: /Stage[main]/Mysql::Server::Root_password/File[/root/.my.cnf]: Skipping because of failed dependencies

The following is the Hiera listen directive that necessitates the above (and, I'm aware this is NOT best practise, but I have a Dev server with nothing sensitive on it, 250+ databases to migrate and nearly that many ancient decrepit web apps connecting to it as root...)

mysql_server_override_options:

  mysqld:

    'bind-address': '10.0.0.5'

Does anyone know how to change the root users 'host' without breaking the module?

[Thomas Müller]

what about

• adding an additional root@% instead of reconfiguring the root@localhost one?
• set create_root_user=> false when declaring mysql::server class (don't know the implications)
  

- Thomas