Ошибка sql 1044 access denied for user - Ремонт и установка крупной бытовой техники

I want to begin writing queries in MySQL.

show grants shows:

+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+

I do not have any user-id but when I want to make a user I don’t have privilleges, also I don’t know how to make privileges when even I don’t have one user!

mysql> CREATE USER 'parsa'@'localhost' IDENTIFIED BY 'parsa';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER pr
ivilege(s) for this operation

I tried to sign in as root:

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> mysql -u root -p root;
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 root' at line 1

the Tin Man

158k42 gold badges215 silver badges303 bronze badges

asked Jan 12, 2012 at 16:44

No, you should run mysql -u root -p in bash, not at the MySQL command-line.
If you are in mysql, you can exit by typing exit.

answered Jan 12, 2012 at 16:58

NowhyNowhy

2,8841 gold badge16 silver badges13 bronze badges

You may need to set up a root account for your MySQL database:

In the terminal type:

mysqladmin -u root password 'root password goes here'

And then to invoke the MySQL client:

mysql -h localhost -u root -p

the Tin Man

158k42 gold badges215 silver badges303 bronze badges

answered Aug 12, 2012 at 11:16

soleshoesoleshoe

1,2052 gold badges11 silver badges16 bronze badges

I was brought here by a different problem.
Whenever I tried to login, i got that message because instead of authenticating correctly I logged in as anonymous user. The solution to my problem was:

To see which user you are, and whose permissions you have:

select user(), current_user();

To delete the pesky anonymous user:

drop user ''@'localhost';

answered May 22, 2013 at 8:50

Lefteris ELefteris E

2,7861 gold badge24 silver badges23 bronze badges

This is something to do with user permissions. Giving proper grants will solve this issue.

Step [1]: Open terminal and run this command

$ mysql -uroot -p

Output [1]:
This should give you mysql prompt shown below

enter image description here

Step [2]:

mysql> CREATE USER 'parsa'@'localhost' IDENTIFIED BY 'your_password';
mysql> grant all privileges on *.* to 'parsa'@'localhost';

Syntax:

mysql> grant all privileges on `database_name`.`table_name` to 'user_name'@'hostname';

Note:

hostname can be IP address, localhost, 127.0.0.1

In database_name/table_name, * means all databases

In hostname, to specify all hosts use ‘%’

Step [3]: Get out of current mysql prompt by either entering quit / exit command or press Ctrl+D.

Step [4]: Login to your new user

$ mysql -uparsa -pyour_password

Step [5]: Create the database

mysql> create database `database_name`;

answered Jan 23, 2018 at 9:15

theBuzzyCodertheBuzzyCoder

2,6322 gold badges31 silver badges26 bronze badges

You might want to try the full login command:

mysql -h host -u root -p

where host would be 127.0.0.1.

Do this just to make sure cooperation exists.

Using mysql -u root -p allows me to do a a lot of database searching, but refuses any database creation due to a path setting.

the Tin Man

158k42 gold badges215 silver badges303 bronze badges

answered Jun 23, 2012 at 4:46

RayRay

1111 silver badge2 bronze badges

If you are in a MySQL shell, exit it by typing exit, which will return you to the command prompt.

Now start MySQL by using exactly the following command:

sudo mysql -u root -p

If your username is something other than root, replace ‘root’ in the above command with your username:

sudo mysql -u <your-user-name> -p

It will then ask you the MySQL account/password, and your MySQL won’t show any access privilege issue then on.

Adrian Mole

49.6k155 gold badges50 silver badges80 bronze badges

answered Feb 26, 2014 at 5:49

Kshitij MittalKshitij Mittal

2,6583 gold badges25 silver badges40 bronze badges

First, if you are unfamiliar with the command line, try using phpmyadmin from your webbrowser. This will make sure you actually have a mysql database created and a username.

This is how you connect from the command line (bash):

mysql -h hostname -u username -p database_name

For example:

fabio@crunchbang ~ $ mysql -h 127.0.0.1 -u fabio -p fabiodb

fedorqui

273k103 gold badges545 silver badges595 bronze badges

answered Dec 22, 2012 at 22:44

fabiog1901fabiog1901

