PUBLIC - Liferay Portal Community Edition

Data length difference causes problems with LAR import / export if the backend database is different

Details

  • Epic/Theme:
  • Fix Priority:
    3
  • Similar Issues:
    Show 5 results 

Description

In Oracle the default text length is > 1000 while in mysql it's 255. The portal allows the user to add > 255 character long data into the text fields (like the title of the DLFileEntry). When this is exported and imported on mysql, it fails as the data is too long.

  1. File
    field_length_checking.sql
    12/Oct/12 2:21 AM
    4 kB
    Peter Borkuti
  2. File
    table-fields.ods
    12/Oct/12 2:21 AM
    48 kB
    Peter Borkuti

Activity

Hide
Permalink
Peter Borkuti added a comment -

In old days, there was 300 character the length of many varchar fields (among them was DLFileEntry.title), but with LPS-2325 we reduced it to 255, probably because of better performance.
I checked in trunk, and the LPS-2325 was committed at 2009-03-04.

Table creation is based on "portal-tables.sql" which is in portal-impl.jar: com.liferay.portal.tools.sql.dependencies

In it there is the appropriate part:
create table DLFileEntry (
uuid_ VARCHAR(75) null,
....
title VARCHAR(255) null,
....

);

So the template is OK.

In the "compiled" sql's (for example: portal-minimal-oracle.sql) there are other length restrictions for Oracle, which (I think) is a bug.
In 6.1 SP2, the portal-minimal-oracle.sql is appropriate for the template, so in SP2 there are no different restriction for field length between Oracle and Mysql.

But beware! If the customer upgrades from 6.1 sp1, than the oracle field sizes will be different from mysql, because the upgrade does not set char_lengths, if I am not mistaken...

Probably we should check values length in the fields, and if it is greater than the new CHARACTER_LENGTH, we should inform the user.

I attached and example checker sql script and an odt file, which helps to generate the sql checker.

Show
Peter Borkuti added a comment - In old days, there was 300 character the length of many varchar fields (among them was DLFileEntry.title), but with LPS-2325 we reduced it to 255, probably because of better performance. I checked in trunk, and the LPS-2325 was committed at 2009-03-04. Table creation is based on "portal-tables.sql" which is in portal-impl.jar: com.liferay.portal.tools.sql.dependencies In it there is the appropriate part: create table DLFileEntry ( uuid_ VARCHAR(75) null, .... title VARCHAR(255) null, .... ); So the template is OK. In the "compiled" sql's (for example: portal-minimal-oracle.sql) there are other length restrictions for Oracle, which (I think) is a bug. In 6.1 SP2, the portal-minimal-oracle.sql is appropriate for the template, so in SP2 there are no different restriction for field length between Oracle and Mysql. But beware! If the customer upgrades from 6.1 sp1, than the oracle field sizes will be different from mysql, because the upgrade does not set char_lengths, if I am not mistaken... Probably we should check values length in the fields, and if it is greater than the new CHARACTER_LENGTH, we should inform the user. I attached and example checker sql script and an odt file, which helps to generate the sql checker.

People

Vote (0)
Watch (3)

Dates

  • Created:
    Updated:
    Days since last comment:
    32 weeks ago