MySQL user DB does not have password columns - Installing MySQL on OSX

Question

I am trying to change MySql root password.

What I have done is below.

Install MySql-5.7.6 ~ .dmg(Community Server) and workbench.
Turn off the server on OSX System preferences.
Access MySql with console. The command was mysqld_safe --skip-grant
Execute update user set password=password('1111') where user='root'; and got an error message --> ERROR 1054 (42S22): Unknown column 'password' in 'field list'.

FYI, I did use mysql;. So I did select query on user table and found password column actually does not exist.

It is very weird. Is it possible that original user table does not have password column?

How can I change password, which does not exist?

Thanks for your answer :D

Now I test again and I can access MySQL without PW :( Is it fine to change (I mean alter user table and add password column via query) user table? — Juneyoung Oh, Jun 07 '15 at 11:14
SET PASSWORD for root@localhost = password('new-pass') -- works for any mysql versions — SIDU, Mar 14 '17 at 04:11
`UPDATE mysql.user SET authentication_string= 'password' WHERE User = 'root';` Because the field 'Password' was removed by mysql and replaced with authentication_string. Note: that the mysql function PASSWORD('password') relies on MD5 algorithm which was cracked long ago using the birth day attack. So using it gives a false sense of security because an attacker can just paste the hashes it produces in a public website like https://hashkiller.co.uk/md5-decrypter.aspx and retrieve your plain text password. — Dr Deo, Sep 21 '18 at 08:42

nodejh · Accepted Answer · 2016-07-26T10:45:27.427

In MySQL 5.7, the password field in mysql.user table field was removed, now the field name is 'authentication_string'.

First choose the database:

mysql>use mysql;

And then show the tables:

mysql>show tables;

You will find the user table, now let's see its fields:

mysql> describe user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(16)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

Surprise！There is no field named 'password', the password field is named ' authentication_string'. So, just do this:

update user set authentication_string=password('1111') where user='root';

Now, everything will be ok.

Compared to MySQL 5.6, the changes are quite extensive: What’s New in MySQL 5.7

This method works even with the --skip-grant-tables restriction when starting mysqld. Since you are an anonymous user you can't change passwords with: ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password' ALTER USER 'root'@'*' IDENTIFIED BY 'new-password' It'll give you an anonymous user permissions error. — Bisonfan95, Nov 17 '15 at 19:06
changing the password field name... one decision that caused so many people so much troubles... — talsibony, Jul 19 '17 at 10:00
Even after executing this command I'm not able to login with the password update user set authentication_string=password('1111') where user='root'; I'm getting ERROR 1698 (28000): Access denied for user 'root'@'localhost' — Sreenath, Nov 08 '18 at 08:47
First MySQL for macOS uses a BAD password by default (includes invalid characters so I can't even connect to root on initial setup). Then they don't allow 127.0.0.1, so I have to manually grant it. But before I do that, I have to ALTER USER. And now they changed the field name. Absolute insanity. — ninjaneer, Apr 03 '19 at 03:35
When resetting root password at step 2), also change the auth plugin to mysql_native_password: `use mysql;` `update user set authentication_string=PASSWORD("") where User='root';` `update user set plugin="mysql_native_password" where User='root'; # THIS LINE` `flush privileges;` `quit;` — Viswa, Nov 19 '19 at 08:56

bl79 · Answer 2 · 2017-04-19T14:43:06.890

27

This error happens if you did not set the password on install, in this case the mysql using unix-socket plugin.

But if delete the plugin link from settings (table mysql.user) will other problem. This does not fix the problem and creates another problem. To fix the deleted link and set password ("PWD") do:

1) Run with --skip-grant-tables as said above.

If it doesnt works then add the string skip-grant-tables in section [mysqld] of /etc/mysql/mysql.conf.d/mysqld.cnf. Then do sudo service mysql restart.

2) Run mysql -u root -p, then (change "PWD"):

update mysql.user 
    set authentication_string=PASSWORD("PWD"), plugin="mysql_native_password" 
    where User='root' and Host='localhost';    
flush privileges;

quit

then sudo service mysql restart. Check: mysql -u root -p.

Before restart remove that string from file mysqld.cnf, if you set it there.

edited Apr 19 '17 at 14:43

answered Apr 19 '17 at 14:32

bl79

1,005
1
10
19

Spot on! Needed plugin="mysql_native_password" and the flush privileges. Couldn't get this to work with any other example. Thank you bl79! – TheRealWebGuy Aug 31 '18 at 16:55
1

