How Can I Reliably Drop and Create a Database and a User in MySQL

Question

I am currently running two scripts.

01_INIT_DATABASE.SQL

CREATE DATABASE foobar;
USE foobar;
CREATE USER 'foo'@'localhost' IDENTIFIED BY 'bar';
GRANT ALL PRIVILEGES ON foobar.* TO 'foo'@'localhost' WITH GRANT OPTION;
CREATE USER 'foo'@'%' IDENTIFIED BY 'bar';
GRANT ALL PRIVILEGES ON foobar.* TO 'foo'@'%' WITH GRANT OPTION;

and

01_DROP_DATABASE.SQL

USE foobar
DROP USER 'foo'@'localhost';
DROP USER 'foo'@'%';
DROP DATABASE IF EXISTS foobar;

They each selectively fail if one or the other has not run properly.

How can I get them to run reliably (or fail gracefully, for example, to only drop a user if it exists)

I am running them through an ant task (if that affects things, but it is really just a Java Exec), in the form of

<exec executable="mysql" input="./src/main/ddl/create/01_init_database.sql">
  <arg value="-uroot"/>
  <arg value="-ptoor"/>
</exec>

My MySQL Version is

mysql  Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.2

Update IF EXISTS and IF NOT EXISTS does not work for USER. It works fine for DATABASE.

ERROR 1064 (42000): 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 NOT EXISTS 'foo'@'localhost' IDENTIFIED BY 'bar'' at line 1

ERROR 1064 (42000): 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 'foo'@'%'' at line 1

Answer 1

You can add IF NOT EXISTS to your databasechema and user creation: like:

CREATE DATABASE IF NOT EXISTS foobar;
CREATE USER IF NOT EXISTS 'foo'@'localhost' IDENTIFIED BY 'bar';
GRANT ALL PRIVILEGES ON foobar.* TO 'foo'@'localhost' WITH GRANT OPTION;
CREATE USER  IF NOT EXISTS 'foo'@'%' IDENTIFIED BY 'bar';
GRANT ALL PRIVILEGES ON foobar.* TO 'foo'@'%' WITH GRANT OPTION;

and for the drop:

DROP USER IF EXISTS 'foo'@'localhost';
DROP USER IF EXISTS  'foo'@'%';
DROP DATABASE IF EXISTS foobar;

As mentioned below: the user if not exists only works on mysql 5.7 and higher. Do not use the create user syntax below 5.7, but change the grant statement to:

GRANT ALL PRIVILEGES ON foobar.* TO 'foo'@'localhost' identified by 'password' WITH GRANT OPTION;