PickleType can't be indexed in MySQL

[Former user]

This is a pretty specific one. When using the PickleType in MySQL in an indexed column, table creation fails. This is because PickleType doesn't support a length parameter, and MySQL doesn't allow a table field to be indexed without defining a length.

My workaround was this:

class PickleWithLengthType (PickleType):
    def __init__(self, protocol=pickle.HIGHEST_PROTOCOL,
                    pickler=None, mutable=False, comparator=None, length=None):
        self.length = length
        super(PickleWithLengthType, self).__init__()

I'm attaching a proposed patch as well, it's fairly straightforward.

[Former user]

Proposed patch to types.py

[Former user]

I forgot to add something. In my workaround I also used this:

@compiles(LargeBinary, "mysql")
def compile_LargeBinary_mysql(type_, compiler, **kw):
    return "BLOB"

I would have thought LargeBinary produced BLOB columns on MySQL, but my code seems to fail if I don't override the type compilation like this. :(

[Former user]

Argh, my bad! It seems SQLAlchemy is raising an exception even though the table is created. I only thought it was working because the table was already there and create_all() was doing nothing! :P

I don't know yet how to make this work then, please ignore my failed patch...

[Former user]

Ok, I think I finally figured out. After reading the Fine Manual, I came across this: http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#mysql-indexes

So the correct solution was to avoid using the "index=True" argument in Column and creating the Index myself, using the mysql_length argument.

I suppose this ticket can be marked as invalid, then. I can't seem to do it myself.

Sorry for having wasted your time! :(

[Mike Bayer]

no worries, glad you figured it out !