0

I have used Django to develop a web app. When I tried to init the mysql database using python manage.py migrate, I got the error:

    _

Operations to perform:
  Apply all migrations: admin, auth, bms, contenttypes, notifications, reversion, sessions
Running migrations:
  Applying bms.0002_auto_20210610_1003...Traceback (most recent call last):
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\db\backends\mysql\base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\MySQLdb\cursors.py", line 206, in execute
    res = self._query(query)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\MySQLdb\cursors.py", line 319, in _query
    db.query(q)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\MySQLdb\connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1071, 'Specified key was too long; max key length is 3072 bytes')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 21, in <module>
    main()
  File "manage.py", line 17, in main
    execute_from_command_line(sys.argv)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\core\management\__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\core\management\__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\core\management\base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\core\management\base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\core\management\base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\core\management\commands\migrate.py", line 234, in handle
    fake_initial=fake_initial,
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\db\migrations\executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\db\migrations\executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\db\migrations\executor.py", line 245, in apply_migration
    state = migration.apply(state, schema_editor)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\db\migrations\migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\db\migrations\operations\models.py", line 530, in database_forwards
    getattr(new_model._meta, self.option_name, set()),
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\db\backends\base\schema.py", line 367, in alter_unique_together
    self.execute(self._create_unique_sql(model, columns))
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\db\backends\base\schema.py", line 137, in execute
    cursor.execute(sql, params)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\db\backends\utils.py", line 99, in execute
    return super().execute(sql, params)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\db\backends\utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\db\backends\utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\db\utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\django\db\backends\mysql\base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\MySQLdb\cursors.py", line 206, in execute
    res = self._query(query)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\MySQLdb\cursors.py", line 319, in _query
    db.query(q)
  File "C:\Users\hcnguyen\AppData\Local\Programs\Python\Python37\lib\site-packages\MySQLdb\connections.py", line 259, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (1071, 'Specified key was too long; max key length is 3072 bytes')

I have tried :
ALTER DATABASE databasename CHARACTER SET utf8;
CREATE DATABASE mydatabase CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

All failed in my case.

model.py:

     class User(AbstractUser):
    user_type = models.CharField(max_length=12, choices=USER_TYPE_CHOICES, null=True, blank=True)
    auth_source = models.CharField(max_length=10, null=True, blank=True, default='local')  # Local or LDAP
    #is_external = models.BooleanField("Is Non-SUSS user", default=False)  # for vendors (Publisher, Makono, Distributor/Vendor)
    # avatar = models.ImageField(upload_to='avatars/', null=True, blank=True)
    executives = models.ManyToManyField(settings.AUTH_USER_MODEL, related_name='hop_executives', blank=True)  # for executives report to HOP

    def __str__(self):
        return self.username
class TimeStampedModel(models.Model):
    """
    An abstract base class model that provides self-updating
    ``created`` and ``modified`` fields.
    """
    created = models.DateTimeField(auto_now_add=True)
    modified = models.DateTimeField(auto_now=True)

    class Meta:
        abstract = True
        ordering = ['modified']


class BaseModel(TimeStampedModel):
    creator = models.ForeignKey(settings.AUTH_USER_MODEL, verbose_name='Created by', blank=True, null=True,
                                related_name="%(app_label)s_%(class)s_created", on_delete=models.SET_NULL)
    updater = models.ForeignKey(settings.AUTH_USER_MODEL, verbose_name='Updated by', blank=True, null=True,
                                related_name="%(app_label)s_%(class)s_updated", on_delete=models.SET_NULL)

    def save(self, *args, **kwargs):
        if self.pk is None and hasattr(local, 'user'):
            self.creator = local.user
        return super(BaseModel, self).save(*args, **kwargs)

    class Meta:
        abstract = True


class School(models.Model):  # school or center
    code = models.CharField(max_length=20, unique=True)
    name = models.CharField(max_length=190)
    cost_center = models.CharField(max_length=12)  # UC1001, GF1004

   
    def __str__(self):
        return self.name
