MySQL case sensitivity

[tcp]

Hi,

Getting started.

I've got a MySQL database running on Windows (MySQL on Windows is not
case sensitive)

I create a model called People which has a Class called Person.

I sync the database and it creates a table "people_person"

If I immediately run syncdb again, it errors out with:

Creating table People_person
Traceback (most recent call last):
File "manage.py", line 11, in <module>
.
.
.
raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1050, "Table 'people_person'
already exists
")


Notice that it doesn't think that People_person exists because it is
using a case sensitive search in Python and then it attempts to create
the table which FAILS in the case insensitive Windows MySQL.

I'm looking at a book that advises setting uses_case_insensitive_name
= True in the django/db/backends/__init__py file....but this doesn't
seem to work.

This seems like a bug to me in the MySQL implementation. Can someone
suggest the best way to work around this problem or to set the db case
insensitivity....

Thanks
Tom

[Juan Hernandez]

I've got a MySQL database running on Windows (MySQL on Windows is not
case sensitive)

That does not depends on the OS, depends on the Charset established in the DB. Check the charset being used at the DB and then run syncdb again

[Karen Tracey]

On Mon, Sep 22, 2008 at 2:29 PM, tcp <thomas....@gmail.com> wrote:

Hi,

Getting started.

I've got a MySQL database running on Windows (MySQL on Windows is not
case sensitive)

I create a model called People which has a Class called Person.

Your terminology is confusing here. I think you are saying you have a Django application (i.e. directory containing models.py, etc.) named 'People' with a Django model named Person.  It is a little odd to have an uppercase letter in your directory name, and that is what is causing the problem.
 

I sync the database and it creates a table "people_person"

Django actually creates a table named 'People_person'.  Django lowercases the part of the name that comes from your model's class name, but not the part that comes from your application directory name.  The lowercasing of 'people' is coming from MySQL, as described here:

http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

assuming you are using the default setting of 1 for lower_case_table_names on Windows.

If I immediately run syncdb again, it errors out with:

Creating table People_person
Traceback (most recent call last):
 File "manage.py", line 11, in <module>
.
.
.
   raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1050, "Table 'people_person'
already exists
")


Notice that it doesn't think that People_person exists because it is
using a case sensitive search in Python and then it attempts to create
the table which FAILS in the case insensitive Windows MySQL.

On a syncdb, Django retrieves the existing table names from the DB.  It gets back 'people_person', which does not match the table it wants to create, which is 'People_person', so it tries to create 'People_person'.  However MySQL lowercases the entire thing and now it matches an exising table so the create fails. 

I'm looking at a book that advises setting uses_case_insensitive_name
= True in the django/db/backends/__init__py file....but this doesn't
seem to work.

I have no idea what book you are referring to here nor what that setting is supposed to affect.  However, if a book recommends changing the Django source to fix a problem like this I'd be a bit leery of following any of its advice.  In general Django accomodates various databases and platforms without requiring any fiddling with the source.  Unless you are sure you understand all the issues and have come to the conclusion through your own research that for some reason Django out of the box doesn't work properly for your environment (in which ase I'd hope you'd bring it up as an issue to be addressed by the Django developers), it's a bad idea to be changing things in the Django code.

This seems like a bug to me in the MySQL implementation. Can someone
suggest the best way to work around this problem or to set the db case
insensitivity....

You could --

1. set lower_case_table_names to 0 on your MySQL implementation.  That way MySQL won't gratuitiously lowercase the names that Django passes to it.  If you are concerned about the scary-sounding warning about corrupt indexes that can result from this being set to 0 if you then also access the tables with a different lettercase, you could instead:

2 - specify an all-lowercase db_table name in the Meta class of your Person model.  See the docs here: http://docs.djangoproject.com/en/dev/ref/models/options/#db-table.  Or:

3 - avoid using uppercase letters in your Djano application names.

Karen

[tcp]

Karen,

Thank you for the detailed answer. Currently I am following the Sams
Django in 24 Hours book. The example there gives an uppercase folder
name.

From your response I can see that it would be good practice to stick
to lowercase folders (I will follow that path).

I found that support uppercase folder names could be supported with
MySQL on Windows by switching MySQL to use lower_case_table_names = 0
(like Unix default) in my.ini. I agree with you that changing the
Django core code doesn't seem to be a good idea and appreciate your
advise.

Best Regards,
Tom

On Sep 22, 3:39 pm, "Karen Tracey" <kmtra...@gmail.com> wrote: