Can I check a database for existing constraint violations (Getting unique constraint violation on production, cannot reproduce it locally)

Question

In a Magento 2 shop I am getting a unique constraint violation on production which I cannot debug, but reproduce there.

I exported the full live database, imported locally and cannot reproduce it any more there.

My current working theory is, that the production database is inconsistent and exporting + importing fixes the problem.

How can I verify this thesis? Is there a command which can check a current MySQL database for any existing constraint violations?

EDIT: My problem seems to be on Magento level somehow... see https://magento.stackexchange.com/questions/277627/possible-bug-in-and-handling-of-getorigdata-when-updating-tier-prices

Answer 1

Assuming that you verified the query below returns false for the keys you suspected:

SELECT
    COUNT(0) <> 0 AS KeyViolated
FROM (
    SELECT
        NULL
    FROM my_schema.my_table
    GROUP BY unique_, key, columns
    HAVING (COUNT(0) > 1) ) A
;

I know that one can list FK violations using this answer. For verifying key constraints one could use the query from here and regular group by & having count query:

DROP PROCEDURE IF EXISTS sp_validate_keys;
DROP PROCEDURE IF EXISTS statement;

DELIMITER $$
CREATE PROCEDURE statement(IN dynamic_statement TEXT)
BEGIN
      SET @dynamic_statement := dynamic_statement;
      PREPARE prepared_statement FROM @dynamic_statement;
      EXECUTE prepared_statement;
      DEALLOCATE PREPARE prepared_statement;
  END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_validate_keys()
BEGIN
    DECLARE var_cur_idx INTEGER UNSIGNED DEFAULT 1;
    DECLARE var_length INTEGER UNSIGNED DEFAULT 0;

    DECLARE var_schema_name VARCHAR(64);
    DECLARE var_table_name VARCHAR(64);
    DECLARE var_column_names VARCHAR(255);
    DECLARE var_cur_statement TEXT;

    CREATE OR REPLACE VIEW v_db_table_key AS
    select stat.table_schema as database_name,
           stat.table_name,
           -- stat.index_name,
           group_concat(stat.column_name
                order by stat.seq_in_index separator ', ') as columns
        -- , tco.constraint_type
    from information_schema.statistics stat
    join information_schema.table_constraints tco
         on stat.table_schema = tco.table_schema
         and stat.table_name = tco.table_name
         and stat.index_name = tco.constraint_name
    where stat.non_unique = 0
          and stat.table_schema not in ('information_schema', 'sys',
                                        'performance_schema', 'mysql')
    group by stat.table_schema,
             stat.table_name,
             stat.index_name,
             tco.constraint_type
    order by stat.table_schema,
             stat.table_name
    ;
    SET var_length := (SELECT COUNT(0) FROM v_db_table_key);

    DROP TEMPORARY TABLE IF EXISTS tmp_db_table_key_idx;
    CREATE TEMPORARY TABLE tmp_db_table_key_idx AS
    SELECT
        v.*,
        (@cnt := @cnt + 1) AS idx
    FROM v_db_table_key v
    CROSS JOIN (SELECT @cnt := 0) _
    ;

    DROP TEMPORARY TABLE IF EXISTS tmp_key_validation;
    CREATE TEMPORARY TABLE tmp_key_validation AS (SELECT * FROM tmp_db_table_key_idx LIMIT 0);

    WHILE (var_cur_idx <= var_length) DO

        SET var_schema_name := (
            SELECT database_name FROM tmp_db_table_key_idx WHERE idx = var_cur_idx LIMIT 1);
        SET var_table_name := (
            SELECT table_name FROM tmp_db_table_key_idx WHERE idx = var_cur_idx  LIMIT 1);
        SET var_column_names := (
            SELECT columns FROM tmp_db_table_key_idx WHERE idx = var_cur_idx  LIMIT 1);
        SET var_cur_statement := CONCAT('
        INSERT INTO tmp_key_validation
        SELECT
            \'', var_schema_name, '\' AS SchemaName,
            \'', var_table_name, '\' AS TableName,
            \'', var_column_names, '\' AS KeyColumns,
            COUNT(0) <> 0 AS KeyViolated
        FROM (
            SELECT
                NULL
            FROM ', var_schema_name, '.', var_table_name, '
            GROUP BY ', var_column_names, '
            HAVING (COUNT(0) > 1)
            ) A
        ;
        ')
        ;
        CALL statement(var_cur_statement);

        SET var_cur_idx := var_cur_idx + 1;
    END WHILE;
    -- SELECT var_cur_statement;
    DROP VIEW IF EXISTS v_db_table_key;
    SELECT * FROM tmp_key_validation WHERE idx = TRUE;
END $$

DELIMITER ;

CALL sp_validate_keys();
DROP PROCEDURE IF EXISTS sp_validate_keys;
DROP PROCEDURE IF EXISTS statement;