mysql - 我可以检查数据库是否存在现有的约束违规(在生产中获取唯一的约束违规，无法在本地重现)

Question

在 Magento 2 商店中，我在生产中遇到了独特的约束违规，我无法调试，但可以在那里重现。

我导出了完整的实时数据库，在本地导入，并且无法再在那里重现它。

我目前的工作理论是，生产数据库不一致，导出+导入可以解决问题。

如何验证这篇论文？是否有一个命令可以检查当前 MySQL 数据库是否存在任何违反约束的情况？

编辑:我的问题似乎在 Magento 级别上......请参阅 https://magento.stackexchange.com/questions/277627/possible-bug-in-and-handling-of-getorigdata-when-updating-tier-prices

Answer 1

假设您验证了以下查询对于您怀疑的 key 返回 false:

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

我知道可以使用 this answer 列出 FK 违规行为。为了验证关键约束，可以使用 here 中的查询以及常规的分组依据和计数查询:

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;