Date and Timestamp formats for Oracle Backend incorrect with Oracle RAC 11g R2

[josh.smeaton@…]

When trying to do a datetime range query, I began receiving the below error after migrating to Oracle RAC 11g R2

    DatabaseError at /url/path/
    ORA-12801: error signaled in parallel query server P026, instance [domain]:[Instance] (2)
    ORA-01861: literal does not match format string

We determined that this was due to incorrect NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT, as manually setting those session parameters in sqlplus and executing the same query was working correctly. We found the below code which seemed to be doing the right thing, as querying the NLS_SESSION_PARAMETERS was returning the correct information.

            # Set oracle date to ansi date format.  This only needs to execute
            # once when we create a new connection. We also set the Territory
            # to 'AMERICA' which forces Sunday to evaluate to a '1' in TO_CHAR().
            cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'"
                           " NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'"
                           " NLS_TERRITORY = 'AMERICA'"
                           + (" TIME_ZONE = 'UTC'" if settings.USE_TZ else ''))

Oracle documentation ​here mentions that setting the NLS_TERRITORY parameter will set a number of parameters (including NLS_TIMESTAMP_FORMAT and NLS_DATE_FORMAT) to the territory default. The above SQL that alters the session information has undefined (I couldn't find it if it exists) behaviour for changing the territory and format parameters in a single statement.

The territory can be modified dynamically during the session by specifying the new NLS_TERRITORY value in an ALTER SESSION statement. 
Modifying NLS_TERRITORY resets all derived NLS session parameters to default values for the new territory.

The solution is to set the territory first and, as a separate statement set, the time/date formats to ensure the territory defaults are overridden correctly.

I will update this ticket with a link to the change in git.

This problem also affects at least version Django 1.2 >

[anonymous]

This is the appropriate Pull Request: ​https://github.com/django/django/pull/149

[Anssi Kääriäinen]

Marking accepted as the above description seems well-researched. I haven't tried to reproduce this myself.

This can be patched in 1.4 and trunk. 1.3 is in security fixes only mode. 1.4 is actually questionable, the list of things to fix is:

    Security issues.
    Data-loss bugs.
    Crashing bugs.
    Major functionality bugs in newly-introduced features.

It is not entirely clear to me if this falls into any of those categories (FWIW I think patching this should be done - this seems like a low-risk fix).

A test case for this would be great. If you can add it to tests/regressionstests/backends/tests.py in the pull request it will reduce the amount of work needed on my end, but attaching a breaking query here in a comment will be enough.

[Josh Smeaton <josh.smeaton@…>]

This definitely fits under the category of "Crashing bugs" as it completely crashes any view that does any date querying. I will work on getting a regression test into the pull request in a little bit, and will update here once that's done. Thanks for the prompt review.

[Josh Smeaton <josh.smeaton@…>]

Test added to the pull request - hopefully the test is sufficient.

[Anssi Kääriäinen]

I agree that we should fix this both in 1.4 and trunk. I will try to find time to get this committed.

FWIW the test actually tests a bit too much IMHO - it is enough to just test that the query works - there is no need to test that the query doesn't work under different connection settings. No need to change that test though, it is useful for testing the test :)

[Anssi Kääriäinen <akaariai@…>]

In [fa182e8ae82f33764d5e1f70bcd45899e1bf17e6]:

Fixed #18465 -- Set date formats correctly on Oracle

Correctly configure NLS_SESSION_PARAMETERS to format Date and DateTime
on Oracle backend.

Thanks to Josh Smeaton for report & patch.

[Anssi Kääriäinen]

I will wait with the backporting, I couldn't reproduce the error on Oracle XE 11g here on my machine. I applied the patch as there doesn't seem to be any documentation what is supposed to happen when NLS_TERRITORY is set together with something it is supposed to override, and there is at least one report of this on Oracle 11g RAC. The cost is one more query on connection setup.

If it turns out there are more users possibly hit by this, and that this doesn't cause any problems for current users, then I will backport. (Sorry if this is overcautious, I am still not comfortable deciding what should be backported, what not).

[Josh Smeaton <josh.smeaton@…>]

I think that's the right choice. If other users become affected later, at least the patch/information is here for them to help diagnose and re-raise the possibility of a backport. Thanks for getting this in so quickly Akaariai.