amazon-aurora

What is the syntax for dropping an Amazon Aurora with mysql compatinility user with single quotes?


I'm using AWS 8.0.mysql_aurora.3.05.2

I do this to get a list of the users

SELECT user,host from mysql.user;

and have these entries, surrounded by single quotes ('...').

user                 |host     |
---------------------+---------+
‘user1’              |%        |
‘my_user’            |%        |

What is the syntax to drop them? I've tried

DROP user "'user1'"@'%';

or (from comments)

DROP user "\'user1\'"@'%';

But I get syntax errors.

SQL Error [1396] [HY000]: Operation DROP USER failed for '\'user1\''@'%'

Solution

  • Demo, using my local MySQL 8.0.39 instance:

    mysql> create user '''user1'''@'%';
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> select user from mysql.user;
    +------------------+
    | user             |
    +------------------+
    | 'user1'          |
    | mysql.infoschema |
    | mysql.session    |
    | mysql.sys        |
    | root             |
    +------------------+
    5 rows in set (0.00 sec)
    
    mysql> drop user '''user1'''@'%';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select user from mysql.user;
    +------------------+
    | user             |
    +------------------+
    | mysql.infoschema |
    | mysql.session    |
    | mysql.sys        |
    | root             |
    +------------------+
    4 rows in set (0.01 sec)
    

    If your user actually has smart-quotes, these are not the same characters as single-quotes. You don't have to do any escaping for smart-quotes.

    mysql> create user '‘user1’'@'%';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select user from mysql.user;
    +------------------+
    | user             |
    +------------------+
    | ‘user1’          |
    | mysql.infoschema |
    | mysql.session    |
    | mysql.sys        |
    | root             |
    +------------------+
    5 rows in set (0.00 sec)
    
    mysql> drop user '‘user1’'@'%';
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> select user from mysql.user;
    +------------------+
    | user             |
    +------------------+
    | mysql.infoschema |
    | mysql.session    |
    | mysql.sys        |
    | root             |
    +------------------+