Issue: Allow specifying MySQL Index Sizes for large columns
Author: jakedt
State: closed
Created: 2015-07-28 20:20:23

InternalError: (1071, u'Specified key was too long; max key length is 767 bytes')

We get this when we use peewee to `create_table`s, and we have an index constraint on a column larger than 767. Apparently in the past MySQL has just truncated these indices to 767 silently, but newer versions are throwing errors when attempting to create these tables. It would be helpful if we could specify the index size separately to make these errors go away.


IIRC, the newer versions are throwing this error, because they default to having `STRICT_TRANS_TABLES` on.


@jakedt -- I'm not quite sure I understand. What exactly is going on here?


When we define a large column that is to be indexed, e.g.:

``` python
big_col = CharField(index=True, max_length=1024)
```

and the version of MySQL defaults to `STRICT_TRANS_TABLES`, subsequent calls to `create_model_tables([ModelWithBigCol])` will cause MySQL to spit out the error from above. We can't change the size of the columns, but we can tell MySQL that we're ok with using a smaller prefix for the index.

Here is an SO post talking about the issue: http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes


Gotcha -- I think this is a wontfix from my point-of-view. The issue is an implementation detail of MySQL's storage engines, and I don't think Peewee has an opinion on that.


Is there not something salvageable across DBMSs whereby we can specify the index prefix length though? This would be enough to allow us to work around the MySQL issues, and may prove helpful when other people are defining their indices with peewee, since they know what's best for their data model.


I ran into this problem myself, and my (admittedly partial) workaround for it was a custom field type:

```
class EnforcedCharField(CharField):
    def __init__(self, max_length=255, *args, **kwargs):
        self.max_length = max_length
        super(CharField, self).__init__(*args, **kwargs)

    def db_value(self, value):
        if value is None:
            length = 0
        else:
            try:
                length = len(str.encode(value))
            except TypeError:
                length = len(value)

        if length > ENFORCED_CHARFIELD_CONSTRAINT:
            from core.error import DatabaseError
            raise DatabaseError("The field '{}' cannot be longer than {} bytes. ({})".format(
                self.name,
                ENFORCED_CHARFIELD_CONSTRAINT,
                request.url))
        return super(CharField, self).db_value(value) 
```

(`ENFORCED_CHARFIELD_CONSTRAINT` is set elsewhere; in this cases it's 767.)

I ran into this problem mainly because of fields where Unicode data was present.


@jakedt -- what would you propose peewee do if a user defines a `CharField` with an index length exceeding what the storage engine supported? I don't think peewee should silently truncate the index -- instead it should raise an error. Well, MySQL is already raising the error for you, so in my mind there's nothing to do.


The problem for me is that the column length and the index length are inextricably tied together. If I could add a keyword to my column definition, e.g. `index_prefix_length=128`, and if this worked across providers, that would increase the expressiveness of the peewee model definitions and let me work around my MySQL issue.

Right now, facing this error, I have only one option, reduce the length of my column. If I can't do that because my data model requires a column of that length, I have zero options.


@jakedt -- could you create the index by hand?

``` python
class MyModel(Model):
    big_col = CharField(max_length=1024)

    @classmethod
    def create_table(cls, fail_silently=False):
        super(MyModel, cls).create_table(fail_silently=fail_silently)
        cls._meta.database.execute_sql('CREATE INDEX mymodel_big_col ON mymodel (big_col(255))
```


@coleifer What you just posted for @jakedt would actually solve my problem quite nicely from the look of it. I never liked the idea of enforcing length constraints for the sake of the underlying index, but I was also reluctant to pass explicit index creations (for the sake of maximum compatibility). Come to think of it, I might be able to set things up so that depending on what database is attached, the appropriate index-creation instructions are passed. (I don't think the example listed would have any ill effects when used in SQLite for instance, but I'd like to ensure it doesn't.)


We can't do that because then our migration generator tool will assume that we don't want an index. We can extend that to read additional keywords and work around that too, but I think the idea of having a smaller index than the column length is more general than just working around problems. If we take a stab at modifying peewee to allow for specifying an index_length, would you consider merging it?

I don't actually know how to extend the compound index syntax to allow for this yet.


@jakedt -- so you are proposing silently shortening the index length in Peewee? I don't think it's the ORM's business to do that. As I said,

> The issue is an implementation detail of MySQL's storage engines, and I don't think Peewee has an opinion on that.

I feel like I've proposed a reasonable solution (create the index explicitly), and @syegulalp has proposed another reasonable solution (create a custom field). At this point I feel like you're just complaining.