[Django] #27321: ignores_quoted_identifier_case broken on MySQL on OS X/Windows
Django
----------------------------------------------+----------------------------
Reporter: Adam Chainz | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.10
Severity: Normal | Keywords: mysql, mariadb
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+----------------------------
#26781 added `test_alter_db_table_case` which presumes that table names
are case sensitive. MySQL and MariaDB rely on the underlying filesystem to
provide case sensitivity, as well as a setting, as per
https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html
and https://mariadb.com/kb/en/mariadb/identifier-case-sensitivity/ . Thus
it fails on OS X where the default filesystem is case insensitive, with:
{{{
django.db.utils.InternalError: (1050, "Table 'SCHEMA_AUTHOR' already
exists")
}}}
I can't find any good information on whether PostgreSQL depends on
filesystem case sensitive or not. All the results I found in five minutes
of Googling seem to relate to whether it's syntactically case sensitive,
and suggest it lowercases all table names in queries unless they are
double-quoted, which would make renaming a table to uppercase a no-op.
This would mean `ignores_quoted_identifier_case` should be `True` for
MySQL with its data directory mounted on a case insensitive filesystem.
The only way to reliably check this would be to try create two tables with
names that differ only in case and seeing if the second one failed..
--
Ticket URL: <https://code.djangoproject.com/ticket/27321>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: Adam Chainz | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql, mariadb | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Adam Chainz):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
> #26781 added `test_alter_db_table_case` which presumes that table names
> are case sensitive. MySQL and MariaDB rely on the underlying filesystem
> to provide case sensitivity, as well as a setting, as per
> https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html
> and https://mariadb.com/kb/en/mariadb/identifier-case-sensitivity/ . Thus
> it fails on OS X where the default filesystem is case insensitive, with:
>
> {{{
> django.db.utils.InternalError: (1050, "Table 'SCHEMA_AUTHOR' already
> exists")
> }}}
>
> I can't find any good information on whether PostgreSQL depends on
> filesystem case sensitive or not. All the results I found in five minutes
> of Googling seem to relate to whether it's syntactically case sensitive,
> and suggest it lowercases all table names in queries unless they are
> double-quoted, which would make renaming a table to uppercase a no-op.
>
> This would mean `ignores_quoted_identifier_case` should be `True` for
> MySQL with its data directory mounted on a case insensitive filesystem.
> The only way to reliably check this would be to try create two tables
> with names that differ only in case and seeing if the second one failed..
New description:
#26781 added the `ignores_quoted_identifier_case` database feature, which
defaults to `False`, so it's `False` for the MySQL backend. Unfortunately
this can't be determined without reference to the filesystem that the
tables are stored on - MySQL and MariaDB rely on the underlying filesystem
to provide case sensitivity, as well as a setting, as per
https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html
and https://mariadb.com/kb/en/mariadb/identifier-case-sensitivity/ .
Thus the added test, `test_alter_db_table_case`, fails on OS X where the
default filesystem is case insensitive, with:
{{{
django.db.utils.InternalError: (1050, "Table 'SCHEMA_AUTHOR' already
exists")
}}}
I can't find any good information on whether PostgreSQL depends on
filesystem case sensitive or not. All the results I found in five minutes
of Googling seem to relate to whether it's syntactically case sensitive,
and suggest it lowercases all table names in queries unless they are
double-quoted, which would make renaming a table to uppercase a no-op
anyway?
`ignores_quoted_identifier_case` should be `True` for MySQL when its data
directory is mounted on a case insensitive filesystem. The only way to
reliably check this that I can think of would be to try create two tables
with names that differ only in case and seeing if the second one failed,
which is kind of nasty.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/27321#comment:1>
Django
Reporter: Adam Chainz | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Claude Paroz):
* stage: Unreviewed => Accepted
Comment:
... or we could be a little bit less reliable and test the tmp file system
with [http://stackoverflow.com/questions/7870041/check-if-file-system-is-
case-insensitive-in-python/36580834 some trick like that].
--
Ticket URL: <https://code.djangoproject.com/ticket/27321#comment:2>
Django
Reporter: Adam Chainz | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql, mariadb | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Adam Chainz):
That's making the assumption that MySQL is running on the same computer as
Django, which might not be the case...
--
Ticket URL: <https://code.djangoproject.com/ticket/27321#comment:3>
Django
Reporter: Adam Chainz | Owner: Adam
| Chainz
Type: Bug | Status: assigned
Component: Database layer | Version: 1.10
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Adam Chainz):
* status: new => assigned
* cc: me@… (added)
* has_patch: 0 => 1
* owner: nobody => Adam Chainz
--
Ticket URL: <https://code.djangoproject.com/ticket/27321#comment:4>
Django
Reporter: Adam Chainz | Owner: Adam
| Chainz
Type: Bug | Status: assigned
Component: Database layer | Version: 1.10
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/27321#comment:5>
Django
Reporter: Adam Chainz | Owner: Adam
| Chainz
Component: Database layer | Version: 1.10
(models, ORM) |
Keywords: mysql, mariadb | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham <timograham@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"95238a7de867e2a23a9247e3d6d8f654f4cee908" 95238a7d]:
{{{
#!CommitTicketReference repository=""
revision="95238a7de867e2a23a9247e3d6d8f654f4cee908"
Fixed #27321 -- Added detection for table case name sensitivity on MySQL.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27321#comment:6>