3423 silver badges12 bronze badges

connect mysql with sudo & gives permission for the necessary user using,

sudo mysql -u user;
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost';

answered Apr 20, 2021 at 5:09

@Nickparsa … you have 2 issues:

1). mysql -uroot -p
should be typed in bash (also known as your terminal) not in MySQL command-line. You fix this error by typing

exit

in your MySQL command-line. Now you are back in your bash/terminal command-line.

2). You have a syntax error:

mysql -uroot -p;

the semicolon in front of -p needs to go. The correct syntax is:

mysql -uroot -p

type the correct syntax in your bash commandline. Enter a password if you have one set up; else just hit the enter button. You should get a response that is similar to this:
enter image description here

Hope this helps!

Most Developers log-in to server(I assume you r having user-name and password for mysql database) then from Bash they switch to mysql> prompt then use the command below(which doesn’t work

mysql -h localhost -u root -p

What needs to be done is use the above command in the bash prompt—> on doing so it will ask for password if given it will take directly to mysql prompt and

then database, table can be created one by one

I faced similar deadlock so sharing the experience

answered Jul 9, 2013 at 14:03

DevrathDevrath

41.9k54 gold badges195 silver badges292 bronze badges

I had the command correct per above answers, what I missed on was on the Workbench, where we mention ‘Limit Connectivity from Host’ for the user, it defaults to «%» — change this to «localhost» and it connects fine thereafter!

answered Feb 24, 2016 at 15:35

killjoykilljoy

9201 gold badge11 silver badges16 bronze badges

I’m using roles to confer least privilege on my database application users. I kept getting ‘ERROR 1044 (42000): Access denied for user…’ until I RTFM and discovered I had to give each user a default role(s) in order their account could be authenticated when they logged in.

#create a role
CREATE ROLE 'rolename';

#give necessary privileges to role
GRANT INSERT, UPDATE, DELETE, SELECT ON database.table TO 'rolename';

#create user
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

#give the user a role(s)
GRANT 'rolename' TO 'username'@'host';

#set the user's default otherwise it's ERROR 1044
SET DEFAULT ROLE 'rolename' FOR 'username'@'host';

answered Apr 6, 2022 at 15:08

ClariusClarius

1,16310 silver badges9 bronze badges

Источник

I want to begin writing queries in MySQL.

show grants shows:

+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+

I do not have any user-id but when I want to make a user I don’t have privilleges, also I don’t know how to make privileges when even I don’t have one user!

mysql> CREATE USER 'parsa'@'localhost' IDENTIFIED BY 'parsa';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER pr
ivilege(s) for this operation

I tried to sign in as root:

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> mysql -u root -p root;
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 root' at line 1

the Tin Man

158k42 gold badges215 silver badges303 bronze badges

asked Jan 12, 2012 at 16:44

No, you should run mysql -u root -p in bash, not at the MySQL command-line.
If you are in mysql, you can exit by typing exit.

answered Jan 12, 2012 at 16:58

NowhyNowhy

2,8841 gold badge16 silver badges13 bronze badges

You may need to set up a root account for your MySQL database:

In the terminal type:

mysqladmin -u root password 'root password goes here'

And then to invoke the MySQL client:

mysql -h localhost -u root -p

the Tin Man

158k42 gold badges215 silver badges303 bronze badges

answered Aug 12, 2012 at 11:16

soleshoesoleshoe

1,2052 gold badges11 silver badges16 bronze badges

I was brought here by a different problem.
Whenever I tried to login, i got that message because instead of authenticating correctly I logged in as anonymous user. The solution to my problem was:

To see which user you are, and whose permissions you have:

select user(), current_user();

To delete the pesky anonymous user:

drop user ''@'localhost';

answered May 22, 2013 at 8:50

Lefteris ELefteris E

2,7861 gold badge24 silver badges23 bronze badges

This is something to do with user permissions. Giving proper grants will solve this issue.

Step [1]: Open terminal and run this command

$ mysql -uroot -p

Output [1]:
This should give you mysql prompt shown below

enter image description here

Step [2]:

mysql> CREATE USER 'parsa'@'localhost' IDENTIFIED BY 'your_password';
mysql> grant all privileges on *.* to 'parsa'@'localhost';

Syntax:

mysql> grant all privileges on `database_name`.`table_name` to 'user_name'@'hostname';

Note:

hostname can be IP address, localhost, 127.0.0.1

In database_name/table_name, * means all databases

In hostname, to specify all hosts use ‘%’

Step [3]: Get out of current mysql prompt by either entering quit / exit command or press Ctrl+D.

Step [4]: Login to your new user

$ mysql -uparsa -pyour_password

Step [5]: Create the database

mysql> create database `database_name`;

answered Jan 23, 2018 at 9:15

theBuzzyCodertheBuzzyCoder

2,6322 gold badges31 silver badges26 bronze badges

You might want to try the full login command:

mysql -h host -u root -p

where host would be 127.0.0.1.

Do this just to make sure cooperation exists.

Using mysql -u root -p allows me to do a a lot of database searching, but refuses any database creation due to a path setting.

the Tin Man

158k42 gold badges215 silver badges303 bronze badges

answered Jun 23, 2012 at 4:46

RayRay

1111 silver badge2 bronze badges

If you are in a MySQL shell, exit it by typing exit, which will return you to the command prompt.

Now start MySQL by using exactly the following command:

sudo mysql -u root -p

If your username is something other than root, replace ‘root’ in the above command with your username:

sudo mysql -u <your-user-name> -p

It will then ask you the MySQL account/password, and your MySQL won’t show any access privilege issue then on.

Adrian Mole

49.6k155 gold badges50 silver badges80 bronze badges

answered Feb 26, 2014 at 5:49

Kshitij MittalKshitij Mittal

2,6583 gold badges25 silver badges40 bronze badges

First, if you are unfamiliar with the command line, try using phpmyadmin from your webbrowser. This will make sure you actually have a mysql database created and a username.

This is how you connect from the command line (bash):

mysql -h hostname -u username -p database_name

For example:

fabio@crunchbang ~ $ mysql -h 127.0.0.1 -u fabio -p fabiodb

fedorqui

273k103 gold badges545 silver badges595 bronze badges

answered Dec 22, 2012 at 22:44

fabiog1901fabiog1901

3423 silver badges12 bronze badges

connect mysql with sudo & gives permission for the necessary user using,

sudo mysql -u user;
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost';

answered Apr 20, 2021 at 5:09

@Nickparsa … you have 2 issues:

1). mysql -uroot -p
should be typed in bash (also known as your terminal) not in MySQL command-line. You fix this error by typing

