Mysql connect error access denied for user bitrix localhost using password yes

Обратите внимание: при редактировании файлов баз данных сторонними программами (Sublime Text, Notepad++ и др.) всегда учитывайте кодировку в которой открывается и сохраняется .sql файл базы данных. В случае допуска ошибки могут проявляться малопредсказуемые последствия работы сайта.

Duplicate entry ‘1’ for key ‘PRIMARY’

Требуется заменить во всей базе данных INSERT INTO на REPLACE INTO.

Multiple primary key defined

Убедитесь, что в базе в которую производится импорт нет каких-либо данных (база должна быть полностью очищена).

[BitrixMainDBSqlQueryException] Mysql query error: Illegal mix of collations for operation ‘UNION’ (400)

Проблема с разными кодировками таблиц (collation utf8_unicode_ci и utf8_general_ci).
Требуется заменить DEFAULT CHARSET=utf8 на DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci во всей базе данных.

MySQL Query Error: SELECT DISTINCT BE.ID as ID … which is not in SELECT list; this is incompatible with DISTINCT

Необходимо очистить содержимое папки /bitrix/tmp/

[BitrixMainDBConnectionException] Mysql connect error [localhost]: (1045) Access denied for user ‘***’@’localhost’ (using password: YES) (400)

Неверные данные для подключения к базе данных. Проверьте название базы данных, имя назначенного пользователя и его пароль в панели хостинга и отредактируйте данные в файлах /bitrix/php_interface/dbconn.php и /bitrix/.settings.php

Переменная sql_mode в MySQL должна быть пустая, текущее значение…

/bitrix/php_interface/after_connect_d7.php
добавить $connection->queryExecute(«SET sql_mode=»»);

/bitrix/php_interface/after_connect.php
добавить $DB->Query(«SET sql_mode=»»);

Ошибка innodb_strict_mode=ON, требуется OFF или Ошибка! Переменная sql_mode в MySQL должна быть пустая, текущее значение…

/bitrix/php_interface/after_connect.php
$DB->Query(«SET sql_mode=»»);
$DB->Query(«SET innodb_strict_mode=0»);

/bitrix/php_interface/after_connect_d7.php
$connection = BitrixMainApplication::getConnection();
$connection->queryExecute(«SET sql_mode=»»);
$connection->queryExecute(«SET innodb_strict_mode=0»);

Oops!! Frustrated with ‘Access denied for user ‘root’@’localhost’ (using password yes) in MySQL?

This error may occur due to many reasons which include insufficient privileges for the user to access the database, and when MySQL root password is wrong and so on.

At Bobcares, we often get requests to fix MySQL errors, as a part of our Server Management Services.

Today, let’s see how our Support Engineers fix this MySQL for our customers.

Why does access denied error occurs in MySQL?

This error may occur due to different reasons in different scenarios. Some of them are:

• When a user who doesn’t exist on the MySQL server tries to access the database.
• When there is no privilege for the user.
• If the username or password is wrong.

Today, we’ll discuss in detail how we fix this Access denied for user ‘root’@’localhost’ (using password yes) in MySQL.

How we fix ‘access denied for user ‘root’@’localhost’ (using password yes) in MySQL’?

Let’s see, how we fix this access denied error for our customers in detail.

a. Not able to login

Recently, one of our customers approached us saying that he is getting an error like the one shown below while he is trying to log in to MySQL.

So, our Support Engineers checked in detail and found that the root password is wrong. So, to reset the password we did the following:

1. Initially, we opened /etc/mysql/my.cnf

2. Then we added skip-grant-tables under [mysqld] section.

Here we skipped grant tables as there is no other way to log in.

By skipping the grant table option it allows anyone to log in from anywhere and can do anything on the database.

Note: skip-grant-tables is dangerous, so we will remove this at the end.

3 Then, we restarted the MySQL service using the command below:

service mysql restart

Now MySQL is configured to skip the grant-tables. So, we can log in now.

4. We logged into MySQL using:

mysql -u root 

5. Then, we flushed the privileges using:

6. After that, we set a new password using the command below: UPDATE user SET

Password=PASSWORD('my_password') where USER='root';
FLUSH PRIVILEGES;

7. Then, we removed skip-grant-tables from /etc/mysql/my.cnf

8. Finally, we restarted the MySQL service and logged in to MySQL using the new password.

This fixes the error.

b. No global privilege

If there is no global privilege then this error can happen. In such cases, our Engineers add the privilege by:

GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost';

This fixes the error.

[Need more assistance to fix MySQL Error?- We’re available 24/7.]

Conclusion

In short, this error occurs mainly due to insufficient privileges to the user and can be fixed by granting the privilege. Also, today we discussed in detail how our Support Engineers fix this error for our customers.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Are you looking for a solution to fix the MySQL ERROR 1045 (28000): Access denied for user ‘user’@’localhost’ (using password: YES) error message?

If you’re an experienced MySQL database manager, you may have come across some vague and confusing problems, like MySQL Error 1045 (28000) messages. Fortunately, while resolving this error can be confusing at first due to its many potential causes, its solutions tend to be relatively simple. Once you determine the reason behind the database error you’re seeing, you should be able to fix it fairly quickly.

In this post, we’ll cover the various possible causes of the MySQL Error 1045 (28000). Then we’ll share solutions for each common situation, to help you get your database and your site back up and running.

Why the MySQL Error 1045 (28000) Error Occurs

The MySQL Error 1045 (28000) is an authentication error. «MySQL ERROR 1045 (28000): Access denied for user ‘user’@’localhost’ (using password: YES)» typically indicates that MySQL cannot log you in using the username and password you’ve specified.

