ERROR 1064 (42000): You have an error in your SQL syntax; Want to configure a password as root being the user

Question

I have just downloaded WAMP. I want to configure a password for the MySQL root user using MySQL console. No password has been set previously.

The following is the input

    mysql-> use mysql
    Database changed
    mysql-> UPDATE user
         -> SET Password=PASSWORD<'elephant7'>
         -> WHERE user='root';

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 'WHERE user='root'' at line 3

https://hsnyc.co/how-to-set-the-mysql-root-password-in-localhost-using-wamp/ — StudioTime, Mar 19 '16 at 07:25
So Windows and old MySQL? You didn't mention what version of MySQL **nor** the Operating System you are using. Hence all the answers are :"Try this.." You should always post your details. Downvoting. — B. Shea, Nov 13 '18 at 18:49

Sahith Vibudhi · Answer 1 · 2019-07-17T07:00:22.763

118

I was using MySQL 8 and non of the above worked for me.

This is what I had to do:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

edited Jul 17 '19 at 07:00

answered Jul 02 '19 at 05:11

Sahith Vibudhi

4,935
2
32
34

11

This worked for me on: `mysql ver 8.0.16 for osx10.14 on x86_64 (Homebrew)`. Update query resulted in syntax error. – Nishant Ghodke Jul 14 '19 at 12:42
2

Yup... only this works on: mysql ver 8.0.16 for mac installed with homebrew – Teddy Jul 16 '19 at 09:25
1

This was the only thing that worked for me been googling forever!! – Sam Johnson Jan 07 '20 at 22:37
1

This query is worked for me but new password is not working. It's only start to work after I recreated root user https://askubuntu.com/a/784347/160368. – Artem P Apr 05 '20 at 21:59
maybe you need to `flush privileges` after this query for new password to work. can you try and update? and upvote if the answer was helpful :) – Sahith Vibudhi Apr 06 '20 at 11:41
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement – Native Coder Sep 19 '20 at 02:57
That wroks for me in version 8.0.25. Mac m1 pro. – Cyrus Jul 02 '21 at 05:28

Jee Mok · Answer 2 · 2021-09-24T02:22:34.803

65

On MySQL 8.0.15 (maybe earlier than this too) the PASSWORD() function does not work anymore, so you have to do:

Make sure you have stopped MySQL first (Go to: 'System Preferences' >> 'MySQL' and stop MySQL).

Run the server in safe mode with privilege bypass:

sudo mysqld_safe --skip-grant-tables
mysql -u root
UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
exit;

Then

mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';

Finally, start MySQL again.

Enlightened by @OlatunjiYso in this GitHub issue.

edited Sep 24 '21 at 02:22

answered Sep 03 '20 at 03:28

Jee Mok

6,157
8
47
80

mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists. – Native Coder Sep 19 '20 at 03:01
2

@NativeCoder this might help: https://stackoverflow.com/questions/42153059/mysqld-safe-directory-var-run-mysqld-for-unix-socket-file-dont-exists – Jee Mok Sep 21 '20 at 00:53
2

The solution for me was to run mysqld_safe as root user. Then execute the version 8 style query – Native Coder Sep 21 '20 at 14:44
1

The steps above worked for me. I tried many different ways but only the above approach worked for me: Mac OS 11, 8.0.26 (MySQL Community Server - GPL) – uudaddy Oct 19 '21 at 16:22
1

In my case, a Ubuntu running in a Windows 10 64bits, was necessary for a Mysql 8 run before the ``` flush privilages ``` the folow comand ``` update user set plugin="mysql_native_password" where User='root' ``` . This workaround I found here: https://stackoverflow.com/questions/37879448/mysql-fails-on-mysql-error-1524-hy000-plugin-auth-socket-is-not-loaded – Zini Oct 23 '21 at 15:21
1

Thank you; this worked for me on MySQL 8.0.28 Homebrew on MacOS 10. – David Cary Feb 28 '22 at 01:55
1

This worked for me, MySQL 8.0.33 on Ubuntu 22.04.2. After all the steps, for restart I used `sudo killall mysqld` then `sudo systemctl restart mysql.service`. If I attempted restart with just `sudo service mysql start` it would hang. – Kalnode Jul 01 '23 at 01:22

White Feather · Accepted Answer · 2016-03-19T07:42:44.387

45

You can use:

SET PASSWORD FOR 'root' = PASSWORD('elephant7');

or, in latest versions:

SET PASSWORD FOR root = 'elephant7'

You can also use:

UPDATE user SET password=password('elephant7') WHERE user='root';

but in Mysql 5.7 the field password is no more there, and you have to use:

UPDATE user SET authentication_string=password('elephant7') WHERE user='root';

Regards

edited Mar 19 '16 at 07:42

answered Mar 19 '16 at 07:27

White Feather

2,733
1
15
21

1

Thank you very much, the last one worked for me. When i wrote the same lines as you it didn't work but on copying and pasting yours it did. I am sure there was no difference in syntax or case in both cases. – Syed Md Ismail Mar 19 '16 at 19:46
Happy to know it! Be kind and vote/accept answer. Regards – White Feather Mar 19 '16 at 21:48
1

`UPDATE ... SET` is the ticket for me. I'm using MariaDB 10.2. – Blairg23 Mar 07 '18 at 22:00
Query OK, 0 rows affected (0.00 sec) Rows matched: 3 Changed: 0 Warnings: 0 – ValRob Apr 20 '18 at 13:07
2

you need to `flush privileges` after all that or any change won't take effect. – Alan Deep Nov 10 '18 at 19:57
`SET PASSWORD FOR root = 'elephant7'` results in latest versions of MARIADB (10.1.44) in error: `ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number`. And `SET PASSWORD FOR 'root' = PASSWORD('elephant7');` does nothing, giving `0 rows affected`. – Eugene Zakharenko Mar 06 '20 at 10:48
1

Both of the bottom two resulted in syntax errors for me. The top two can't be executed because I'm using the --skip-grant-tables option. Version 8.0.21-0ubuntu0.20.04.4 – Native Coder Sep 19 '20 at 02:53

score 8 · Answer 4 · answered May 14 '20 at 08:38

8

I have problems with set password too. And find answer at official site

SET PASSWORD FOR 'root'@'localhost' = 'your_password';

answered May 14 '20 at 08:38

forest smoker

119
1
3

1

The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement – windmaomao Nov 02 '21 at 00:15

score 7 · Answer 5 · answered Aug 29 '19 at 23:24

7

This is the only command that worked for me. (I got it from M 8.0 documentation)

ALTER USER 'root'@'*' IDENTIFIED WITH mysql_native_password BY 'YOURPASSWORD';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YOURPASSWORD';

answered Aug 29 '19 at 23:24

Lucas Santos

2,991
3
19
29

4

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement – akikara Jan 21 '20 at 11:06

score 4 · Answer 6 · answered Feb 25 '21 at 19:57

The following commands (modified after those found here) worked for me on my WSL install of Ubuntu after hours of trial and error:

sudo service mysql stop
sudo mysqld --skip-grant-tables &
mysql -u root mysql
UPDATE mysql.user SET authentication_string=null WHERE User='root';
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_password_here';
flush privileges;
exit;

score 3 · Answer 7 · edited Mar 19 '16 at 08:06

3

Try this one. It may be helpful:

mysql> UPDATE mysql.user SET Password = PASSWORD('pwd') WHERE User='root';

I hope it helps.

edited Mar 19 '16 at 08:06

radoh

4,554
5
30
45

answered Mar 19 '16 at 07:29

JYoThI

11,977
1
11
26

Sergio Perez · Answer 8 · 2018-11-11T21:42:57.313

3

If you have ERROR 1064 (42000) or ERROR 1046 (3D000): No database selected in Mysql 5.7, you must specify the location of the user table, the location is mysql.table_name Then the code will work.

sudo mysql -u root -p

UPDATE mysql.user SET authentication_string=password('elephant7') WHERE user='root';

edited Nov 11 '18 at 21:42

answered Aug 10 '18 at 10:27

Sergio Perez

571
6
6

1

It is necessary to `flush privileges` which is being missed by many answerers. Thank you Sergio – Alan Deep Nov 10 '18 at 19:59

score 2 · Answer 9 · answered Mar 19 '16 at 07:26

2

Try this:

UPDATE mysql.user SET password=password("elephant7") where user="root"

answered Mar 19 '16 at 07:26

Wajih

