Ошибка sql 1044

I have strange error. I am logged in local Mysql as root via command line. After creating database:

create database some_db;

Then giving privileges to some user:

grant all privileges on some_db.* to some_user@'localhost' identified by 'password';

This is giving error:

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'some_db'

The permissions for the root(show grants;) shows:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*8919C53DC7A4DFBF3F8584382E96463583EB7FDA' 

I am also making sure i am logged in as root:

select current_user();

And this confirms that i am logged in as ‘root’@’localhost’

I have created database and assigned user multiple times and never had an issue. Why i am unable assign user to the database created by root while logged in as root?

p.s. from other posts, i am thinking the issue may be caused due to some strange users

select host, user from mysql.user;

Some users that i have not added but does show up:

 MY_COMPuTER_name.local |       ''
 MY_COMPuTER_name.local |     root

I tried to delete these users

drop user 'root'@'MY_COMPuTER_name.local';
drop user ''@'MY_COMPuTER_name.local';

However, while it states query run successful, the users are not dropped even after flush privileges. Why i am unable delete users? Any help is much appricated

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»;

This page will assist you with troubleshooting a MySQL – 1044 “Access Denied” Error Message.

Troubleshooting the MySQL 1044 “Access Denied” Error

When you import a database using phpMyAdmin, generally you are 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.

-- phpMyAdmin SQL Dump -- version 2.11.9.5 -- https://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Apr 02, 2010 at 08:01 AM -- Server version: 5.0.81 -- PHP Version: 5.2.6   
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";   
CREATE DATABASE employees;   
-- --------------------------------------------------------   -- -- Table structure for table `employee_list` --   
CREATE TABLE 
IF NOT EXISTS `employee_list` ( `first_name` text NOT NULL, `last_name` text NOT NULL ) 
ENGINE=MyISAM 
DEFAULT CHARSET=latin1; 

When using phpMyAdmin to attempt to import such a file, you will receive an error message similar to:

 Error
 
SQL query:
  
 CREATE DATABASE employees;
  
 MySQL said: Documentation
 #1044 - Access denied for user 'training'@'localhost' to database 'employees'   

In this scenario, my cPanel username is Training. Because of cPanel’s database naming conventions, all database names must begin with the cPanel username followed by an “_”. I cannot create a database named Employees, however I can create a database named Training_employees.

Again, I cannot create a database named employees, however I can create 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:  

 Error
  
 SQL query:
  
 CREATE DATABASE training_employees;
  
 MySQL said: Documentation
 #1044 - Access denied for user 'training'@'localhost' to database 'training_employees' 

When using cPanel, databases must be created within the cPanel itself. To fix the issue, you will need to:

1. Create the: training_employees database within cPanel
2. Comment out the: CREATE DATABASE command in my .sql file. To do this, simply change: CREATE DATABASE employees; to — CREATE DATABASE employees; You are simply adding dash dash space to the front of the line to comment it out so that it will not be executed.
3. Log into phpMyAdmin, access the training_employees database, and then import as normal.

For Web Hosting packages

In the IONOS web hosting packages, the use of the MySQL commands CREATE DATABASE %DatabaseName% and USE %DatabaseName% are blocked for you for administrative reasons. Imports of SQL files that contain these commands fail with error 1044 — Access denied. To import a database nevertheless, for example to change the MySQL version, follow the steps below.

• Make a backup copy of the SQL file you want to import.
• Open the SQL file with a text editor, such as Notepad++.
• Search for strings that are CREATE DATABASE %Database name% or USE %Database name%. %DatabaseName% is a placeholder for the actual database name in your SQL file.
• Delete these commands. Be careful not to inadvertently remove other statements.
• Save your changes.

How to import the cleaned SQL file into a database is described in the article Restoring a MySQL Database with phpMyAdmin.

Example

The following extract from an SQL file contains the CREATE DATABASE command in line 11 and the USE command in line 12. Since there are no other commands in either line, you can delete these lines completely.

-- phpMyAdmin SQL Dump
-- version 2.6.4-pl3
-- http://www.phpmyadmin.net
--
-- Host: db123456789.hosting-data.io
-- Creation Date: 01. Januar 2013 um 12:00
-- Server Version: 5.3.3-7+squeeze14
--
-- Database `db123456789`
--
CREATE DATABASE `db123456789` DEFAULT CHARACTER SET latin1 COLLATE lating1_english2_ci;
USE db123456789;

Fixing Error #1046 — No Database Selected

If you encounter the message Error #1046 — No database selected in phpMyAdmin instead of Error #1044, simply click on the database name on the left side in phpMyAdmin. Your database is now selected, and the database name will then also be displayed in the upper-right corner by the server name. Now you can repeat the import process.

---