'''
class Discipline_code(models.Model):  # school or center
    code = models.CharField(max_length=20, unique=True)
    Discipline = models.CharField(max_length=200)
    Discipline_code = models.CharField(max_length=12)  # UC1001, GF1004

    # __str__ method is used to override default string returned by an object
    def __str__(self):
        return self.Discipline'''


class Discipline(TimeStampedModel):
    #code = models.CharField(max_length=20, db_index=True)
    name = models.CharField(max_length=190)
    school = models.ForeignKey(School, on_delete=models.SET_NULL, blank=True, null=True)
    hop1 = models.ForeignKey(settings.AUTH_USER_MODEL, related_name='primary_hop', on_delete=models.SET_NULL, blank=True,
                             null=True)  # Head of Program, as primary
    hop2 = models.ForeignKey(settings.AUTH_USER_MODEL, related_name='secondary_hop', on_delete=models.SET_NULL, blank=True,
                             null=True)  # Head of Program, as backup?
    executive = models.ForeignKey(settings.AUTH_USER_MODEL, related_name='executive', on_delete=models.SET_NULL, blank=True,
                             null=True)  # Head of Program, as backup?
    executive2 = models.ForeignKey(settings.AUTH_USER_MODEL, related_name='executive2', on_delete=models.SET_NULL, blank=True,
                             null=True)  # Head of Program, as backup?
    executive3 = models.ForeignKey(settings.AUTH_USER_MODEL, related_name='executive3', on_delete=models.SET_NULL, blank=True,
                             null=True)  # Head of Program, as backup?

    class Meta:
        ordering = ('name',)

    def __str__(self):
        return self.name





class CourseInfo(TimeStampedModel):  # school or center
    code = models.CharField(max_length=20, db_index=True)
    #code = models.CharField(max_length=20, db_index=True)
    title = models.CharField(max_length=190)
    school = models.ForeignKey(School, on_delete=models.SET_NULL, blank=True, null=True)
    postgraduate = models.BooleanField(default=False)  # indicate Postgraduate course
    discipline = models.ForeignKey(Discipline, on_delete=models.SET_NULL, blank=True, null=True)  # should not be null
    discipline_code = models.CharField(max_length=20, blank=True, null=True)
    last_semester_presented = models.CharField(max_length=20, blank=True, null=True)
    pattern = models.CharField(max_length=120, choices=PRESENTATION_PATTERN, blank=True, null=True,
                               help_text="Presentation Pattern")
    type = models.TextField(blank=True)
    available = models.BooleanField(default=True)  # mean Active or Retired

    semesters = models.ManyToManyField('Semester', through="Course")  # auto have semestercourse_set

    # __str__ method is used to override default string returned by an object
    def __str__(self):
        #return "{} - {}".format(self.code, self.title)
        return "{}".format(self.title)


    class Meta:
        #constraints = [models.UniqueConstraint(fields=['code', 'type'], condition=models.Q(available=True),
         #                                      name='unique_course_type')]
        #unique_together = ('discipline_code', 'code', 'type', 'semesters')
        ordering = ['code', ]


class Semester(models.Model):  # the semester where the book is used
    year = models.PositiveIntegerField()
    month = models.CharField(max_length=20)
    slug = models.SlugField(default='', editable=False, max_length=190)
    courses = models.ManyToManyField(CourseInfo, through="Course")

    class Meta:
        unique_together = ('year', 'month')
        ordering = ('-year', '-month',)

    def save(self, *args, **kwargs):
        value = "{}{}".format(self.month, self.year)
        self.slug = slugify(value, allow_unicode=True)
        super().save(*args, **kwargs)

    # __str__ method is used to override default string returned by an object
    def __str__(self):
        return "{}{}".format(self.month, self.year)


