Opened 5 years ago

Closed 3 years ago

Last modified 12 months ago

#17202 closed Bug (duplicate)

Inspectdb produces incorrect max_length for NVARCHAR2 columns with Oracle

Reported by: rodolfo.3+django@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords: inspectdb oracle max_length mysql
Cc: Shai Berger Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I created a table in a Oracle database with a "NVARCHAR2" field with size=150:

CREATE table "TEST" (
    "CHAR_150"   NVARCHAR2(150)
)

Then, when I run "manage.py inspectdb", the generated code is:

class Test(models.Model):
    char_150 = models.CharField(max_length=300, blank=True)
    class Meta:
        db_table = u'test'

A field type "NVARCHAR2(150)" generate a "max_length=300" CharField.

Change History (3)

comment:1 Changed 5 years ago by Ian Kelly

Summary: Double max_length using inspectdb with OracleInspectdb produces incorrect max_length for NVARCHAR2 columns with Oracle
Triage Stage: UnreviewedAccepted

The problem is that the cursor description only includes the size of the column in bytes, not in characters. For a 16-bit encoding, each character occupies two bytes. For another encoding, the ratio may be different. The other problem is that we can't distinguish between VARCHAR2 and NVARCHAR2 using the cursor description, which would make it impossible to correct for the problem by determining the appropriate factor and dividing.

To fix this, we'll need to generalize the column introspection code into the backends, and rewrite it for the oracle backend, to have it inspect columns by querying the all_tab_cols view instead of just relying on a cursor description.

comment:2 Changed 4 years ago by Mark Jones

A similar problem happens with mysql 5.5.28CE on linux where the size is 3x greater than that specified, given the SQL:

CREATE TABLE `primaryfields` (
  `fpost` int(11) NOT NULL,
  `index1911` char(36) DEFAULT NULL,
  `ssn` char(11) DEFAULT NULL,
  `middleinitial` char(1) DEFAULT NULL,
  `firstname` char(30) DEFAULT NULL,
  `lastname` char(30) DEFAULT NULL,
  `employeeid` char(10) DEFAULT NULL,
  PRIMARY KEY (`fpost`),
  CONSTRAINT `primaryfields_ibfk_1` FOREIGN KEY (`fpost`) REFERENCES `filenames` (`fpost`)
) ENGINE=InnoDB DEFAULT CHARSET='''latin1''' |

You get the Python:

class Primaryfields(models.Model):
    fpost = models.ForeignKey(Filenames, primary_key=True, db_column='fpost')
    index1911 = models.CharField(max_length=108, blank=True)
    ssn = models.CharField(max_length=33, blank=True)
    middleinitial = models.CharField(max_length=3, blank=True)
    firstname = models.CharField(max_length=90, blank=True)
    lastname = models.CharField(max_length=90, blank=True)
    employeeid = models.CharField(max_length=30, blank=True)
    class Meta:
        db_table = u'primaryfields'
Last edited 12 months ago by Tim Graham (previous) (diff)

comment:3 Changed 3 years ago by Shai Berger

Cc: Shai Berger added
Keywords: mysql added
Resolution: duplicate
Status: newclosed

For Oracle, a more general ticket #19884 tracks this issue (and similar ones).

For MySQL, #22305 is a documentation issue that says to use utf-8, not latin1.

I'm tentatively closing as duplicate -- I'm sure about Oracle, but anyone who has objections about the MySQL issue should feel free to reopen.

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