Erroneous mysql syntax error when creating user

Question

I'm using a list of sql statements in a text file to bootstrap mysql before actually starting it, the command is as follows

/usr/sbin/mysqld --user=mysql --bootstrap --verbose=0 < $tfile && echo "Successfully run $tfile"

And the SQL statements, to update the root users password and create a wordpress users in the $tfile are

USE mysql;
FLUSH PRIVILEGES;
CREATE USER "root"@"%" IDENTIFIED BY "1234567890abcdef";
GRANT ALL PRIVILEGES ON *.* TO "root"@"%" WITH GRANT OPTION;
UPDATE user SET password=PASSWORD("1234567890abcdef") WHERE user="root";
GRANT ALL PRIVILEGES ON *.* TO "root"@"localhost" WITH GRANT OPTION;
UPDATE user SET password=PASSWORD(") WHERE user="root" AND host="localhost";
CREATE DATABASE IF NOT EXISTS `wordpress` CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE USER "wordpress"@"%" IDENTIFIED BY "1234567890abcdef";
GRANT ALL PRIVILEGES ON *.* TO "wordpress"@"%" WITH GRANT OPTION;

However no matter how K tweak this, I come back to the same error

ERROR: 1064 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 '1234567890abcdef' at line 1 [ERROR] Aborting

I'm not sure about MySQL, but in SQL, strings are enclosed in single quotes ', not double quotes ". Have you tried replacing all double quotes with single ones? — Frank Schmitt
– Frank Schmitt, Commented Mar 10, 2017 at 7:29
I have tried that, but I used piliapp.com/mysql-syntax-check to syntax check — Jonathan
– Jonathan, Commented Mar 10, 2017 at 7:49
changing it to quotes I get ERROR: 1064 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 '1234567890abcdef '' at li ne 1 — Jonathan
– Jonathan, Commented Mar 10, 2017 at 8:25

Devart · Accepted Answer · 2017-03-10 08:22:15Z

1

In simple mode, MySQL treats " as a literal quote, but if ANSI_QUOTES is enabled, then it is used as quote symbol for identifiers.

Check the SQL_MODE, for example session value -

SELECT @@SESSION.sql_mode;

And change it if needed. More information - ANSI_QUOTES.

Try to change this mode before the script -

-- SET @@SESSION.sql_mode = 'ANSI_QUOTES';
SET @@SESSION.sql_mode = '';
USE mysql;
FLUSH PRIVILEGES;
CREATE USER "root"@"%" IDENTIFIED BY "1234567890abcdef";

edited Mar 10, 2017 at 8:22

answered Mar 10, 2017 at 7:38

Devart

120k23 gold badges172 silver badges189 bronze badges

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

3 Comments

Jonathan Over a year ago

returned

mysql> SELECT @@SESSION.sql_mode; +--------------------+ | @@SESSION.sql_mode | +--------------------+ |                    | +--------------------+ 1 row in set (0.00 sec)

Devart Over a year ago

I have added some details in answer.

Jonathan Over a year ago

I can create the use now! But this happens when I try to read in values from env variables

mysql> select User,Host from mysql.user; +-------------+---------------------------------+ | User        | Host                            | +-------------+---------------------------------+ | $MYSQL_USER | %                               | | root        | 127.0.0.1                       | | root        | ::1                             | | root        | localhost                       | | root        | wordpress-mysql-403298046-915cl | +-------------+---------------------------------+)

Collectives™ on Stack Overflow

Erroneous mysql syntax error when creating user

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related