CODEWITHSUNDEEP
pythonmysqldjango
Cuong Tran
Cuong Tran

Reputation: 1989

Django Migration Error with MySQL: BLOB/TEXT column 'id' used in key specification without a key length"

We have Django Model, use Binary Field for ID.

# Create your models here.
class Company(models.Model):
    id = models.BinaryField(max_length=16, primary_key=True)
    name = models.CharField(max_length=12)

    class Meta:
        db_table = "company"

We use MySQL Database and have error when migrate.

  File "/home/cuongtran/Downloads/sample/venv/lib/python3.5/site-packages/MySQLdb/connections.py", line 270, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (1170, "BLOB/TEXT column 'id' used in key specification without a key length")

Do you have any solution? We need to use MySQL and want to use the Binary Field for ID.

Thank you!

Upvotes: 1

Views: 5551

Answers (2)

T.H.
T.H.

Reputation: 876

MySQL restricts the primary key on BLOB/TEXT column to first N chars, when you generates migration file using Django's makemigrations command, BinaryField in Django is mapped to longblob which is BLOB column in MySQL without specifying the key length.

Which means your Django model definition :

class Company(models.Model):
    id = models.BinaryField(max_length=16, primary_key=True)
    name = models.CharField(max_length=12)
    class Meta:
        db_table = "company"

will be converted to SQL expression that causes this error (You can check out the detailed SQL expressions by sqlmigrate command) :

CREATE TABLE `company` (`id` longblob NOT NULL PRIMARY KEY,
  `name` varchar(12) NOT NULL);

while the correct SQL expression for MySQL should be like this :

CREATE TABLE `company` (`id` longblob NOT NULL,
  `name` varchar(12) NOT NULL);
ALTER TABLE  `company` ADD PRIMARY KEY (id(16));

where PRIMARY KEY (id(16)) comes from your id length in the BLOB column, used to structure primary key index of the table.

So the easiest solution is as described in the accepted answer -- avoid BinaryField in Django as primary key, or you can manually add raw SQL scripts to your migration file if you really need BinaryField (BLOB column) to be primary key and you are sure the id field will NOT go beyond the specific size (in your case, 16 bytes).

Upvotes: 0

Aditya
Aditya

Reputation: 630

I think you cannot achieve this. Based on Django documentation it looks like use of binary fields is discouraged

A field to store raw binary data. It only supports bytes assignment. Be aware that this field has limited functionality. For example, it is not possible to filter a queryset on a BinaryField value. It is also not possible to include a BinaryField in a ModelForm.

Abusing BinaryField

Although you might think about storing files in the database, consider that it is bad design in 99% of the cases. This field is not a replacement for proper static files handling.

And based on a Django bug, it is most likely impossible to achieve a unique value restriction on a binary field. This bug is marked as wont-fix. I am saying most likely impossible as I did not find evidence to confirm that binary field is stored as a BLOB field but the error does allude to it.

Description

When I used a field like this:
text = models.TextField(maxlength=2048, unique=True)
it results in the following sql error when the admin app goes to make the table
_mysql_exceptions.OperationalError: (1170, "BLOB/TEXT column 'text' used in key specification without a key length")
After a bit of investigation, it turns out that mysql refuses to use unique with the column unless it is only for an indexed part of the text field: 

CREATE TABLE `quotes` ( \`id\` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `text` longtext NOT NULL , \`submitTS\` datetime NOT NULL, `submitIP` char(15) NOT NULL, `approved` bool NOT NULL, unique (text(1000)));

Of course 1000 is just an arbitrary number I chose, it happens to be the maximum my database would allow. Not entirely sure how this can be fixed, but I figured it was worth mentioning.

Upvotes: 3

Related Questions