Code

Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#16592 closed Bug (fixed)

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

Reported by: jsdalton Owned by: nobody
Component: Testing framework Version: master
Severity: Normal Keywords: mysql
Cc: jsdalton Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Attachments (2)

fix_unmanaged_models_tests.v1.diff (1.9 KB) - added by jsdalton 3 years ago.
fix_unmanaged_models_tests.v2.diff (3.5 KB) - added by jsdalton 3 years ago.

Download all attachments as: .zip

Change History (9)

comment:1 Changed 3 years ago by ramiro

  • Component changed from Database layer (models, ORM) to Testing framework
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

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.

comment:2 Changed 3 years ago by jsdalton

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.

comment:3 Changed 3 years ago by jsdalton

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.

comment:4 Changed 3 years ago by jsdalton

  • Has patch set

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.

Changed 3 years ago by jsdalton

comment:5 Changed 3 years ago by jsdalton

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.

Changed 3 years ago by jsdalton

comment:6 Changed 3 years ago by adamnelson

  • Triage Stage changed from Accepted to Ready for checkin

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

comment:7 Changed 3 years ago by russellm

  • Resolution set to fixed
  • Status changed from new to closed

In [16787]:

(The changeset message doesn't reference this ticket)

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


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

 
Note: See TracTickets for help on using tickets.