[SOLVED] How to properly resolve ERROR 1044 on mySQL? Other answers here don't do the trick

Issue

I am studying the SQL language and I am trying to use MySQL on Linux Mint to test my work, however I encounter issues like ERROR 1044. I have tried to follow all the various kinds of advice here but I then encounter a different error code and then in an attempt to solve the new issue I see another error code, and in an attempt to solve this other new issue I see another error code, and repeat ad nauseam. So I am starting from scratch in the hopes that my particular problem can be resolved.

I tried this

mysql> USE preciousMetals;
ERROR 1044 (42000): Access denied for user 'phpmyadmin'@'localhost' to database 'preciousMetals'

Checked this

mysql> show grants;
+--------------------------------------------------------------------+
| Grants for phpmyadmin@localhost                                    |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'phpmyadmin'@'localhost'                     |
| GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO 'phpmyadmin'@'localhost' |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

I enter this command into the prompt

mysql> select user ();
+----------------------+
| user ()              |
+----------------------+
| phpmyadmin@localhost |
+----------------------+
1 row in set (0.00 sec)

Then this

mysql> select current_user ();
+----------------------+
| current_user ()      |
+----------------------+
| phpmyadmin@localhost |
+----------------------+
1 row in set (0.00 sec)

I want to keep this question neat and "small" for now so it won't be all over the place. I will add that I tried to go in as root as some posts have said but I don't have a password for that. I never set a password for root only for user during installation. I tried the user password and ERROR. Tried to look for it or reset it, and apparently I don't have any privileges or rights or something. I try to fix that and, you know it, another ERROR. >:O

Can someone help on how to solve ERROR 1044? Is there a reason that I encounter so many issues? Is it a buggy RDBMS or is the issue related to my actual computer and the way it handles things? Is there a good solution to get rid of all problems and just use the RDBMS without the Some Assembly Required thing.

Solution

Is it a buggy RDBMS ... ?

It's not a bug, it's a feature!

(I can't tell you how long I've waited to say that without irony.)

Your grants show that your phpmyadmin user has privileges only on the phpmyadmin database.

The privilege USAGE ON *.* means you can connect to the MySQL server, but it does not mean you have any privileges on any of the databases on that server.

The privilege ALL PRIVILEGES ONphpmyadmin.* means you have privileges on all the tables of the phpmyadmin database. You have no privileges on any other database. So when you try to USE preciousMetals; it immediately denies the request.

The fact that you don't have permissions to access some particular resource is not a bug in the software.

By analogy, this is like on Linux if you try to cd <directory> if you don't have permissions to do that. You would — correctly — get an error if you did that.

$ cd secret_directory
-bash: cd: secret_directory: Permission denied

You will need to grant the phpmyadmin user greater privileges. Do you have access to the root MySQL account? That's typically the one that has all the privileges on all databases, and the ability to grant privileges to other MySQL users.

As for your second question, there is at least one other SQL database product that doesn't enforce GRANT/REVOKE or privileges at all: SQLite. As long as you have access at the Linux level to open a database file, you have free access to all tables within it. There's no access privilege system implemented. But SQLite is not exactly an alternative to MySQL. There's no support for concurrent multi-user access, or network connections, or a number of other features you probably need.

---

For MySQL, you can run it in a mode with no enforcement of passwords or privileges.

1. Edit the MySQL options file (typically /etc/my.cnf on Linux systems).

2. Add the option to disable enforcement. This should go anywhere below the [mysqld] option group in the option file.

   [mysqld]
   skip_grant_tables
   

3. Restart the mysqld process. This is done with a shell command as the Linux root user:

   $ sudo service mysql restart
   

After that, you will not be required to enter a password, and there is no enforcement of SQL privileges.

This is obviously not the way you would use MySQL for a live application, because it compromises all security. But if you're struggling with privileges and you just want to learn SQL on your local instance, go ahead.

Answered By - Bill Karwin

Answer Checked By - Marilyn (WPSolving Volunteer)