Foreignkey Creation Error

Question

Foreignkey Creation Error

Closed this issue 2 years ago · 7 comments

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;`

Answer 1 · 2022-08-05T14:53:50.000Z

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 <#99>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADHR7N3BH7QHKMIRIRNVX23VXTNMRANCNFSM55VN6WQQ> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

Answer 2 · 2022-08-07T19:57:46.000Z

@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?

Answer 3 · 2022-08-08T08:41:48.000Z

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;

Answer 4 · 2022-08-08T08:42:28.000Z

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

Answer 5 · 2022-08-08T12:14:22.000Z

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

Answer 6 · 2022-08-08T12:16:28.000Z

Thank you very much.

Answer 7 · 2022-08-08T21:50:21.000Z

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