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

User · Answer

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

User · Answer

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

User · Answer

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 gt use mysql    And then show the tables    mysql gt show tables    You will find the user table  now let s see its fields   mysql gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt  MySQL  gt   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

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

Examples related to mysql

Examples related to macos

Examples related to passwords

Examples related to root