Opened 2 years ago

Closed 2 years ago

Last modified 2 years ago

#33789 closed Bug (fixed)

Document changes in quoting table/colums names on Oracle.

Reported by: Paul in 't Hout Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: 4.0
Severity: Release blocker Keywords: oracle
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Paul in 't Hout)

While in the proces of updating our project from Django 2.2 to 3.2 to 4.0 I noticed that things started to break down in 4.0.

Since upgrading to 4.0, tables with names longer than 30 chars fail with
ORA-00942: table or view does not exist
models with columns with names longer than 30 chars fail with
ORA-00904: "COMPONENT_ATTRIBUTE_METADATA"."REQUIRES_ILOM_CONNECT_FOR_DD63": invalid identifier

When I run the following on django 2.2 / 3.2.13 with python 3.6

from django.db.backends.utils import truncate_name
truncate_name("very_long_database_table_or_column" length=30)
Out[7]: 'VERY_LONG_DATABASE_TABLE_O20cb'

Then run the same on django 4.0.5 with python 3.9

from django.db.backends.utils import truncate_name
truncate_name("very_long_database_table_or_column", length=30)
Out[6]: 'very_long_database_table_o0c9a'

The hash value is different. As a result a table or column name is queried that does not exist, because they where created using the older algorithm.

I believe this was broken by this change: https://code.djangoproject.com/ticket/32653

Attachments (1)

generate_rename_table_colums_new_trunc.py (2.0 KB ) - added by Mariusz Felisiak 2 years ago.

Download all attachments as: .zip

Change History (21)

comment:1 by Paul in 't Hout, 2 years ago

Description: modified (diff)

comment:2 by Mariusz Felisiak, 2 years ago

Summary: Table and colums with more then 30 chars can no longer be found / queried after migrating from django 3.2 to 4.0 using an Oracle backendTable and colums with more then 30 chars can no longer be found on Oracle.

Yes, this behavior was intentionally changed in 1f643c28b5f2b039c47155692844dbae1cb091cd. Unfortunately, the previous implementation of quote_name() on Oracle was also buggy and not consistent with db_table. Do you have manually specified db_table names? We can document this change in 4.0 release notes with the recommendation to specify db_table in such cases. What do you think?

in reply to:  2 ; comment:3 by Paul in 't Hout, 2 years ago

Replying to Mariusz Felisiak:

Yes, this behavior was intentionally changed in 1f643c28b5f2b039c47155692844dbae1cb091cd. Unfortunately, the previous implementation of quote_name() on Oracle was also buggy and not consistent with db_table. Do you have manually specified db_table names? We can document this change in 4.0 release notes with the recommendation to specify db_table in such cases. What do you think?

We do use db_table in Model.Meta. I can update those to use full UPPER notation of the table_name, I suppose that should address the table name truncation.

Is there a similar workaround that can be used for column names and indexes ?

Last edited 2 years ago by Paul in 't Hout (previous) (diff)

in reply to:  3 comment:4 by Mariusz Felisiak, 2 years ago

We do use db_table in Model.Meta.

Can you show us an example that causes the issue in Django 4.0+?

Is there a similar workaround that can be used for column names and indexes?

Yes, you can use db_column, and name for Index().

comment:5 by Mariusz Felisiak, 2 years ago

Resolution: needsinfo
Status: newclosed

comment:6 by Paul in 't Hout, 2 years ago

Here are 2 fictitious models that would have this problem.
In our case they would have been created with a django 1.x , but for this bug I would think 3.2 would also reproduce.

  1. Create migration in 3.2.13
  2. Migrate
  3. Upgrade to 4.0.5
  4. Query the models
class LongColumnName( models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=100, db_index=True, unique=True)
    my_very_long_boolean_field_setting = models.BooleanField(default=False)

    class Meta:
        db_table = "long_column_name"
        app_label = "my_app"
        ordering = ["id", "name"]  

And for a long table name

class LongTableName( models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=100, db_index=True, unique=True)
    short_field = models.BooleanField(default=False)

    class Meta:
        db_table = "my_very_long_table_name_for_demo"
        app_label = "my_app"
        ordering = ["id", "name"]  

I have now gone ahead with a workaround. Which, for the above tables, would look like this:

  1. Get the table and column name as defined in the database for the long fields and table names:

MY_VERY_LONG_BOOLEAN_FIELD76a5
MY_VERY_LONG_TABLE_NAME_FOd906

  1. Update the models with the table_name and db_column name attributes:
class LongColumnName( models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=100, db_index=True, unique=True)
    my_very_long_boolean_field_setting = models.BooleanField(default=False, db_column="MY_VERY_LONG_BOOLEAN_FIELD76a5"
)

    class Meta:
        db_table = "long_column_name"
        app_label = "my_app"
        ordering = ["id", "name"]  

