CREATE USER fails with error 1396
CREATE USER is refused for an account whose user and host already exist (error 1396)Short answer
Error 1396 on CREATE USER means an account with that exact user and host already exists, often left from an earlier attempt. List the accounts, then drop the old one with DROP USER, or use CREATE USER IF NOT EXISTS when reuse is fine.
Fix it fast
1Find out which cause you have
List every account with that user name, host included. The host part decides which account is meant.
SELECT user, host FROM mysql.user WHERE user = 'jack';2Match what you see, then jump to its fix
- 1
The query above returns a row for the same user and hostThe account already existsFix - 2
A previous attempt or an earlier install created the account and you want it goneAn old account must be replacedFix - 3
DROP USER 'jack' reports that the operation failed, yet an account for jack existsDROP USER used a name without a hostFix
- 1
What does the error look like?
CREATE USER is refused for an account whose user and host already exist (error 1396)
Cause 1 of 3
The account already exists
1Check
Compare the user and host in the error with the rows returned.
SELECT user, host FROM mysql.user WHERE user = 'jack';2Fix
Reuse it with IF NOT EXISTS (this only warns), or change its password. Do not recreate it.
CREATE USER IF NOT EXISTS 'jack'@'localhost' IDENTIFIED BY 'a-new-strong-password';3Verify
The statement ends with a warning, not an error.
SHOW WARNINGS;Confirmed by dev.mysql.com
Cause 2 of 3
An old account must be replaced
1Check
Confirm the exact user and host, because 'jack'@'%' and 'jack'@'localhost' are two accounts.
SELECT user, host FROM mysql.user WHERE user = 'jack';2Fix
Drop that account with the full name, then create it again. DROP USER removes its privilege rows from all grant tables.
DROP USER 'jack'@'localhost';
CREATE USER 'jack'@'localhost' IDENTIFIED BY 'a-new-strong-password';3Verify
The new account is listed.
SELECT user, host FROM mysql.user WHERE user = 'jack';Confirmed by dev.mysql.com
Cause 3 of 3
DROP USER used a name without a host
1Check
A name without a host means the host '%'. If the account is 'jack'@'localhost', the short name points at a different account.
SELECT user, host FROM mysql.user WHERE user = 'jack';2Fix
Write the host exactly as listed. IF EXISTS turns a missing account into a warning.
DROP USER IF EXISTS 'jack'@'localhost';3Verify
No row remains for that user and host.
SELECT user, host FROM mysql.user WHERE user = 'jack';Confirmed by dev.mysql.com
How do I stop it coming back?
- Use CREATE USER IF NOT EXISTS in setup scripts so reruns do not fail.
- Always write both user and host in account statements.
- Use DROP USER, not DELETE on the grant tables.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix