Re: [Django] #33789: Table and colums with more then 30 chars can no longer be found on Oracle.

[Django]

#33789: Table and colums with more then 30 chars can no longer be found on Oracle.
-------------------------------------+-------------------------------------
Reporter: Paul in 't Hout | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Paul in 't Hout):

Replying to [comment:2 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 ?

--
Ticket URL: <https://code.djangoproject.com/ticket/33789#comment:3>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#33789: Table and colums with more then 30 chars can no longer be found on Oracle.
-------------------------------------+-------------------------------------
Reporter: Paul in 't Hout | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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()`.

--
Ticket URL: <https://code.djangoproject.com/ticket/33789#comment:4>

[Django]

#33789: Table and colums with more then 30 chars can no longer be found on Oracle.
-------------------------------------+-------------------------------------
Reporter: Paul in 't Hout | Owner: nobody

Type: Bug | Status: closed

Component: Database layer | Version: 4.0
(models, ORM) |

Severity: Normal | Resolution: needsinfo

Keywords: oracle | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: new => closed
* resolution: => needsinfo

--
Ticket URL: <https://code.djangoproject.com/ticket/33789#comment:5>

[Django]

#33789: Table and colums with more then 30 chars can no longer be found on Oracle.
-------------------------------------+-------------------------------------
Reporter: Paul in 't Hout | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: oracle | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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)

--
Ticket URL: <https://code.djangoproject.com/ticket/33789#comment:6>

[Django]

#33789: Table and colums with more then 30 chars can no longer be found on Oracle.
-------------------------------------+-------------------------------------
Reporter: Paul in 't Hout | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: oracle | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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?

--
Ticket URL: <https://code.djangoproject.com/ticket/33789#comment:7>

[Django]

#33789: Table and colums with more then 30 chars can no longer be found on Oracle.
-------------------------------------+-------------------------------------
Reporter: Paul in 't Hout | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: oracle | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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$%'
/
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33789#comment:8>

[Django]

#33789: Table and colums with more then 30 chars can no longer be found on Oracle.
-------------------------------------+-------------------------------------
Reporter: Paul in 't Hout | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: oracle | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* Attachment "fix_long_quoted_table_names.py" added.

Script to generate RENAME TABLE for table with incorrect quoted names

--
Ticket URL: <https://code.djangoproject.com/ticket/33789>

[Django]

#33789: Table and colums with more then 30 chars can no longer be found on Oracle.
-------------------------------------+-------------------------------------
Reporter: Paul in 't Hout | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution: needsinfo
Keywords: oracle | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak):

What do you think about

[https://code.djangoproject.com/attachment/ticket/33789/fix_long_quoted_table_names.py
this script] to generate `RENAME TABLE` statements for outdated table
names? I can create a similar one for the columns.

--
Ticket URL: <https://code.djangoproject.com/ticket/33789#comment:9>