class LongTableName( models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=100, db_index=True, unique=True)
    short_field = models.BooleanField(default=False)

    class Meta:
        db_table = "MY_VERY_LONG_TABLE_NAME_FOd906"
        app_label = "my_app"
        ordering = ["id", "name"]  
  1. Create a migration
  2. Update to Django 4.0.5
  3. Fake the migration. I needed to auto-fake , so I added an empty apply / unapply method as described here (https://stackoverflow.com/questions/49150541/auto-fake-django-migration)

comment:7 by Mariusz Felisiak, 2 years ago

Thanks for extra details. I'm afraid that we cannot revert Django 4.0+ behavior because we will break all tables/columns created after this change 😕 What do you think about adding release notes with a link to the script to help identify and fix problematic identifiers? For example with the list of models/columns that should be updated?

comment:8 by Paul in 't Hout, 2 years ago

That sounds good to me. I'm sure that's going to be helpful for those encountering the same situation.

I used the following sql to find out the columns and table names that are potential at risk for this issue.

select table_name,
       LENGTH(table_name) as l
from USER_TABLES
where LENGTH(table_name) = 30
and table_name not like 'DM$%'
/

select TABLE_NAME,
       COLUMN_NAME,
       LENGTH(COLUMN_NAME) as l
from USER_TAB_COLUMNS
where LENGTH(COLUMN_NAME) = 30
and table_name not like 'DM$%'
/

comment:9 by Mariusz Felisiak, 2 years ago

What do you think about this script to generate RENAME TABLE statements for outdated table names? I can create a similar one for the columns.

comment:10 by Mariusz Felisiak, 2 years ago

Resolution: needsinfo
Severity: NormalRelease blocker
Status: closednew
Summary: Table and colums with more then 30 chars can no longer be found on Oracle.Document changes in quoting table/colums names on Oracle.
Triage Stage: UnreviewedAccepted

comment:11 by Paul in 't Hout, 2 years ago

This mostly works, I had to change the following to make it work

rename_table_sql = "RENAME TABLE %s TO %s;"

to

rename_table_sql = "ALTER TABLE %s RENAME TO %s;"

by Mariusz Felisiak, 2 years ago

in reply to:  11 comment:12 by Mariusz Felisiak, 2 years ago

Replying to Paul in 't Hout:

This mostly works, I had to change the following to make it work

rename_table_sql = "RENAME TABLE %s TO %s;"

to

rename_table_sql = "ALTER TABLE %s RENAME TO %s;"

Thanks for checking. I attached the new version that also should generate RENAME statements for columns. Can you take a look?

comment:13 by Paul in 't Hout, 2 years ago

I've run this multiple times for our project and it turned up exactly those tables/columns with the problem, very nice!
If there is a place for a caution or a note that this script should be run with Django 4.x in place (and not prior to upgrading) that would be great. That had me fooled first time around :-)

comment:14 by Mariusz Felisiak, 2 years ago

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

Thanks for checking. I'd prepare a release note.

comment:15 by Mariusz Felisiak, 2 years ago

The upgrade script to generate RENAME DDL statements for Django 4.0.

comment:16 by Mariusz Felisiak, 2 years ago

Has patch: set

comment:17 by Carlton Gibson, 2 years ago

Triage Stage: AcceptedReady for checkin

comment:18 by GitHub <noreply@…>, 2 years ago

Resolution: fixed
Status: assignedclosed

In a0608c4b:

Fixed #33789 -- Doc'd changes in quoting table/column names on Oracle in Django 4.0.

Thanks Paul in 't Hout for the report.

Regression in 1f643c28b5f2b039c47155692844dbae1cb091cd.

comment:19 by Mariusz Felisiak <felisiak.mariusz@…>, 2 years ago

In 91b365e:

[4.1.x] Fixed #33789 -- Doc'd changes in quoting table/column names on Oracle in Django 4.0.

Thanks Paul in 't Hout for the report.

Regression in 1f643c28b5f2b039c47155692844dbae1cb091cd.
Backport of a0608c4b111555023c24ab7333a42ec53dca6b42 from main

comment:20 by Mariusz Felisiak <felisiak.mariusz@…>, 2 years ago

In 0f3b2504:

[4.0.x] Fixed #33789 -- Doc'd changes in quoting table/column names on Oracle in Django 4.0.

Thanks Paul in 't Hout for the report.

Regression in 1f643c28b5f2b039c47155692844dbae1cb091cd.
Backport of a0608c4b111555023c24ab7333a42ec53dca6b42 from main

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