Could not execute multiprocessing in python SQLAlchemy

Question

With below simple code snippet, I tried to get thread multiprocessing up and running.

import multiprocessing
import os

from app.PD.models import PAST_DUE
from app.LoanContract.models import LOAN_CONTRACT

def doPDDate(n):
    print("Worker process id for {0}: {1}".format(n.ID, os.getpid()))
    LoanID = n.LoanID
    LC = LOAN_CONTRACT.query.filter_by(ID=LoanID)
    for row in LC:
        print row.ID
    return n.ID

if __name__ == "__main__":
    # input list
    # mylist = [1,2,3,4,5]
    PD = PAST_DUE.query.all()

    # creating a pool object
    p = multiprocessing.Pool()

    # map list to target function
    result = p.map(doPDDate, PD)

Yet, on a half way while doPDDate() was running, I got wired error message:

Traceback (most recent call last): File "test.py", line 26, in result = p.map(doPDDate, PD) File "/usr/lib/python2.7/multiprocessing/pool.py", line 253, in map return self.map_async(func, iterable, chunksize).get() File "/usr/lib/python2.7/multiprocessing/pool.py", line 572, in get raise self._value sqlalchemy.exc.OperationalError: (OperationalError) SSL error: decryption failed or bad record mac

Before that function continued executing.

The whole Trackback is as below:

Worker process id for PDM0000003326: 10658
Worker process id for PDM0000002726: 10659
Worker process id for PD18032300232: 10660
Worker process id for PD18012400045: 10661
Worker process id for PDM0000002445: 10665
Worker process id for PDM0000002715: 10666
Worker process id for PD17122600008: 10662
Worker process id for PDM0000002927: 10668
Worker process id for PDM0000001153: 10663
Worker process id for PDM0000003183: 10670
Worker process id for PDM0000003057: 10665
Worker process id for PDM0000001209: 10664
Worker process id for PDM0000003277: 10658
Worker process id for PDM0000003263: 10666
Worker process id for PDM0000002796: 10667
Traceback (most recent call last):
  File "test.py", line 26, in <module>
    result = p.map(doPDDate, PD)
  File "/usr/lib/python2.7/multiprocessing/pool.py", line 253, in map
    return self.map_async(func, iterable, chunksize).get()
  File "/usr/lib/python2.7/multiprocessing/pool.py", line 572, in get
    raise self._value
