Foreignkey Creation Error

[TheCutter]

The query in ForeignKeyProcessor.ts returns multiple rows for the same foreign key. I wanted to prevent it with making it a SELECT DISTINCT, but now I got:

`--[ForeignKeyProcessor::default] Error: Out of memory (Needed 66016 bytes)

    SQL: SELECT DISTINCT cols.COLUMN_NAME, refs.REFERENCED_TABLE_NAME, refs.REFERENCED_COLUMN_NAME,
        cRefs.UPDATE_RULE, cRefs.DELETE_RULE, cRefs.CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.`COLUMNS` AS cols
        INNER JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` AS refs
        ON refs.TABLE_SCHEMA = cols.TABLE_SCHEMA
        AND refs.REFERENCED_TABLE_SCHEMA = cols.TABLE_SCHEMA
        AND refs.TABLE_NAME = cols.TABLE_NAME
        AND refs.COLUMN_NAME = cols.COLUMN_NAME
        LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS cRefs
        ON cRefs.CONSTRAINT_SCHEMA = cols.TABLE_SCHEMA
        AND cRefs.CONSTRAINT_NAME = refs.CONSTRAINT_NAME
        LEFT JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` AS links
        ON links.TABLE_SCHEMA = cols.TABLE_SCHEMA
        AND links.REFERENCED_TABLE_SCHEMA = cols.TABLE_SCHEMA
        AND links.REFERENCED_TABLE_NAME = cols.TABLE_NAME
        AND links.REFERENCED_COLUMN_NAME = cols.COLUMN_NAME
        LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS cLinks
        ON cLinks.CONSTRAINT_SCHEMA = cols.TABLE_SCHEMA
        AND cLinks.CONSTRAINT_NAME = links.CONSTRAINT_NAME
        WHERE cols.TABLE_SCHEMA = 'teg_sol'
        AND cols.TABLE_NAME = 'bas_tolerance';

node:internal/process/promises:279 triggerUncaughtException(err, true / fromPromise /); ^`

If I run the Importer without the DISTINCT there is no Out of Memory Error, but the foreign keys are not created correctly. This is the generated statement: ` --[ForeignKeyProcessor::processForeignKeyWorker] error: die Liste der Spalten, auf die ein Fremdschlüssel verweist, darf keine doppelten Einträge enthalten

SQL: ALTER TABLE "public"."qrtz_triggers" 
        ADD FOREIGN KEY ("SCHED_NAME","SCHED_NAME","SCHED_NAME","SCHED_NAME","JOB_NAME","JOB_GROUP") 
        REFERENCES "public"."qrtz_job_details" 
        ("SCHED_NAME","SCHED_NAME","SCHED_NAME","SCHED_NAME","JOB_NAME","JOB_GROUP") 
        ON UPDATE RESTRICT 
        ON DELETE RESTRICT;`

[AnatolyUss]

Thanks for posting the issue. I'll check it.

On Fri, Aug 5, 2022, 12:30 PM TheCutter @.***> wrote:

The query in ForeignKeyProcessor.ts returns multiple rows for the same foreign key. I wanted to prevent it with making it a SELECT DISTINCT, but now I got:

`--[ForeignKeyProcessor::default] Error: Out of memory (Needed 66016 bytes)

SQL: SELECT DISTINCT cols.COLUMN_NAME, refs.REFERENCED_TABLE_NAME, refs.REFERENCED_COLUMN_NAME,
    cRefs.UPDATE_RULE, cRefs.DELETE_RULE, cRefs.CONSTRAINT_NAME
    FROM INFORMATION_SCHEMA.`COLUMNS` AS cols
    INNER JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` AS refs
    ON refs.TABLE_SCHEMA = cols.TABLE_SCHEMA
    AND refs.REFERENCED_TABLE_SCHEMA = cols.TABLE_SCHEMA
    AND refs.TABLE_NAME = cols.TABLE_NAME
    AND refs.COLUMN_NAME = cols.COLUMN_NAME
    LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS cRefs
    ON cRefs.CONSTRAINT_SCHEMA = cols.TABLE_SCHEMA
    AND cRefs.CONSTRAINT_NAME = refs.CONSTRAINT_NAME
    LEFT JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` AS links
    ON links.TABLE_SCHEMA = cols.TABLE_SCHEMA
    AND links.REFERENCED_TABLE_SCHEMA = cols.TABLE_SCHEMA
    AND links.REFERENCED_TABLE_NAME = cols.TABLE_NAME
    AND links.REFERENCED_COLUMN_NAME = cols.COLUMN_NAME
    LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS cLinks
    ON cLinks.CONSTRAINT_SCHEMA = cols.TABLE_SCHEMA
    AND cLinks.CONSTRAINT_NAME = links.CONSTRAINT_NAME
    WHERE cols.TABLE_SCHEMA = 'teg_sol'
    AND cols.TABLE_NAME = 'bas_tolerance';

node:internal/process/promises:279 triggerUncaughtException(err, true / fromPromise /); ^`

