2

I'm using MySQL on windows (I wish I was using Mac, but due to some issues I'm using Bootcamp), and I've encountered a problem I think I've encountered before: the error that is written in the title. I think I might have solved this once before by erasing MySQL through the control panel and by erasing everything in the hidden folder, ProgramData, and MySQL from ProgramFiles, then reinstalling it. It is such a hassle and consumes a lot of time. It definitely isn't something I can do repeatedly to solve this problem if I get an error like this again and again.

The problem:

I simply ran this code on CMD after "cd"ing to the bin folder that contains mysql: mysql -u root -p PASSWORD but I get the error written in the title. Thus I can't login to mysql and run any lines to edit the database.

There are so many other topics like this in stackoverflow:

  1. MySQL Error: : 'Access denied for user 'root'@'localhost'

  2. Access Denied for User 'root'@'localhost' (using password: YES) - No Privileges?

  3. https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

I tried implementing the solutions from the three links above. Many other advices advise to use the "sudo" command, which is not a windows command. This is the line I get from implementing the solution given to me by dev.mysql.com (#3):

This is the line of code I ran in CMD:

mysqld --init-file=C:\\mysql-init.txt --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --console

ADMIN CMD MySQL

This is the code I ran to implement the solution in link #2:

mysqld --skip-grant-tables --skip-networking --console

and the code I get back:

ADMIN CMD MySQL

Things to note:

  • Each time, I get this line of code back from the console: [Warning]...[Server] CA certificate ca.pem is self signed.
  • In the first picture, this is the "ERROR" I get from the console: [ERROR]...[Server] unknown variable 'defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini'
  • In the second picture, this is the "ERROR" I get from the console: [ERROR] TCP/IP --shared memory, or --named-pipe should be configured on NT OS
  • This is the line of code I would put in in mysql-init.txt: ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORDHERE';
  • or this: INSERT INTO mysql.user (Host, User, Password) VALUES ('%', 'root', password('YOURPASSWORD')); GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;
  • I installed MySQL through the installation wizard.

Q. How did I encounter this error?:

A. I was experimenting on a project for a back-end Spring Boot project that uses JPA and JDBC combined. I got errors telling me that no datasource could be configured.

Q.How did my root password get reset?

A.I think they got reset because of JPA's datasource autoconfiguration. I didn't touch any of mysql's settings myself. Something I did on Spring Boot and its automatic configuration to localhost must have reset the password.

DP Park
  • 815
  • 1
  • 9
  • 19
  • Possible duplicate of [MySQL Error: : 'Access denied for user 'root'@'localhost'](https://stackoverflow.com/questions/41645309/mysql-error-access-denied-for-user-rootlocalhost) – Reyan Chougle Dec 17 '18 at 06:47
  • @ReyanChougle I tried implementing the solution written in that article without success, as I think I implied in what I wrote above. – DP Park Dec 17 '18 at 07:07

2 Answers2

0

I will say that uninstall your MySQL and again install it with a new instance but not like just uninstall it from the control panel that's why you are facing the same error every time. I already answered here in detail that how to remove properly so no error will come by creating a new instance.

AmeeQ
  • 117
  • 1
  • 3
  • 12
  • I successfully installed a new instance before. It worked fine - I made several connections to it through a "Driver" on Java. I edited, updated, saved, etc. I could no longer connect to it after I tried to make a connection through JPA on Spring Boot. If this happens again and again, I need a solution other than uninstalling and reinstalling it - doing so takes lots of time. – DP Park Dec 17 '18 at 07:06
  • Hmm, of course uninstalling and reinstalling is not the solution then maybe there is a problem in your connection string OR I think you should try one more time uninstall it with my way as I have mentioned in an above answer maybe it solves your problem because it seems like you have installed and uninstalled MySQL so many times because of multiple references in your system not allowing you the access of "root@localhost" just uninstall it with my way then connect through JPA on spring boot hope it will solve your issue. – AmeeQ Dec 17 '18 at 07:39
0

For anyone encountering this issue after they initialized their mysql server with following command:

mysqld --initialize

Look out for the last output of the process, it should say something like this:

[Note] [MY-010454] [Server] A temporary password is generated for root@localhost: password12345

there is your temporary password to log in.

PssstZzz
  • 349
  • 1
  • 3
  • 11