Opened 2 years ago

Closed 18 months ago

#20200 closed Bug (wontfix)

Oracle Inconsistencies with NVARCHAR2 and NCLOB

Reported by: deejross Owned by: nobody
Component: Database layer (models, ORM) Version: 1.5
Severity: Normal Keywords: oracle
Cc: deejross, shai@… Triage Stage: Unreviewed
Has patch: yes Needs documentation: yes
Needs tests: yes Patch needs improvement: no
Easy pickings: yes UI/UX: no

Description

We are currently working on moving our MySQL database over to an Oracle Exadata 11.2g cluster. We've hit a few surprises so far, such as the 30 character limit on table names, but this one I can't fix without making a code change to Django's Oracle backend. We are having two issues: the NVARCHAR2 fields cannot have their max_length argument set to 4000, per the documentation because it's max is calculated using the NLS_NCHAR_CHARACTERSET. In a shared environment, this cannot be changed without affecting other databases in the cluster.

The other issue is with NCLOB not accepting strings longer than 2000 characters. In my experiements, even though this is supposed to work, it doesn't unless you truncate the string first, save that to the database, then try and save the longer version again. Very strange behavior and I don't know if it's an Oracle bug or a cx_Oracle bug. I am using cx_Oracle 5.1.2 with Python 2.7 x86 on Windows 7 for the lab environment.

I have managed to solve these issues by editing the django/db/backends/oracle/creation.py file and swapping all instances of NVARCHAR2 with VARCHAR2 and NCLOB with CLOB. I've also noticed a lot of conversation about switching these and it was my understanding that the decision had already been made to correct this by using above recommendation. I have attached the current patch I am using which resolves all issues.

Attachments (1)

creation_fix.diff (2.0 KB) - added by deejross 2 years ago.
oracle creation.py diff

Download all attachments as: .zip

Change History (7)

Changed 2 years ago by deejross

oracle creation.py diff

comment:1 Changed 2 years ago by deejross

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

The second issue (with NCLOB was not resolved with CLOB) was not resolved by this patch. I have discovered some very strange behavior with that and have created a new ticket for that: #20201

The submitted patch is still valid though for the max_length issue.

comment:2 Changed 2 years ago by deejross

  • Cc deejross added

comment:3 Changed 2 years ago by shai

  • Cc shai@… added
  • Needs documentation set
  • Needs tests set

Hi,

References to the conversations about switching nvarchar2 to varchar2 may be nice here. I haven't seen any such conversations lately, and I don't think I'd like Django deciding that it's ok to have strings that don't fit into fields (when the database CHARSET is not unicode).

W.r.t the patch: Especially given the claim that you are not doing an enhancement, but fixing a bug, please add a test that fails without your fix, and passes with it (I am not a core committer, and as I said, I am against your fix, but without tests and a documentation note about the change, the patch shouldn't even be considered by core).

comment:4 Changed 2 years ago by timo

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

Closing as "won't fix" given shai's objections and lack of follow-up from OP.

comment:5 Changed 18 months ago by deejross

  • Resolution wontfix deleted
  • Status changed from closed to new

I never got any notifications on responses from this, so I'm sorry for not getting back to you. Here's the perfect reason why N-type fields should not be used with Oracle and Django: http://stackoverflow.com/questions/18978536/poor-performance-of-django-orm-with-oracle

We are at the tail end of the conversion process from MySQL or Oracle, and I had noticed a severe performance problem with Oracle. It turns out there are cases where Oracle's implicit type conversion rules prevent indexes from being used sometimes. This means full table scans, regardless of your indexes. The only workaround is to use cursor.execute() or to create a C2C index on every field, neither of which are suitable options.

That question also refers to these threads on the subject:
http://comments.gmane.org/gmane.comp.python.db.cx-oracle/3049
http://comments.gmane.org/gmane.comp.python.db.cx-oracle/2940

Version 0, edited 18 months ago by deejross (next)

comment:6 Changed 18 months ago by deejross

  • Resolution set to wontfix
  • Status changed from new to closed
Note: See TracTickets for help on using tickets.
Back to Top