How to solve the error ERROR 1064 (42000) reported by MySQL in grant

Most of the grant methods found on the Internet will report errors. The main reason is that the original method cannot be used after MySQL version 8.0.

Query MySQL version

SELECT version();

Under version 8.0

grant all privileges on test.* to test@'%' identified by '123456';

Error reporting

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 '123456 '' at line 1

Correct grant method

create user test@'localhost' identified by '123456';

grant all privileges on test.* to test@'localhost';

flush privileges;

MySQL8.0 password cannot be logged in

alter user test@'localhost' identified with mysql_native_password by '123456';

Additional: MySQL ERROR 1064 (42000) - Grant always reports an error no matter what, what's going on?

Friends who have used MySQL know that there are often some grant (authorization) operations. I wonder if you have encountered such a problem.

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 ‘123456’&rsquo ; at line 1

MySQL grant's SQL

is pasted into the terminal and executed, an error is reported!
After typing each letter, it will be executed, an error will be reported again!
Repeatedly confirmed the execution many times, and reported an error again!

I’m going crazy, what should I do? Don't worry, continue reading the article.

Differences in versions cause

First, check your MySQL version first. Most of the MySQL versions that report errors are 8.0, and errors will not be reported by memorizing or even blind typing commands. I guess 5.7 is usually used the most. Believe it or not?

Test with real data

1. Try it with MySQL 8.0 first

mysql> grant all privileges on test.* to test@'%' identified by '123456';  
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 '123456'' at line 1

Report an error

2.Try it with MySQL 5.7 again

mysql> grant all privileges on test.* to test@'%' identified by '123456';    
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> flush privileges;

success.

Have careful friends noticed that there is a message in the returned result: 1 warning, what is it?

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

It turns out that in MySQL 5.7, the official prompted that this SQL syntax will be deprecated.

Correct execution of grant

So in MySQL version 8.0 and later, how do we execute grant correctly?

Create a user first, and then grant authorization.

mysql> create user test@'localhost' identified by '123456';
Query OK, 0 rows affected (0.10 sec)

mysql> grant all privileges on test.* to test@'localhost';
Query OK, 0 rows affected (0.17 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.18 sec)

It is recommended to use this method to grant permissions in future use, because it is also applicable to MySQL version 5.7, and the official method of one-click user creation and permission granting has been abandoned.

The above is the detailed content of How to solve the error ERROR 1064 (42000) reported by MySQL in grant. For more information, please follow other related articles on the PHP Chinese website!