将mysql连接传递给python线程时管道损坏 [英] Getting broken pipe when passing mysql connection to a python thread

问题描述

我正在尝试将 mysql 连接传递给 python 中的线程.如果我在 worker 类中初始化 mysql，则没有错误.

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.

但是，连接可能会很昂贵，所以我尝试只从调用者函数传递 mysql 连接(见下面的代码).但这不断抛出这个错误:

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 服务器已经消失(BrokenPipeError(32, 'Broken管道'))

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

知道为什么吗?我想是因为我们通过mysql连接的方式

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]

更新

我们创建了一个连接池并在线程中使用该池，而不是传递 mysql 连接.这样，我们只是在线程级别从池中获取连接.

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.

推荐答案

数据库连接不是线程安全的，因此您不应将它们从一个线程传递到另一个线程.连接池保留请求之间的开放连接，因此从池中获取连接、将其用于查询、然后释放它会更快.

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.

这篇关于将mysql连接传递给python线程时管道损坏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！