Opened 2 years ago

Closed 2 years ago

Last modified 2 years ago

#20785 closed Bug (fixed)

[oracle] ORA-01425: escape character must be character string of length 1

Reported by: ludo Owned by: shai
Component: Database layer (models, ORM) Version: 1.6-beta-1
Severity: Release blocker Keywords:
Cc: shai@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Upgrading from 1.5 to 1.6 beta this error, which was fixed in #14149 crops up again. The simple fix is to add DatabaseError to the try/except block that wraps the query to test for Oracle operators when creating the connection. I'm on Python 2.7.3 cxOracle 5.1.2.

Attachments (2)

db_backends_oracle_base.diff (669 bytes) - added by ludo 2 years ago.
diff against trunk
oracle-10.patch (1010 bytes) - added by shai 2 years ago.
Make Oracle backend not use unicode literals to work around Oracle 10 bug

Download all attachments as: .zip

Change History (16)

Changed 2 years ago by ludo

diff against trunk

comment:1 Changed 2 years ago by aaugustin

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Severity changed from Normal to Release blocker

Marking as a release blocker since the report says it's a regression.

comment:2 Changed 2 years ago by shai

What Oracle version are you using?

(obviously, starting connections on Oracle works for most of us -- further, master is continuously tested against Oracle, and ATM the Oracle backend is essentially the same in master and 1.6b1).

comment:3 Changed 2 years ago by shai

  • Cc shai@… added

comment:4 Changed 2 years ago by anonymous

Shai, of course I imagine I'm not the only one using Oracle, that's why the error threw me off guard. :)

Here is the output of "select * from product_component_version"

[('NLSRTL ', '10.2.0.2.0', 'Production'),

('Oracle Database 10g Enterprise Edition ', '10.2.0.2.0', 'Prod'),
('PL/SQL ', '10.2.0.2.0', 'Production'),
('TNS for Linux: ', '10.2.0.2.0', 'Production')]

Changed 2 years ago by shai

Make Oracle backend not use unicode literals to work around Oracle 10 bug

comment:5 Changed 2 years ago by shai

I suspect this is a unicode-vs-oracle-10 problem, similar to #20292 (although that one shows up already with Django 1.5.1).

If this is indeed the case, applying the attached oracle-10.patch should probably fix both problems; I have no way to test this myself, so I'm asking you 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).

comment:6 Changed 2 years ago by ludo

The patch does not work (and btw line numbers are relative to master, they are different for the second block in 1.6.x).

What I don't understand is why use utils.DatabaseError -- which does not work to trap the exception -- where you have a perfectly working local DatabaseError which works as intended.

comment:7 follow-up: Changed 2 years ago by shai

We could use the DatabaseError, but I think that would be the wrong fix -- as far as I understand, the operators this code checks for are available on Oracle 10, so you should only get the exception on Oracle 9. If the exception is thrown and caught, you will be limited in your "like" queries (contains, startswith, etc).

That's why I'm trying to fix it from the other direction -- preventing the exception from being raised in the first place.

And yes, I made the change in master -- the oracle backend on 1.6 is not significantly different, so I take the liberty to work where it is more convenient for me; the patch uses different line numbers, but does apply cleanly with patch -p1 < oracle-10.patch executed at the django root.

Is there any chance for you to test this on 10.2.0.5 ? I believe that users of that version do not encounter this problem (only #20292)

comment:8 Changed 2 years ago by ludo

Yes I know it applies cleanly, I was just nitpicking. :)

Unfortunately I have no way to test against 10.2.0.5...

comment:9 in reply to: ↑ 7 Changed 2 years ago by ludo

Replying to shai:

We could use the DatabaseError, but I think that would be the wrong fix -- as far as I understand, the operators this code checks for are available on Oracle 10, so you should only get the exception on Oracle 9. If the exception is thrown and caught, you will be limited in your "like" queries (contains, startswith, etc).

Might be there are two fixes needed: one to be able to use the right operators, and one to actually trap DatabaseError if it gets raised (which it shouldn't as per the first needed fix, but anyway...).

comment:10 Changed 2 years ago by shai

  • Owner changed from nobody to shai
  • Status changed from new to assigned
  • Triage Stage changed from Unreviewed to Accepted

Having read the related tickets and discussions (and code) more carefully, it seems I was a little confused, and your solution appears correct -- except for the part that utils.DatabaseError is actually not necessary at all there.

The regression was introduced in [59a352087591a26023412cbcb830cd1d34fc9b99] which refactored the wrapping of database exceptions -- and made it so that this code runs with a "bare" (non-wrapped) cursor.

comment:11 Changed 2 years ago by Shai Berger <shai@…>

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

In 6ed579e7eb6e1eda3dd1b0afb306d58b9498bfa0:

Fixed #20785 -- Corrected exception caught for Oracle LIKE operator detection

The code that tests to see which LIKE expressions to use now runs
using non-error-wrapped cursor, so cx_Oracle exceptions need to be caught
rather than Django DatabaseErrors.

Thanks Trac user ludo for report and initial patch.

comment:12 Changed 2 years ago by Shai Berger <shai@…>

In 17e632929cdf9575f538c1f98379adac8698c288:

[1.6.x] Fixed #20785 -- Corrected exception caught for Oracle LIKE operator detection

The code that tests to see which LIKE expressions to use now runs
using non-error-wrapped cursor, so cx_Oracle exceptions need to be caught
rather than Django DatabaseErrors.

Thanks Trac user ludo for report and initial patch.

comment:13 Changed 2 years ago by aaugustin

Thank you for fixing my mess, and sorry.

comment:14 Changed 2 years ago by shai

For the record, I don't think there was any reasonable way for you to avoid this, nothing you should apologize for. At some point I may try to get to the bottom of this and set up a test to validate it, but since Ian already dug into this a few years ago and came up with not-much, I'm not making it a high priority.

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