I need to migrate a db to a new db with foreign key. How do I check for constraint errors?

Question

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?

Answer 1

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.

Answer 2

This is exactly what Test Databases are for.

Create one and try to load your data into it.

If it loads, great. If it doesn't then MySQL will tell you why it couldn't do it.

Answer 3

It might be a bit messy:

Plan A:

1. mysqldump --no-data > schema.txt
2. mysqldump --no-create-table > data.txt
3. mysql < schema.txt
4. ALTER TABLE ... ENGINE=InnoDB; -- for each table
5. mysql < data.txt -- will check the FK constraints as it inserts

But... If the tables are loaded in the 'wrong' order, there will be a lot of failures in step 5. So...

2. dump one table at a time
3. load the tables in the order so that parent tables get loaded before children.

Plan B:

On the same server:

1. CREATE DATABASE try;
2. (parent tables before children): For each table:
   • CREATE TABLE try.tbl LIKE real.tbl;
   • ALTER TABLE try.tbl ENGINE=InnoDB;
   • INSERT INTO try.tbl SELECT * FROM real.tbl;

Plan C:

On your existing MyISAM database, devise and run SELECTs that would find any FK errors. These would probably involve LEFT JOIN ... IS NULL (to look for missing rows.)

Suggest using a small LIMIT and/or wrapping with a SELECT COUNT(*).