mysql_user: waits forever for table metadata lock with loop on MySQL 8

[GhostLyrics]

SUMMARY

I am trying to apply GRANTs with a loop on MySQL 8, using a custom --defaults-extra-file. Due to you performing some statements in a transaction (the python db module default - I've used the same module myself), this waits infinitely for a metadata lock on MySQL 8.

Note that I'm not sure whether Ansible is correct here or not. If Ansible is correct, then perhaps someone with better understanding of MySQL should forward this bug to them.

ISSUE TYPE

• Bug Report

COMPONENT NAME

task: community.mysql.mysql_user
mysql version: mysql Ver 8.0.30-22 for Linux on x86_64 (Percona Server (GPL), Release '22', Revision '7e301439b65')

ANSIBLE VERSION

ansible [core 2.11.6]
  config file = /Users/ghostlyrics/Repositories/ansible/ansible.cfg
  configured module search path = ['/Users/ghostlyrics/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /opt/homebrew/Cellar/[email protected]/4.8.0/libexec/lib/python3.10/site-packages/ansible
  ansible collection location = /Users/ghostlyrics/Repositories/ansible/collections
  executable location = /opt/homebrew/bin/ansible
  python version = 3.10.9 (main, Dec 15 2022, 17:11:09) [Clang 14.0.0 (clang-1400.0.29.202)]
  jinja version = 3.0.2
  libyaml = True

COLLECTION VERSION

# /Users/ghostlyrics/Repositories/ansible/collections/ansible_collections
Collection      Version
--------------- -------
community.mysql 3.5.1

# /opt/homebrew/Cellar/[email protected]/4.8.0/libexec/lib/python3.10/site-packages/ansible_collections
Collection      Version
--------------- -------
community.mysql 2.3.1

CONFIGURATION

ANSIBLE_FORCE_COLOR(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = True
ANSIBLE_NOCOWS(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = True
CACHE_PLUGIN(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = jsonfile
CACHE_PLUGIN_CONNECTION(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = ./facts
CACHE_PLUGIN_TIMEOUT(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = 7200
COLLECTIONS_PATHS(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = ['/Users/ghostlyrics/Repositories/ansible/collections']
DEFAULT_FILTER_PLUGIN_PATH(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = ['/Users/ghostlyrics/Repositories/ansible/filter_plugins']
DEFAULT_FORKS(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = 40
DEFAULT_GATHERING(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = smart
DEFAULT_HOST_LIST(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = ['/Users/ghostlyrics/Repositories/ansible/inventory/hosts.ini']
DEFAULT_LOAD_CALLBACK_PLUGINS(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = True
DEFAULT_LOG_PATH(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = /Users/ghostlyrics/Repositories/ansible/log
DEFAULT_ROLES_PATH(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = ['/Users/ghostlyrics/Repositories/ansible/.galaxy', '/Users/ghostlyrics/Reposit>
DEFAULT_STDOUT_CALLBACK(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = community.general.yaml
DEFAULT_STRATEGY(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = mitogen_linear
DEFAULT_STRATEGY_PLUGIN_PATH(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = ['/Users/ghostlyrics/Repositories/ansible/contrib/mitogen-0.3.3/ansib>
DEFAULT_VAULT_IDENTITY_LIST(env: ANSIBLE_VAULT_IDENTITY_LIST) = ['stage@gpg/get-vault-pass-stage', 'common@gpg/get-vault-pass-common']
DEFAULT_VAULT_ID_MATCH(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = True
MAX_FILE_SIZE_FOR_DIFF(/Users/ghostlyrics/Repositories/ansible/ansible.cfg) = 1048576

OS / ENVIRONMENT

Debian Bullseye

STEPS TO REPRODUCE

- name: Create mysql root user
2  community.mysql.mysql_user:
3    name: root
4    host: "{{ item }}"
5    password: "{{ mysql_root_password }}"
6    config_file: /etc/mysql/debian.cnf
7    priv: "*.*:ALL,GRANT"
8  with_items:
9    - localhost
10    - 127.0.0.1
11    - '%'

PS: I'm not 100% sure if this is enough to re-produce the issue. If it isn't, I'll do my best to construct a better minimal case, but I'll have to un-entangle it from company-internal things.

EXPECTED RESULTS

Ansible properly applies and processes all users.

ACTUAL RESULTS

Ansible stops because MySQL never returns.

If I manually add autocommit=True to the cursor object at mysql_user.py , it works flawlessly.

if not cursor:
    cursor, db_conn = mysql_connect(module, login_user, login_password, config_file, ssl_cert, ssl_key, ssl_ca, db,
                                    connect_timeout=connect_timeout, check_hostname=check_hostname, autocommit=True)

MySQL internals (maybe relevant)

mysql> show processlist;
+----+-----------------+-----------+--------------------+---------+------+---------------------------------+--------------------------------+---------+-----------+---------------+
| Id | User            | Host      | db                 | Command | Time | State                           | Info                           | Time_ms | Rows_sent | Rows_examined |
+----+-----------------+-----------+--------------------+---------+------+---------------------------------+--------------------------------+---------+-----------+---------------+
|  5 | event_scheduler | localhost | NULL               | Daemon  | 1050 | Waiting on empty queue          | NULL                           | 1050016 |         0 |             0 |
| 24 | root            | localhost | NULL               | Sleep   |  650 |                                 | NULL                           |  650160 |         0 |             0 |
| 25 | root            | localhost | performance_schema | Query   |    0 | init                            | show processlist               |       0 |         0 |             0 |
| 32 | root            | localhost | NULL               | Sleep   |  527 |                                 | NULL                           |  526943 |         0 |             0 |
| 33 | root            | localhost | NULL               | Query   |  527 | Waiting for table metadata lock | GRANT ALL ON *.* TO 'root'@'%' |  526826 |         0 |             0 |
+----+-----------------+-----------+--------------------+---------+------+---------------------------------+--------------------------------+---------+-----------+---------------+
5 rows in set (0.00 sec)

mysql> SELECT   pslst.id 'PROCESS ID'  ,itrx.trx_id 'TRX_ID'     ,pesh.event_name 'EVENT NAME'     ,pesh.sql_text 'SQL'     ,th.processlist_user 'USER'     ,th.processlist_host 'HOST' FROM   information_schema.innodb_trx itrx JOIN information_schema.processlist pslst ON   itrx.trx_mysql_thread_id = pslst.id JOIN performance_schema.threads th ON   th.processlist_id = itrx.trx_mysql_thread_id JOIN performance_schema.events_statements_history pesh ON   pesh.thread_id = th.thread_id WHERE   itrx.trx_started < CURRENT_TIME - INTERVAL 100 SECOND  AND pslst.USER != 'SYSTEM_USER' GROUP BY  pesh.sql_text ORDER BY   pesh.EVENT_ID\G
*************************** 1. row ***************************
PROCESS ID: 32
    TRX_ID: 421243532045688
EVENT NAME: statement/sql/select
       SQL: SELECT COLUMN_NAME FROM information_schema.COLUMNS
                    WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'user' AND COLUMN_NAME IN ('Password', 'authentication_string')
                    ORDER BY COLUMN_NAME DESC LIMIT 1
      USER: root
      HOST: localhost
*************************** 2. row ***************************
PROCESS ID: 32
    TRX_ID: 421243532045688
EVENT NAME: statement/sql/select
       SQL: SELECT COLUMN_NAME FROM information_schema.COLUMNS
                    WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'user' AND COLUMN_NAME IN ('Password', 'authentication_string')
                    ORDER BY COLUMN_NAME ASC  LIMIT 1
      USER: root
      HOST: localhost
*************************** 3. row ***************************
PROCESS ID: 32
    TRX_ID: 421243532045688
EVENT NAME: statement/sql/select
       SQL: SELECT COALESCE(
                            CASE WHEN authentication_string = '' THEN NULL ELSE authentication_string END,
                            CASE WHEN authentication_string = '' THEN NULL ELSE authentication_string END
                        )
                    FROM mysql.user WHERE user = 'root' AND host = '%'
      USER: root
      HOST: localhost
*************************** 4. row ***************************
PROCESS ID: 32
    TRX_ID: 421243532045688
EVENT NAME: statement/sql/select
       SQL: SELECT CONCAT('*', UCASE(SHA1(UNHEX(SHA1('REDACTED')))))
      USER: root
      HOST: localhost
*************************** 5. row ***************************
PROCESS ID: 32
    TRX_ID: 421243532045688
EVENT NAME: statement/sql/grant
       SQL: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'
      USER: root
      HOST: localhost
*************************** 6. row ***************************
PROCESS ID: 32
    TRX_ID: 421243532045688
EVENT NAME: statement/sql/show_grants
       SQL: SHOW GRANTS FOR 'root'@'%'
      USER: root
      HOST: localhost
*************************** 7. row ***************************
PROCESS ID: 32
    TRX_ID: 421243532045688
EVENT NAME: statement/sql/select
       SQL: SELECT VERSION() AS version
      USER: root
      HOST: localhost
*************************** 8. row ***************************
PROCESS ID: 32
    TRX_ID: 421243532045688
EVENT NAME: statement/sql/show_create_user
       SQL: SHOW CREATE USER 'root'@'%'
      USER: root
      HOST: localhost
8 rows in set (0.00 sec)

Additionally, it may help to take a look at select * from performance schema.metadata locks;.

[strafer]

I have this happening with the mysql_role module as well.

[Andersson007]

@GhostLyrics @st-rafer hello, thanks for reporting the issue! and sorry for the late reply, holidays..

So does adding autocommit=True solve it?

I think we could just add it (we use commit/rollback only in the mysql_query module as far as i can see). Ideas?

cc @laurent-indermuehle @betanummeric

If no objections, does anyone want to do it? We have the Quick-start guide for folks who has no experience.

[GhostLyrics]

It did for me, but be aware that I haven't checked against a comprehensive suite of tests if adding
autocommit=true has any side effects.

[Andersson007]

It did for me, but be aware that I haven't checked against a comprehensive suite of tests if adding autocommit=true has any side effects.

@GhostLyrics i think everything should be fine, would you like to submit a PR using https://docs.ansible.com/ansible/devel/community/create_pr_quick_start.html ?

[betanummeric]

As far as I see the mysql_user module does not explicitly use transactions, so enabling autocommit would make sense.

[laurent-indermuehle]

Question: What should be rollback? The whole loop or only the current item?
The module description state "Adds or removes a user". It is not responsible for handling multiple users at once, this is the job of Ansible.

[Andersson007]

@betanummeric @laurent-indermuehle thanks for the feedback!
@GhostLyrics feel free to proceed with #479 (comment)
If no time, please let us know

[betanummeric]

@GhostLyrics Can you share the content of /etc/mysql/debian.cnf? (You can censor secrets.)

[Andersson007]

@GhostLyrics please take a look ^

[GhostLyrics]

Getting on things, I was on leave. 😁

[GhostLyrics]

Here's my /etc/mysql/debian.cnf:

root@REDACTED ~ # cat /etc/mysql/debian.cnf
[client]
host     = localhost
user     = root
password = REDACTED
socket   = /var/run/mysqld/mysqld.sock

[mysql_upgrade]
host     = localhost
user     = root
password = REDACTED
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

I'm about to read the document for submitting patches.

[GhostLyrics]

I have this happening with the mysql_role module as well. - @st-rafer

Since I can't easily reproduce this with code I have and no example has been provided, I'll not update mysql_role in my branch.

[GhostLyrics]

@Andersson007 @betanummeric I've submitted #483.

Having said that, most things are nicely documented in your instructions (safe for whether to use the issue key or the pull request key for the changelog fragment). It's great for people that want to become regular contributors but it took me more than an hour to read everything and submit a single line change. 😕

[Andersson007]

@GhostLyrics thanks!

@betanummeric would you like to take a look at the cfg?

It's great for people that want to become regular contributors but it took me more than an hour to read everything and submit a single line change.

@GhostLyrics we hope you'll be coming back and will become a regular contributor;) Getting regular contributors is our first priority

BTW we have #mysql:ansible.com on Matrix, welcome

[betanummeric]

ah, the module uses only the part of the config file to set login_host and login_port, so no side effect here

[Andersson007]

@GhostLyrics would you like to fix this for mysql_role module?
@st-rafer reported that they have same issue there as well

[GhostLyrics]

@st-rafer could you please try the following:

• in your regular Ansible setup, go to the relevant file for mysql_role (similar to Repositories/ansible/collections/ansible_collections/community/mysql/plugins/modules/mysql_role.py on my machine)
• In the function call to mysql_connect(), add the keyword parameter autocommit=True, for both occurences (for me that's line 1008-1011 and line 1016-1019)
• Try if that solves the metadata lock problem for you.
• Report back with your findings in this thread.

If it does, I'll submit a second Pull Request. Given that I can't easily test this myself given that you haven't submitted additional example code for how to trigger it, I think that's the best I can do. 👍

[strafer]

@GhostLyrics I made this change immediately in both modules as soon as I saw this issue, it works flawlessly.
I think mysql_role can also be changed.

[Andersson007]

@GhostLyrics @st-rafer thanks for collaboration!

@GhostLyrics so looking forward to the PR:)

[GhostLyrics]

Working on it.
My apologies for the delay. My open source contributions are currently made possible by my employer, but that means that internal tasks get priority. 😅

[GhostLyrics]

Pull Request sent.

[Andersson007]

Merged, thanks everyone for the contribution!