Opened 2 years ago

Closed 13 months ago

Last modified 8 months ago

#20292 closed Bug (fixed)

Django 1.5.1: ORA-01843: not a valid month

Reported by: CarstenF Owned by: nobody
Component: Database layer (models, ORM) Version: 1.5
Severity: Normal Keywords: Oracle, cx_oracle, unicode
Cc: shai@…, mcallister.sean@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.)

Attachments (1)

issue-20292.diff (3.1 KB) - added by jtiai 13 months ago.

Download all attachments as: .zip

Change History (27)

comment:1 Changed 2 years ago by jacob

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

comment:2 Changed 2 years ago by shai

  • Cc shai@… added

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

comment:3 Changed 23 months ago by 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;

comment:4 follow-up: Changed 22 months ago by mcallister.sean@…

  • Cc mcallister.sean@… added

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?

comment:5 in reply to: ↑ 4 ; follow-up: Changed 22 months ago by shai

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.

comment:6 Changed 22 months ago by akaariai

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?

comment:7 in reply to: ↑ 5 Changed 22 months ago by 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.

comment:8 Changed 22 months ago by 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

comment:9 Changed 22 months ago by 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.

comment:10 follow-up: Changed 22 months ago by 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.

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;

comment:11 in reply to: ↑ 10 ; follow-up: Changed 22 months ago by jtiai

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.

comment:12 in reply to: ↑ 11 Changed 22 months ago by 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';

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

comment:13 Changed 21 months ago by jtiai

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

comment:14 Changed 21 months ago by 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?

comment:15 Changed 20 months ago by shai

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.

Last edited 20 months ago by shai (previous) (diff)

comment:16 Changed 18 months ago by 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

comment:17 Changed 17 months ago by 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?

comment:18 Changed 17 months ago by shai

@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).

comment:19 Changed 17 months ago by 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 ''))

comment:20 Changed 13 months ago by jtiai

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.

comment:21 Changed 13 months ago by jtiai

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.

Changed 13 months ago by jtiai

comment:22 Changed 13 months ago by jtiai

Proposed patch against 1.5.x

issue-20292.diff

comment:23 Changed 13 months ago by Shai Berger <shai@…>

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

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.

comment:24 Changed 8 months ago by 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.

comment:25 Changed 8 months ago by Tim Graham <timograham@…>

In f294f93a1729bb87bedb8d79fdecf4197d0f9e5b:

Forwardported 1.6.6 release notes for refs #20292.

comment:26 Changed 8 months ago by Tim Graham <timograham@…>

In dcedc453a2e6446d052fc7a3d24fe190a607e958:

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

Backport of f294f93a17 from master

Note: See TracTickets for help on using tickets.
Back to Top