class Course(models.Model):  # CourseInfo Quota
    courseInfo = models.ForeignKey(CourseInfo, on_delete=models.CASCADE)
    semester = models.ForeignKey(Semester, on_delete=models.CASCADE)
    term = models.IntegerField(default=1)  # Optional, Term start: 1 or 2
    quota = models.IntegerField(blank=True, null=True)  # total student enrolment

    titles = models.ManyToManyField('Title', through="Material")  # performance issue, it will load all books, for the DRF only

    class Meta:
        # must add unique_together for course, course_type and semester
        ordering = ('semester',)

    def __str__(self):
        return "{}_{}".format(self.courseInfo.code, self.semester)


# relation containing Publisher of books
class Publisher(models.Model):
    name = models.TextField(blank=True, help_text="Publisher name")
    contact = models.CharField(max_length=50, blank=True, null=True)

    def __str__(self):
        return self.name

    class Meta:
        ordering = ['name', ]


CURRENCY_SGD = 'SGD'
CURRENCY_USD = 'USD'
CURRENCY_GBP = 'GBP'
CURRENCY_CHOICES = ((CURRENCY_SGD, 'SGD'), (CURRENCY_USD, 'USD'), (CURRENCY_GBP, 'GBP'))
LANG_CHOICES = (('en', 'English'), ('ch', "Chinese"), ('ta', "Tamil"), ('ma', "Malay"), ('ot', "Other"))


# must run createinitialrevisions whenever register a new model
# e.g: python3 manage.py createinitialrevisions bms.Book --comment="Initial revision."
# @reversion.register()
class Title(TimeStampedModel):
    type = models.CharField("Category", max_length=20, default='Textbook',
                            choices=(('Textbook', 'Textbook'), ('iSG', 'iStudy Guide'), ('Course Guide', 'Course Guide'),
                                     ('Practicum Handbook', 'Practicum Handbook'), ('Software', 'Software'), ('Reading', 'Reading'),
                                     ('Companion Site', 'Companion Site')))  # iSG, Book, etc
    title = models.CharField(max_length=100, db_index=True)
    author = models.CharField(max_length=100, blank=True, null=True)

    description = models.TextField(max_length=190, help_text="Enter a brief description of the book", blank=True,
                                   null=True)
    isbn = models.CharField('ISBN', max_length=25,
                            help_text='13 Characters <a href="https://www.isbn-international.org/content/what-isbn">ISBN number</a>',
                            blank=True, null=True)
    isbn_pbk = models.CharField('ISBN Paperback', max_length=25,
                                help_text='This is alternative format to be adopted if the main is out of stock',
                                blank=True, null=True)
    isbn_10 = models.CharField('ISBN (10)', max_length=10, blank=True, null=True)
    e_isbn = models.CharField('e_isbn', max_length=10, blank=True, null=True)
    e_isbn_2 = models.CharField('e_isbn_2', max_length=10, blank=True, null=True)
    publisher = models.ForeignKey('Publisher', on_delete=models.SET_NULL, blank=True, null=True)
    pub_date = models.CharField('date published', max_length=10, blank=True, null=True)
    edition = models.CharField(max_length=10, blank=True, null=True)
    language = models.CharField(max_length=5, choices=LANG_CHOICES, default='en')
    page_count = models.IntegerField(blank=True, null=True)
    info_link = models.CharField(max_length=190, blank=True, null=True)

    retail_price = models.DecimalField(max_digits=6, decimal_places=2, blank=True, null=True)
    retail_price_cur = models.CharField(max_length=4, choices=CURRENCY_CHOICES,
                                        default=CURRENCY_SGD)
    e_price = models.DecimalField(max_digits=6, decimal_places=2, blank=True, null=True)
    e_price_cur = models.CharField(max_length=4, choices=CURRENCY_CHOICES,
                                   default=CURRENCY_SGD)  # etextbook price
    suss_price = models.DecimalField(max_digits=6, decimal_places=2, blank=True, null=True)  # price for suss
    suss_price_cur = models.CharField(max_length=4, choices=CURRENCY_CHOICES,
                                      default=CURRENCY_SGD)  # always SGD

    cover = models.ImageField(blank=True, null=True, upload_to='book_image')
    thumbnail = models.ImageField(blank=True, null=True, upload_to='book_image')

    is_etextbook = models.BooleanField(default=False)  # etextbook
    vbid = models.CharField(max_length=50, blank=True, null=True)  # Vitalsource book ID

    print_available = models.BooleanField(default=False)  # for ibookstore to show/hide Printing function
    bridge_available = models.BooleanField(default=False)  # for ibookstore to indicate available on VS Bridge

    _version = models.IntegerField(default=1)  # to control version
    slug = models.SlugField(default='', editable=False, max_length=190)
    Format_issued = models.CharField(max_length=100, blank=True, null=True)
    Distribution_platform = models.CharField(max_length=100, blank=True, null=True)
    Shared_Text_Title_Remark = models.CharField(max_length=100, blank=True, null=True)
    STOCK = models.CharField(max_length=100, blank=True, null=True)
    Total_StockAndWrap = models.CharField(max_length=100, blank=True, null=True)
    Order_Qty_1st = models.CharField(max_length=100, blank=True, null=True)
    Order_Qty_2nd = models.CharField(max_length=100, blank=True, null=True)
    Order_Qty_3rd = models.CharField(max_length=100, blank=True, null=True)
    Order_Qty_4th = models.CharField(max_length=100, blank=True, null=True)
    Total_StocktakeAndQty_Purchase = models.CharField(max_length=100, blank=True, null=True)
    Buffer = models.CharField(max_length=100, blank=True, null=True)
    Discard_Status = models.CharField(max_length=100, blank=True, null=True)
    Qty_to_Discard = models.CharField(max_length=100, blank=True, null=True)
    Reason_for_discarding_stock = models.CharField(max_length=100, blank=True, null=True)
    Print_Unit_Price = models.CharField(max_length=100, blank=True, null=True)
    Total_Cost_Wastage = models.CharField(max_length=100, blank=True, null=True)
    PO_Number = models.CharField(max_length=100, blank=True, null=True)
    Invoice_Number = models.CharField(max_length=100, blank=True, null=True)
    Supplier = models.CharField(max_length=100, blank=True, null=True)
    Supplier_Contact = models.CharField(max_length=100, blank=True, null=True)
    Cost_Revenue_Centre = models.CharField(max_length=100, blank=True, null=True)
    Product_Code = models.CharField(max_length=100, blank=True, null=True)
    Product_Text = models.CharField(max_length=100, blank=True, null=True)
    Account = models.CharField(max_length=100, blank=True, null=True)
    Progcse = models.CharField(max_length=100, blank=True, null=True)
    Budyear = models.CharField(max_length=100, blank=True, null=True)

    # courses = models.ManyToManyField(Course, through="Material")  # performance issue

    # org_id = models.UUIDField()

    # return canonical url for an object
    def get_absolute_url(self):
        kwargs = {
            'slug': self.slug,
            'pk': self.id,
        }
        return reverse('bms:book_detail', kwargs=kwargs)

    def save(self, *args, **kwargs):
        value = self.title
        self.slug = slugify(value, allow_unicode=True)
        super().save(*args, **kwargs)

    # __str__ method is used to override default string returned by an object
    def __str__(self):
        return self.title

    @property
    def retail_price_display(self):
        if self.retail_price:
            return "{} ${}".format(self.retail_price_cur, self.retail_price)
        return ""

    class Meta:
        ordering = ['-modified']




class ContentChecklist(BaseModel):
    slug = models.SlugField(editable=False, max_length=20)
    discipline = models.ForeignKey(Discipline, on_delete=models.CASCADE)
    course_code = models.CharField(max_length=190, blank=True, null=True)
    course_title = models.CharField(max_length=190, blank=True, null=True)
    #type = models.CharField(max_length=2, choices=(('PT', 'Part Time'), ('FT', 'Full Time'), ('OL', 'Online')))
    type = models.CharField(max_length=190, blank=True, null=True)
    semester = models.ForeignKey(Semester, on_delete=models.CASCADE)
    # hop = models.ForeignKey(User, on_delete=models.CASCADE)
    status = models.CharField(max_length=20, default=DRAFT, choices=MATERIAL_STATUS_CHOICES)
    remark = models.CharField(max_length=190, blank=True, null=True)
    postgraduate_course = models.CharField(max_length=190, blank=True, null=True)
    Presentation_pattern = models.CharField(max_length=190, blank=True, null=True)

    class Meta:
        unique_together = ('discipline', 'course_code', 'type', 'semester')
        ordering = ('semester', 'discipline', 'type', )

    def save(self, *args, **kwargs):
        if not self.pk:  # generate slug value
            value = str(self.discipline.name) + "-" + str(self.semester)
            self.slug = slugify(value, allow_unicode=True)
        super().save(*args, kwargs)

    def get_absolute_url(self):
        kwargs = {
            'slug': self.slug,
            'pk': self.pk,
        }
        return reverse_lazy('bms:cc_detail', kwargs=kwargs)

    def __str__(self):
        return "{} {} {} - Content Checklist".format(self.discipline, self.type, self.semester)

    @property
    def total_titles(self):
        return self.material_set.count()

    @property
    def total_course(self):
        return self.material_set.values('semester_course_id').distinct().count()
        # return self.material_set.filter(semester_course__semester=self.semester,
        #                                 semester_course__course__in=self.discipline.course_set.all()).count()


# to contain list of Title that will involve in a Tender Exercise
# titles from 7 publisher will automatically out from TE
class TenderExercise(TimeStampedModel):
    slug = models.SlugField(default='', editable=False, max_length=20)
    from_year = models.CharField(max_length=20)  # from semester year
    from_month = models.CharField(max_length=20, choices=MONTH_CHOICES)
    to_year = models.CharField(max_length=20)  # to semester year
    to_month = models.CharField(max_length=20, choices=MONTH_CHOICES)
    materials = models.ManyToManyField(Material)
    status = models.CharField(max_length=20, default=DRAFT, editable=False)
    vendor = models.CharField(max_length=120, blank=True, null=True)  # this is winning vendor

    def save(self, *args, **kwargs):
        if not self.pk:  # generate slug value
            value = self.from_month + self.from_year + "-" + self.to_month + self.to_year
            self.slug = slugify(value, allow_unicode=True)
        super().save(*args, kwargs)

    @property
    def total_titles(self):
        return len(self.materials)
    
class Task(BaseModel):
    name = models.CharField(max_length=120)
    description = models.CharField(max_length=190, blank=True, null=True)
    category = models.CharField(max_length=120, choices=CATEGORY_CHOICES)
    start_date = models.DateTimeField(auto_now_add=True)
    end_date = models.DateTimeField(null=True, blank=True)
    # initiator => refer to the creator of the BaseModel
    # assignee should be multiple, to create TaskUser table for this
    assignee_hop = models.ForeignKey(settings.AUTH_USER_MODEL, null=True, on_delete=models.CASCADE, related_name='assignee_hop')
    assignee_hop2 = models.ForeignKey(settings.AUTH_USER_MODEL, null=True, on_delete=models.CASCADE, related_name='assignee_hop2')
    assignee_executive = models.ForeignKey(settings.AUTH_USER_MODEL, null=True,  on_delete=models.CASCADE, related_name='assignee_executive')
    assignee_executive2 = models.ForeignKey(settings.AUTH_USER_MODEL, null=True, on_delete=models.CASCADE, related_name='assignee_executive2')
    assignee_executive3 = models.ForeignKey(settings.AUTH_USER_MODEL, null=True, on_delete=models.CASCADE, related_name='assignee_executive3')
    status = models.CharField(max_length=20)

    def __str__(self):
        to_tz = timezone.get_default_timezone()
        return "{} task: {} is due on {}".format(self.category, self.name, self.end_date.astimezone(to_tz).strftime("%Y-%m-%d %H:%M:%S"))


