Django 1.5.1: ORA-01843: not a valid month

[Carsten Fuchs]

With

• Django 1.5.1
• cx_Oracle-5.1.2

we're seeing error "ORA-01843: not a valid month" when saving data.
This problem does not occur with Django 1.4.x or cx_Oracle 5.0.3!

I originally reported this problem on django-users:
​https://groups.google.com/d/msg/django-users/jeUCYd1lYkE/sJUQ7Z5oZnAJ

A similar report is at:
​http://stackoverflow.com/questions/15525312/django-oracle-backend-error

---

This is the model definition that we're seeing the problem with:

class Erfasst(models.Model):
    id            = models.AutoField(primary_key=True)
    key           = models.ForeignKey(Mitarbeiter, db_column='key', to_field='key', on_delete=models.PROTECT)

    datum         = models.DateField()
    status        = models.ForeignKey(Status, db_column='status', on_delete=models.PROTECT)

    anfang        = models.CharField(max_length=8, blank=True)
    ende          = models.CharField(max_length=8, blank=True)
    pause         = models.CharField(max_length=8, blank=True)
    faktor        = models.SmallIntegerField(choices=[(60, "1.0"), (90, "1.5"), (120, "2.0")], default=60)

    bonus_url     = models.DecimalField(max_digits=5, decimal_places=2, null=True, blank=True, verbose_name=u'Bonus-Urlaub')
    bemerkung     = models.CharField(max_length=40, blank=True)
    bereich       = models.ForeignKey(Bereich, null=True, blank=True, on_delete=models.PROTECT)
    zustand       = models.SmallIntegerField(db_column='flag')

    approved_by   = models.ForeignKey(User, null=True, blank=True, on_delete=models.PROTECT, related_name='approved_set')
    approved_at   = models.DateTimeField(null=True, blank=True)

    last_user     = models.ForeignKey(User, null=True, blank=True, on_delete=models.PROTECT)
    last_modified = models.DateTimeField(auto_now=True, null=True)

And this is an example from ./manage.py shell that reproduces the error:

>>> from Lori.models import *
>>> st = Status.objects.get(id=1)
>>> ma = Mitarbeiter.objects.get(key="F200")
>>> e = Erfasst(key=ma, datum=date(2013, 12, 12), status=st, bemerkung="Test CF", zustand=1)
>>> e.save()
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/local/lib/python2.6/dist-packages/django/db/models/base.py", line 546, in save
    force_update=force_update, update_fields=update_fields)
  File "/usr/local/lib/python2.6/dist-packages/django/db/models/base.py", line 650, in save_base
    result = manager._insert([self], fields=fields, return_id=update_pk, using=using, raw=raw)
  File "/usr/local/lib/python2.6/dist-packages/django/db/models/manager.py", line 215, in _insert
    return insert_query(self.model, objs, fields, **kwargs)
  File "/usr/local/lib/python2.6/dist-packages/django/db/models/query.py", line 1661, in insert_query
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/usr/local/lib/python2.6/dist-packages/django/db/models/sql/compiler.py", line 937, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python2.6/dist-packages/django/db/backends/oracle/base.py", line 717, in execute
    six.reraise(utils.DatabaseError, utils.DatabaseError(*tuple(e.args)), sys.exc_info()[2])
  File "/usr/local/lib/python2.6/dist-packages/django/db/backends/oracle/base.py", line 710, in execute
    return self.cursor.execute(query, self._param_generator(params))
DatabaseError: ORA-01843: not a valid month

(As a side note, from our live site I didn't receive any of the usual error e-mails that normally are reliably sent whenever an application error occurs.)

[Shai Berger]

Note: All the reports seem to mention Oracle 10; on a report of the same issue referenced from the cited SO post, someone even explicitly mentions not being able to reproduce against Oracle 11.
​http://stackoverflow.com/questions/15396241/cx-oracle-ora-01843-not-a-valid-month-with-unicode-parameter

[anonymous]

It might be actually some problem in Oracle 10g

-- NVARCHAR data type, FAILS in 10g, works in 11g
create table TMP_TBL_NVARCHAR2(MY_DATE NVARCHAR2(256), MY_STAMP NVARCHAR2(256));
insert into TMP_TBL_NVARCHAR2 (MY_DATE,MY_STAMP) values ('2013-03-12','2013-03-12 08:22:31.332144');
commit;

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
select to_date(MY_DATE), to_timestamp(MY_STAMP) from TMP_TBL_NVARCHAR2;

ERROR at line 1:
ORA-01843: not a valid month

drop table TMP_TBL_NVARCHAR2;

-- VARCHAR data type, WORKS in 10g and 11g
create table TMP_TBL_VARCHAR2(MY_DATE VARCHAR2(256), MY_STAMP VARCHAR2(256));
insert into TMP_TBL_VARCHAR2 (MY_DATE,MY_STAMP) values ('2013-03-12','2013-03-12 08:22:31.332144');
commit;

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
select to_date(MY_DATE), to_timestamp(MY_STAMP) from TMP_TBL_VARCHAR2;

TO_DATE(MY_DATE)
-------------------
TO_TIMESTAMP(MY_STAMP)
---------------------------------------------------------------------------
2013-03-12 00:00:00
2013-03-12 08:22:31.332144000



drop table TMP_TBL_VARCHAR2;

[mcallister.sean@…]

The breaking change for me is the fix to ticket #19606
The detection of unicode capability of the backend is somewhat broken, and if I force usage of

convert_unicode = force_bytes 

around line 70 in db/backends/oracle/base.py the errors seems to go away.

I can reproduce the problem using this model:

from django.db import models

class TS(models.Model):

    ts = models.DateTimeField()
    d = models.DateField()

And then creating some instances:

from .models import TS
from datetime import datetime

n = datetime.now()
d= n.date()

TS(ts=n, d=d).save()
TS(ts=n, d=d).save()

The above snippets works on django 1.5.1 and cx_Oracle 5.0.4, but breaks when upgrading to to 5.1.2 (I don't think Instant client version plays into this, I compiled cx oracle against 11.2 and 10.2.0.5, same behaviour).

The first save actually works, but the the second raises the invalid month error.
This behaviour seems similar to this SO question: ​http://stackoverflow.com/questions/15396241/cx-oracle-ora-01843-not-a-valid-month-with-unicode-parameter

Perhaps #19606 needs re-opening?

[Shai Berger]

Replying to mcallister.sean@…:

The above snippets works on django 1.5.1 and cx_Oracle 5.0.4, but breaks when upgrading to to 5.1.2 (I don't think Instant client version plays into this, I compiled cx oracle against 11.2 and 10.2.0.5, same behaviour).

The first save actually works, but the the second raises the invalid month error.
This behaviour seems similar to this SO question: ​http://stackoverflow.com/questions/15396241/cx-oracle-ora-01843-not-a-valid-month-with-unicode-parameter

Important point missing is your Oracle server version -- as I've mentioned when I linked this SO question, the person who answered it specifically mentioned not being able to reproduce the problem against Oracle 11.

[Anssi Kääriäinen]

I am willing to help in any way I can to fix this issue (I am the one who committed the breaking commit after all), but the problem is that all combinations of Oracle + cx_oracle I have work correctly. Reverting the patch isn't good either, as that will break some other combinations. So, we need exact conditions where this bug happens, otherwise we can't fix this.

I wonder if this should be marked as a release blocker for 1.5.2?

[mcallister.sean@…]

Replying to shai:

Important point missing is your Oracle server version -- as I've mentioned when I linked this SO question, the person who answered it specifically mentioned not being able to reproduce the problem against Oracle 11.

I am connecting to an Oracle 11 Server, I need to check the exact release tomorrow and will post them here.
Since this doesn't seem to be a very common problem it might be something special with our setup.

[anonymous]

This is the exact server version as shown when connected with sqlplus64 (from instant client 11.2.0.3.0)

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

[mal]

I confirm that the error with Django 1.5.1, Oracle Database 10g Release 10.2.0.5.0 - 64bit Production and cx_Oracle 5.1.2.
Same combination works fine with 11g.

Oracle 10g is still very popular and sometimes its just not possible to upgrade to 11g.

[Jani Tiainen]

This seems to be bug in Oracle 10g.

Explanation:

Any unicode (NVARCHAR2) to DATE datatype conversion seems to trigger something inside Oracle that causes session nls parameters to break.
Following unicode (NVARCHAR2) to TIMESTAMP datatype conversion fails which in turn causes all non-unicode to TIMESTAMP datatype conversions fail as well.

Here folloows solid test case that fill work exactly as comments do state:

ALTER SESSION SET NLS_TERRITORY = 'AMERICA';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';

REM --- WORKS:
SELECT TO_TIMESTAMP('2013-06-24 18:15:10.312') FROM DUAL;

REM --- WORKS:
SELECT TO_DATE('2013-06-24 18:15:10') FROM DUAL;

REM --- WORKS:
SELECT TO_TIMESTAMP('2013-06-24 18:15:10.312') FROM DUAL;

REM --- WORKS:
SELECT TO_TIMESTAMP(x) FROM (SELECT CAST('2013-06-24 18:15:10.312' AS NVARCHAR2(30)) AS X FROM DUAL);

REM --- WORKS:
SELECT TO_DATE(x) FROM (SELECT CAST('2013-06-24 18:15:10' AS NVARCHAR2(30)) AS X FROM DUAL);

REM --- WORKS:
SELECT TO_TIMESTAMP('2013-06-24 18:15:10.312') FROM DUAL;

REM !!! FAILS!
SELECT TO_TIMESTAMP(x) FROM (SELECT CAST('2013-06-24 18:15:10.312' AS NVARCHAR2(30)) AS X FROM DUAL);

REM !!! FAILS!
SELECT TO_TIMESTAMP('2013-06-24 18:15:10.312') FROM DUAL;

REM --- WORKS:
SELECT TO_DATE('2013-06-24 18:15:10') FROM DUAL;

[Jani Tiainen]

Replying to jtiai:

This seems to be bug in Oracle 10g.

Explanation:

Any unicode (NVARCHAR2) to DATE datatype conversion seems to trigger something inside Oracle that causes session nls parameters to break.
Following unicode (NVARCHAR2) to TIMESTAMP datatype conversion fails which in turn causes all non-unicode to TIMESTAMP datatype conversions fail as well.

Update:
This has been reported to Oracle and current response is that this affects 11.1.x and 10.2.0.5 versions.

[mcallister.sean@…]

Replying to jtiai:

Update:
This has been reported to Oracle and current response is that this affects 11.1.x and 10.2.0.5 versions.

I don't think there is much that django can do to work around this, so I will just keep patching the base.py and hope that sooner or later we will be able to upgrade.
FYI our DB Admin also filed a ticket and we got the following answer:

(..) this is an 11.1.0.7 bug that is solved by upgrading to 11.2.0.2 or higher. If you can't upgrade then please use one of the following > workarounds:

1. run the following command every time you use the to_date function

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';

2. Change the SQL statement to use cast to VARCHAR2 instead of NVARCHAR2

[Jani Tiainen]

Today (June 27th) Oracle Support has submitted issue to development team and Oracle Development is now working on an issue.

[mal]

The query like

SELECT TO_DATE(TO_CHAR(x)) FROM (SELECT CAST('2013-06-24 18:15:10' AS NVARCHAR2(30)) AS X FROM DUAL);

seems to work.

Would it be possible to use to_date(to_char(to_nchar('date string'))) as a workaround in Django ORM?

[Shai Berger]

I suspect this and #20785 are both related to the Oracle Unicode bug.

If this is indeed the case, applying this patch should probably fix both problems; I have no way to test this myself, so I'm asking you guys to test. I only tested on master against Oracle 11, to see on a preliminary level that it doesn't break things; if it helps, I'll test it more thoroughly (but still, only against Oracle 11; that's what I have).

Hope this helps,
Shai.

[anonymous]

Thanks for posting the patch. I've tested it on several systems, but unfortunately, it doesn't seem to make a difference: also with the patch applied, we observe the same problem as originally reported.

Best regards,
Carsten

[robert.lujo@…]

I experienced the same problem and it seems that this is Oracle bug (I experienced the same behaviour as described here ​http://stackoverflow.com/a/17269719/565525). The only thing I can think of is patching django/oracle/base.py:

• before calling any problematic sql-s (e.g. oracle 10.5.0.2 and 11.2.0.1, cx_oracle 5.1.2), reset NLS_DATE_FORMAT/NLS_TIMESTAMP_FORMAT again - done in django/db/backends/oracle/base.py in method def execute(...):

Temp. patch here: ​http://stackoverflow.com/a/19705772/565525. Any better solution?

[Shai Berger]

@robert.lujo -- this is a promising direction, but I think adding an extra query on every single query will cause unacceptable performance degradation.

I will accept it if is shown not to have such effects; I am more likely to accept a patch which limits the effect to susceptible Oracle versions (Note that the backend already checks the Oracle version on the first connection opened, because it needs to do things differently on Oracle 9), and/or only resets the format parameters once a problem is discovered (that is, checks for ORA-01843, like it currently does for ORA-01400, and if encountered, reset the format and try the original query again).

[mal]

I tried the patch in our environment and now we get "ORA-01036: illegal variable name/number" once in a while in that new part:

            self.cursor.execute(
                "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'"
                " NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'"
                + (" TIME_ZONE = 'UTC'" if settings.USE_TZ else ''))

[Jani Tiainen]

Just an observation note: provided patch only works if you have single date/timestamp field on a model. Having a multiple date/timestamp fields will cause "not a valid month"-error and thus rendering using patch unusable in a such cases.

[Jani Tiainen]

Accidentally we do use use custom DateTime field for Oracle and we had never problems with that.

Now looking closely our magical field it actually passes real datetime objects for cx_Oracle (cx_Oracle knowns how to do conversions internally).

My suggestion is to change Oracle backend hooks for value_to_db_date, value_to_db_time and value_to_db_datetime to return real datetime/date/time objects for Oracle.

See ​http://www.oracle.com/technetwork/articles/dsl/prez-python-timesanddates-093014.html for more information about conversions.

[Jani Tiainen]

Proposed patch against 1.5.x

issue-20292.diff​

[Shai Berger <shai@…>]

In 6983201cfb172c2895ed6eea1e0f0eb3804d4c00:

Fixed #20292: Pass datetime objects (not formatted dates) as params to Oracle

This seems worthwhile in its own right, but also works around an Oracle
bug (in versions 10 -- 11.1) where the use of Unicode would reset the
date/time formats, causing ORA-01843 errors.

Thanks Trac users CarstenF for the report, jtiai for the initial patch,
and everyone who contributed to the discussion on the ticket.

[Tim Graham <timograham@…>]

In 838b7f8220d4ac2af6e39e2b5a3c1a7b95131083:

[1.6.x] Fixed #20292: Pass datetime objects (not formatted dates) as params to Oracle

This seems worthwhile in its own right, but also works around an Oracle
bug (in versions 10 -- 11.1) where the use of Unicode would reset the
date/time formats, causing ORA-01843 errors.

Thanks Trac users CarstenF for the report, jtiai for the initial patch,
and everyone who contributed to the discussion on the ticket.

Backport of 6983201 from master.

[Tim Graham <timograham@…>]

In f294f93a1729bb87bedb8d79fdecf4197d0f9e5b:

Forwardported 1.6.6 release notes for refs #20292.

[Tim Graham <timograham@…>]

In dcedc453a2e6446d052fc7a3d24fe190a607e958:

[1.7.x] Forwardported 1.6.6 release notes for refs #20292.

Backport of f294f93a17 from master