exit

in your MySQL command-line. Now you are back in your bash/terminal command-line.

2). You have a syntax error:

mysql -uroot -p;

the semicolon in front of -p needs to go. The correct syntax is:

mysql -uroot -p

Hope this helps!

Most Developers log-in to server(I assume you r having user-name and password for mysql database) then from Bash they switch to mysql> prompt then use the command below(which doesn’t work

mysql -h localhost -u root -p

What needs to be done is use the above command in the bash prompt—> on doing so it will ask for password if given it will take directly to mysql prompt and

then database, table can be created one by one

I faced similar deadlock so sharing the experience

answered Jul 9, 2013 at 14:03

DevrathDevrath

41.9k54 gold badges195 silver badges292 bronze badges

answered Feb 24, 2016 at 15:35

killjoykilljoy

9201 gold badge11 silver badges16 bronze badges

#create a role
CREATE ROLE 'rolename';

#give necessary privileges to role
GRANT INSERT, UPDATE, DELETE, SELECT ON database.table TO 'rolename';

#create user
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

#give the user a role(s)
GRANT 'rolename' TO 'username'@'host';

#set the user's default otherwise it's ERROR 1044
SET DEFAULT ROLE 'rolename' FOR 'username'@'host';

answered Apr 6, 2022 at 15:08

ClariusClarius

1,16310 silver badges9 bronze badges

Источник

I tried a lot to import the sql script to create database and tables through phpmyadmin in the new site I hosted. But i’m getting the error,

1044 — Access denied for user ‘user’@’localhost’ to database ‘db’

I tried to create a sample db directly : create database sampled; I’m getting the same access denied error.

I’m not able to grant privileges to the user also, i’m getting the same access denied error.

Following is the output of show grants command,

show grants;
GRANT USAGE ON . TO 'someuser'@'localhost' IDENTIFIED BY PASSWORD 'somepw' 

GRANT ALL PRIVILEGES ON someuser_%.* TO 'someuser'@'localhost'

Any help would be appreciated. thank you.

asked Jan 30, 2014 at 4:24

If you are using Godaddy then don’t directly go to phpMyAdmin and run the sql command.

You have to go to MySQL® Databases section and create a database there.
Then create a user and give it the permission to access the database you just created.
Now you can go to phpMyAdmin and write your SQL commands.

Hope this helps

answered Dec 16, 2015 at 19:01

55110022335511002233

4938 silver badges19 bronze badges

This error can be avoided in the beginning when creating the user account. Following commands must be used to create user account having all the PRIVILEDGES.

CREATE USER 'demouser'@'localhost' IDENTIFIED WITH mysql_native_password BY '***';

GRANT ALL PRIVILEGES ON *.* TO 'demouser'@'localhost' WITH GRANT OPTION;

ALTER USER 'demouser'@'localhost' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

CREATE DATABASE IF NOT EXISTS 'demouser';

GRANT ALL PRIVILEGES ON `demouser`.* TO 'demouser'@'localhost';

answered Aug 3, 2022 at 17:22

It is clear that the user someuser do not have proper privilege over the database db. You need to grant privileges for the user over the database to correct this issue.

If you do bot have administrative rights contact your admin for granting privileges to someuser on that db

answered Jan 30, 2014 at 4:28

Abdul ManafAbdul Manaf

4,7603 gold badges27 silver badges34 bronze badges

For me the problem is I’m not having permissions to Create new Databasename or even Modify the name of the Database. You can contact your admin to avail privileges or if you want to quickly import the .sql file… then you can open the .sql file with text editor and find the following line:

CREATE DATABASE IF NOT EXISTS `enter the existing db name` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `enter the existing db name`;

After that try to import the .sql file…now you can successfully import all the tables!

answered May 7, 2018 at 13:29

BharathRaoBharathRao

1,8161 gold badge17 silver badges28 bronze badges

I had the same problem i solve it buy change the GRANTEE and made the user he can do all the operation in PhpMyAdmin ,follow the steps :

Enter to mysql in root permission but :

sudo mysql
Enter the password, and type this :

SELECT * FROM information_schema.user_privileges;
And see the user in IS_GRANTABLE is he yes or no.
If he yes then see the PRIVILEGE_TYPE what it might it be just tow operation if it’s true add the last operations.
and see this link to change IS_GRANTABLE from no to yes.
I wish this could help you.

answered Mar 7, 2020 at 11:28

Must Ensure that your User_Name, Password and Database name are correct.
If you are Deal with database ‘sampled’ then type ‘sampled’ in query instead of ‘db’ also must ensure that you don’t have to use quota(‘) in Statement.

answered Mar 29, 2017 at 2:49

you have not exported database from localhost or from web server correctly follow these steps go to localhost database click export tab than select ‘Custom — display all possible options ‘check box few options will open in section ‘output’ change compression none to zipped and now click go button at bottom it will properly export your database you can now import to hosting or anywhere you want with no errors

answered Mar 31, 2016 at 22:51

I started getting this error message for no reason at all. Solved by using MySQL and the Server menu option; Selected my bitnami_wordpress schema; selected the bn_wordpress user; then picked the Administrative Roles tab and granted access to this user. I had to do this to all of the users that the access was removed from.

Andrew

1,7351 gold badge21 silver badges29 bronze badges

answered Mar 4, 2021 at 16:02

Источник

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.

sqlstate 42000 1044 access denied for user

MySQL databases option in cPanel

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

sqlstate 42000 1044 access denied for user

How to set database user privileges in cPanel

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.

sqlstate 42000 1044 access denied for user

PHP Selector in cPanel

[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.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

SEE SERVER ADMIN PLANS

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.

The reason this import failed is because of the following line in the .sql file:

CREATE DATABASE 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:

Create the: training_employees database within cPanel
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.
Log into phpMyAdmin, access the training_employees database, and then import as normal.

Источник