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

[frank.hoffsummer@…]

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.

[frank.hoffsummer@…]

model that causes the syncdb error

[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.

[nick]

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

[Erin 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.

[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.

[Erin Kelly]

(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.

[Erin 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.

[Erin Kelly]

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

[Aymeric Augustin]

Change UI/UX from NULL to False.

[Aymeric Augustin]

Change Easy pickings from NULL to False.

[Asif Saifuddin Auvi]

Replying to collinanderson:

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

[Tim Graham]

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