Question about SQL syntax

Question

Hi,

anyone whats wrong with this? why outputs a syntax error?

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 169 Server version: 5.0.51a-3ubuntu5.7 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' -> ; Query OK, 0 rows affected (0.14 sec)

mysql> IDENTIFIED BY 'm' WITH GRANT OPTION; 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 'IDENTIFIED BY 'new_password' WITH GRANT OPTION' at line 1 mysql>

Regards

Javi

Answer 1

Isn't it splitted over two lines so MySQL client understands it as two queries not one?

Try to execute the query as one line:

GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' IDENTIFIED BY 'm' WITH GRANT OPTION;

Answer 2

you've split one statement into two bits.

Answer 3

There is an extra semicolon here at the end:

GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' ;

You can split a MySQL query into several lines, so there's no problem writing it like this:

 GRANT ALL PRIVILEGES ON . TO 'root'@'localhost'
 IDENTIFIED BY 'm' WITH GRANT OPTION;

but once MySQL sees a semicolon (ending a query statement) it will execute whatever is in the buffer, which in your case was a complete, valid query (grant all privileges to the root user); but then the second line becomes a new query:

   IDENTIFIED BY 'm' WITH GRANT OPTION;

which by itself is not valid.

Answer 4

You've used the mysql command line tool. A semicolon ends your sql command like \g or \G.

You should siwtch to a different tool if you don't like or know how to use this command line tool: http://dev.mysql.com/downloads/gui-tools/5.0.html -> Mysql Administrator