— Reply to this email directly, view it on GitHub https://github.com/AnatolyUss/nmig/issues/99, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADHR7N3BH7QHKMIRIRNVX23VXTNMRANCNFSM55VN6WQQ . You are receiving this because you are subscribed to this thread.Message ID: @.***>

[AnatolyFromPerion]

@TheCutter I struggle to reproduce the issue. Could you send the structure of following tables: qrtz_triggers and qrtz_job_details, so I could reproduce your case?

[TheCutter]

DROP TABLE IF EXISTS qrtz_blob_triggers; CREATE TABLE qrtz_blob_triggers ( SCHED_NAME varchar(120) NOT NULL, TRIGGER_NAME varchar(200) NOT NULL, TRIGGER_GROUP varchar(200) NOT NULL, BLOB_DATA blob, PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), KEY SCHED_NAME (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), CONSTRAINT qrtz_blob_triggers_ibfk_1 FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) REFERENCES qrtz_triggers (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS qrtz_calendars; CREATE TABLE qrtz_calendars ( SCHED_NAME varchar(120) NOT NULL, CALENDAR_NAME varchar(200) NOT NULL, CALENDAR blob NOT NULL, PRIMARY KEY (SCHED_NAME,CALENDAR_NAME) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS qrtz_cron_triggers; CREATE TABLE qrtz_cron_triggers ( SCHED_NAME varchar(120) NOT NULL, TRIGGER_NAME varchar(200) NOT NULL, TRIGGER_GROUP varchar(200) NOT NULL, CRON_EXPRESSION varchar(120) NOT NULL, TIME_ZONE_ID varchar(80) DEFAULT NULL, PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), KEY SCHED_NAME (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), CONSTRAINT qrtz_cron_triggers_ibfk_1 FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) REFERENCES qrtz_triggers (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS qrtz_fired_triggers; CREATE TABLE qrtz_fired_triggers ( SCHED_NAME varchar(120) NOT NULL, ENTRY_ID varchar(95) NOT NULL, TRIGGER_NAME varchar(200) NOT NULL, TRIGGER_GROUP varchar(200) NOT NULL, INSTANCE_NAME varchar(200) NOT NULL, FIRED_TIME bigint(19) NOT NULL, PRIORITY int(11) NOT NULL, STATE varchar(16) NOT NULL, JOB_NAME varchar(200) DEFAULT NULL, JOB_GROUP varchar(200) DEFAULT NULL, IS_NONCONCURRENT tinyint(1) DEFAULT NULL, REQUESTS_RECOVERY tinyint(1) DEFAULT NULL, PRIMARY KEY (SCHED_NAME,ENTRY_ID), KEY IDX_QRTZ_FT_TRIG_INST_NAME (SCHED_NAME,INSTANCE_NAME), KEY IDX_QRTZ_FT_INST_JOB_REQ_RCVRY (SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY), KEY IDX_QRTZ_FT_J_G (SCHED_NAME,JOB_NAME,JOB_GROUP), KEY IDX_QRTZ_FT_JG (SCHED_NAME,JOB_GROUP), KEY IDX_QRTZ_FT_T_G (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), KEY IDX_QRTZ_FT_TG (SCHED_NAME,TRIGGER_GROUP) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS qrtz_job_details; CREATE TABLE qrtz_job_details ( SCHED_NAME varchar(120) NOT NULL, JOB_NAME varchar(200) NOT NULL, JOB_GROUP varchar(200) NOT NULL, DESCRIPTION varchar(250) DEFAULT NULL, JOB_CLASS_NAME varchar(250) NOT NULL, IS_DURABLE tinyint(1) NOT NULL, IS_NONCONCURRENT tinyint(1) NOT NULL, IS_UPDATE_DATA tinyint(1) NOT NULL, REQUESTS_RECOVERY tinyint(1) NOT NULL, JOB_DATA blob, PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP), KEY IDX_QRTZ_J_REQ_RECOVERY (SCHED_NAME,REQUESTS_RECOVERY), KEY IDX_QRTZ_J_GRP (SCHED_NAME,JOB_GROUP) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS qrtz_locks; CREATE TABLE qrtz_locks ( SCHED_NAME varchar(120) NOT NULL, LOCK_NAME varchar(40) NOT NULL, PRIMARY KEY (SCHED_NAME,LOCK_NAME) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS qrtz_paused_trigger_grps; CREATE TABLE qrtz_paused_trigger_grps ( SCHED_NAME varchar(120) NOT NULL, TRIGGER_GROUP varchar(200) NOT NULL, PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS qrtz_scheduler_state; CREATE TABLE qrtz_scheduler_state ( SCHED_NAME varchar(120) NOT NULL, INSTANCE_NAME varchar(200) NOT NULL, LAST_CHECKIN_TIME bigint(19) NOT NULL, CHECKIN_INTERVAL bigint(19) NOT NULL, PRIMARY KEY (SCHED_NAME,INSTANCE_NAME) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS qrtz_simple_triggers; CREATE TABLE qrtz_simple_triggers ( SCHED_NAME varchar(120) NOT NULL, TRIGGER_NAME varchar(200) NOT NULL, TRIGGER_GROUP varchar(200) NOT NULL, REPEAT_COUNT bigint(7) NOT NULL, REPEAT_INTERVAL bigint(12) NOT NULL, TIMES_TRIGGERED bigint(10) NOT NULL, PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), KEY SCHED_NAME (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), CONSTRAINT qrtz_simple_triggers_ibfk_1 FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) REFERENCES qrtz_triggers (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS qrtz_simprop_triggers; CREATE TABLE qrtz_simprop_triggers ( SCHED_NAME varchar(120) NOT NULL, TRIGGER_NAME varchar(200) NOT NULL, TRIGGER_GROUP varchar(200) NOT NULL, STR_PROP_1 varchar(512) DEFAULT NULL, STR_PROP_2 varchar(512) DEFAULT NULL, STR_PROP_3 varchar(512) DEFAULT NULL, INT_PROP_1 int(11) DEFAULT NULL, INT_PROP_2 int(11) DEFAULT NULL, LONG_PROP_1 bigint(20) DEFAULT NULL, LONG_PROP_2 bigint(20) DEFAULT NULL, DEC_PROP_1 decimal(13,4) DEFAULT NULL, DEC_PROP_2 decimal(13,4) DEFAULT NULL, BOOL_PROP_1 tinyint(1) DEFAULT NULL, BOOL_PROP_2 tinyint(1) DEFAULT NULL, PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), CONSTRAINT qrtz_simprop_triggers_ibfk_1 FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) REFERENCES qrtz_triggers (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS qrtz_triggers; CREATE TABLE qrtz_triggers ( SCHED_NAME varchar(120) NOT NULL, TRIGGER_NAME varchar(200) NOT NULL, TRIGGER_GROUP varchar(200) NOT NULL, JOB_NAME varchar(200) NOT NULL, JOB_GROUP varchar(200) NOT NULL, DESCRIPTION varchar(250) DEFAULT NULL, NEXT_FIRE_TIME bigint(19) DEFAULT NULL, PREV_FIRE_TIME bigint(19) DEFAULT NULL, PRIORITY int(11) DEFAULT NULL, TRIGGER_STATE varchar(16) NOT NULL, TRIGGER_TYPE varchar(8) NOT NULL, START_TIME bigint(19) NOT NULL, END_TIME bigint(19) DEFAULT NULL, CALENDAR_NAME varchar(200) DEFAULT NULL, MISFIRE_INSTR smallint(2) DEFAULT NULL, JOB_DATA blob, PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), KEY SCHED_NAME (SCHED_NAME,JOB_NAME,JOB_GROUP), KEY IDX_QRTZ_T_J (SCHED_NAME,JOB_NAME,JOB_GROUP), KEY IDX_QRTZ_T_JG (SCHED_NAME,JOB_GROUP), KEY IDX_QRTZ_T_C (SCHED_NAME,CALENDAR_NAME), KEY IDX_QRTZ_T_G (SCHED_NAME,TRIGGER_GROUP), KEY IDX_QRTZ_T_STATE (SCHED_NAME,TRIGGER_STATE), KEY IDX_QRTZ_T_N_STATE (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE), KEY IDX_QRTZ_T_N_G_STATE (SCHED_NAME,TRIGGER_GROUP,TRIGGER_STATE), KEY IDX_QRTZ_T_NEXT_FIRE_TIME (SCHED_NAME,NEXT_FIRE_TIME), KEY IDX_QRTZ_T_NFT_ST (SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME), KEY IDX_QRTZ_T_NFT_MISFIRE (SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME), KEY IDX_QRTZ_T_NFT_ST_MISFIRE (SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE), KEY IDX_QRTZ_T_NFT_ST_MISFIRE_GRP (SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE), CONSTRAINT qrtz_triggers_ibfk_1 FOREIGN KEY (SCHED_NAME, JOB_NAME, JOB_GROUP) REFERENCES qrtz_job_details (SCHED_NAME, JOB_NAME, JOB_GROUP) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[TheCutter]

The output of the query is:

COLUMN_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME,UPDATE_RULE,DELETE_RULE,CONSTRAINT_NAME SCHED_NAME,qrtz_job_details,SCHED_NAME,RESTRICT,RESTRICT,qrtz_triggers_ibfk_1 SCHED_NAME,qrtz_job_details,SCHED_NAME,RESTRICT,RESTRICT,qrtz_triggers_ibfk_1 SCHED_NAME,qrtz_job_details,SCHED_NAME,RESTRICT,RESTRICT,qrtz_triggers_ibfk_1 SCHED_NAME,qrtz_job_details,SCHED_NAME,RESTRICT,RESTRICT,qrtz_triggers_ibfk_1 JOB_NAME,qrtz_job_details,JOB_NAME,RESTRICT,RESTRICT,qrtz_triggers_ibfk_1 JOB_GROUP,qrtz_job_details,JOB_GROUP,RESTRICT,RESTRICT,qrtz_triggers_ibfk_1

[AnatolyFromPerion]

Now reproduced, thanks! I'll ping you once patch is available.

[TheCutter]

Thank you very much.

[AnatolyFromPerion]

@TheCutter The issue is fixed. Please, git-pull the latest master