#20292 closed Bug (fixed)
Django 1.5.1: ORA-01843: not a valid month
Reported by: | Carsten Fuchs | 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)
Change History (27)
comment:1 by , 11 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:2 by , 11 years ago
Cc: | added |
---|
comment:3 by , 11 years ago
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;
follow-up: 5 comment:4 by , 11 years ago
Cc: | 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?
follow-up: 7 comment:5 by , 11 years ago
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 by , 11 years ago
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 by , 11 years ago
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 by , 11 years ago
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 by , 11 years ago
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.
follow-up: 11 comment:10 by , 11 years ago
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;
follow-up: 12 comment:11 by , 11 years ago
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 by , 11 years ago
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:
- 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';
- Change the SQL statement to use cast to VARCHAR2 instead of NVARCHAR2
comment:13 by , 11 years ago
Today (June 27th) Oracle Support has submitted issue to development team and Oracle Development is now working on an issue.
comment:14 by , 11 years ago
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 by , 11 years ago
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.
comment:16 by , 11 years ago
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 by , 11 years ago
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 by , 11 years ago
@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 by , 11 years ago
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 by , 11 years ago
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 by , 11 years ago
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.
by , 11 years ago
Attachment: | issue-20292.diff added |
---|
comment:23 by , 11 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
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