Getting intermittent pipe while passing mysql connection to python stream

Question

I am trying to pass mysql connection to a stream in python. If I perform mysql initialization inside the working class, no error occurs.

However, this can be expensive to connect, so I tried just passing the mysql connection from the caller function (see code below). But this continues to throw this error:

(2006, the 'MySQL server is gone' (BrokenPipeError (32, 'Broken pipe'))

Any idea why? I think this is because we are passing 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 used this pool in threads. So we just get the connection from the pool at the thread level.

Answer 1

Database connections are not thread safe, so you should not transfer them from one thread to another. The connection pool is held on open connections between requests, so it’s faster to get the connection from the pool, use it for the request and then release it.

This related answer provides useful links to the security of database connection flows.