Initially I tried to setup a connection between MySQL and Node.JS and got error ER_NOT_SUPPORTED_AUTH_MODE, which I tried to fix by issuing ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password' and flushing privileges as described here. But after that I got another error ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'localhost', which I tried to solve by dropping and creating new user, among other steps as suggested on this page.
However, now I am unable to create any database at all, getting ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'test'. I login successfully to MySQL command line client with the password that I setup during the installation but it seems I am not allowed to perform any operation there. How could I restore my privileges to create databases on MySQL v 8.0 on Windows 10?
Here is some other output which I get after issuing certain commands:
mysql> select user(), current_user;
+----------------+----------------+
| user() | current_user |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)
mysql> CREATE DATABASE test;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'test'
mysql> mysql -u root -p;
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 'mysql -u root -p' at line 1
mysql>
