i have created a scheduled a job to sync items from my erp to my frappe system and setup a daily long job for it
But whenever i start the job it shows the error
Job stopped by user, work-horse terminated.
despite i have not force stopped the job the status showing is still started but the function is not working and syncing any items
and after reaching timeout it shows failed and give timeout error.
I have increased the timeout to 4000 using common_site_config.json file
the same job gets completed in my test server without any error
i have same timeout settings in test server as well
despite i have way less resources in test server than in production
Error after the timeout
Traceback (most recent call last):
File “/home/helpdesk/helpdesk/env/lib/python3.12/site-packages/rq/worker.py”, line 1428, in perform_job
rv = job.perform()
^^^^^^^^^^^^^
File “/home/helpdesk/helpdesk/env/lib/python3.12/site-packages/rq/job.py”, line 1278, in perform
self._result = self._execute()
^^^^^^^^^^^^^^^
File “/home/helpdesk/helpdesk/env/lib/python3.12/site-packages/rq/job.py”, line 1315, in _execute
result = self.func(*self.args, **self.kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/home/helpdesk/helpdesk/apps/frappe/frappe/utils/background_jobs.py”, line 243, in execute_job
frappe.db.commit()
File “/home/helpdesk/helpdesk/apps/frappe/frappe/database/database.py”, line 1036, in commit
self.sql(“commit”)
File “/home/helpdesk/helpdesk/apps/frappe/frappe/database/database.py”, line 234, in sql
self._cursor.execute(query, values)
File “/home/helpdesk/helpdesk/env/lib/python3.12/site-packages/pymysql/cursors.py”, line 153, in execute
result = self._query(query)
^^^^^^^^^^^^^^^^^^
File “/home/helpdesk/helpdesk/env/lib/python3.12/site-packages/pymysql/cursors.py”, line 322, in _query
conn.query(q)
File “/home/helpdesk/helpdesk/env/lib/python3.12/site-packages/pymysql/connections.py”, line 562, in query
self._execute_command(COMMAND.COM_QUERY, sql)
File “/home/helpdesk/helpdesk/env/lib/python3.12/site-packages/pymysql/connections.py”, line 843, in _execute_command
raise err.InterfaceError(0, “”)
pymysql.err.InterfaceError: (0, ‘’)
this is the code i am using
def sync_item_master():
try:
con = get_oracle_connection()
if not con:
return
cur = con.cursor()
# Fetch records from Oracle where ITEM_NATURE = 'SI'
cur.execute(
"""
SELECT ITEM_CODE, ITEM_NAME, UM, ITEM_STATUS
FROM RIPLRR.VIEW_ITEM_MAST
WHERE ITEM_NATURE = 'SI'
"""
)
oracle_records = cur.fetchall()
# Fetch records from Frappe (MariaDB)
frappe_records = frappe.db.sql(
"""
SELECT item_erp_code, item_name, unit, enabled
FROM `tabItem_Master`
""",
as_dict=True,
)
# Convert to dictionaries for easier comparison
oracle_dict = {rec[0]: rec for rec in oracle_records}
frappe_dict = {rec["item_erp_code"]: rec for rec in frappe_records}
# Sync logic
for oracle_code, oracle_item in oracle_dict.items():
unit = oracle_item[2] # The unit from Oracle
item_status = (
1 if oracle_item[3] == "R" else 0
) # Status conversion (R = Running, C = Closed)
# Check if UOM exists in Frappe
if not check_uom_exists(unit):
frappe.log_error(
title="Missing UOM",
message=f"Could not find UOM: {unit}. Skipping item {oracle_item[0]}.",
)
continue # Skip this item if UOM is missing
frappe_item = frappe_dict.get(oracle_code)
# Check if an item with the same name already exists to avoid duplicates
existing_item_by_name = frappe.db.exists(
"Item_Master", {"item_name": oracle_item[1]}
)
if existing_item_by_name:
# Item with the same name exists, update instead of inserting
frappe.db.sql(
"""
UPDATE `tabItem_Master`
SET item_erp_code=%s, unit=%s, enabled=%s, modified=%s
WHERE item_name=%s
""",
(
oracle_code, # Item code
unit, # Unit
item_status, # Status (enabled/disabled)
now(), # Modification timestamp
oracle_item[1], # Item name
),
)
frappe.msgprint(f"Updated existing item: {oracle_item[1]}")
elif frappe_item:
# Item exists in MariaDB by item code, compare and update if needed
if (
frappe_item["item_name"] != oracle_item[1]
or frappe_item["unit"] != unit
or frappe_item["enabled"] != item_status
):
frappe.db.sql(
"""
UPDATE `tabItem_Master`
SET item_name=%s, unit=%s, enabled=%s, modified=%s
WHERE item_erp_code=%s
""",
(
oracle_item[1], # Item name
unit, # Unit
item_status, # Status (enabled/disabled)
now(), # Modification timestamp
oracle_code, # Oracle item code
),
)
frappe.msgprint(f"Updated item: {oracle_item[1]}")
else:
# Item does not exist, insert it
frappe.get_doc(
{
"doctype": "Item_Master",
"item_erp_code": oracle_item[0], # Item code
"item_name": oracle_item[1], # Item name
"unit": unit, # Unit of measure
"enabled": item_status, # Status (enabled/disabled)
}
).insert()
frappe.msgprint(f"Inserted new item: {oracle_item[1]}")
# Commit changes in Frappe
frappe.db.commit()
except cx_Oracle.DatabaseError as e:
frappe.log_error(title="Oracle Sync Error", message=str(e))
frappe.throw(f"Oracle Sync Error: {str(e)}")
finally:
if con:
con.close()
