Teiid Designer: Unable to import foreign keys from mysql db

[cjohn001]

Hello together,

I am trying to import a mysql database scheme as datasource with Teiid designer. Unfortunately, the importer always throws a warning  of type=Problem "The uniqueKey is required". After import all imported foreign keys are marked as defect, i.e. the Package Diagram in Teiid Designer does not show the relationships, just a red cross in the foreign key icon.

I am working with mysql 5.7 and 8.0 and tried  mysql 5.1.47 and 8.0.12 mysql drivers.  Has anybody experienced a similar situation and has a hint what I am doing wrong? Thanks for your help!

Best regards,

Christoph

[cjohn001]

Hello,

I found the first part of the problem, however, this does not solve my problem. If a am using mysql 8.0, the syntax from mysql workbench uses the keyword VISIBLE for the index. TEIID seems to be unable to resolve the foreign key relationships in this case. Even so I use the older syntax for mysql 5.7, i.e. without VISIBLE, TEIID cannot correctly read the foreign keys.  Here is an example. First version works for TEIID with mysql 5.7 database, second does not. Both variants do not work with mysql 8.0. As I would like to work with mysql 8.0 I am wondering if someone has an idea how I can circumvent the situation?

Is this a bug in TEIID designer and there is more trouble to expect if I go with mysql 8.0? Has someone experience using mysql 8.0 together with TEIID or should I better switch back to 5.7?

-- -----------------------------------------------------

-- 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),

  CONSTRAINT `fkCountry`

    FOREIGN KEY (`fkCountry`)

    REFERENCES `IMS_db`.`Country` (`idCountry`))

ENGINE = InnoDB

AUTO_INCREMENT = 2

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;

[rareddy]

Teiid uses JDBC driver to read the metadata, not DDL. So, I suggest you find the correct JDBC driver versions designed for 8.0 and try again.

[cjohn001]

Hello Ramesh,

I just double checked. I am using the latest jdbc driver together with the database. Mysql db version is 8.0.11 and the jdbc driver I am using is 8.0.12. Have you experience with using mysql v8 together with Teiid? I mean, is it known to be working?

I have just seen that today mysql driver 8.0.13 came out. Just checked with this version, still does not work for me.  For each imported foreign key I get the error "The uniqueKey is required". However, I get this error irrespectiv of the flags in the database. Same behavior for foreign key with and without unique set.

[cjohn001]

Nobody in the forum who is using a V8 mysql database with teiid designer?

[rareddy]

Looks like MySQL 8 is brand new so usage might be low at this point. Also not everyone reads forums or care enough to answer

As Steve suggested in another forum, have you tried using "Teiid Connection Importer" for importing rather than using JDBC Importer?

[cjohn001]

Hello Ramesh,

thanks for the feedback. I am currently trying the approach Steve suggested. However, I have not had success yet. When I go for the suggested route, I have to set a data source on Wildfly first. Seems a bug in eclipse is hitting me here as I do not get the option to provide the data source properties. According to the attached image

I miss editable fields in the lower table. My view looks like the following, and Data Soure Properties is not editable:

I also tried to set up the data source via wildfly, also not successful yet. I have not found an answer to the question yet on how I have to setup the Security Domain.If it is empty, the connection also fails with

Unexpected HTTP response: 500

Request

{

  "address" => [

  ("subsystem" => "datasources"),

  ("data-source" => "test")

  ],

  "operation" => "test-connection-in-pool"

}

Response

Internal Server Error

{

  "outcome" => "failed",

  "failure-description" => "WFLYJCA0040: failed to invoke operation: WFLYJCA0047: Connection is not valid",

  "rolled-back" => true

}

Do you have a helpful hint for the Security Domain topic?

[rareddy]

Make sure you have the server running and Admin connection is working correctly before using this option.

[cjohn001]

Well,

I used now the jdbc connection guide to create a data source. However, when I take the route via Import/Teiid Connection>>>Source Model I get the following error

VDB DEPLOYMENT ERRORS:

TEIID60013 Duplicate Table Avatar

Error deploying "importVDB" - please check the server log for more details.

The deployment timeout can be increased on the previous page (Advanced tab), or check the server log for other errors.

And in the log file:

Problems occurred when invoking code from plug-in: "org.eclipse.jface".

Looks like I stay with mysql 5 . 4 weeks trial and error now to get wildfly up with odata Gets time for some programming. With mysql5 I have wildfly now up and running. Do you have a helpful tutorial at hand, how I can configure teiid together with keycloak. I would like to have table read/write constraints based on a users group and also row based read/write constraints based on the users ID. I want to limit inserts and updates of a user to certain values. For example, if a user adds a comment in a comment table, this comment should include his ID as foreign key. Teiid than would need to check that his ID equals the ID of the foreign key he provided. Second example. If a user changes a row with config options. This row will include his foreign key. He should only be able to update or read this row if his ID matches the foreign key.

[cjohn001]

connections were active and admin connection did work. Was unfortunately not the issue.

[rareddy]

For WildFly server you can try OAuth2 Based Security For OData Using KeyCloak · GitBook

[cjohn001]

thx

[cjohn001]

Hello Ramesh,

I just run into the next issue. Maybe you have a hint here. When I build a vdb against mysql 5 I can deploy the vdb from eclipse without a problem. In the past I have just build a vdb.xml from the vdb and deployed it by copying it into my teiid docker container. I have no idea what I should have done different in the past. However, now the vdb still seems to be ok and can be deployed via Eclipse, but if I build a vdb.xml for deployment it has errors. The Teiid server gives the following error message:

2018-11-10 17:26:47,667 WARN  [org.teiid.RUNTIME] (Worker8_async-teiid-threads13)  TEIID50036 VDB svc.1 model "my_nutri_diary" metadata failed to load. Reason:TEIID31259 In the statement ending with token teiid_mysql on line 3 column 61 the ddl is not valid: TEIID60017 Invalid prefix teiid_mysql, teiid_ is reserved for Teiid use.: org.teiid.metadata.ParseException: TEIID31259 In the statement ending with token teiid_mysql on line 3 column 61 the ddl is not valid: TEIID60017 Invalid prefix teiid_mysql, teiid_ is reserved for Teiid use.

at org.teiid.query.parser.QueryParser.parseDDL(QueryParser.java:554)

at org.teiid.query.parser.QueryParser.parseDDL(QueryParser.java:534)

at org.teiid.metadata.MetadataFactory.parse(MetadataFactory.java:757)

at org.teiid.query.metadata.DDLMetadataRepository.loadMetadata(DDLMetadataRepository.java:36)

at org.teiid.runtime.AbstractVDBDeployer$MetadataRepositoryWrapper.loadMetadata(AbstractVDBDeployer.java:82)

at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:51)

at org.teiid.jboss.VDBService$6.run(VDBService.java:338)

at org.teiid.jboss.VDBService$7.run(VDBService.java:389)

at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:278)

at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)

at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

at java.lang.Thread.run(Thread.java:748)

Caused by: org.teiid.metadata.MetadataException: TEIID60017 Invalid prefix teiid_mysql, teiid_ is reserved for Teiid use.

at org.teiid.metadata.NamespaceContainer.addNamespace(NamespaceContainer.java:97)

at org.teiid.metadata.Database.addNamespace(Database.java:277)

at org.teiid.query.metadata.DatabaseStore.createNameSpace(DatabaseStore.java:626)

at org.teiid.query.parser.SQLParser.createNameSpace(SQLParser.java:13692)

at org.teiid.query.parser.SQLParser.ddlStmt(SQLParser.java:13614)

at org.teiid.query.parser.SQLParser.parseMetadata(SQLParser.java:13547)

at org.teiid.query.parser.QueryParser.parseDDL(QueryParser.java:548)

... 13 more

I also attach the xml here.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<vdb name="svc" version="1">

    <description/>

    <property name="validationDateTime" value="Sat Nov 10 18:24:24 CET 2018"/>

    <property name="validationVersion" value="11.2.0"/>

    <model name="my_nutri_diary">

        <source connection-jndi-name="java:/my_nutri_diary" name="my_nutri_diary" translator-name="mysql"/>

        <metadata type="DDL">

            <![CDATA[

SET NAMESPACE 'http://www.teiid.org/ext/relational/2012' AS teiid_mysql

CREATE FOREIGN TABLE Account (

  uuidUser string(36) NOT NULL OPTIONS(NAMEINSOURCE '`uuidUser`', NATIVE_TYPE 'CHAR'),

  idProfile long NOT NULL AUTO_INCREMENT OPTIONS(NAMEINSOURCE '`idProfile`', NATIVE_TYPE 'BIGINT UNSIGNED'),

  CONSTRAINT "PRIMARY" PRIMARY KEY(uuidUser),

  CONSTRAINT idProfile_UNIQUE UNIQUE(idProfile)

) OPTIONS(NAMEINSOURCE '`Account`', UPDATABLE 'TRUE', "teiid_mysql:fqn" 'catalog=my_nutri_diary/table=Account', "teiid_mysql:source_type" 'TABLE');

CREATE FOREIGN TABLE Avatar (

  fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '`fkProfile`', NATIVE_TYPE 'BIGINT UNSIGNED'),

  AvatarImg blob(65535) OPTIONS(NAMEINSOURCE '`AvatarImg`', NATIVE_TYPE 'BLOB'),

  CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile),

  CONSTRAINT fkProfileInAvatar FOREIGN KEY(fkProfile) REFERENCES Account(idProfile)

) OPTIONS(NAMEINSOURCE '`Avatar`', UPDATABLE 'TRUE', "teiid_mysql:fqn" 'catalog=my_nutri_diary/table=Avatar', "teiid_mysql:source_type" 'TABLE');

CREATE FOREIGN TABLE BodyWeight (

  idBodyWeight long NOT NULL OPTIONS(NAMEINSOURCE '`idBodyWeight`', NATIVE_TYPE 'BIGINT UNSIGNED', ANNOTATION '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 long NOT NULL OPTIONS(NAMEINSOURCE '`fkProfile`', NATIVE_TYPE 'BIGINT UNSIGNED'),

  WeightMeasurementDateTime timestamp NOT NULL OPTIONS(NAMEINSOURCE '`WeightMeasurementDateTime`', NATIVE_TYPE 'DATETIME'),

  Weight short NOT NULL OPTIONS(NAMEINSOURCE '`Weight`', NATIVE_TYPE 'TINYINT UNSIGNED'),

  CONSTRAINT "PRIMARY" PRIMARY KEY(idBodyWeight),

  CONSTRAINT fkProfileInBodyWeight FOREIGN KEY(fkProfile) REFERENCES Account(idProfile)

) OPTIONS(NAMEINSOURCE '`BodyWeight`', UPDATABLE 'TRUE', "teiid_mysql:fqn" 'catalog=my_nutri_diary/table=BodyWeight', "teiid_mysql:source_type" 'TABLE');

CREATE FOREIGN TABLE Profile (

  fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '`fkProfile`', NATIVE_TYPE 'BIGINT UNSIGNED'),

  BodyHeight long OPTIONS(NAMEINSOURCE '`BodyHeight`', NATIVE_TYPE 'INT UNSIGNED'),

  Gender string(1) OPTIONS(NAMEINSOURCE '`Gender`', NATIVE_TYPE 'ENUM'),

  BirthDate date OPTIONS(NAMEINSOURCE '`BirthDate`', NATIVE_TYPE 'DATE'),

  CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile),

  CONSTRAINT fkProfileInBiometricProfile FOREIGN KEY(fkProfile) REFERENCES Account(idProfile)

) OPTIONS(NAMEINSOURCE '`Profile`', UPDATABLE 'TRUE', "teiid_mysql:fqn" 'catalog=my_nutri_diary/table=Profile', "teiid_mysql:source_type" 'TABLE');

]]>

        </metadata>

    </model>

</vdb>

One more question. is there a different option how I could deploy the vdb in my docker container, without going via the vdb.xml approach?

[cjohn001]

I have just seen, that in my old vdb.xml no namespace was created. The following line and all teiid_mysql5 prefixes were not included in my old files. Can you tell me how I can disable the generation of this namespace?

SET NAMESPACE 'http://www.teiid.org/ext/relational/2012' AS teiid_mysql5

[cjohn001]

OK,

I found the solution. I have used Teiid Designer against Teiid 11.2 instead of 9.0. This was my mistake. Against V9 things are like expected.