This usually occurs when you provide an incorrect username/password combination when making the database connection. However, there are many other different situations that can lead to this type of behaviour.

• Connecting to the wrong host or port
• Provided user does not exist
• User does exist but client host have insufficient permission to connect, or be IP banned
• Wrong password provided
• Bash converts special characters in the password to a different encoding

Wrong host or port

If you don’t specify the host to connect (with the -h flag), MySQL client will try to automatically connect to the localhost instance. You may be trying to connect to another host/port instance. In this case, simply add -h flag followed by the hostname you’re trying to connect to.

Code language: HTML, XML (xml)

Also, double check if you are trying to connect to the right port. MySQL, by default, listen on port 3306, but different setups use different ports for security purposes. Providing -P flag and a port number in the command is a great way to remind you of this detail.

[[email protected]]# mysql -u root -psekret -h <IP_or_hostname> -P 3306
Code language: HTML, XML (xml)

Provided user does not exist

MySQL stores user data in a table called user in a database named mysql (by default). The following query will return 1 if a user with the specified username exists, 0 otherwise.

mysql> SELECT EXISTS(SELECT 1 FROM mysql.user WHERE user = 'username')
Query OK, 0 rows affected (0.00 sec)
Code language: JavaScript (javascript)

If the user does not exist, you can create a new user:

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
Code language: JavaScript (javascript)

Client host have insufficient permission

By default, the MySQL server listens for connections only from localhost, which means it can be accessed only by applications running on the same host.

Remote root access is disabled by default. If you want to enable that, run this SQL command locally:

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
 FLUSH PRIVILEGES;
Code language: JavaScript (javascript)

And then find the following line and comment it out in your my.cnf file, which usually lives on /etc/mysql/my.cnf on Unix/OSX systems. In some cases the location for the file is /etc/mysql/mysql.conf.d/mysqld.cnf).

Alternatively, you can check to see which host user/host MySQL allows connections with the following query:

mysql> SELECT Host, User FROM mysql.user WHERE User='user01';
+-------------+-------------+
| Host        | User        |
+-------------+-------------+
| 192.168.0.1 | user01 |
+-------------+-------------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)

Wrong password provided

This is by far the most common reason for MySQL ERROR 1045 (28000): Access denied for user ‘user’@’localhost’ (using password: YES).

MySQL lists user accounts in the user table of the mysql database. Each MySQL account can be assigned a password, although the user table does not store the cleartext version of the password, but a hash value computed from it.

You cannot read user passwords in plain text from MySQL as the password hash is used for authentication, but if you can get into MySQL command line interface (with root privileges), you can compare what you remember with the hashed value with “PASSWORD” function with the command below.

mysql> SELECT Host, User, authentication_string, PASSWORD('forgotten') FROM mysql.user WHERE User='nonexistant';  
+-------------+-------------+-------------------------------------------+-------------------------------------------+
| Host        | User        | authentication_string                     | PASSWORD('forgotten')                     |
+-------------+-------------+-------------------------------------------+-------------------------------------------+
| 192.168.0.1 | nonexistant | *AF9E01EA8519CE58E3739F4034EFD3D6B4CA6324 | *70F9DD10B4688C7F12E8ED6C26C6ABBD9D9C7A41 |
| %           | nonexistant | *AF9E01EA8519CE58E3739F4034EFD3D6B4CA6324 | *70F9DD10B4688C7F12E8ED6C26C6ABBD9D9C7A41 |
+-------------+-------------+-------------------------------------------+-------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
Code language: JavaScript (javascript)

From the output, we can clearly see that the hashed value for ‘forgotten’ does not match the authentication_string column, which means ‘forgotten’ is the wrong password.

Executing the following query will overwrite the current password with a new one :

mysql> set password for 'nonexistant'@'%' = 'helloworld';
Empty set (0.00 sec)
Code language: PHP (php)

Special characters in the password

If you’re using Bash as your main shell, you should know that not all characters are treated equal in Bash. Some characters are evaluated by Bash to have a non-literal meaning. Instead, these characters carry out a special instruction, or have an alternate meaning; they are called «special characters», or «meta-characters».

In this case, your password contains some of the those special characters which might be mistakenly converted to another form by Bash. To prevent this from happening, wrap your password in single quotes in the command:

[[email protected]]# mysql -u user01 -p'hello$!*&^%$#@!'
mysql: [Warning] Using a password on the command line interface can be insecure
...
mysql>
Code language: PHP (php)

Regain access to the database

If you’re locked out and need to bypass the authentication mechanisms to regain access to the database, here are simple steps to do so. Please note that we’re using MariaDB to demonstrate the steps.

1. Stop the instance by running sudo systemctl stop mariadb
2. Use mysqld_safe to start mysqld server by running the command: mysqld_ safe –user=mysql –skip-grant-tables –skip-networking
3. Now you can open up a new terminal and access the MySQL server instance with root privileges by running mysql -u root -h localhost .
4. Please do note that since we’re running grant-skip-tables, any GRANT/CREATE/SET PASSWORD statements won’t work straight away. In order to fix this, run FLUSH PRIVILEGES;.
5. Run SET PASSWORD FOR 'root'@'localhost' = '[email protected]!' to change the password for root user.
6. Alternatively, you can modify mysql.users table with a query which modifies the password for User and Host like UPDATE mysql.user SET authentication_string=PASSWORD(‘newpwd’) WHERE User=’root’ and Host=’localhost’;
7. Stop the mysqld_safe instance and restart MySQL again from systemctl.
8. You should be able to login with root from the localhost with the new password and do any other necessary corrective operations with root user.