Code

Opened 7 years ago

Last modified 3 years ago

#4140 new Bug

[boulder-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: mboersma 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@… 7 years ago.
model that causes the syncdb error

Download all attachments as: .zip

Change History (18)

Changed 7 years ago by frank.hoffsummer@…

model that causes the syncdb error

comment:1 Changed 7 years ago by bouldersprinters

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

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 7 years ago by Simon G. <dev@…>

  • Triage Stage changed from Unreviewed to Design decision needed

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

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

comment:4 Changed 7 years ago by mboersma

  • Keywords Oracle added
  • Version changed from other branch to SVN

comment:5 Changed 7 years ago by ikelly

  • Owner changed from nobody to ikelly
  • Status changed from new to assigned

comment:6 Changed 7 years ago by ikelly

  • Triage Stage changed from Design decision needed to Accepted

comment:7 Changed 6 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 6 years ago by ikelly

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 5 years ago by mboersma

  • Cc mboersma added

comment:10 Changed 5 years ago by mboersma

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 4 years ago by ikelly

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

(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 4 years ago by ikelly

(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 4 years ago by ikelly

  • Resolution fixed deleted
  • Status changed from closed to reopened

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

comment:14 Changed 3 years ago by ikelly

  • Owner ikelly deleted
  • Status changed from reopened to new

comment:15 Changed 3 years ago by lrekucki

  • Severity set to Normal
  • Type set to Bug

comment:16 Changed 2 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:17 Changed 2 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from (none) to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


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

 
Note: See TracTickets for help on using tickets.