[BUG]Failed to create dataset in Kylin MDX

[sandeepvinay]

Describe the bug
Error creating a dataset in Kylin MDX

`[MDX-00000001] Internal Error, ERROR: null value in column "type" of relation "dataset" violates not-null constraint
Detail: Failing row contains (22, learn_kylin, demo, null, {"models":[{"x":214,"y":105,"top":[],"right":[],"bottom":[],"lef..., v0.4, NORMAL, null, ADMIN, 1672896104, 1672896104, {"model_aliases":[{"alias":"kylin_sales_cube","name":"kylin_sale..., [], 0).
org.springframework.dao.DataIntegrityViolationException:

Error updating database. Cause: org.postgresql.util.PSQLException: ERROR: null value in column "type" of relation "dataset" violates not-null constraint

Detail: Failing row contains (22, learn_kylin, demo, null, {"models":[{"x":214,"y":105,"top":[],"right":[],"bottom":[],"lef..., v0.4, NORMAL, null, ADMIN, 1672896104, 1672896104, {"model_aliases":[{"alias":"kylin_sales_cube","name":"kylin_sale..., [], 0).

The error may exist in class path resource [io/kylin/mdx/insight/core/dao/xml/DatasetMapper.xml]

The error may involve io.kylin.mdx.insight.core.dao.DatasetMapper.insertOneReturnId-Inline

The error occurred while setting parameters`

To Reproduce
Steps to reproduce the behavior:
Followed Quickly Start With MDX for Kylin documentation to setup sample dataset.
https://kylin.apache.org/docs/tutorial/quick_start_for_mdx.html

Expected behavior
Create dataset successfully

Screenshots

Additional context
We are using Kylin-4.0.3 with Postgres as Kylin metastore. We were able to create MDX dataset with Mysql as kylin metastore but not with Postgres.

[srroh1]

@Mukvin Do you have any idea on this issue? We are trying to create a dataset in MDX getting PSQL exception. And We were able to create the same dataset with MYSQL as a metastore.
We are using Kylin 4.0.3 with updated PostgreSQL updates, everything works fine with Kylin. We are seeing issues with MDX thought you might have an idea on this ?

[Mukvin]

@srroh1 ,
As I tested before only use MySQL as metadata of MDX.
So if you want to PG as metadata of MDX, there may have a gap.
I suggest that you use Postgresql as the metadata of KYLIN, but use MYSQL as the metadata of MDX.
And I will check this error.

[srroh1]

@Mukvin I really thank you for looking into this, we have almost finalized the configs for Kylin + Postgres it was working as expected, But when it comes to MDX we are getting an issue as described above. For time being we can use MySQL as metadata for MDX but this will not fit in our tech as a long-term solution.
Can we expect this anytime soon (MDX + PSQL)?

please let me know if we can help with testing

Thanks
Rohan

[Mukvin]

@srroh1 ,
Don't worry about this, I need to check this error, and May be a week to support MDX + PSQL.

[shaofengshi]

Hello Rohan, Kylin MDX service only support MySQL 5.6.x, 5.7.x as the metadata store (at least at this moment). If PG is a must-to-have feature for you, we'd like to suggest you develop this function, and then contribute back, this is how open source project runs. Any contribution is welcomed!

You can new another issue, can select "feature request" as the issue type, then enter the requirement there, so that it can be tracked.

[sandeepvinay]

@Mukvin We are using Postgresql as the metadata of KYLIN, and MYSQL as the metadata of MDX like you suggested and it is working as expected, thanks you!.

Did you get a chance to look on MDX + PSQL support? if yes, can we expect this anytime soon?

[roh3]

@Mukvin We finally figured out the issue by changing the "Nullable" value for the dataset

Here is the command we executed on PG14 and everything is working now
kylinmdx=> ALTER TABLE dataset ALTER COLUMN type DROP NOT NULL;