Uploaded image for project: 'Teiid Designer'
  1. Teiid Designer
  2. TEIIDDES-3217

Error reading Foreign Keys from Mysql 8.0

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 11.1.3
    • Fix Version/s: None
    • Component/s: Teiid Integration
    • Labels:
      None
    • Steps to Reproduce:
      Hide

      create mysql 8.0 database and drop tables, see below. Than import jdbc database with TEIID designer. I am using eclipse with teid designer 11.1.3.

      – MySQL Workbench Forward Engineering

      SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
      SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
      SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

      – -----------------------------------------------------
      – Schema IMS_db
      – -----------------------------------------------------

      – -----------------------------------------------------
      – Schema IMS_db
      – -----------------------------------------------------
      CREATE SCHEMA IF NOT EXISTS `IMS_db` DEFAULT CHARACTER SET utf8 ;
      USE `IMS_db` ;

      – -----------------------------------------------------
      – Table `IMS_db`.`Country`
      – -----------------------------------------------------
      CREATE TABLE IF NOT EXISTS `IMS_db`.`Country` (
      `idCountry` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `Name` VARCHAR(255) NOT NULL,
      PRIMARY KEY (`idCountry`))
      ENGINE = InnoDB
      AUTO_INCREMENT = 5
      DEFAULT CHARACTER SET = utf8;

      – -----------------------------------------------------
      – Table `IMS_db`.`ClientCompany`
      – -----------------------------------------------------
      CREATE TABLE IF NOT EXISTS `IMS_db`.`ClientCompany` (
      `idClientCompany` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `CompanyName` VARCHAR(255) NOT NULL,
      `Street` VARCHAR(255) NOT NULL,
      `StreetNumber` VARCHAR(45) NOT NULL,
      `CityCode` VARCHAR(45) NOT NULL,
      `City` VARCHAR(255) NOT NULL,
      `fkCountry` INT(10) UNSIGNED NOT NULL,
      PRIMARY KEY (`idClientCompany`),
      INDEX `fkCountry_idx` (`fkCountry` ASC) VISIBLE,
      CONSTRAINT `fkCountry`
      FOREIGN KEY (`fkCountry`)
      REFERENCES `IMS_db`.`Country` (`idCountry`))
      ENGINE = InnoDB
      AUTO_INCREMENT = 2
      DEFAULT CHARACTER SET = utf8;

      SET SQL_MODE=@OLD_SQL_MODE;
      SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
      SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

      Show
      create mysql 8.0 database and drop tables, see below. Than import jdbc database with TEIID designer. I am using eclipse with teid designer 11.1.3. – MySQL Workbench Forward Engineering SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; – ----------------------------------------------------- – Schema IMS_db – ----------------------------------------------------- – ----------------------------------------------------- – Schema IMS_db – ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `IMS_db` DEFAULT CHARACTER SET utf8 ; USE `IMS_db` ; – ----------------------------------------------------- – Table `IMS_db`.`Country` – ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `IMS_db`.`Country` ( `idCountry` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `Name` VARCHAR(255) NOT NULL, PRIMARY KEY (`idCountry`)) ENGINE = InnoDB AUTO_INCREMENT = 5 DEFAULT CHARACTER SET = utf8; – ----------------------------------------------------- – Table `IMS_db`.`ClientCompany` – ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `IMS_db`.`ClientCompany` ( `idClientCompany` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `CompanyName` VARCHAR(255) NOT NULL, `Street` VARCHAR(255) NOT NULL, `StreetNumber` VARCHAR(45) NOT NULL, `CityCode` VARCHAR(45) NOT NULL, `City` VARCHAR(255) NOT NULL, `fkCountry` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`idClientCompany`), INDEX `fkCountry_idx` (`fkCountry` ASC) VISIBLE, CONSTRAINT `fkCountry` FOREIGN KEY (`fkCountry`) REFERENCES `IMS_db`.`Country` (`idCountry`)) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARACTER SET = utf8; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

      Description

      I am currently trying to use TEIID designer together with a mysql 8.0 database to import a source model. Unfortunately, foreign keys cannot be read correctly. I assume it has to do with the VISIBLE flag on the index. Is there a known workaround available for mysql 8.0?

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                cjohn001 Christoph John
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: