Document changes in quoting table/colums names on Oracle.

[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

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

[Paul in 't Hout]

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 ?

[Mariusz Felisiak]

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

[Paul in 't Hout]

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

2. 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"]  

3. Create a migration
4. Update to Django 4.0.5
5. 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)

[Mariusz Felisiak]

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?

[Paul in 't Hout]

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$%'
/

[Mariusz Felisiak]

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.

[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;"

[Mariusz Felisiak]

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?

[Paul in 't Hout]

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 :-)

[Mariusz Felisiak]

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

[Mariusz Felisiak]

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

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

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.

[Mariusz Felisiak <felisiak.mariusz@…>]

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

[Mariusz Felisiak <felisiak.mariusz@…>]

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