Mysql error 1044 42000 access denied for user localhost to database mysql

I developed my website, but many pages that access the database throw the error ‘SQLSTATE 42000 1044 access denied for user’. Can you help!

That was a recent support ticket received at our Outsourced Technical Support department where we resolve support queries for web hosts.

Website owners often face this error due to insufficient database privileges, typo errors in username/password, and more.

So, what’s the solution here? Well, the solution varies depending on the reason for this error.

Today, let’s discuss the top 5 reasons for this error and how our Dedicated Support Engineers fix it.

‘SQLSTATE 42000 1044 access denied for user’ – What this means?

Before we move on to the reasons for this error, let’s first get an idea of this error.

Website owners usually face this error when MySQL disallow access to a database.

For instance, the complete error message looks like this:

SQLSTATE[42000] [1044] Access denied for user 'test'@'localhost' to database 'test_database'

This error shows that MySQL denies the user ‘test’@’localhost’ access to the ‘test_database’ database.

[You don’t have to be a MySQL expert to keep your websites online. Our MySQL admins are available round the clock.]

‘SQLSTATE 42000 1044 access denied for user’ – Causes and Fixes

In our experience managing servers, let’s see the main causes of this error and how our Dedicated Support Engineers fix it.

1) Incorrect details in website configuration file

This is the most common reason for the error ‘SQLSTATE 42000 1044 access denied for user‘.

Database driven websites like WordPress, Drupal, etc. use the details in the website configuration file to connect to the database and fetch data.

So, typo errors in the database name, database username, password, hostname, database port, etc. can lead to errors.

How we fix?

In such cases, our Hosting Engineers recover the database details, and correct them in the website configuration files.

And, if we can’t recover the password, we reset it and update it in the website configuration file.

Also, we ensure that the new password adheres to the MySQL password policy.

For example, in cPanel servers, we reset the database user password from

cPanel > Databases > MySQL databases > MySQL users > Current users.

2) Database user doesn’t exist

Similarly, this error occurs when the user trying to access the database doesn’t exist on the MySQL server.

Also, this error can sometimes occur when the database user isn’t properly mapped to the database.

How we fix?

In such cases, our Support Engineers check whether the database user exists in the MySQL user table.

If not, we check the user’s requirement and if valid, we create a user with that username.

In addition to that, we assign this user to the corresponding database.

For instance, in cPanel servers, we map the database user to the database from cPanel > Databases > MySQL Databases > MySQL users > Add User to Database.

3) Insufficient database user permissions

Sometimes, database users don’t have the right privileges to access the database.

In such cases, website owners see this error ‘SQLSTATE 42000 1044 access denied for user‘

How we fix?

Here, our Hosting Engineers grant the user, proper privileges over the database to correct this problem.

For example, in cPanel servers, we assign access privileges to a user from here:

cPanel > MySQL databases > Current databases >Privileged users > Click on the database user

On plain servers, we assign the user privileges from command line.

For example, we use the below command to grant all privileges to the user, ‘test’@’localhost to the database ‘test_database’.

GRANT ALL PRIVILEGES ON test_database.* TO 'test'@'localhost';

And, in-order for the changes to reflect, and the privileges to be saved, we use the below command.

FLUSH PRIVILEGES;

[Struggling with database user permissions and privileges. Our MySQL experts are here for your help.]

4) Existence of anonymous users

Website owners face this error when there exist anonymous users like ‘ ‘@localhost or ‘ ‘ @127.0.0.1.

That is, when a client connects to the database, MySQL looks through the rows in the user table in a sorted way.

And, it uses the first row that matches the hostname and username.

So, here the anonymous user precedes all other users when connecting from localhost.

How we fix?

Our Support Engineers check the MySQL user table and remove the anonymous user.

For instance, we use the below command to remove the anonymous user from MySQL user table.

delete from user where User=' ';

5) Missing PDO module

Website developers see this error when trying to access the database using PDO.

PDOException: SQLSTATE[42000] [1044] Access denied for user 'test'@'localhost' to database 'test_database' in lock_may_be_available() (line 164 of /home/test/public_html/includes/lock.inc).

And, this often occurs due to the missing PDO module.

Most web hosts enable PDO module by default, but some web hosts may disable this module.

How we fix?

In such cases, our Hosting Engineers enable the PDO module on the server.

On cPanel servers, we enable it exclusively for the domain via the PHP Selector option.

[If you suspect missing PHP modules in your server. Our Support Experts can fix it for you within minutes.]

Conclusion

In short, ‘SQLSTATE 42000 1044 access denied for user’ error can occur due to insufficient user rights, typo in username/password, and more. Today, we’ve discussed the top 5 reasons for this error and how our Dedicated Support Engineers fix it.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Server Not Running in Specified Location

If the error you get is something like:

mysql -uname -p
ERROR 2002 (HY000): Can't connect to local MySQL server through 
  socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

or

mysql -uname -p --port=3307 --protocol=tcp
ERROR 2003 (HY000): Can't connect to MySQL server on  'localhost' 
  (111 "Connection refused")

the server is either not running, or not running on the specified port, socket or pipe. Make sure you are using the correct host, port, pipe, socket and protocol options, or alternatively, see Getting, Installing and Upgrading MariaDB, Starting and Stopping MariaDB or Troubleshooting Installation Issues.

The socket file can be in a non-standard path. In this case, the socket option is probably written in the my.cnf file. Check that its value is identical in the [mysqld] and [client] sections; if not, the client will look for a socket in a wrong place.

If unsure where the Unix socket file is running, it’s possible to find this out, for example:

netstat -ln | grep mysqld
unix  2      [ ACC ]     STREAM     LISTENING     33209505 /var/run/mysqld/mysqld.sock

Unable to Connect from a Remote Location

Usually, the MariaDB server does not by default accept connections from a remote client or connecting with tcp and a hostname and has to be configured to permit these.

(/my/maria-10.4) ./client/mysql --host=myhost --protocol=tcp --port=3306 test
ERROR 2002 (HY000): Can't connect to MySQL server on 'myhost' (115)
(/my/maria-10.4) telnet myhost 3306
Trying 192.168.0.11...
telnet: connect to address 192.168.0.11: Connection refused
(/my/maria-10.4) perror 115
OS error code 115:  Operation now in progress

To solve this, see Configuring MariaDB for Remote Client Access

Authentication Problems

Note that from MariaDB 10.4.3, the unix_socket authentication plugin is enabled by default on Unix-like systems. This uses operating system credentials when connecting to MariaDB via the local Unix socket file. See unix_socket authentication plugin for instructions on connecting and on switching to password-based authentication as well as Authentication from MariaDB 10.4 for an overview of the MariaDB 10.4 changes..

Authentication is granted to a particular username/host combination. user1'@'localhost', for example, is not the same as user1'@'166.78.144.191'. See the GRANT article for details on granting permissions.

Passwords are hashed with PASSWORD function. If you have set a password with the SET PASSWORD statement, the PASSWORD function must be used at the same time. For example, SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass') rather than just SET PASSWORD FOR 'bob'@'%.loc.gov' = 'newpass';

Problems Exporting Query Results

If you can run regular queries, but get an authentication error when running the SELECT … INTO OUTFILE, SELECT … INTO DUMPFILE or LOAD DATA INFILE statements, you do not have permission to write files to the server. This requires the FILE privilege. See the GRANT article.

Access to the Server, but not to a Database

If you can connect to the server, but not to a database, for example:

USE test;
ERROR 1044 (42000): Access denied for user 'ian'@'localhost' to database 'test'

or can connect to a particular database, but not another, for example
mysql -u name db1 works but not mysql -u name db2, you have not been granted permission for the particular database. See the GRANT article.

Option Files and Environment Variables

It’s possible that option files or environment variables may be providing incorrect connection parameters. Check the values provided in any option files read by the client you are using (see mysqld Configuration Files and Groups and the documentation for the particular client you’re using — see Clients and Utilities).

Option files can usually be suppressed with no-defaults option, for example:

mysqlimport --no-defaults ...

Unable to Connect to a Running Server / Lost root Password

If you are unable to connect to a server, for example because you have lost the root password, you can start the server without using the privilege tables by running the --skip-grant-tables option, which gives users full access to all tables. You can then run FLUSH PRIVILEGES to resume using the grant tables, followed by SET PASSWORD to change the password for an account.

localhost and %

You may have created a user with something like:

CREATE USER melisa identified by 'password';

This creates a user with the ‘%’ wildcard host.

select user,host from mysql.user where user='melisa';
+--------+------+
| user   | host |
+--------+------+
| melisa | %    |
+--------+------+

However, you may still be failing to login from localhost. Some setups create anonymous users, including localhost. So the following records exist in the user table:

select user,host from mysql.user where user='melisa' or user='';
+--------+-----------+
| user   | host      |
+--------+-----------+
| melisa | %         |
|        | localhost |
+--------+-----------+

Since you are connecting from localhost, the anonymous credentials, rather than those for the ‘melisa’ user, are used. The solution is either to add a new user specific to localhost, or to remove the anonymous localhost user.

See Also

• CREATE USER
• GRANT
• Authentication from MariaDB 10.4
• Authentication from MariaDB 10 4 video tutorial

This page will assist you with troubleshooting a MySQL — 1044 «Access Denied» Error Message.  A MySQL denial error sometimes occurs when a database is imported through phpmyadmin.

Note: If you are using cPanel, databases must be created using either the phpMyAdmin or the MySQL Database option.

1044 Access Denied Error Resolution

When you import a database using phpMyAdmin, normally you do so by importing a text file with a .sql extension. Here is a section of code that may be in a .sql database backup. In this example, the database we are trying to import is named employees.

In this scenario, the cPanel username is «training.» Because of cPanel’s database naming conventions, all database names must begin with the cPanel username followed by an _.  Using this format you can only creat a database named training_employees.

If I change the line that says: CREATE DATABASE so that it creates: training_employees instead of employees it will again fail with the following message: