Modify

Ticket #1052 (closed defect: invalid)

Opened 2 years ago

Last modified 2 years ago

db.create_table generates index name too long for mysql

Reported by: fexcoffi@… Owned by: andrew
Priority: trivial Milestone: The Future
Component: commands Version: 0.7.3
Keywords: Cc:

Description

Issue does not exist on 0.5:
CREATE INDEX management_system_named_system_group_systems_namedsystemgroup_i ON management_system_named_system_group_systems (namedsystemgroup_id)

On 0.73:
errorhandler
_mysql_exceptions.OperationalError?: (1059, "Identifier name 'management_system_named_system_group_systems_groupsysteminstance_id' is too long")

django ran into similar issue in the past https://code.djangoproject.com/ticket/1820
I ran into that issue converting script from 0.5 with M2M:

db.create_table('management_system_named_system_group_systems', (

('id', self.gf('django.db.models.fields.AutoField?')(primary_key=True)),
('namedsystemgroup', self.gf('django.db.models.fields.related.ForeignKey?')(NamedSystemGroup?)),
('groupsysteminstance', self.gf('django.db.models.fields.related.ForeignKey?')(GroupSystemInstance?)),
))

when generating index name, it concatenates all the names - resulting in a name too long for mysql to handle.
I did a syncdb to see what django (1.1.4) did and it did not even bother generating an index, so no help there. As we are talking about index names here, I don't think we really care on the name as long as it is unique.... so maybe the hash suggestion in the django ticket might sufice.

Attachments

Change History

comment:1 Changed 2 years ago by andrew

  • Status changed from new to infoneeded

Please re-test using the latest development version of South - 0.7.3 is now very old, and we're about to make a new release, and I remember at least something like this being fixed.

comment:2 Changed 2 years ago by anonymous

  • Status changed from infoneeded to assigned

looked at the code: something is definitely wrong - it should have been truncated

max_index_name_length = 63

def create_index_name(self, table_name, column_names, suffix=""):

"""
Generate a unique name for the index
"""

table_name = table_name.replace('"', ).replace('.', '_')
index_unique_name =

if len(column_names) > 1:

index_unique_name = '_%x' % abs(hash((table_name, ','.join(column_names))))

# If the index name is too long, truncate it
index_name = ('%s_%s%s%s' % (table_name, column_names[0], index_unique_name, suffix)).replace('"', ).replace('.', '_')
if len(index_name) > self.max_index_name_length:

part = ('_%s%s%s' % (column_names[0], index_unique_name, suffix))
index_name = '%s%s' % (table_name[:(self.max_index_name_length-len(part))], part)

return index_name

will post my findings. Will also have a look at the last dev version.

comment:3 Changed 2 years ago by fexcoffi@…

tracked down the issue to django itself:

south calls django: in genery.py column_sql:

# In 1.2 and above, you have to ask the DatabaseCreation? stuff for it.
# This also creates normal indexes in 1.1.
if hasattr(self._get_connection().creation, "sql_indexes_for_field"):

# Make a fake model to pass in, with only db_table
model = self.mock_model("FakeModelForGISCreation", table_name)
for stmt in self._get_connection().creation.sql_indexes_for_field(model, field, no_style()):

self.add_deferred_sql(stmt)

that version of django code itself does not truncate (issue I previously indicated):

def sql_indexes_for_field(self, model, f, style):

"Return the CREATE INDEX SQL statements for a single model field"
if f.db_index and not f.unique:

qn = self.connection.ops.quote_name
tablespace = f.db_tablespace or model._meta.db_tablespace
if tablespace:

sql = self.connection.ops.tablespace_sql(tablespace)
if sql:

tablespace_sql = ' ' + sql

else:

tablespace_sql =

else:

tablespace_sql =

output = [style.SQL_KEYWORD('CREATE INDEX') + ' ' +

style.SQL_TABLE(qn('%s_%s' % (model._meta.db_table, f.column))) + ' ' +
style.SQL_KEYWORD('ON') + ' ' +
style.SQL_TABLE(qn(model._meta.db_table)) + ' ' +
"(%s)" % style.SQL_FIELD(qn(f.column)) +
"%s;" % tablespace_sql]

else:

output = []

return output

That ticket can be canceled - nothing can be done on south side - will patch the django code accordingly.

comment:4 Changed 2 years ago by fexcoffi@…

does not look like I can cancel that ticket - please do so for me - thanks

comment:5 Changed 2 years ago by andrew

  • Status changed from assigned to closed
  • Resolution set to invalid
View

Add a comment

Modify Ticket

Action
as closed
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.