Dashboard position_json truncates if dashboard gets too large

[michellethomas]

If a v2 dashboard gets too large, the position_json data will get truncated because the text field in mysql only allows up to 65kb of data. This will cause an internal server error as the position_json is invalid, and the data truncated will likely be unrecoverable unless you make frequent backups. More metadata is being stored in position_json in v2 (nested tabs, rows, columns), causing this issue.

Solutions

Simple changes to do soon:

• Removing unnecessary text from position_json (like DASHBOARD_* being used in type descriptions)[Reduce dashboard position_json data size #5543]
• Stop pretty printing json that goes into the db[Reduce dashboard position_json data size #5543]

Options to consider doing:

• Changing the type for position_json so it can store more data
• Gzip results or otherwise compressing the data
• Prevent the user from adding to dashboards over the size limit[Reduce dashboard position_json data size #5543]

Option 4 is an easy change that can reduce the size from 65kb to ~45kb, so we should do this soon. I think we also want to do option 2 to reduce space. We still need to look into whether option 3 is viable.

• I have checked the superset logs for python stacktraces and included it here as text if any
• I have reproduced the issue with at least the latest released version of superset
• I have checked the issue tracker for the same issue and I haven't found one similar

Steps to reproduce

Create a dashboard where the position_json is larger than 65kb.
Load the dashboard and see internal server error because of invalid json

@graceguo-supercat @mistercrunch @john-bodley

[mistercrunch]

In mysql, MEDIUMTEXT allows for 16mb:

      Type | Maximum length
-----------+-------------------------------------
  TINYTEXT |           255 (2 8−1) bytes
      TEXT |        65,535 (216−1) bytes = 64 KiB
MEDIUMTEXT |    16,777,215 (224−1) bytes = 16 MiB
  LONGTEXT | 4,294,967,295 (232−1) bytes =  4 GiB

https://stackoverflow.com/questions/13932750/tinytext-text-mediumtext-and-longtext-maximum-storage-sizes

Though while you could just go ALTER TABLE and call it a day in your env, we should find a way for SQLAlchemy to work regardless of the engine used. Maybe some conditions around which engine is used in a db migration script, and point to sqlalchemy.dialects.mysql.MEDIUMTEXT where mysql is used.

http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#sqlalchemy.dialects.mysql.MEDIUMTEXT

[graceguo-supercat]

@mistercrunch what do you think of using JSON data type:
https://dev.mysql.com/doc/refman/8.0/en/json.html

[EwertonDCSilv]

Problema ainda continua, o que pode ser feito para driblar isso ?