class TaskData(models.Model):
    task = models.ForeignKey(Task, on_delete=models.CASCADE)
    name = models.CharField(max_length=64)
    value = mod

els.CharField(max_length=190)

...

How could I quickly fix this problem?

django
  • 13
  • 6

1 Answers1

0

Depending what your query is, it seems like you need to change the size of the column in MySQL. SEE: stackoverflow question MySQL varchar index length. Look at this link to see how many bytes a utf8 character can be Max. bytes in a UTF-8 char?.

It seems like you can alter the table right? So if you had something like

CREATE TABLE `test`.`my_table` (
  `my_table_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `my_varchar_column` VARCHAR(768) NULL,
  `my_indexed_column` VARCHAR(768) NULL,
  PRIMARY KEY (`my_table_id`),
  INDEX `index2` (`my_indexed_column` ASC) VISIBLE);

you might need to alter the table column in a manner similar to this:

ALTER TABLE `test`.`my_table` 
CHANGE COLUMN `my_varchar_column` `my_varchar_column` VARCHAR(1024) NULL DEFAULT NULL ,
CHANGE COLUMN `my_indexed_column` `my_indexed_column` VARCHAR(2048) NULL DEFAULT NULL ;

NOTE that according to the link about utf-8 characters, I initially set the size of the column to be 768 utf-8 characters, which at a maximum; would be 3027 bytes.

If you need to alter a bunch of tables the following example is an option to get started. THIS IS JUST SIMPLE EXAMPLE OF WHAT YOU CAN DO WITH GROUP_CONCAT, YOU WILL NEED TO MODIFY IT TO MEET YOUR NEEDS - AND BE CAREFUL.

set session group_concat_max_len = 1024 * 1024 * 124;

select
  group_concat(
    concat(
      'alter `', table_name, '` CHANGE COLUMN `', column_name, '` `', column_name, '` VARCHAR(1024);' 
    )
    separator '\r\n'
  ) as stmt
from
  information_schema.columns
where
  table_schema = 'your_database_name_here'
  and
  table_name in ('your', 'list', 'of', 'tables', 'to', 'alter', 'here')
  and
  data_type like 'varchar'
  and
  CHARACTER_MAXIMUM_LENGTH < 255; # You don't need this line, it's just an example

Then follow these 6 simple steps

  1. Make the adjustments as 'quoted' in the query (then run it)
  2. Copy the result to the clipboard
  3. Paste the result into a new query tab
  4. Examine the script, MAKE SURE IT IS WHAT YOU WANT
  5. Then make sure it is what you want
  6. Then delete it because I don't want to be responsible for giving you the script to alter a bunch of tables that shouldn't have been altered.

Again... the above sql is just a glimpse at what you probably need to do.

Mark Davich
  • 512
  • 1
  • 5
  • 16
  • I have a lot of tables, tens of columns has VARCHAR(1000), so could not change to 2048 one by one, any quick method in mysql workbench? – django Jun 10 '21 at 06:29
  • Yes, there are quick ways to alter many tables if you are good with MySQL. I would use GROUP_CONCAT coupled with INFORMATION_SCHEMA.COLUMNS to build a dynamic statement (like `set @stmt = GROUP_CONCAT(...` and then execute the statment with `prepare stmt from @stmt; execute stmt; deallocate prepare stmt;` Any more than that you will have to ask another question on stackoverflow. **Have you determined that you need to increase the size of the columns?** – Mark Davich Jun 10 '21 at 06:36
  • I just need to do database migrate to init the database so I could load the data. I am ok with any size of the column as long as this error disappears – django Jun 10 '21 at 06:42
  • I edited my answer to include some sql magic, maybe it will help. Just to get started – Mark Davich Jun 10 '21 at 07:02
  • Check the answer that @MewX provides here: https://stackoverflow.com/a/47484842/4538640 It seems to be related – Mark Davich Jun 10 '21 at 07:43
  • I have tried all , all failed, any force method? can break the database or whatever as long as the error disappears coz I would get fired if can not show boss the database tmr – django Jun 10 '21 at 10:20