Hi everyone, I have an Issue with bench migrate, every time that I did migrate my screen stays about 15 minutes this way, in every bench migrate I need wait this time.
Obs: When I install erpnext or did bench update happens the same thing.
Same issue with me it takes 6-7 hours to complete bench migrate command
on test server I have less data so it completes under 5-10 mins but on production I have lots of data so it takes 6-7 hours
It fires same query again and again on bench migrate
SELECT * FROM `tabUser Email` WHERE `parent`='test@gmail.com' AND `parenttype`='User' AND `parentfield`='user_emails' ORDER BY `idx` ASC
Execution time: 0.00 sec
SELECT * FROM `tabBlock Module` WHERE `parent`='test@gmail.com' AND `parenttype`='User' AND `parentfield`='block_modules' ORDER BY `idx` ASC
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
Execution time: 0.00 sec
SELECT `module`,`custom` FROM `tabDocType` WHERE `name`='Block Module' ORDER BY `modified` DESC LIMIT 1
in our case the reason was: there was a Doctype that contains arround 250,000 records and it’s fixture contains the same count.
Solution:
1- open the site console
bench --site your-site console
2- truncate the huzzle doctype from it’s data, and don’t forget to commit the action else it won’t reflect in the database why? because you are in the console screen not the database screen.
In [1]: frappe.db.truncate("Has Role")
In [2]: frappe.db.commit()
In [3]: exit
3- now migration will be faster.
bench --site your-site migrate
if the migration take so long time, 90% the reason will be the fixture
WHY???
The large number of duplicate records in the “Has Role” doctype likely happened because fixtures or scripts repeatedly assigned the same roles to users during each migration, without checking if the role was already assigned. Since Frappe doesn’t enforce a unique constraint on user-role combinations by default, duplicates pile up over time. This slows down migrations significantly.
In short:
Every migration re-added roles from fixtures without checks, creating duplicates. Without database rules to block this, the records kept growing. The solution is to clean duplicates, add uniqueness checks, and fix the scripts or fixtures.