In MySQL, table names in unmanaged intermediate models are being lower cased

[Jim Dalton]

This is an obscure error, but it's causing an error in the MySQL (InnoDB) test suite when the entire suite is run. The error can be replicated as follows:

$ ./runtests.py --settings=testproject.settings unmanaged_models  proxy_model_inheritance
Creating test database for alias 'default'...
Creating test database for alias 'other'...
...E
======================================================================
ERROR: test_table_exists (modeltests.proxy_model_inheritance.tests.ProxyModelInheritanceTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/jsdalton/webs/testproject/src/django/tests/modeltests/proxy_model_inheritance/tests.py", line 25, in setUp
    call_command('syncdb', verbosity=0)
  File "/Users/jsdalton/webs/testproject/src/django/django/core/management/__init__.py", line 166, in call_command
    return klass.execute(*args, **defaults)
  File "/Users/jsdalton/webs/testproject/src/django/django/core/management/base.py", line 220, in execute
    output = self.handle(*args, **options)
  File "/Users/jsdalton/webs/testproject/src/django/django/core/management/base.py", line 351, in handle
    return self.handle_noargs(**options)
  File "/Users/jsdalton/webs/testproject/src/django/django/core/management/commands/syncdb.py", line 121, in handle_noargs
    custom_sql = custom_sql_for_model(model, self.style, connection)
  File "/Users/jsdalton/webs/testproject/src/django/django/core/management/sql.py", line 150, in custom_sql_for_model
    app_dir = os.path.normpath(os.path.join(os.path.dirname(models.get_app(model._meta.app_label).__file__), 'sql'))
  File "/Users/jsdalton/webs/testproject/src/django/django/db/models/loading.py", line 151, in get_app
    raise ImproperlyConfigured("App with label %s could not be found" % app_label)
ImproperlyConfigured: App with label unmanaged_models could not be found

----------------------------------------------------------------------
Ran 4 tests in 2.080s

FAILED (errors=1)
Destroying test database for alias 'default'...
Destroying test database for alias 'other'...

In brief, the syncdb command is hiccoughing in the proxy_model_inheritance test because the previously installed model Intermediate (from unmanaged_model) is not getting filtered properly. The reason it is not getting filtered properly is that the db_table specified in the model definition is "D01" but the MySQL table name is "d01". In Postegres and other backends, the table name is correct as "D01".

I'm digging around trying to find where this is happening but I don't know this part of the code base very well. If anyone has a lead let me know and I'd be happy to put together a patch.

[Ramiro Morales]

I can't reproduce this on Linux.

This seem to be caused by a combination of a) the fact that the InnoDB MySQL storage engine force table names to lowercase when creating the corresponding files on disk and b) The fact that the default value for the lower_case_table_names MySQL setting is 2 under Mac OS X.

The kind of errors we did'n notice among the vast amount of errors and failures we had when running the tets suite innodb before r16590.

See ​http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html and ​http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_lower_case_table_names

Maybe we can workaround this by simply changing the 'db_table' values used in unmanaged_models models to lowercase.

[Jim Dalton]

Here's an output of the tables that are created when I run those tests:

[u'A01', u'B01', u'C01', u'auth_group', u'auth_group_permissions', u'auth_permission', u'auth_user', u'auth_user_groups', u'auth_user_user_permissions', u'd01', u'django_admin_log', u'django_comment_flags', u'django_comments', u'django_content_type', u'django_flatpage', u'django_flatpage_sites', u'django_redirect', u'django_session', u'django_site', u'unmanaged_models_managed1', u'unmanaged_models_managed1_mm', u'unmanaged_models_proxy1', u'unmanaged_models_proxy2']

(Just added a print tables in the syncdb command to get that list.)

The fact that A01 etc are right and d01 is wrong is what's making me think it's a bit different from what you brought up? Or rather, it wouldn't surprise me if the lower casing behavior is what's causing this, but it's probably a (minor) but if it's ignoring the value in db_table. Ideally it should be consistent I should think.

Well anyhow, I'll take a look and see if I can make any progress later.

[Jim Dalton]

Okay, there are two things at work here. I believe it is likely they are only an issue if you have lower_case_table_names in MySQL set to 2.

The problem is:

• When you execute a CREATE INDEX command, it changes the table name to lower case. For real. It's a known bug: ​http://bugs.mysql.com/bug.php?id=48875 . Given that its' been open since 2009 it doesn't look like they are in any hurry to fix it.
• When you execute an ALTER TABLE ... AUTO_INCREMENT=1 statement, the table name is changed to whatever case you use in the statement. For real.

So what actually happens here, on my set up:

1. All those tables from unmanaged models get created as UPPER CASE.
2. When the CREATE INDEX command is run on each of them in the latter part of syncdb, they get converted to lower case.
3. During sqlflush The AUTO INCREMENT reset is applied only to a01, b01, and c01, since d01 is an exception (an m2m using an intermediate table).

I don't know if the ALTER TABLE behavior is a bug or not (seems like one) but in either case there's a lot of table name changing going on, and in a sense it's just dumb luck that it works right most of the time.

I'll think about a solution.

[Jim Dalton]

Here's what I think.

I think the problematic behavior is due to the bugs and inconsistencies of MySQL. I don't think there is a straightforward solution to this that doesn't involve a lot of weird hacking around and exceptions.

First, to immediately fix the problem in this ticket, I think we should just specify the explicit db_table table names in the test cases in all lowercase.

Second, I think we should add a note in the documentation in which we mention that MySQL exhibits inconsistent table name case behaviors on different platforms, that we recommend you use all lower cased names when specifying db_table and that using upper case letters could create inconsistencies in certain edge cases.

Patch to address the first point is attached.

If we need to address this in documentation, I can whip up that patch as well.

[Jim Dalton]

I went ahead and added an admonition to use lowercase table names, as well as a few sentences noting the problematic behavior in MySQL when using mixed case table names. Patch attached.

[Adam Nelson]

Patch applies and test now passes on OS X Lion using mysql 5.5.14.

[Russell Keith-Magee]

In [16787]:

(The changeset message doesn't reference this ticket)