Forums

SQLAlchemy PickleType

Hello, I've got a problem running my application on PA. The app was previously working great on PA and still work perfectly using on desktop using the development server.

This model was introduce :

class Report(Base)
    __tablename__ = 'reports'
    identifier = Column(Integer, primary_key=True)
    title = Column(String(250), nullable=False, index=True)
    data = Column(PickleType)

This is the code (simplify) of the problematic view :

reports = session.query(Report).all()
return render_template('reports.html', reports=reports)

When there is not any report records in the database it display fine, when there is a report it throw out a '500 : internal server error' and the log file show 'EOFError: Ran out of input'.

I've wonder if it was the size of the record that was causing this troubles (but each LargeBinary field is arround 150 kB only) and I've got only one record in the table but when I switched the view code as following it was working again :

session.query(Report).options(load_only('identifier', 'title'))

So, that's great for the collection view, but accessing the ressource view (wich need to load the PickleType column) still throw an error and I've got no workaround... Could somebody help me please ?

Problem solved with more research on the web ! So if anyone run into it in the future, here is the solution : Problem is that SQLAlchemy default for storing PickleType on MySQL is a column of type "BLOB" wich can only contain 64 kB of data (my records where about 150 kB), so I guess during the storing process records have been truncated thus raisin the EOFError on load. I haven't spot the problem initialy as my dev configuration is a pgSQL database. So workaround is to set the column type to "LONGBLOB", manually. A bug has been reported to SQLAlchemy (https://bitbucket.org/zzzeek/sqlalchemy/issues/4177/largebinary-should-truly-be-unlimited-in) seem that in 1.3 this behaviour is going to change ! ;)

Thanks for sharing your findings!

And congrats on getting your bug patched in SQLAlchemy v1.3!

This is not my patch, anyway, happy to share the solution to this bug here!