Mysql – migrate a db to a new db with foreign key. How to check for constraint errors

MySQL

I need to migrate a lot of mysql MyISAM table to a new InnoDb version, adding foreign key constraints.
I can dump and import, disabling first foreign key checks.

But is there a way to check which tables have constraint problems (without checking manually all of them) when I activate again the foreign key check?

Best Answer

I don't know of any backup solution that respects the order of the tables in regards to foreign keys and doesn't simply disable foreign key checks when importing the data. Therefore you have to check for data inconsistencies yourself. You can do this for example with the following procedure:

DROP PROCEDURE IF EXISTS sp_checkFKIntegrity;
DELIMITER $$
CREATE PROCEDURE sp_checkFKIntegrity(
    IN p_schemaname varchar(255),
    IN p_tablename varchar(255)
)
BEGIN
    DECLARE v_table_schema varchar(255);
    DECLARE v_table_name varchar(255);
    DECLARE v_referenced_table_schema varchar(255);
    DECLARE v_referenced_table_name varchar(255);
    DECLARE v_column_name varchar(255);
    DECLARE v_referenced_column_name varchar(255);
    DECLARE v_constraint_name varchar(255);

    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR
        SELECT
        table_schema,
        table_name,
        referenced_table_schema,
        referenced_table_name,
        column_name,
        referenced_column_name,
        constraint_name
        FROM
        information_schema.key_column_usage k
        WHERE k.table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'common')
        AND referenced_table_name IS NOT NULL
        AND table_schema = COALESCE(p_schemaname, table_schema)
        AND table_name = COALESCE(p_tablename, table_name);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SELECT
    COUNT(*) INTO @countFKs
    FROM
    information_schema.key_column_usage k
    WHERE k.table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'common')
    AND referenced_table_name IS NOT NULL
    AND table_schema = COALESCE(p_schemaname, table_schema)
    AND table_name = COALESCE(p_tablename, table_name);

    SET @counter := 1;
    OPEN cur;
    cur_loop: LOOP
        FETCH cur INTO v_table_schema, v_table_name, v_referenced_table_schema, v_referenced_table_name, v_column_name, v_referenced_column_name, v_constraint_name;
        IF done THEN
            LEAVE cur_loop;
        END IF;
        SET @query := CONCAT('SELECT EXISTS(
            SELECT 1 FROM ', v_table_schema, '.', v_table_name, ' k 
            LEFT JOIN ', v_referenced_table_schema, '.', v_referenced_table_name, ' e ON k.', v_column_name, ' = e.', v_referenced_column_name, 
            ' WHERE e.', v_referenced_column_name, ' IS NULL) INTO @consistency;');

        SELECT CONCAT('Checking FK constraint ', @counter, ' of ', @countFKs, '...') AS info;
        SET @counter := @counter + 1;

        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        IF @consistency = 1 THEN
            SELECT CONCAT('Inconsistency found in foreign key "', v_table_schema, '.', v_table_name, '(', v_column_name, ') -> ', v_referenced_table_schema, '.', v_referenced_table_name, '(', v_referenced_column_name, ')", constraint_name "', v_constraint_name, '"') AS info;
            SELECT CONCAT('SELECT COUNT(*) FROM ', v_table_schema, '.', v_table_name, ' k LEFT JOIN ', v_referenced_table_schema, '.', v_referenced_table_name, ' e ON k.', v_column_name, ' = e.', v_referenced_column_name, 
            ' WHERE e.', v_referenced_column_name, ' IS NULL;') AS "Statement for further investigation";
        END IF;
    END LOOP;

    CLOSE cur;
    SELECT 'completed OK' AS info;
END $$
DELIMITER ;

The schema and table name parameters are optional, so you can execute the procedure like this:

CALL sp_checkFKIntegrity('playground', null); -- checks all FK constraints in the playground schema
CALL sp_checkFKIntegrity('playground', 'foo'); -- checks the FK constraints the table foo in the playground schema has to other (parent) tables
CALL sp_checkFKIntegrity(null, null); -- checks all FKs except the schemas mentioned in the procedure
CALL sp_checkFKIntegrity(null, 'foo'); -- checks the FK constraints the table foo has to other tables, no matter in which schema it is

What this procedure does not yet support is foreign keys with multiple columns. I was too lazy for that and although possible I haven't seen this in the wild.