4,227
2
25
40

Query OK, 0 rows affected (0.00 sec) Rows matched: 3 Changed: 0 Warnings: 0 – ValRob Apr 20 '18 at 13:07

score 2 · Answer 10 · edited Dec 22 '20 at 08:26

2

mysql> use mysql;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'my-password-here';

Try it once, it worked for me.

edited Dec 22 '20 at 08:26

Clemsang

5,053
3
23
41

answered Dec 22 '20 at 08:08

Manohar Nookala

21
1

score 1 · Answer 11 · edited Jan 11 '20 at 19:08

1

From the mysql documentation version: 8.0.18:

A superuser account 'root'@'localhost' is created. A password for the superuser is set and stored in the error log file. To reveal it, use the following command: shell> sudo grep 'temporary password' /var/log/mysqld.log Change the root password as soon as possible by logging in with the generated, temporary password and set a custom password for the superuser account:

shell> mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

edited Jan 11 '20 at 19:08

E. Zeytinci

2,642
1
20
37

answered Jan 11 '20 at 17:01

hiclas

31
9

This is just for centos. Not working on ubuntu flavours. – akikara Jan 21 '20 at 11:06
1

I don't think so, it's a SQL command, actually I ran it on Windows seven – hiclas Jan 27 '20 at 16:40
I've tried. At least not working for MySql Server 8.x Ubuntu 19.10. – akikara Jan 27 '20 at 16:42
regardless of whether it works for you or not, SQL is SQL. It's OS agnostic. @hiclas is right. – Native Coder Sep 19 '20 at 03:01

ravi KUMAR · Answer 12 · 2020-07-15T07:09:22.703

1

While using mysql version 8.0 + , use the following syntax to update root password after starting mysql daemon with --skip-grant-tables option

UPDATE user SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your_new_password')

edited Jul 15 '20 at 07:09

answered Jul 15 '20 at 06:58

ravi KUMAR

19
3

I am running version 8.0.21 using --skip-grant-tables. This throws a syntax error. – Native Coder Sep 19 '20 at 02:58

score 1 · Answer 13 · answered Jan 23 '21 at 08:54

1

This worked perfectly for me.

mysql> use mysql; mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'my-password-here';

answered Jan 23 '21 at 08:54

Hillys

9
3

score 0 · Answer 14 · answered Mar 06 '21 at 15:11

0

For mysql 8.0.23 based on Official Documentation

ALTER USER root@localhost SET ='New_Password';

For Windows 10 environment.

answered Mar 06 '21 at 15:11

Tri Hantoro

1

score 0 · Answer 15 · answered Sep 15 '21 at 14:29

0

click on start manager.
select MySQL and open it.
write the below code and press enter button

SET PASSWORD FOR 'root' = PASSWORD('elephant7');

answered Sep 15 '21 at 14:29

aezaz vahora

11
5

score 0 · Answer 16 · answered Mar 23 '22 at 20:13

0

For mysql 8.0.28

[thor@john ~]$ sudo -i
[root@app01 ~]# mysql -u root -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'P@ssw0rd123';
mysql> FLUSH PRIVILEGES;

answered Mar 23 '22 at 20:13

O'seun Iyadi

47
5

This information was already available in other answers. I don't see why it needs to be repeated over and over again. – trincot Mar 25 '22 at 21:44

score -2 · Answer 17 · answered Jan 10 '23 at 17:37

CREATE TABLE cas_num_folio_envio_finanzas ( next_not_cached_value bigint(21) NOT NULL, minimum_value bigint(21) NOT NULL, maximum_value bigint(21) NOT NULL, start_value bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', increment bigint(21) NOT NULL COMMENT 'increment value', cache_size bigint(21) unsigned NOT NULL, cycle_option tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', cycle_count bigint(21) NOT NULL COMMENT 'How many cycles have been done' ) ENGINE=InnoDB SEQUENCE=1;

If you have a new question, please ask it by clicking the [Ask Question](https://stackoverflow.com/questions/ask) button. Include a link to this question if it helps provide context. - [From Review](/review/late-answers/33602122) — Nuriddin Rashidov, Jan 14 '23 at 20:08

ERROR 1064 (42000): You have an error in your SQL syntax; Want to configure a password as root being the user

17 Answers17

Linked