Access denied for user ‘root’@‘localhost’ while create new site

ERPNext
,
1

Hi team,
I am facing issue “Access denied for user ‘root’@‘localhost’” when i am creating new site. any one can guide how can i fix this issue.

bench new-site erpnext2
MySQL root password:
Traceback (most recent call last):
File “/usr/lib/python3.8/runpy.py”, line 194, in _run_module_as_main
return _run_code(code, main_globals, None,
File “/usr/lib/python3.8/runpy.py”, line 87, in _run_code
exec(code, run_globals)
File “/home/samy/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 110, in
main()
File “/home/samy/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 20, in main
click.Group(commands=commands)(prog_name=“bench”)
File “/home/samy/frappe-bench/env/lib/python3.8/site-packages/click/core.py”, line 829, in call
return self.main(*args, **kwargs)
File “/home/samy/frappe-bench/env/lib/python3.8/site-packages/click/core.py”, line 782, in main
rv = self.invoke(ctx)
File “/home/samy/frappe-bench/env/lib/python3.8/site-packages/click/core.py”, line 1259, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/samy/frappe-bench/env/lib/python3.8/site-packages/click/core.py”, line 1259, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/samy/frappe-bench/env/lib/python3.8/site-packages/click/core.py”, line 1066, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/home/samy/frappe-bench/env/lib/python3.8/site-packages/click/core.py”, line 610, in invoke
return callback(*args, **kwargs)
File “/home/samy/frappe-bench/apps/frappe/frappe/commands/site.py”, line 74, in new_site
_new_site(
File “/home/samy/frappe-bench/apps/frappe/frappe/installer.py”, line 62, in _new_site
install_db(
File “/home/samy/frappe-bench/apps/frappe/frappe/installer.py”, line 132, in install_db
setup_database(force, source_sql, verbose, no_mariadb_socket)
File “/home/samy/frappe-bench/apps/frappe/frappe/database/init.py”, line 20, in setup_database
return frappe.database.mariadb.setup_db.setup_database(
File “/home/samy/frappe-bench/apps/frappe/frappe/database/mariadb/setup_db.py”, line 34, in setup_database
if force or (db_name not in dbman.get_database_list()):
File “/home/samy/frappe-bench/apps/frappe/frappe/database/db_manager.py”, line 64, in get_database_list
return [d[0] for d in self.db.sql(“SHOW DATABASES”)]
File “/home/samy/frappe-bench/apps/frappe/frappe/database/database.py”, line 161, in sql
self.connect()
File “/home/samy/frappe-bench/apps/frappe/frappe/database/database.py”, line 85, in connect
self._conn = self.get_connection()
File “/home/samy/frappe-bench/apps/frappe/frappe/database/mariadb/database.py”, line 76, in get_connection
conn = pymysql.connect(
File “/home/samy/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py”, line 353, in init
self.connect()
File “/home/samy/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py”, line 633, in connect
self._request_authentication()
File “/home/samy/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py”, line 921, in _request_authentication
auth_packet = self._process_auth(plugin_name, auth_packet)
File “/home/samy/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py”, line 1018, in _process_auth
pkt = self._read_packet()
File “/home/samy/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py”, line 725, in _read_packet
packet.raise_for_error()
File “/home/samy/frappe-bench/env/lib/python3.8/site-packages/pymysql/protocol.py”, line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File “/home/samy/frappe-bench/env/lib/python3.8/site-packages/pymysql/err.py”, line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, “Access denied for user ‘root’@‘localhost’ (using password: YES)”)

1 Like
2

Hi,

It might help to say the version of Frappe and ERPNext that is in use and whether other sites have been created successfully. Using the forum search tool to look for post with the same subject may also be helpful.

1 Like
3

Setting a new root password for MySql is dead easy. Google it.

Then set the password you want.

Then specify that password when you rerun your ERPNext installation scripts.

1 Like
4

Hi @smino,
I am using erpnext 14 version, but still getting same error.

thanks.

5 
> sudo mysql –u root
> grant all privileges on *.* to 'root'@'localhost' identified by 'password' with grant option;
> FLUSH PRIVILEGES;

Replace password with your password

2 Likes
6

I am Facing the same issue , i am able to access MySQL using the root password, but when the site is being created the library pymysql is getting the access denied.

things that i tried to resolve this.

  1. i Changed the root password and checked if we can access the DB using the password and we were able to
    2.i gave all privileges to the Root user by running the query.
  2. Reinstalled the software multiple times and checked the config file if the root password is set correctly.

@smino the bench version i am using is 5.16.2

@MartinHBramwell yes i have tried that i am able to access the mysql using the service itself , but erp connectors are the one that is giving access denied is there anywhere in ERPNext configuration its configured where i can double check.

milsign thanks , i have tried that as well we have given full privileges and we are able to access DB using MariaDB service, but still bench commands are denied with the access

Screenshot (298)
Screenshot (298)1366×698 30.6 KB

i have spent some time debugging this but not able to, can anyone please guide me what else could have gone wrong.

thank you

7

Have you checked (from CLI) that python is able to access mariadb?

pip3 install --upgrade mysqlclient;   #install a python/pip mysql client
cat <<EOF > ./connTest.py;
import MySQLdb
def main():
  db = MySQLdb.connect(host = "localhost", user = "$MySQLusr", passwd = "$MySQLpwd", db = "mysql")
  if (db):
    print ("Connection successful")
  else:
    print ("Connection unsuccessful")

main()
EOF
#cat ./connTest.py  #4DBG#
python3 ./connTest.py
#rm -f ./connTest.py  #4DBG#Maybe only remove this if the connection succeeds

pip3 list | grep -i mysql;  #Check which pip/mysql modules are available...
   #mysqlclient                   (v2.0.1 definitely DOES work)
1 Like
8

Don’t know if it’ll help. I’m in a rush and have no time to explain it.

Here’s a snippet of my installation scripts:

export MYPWD="MEGAs3CR3t";

export pDFLT="\033[0m";
export pPALE_MAGENTA="\033[1;49;35m";
export pGREEN="\033[1;40;32m";

echo -e "${pGREEN}\n\nReady for MySql setup${pPALE_MAGENTA}";

sudo -A service mysqld restart;
if mysql -u root -e "select 1+1;" mysql &>/dev/null; then
  echo -e "\n\n${pYELLOW}MariaDB already secured${pDFLT}";
else
  echo -e "\n\n${pYELLOW}Securing MariaDB....${pPALE_MAGENTA}";

  sudo -A mysql_secure_installation 2>/dev/null <<MSI

n
y
${MYPWD}
${MYPWD}
y
y
y
y

MSI

  echo -e "${pGREEN}\n\nUpdate MySql${pPALE_MAGENTA}";
  sudo -A mysql -u root -e "FLUSH PRIVILEGES;;" mysql;
fi;

 echo -e "${pDFLT}";

The point is to pay close attention to what you do in the mysql_secure_installation step.

3 Likes
9

AWESOME script - thank you for sharing!

1 Like
10

thanks @MartinHBramwell , @trentmu it got resolved.

11

Please help future readers by describing your solution.

12

Larry Wall’s 3 deadly sins of programmers:

  1. Laziness
  2. Impatience
  3. Hubris

That snippet comes from point #1. After redoing those commands manually a dozen times in a day or two I … uhmm … threw a fit!

1 Like