Syntax error with emulating "create user if not exists"

Question

MySQL does not allow you to specify an if not exists clause in a create user statement (despite the fact that create table and create procedure do support this). There was a feature request for this in 2005 but the MySQL devs have done sod all about it, so it's probably not going to happen anytime soon:

http://bugs.mysql.com/bug.php?id=15287

I'm trying to emulate this functionality with the following statement:

if (select ifnull((select 1
                     from mysql.user
                    where User = 'recuser'
                      and Host = '%'), 0) = 0) then
  create user 'recuser'@'%' identified by password 'blah';
end if;

but MySQL complains about the syntax in the if statement - I can't see anything wrong with it, hopefully someone else can point out what the issue is.

Thanks!

Community · Accepted Answer · 2017-05-23 11:53:16Z

10

Denis answered your precise question, but there are other ways around your problem: see my answer to mysql create user only when the user doesn't exist (where there is also an example of a similar stored procedure as well)

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Mar 24, 2011 at 6:43

Tim Diggins

4,5663 gold badges32 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Denis Otkidach · Accepted Answer · 2009-10-01 09:46:32Z

-2

MySQL supports flow control constructs within stored procedures only.

answered Oct 1, 2009 at 9:46

Denis Otkidach

33.5k8 gold badges86 silver badges102 bronze badges

2 Comments

Ian Kemp - SO dead by AI greed Over a year ago

... and it doesn't have system stored procedures to add/remove users, and you can't prepare a create user statement because it's not supported. Another reason to "love" MySQL I guess.

Cerin Over a year ago

You told him something he essentially already knows, instead of helping fix his problem...

Collectives™ on Stack Overflow

Syntax error with emulating "create user if not exists"

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related