PUBLIC - Liferay Portal Community Edition
  1. PUBLIC - Liferay Portal Community Edition
  2. LPS-28288

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. field_length_checking.sql
        4 kB
        Peter Borkuti
      2. table-fields.ods
        48 kB
        Peter Borkuti

        Activity

        Hide
        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.
        Hide
        Mate Thurzo added a comment -

        Hey,

        I have just checked the data length for both mysql and oracle and they are the same. The Oracle works now with 255 characters so does the mysql. I'm closing this ticket for now since we have dropped using the 1000+ length for oracle, if this is still a problem please reopen it.

        Thanks,

        Máté

        Show
        Mate Thurzo added a comment - Hey, I have just checked the data length for both mysql and oracle and they are the same. The Oracle works now with 255 characters so does the mysql. I'm closing this ticket for now since we have dropped using the 1000+ length for oracle, if this is still a problem please reopen it. Thanks, Máté

          People

          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:
              Days since last comment:
              1 year, 47 weeks, 5 days ago

              Development

                Structure Helper Panel