MySQL result backend defaults to BLOB which may be too short for some results (LONGBLOB)

I was running into errors caused by loading truncated pickled results. Specifically, I would get an EOFError (see below). I eventually figured out that the result column of the celery_taskmeta MySQL table was not able to hold all of the pickled result data. By altering the table to use LONGBLOB instead of BLOB, I was able to deal with the large results (alter table celery_taskmeta modify result LONGBLOB). Is there a way to make this the default type in the future?

[2011-08-31 13:34:13,605: ERROR/PoolWorker-1] 
Traceback (most recent call last):
  File "/home/ubuntu/w/apis/trunk/apis/celerytasks/tasks/file_task_base.py", line 293, in process
    self.process_records()
  File "/home/ubuntu/w/apis/trunk/apis/celerytasks/tasks/proxmatch_file.py", line 381, in process_records
    self.wait_results(async_result_buffer)
  File "/home/ubuntu/w/apis/trunk/apis/celerytasks/tasks/proxmatch_file.py", line 435, in wait_results
    async_result.wait()
  File "/usr/local/lib/python2.6/dist-packages/celery/result.py", line 87, in wait
    return self.get(*args, **kwargs)
  File "/usr/local/lib/python2.6/dist-packages/celery/result.py", line 83, in get
    interval=interval)
  File "/usr/local/lib/python2.6/dist-packages/celery/backends/base.py", line 110, in wait_for
    status = self.get_status(task_id)
  File "/usr/local/lib/python2.6/dist-packages/celery/backends/base.py", line 206, in get_status
    return self.get_task_meta(task_id)["status"]
  File "/usr/local/lib/python2.6/dist-packages/celery/backends/base.py", line 227, in get_task_meta
    meta = self._get_task_meta_for(task_id)
  File "/usr/local/lib/python2.6/dist-packages/celery/backends/database.py", line 61, in _get_task_meta_for
    task = session.query(Task).filter(Task.task_id == task_id).first()
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line 1770, in first
    ret = list(self[0:1])
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line 1667, in __getitem__
    return list(res)
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/query.py", line 1960, in instances
    rows = [process[0](row, None) for row in fetch]
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/mapper.py", line 2600, in _instance
    populate_state(state, dict_, row, isnew, only_load_props)
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/mapper.py", line 2454, in populate_state
    populator(state, dict_, row)
  File "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/strategies.py", line 150, in new_execute
    dict_[key] = row[col]
  File "/usr/local/lib/python2.6/dist-packages/celery/db/a805d4bd.py", line 55, in process
    return loads(value)
EOFError

[ask]

I think we'd prioritise small results first, as this is the most common use case, but if a blob is not big enough to hold
"reasonably" sized results, then longblob wold be a good default.

Could we find out the current limit for blobs in mysql? What are the space implications for smaller results?

The MySQL manual offers this as the reason that an error was not raised when a pickled value longer than BLOB's max length is inserted:

http://dev.mysql.com/doc/refman/5.5/en/char.html

If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 5.1.6, “Server SQL Modes”.

This page has the allowed dimensions:

http://dev.mysql.com/doc/refman/5.5/en/string-type-overview.html

BLOB 65,535 (216 – 1) bytes
MEDIUMBLOB 16,777,215 (224 – 1) bytes
LONGBLOB 4,294,967,295 or 4GB (232 – 1) bytes

I don't think there is much overhead in switching between these types since they only take up the space one actually uses plus a few bytes to track the length. So I wouldn't expect smaller results to be impacted.

[ask]

Yeah, I know about strict mode, that it's not enabled by default is kind of unique to MySQL, and often criticised.
It's one of the first things I enable on new MySQL installs.

16bits is very small indeed, we should definitely increase that.

It seems that a BLOB in Oracle is 2**32 by default: http://ss64.com/ora/syntax-datatypes.html
In PostgreSQL it seems that the bytea type is preferred, and that it is of unlimited size. Maybe
BLOB maps to this field directly. I couldn't find any information about whether any of the SQL standards
describe dimensions for the BLOB field type.

django-celery uses django-picklefield, and pickled data is stored in a text field
there: https://github.com/shrubberysoft/django-picklefield/blob/master/src/picklefield/fields.py
I checked django.db.backends.mysql.creation and TextFields uses LONGTEXT on MySQL.

picklefield also uuencodes the data though, and there is an issue open for the sqlalchemy backend to use the same
method so the backends can be interchangeable: #183

I think maybe we should resolve issue #183 at the same time so we don't have to change the types that often.
Which means changing the BLOB to be a LONGTEXT

[ask]

On further inspection this is behavior from SQLAlchemy, where sqlalchemy.types.PickleType is a LargeBinary field that resolves to a BLOB when using MYSQL. Sadly there is no generic data type in sqlalchemy that is larger than a LargeBlob and so if we used a LARGE_BLOB it would not be compatible with all other supported databases.

I think sqlalchemy must be lobbied to change the default large binary type for MySQL, or people depending on large results must alter the table :(

[ericraymond]

Is there a resolution on this before it was closed?

[georgepsarakis]

@ericraymond the database result backend still uses PickleType, which maps to a LargeBinary field in SQLAlchemy as far as I can tell. I suggest that you review the previous comments and if you can provide or propose a solution please feel free to open a new issue.

[ericraymond]

@georgepsarakis I see your point.

Celery could directly use the MySQL longblob type but that would break the abstraction that Celery uses sqlalchemy for multiple database dialects. The other alternatives are for SQLAlchemy to change the MySQL dialect mapping or create a new (largeblob) MySQL Dialect.

[georgepsarakis]

@ericraymond I think I came up with a possible solution. We can redefine the field as LargeBinary and handle the pickling part separately (before storing the result). From the documentation, you can pass a length parameter to the field definition, which MySQL will then map to the corresponding type (MEDIUMBLOB, LONGBLOB) that can contain binary strings of this length:

-- Set the maximum length to 4GB
mysql> CREATE TABLE results(serialized_result BLOB(4294967295));
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE results\G
*************************** 1. row ***************************
       Table: results
Create Table: CREATE TABLE `results` (
  `serialized_result` longblob
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

What do you think?

[ericraymond]

Looks promising! Nice.

…

On Thu, Nov 29, 2018, 9:51 PM George Psarakis ***@***.***> wrote: @ericraymond <https://github.com/ericraymond> I think I came up with a possible solution. We can redefine the field as LargeBinary <https://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.LargeBinary> and handle the pickling part separately (before storing the result). From the documentation, you can pass a length parameter to the field definition, which MySQL will then map to the corresponding type (MEDIUMBLOB, LONGBLOB) that can contain binary strings of this length: -- Set the maximum length to 4GB mysql> CREATE TABLE results(serialized_result BLOB(4294967295)); Query OK, 0 rows affected (0.02 sec) mysql> SHOW CREATE TABLE results\G *************************** 1. row *************************** Table: results Create Table: CREATE TABLE `results` ( `serialized_result` longblob ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) What do you think? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#461 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AVcTN7N_0x3HYsOUSHF1EYFsriTRXYZnks5u0MdcgaJpZM4AIZ20> .