Cannot run with MySQL 5.7.14 version: Data too long for column 'thumbnail'

[Przybyszo]

Auto-reviewers: @NiharikaRay @matthewwardrop @earthmancash @danfrankj

Hi. I am experiencing some problems with deploying knowledge repo with the database specified above. It fails when inserting posts to the posts table due to:

sqlalchemy.exc.DataError: (_mysql_exceptions.DataError) (1406, "Data too long for column 'thumbnail' at row 1") 
[SQL: 'UPDATE posts SET keywords=%s, thumbnail=%s, private=%s, created_at=%s, 
updated_at=%s, status=%s WHERE posts.id = %s'] 
[parameters: ("mariusz wesley_wisdom Mariusz's post This short description content findings post. knowledge example", 
'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAA4AAAAJ4CAYAAAA0go2XAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAIABJREFUeJzsvXnYJFV5/n8fZphhZtg ... 
(85608 characters truncated) ... 
sjneSxmu9NiJaWnzbuoOPn5mZTTYOAM3MzMzMzCYJdwE1MzMzMzObJBwAmpmZmZmZTRIOAM3MzMzMzCYJB4BmZmZmZmaThANAMzMzMzOzScIBoJmZmZmZ2STx/3OJavwkr/sSAAAAAElFTkSuQmCC', 
0, datetime.datetime(2016, 6, 29, 0, 0), datetime.datetime(2018, 6, 12, 0, 0), 3, 1)] (Background on this error at: http://sqlalche.me/e/9h9h)

I guess this is a problem with this script: https://github.com/airbnb/knowledge-repo/blob/9c60931151d4f6e2c855f61760d70957823b09ba/knowledge_repo/app/migrations/versions/40dfdcb2b07c_initial_version.py. It probably works ok when applied to PostgreSQL, because the Text column type can store 2^32-1, but the same column type in MySQL can store only 2^16-1. When I manually altered the thumbnail column in the posts table from text to longtext, it started working.

[CPapadim]

There's a similar issue with the data column, in that it uses a blob, which is 64 KB. That's quite small, especially if you're embedding interactive plots. This leads to the notebook being truncated. MEDIUMBLOB or LONGBLOB work fine.

[matthewwardrop]

Thanks for the heads up @Przybyszo and @CPapadim . Currently we've specified the datatypes in backend-agnostic ways via SQLAlchemy. It would be great to resolve this in a way that doesn't require special-casing. I don't have a lot of time to look into this right now, but if anyone does, patches are welcome!

[CPapadim]

Depending on the data type, you can pass a size to SQLAlachemy data classes and they will correctly create the right columns. E.g. I believe Text(4294000000) will make a Text type on Postgres since that supports large texts, but a LONGTEXT in MySQL. Similarly for Longblob you can pass LargeBinary(length=###) and it will intelligently create the right data type.