OpenJPA
  1. OpenJPA
  2. OPENJPA-455

Incorrect MySQL DDL Generation for integer types

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.0.0, 1.0.1
    • Fix Version/s: 1.1.0
    • Component/s: None
    • Labels:
      None

      Description

      Opening a JIRA report on Tim's behalf.

      I turned the schema tool loose on a MySQL production database this
      afternoon and it failed. The essence of the problem appears that DDL was
      being generated with a type declaration of this form:

      int unsigned(10)

      In MySQL, the proper form is:

      int(10) unsigned

      viz:

      ALTER TABLE fubar MODIFY col1 int(10) unsigned;

      Checking other options indicates that similar constructs such as CREATE
      TABLE are likewise defective.

      I looked at the svn trunk head source code in
      org.apache.openjpa.jdbc.sql.MySQLDictionary.java and the parent class
      DBDictionary.java. The offending method appears to be:

      1508: public String getTypeName(Column col)

      This method has no override in MySQLDictionary, but apparently needs
      one. I think it's a minor mod, but I'm not currently set up to build and
      test in the environment where the offending database exists.

      This is a SEVERE error. It causes generation of defective SQL for
      SQL-generating options and causes live updates to schemas to fail.

      I don't have a Jira login at present, so if someone could log this, it
      would be appreciated.

      Thanks,

      Tim Holloway

        Issue Links

          Activity

          Hide
          Patrick Linskey added a comment -

          Re-resolving, since Joe opened a new issue for the problem he found.

          Show
          Patrick Linskey added a comment - Re-resolving, since Joe opened a new issue for the problem he found.
          Hide
          Joe Weinstein added a comment -

          Hi. I think I've found a problem here. Oracle has a NUMBER
          column type, which may be specified with sizes, eg:
          NUMBER(7), or NUMBER(9,3), but may also be unsized,
          eg: NUMBER. Since this fix, the new code is failing to
          process the "NUMBER

          {0}" template for non-sized
          columns, so the DDL sent to the DBMS is like:

          CREATE TABLE ABSTRACTMAPPEDAPPIDSUPER ( ..., VERSN NUMBER{0}

          , ...

          which needless to say, dies.

          The change I made that fixed this is:

          protected String insertSize(String typeName, String size) {
          if(StringUtils.isEmpty(size)) {

          int idx = typeName.indexOf("

          {0}"); // remove the size token if not needed...
          if (idx != -1) { return typeName.substring(0,idx); }
          return typeName;
          }

          int idx = typeName.indexOf("{0}

          ");
          ...

          ie:

          Index: openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java
          ===================================================================
          — openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java (revision 61099
          9)
          +++ openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java (working copy)
          @@ -1648,8 +1648,13 @@
          */
          protected String insertSize(String typeName, String size) {
          if(StringUtils.isEmpty(size))

          { - return typeName; - }

          +
          + int idx = typeName.indexOf("

          {0}");
          + if (idx != -1) { + return typeName.substring(0,idx); + }
          + return typeName;
          + }

          int idx = typeName.indexOf("{0}

          ");
          if (idx != -1) {

          Please let me know what you think, and how to absorb this
          change, or it's purpose. thanks,
          Joe Weinstein at BEA Systems

          Show
          Joe Weinstein added a comment - Hi. I think I've found a problem here. Oracle has a NUMBER column type, which may be specified with sizes, eg: NUMBER(7), or NUMBER(9,3), but may also be unsized, eg: NUMBER. Since this fix, the new code is failing to process the "NUMBER {0}" template for non-sized columns, so the DDL sent to the DBMS is like: CREATE TABLE ABSTRACTMAPPEDAPPIDSUPER ( ..., VERSN NUMBER{0} , ... which needless to say, dies. The change I made that fixed this is: protected String insertSize(String typeName, String size) { if(StringUtils.isEmpty(size)) { int idx = typeName.indexOf(" {0}"); // remove the size token if not needed... if (idx != -1) { return typeName.substring(0,idx); } return typeName; } int idx = typeName.indexOf("{0} "); ... ie: Index: openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java =================================================================== — openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java (revision 61099 9) +++ openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/DBDictionary.java (working copy) @@ -1648,8 +1648,13 @@ */ protected String insertSize(String typeName, String size) { if(StringUtils.isEmpty(size)) { - return typeName; - } + + int idx = typeName.indexOf(" {0}"); + if (idx != -1) { + return typeName.substring(0,idx); + } + return typeName; + } int idx = typeName.indexOf("{0} "); if (idx != -1) { Please let me know what you think, and how to absorb this change, or it's purpose. thanks, Joe Weinstein at BEA Systems
          Hide
          Michael Dick added a comment -

          Attaching potential patch. I'll commit this when I've had a chance to do a little more testing.

          Show
          Michael Dick added a comment - Attaching potential patch. I'll commit this when I've had a chance to do a little more testing.

            People

            • Assignee:
              Patrick Linskey
              Reporter:
              Michael Dick
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development