sqlahemy.exc.OperationalError: (OperationalError) SSL error: decryption failed or bad record mac
 'SELECT "LOAN_CONTRACT"."Branch" AS "LOAN_CONTRACT_Branch", "LOAN_CONTRACT"."Status" AS "LOAN_CONTRACT_Status", "LOAN_CONTRACT"."Curr" AS "LOAN_CONTRACT_Curr", "LOAN_CONTRACT"."Inputter" AS "LOAN_CONTRACT_Inputter", "LOAN_CONTRACT"."Createdon" AS "LOAN_CONTRACT_Createdon", "LOAN_CONTRACT"."Authorizer" AS "LOAN_CONTRACT_Authorizer", "LOAN_CONTRACT"."Authorizeon" AS "LOAN_CONTRACT_Authorizeon", "LOAN_CONTRACT"."ID" AS "LOAN_CONTRACT_ID", "LOAN_CONTRACT"."LoanApplicationID" AS "LOAN_CONTRACT_LoanApplicationID", "LOAN_CONTRACT"."ContractCustomerID" AS "LOAN_CONTRACT_ContractCustomerID", "LOAN_CONTRACT"."Account" AS "LOAN_CONTRACT_Account", "LOAN_CONTRACT"."Currency" AS "LOAN_CONTRACT_Currency", "LOAN_CONTRACT"."Amount" AS "LOAN_CONTRACT_Amount", "LOAN_CONTRACT"."Disbursed" AS "LOAN_CONTRACT_Disbursed", "LOAN_CONTRACT"."ApprovedAmount" AS "LOAN_CONTRACT_ApprovedAmount", "LOAN_CONTRACT"."OutstandingAmount" AS "LOAN_CONTRACT_OutstandingAmount", "LOAN_CONTRACT"."DisbursedStat" AS "LOAN_CONTRACT_DisbursedStat", "LOAN_CONTRACT"."ValueDate" AS "LOAN_CONTRACT_ValueDate", "LOAN_CONTRACT"."Installment" AS "LOAN_CONTRACT_Installment", "LOAN_CONTRACT"."Term" AS "LOAN_CONTRACT_Term", "LOAN_CONTRACT"."Cycle" AS "LOAN_CONTRACT_Cycle", "LOAN_CONTRACT"."MaturityDate" AS "LOAN_CONTRACT_MaturityDate", "LOAN_CONTRACT"."LoanProduct" AS "LOAN_CONTRACT_LoanProduct", "LOAN_CONTRACT"."Category" AS "LOAN_CONTRACT_Category", "LOAN_CONTRACT"."InterestRate" AS "LOAN_CONTRACT_InterestRate", "LOAN_CONTRACT"."IRR" AS "LOAN_CONTRACT_IRR", "LOAN_CONTRACT"."FreqType" AS "LOAN_CONTRACT_FreqType", "LOAN_CONTRACT"."Frequency" AS "LOAN_CONTRACT_Frequency", "LOAN_CONTRACT"."DeliqMode" AS "LOAN_CONTRACT_DeliqMode", "LOAN_CONTRACT"."LoanPurpose" AS "LOAN_CONTRACT_LoanPurpose", "LOAN_CONTRACT"."ContractVB" AS "LOAN_CONTRACT_ContractVB", "LOAN_CONTRACT"."Group" AS "LOAN_CONTRACT_Group", "LOAN_CONTRACT"."SourceOfFund" AS "LOAN_CONTRACT_SourceOfFund", "LOAN_CONTRACT"."TotalInterest" AS "LOAN_CONTRACT_TotalInterest", "LOAN_CONTRACT"."AccrInterest" AS "LOAN_CONTRACT_AccrInterest", "LOAN_CONTRACT"."AccrCurrentInt" AS "LOAN_CONTRACT_AccrCurrentInt", "LOAN_CONTRACT"."AccrIntCurrMonth" AS "LOAN_CONTRACT_AccrIntCurrMonth", "LOAN_CONTRACT"."AccrIntPreMonth" AS "LOAN_CONTRACT_AccrIntPreMonth", "LOAN_CONTRACT"."IntIncEarned" AS "LOAN_CONTRACT_IntIncEarned", "LOAN_CONTRACT"."AccrIntPerDay" AS "LOAN_CONTRACT_AccrIntPerDay", "LOAN_CONTRACT"."NextAccrDate" AS "LOAN_CONTRACT_NextAccrDate", "LOAN_CONTRACT"."Suspend" AS "LOAN_CONTRACT_Suspend", "LOAN_CONTRACT"."ContractOfficerID" AS "LOAN_CONTRACT_ContractOfficerID", "LOAN_CONTRACT"."LoanType" AS "LOAN_CONTRACT_LoanType", "LOAN_CONTRACT"."AssetClass" AS "LOAN_CONTRACT_AssetClass", "LOAN_CONTRACT"."MoreThanOneYear" AS "LOAN_CONTRACT_MoreThanOneYear", "LOAN_CONTRACT"."NextRunDate" AS "LOAN_CONTRACT_NextRunDate", "LOAN_CONTRACT"."Penalty" AS "LOAN_CONTRACT_Penalty", "LOAN_CONTRACT"."FirstCollectionDate" AS "LOAN_CONTRACT_FirstCollectionDate", "LOAN_CONTRACT"."Classification" AS "LOAN_CONTRACT_Classification", "LOAN_CONTRACT"."Reviewcurr" AS "LOAN_CONTRACT_Reviewcurr", "LOAN_CONTRACT"."Reviewer_1" AS "LOAN_CONTRACT_Reviewer_1", "LOAN_CONTRACT"."Reviewedon_1" AS "LOAN_CONTRACT_Reviewedon_1", "LOAN_CONTRACT"."Reviewer_2" AS "LOAN_CONTRACT_Reviewer_2", "LOAN_CONTRACT"."Reviewedon_2" AS "LOAN_CONTRACT_Reviewedon_2", "LOAN_CONTRACT"."DisbursedBalance" AS "LOAN_CONTRACT_DisbursedBalance", "LOAN_CONTRACT"."PayableTo1" AS "LOAN_CONTRACT_PayableTo1", "LOAN_CONTRACT"."PayableTo2" AS "LOAN_CONTRACT_PayableTo2", "LOAN_CONTRACT"."LegacyID" AS "LOAN_CONTRACT_LegacyID" \nFROM "LOAN_CONTRACT" \nWHERE "LOAN_CONTRACT"."ID" = %(ID_1)s' {'ID_1': u'18032300232'}
Worker process id for PDM0150612427: 10669
M0000003326
Worker process id for PD18021300102: 10659
Worker process id for PDM0151009885: 10663
Worker process id for PD17122500013: 10668
Worker process id for PDM1602261298: 10663
Worker process id for PDM1603011307: 10660
Worker process id for PDM1606211506: 10665

I have no idea if I remove block for-loop from function doPDDate() everything just working as fine; the print statement just keep printing without error message as above.

Could I know what's this problem ? How can I solve that ? Thanks.

have you seen this current solution in [SO](https://stackoverflow.com/questions/41279157/connection-problems-with-sqlalchemy-and-multiple-processes) — gyx-hh, May 30 '18 at 15:30
@gyx-hh, thanks for pointing that, after reading through comments and answer, I grasped some points from that says `The SQLAlchemy Engine object refers to a connection pool of existing database connections. So when this object is replicated to a child process, the goal is to ensure that no database connections are carried over.`. Yet, my case, every single process keep pinging to db connection, but why it is problem if I execute for-loop block ? Without it, everything is just working fine. — Houy Narun, May 30 '18 at 16:14
@gyx-hh, anyway, the point is to close connection just before each of every another process accessing to db connection, so by putting ` session.close()`, `engine.dispose()` before in function `doPDDate()` help resolve problem. Thanks. — Houy Narun, May 30 '18 at 16:19
What about using NullPool as the document says the most simplistic solution? https://docs.sqlalchemy.org/en/13/faq/connections.html#how-do-i-use-engines-connections-sessions-with-python-multiprocessing-or-os-fork — JunKim, Mar 25 '20 at 04:39

Could not execute multiprocessing in python SQLAlchemy

0 Answers0