DdlUtils
  1. DdlUtils
  2. DDLUTILS-195

Can not update a foreign key on a case sensetive MySql Database

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.1
    • Fix Version/s: 1.1
    • Component/s: Core - MySql
    • Labels:
      None
    • Environment:
      Trying to update a existing case sensitive mysql database on Linux

      Description

      For testing purposes I made my MySql database case sensitive (to avoid problems when deploying to linux)

      The foreign key restrained throws the following exception;
      throw new ModelException("The foreignkey "fkDesc" in table "curTable.getName()" references the undefined table "+fk.getForeignTableName());

      fk.getForeignTableName is all lower case, while the table name is camel case. hacking the correct camel case into the piece of code that throws the exception 'fixes' the issue. Therefore the issue is that fk.getForeignTableName()) contains the wrong table name.

      This wrong table name comes from JdbcModelReader
      fk.setForeignTableName((String)values.get("PKTABLE_NAME"));

      The PKTABLE_NAME value comes from readColumn what returns the table name in lowercase

        Activity

        Hide
        Thomas Dudziak added a comment -

        Are you talking about the lower_case_table_names configuration option ? A normal Linux Mysql installation should not require tweaking this setting. Could you try these two sets of SQL:

        (1)

        CREATE TABLE Roundtrip1
        (
        Pk1 INTEGER NOT NULL,
        Pk2 DOUBLE NOT NULL,
        PRIMARY KEY (Pk1, Pk2)
        ) ENGINE=InnoDB;

        CREATE TABLE Roundtrip2
        (
        Pk INTEGER NOT NULL,
        Avalue1 DOUBLE NOT NULL,
        Avalue2 INTEGER NOT NULL,
        PRIMARY KEY (Pk)
        ) ENGINE=InnoDB;

        ALTER TABLE Roundtrip2
        ADD CONSTRAINT Rt1_To_Rt2 FOREIGN KEY (Avalue2, Avalue1) REFERENCES Roundtrip1 (Pk1, Pk2);

        ALTER TABLE Roundtrip2
        DROP FOREIGN KEY Rt1_To_Rt2;

        ALTER TABLE Roundtrip2
        DROP INDEX Rt1_To_Rt2;

        (2)

        CREATE TABLE `Roundtrip1`
        (
        `Pk1` INTEGER NOT NULL,
        `Pk2` DOUBLE NOT NULL,
        PRIMARY KEY (`Pk1`, `Pk2`)
        ) ENGINE=InnoDB;

        CREATE TABLE `Roundtrip2`
        (
        `Pk` INTEGER NOT NULL,
        `Avalue1` DOUBLE NOT NULL,
        `Avalue2` INTEGER NOT NULL,
        PRIMARY KEY (`Pk`)
        ) ENGINE=InnoDB;

        ALTER TABLE `Roundtrip2`
        ADD CONSTRAINT `Rt1_To_Rt2` FOREIGN KEY (`Avalue2`, `Avalue1`) REFERENCES `Roundtrip1` (`Pk1`, `Pk2`);

        ALTER TABLE `Roundtrip2`
        DROP FOREIGN KEY `Rt1_To_Rt2`;

        ALTER TABLE `Roundtrip2`
        DROP INDEX `Rt1_To_Rt2`;

        Both of these should work fine (they run without problems on my my 5.0.51a installation running with default configuration on Ubuntu).

        Show
        Thomas Dudziak added a comment - Are you talking about the lower_case_table_names configuration option ? A normal Linux Mysql installation should not require tweaking this setting. Could you try these two sets of SQL: (1) CREATE TABLE Roundtrip1 ( Pk1 INTEGER NOT NULL, Pk2 DOUBLE NOT NULL, PRIMARY KEY (Pk1, Pk2) ) ENGINE=InnoDB; CREATE TABLE Roundtrip2 ( Pk INTEGER NOT NULL, Avalue1 DOUBLE NOT NULL, Avalue2 INTEGER NOT NULL, PRIMARY KEY (Pk) ) ENGINE=InnoDB; ALTER TABLE Roundtrip2 ADD CONSTRAINT Rt1_To_Rt2 FOREIGN KEY (Avalue2, Avalue1) REFERENCES Roundtrip1 (Pk1, Pk2); ALTER TABLE Roundtrip2 DROP FOREIGN KEY Rt1_To_Rt2; ALTER TABLE Roundtrip2 DROP INDEX Rt1_To_Rt2; (2) CREATE TABLE `Roundtrip1` ( `Pk1` INTEGER NOT NULL, `Pk2` DOUBLE NOT NULL, PRIMARY KEY (`Pk1`, `Pk2`) ) ENGINE=InnoDB; CREATE TABLE `Roundtrip2` ( `Pk` INTEGER NOT NULL, `Avalue1` DOUBLE NOT NULL, `Avalue2` INTEGER NOT NULL, PRIMARY KEY (`Pk`) ) ENGINE=InnoDB; ALTER TABLE `Roundtrip2` ADD CONSTRAINT `Rt1_To_Rt2` FOREIGN KEY (`Avalue2`, `Avalue1`) REFERENCES `Roundtrip1` (`Pk1`, `Pk2`); ALTER TABLE `Roundtrip2` DROP FOREIGN KEY `Rt1_To_Rt2`; ALTER TABLE `Roundtrip2` DROP INDEX `Rt1_To_Rt2`; Both of these should work fine (they run without problems on my my 5.0.51a installation running with default configuration on Ubuntu).
        Hide
        Thomas Dudziak added a comment -

        Please post the SQL to create the relevant tables and foreign key, as well as Ant target or Java code that you used.

        Show
        Thomas Dudziak added a comment - Please post the SQL to create the relevant tables and foreign key, as well as Ant target or Java code that you used.

          People

          • Assignee:
            Thomas Dudziak
            Reporter:
            meindert hoving
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development