How to debug Magento 2 MySQL (SQLSTATE) errors

Sometimes it is not easy to figure out where the database related errors come from. Error is usually being written to exception.log by Magento\Framework\DB\Statement\Pdo\Mysql class. But error message is not giving you idea what exactly caused the error. Below is the trick to get more info about error.

Recently on one of the projects we have got the following error and it was not obvious what triggered it:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row

Error is generated in vendor/magento/framework/DB/Statement/Pdo/Mysql.php in tryExecute method which looks like that:

private function tryExecute($callback)
    {
        $previousLevel = error_reporting(\E_ERROR); // disable warnings for PDO bugs #63812, #74401
        try {
            return $callback();
        } catch (\PDOException $e) {
            $message = sprintf('%s, query was: %s', $e->getMessage(), $this->_stmt->queryString);
            throw new \Zend_Db_Statement_Exception($message, (int)$e->getCode(), $e);
        } finally {
            error_reporting($previousLevel);
        }
    }

Let’s modify it and add backtrace info to the message. This way we will know what feature or module triggers the error. Here is how it can look like:

 private function tryExecute($callback)
    {
        $previousLevel = error_reporting(\E_ERROR); // disable warnings for PDO bugs #63812, #74401
        try {
            return $callback();
        } catch (\PDOException $e) {
            $info = 'info:';
            foreach (debug_backtrace() as $_stack) {
                $info .=  (isset($_stack["file"]) ? $_stack["file"] : '') . ':' .
                    (isset($_stack["line"]) ? $_stack["line"] : '') . ' - ' .
                    (isset($_stack["function"]) ? $_stack["function"] : '') . PHP_EOL;
            }

            $r = new \ReflectionFunction($callback);
            $static = $r->getStaticVariables();
            if (isset($static['params'])) {
                $message = sprintf('%s, query was: %s, parameters: %s, backtrace: %s', $e->getMessage(), $this->_stmt->queryString, json_encode($static['params']), $info);
            } else {
                $message = sprintf('%s, query was: %s', $e->getMessage(), $this->_stmt->queryString);
            }

            throw new \Zend_Db_Statement_Exception($message, (int)$e->getCode(), $e);
        } finally {
            error_reporting($previousLevel);
        }
    }

Hope it will save you some time.