Fixed: "ERROR 3118 (HY000): Access denied for user. Account is locked." in MySQL

Access denied for user 'inventory_app'@'localhost'. Account is locked. is unusually clear: the password isn't the problem, the account is switched off. One thing I didn't expect: sessions that were already connected kept working.

What I Was Doing

I was locking an account in MySQL 9.7, to see what it does to people already signed in, and new ones:

mysql>

ALTER USER 'inventory_app'@'%' ACCOUNT LOCK;

Then a new sign-in:

Terminal

docker compose exec mysql mysql --user=inventory_app --password

What I Expected

For this test, a refusal. If you've landed here, you probably didn't expect it.

What I Got

Output

ERROR 3118 (HY000): Access denied for user 'inventory_app'@'localhost'. Account is locked.

What I Checked

The account's state, as root:

mysql>

SELECT user, host, account_locked FROM mysql.user WHERE user = 'inventory_app';

Output

+---------------+------+----------------+
| user          | host | account_locked |
+---------------+------+----------------+
| inventory_app | %    | Y              |
+---------------+------+----------------+
1 row in set (0.000 sec)

A session already open, when the lock happened:

Output

mysql> SELECT 'this session keeps working' AS answer;
+----------------------------+
| answer                     |
+----------------------------+
| this session keeps working |
+----------------------------+
1 row in set (0.000 sec)

It kept working. So did an API's pooled connections, which is why an app can seem fine right up to its next restart.

Why It Happens

ACCOUNT LOCK stops new logins. It doesn't touch existing connections. An administrator can set it, or a script, and CREATE USER can start an account locked. System accounts such as mysql.sys are locked permanently, on purpose.

The Fix

First find out why it's locked, if you didn't do it yourself: someone may have done it for a reason. Then, as root:

mysql>

ALTER USER 'inventory_app'@'%' ACCOUNT UNLOCK;

After the Fix

Output

+---------------+------+----------------+
| user          | host | account_locked |
+---------------+------+----------------+
| inventory_app | %    | N              |
+---------------+------+----------------+
1 row in set (0.000 sec)

New sign-ins work again, with no restart.

Common Mistakes

Mistake	What happens	Do this instead
Resetting the password	It stays locked	Unlock it
Assuming a lock stops a running app	Existing connections carry on	Close them too, if that's what you want
Unlocking mysql.sys	It's locked for a reason	Leave system accounts alone

Troubleshooting

Symptom	Likely cause	Fix
ERROR 3955 and Account is blocked	Too many failed logins: a different, temporary lock	ALTER USER … ACCOUNT UNLOCK ends it
ALTER USER is denied	You need the CREATE USER right	Sign in as an administrator
ERROR 1045 instead	The account is open, and the login is wrong	The fix

Conclusion

ERROR 3118 means the account is locked, so the password doesn't matter. Find out why, then run ALTER USER … ACCOUNT UNLOCK.

Additional Resources

• Account Locking — what it does and doesn't stop.
• ALTER USER Statement — ACCOUNT LOCK and UNLOCK.

Related

• How to Lock and Unlock a MySQL Account
• How to See What a MySQL Account Can Do with SHOW GRANTS
• How to Change a MySQL Password Without Downtime