ERPnext pymysql.err.OperationalError

ERPNext
, ,
1

After updating to version 11. When restarting the server and getting this permission issue.

OperationalError: (1045, u"Access denied for user ‘_dd167c2941c85684’@‘localhost’ (using password: YES)")

After this, I tried changing my DB password in MySQL as suggested in ERP Discuss. am getting this msg. Please help.

MariaDB [mysql]> SET PASSWORD FOR ‘_dd167c2941c85684’@‘localhost’ = PASSWORD(‘abc123456789’);
ERROR 1133 (28000): Can’t find any matching row in the user table

2

seems like almost all restore/migrate related mysql issues stem from the system trying to restore/rebuild the database on top of an existing one.

The only restore command I never get any lip from is:

bench --force --site <site> restore /path/to/dbbackup.sql

Every time I’ve ever tried without ‘–force’ or with something like ‘–with-files/–private-files’ I’ve encountered some form of issue either immediately or shortly down the line.

Which reminds me- before you do anything with your old site, make sure that you have the encryption key from the site_config.json copied over to the new site’s site_config.json This is a fun one, since, as far as I know, it isn’t backed up in the sql.gz, the files.tar, or private-files.tar AND the new site will ‘work’ fine without it… at first glance. without copying that encryption key over, you will start noticing and begin having issues with basically anything that held a key or token of any kind- email settings, integrations (including dropbox and S3), oauth, etc.

I know I didn’t really answer your question, but honestly, I’m pretty garbage at sql cli and the one time I tried to fix this exact issue, I ended up messing with it for 2 hours before just nuking the server and starting over, restoring with the above command successfully in about 30min start to finish. So yeah. I recommend that.

2 Likes
3

This is what I have found to work if I get an error during installation while doing the

bench new-site site1.local

It’s not the exact error you have, but similar enough that it should work OK.

#IF this happens....
#ERROR 1045 (28000): Access denied for user '_dd167c2941c85684'@'localhost' (using password: YES)
DBname=$(cat ~/frappe-bench/sites/site1.local/site_config.json | awk '/db_name/ {print $2}' | sed "s/\"//g" | sed "s/,//g")
DBpass=$(cat ~/frappe-bench/sites/site1.local/site_config.json | awk '/db_password/ {print $2}' | sed "s/\"//g")
#
mysql -uroot -pYourMYSQLpassword -Bse "GRANT ALL PRIVILEGES ON *.* TO '$DBname'@'localhost' IDENTIFIED BY '$DBpass' WITH GRANT OPTION;"
mysql -uroot -pYourMYSQLpassword -Bse "GRANT ALL PRIVILEGES ON *.* TO '$DBname'@'127.0.0.1' IDENTIFIED BY '$DBpass' WITH GRANT OPTION;"
#
mysql -uroot -pYourMYSQLpassword -Bse "set password for '$DBname'@'localhost' = password('$DBpass');"
mysql -uroot -pYourMYSQLpassword -Bse "set password for '$DBname'@'127.0.0.1' = password('$DBpass');"
#delete the DB and re-run
bench new-site site1.local

Just ensure that the “site1.local” and the “YourMYSQLpassword” match your settings/setup. The commands should be run as your normal frappe user (NOT root)

4

After a long struggle. This issue is resoled. just follow the step as given below.

  • go to frappe-bench/
  • mysql -uroot -pyourpassword
  • use mysql;
  • grant all privileges on . to databasename@localhost identified by ‘newpassword’ with grant option;
  • go to frappe-bench/site/site.local/site_config.json (change db_password to newpassword)
  • restart mysql.
  • restart bench.
    Done
3 Likes
5

@keerthideepak when I try to connect RDS from EC2 I got this error
pymysql.err.OperationalError: (1045, “Access denied for user ‘_2e68096a1650b265’@‘RDS’ (using password: YES)”) help me how to overcome the issue

Also can you suggest any doc to install ERPNext12
properly

6

Thanks alot this saved alot of my time