ERPNext Conference 2019* ERPNext.com Blog

Sudden lost of data (Employee doctype) bench migrate error


#1

Good day devs. I’m having a problem with migrating database. My employee doctype suddenly lost its data. Didn’t know what happened. We have backups stored locally with the help of cron jobs. Tried migrating my latest backup on the current server and i always get this error:

  • (1396, “Operation CREATE USER failed for ‘_1bd3e0294da1919’@‘localhost’”)

then i run bench migrate and got this error:

  • (1045, “Access denied for user ‘_1bd3e0294da19198’@‘localhost’ (using password: YES)”)

Thank you so much in advance


#2

#IF this happens…
#ERROR 1045 (28000): Access denied for user ‘_1bd3e0294da19198’@‘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');"

#3

how do you do this:

“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")"

i didn’t clearly undestand. thank you @trentmu


#4

and where do i get the $DBpass. I’m so new to this. Thank you so much


#5

I will explain each line… The 1st 2 commands

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")

will do a lookup of what your settings already are, and store those values in the variables which I have called DBname/DBpass. If you want to just check them, you can run the piece which is within the $(), for example…

cat ~/frappe-bench/sites/site1.local/site_config.json | awk '/db_name/ {print $2}' | sed "s/\"//g" | sed "s/,//g"

will show you the name of the DB as configured

The remainder just sets the permissions within the DB using the extracted parameters and variables


#6

Thank you @trentmu. But it didn’t work


#7

I just noticed that I included the name of MY site instead of the default - you should tune the above commands to match the sitename that you chose


#8

Sorry for the late reply but i was able to solve the problem. The problem is with mysql and not with ERPnext, “Authentication Error”. Open site_config.json. Find"db_name" property and check if “db_name” property exist in mysql database. table “user”. In my case, “db_name” property in my site_config.json didn’t match with any values from the “user” table in mysql