Missing backquotes in ForeignKeyProcessor.ts generates error

[ced75]

Hi,

I'm using Nmig v5.2.0 to migrate a MySQL database to PostgreSQL.
Everything works like a charm, until an error occures in the foreign keys generaition.
The error is the following:

[ForeignKeyProcessor::default] Error: ER_UNKNOWN_TABLE: Unknown table 'referential_constraints' in information_schema

SQL: SELECT 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 = 'joomla-saa' 
        AND cols.TABLE_NAME = 'vol_personnel';

In ForeignKeyProcessor.ts, at lines 116 and 124, I believe that backquotes are missing around REFERENTIAL_CONSTRAINTS table name.

The corresponding lines in ForeignKeyProcessor.js are 72 and 80.

Kind regards,

Cedric

[ced75]

I think the error is more probably due to the old version of MySQL I'm trying to migrate from...
REFERENTIAL_CONSTRAINTS does not exist in INFORMATION_SCHEMA in version 5.0.45.

Is there a way to skip foreign keys creation?
What is supposed to be done after that operation?

[AnatolyUss]

Hey Cedric, Your assumption is correct. The REFERENTIAL_CONSTRAINTS column was introduced since version MySQL 5.1. There is no need to "skip" foreign key creation. In case of such error Nmig should just continue the process (it logs the error and proceeds to the next stage). "What is supposed to be done after that operation?" Nmig will attempt to migrate views, then it will run the VACUUM operation on each newly created table. You should check newly created PostgreSQL database to ensure that it meets your expectations. In case of issues, you can check migration log files, located under $ /path/to/nmig/logs_directory

…

On Thu, Feb 6, 2020 at 11:58 AM ced75 ***@***.***> wrote: I think the error is more probably due to the old version of MySQL I'm trying to migrate from... REFERENTIAL_CONSTRAINTS does not exist in INFORMATION_SCHEMA in version 5.0.45. Is there a way to skip foreign keys creation? What is supposed to be done after that operation? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#69?email_source=notifications&email_token=ADHR7NZRAFQBI37LE7VSENDRBPNLFA5CNFSM4KQZIDJKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEK6THPA#issuecomment-582824892>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADHR7NZJVTLKTBAFNOF7JOTRBPNLFANCNFSM4KQZIDJA> .