How to fix ERROR 1396 (HY000): Operation xxx failed for user

Error Message#

When encountering MySQL Error 1396, you'll see a message similar to:

ERROR 1396 (HY000): Operation CREATE USER failed for 'username'@'hostname'

Or variations such as:

ERROR 1396 (HY000): Operation DROP USER failed for 'username'@'hostname'
ERROR 1396 (HY000): Operation GRANT failed for 'username'@'hostname'

What It Means#

This error occurs when trying to perform a user management operation (such as CREATE USER, DROP USER, or GRANT) on a MySQL user that either already exists (for CREATE) or doesn't exist (for DROP or GRANT). It indicates a mismatch between the expected and actual state of the user account in the MySQL user table.

Common Causes#

1. User already exists: Attempting to create a user that already exists in the database
2. User doesn't exist: Trying to modify or drop a user that doesn't exist
3. Hostname mismatch: Using a different hostname specification than what's in the database
4. Case sensitivity issues: Inconsistent case usage in usernames or hostnames
5. Partial user information: The user exists but with different connection parameters
6. MySQL privilege cache: Stale privilege information not properly flushed
7. Replication inconsistencies: User exists on master but not on replica

How to Fix#

Solution 1: Check if User Exists Before Operations#

Before creating, dropping, or modifying a user, check if the user already exists:

-- Check if a user exists
SELECT User, Host FROM mysql.user WHERE User = 'username' AND Host = 'hostname';

-- For MySQL 8.0+, you can also use
SELECT * FROM mysql.user WHERE User = 'username' AND Host = 'hostname'\G

Solution 2: Use IF EXISTS or IF NOT EXISTS Clauses#

Add conditional clauses to your user management statements:

-- Creating users
CREATE USER IF NOT EXISTS 'username'@'hostname' IDENTIFIED BY 'password';

-- Dropping users
DROP USER IF EXISTS 'username'@'hostname';

Solution 3: Correct User Specification#

Ensure you're using the exact host specification that exists in the database:

-- List all instances of the user with different hosts
SELECT User, Host FROM mysql.user WHERE User = 'username';

-- Then use the exact user and host combination
DROP USER 'username'@'192.168.1.%';  -- Instead of 'username'@'%'

Solution 4: Fix Case Sensitivity Issues#

MySQL usernames are case-sensitive on some systems and not on others, depending on the lower_case_table_names setting and the operating system:

-- Check case sensitivity setting
SHOW VARIABLES LIKE 'lower_case_table_names';

-- Use consistent case in your operations
CREATE USER 'UserName'@'hostname' IDENTIFIED BY 'password';

Solution 5: Use FLUSH PRIVILEGES After Manual Changes#

If you've made direct changes to the mysql.user table, flush privileges:

-- After direct modifications to mysql tables
FLUSH PRIVILEGES;

Solution 6: Handle Host Wildcards Properly#

Be careful with wildcard characters in hostnames:

-- These are different user specifications
'username'@'localhost'   -- Only local connections
'username'@'127.0.0.1'   -- Only IPv4 loopback
'username'@'::1'         -- Only IPv6 loopback
'username'@'%'           -- Any host

Make sure to use the correct wildcard pattern that matches how the user was created.

Solution 7: Recreate User with Proper Settings#

If you're unsure about the user's current state, you can drop and recreate:

-- First, check existing privileges to preserve them
SHOW GRANTS FOR 'username'@'hostname';

-- Drop the user if it exists
DROP USER IF EXISTS 'username'@'hostname';

-- Create the user with proper settings
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

-- Re-grant necessary privileges
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'hostname';

Cloud Vendor Considerations#

When working with managed MySQL services:

• AWS RDS: Some user operations might be restricted for the master user
• Google Cloud SQL: User management might require specific IAM permissions
• Azure Database for MySQL: Some administrative operations are handled through the Azure portal
• All cloud platforms: Consider using the cloud provider's user management interfaces for consistent results

For consistent user management across environments:

1. Always use explicit IF EXISTS/IF NOT EXISTS clauses
2. Check for user existence before operations
3. Document user creation scripts with exact specifications
4. Be aware of hostname differences between local and cloud environments