Getting broken pipe when passing mysql connection to a python thread

Question

I'm trying to pass a mysql connection to a thread in python. If i do the initialization of the mysql inside the worker class, there is no error.

However, it might be costly for the connection so I tried just passing the mysql connection from the caller function (see code below). But this keeps throwing this error:

(2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))

Any idea why? I think its because the way we pass the mysql connection

def worker(db):
""" Distributes the workload for a thread
"""
while True:
    item = queue_handler.get()
    perform_insert(item, db)
    queue_handler.task_done()

def insert_bulk(params):
""" Handles the insert
"""
    cntr = 0
    res = []
    cannot_read = []
    (data, cms, report_id) = params

    db = nmi_mysql.DB(CONFIG['earnings_db'], True)

    for i in range(10):
        thrd = threading.Thread(target=worker, args=(db,))
        thrd.deamon = True
        thrd.start()

    for row in data:
        split_files = row.split(',')

        if len(split_files) != 34:
            cannot_read.append(split_files)
            continue

        now = datetime.datetime.now()

        res.append(<some data to insert>)

        if len(res) == 750 or cntr == len(data):
            queue_handler.put([res, cms, report_id])
            res = []

        cntr += 1

    queue_handler.join()

    db.close()

    return [len(res), cms]

UPDATE

Instead of passing the mysql connection, we created a connection pool and use that pool in the threads. This way, we just get connection from the pool on the thread level.

Sentry

Typically, database connections are not to be used between different client threads: They are not thread-safe. If nobody offers you a better answer, I would recommend you to process each connection into the same thread: open, use and close. – Bent

@LittleSanti we used a connection pool instead. Works really well. – Sentry

It's OK using a pool while you handle every connection fully within the same thread: getting the connection, using it, releasing it. The reason is still the same: Connections usually are not thread-safe. – Bent

Answer 1

Database connections are not thread safe, so you shouldn't pass them from one thread to another. A connection pool holds onto open connections between requests, so it's faster to get a connection from the pool, use it for a query, and then release it.

This related answer has some useful links on the thread safety of database connections.

Millicent

My answer is mostly a summary of the comments. I just posted it so this question will be removed from the list of unanswered questions. – Millicent