When using multiprocessing to access MySQL, it always throws the following error, how to fix?

Question

When I used Python multiprocessing to access MySQL database, always got the errors:

OperationalError: (2006, 'MySQL server has gone away')

Lost connection to MySQL server during query

I would greatly appreciate if somebody could explain this to me.

Here is my code:

class MetricSource:
  def __init__(self, task):
    self.task = task

  def get_next_task_time(self):
    try:
      task_id = self.task.id
      next_task = Task.objects.get(id=task_id)
      next_time = next_task.last_success_time
    except Task.DoesNotExist as e:
      print 'Find Exception: %d' % self.task.id

def access_queue(input_queue, output_queue):
  while True:
    try:
      metric_source = input_queue.get(timeout=0.5)
      metric_source.get_next_task_time()
      output_queue.put(metric_source)
    except Queue.Empty:
      print "Queue Empty Error"
      continue

class Command(BaseCommand):

  def handle(self, *args, **options):
    self.manager = multiprocessing.Manager()
    self.input_queue = self.manager.Queue()
    self.output_queue = self.manager.Queue()

    self.init_data()

    for i in range(PROCESS_NUM):
      Process(target=access_queue, args=(self.input_queue, self.output_queue)).start()

  def init_data(self):
    for i in range(200):
      try:
        task = Task.objects.get(id=i+1)
        self.input_queue.put(MetricSource(task))
      except Exception as e:
        print 'find task_id %d' % i
        continue
      except IOError as e:
        print "find IOError: %r" % e
        continue

And I have doubt that it's my MySQL configuration's problem, but I think it's not the problem. Here is my.cnf:

[mysqld]
default-character-set=utf8
collation_server = utf8_general_ci
character_set_server = utf8
max_allowed_packet = 100M

datadir=/home/work/data1/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
slow_query_log
slow_query_log_file=/home/work/data1/mysql/mysql-slow.log
max_allowed_packet=100M

log-error=/home/work/data1/mysql/error.log
general_log
general_log_file=/home/work/data1/mysql/mysql.log

tmp_table_size=2G
max_heap_table_size=2G

wait_timeout=2880000
interactive_timeout=2880000
innodb_data_home_dir=/home/work/data1/mysql/ibdata/

[mysqld_safe]
default-character-set=utf8
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Answer 1

i have found the reason why the db connection crash, share some points here.

The problem is caused by the order of 'init_data' and 'fork subProcess':

self.init_data()

for i in range(PROCESS_NUM):
  Process(target=access_queue, args=(self.input_queue, self.output_queue)).start()

in this order, when executing init_data(), the db connection has been built and then fork the subProcess, then the connection is copyed into the subprocess. So all the subprocess are using the same connection actually, it will cause some indeterminate problems definitely.

When i change the order to:

for i in range(PROCESS_NUM):
  Process(target=access_queue, args=(self.input_queue, self.output_queue)).start()

self.init_data()

and add sleep for subprocess:

def access_queue(id, input_queue, output_queue):
  time.sleep(5)
  while True:
    ...

it works. Because under this change, the subProcesses will be constructed before the connection built and then the subProcesses will use their separate connection to db.

so i have a question:

Is there any graceful solutions to resolve/prevent this kind of problems in multiprocessing to access db by orm ?

Anyone to share some points?

thanks

Have found a good post: Django multiprocessing and database connections