Getting syntax error in mariadb trying to create user

Question

I have a small mariadb SQL initialization script that starts like this:

drop user if exists 'uslmonitor';
drop database if exists uslmonitordb;
create database uslmonitordb;

This fails with this error:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'if exists 'uslmonitor'' at line 1

I checked the version of mysql/mariadb on the box, and it says this:

mysql  Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

Update:

Ok, I've upgraded my MariaDB to "10.1.21-MariaDB-1~jessie" (I love Docker), and my init script excerpt is now this:

drop user if exists 'uslmonitor';
drop database if exists uslmonitordb;

flush privileges;

create database uslmonitordb;
create user 'uslmonitor'@localhost identified by 'uslmonitor';

This is failing with:

ERROR 1396 (HY000) at line 7: Operation CREATE USER failed for 'uslmonitor'@'localhost'

I managed to resolve this. The key was that the "drop user" was dropping a DIFFERENT user, so the create still fails. After changing the drop to reference the name in the same format (also enclosing localhost in single quotes), this started working. — David M. Karr
– David M. Karr, Commented Mar 15, 2017 at 22:26

O. Jones · Accepted Answer · 2017-03-15 17:44:00Z

1

This is one of those cases where reading the error message gives the answer.

MySQL version 5.5 (with which the MariaDB you're using is compatible) doesn't let you say IF EXISTS on the DROP USER operation.

That didn't come in until version 5.7.8.

answered Mar 15, 2017 at 17:44

O. Jones

110k17 gold badges135 silver badges188 bronze badges

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

1 Comment

David M. Karr Over a year ago

Ok. I'm using the latest version of mariadb available for centos 7.3, using the default yum repos, so I guess I'll have to research whether I can make it use a newer version.

Collectives™ on Stack Overflow

Getting syntax error in mariadb trying to create user

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related