👻

[Flask-SQLAlchemy] MySQLへのinsert でBrokenPipeErrorが発生する場合の対処

2020/11/27に公開

MySQL

はじめに

Flask-SQLAlchemyを使ってMySQLにdataを格納する際に、LargeBinaryでmodelを定義するとBrokenPipeErrorが発生することがあります。この解決方法としてはdataのsizeに応じた適切なdata type指定が必要となるため、以下にまとめます。

環境

MySQL: 5.7
Python: 3.7.4
Flask: 1.1.2
Flask-SQLAlchemy: 2.4.4
LargeBinaryとして格納しようとしたdata size: 100MB程度

error内容

Flask-SQLAlchemyで以下のmodel定義をすると、insert処理でBrokenPipeErrorが発生します。

model.py

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class TestStorage(db.Model):
    id = db.Column(String(64), primary_key=True)
    name = db.Column(String(32), primary_key=True)
    data = db.Column(LargeBinary()) # ここに100MBのdataを格納したい

error内容は以下のとおりです。

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

解決方法

application(Flask-SQLAlchemy)とserver(MySQL)の両方での対応が必要となります。

LargeBinaryのparameter設定

Flask-SQLAlchemyにおいては、LargeBinaryのmodel定義はdefaultでBLOBに変換されます。BLOBのsize制限は65535bytesであり、それよりsizeの大きいdataを格納する場合にはMEDIUMBLOBやLONGBLOBを定義します。

mysql> describe TestStorage;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | varchar(64) | NO   | PRI | NULL    |       |
| name    | varchar(32) | NO   | PRI | NULL    |       |
| data    | blob        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

この対処としてlinkの回答にあるようにLargeBinaryのparameterにlengthを指定します。代替としてdialects(方言、なまり)での記述も可能です。

model.py

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.dialects import mysql

db = SQLAlchemy()

class TestStorage(db.Model)
    id = db.Column(String(64), primary_key=True)
    name = db.Column(String(32), primary_key=True)
    data = db.Column(LargeBinary(length=(2**32)-1)) # lengthで指定
    data_a = db.Column(mysql.LONGBLOB) # dialectsで指定
    data_b = db.Column(LargeBinary().with_variant(LONGBLOB, "mysql")) # dialectsで指定

mysql> describe TestStorage;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | varchar(64) | NO   | PRI | NULL    |       |
| name    | varchar(32) | NO   | PRI | NULL    |       |
| data    | longblob    | YES  |     | NULL    |       |
| data_a  | longblob    | YES  |     | NULL    |       |
| data_b  | longblob    | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MySQLのmax-allowed-packet設定

上述に加え、serverに流れるpacket sizeを変更する必要があります。defaultだとmax-allowed-packetは67108864bytesであるため、これを超えるdataを取り扱う場合は変更します。

以下はMySQLのofficial documentの抜粋ですが、data typeの定義だけでなくpacket sizeの変更が必要であることがさらっと書いてあります。

You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

以下はpacket sizeを最大値に変更する例です。

mysqld --max-allowed-packet=1073741824

以上、applicationとserverに必要となる2点を説明しましたが、これらはいずれもdata sizeの制限に対するものです。

まとめ

このerror調査のために半日以上要しました。同じerrorに遭遇した人々が、私と同じように時間を費やさなくて済むのであれば幸いです。

Flask-SQLAlchemyは各種databaseの差異を吸収してくれる便利な道具ですが、本件のようにcoverできない場合も当然あり、その場合はやはりnativeの知識が必要になります。