I'm trying to create a stored procedure to rename users in my MySQL database:
CREATE DEFINER=`admin`@`%` PROCEDURE `renamedbuser`(
IN _oldname VARCHAR(200),
IN _newname VARCHAR(200)
)
BEGIN
IF (SELECT COUNT(User) FROM mysql.user WHERE User = _oldname) > 0 THEN
BEGIN
RENAME USER _oldname TO _newname;
END;
END IF;
END
When I run this procedure I get the following error:
MySql.Data.MySqlClient.MySqlException (0x80004005): Operation RENAME USER failed for '_oldname'@'%'
It seems to me that when the RENAME USER line executes, it uses the name of the variable _oldname as the original username, rather than the value that the variable contains.
If I execute something like
RENAME USER 'test' TO 'testing';
If works as I'd expect, renaming the 'test'@'%' user.
Am I calling RENAME USER incorrectly? Does that statement just not work this way when called from a stored procedure? Is there any way to achieve the behavior I'm looking for?
In the documentation of account names it says:
The user name and host name need not be quoted if they are legal as unquoted identifiers.
I guess that takes precedence over your stored procedure variables. It also says:
Quote user names and host names as identifiers or as strings, using either backticks (`), single quotation marks ('), or double quotation marks (").
You could try it with backticks, like so:
RENAME USER `_oldname` TO `_newname`;
User contributions licensed under CC BY-SA 3.0