I get `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 '("MYNEWPASSWORD"), plugin="mysql_native_password"` – Chance Smith Jan 12 '19 at 15:33
your answer is Great! this is one answer helped me during 6 hours fight – Alexey Nikonov Aug 12 '19 at 15:44
plugin="mysql_native_password" Done the rick for me – Joyner Mar 05 '20 at 11:14
Perfectly Works for me – Mayank Sharma Apr 06 '20 at 09:39

score 24 · Answer 3 · edited Aug 07 '16 at 22:49

24

One pitfall I fell into is there is no password field now, it has been renamed so:

update user set password=PASSWORD("YOURPASSWORDHERE") where user='root';

Should now be:

update user set authentication_string=password('YOURPASSWORDHERE') where user='root';

edited Aug 07 '16 at 22:49

Alexander Wigmore

2,975
4
32
57

answered Aug 07 '16 at 20:49

John C

241
2
2

score 18 · Answer 4 · answered Jun 07 '15 at 11:38

18

Use the ALTER USER command rather than trying to update a USER row. Keep in mind that there may be more than one 'root' user, because user entities are qualified also by the machine from which they connect

https://dev.mysql.com/doc/refman/5.7/en/alter-user.html

For example.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password' 
ALTER USER 'root'@'*' IDENTIFIED BY 'new-password'

answered Jun 07 '15 at 11:38

O. Jones

81,279
15
96
133

2

For MySQL 5.7.9, I have to ALTER USER to work properly. Thanks. – shin Oct 30 '15 at 00:09
we've needed to user \g followed by the above command. Using MySql 5.7.12. ty – bobsouza Apr 20 '16 at 21:46
this answer only worked for me, on Ubuntu 16.04, mysql 5.7.19; – A1Gard Jul 31 '17 at 18:49
Did they change the wildcard from @ to *? Grr... now I have more questions than when I came here. – pguardiario Aug 16 '17 at 05:29

score 4 · Answer 5 · answered Apr 30 '16 at 18:24

It only worked with me when I "flushed" after the commands mentioned here. Here's the full list of commands I used:

Previous answers might not work for later mysql versions. Try these steps if previous answers did not work for you:

1- Click on the wamp icon > mysql > mysql console

2- write following commands, one by one

use mysql;
update user set authentication_string=password('your_password') where user='root';
FLUSH PRIVILEGES;
quit

score 4 · Answer 6 · edited Sep 19 '16 at 10:10

Thank you for your help. Just in case if people are still having problems, try this.

For MySQL version 5.6 and under

Have you forgotten your Mac OS X 'ROOT' password and need to reset it? Follow these 4 simple steps:

Stop the mysqld server. Typically this can be done by from 'System Prefrences' > MySQL > 'Stop MySQL Server'
Start the server in safe mode with privilege bypass From a terminal: sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
In a new terminal window: sudo /usr/local/mysql/bin/mysql -u root UPDATE mysql.user SET Password=PASSWORD('NewPassword') WHERE User='root'; FLUSH PRIVILEGES; \q
Stop the mysqld server again and restart it in normal mode.

For MySQL version 5.7 and up

Stop the mysqld server. Typically this can be done by from 'System Prefrences' > MySQL > 'Stop MySQL Server'
Start the server in safe mode with privilege bypass From a terminal: sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
In a new terminal window: sudo /usr/local/mysql/bin/mysql -u root UPDATE mysql.user SET authentication_string=PASSWORD('NewPassword') WHERE User='root'; FLUSH PRIVILEGES; \q
Stop the mysqld server again and restart it in normal mode.

This help me while installing mysql < 5.7 – Ankit Agarwal May 09 '18 at 18:21 — Ankit Agarwal, May 09 '18 at 18:21

score 3 · Answer 7 · answered Apr 18 '17 at 02:35

For this problem, I used a simple and rude method, rename the field name to password, the reason for this is that I use the mac navicat premium software in the visual operation error: Unknown column 'password' in 'field List ', the software itself uses password so that I can not easily operate. Therefore, I root into the database command line, run

Use mysql;

And then modify the field name:

ALTER TABLE user CHANGE authentication_string password text;

After all normal.

score 0 · Answer 8 · answered Jun 03 '18 at 01:48

Root Cause: root has no password, and your python connect statement should reflect that.

To solve error 1698, change your python connect password to ''.

note: manually updating the user's password will not solve the problem, you will still get error 1698

score 0 · Answer 9 · answered Aug 30 '18 at 18:23

0

remember password needs to be set further even after restarting mysql as below

SET PASSWORD = PASSWORD('root');

answered Aug 30 '18 at 18:23

Pravin Bansal

2,543
20
12

MySQL user DB does not have password columns - Installing MySQL on OSX

9 Answers9

Linked