Opened 10 years ago

Last modified 13 months ago

#4140 new Bug

oracle: manage.py syncdb fails with ORA-06552 when a fieldname is a keyword

Reported by: frank.hoffsummer@… Owned by:
Component: Core (Management commands) Version: master
Severity: Normal Keywords: oracle Oracle
Cc: Matt Boersma Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

calling "manage.py syncdb log" with my log model on an empty Oracle database yields the following exception

Traceback (most recent call last):
  File "./manage.py", line 11, in ?
    execute_manager(settings)
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/site-packages/django/core/management.py", line 1730, in execute_manager
    execute_from_command_line(action_mapping, argv)
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/site-packages/django/core/management.py", line 1627, in execute_from_command_line
    action_mapping[action](int(options.verbosity), options.interactive)
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/site-packages/django/core/management.py", line 573, in syncdb
    cursor.execute(statement)
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/site-packages/django/db/backends/util.py", line 13, in execute
    return self.cursor.execute(sql, params)
  File "/Library/Frameworks/Python.framework/Versions/2.4/lib/python2.4/site-packages/django/db/backends/oracle/base.py", line 104, in execute
    return Database.Cursor.execute(self, query, params)
cx_Oracle.DatabaseError: ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed

the model myapp/log/models.py is simple and causes no problem in MySQL, I attached it to this ticket.
a subsequent, second run of manage py syncdb goes without error.

Attachments (1)

models.py (2.3 KB) - added by frank.hoffsummer@… 10 years ago.
model that causes the syncdb error

Download all attachments as: .zip

Change History (21)

Changed 10 years ago by frank.hoffsummer@…

Attachment: models.py added

model that causes the syncdb error

comment:1 Changed 10 years ago by Matt Boersma

In your LogMessage model, the first column is named "timestamp". This is an Oracle reserved keyword (we've often seen columns named "date" cause the same problem). Renaming it and the references to it in that module fixes the problem.

Unfortunately, Oracle will gladly create the table for you even with an illegal column name that you won't be able to SELECT on. Then when trying to compile the trigger referencing that table, it will bomb with "ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed," an utterly unhelpful error message.

This is brain-damaged Oracle behavior we can't fix, but we could perhaps put in a check somewhere for SQL reserved words as column names so that the problem is flagged earlier, with an informative message.

comment:2 Changed 10 years ago by Simon G. <dev@…>

Triage Stage: UnreviewedDesign decision needed

comment:3 Changed 10 years ago by Simon G. <dev@…>

Summary: [boulder-oracle]: manage.py syncdb fails with ORA-06552[boulder-oracle]: manage.py syncdb fails with ORA-06552 when a fieldname is a keyword

comment:4 Changed 9 years ago by Matt Boersma

Keywords: Oracle added
Version: other branchSVN

comment:5 Changed 9 years ago by Ian Kelly

Owner: changed from nobody to Ian Kelly
Status: newassigned

comment:6 Changed 9 years ago by Ian Kelly

Triage Stage: Design decision neededAccepted

comment:7 Changed 8 years ago by nick

Any changes coming up for this ticket?
Or maybe i just overlooked and option how to get around this.

comment:8 Changed 8 years ago by Ian Kelly

I started working on a patch a long time ago to detect and mangle problematic column names in order to make them pass, but I've never gotten around to finishing it. If anybody wants to take the ticket over from me, feel free; otherwise, I'm sure I'll get to it eventually.

In the meantime, the workaround is to not name your fields with Oracle keywords.

comment:9 Changed 8 years ago by Matt Boersma

Cc: Matt Boersma added

comment:10 Changed 8 years ago by Matt Boersma

Oracle allows you to create columns using reserved words (as long as they're in double quotes), but afterward the same table can't be involved in any triggers or stored procedures, even if that column is double-quoted or even if it isn't referenced directly. The list of reserved words differs between Oracle versions, and determining the list at runtime appears to be problematic if I recall correctly Ian's earlier investigations.

Perhaps the most workable "fix" here is to catch the uninformative ORA-06552 error during syncdb and append the hint "Does the model_foobar table use a reserved word such as DATE or TIMESTAMP for one of its columns?" before re-throwing it.

comment:11 Changed 7 years ago by Ian Kelly

Resolution: fixed
Status: assignedclosed

(In [12346]) Fixed #4140: Documented the ORA-06552 error that can occur when a keyword is used as a field name in the Oracle notes.

comment:12 Changed 7 years ago by Ian Kelly

(In [12347]) [1.1.X] Fixed #4140: Documented the ORA-06552 error that can occur when a keyword is used as a field name in the Oracle notes. Backport of r12346 from trunk.

comment:13 Changed 7 years ago by Ian Kelly

Resolution: fixed
Status: closedreopened

I closed the wrong ticket. Should have been #10422.

comment:14 Changed 6 years ago by Ian Kelly

Owner: Ian Kelly deleted
Status: reopenednew

comment:15 Changed 6 years ago by Łukasz Rekucki

Severity: Normal
Type: Bug

comment:16 Changed 5 years ago by Aymeric Augustin

UI/UX: unset

Change UI/UX from NULL to False.

comment:17 Changed 5 years ago by Aymeric Augustin

Easy pickings: unset

Change Easy pickings from NULL to False.

comment:18 Changed 23 months ago by Collin Anderson

Summary: [boulder-oracle]: manage.py syncdb fails with ORA-06552 when a fieldname is a keywordoracle: manage.py syncdb fails with ORA-06552 when a fieldname is a keyword

comment:19 in reply to:  18 Changed 13 months ago by Asif Saifuddin Auvi

Replying to collinanderson:

since syncdb is removed from django, is the issue still valid?

comment:20 Changed 13 months ago by Tim Graham

Likely the same issue applies to migrate, but it will need to be checked.

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