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

Error reading Foreign Keys from Mysql 8.0

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • None
    • 11.1.3
    • Teiid Integration
    • None
    • 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?

      Attachments

        Activity

          People

            Unassigned Unassigned
            cjohn001 Christoph John (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: