Public bug reported: With MySQL version 8.0, the ansible mysql module runs into an error while creating a new MySQL user with the option "encrypted: yes" (if the MySQL user does not exist yet).
The Bug appears to be in the 'user_add' function in file '/usr/lib/python3/dist- packages/ansible/modules/database/mysql/mysql_user.py' (see arrow below): def user_add(cursor, user, host, host_all, password, encrypted, new_priv, check_mode): # we cannot create users without a proper hostname if host_all: return False if check_mode: return True if password and encrypted: ---> cursor.execute("CREATE USER %s@%s IDENTIFIED BY PASSWORD %s", (user, host, password)) <--- elif password and not encrypted: cursor.execute("CREATE USER %s@%s IDENTIFIED BY %s", (user, host, password)) else: cursor.execute("CREATE USER %s@%s", (user, host)) if new_priv is not None: for db_table, priv in iteritems(new_priv): privileges_grant(cursor, user, host, db_table, priv) return True Ansible translates this to and tries to execute the following MySQL command which fails (here the output from mysql after retrying): mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY PASSWORD "*123456789012345678901234567890"; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD "*123456789012345678901234567890"' at line 1 The following MySQL command actually does what I am trying to accomplish: mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED WITH mysql_native_password AS '*123456789012345678901234567890'; Query OK, 0 rows affected (0.01 sec) ** Affects: ansible (Ubuntu) Importance: Undecided Status: New ** Description changed: - With MySQL version 8.0, the ansible mysql module runs into an error due while creating a new - MySQL user with the option "encrypted: yes", when the MySQL user does - not exist yet. + With MySQL version 8.0, the ansible mysql module runs into an error + while creating a new MySQL user with the option "encrypted: yes" (if the + MySQL user does not exist yet). The Bug appears to be in the 'user_add' function in file '/usr/lib/python3/dist- packages/ansible/modules/database/mysql/mysql_user.py' (see arrow below): - ``` def user_add(cursor, user, host, host_all, password, encrypted, new_priv, check_mode): - # we cannot create users without a proper hostname - if host_all: - return False + # we cannot create users without a proper hostname + if host_all: + return False - if check_mode: - return True + if check_mode: + return True - if password and encrypted: + if password and encrypted: ---> cursor.execute("CREATE USER %s@%s IDENTIFIED BY PASSWORD %s", (user, host, password)) <--- - elif password and not encrypted: - cursor.execute("CREATE USER %s@%s IDENTIFIED BY %s", (user, host, password)) - else: - cursor.execute("CREATE USER %s@%s", (user, host)) - if new_priv is not None: - for db_table, priv in iteritems(new_priv): - privileges_grant(cursor, user, host, db_table, priv) - return True - ``` + elif password and not encrypted: + cursor.execute("CREATE USER %s@%s IDENTIFIED BY %s", (user, host, password)) + else: + cursor.execute("CREATE USER %s@%s", (user, host)) + if new_priv is not None: + for db_table, priv in iteritems(new_priv): + privileges_grant(cursor, user, host, db_table, priv) + return True - Ansible translates this to and tries to execute the following MySQL - command which fails (here the output from mysql after retrying): - ``` - mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY PASSWORD "*123456789012345678901234567890"; - ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD "*123456789012345678901234567890"' at line 1 - ``` + Ansible translates this to and tries to execute the following MySQL command which fails (here the output from mysql after retrying): - The MySQL following command actually does what I am trying to acomplis + mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY PASSWORD "*123456789012345678901234567890"; + ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD "*123456789012345678901234567890"' at line 1 - ``` - mysql> CREATE USER 'bhoeneis'@'localhost' IDENTIFIED WITH mysql_native_password AS '*112B6255EF222A0E29C7F299DF6BB43048CDA254'; - Query OK, 0 rows affected (0.01 sec) - ``` + The following MySQL command actually does what I am trying to + accomplish: + + mysql> CREATE USER 'bhoeneis'@'localhost' IDENTIFIED WITH mysql_native_password AS '*112B6255EF222A0E29C7F299DF6BB43048CDA254'; + Query OK, 0 rows affected (0.01 sec) ** Description changed: With MySQL version 8.0, the ansible mysql module runs into an error while creating a new MySQL user with the option "encrypted: yes" (if the MySQL user does not exist yet). The Bug appears to be in the 'user_add' function in file '/usr/lib/python3/dist- packages/ansible/modules/database/mysql/mysql_user.py' (see arrow below): def user_add(cursor, user, host, host_all, password, encrypted, new_priv, check_mode): # we cannot create users without a proper hostname if host_all: return False if check_mode: return True if password and encrypted: ---> cursor.execute("CREATE USER %s@%s IDENTIFIED BY PASSWORD %s", (user, host, password)) <--- elif password and not encrypted: cursor.execute("CREATE USER %s@%s IDENTIFIED BY %s", (user, host, password)) else: cursor.execute("CREATE USER %s@%s", (user, host)) if new_priv is not None: for db_table, priv in iteritems(new_priv): privileges_grant(cursor, user, host, db_table, priv) return True - - Ansible translates this to and tries to execute the following MySQL command which fails (here the output from mysql after retrying): + Ansible translates this to and tries to execute the following MySQL + command which fails (here the output from mysql after retrying): mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY PASSWORD "*123456789012345678901234567890"; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD "*123456789012345678901234567890"' at line 1 The following MySQL command actually does what I am trying to accomplish: - mysql> CREATE USER 'bhoeneis'@'localhost' IDENTIFIED WITH mysql_native_password AS '*112B6255EF222A0E29C7F299DF6BB43048CDA254'; + mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED WITH mysql_native_password AS '*123456789012345678901234567890'; Query OK, 0 rows affected (0.01 sec) -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1923077 Title: Ansible module with mysql_user fails to create new user, if option encrypted is set To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/ansible/+bug/1923077/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs