CREATE USER MySQL

Question

Error with dropping tables:

SQL query:

DROP user IF EXISTS  's01'@'%';


MySQL said: Documentation

#1064 - 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 'if exists 's01'@'%'' at line 1

Error without dropping tables:

SQL query:

/*Students*/ CREATE User 's01'@'%' IDENTIFIED BY  'pw1';


MySQL said: Documentation

#1396 - Operation CREATE USER failed for 's01'@'%'

Example of some of my code:

Drop user if exists 's01'@'%';

flush privileges;

/*Students*/
Create User 's01'@'%' Identified by 'pw1';

I don't exactly know what is wrong. I looked it up and it said add flush privileges, but I still get the same two errors.

maybe a problem with privileges? - https://dev.mysql.com/doc/refman/5.7/en/drop-user.html — Sebastian Brosch, Sep 06 '17 at 13:19
`flush privileges` -> not needed with proper user manipulation SQL statements. — danblack, Jan 30 '19 at 21:30

score 1 · Answer 1 · answered Sep 06 '17 at 17:25

Check your version of mysql with select version();. The IF EXISTS option for DROP USER was added in 5.7. If you're not using 5.7, then that likely explains your first error.

Check if the user exists by querying mysql.user table.

select user, host from mysql.user where user = 's01';

Also since you are not specifying a hostname, just execute

DROP user IF EXISTS 's01';
Create User 's01' Identified by 'pw1';

The wildcard host (@'%') is implied.

Also execute SHOW GRANTS to verify that your user has the correct privileges to create and drop users.

CREATE USER MySQL

1 Answers1