MySQL error with edoras 2.0.6


#1

Hello,

I try to upgrade from edoras 1.6.13 to edoras 2.0.6. If I start my custom code project with edoras 2.0.6 and an empty MySQL database the following error occurs: “Failed to add the foreign key constraint on table ‘act_procdef_info’. Incorrect options in FOREIGN KEY constraint ‘edorasdev_2_x_empty/ACT_FK_INFO_JSON_BA’ (SQL State: HY000 - Error Code: 1825)”. At the same time I can start my project with an empty h2 database without problem. Could you help me solve this problem, please?

The full exception stack trace is attached.

Thank you.

Best regards,
Olga

FaildToAddForeignKeyConstraint_ExceptionStackTrace_edoras2_0_6.zip (3.5 KB)


#2

Hello Olga,

Usually flyway errors are due to local database changes that affect the regular database migration path. What do you have as initial database state?

Regards,
José


#3

Hello José,

I use an empty database for my tests. I create the new schema on mySQL server. Then I start the tomcat with my custom code project. You can see the schema details in the attached screenshot.

Thank you and best regards,
Olga

SchemaDetails


#4

Hello Olga, can you execute the following statement after the exception appears?

SHOW ENGINE innodb STATUS

Thanks and regards,
José.


#5

Hello José,

you can view the result of the statement in the attached file.

Best regards,
Olga

SHOW _ENGINE_innodb_STATUS.zip (2.3 KB)


#6

Hi Olga.

The problem which I see there is
LATEST FOREIGN KEY ERROR
------------------------
2019-02-15 11:09:45 0x1dcc Error in foreign key constraint of table edorasdev_2_x_empty/#sql-e08_5:

    foreign key (INFO_JSON_ID_)
    references ACT_GE_BYTEARRAY (ID_):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.
------------

the problem could be that

create table ACT_PROCDEF_INFO (
 ID_ varchar(64) not null,
    PROC_DEF_ID_ varchar(64) not null,
    REV_ integer,
    INFO_JSON_ID_ varchar(64),
    primary key (ID_)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;

create index ACT_IDX_INFO_PROCDEF on ACT_PROCDEF_INFO(PROC_DEF_ID_);

alter table ACT_PROCDEF_INFO
    add constraint ACT_FK_INFO_JSON_BA
    foreign key (INFO_JSON_ID_)
    references ACT_GE_BYTEARRAY (ID_);

is created in a different way as referenced column

create table ACT_GE_BYTEARRAY (
    ID_ varchar(64),
    REV_ integer,
    NAME_ varchar(255),
    DEPLOYMENT_ID_ varchar(64),
    BYTES_ LONGBLOB,
    GENERATED_ TINYINT,
    primary key (ID_)
) ENGINE=InnoDB;

I can not check whether it is the case. Could you recreate the ACT_GE_BYTEARRAY table with ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin and run upgrade again please?
Please let us know whether it helps.
Thank you for your answer,

Regards
Martin


#7

Hello Martin,

I can’t recreate the table manually after running the upgrade, because the table already exists. And I can’t create the table manually before running the upgrade, because the flyway error Found non-empty schema 'edorasdev_2_x_empty' without metadata table! occurs.

I’ve noticed, that my database has utf8_general_ci collation, but edoras creates the ACT_PROCDEF_INFO table with ut8_bin collation. Maybe this is the problem? After seeing this, I created the empty database with utf8_bin collation und then ran the upgrade again. Unfortunatily an exception occurs on removing external id constraint (java.lang.IllegalStateException: The column WRK_OBJ_EXTERNAL_ID in table EDW_GEAR_WRK_OBJ still has the WRK_OBJ_EXTERNAL_ID index/constraint. Please remove it manually.). The new exception stack is attached. If I try to remove the constraint manually with ALTER TABLE EDW_GEAR_WRK_OBJ DROP FOREIGN KEY WRK_OBJ_EXTERNAL_ID; or with ALTER TABLE EDW_GEAR_WRK_OBJ DROP index WRK_OBJ_EXTERNAL_ID; I get error check that column/key exists.

Thank you and best regards,
Olga

Error_RemoveExternalIdConstraint_UpgradeEdoras2.x.txt.zip (3.4 KB)


#8

Hi Olga.

Our internal MySql 5.7.19 tests are doing exactly the same. They creates MySql DB from scratch. The default collation settings for edorasware database is utf8_bin. That’s why I do not understand why there is an illegal state exception

( java.lang.IllegalStateException: The column WRK_OBJ_EXTERNAL_ID in table EDW_GEAR_WRK_OBJ still has the WRK_OBJ_EXTERNAL_ID index/constraint. Please remove it manually. )

Could you point me to the e.g. MySql docker image (to have exactly the same DB config as you are using) on which I could test this failure please?
Thank you in advance,
Regards
Martin
Thank you in advance.
Regards,
Martin


#9

Hello Martin,

unfortunatily I can’t give you a mySQL docker image, but I attached the my.ini config file of our MySQL server. Maybe you can reproduce the error with this. We use MySQL Community Server 5.7.23 on Windows Server 2012 R2.

Thank you and best regards,
Olga

my.zip (5.1 KB)


#10

Olga,
could you create an emtpy DB with utf8_bin?
create database edorasone character set utf8 collate utf8_bin
and try to run edorasone on this DB.

Thanks
Martin


#11

Hello Martin,

I’m already doing that, but the following error occurs every time:

Caused by: java.lang.IllegalStateException: The column WRK_OBJ_EXTERNAL_ID in table EDW_GEAR_WRK_OBJ still has the WRK_OBJ_EXTERNAL_ID index/constraint. Please remove it manually.

Best regards,
Olga


#12

Hi Olga,

  1. I was able to reproduce the issue with create database edorasone character set utf8 collate utf8_general_ci;
    Migration edoras-gear_1.2.0_Flowable_5_22.sql failed
    ----------------------------------------------------
    SQL State  : HY000
    Error Code : 1215
    Message    : Cannot add foreign key constraint
    Location   : com/edorasware/commons/core/persistence/schema/mysql/edoras-gear_1.2.0_Flowable_5_22.sql (/opt/apache-tomcat-8.5.34/bin/file:/home/martin/flowable/edoras-two/edoras-one-hosted/build/libs/exploded/edoras-one-hosted-2.0.8.war/WEB-INF/lib/com.edorasware.one-edoras-commons-core-2.0.8.jar!/com/edorasware/commons/core/persistence/schema/mysql/edoras-gear_1.2.0_Flowable_5_22.sql)
    Line       : 19
    Statement  : alter table ACT_PROCDEF_INFO
        add constraint ACT_FK_INFO_JSON_BA
        foreign key (INFO_JSON_ID_)
        references ACT_GE_BYTEARRAY (ID_)
  1. When I dropped and re-created database with create database edorasone character set utf8 collate utf8_bin;, I applied baseline and granted all privileges to the user, edorasone was started without any error.

Could you provide a description how to reproduce

Caused by: java.lang.IllegalStateException: The column WRK_OBJ_EXTERNAL_ID in table EDW_GEAR_WRK_OBJ still has the WRK_OBJ_EXTERNAL_ID index/constraint. Please remove it manually.

issue?

Thank you in advance.
Regards,
Martin


#13

Hello Martin,

I have noticed that the error does not occur after reinstalling mySQL. The error is still reproducible on all our old servers. I will compare the preferences of our new and old servers to find out which of them are causing the error. I will inform you when I find the reason.

Best regards,
Olga