Code

Opened 13 months ago

Closed 11 months ago

Last modified 11 months ago

#20201 closed Bug (duplicate)

Oracle String Length Issue with CLOB and NCLOB

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

Description

As mentioned in #20200, we are moving from MySQL to Oracle, but are having issues with TextFields. When the length of a field is greater than 2000, I get the following error:

*** DatabaseError: ORA-01461: can bind a LONG value only for insert into a LONG column

I am writing a script using Django's ORM to copy the data from one database to the other. Once it gets to a row with more than 2000 characters, it throws that error. I have figured out a workaround, but it's ugly and not a good solution.

This is a part of my scripts (pseudo-code):

for row in Model.objects.using(source).all():
    row.save(using=target)

This is my workaround (again, very ugly):

for row in Model.objects.using(source).all():
    try:
        row.save(using=target)
    except:
        desc = row.description
        row.description = desc[:2000]
        row.save(using=target)

        # once you have a "good" save, you can now save the original data with no issues
        row.description = desc
        row.save(using=target)


My lab environment:

Windows 7
Python 2.7 (x86)
cx_Oracle 5.1.2
Oracle 11.2g (running in Exadata cluster)
Django 1.5.1

Attachments (0)

Change History (8)

comment:1 Changed 13 months ago by deejross

  • Cc deejross added
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

comment:2 Changed 13 months ago by deejross

This appears to be a Django regression. Out of curiosity, I went back to Django 1.4.3 and it works fine without any workaround.

comment:3 Changed 13 months ago by deejross

  • Severity changed from Normal to Release blocker

comment:4 Changed 12 months ago by shai

  • Cc shai@… added

There seems to be a problem here, but probably not the one you name.

First of all, how do you get values longer than 2000? What is the field definition?

Second, what is the column definition in Oracle? What was it in MySql?

Third -- when things work "with no issues" -- did you check that the whole value was saved, and not just the first 2000 characters?

Thanks,
Shai.

comment:5 Changed 11 months ago by aaugustin

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

There's a long history of ORA-01461 errors: see #304, [ed5eca59], #9152, #11487.

However, the limit is usually 4000 chars, not 2000.

If your problem isn't a duplicate of any of these tickets, please provide the information requested by Shai and reopen.

Thank you.

comment:6 Changed 11 months ago by ikelly

I can confirm that this bug is still around. The backend marks strings that are longer than 4000 characters as a CLOB when sending them to the database. The problem is that the limit is actually 4000 bytes, not 4000 chars. Consequently, as a practical matter the limit depends on the particular national character set used by the database, and for a variable-length encoding the particular characters used in the string would matter also. The only way I can see to generally fix it is to (expensively) test-encode the string just to determine its length before sending it. More practically, we should probably change the threshold from 4000 to 1000 characters.

comment:7 Changed 11 months ago by shai

Hi Ian,

I'd like to work on fixing Oracle bugs, but from the current description it was very unclear to me what the bug is and how to reproduce it. While your comment suggests that this is clear to you, it doesn't quite make it clear to me.

Would you mind trying to fix the description?

Thanks,
Shai.

comment:8 Changed 11 months ago by aaugustin

  • Resolution changed from needsinfo to duplicate

Ian: if I understand correctly, #11487 is the canonical bug about this issue?

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.