[
https://issues.jboss.org/browse/TEIIDDES-3217?page=com.atlassian.jira.plu...
]
Christoph John edited comment on TEIIDDES-3217 at 11/2/18 3:04 PM:
-------------------------------------------------------------------
Hallo Steven,
I played now for a while with the different Teiid importer settings in hope to get the
model running with teiid. However, still without success. I am not sure if it is a single
issue or several with the database.
As a matter of principe I have two schemes in the database and during testing made several
observations.
- Partly, tables have the same name in the different schemas. Seems this results in
conflicts during import. I played around with the catalog option. This resulted in the
correct tables selected. But for one table still a foreign key was missing.
- I than removed one database. The exported source model looked ok at the beginning.
However, the deployed model is not running at all. If I try to access the tables I just
get errors. For example:
<error
xmlns="http://docs.oasis-open.org/odata/ns/metadata">
<code>TEIID30504</code>
<message>
TEIID30504 my_nutri_diary: 1046 TEIID11008:TEIID11004 Error executing statement(s):
[Prepared Values: [] SQL: SELECT g_0.`fkProfile` AS c_0, g_0.`AvatarImg` AS c_1 FROM
`Avatar` AS g_0 WHERE g_0.`fkProfile` = 1 ORDER BY c_0]
</message>
</error>
when reading a table entry. As I have not found an option to attach a file in the
following I will attach my complete db. I hope that you can reproduce the issue with the
full model. If you are interested I could also provide a docker image with the database.
However, I would need a place for upload.
Best regards,
Christoph
-- 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 service_center
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema service_center
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `service_center` DEFAULT CHARACTER SET utf8mb4 COLLATE
utf8mb4_unicode_ci ;
-- -----------------------------------------------------
-- Schema my_nutri_diary
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema my_nutri_diary
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `my_nutri_diary` DEFAULT CHARACTER SET utf8mb4 COLLATE
utf8mb4_unicode_ci ;
USE `service_center` ;
-- -----------------------------------------------------
-- Table `service_center`.`Profile`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `service_center`.`Profile` (
`idProfile` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`uuidUser` CHAR(36) CHARACTER SET 'ascii' BINARY NOT NULL,
PRIMARY KEY (`idProfile`),
UNIQUE INDEX `uuidUser_UNIQUE` (`uuidUser` ASC) VISIBLE)
ENGINE = InnoDB
COMMENT = 'Table stores a mapping between user profiles and uuids of keycloak
accounts';
-- -----------------------------------------------------
-- Table `service_center`.`Avatar`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `service_center`.`Avatar` (
`fkProfile` BIGINT UNSIGNED NOT NULL,
`AvatarImg` BLOB NULL,
UNIQUE INDEX `fkProfile_UNIQUE` (`fkProfile` ASC) VISIBLE,
PRIMARY KEY (`fkProfile`),
CONSTRAINT `fkProfileInAvatar`
FOREIGN KEY (`fkProfile`)
REFERENCES `service_center`.`Profile` (`idProfile`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
USE `my_nutri_diary` ;
-- -----------------------------------------------------
-- Table `my_nutri_diary`.`Profile`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `my_nutri_diary`.`Profile` (
`idProfile` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`uuidUser` CHAR(36) CHARACTER SET 'ascii' BINARY NOT NULL,
PRIMARY KEY (`idProfile`),
UNIQUE INDEX `uuidUser_UNIQUE` (`uuidUser` ASC) VISIBLE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `my_nutri_diary`.`Avatar`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `my_nutri_diary`.`Avatar` (
`fkProfile` BIGINT UNSIGNED NOT NULL,
`AvatarImg` BLOB NULL,
UNIQUE INDEX `fkProfile_UNIQUE` (`fkProfile` ASC) VISIBLE,
PRIMARY KEY (`fkProfile`),
CONSTRAINT `fkProfileInAvatar`
FOREIGN KEY (`fkProfile`)
REFERENCES `my_nutri_diary`.`Profile` (`idProfile`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `my_nutri_diary`.`BiometricProfile`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `my_nutri_diary`.`BiometricProfile` (
`fkProfile` BIGINT UNSIGNED NOT NULL,
`Age` INT UNSIGNED NULL,
`BodyHeight` INT UNSIGNED NULL,
`Gender` ENUM('m', 'w') NULL,
`BirthDate` DATE NULL,
INDEX `fkProfileInBiometricProfile_idx` (`fkProfile` ASC) VISIBLE,
UNIQUE INDEX `fkProfile_UNIQUE` (`fkProfile` ASC) VISIBLE,
PRIMARY KEY (`fkProfile`),
CONSTRAINT `fkProfileInBiometricProfile`
FOREIGN KEY (`fkProfile`)
REFERENCES `my_nutri_diary`.`Profile` (`idProfile`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `my_nutri_diary`.`BodyWeight`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `my_nutri_diary`.`BodyWeight` (
`idBodyWeight` BIGINT UNSIGNED NOT NULL COMMENT 'We need a surrogate key here as
Teiid requires a primary key on each table. fkProfile is not unique here as we can will
have multiple weight measurements per person. Moreover, combining it with
WeightMeasurementDateTime makes the index to compute intensive.',
`fkProfile` BIGINT UNSIGNED NOT NULL,
`WeightMeasurementDateTime` DATETIME NOT NULL,
`Weight` TINYINT UNSIGNED NOT NULL,
INDEX `fkProfileInBodyWeight_idx` (`fkProfile` ASC) VISIBLE,
PRIMARY KEY (`idBodyWeight`),
CONSTRAINT `fkProfileInBodyWeight`
FOREIGN KEY (`fkProfile`)
REFERENCES `my_nutri_diary`.`Profile` (`idProfile`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
was (Author: cjohn001):
Hallo Steven,
I played now for a while with the different Teiid importer settings in hope to get the
model running with teiid. However, still without success. I am not sure if it is a single
issue or several with the database.
Error reading Foreign Keys from Mysql 8.0
-----------------------------------------
Key: TEIIDDES-3217
URL:
https://issues.jboss.org/browse/TEIIDDES-3217
Project: Teiid Designer
Issue Type: Bug
Components: Teiid Integration
Affects Versions: 11.1.3
Reporter: Christoph John
Priority: Major
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?
--
This message was sent by Atlassian Jira
(v7.12.1#712002)