SQLSTATE[HY000]: General error with doctrine2 and no error

Question

I've seen many topic beginning with that message but I can't find a topic related to my problem.

Here is the code that create the exception with the error message : "SQLSTATE[HY000]: General error". My problem is : I have nothing except "General error". Actually, there are no error : the lines I want to delete are correctly deleted as far as I can tell. If I use the query in phpMyAdmin, I've got no error. I would like to know why I've got this error while everthing seems to be ok.

public function deleteAllUnused() {
    try {
        $rsm = new ResultSetMappingBuilder($this->getEntityManager());
        $rsm->addRootEntityFromClassMetadata("\Entity\CataloguePrixDetailProduit", "cpdp");
        $sqlRequest = $this->buildDeleteAllUnusedRequest();
        return $this->getEntityManager()
                ->createNativeQuery($sqlRequest, $rsm)
                ->execute();
    } catch (Exception $exc) {
        throw new Exception($exc->getMessage());
    }
}

private function buildDeleteAllUnusedRequest() {
    return 
    "DELETE cpdp.*\n"
    . "FROM CataloguePrixDetailProduit cpdp JOIN (\n"
        . "SELECT cpdp2.idCataloguePrixDetailProduit\n"
        . "FROM CataloguePrixDetailProduit cpdp2\n"
        . "WHERE cpdp2.idCataloguePrixDetailProduit NOT IN (\n"
            . "SELECT DISTINCT(cpdp3.idCataloguePrixDetailProduit)\n"
            . "FROM CataloguePrixDetailProduit cpdp3\n"
            . "INNER JOIN CatalogueExploitantFournisseur cef ON cpdp3.idCatalogueExploitantFournisseur = cef.idCatalogueExploitantFournisseur\n"
            . "INNER JOIN CatalogueExploitant ce ON cef.idCatalogueExploitant = ce.idCatalogueExploitant\n"
            . "INNER JOIN DetailProduitCommandeServiceValide dpcsv \n"
                . "ON dpcsv.idDetailProduit = cpdp3.idDetailProduit\n"
                . "AND dpcsv.idFournisseur = cef.idFournisseur\n"
                . "AND dpcsv.idExploitant = ce.idExploitant\n"
        . ")\n"
    . ") r ON r.idCataloguePrixDetailProduit = cpdp.idCataloguePrixDetailProduit";
}

I don't understand what went wrong. I can't keep an application which is sending error message for no reason.

EDIT : Here is the Exception Stack :

[10:02:38][Repository\CataloguePrixDetailProduitRepository][deleteAllUnused] : SQLSTATE[HY000]: General error

#0 C:\xampp\htdocs\projects\Sogesa\applications\SogesaWeb\library\DoctrineOrm\Doctrine\ORM\Internal\Hydration\ObjectHydrator.php(148): PDOStatement->fetch(2)
#1 C:\xampp\htdocs\projects\Sogesa\applications\SogesaWeb\library\DoctrineOrm\Doctrine\ORM\Internal\Hydration\AbstractHydrator.php(111): Doctrine\ORM\Internal\Hydration\ObjectHydrator->hydrateAllData()
#2 C:\xampp\htdocs\projects\Sogesa\applications\SogesaWeb\library\DoctrineOrm\Doctrine\ORM\AbstractQuery.php(747): Doctrine\ORM\Internal\Hydration\AbstractHydrator->hydrateAll(Object(Doctrine\DBAL\Driver\PDOStatement), Object(Doctrine\ORM\Query\ResultSetMappingBuilder), Array)
#3 C:\xampp\htdocs\projects\Sogesa\applications\SogesaWeb\model\Repository\CataloguePrixDetailProduitRepository.php(107): Doctrine\ORM\AbstractQuery->execute()
#4 C:\xampp\htdocs\projects\Sogesa\applications\SogesaWeb\controller\CataloguePrixDetailProduitController.php(196): Repository\CataloguePrixDetailProduitRepository->deleteAllUnused()
#5 C:\xampp\htdocs\projects\Sogesa\applications\SogesaWeb\library\Dispatcher.php(60): CataloguePrixDetailProduitController->ajaxLoadedFlushPrixUnused()
#6 C:\xampp\htdocs\projects\Sogesa\applications\SogesaWeb\library\Dispatcher.php(11): Dispatcher->callAction(Object(CataloguePrixDetailProduitController))
#7 C:\xampp\htdocs\projects\Sogesa\applications\SogesaWeb\webroot\index.php(21): Dispatcher->__construct()
#8 {main}

Thanks for your time.

are you sure these are the exact lines the error occurs? are you using the return value further in your code? - One possible cause for this error message is that the result of an update/delete (non-select) query is used with functions that only work on a select result set (for example $result->fetchAll() ). see this question, too: http://stackoverflow.com/questions/20315898/pdo-error-pdoexception-with-message-sqlstatehy000-general-error?rq=1 — cypherabe, Jul 14 '14 at 10:00
If this is an exception, you have its message here. Catch it and dump the stack trace to know where it happens. Typically, your `catch` in `deleteAllUnused`, instead of re-throwing the exception, should do something like: `echo $exc->getMessage() . "\n\n" . $exc->getTraceAsString();die;` (but only for debugging this, don't keep the `echo;die;` in production, obviously!) — Stock Overflaw, Jul 15 '14 at 10:20
@cypherabe : yes I'm sure at 100% that's the code which launch the exception. — Mortagus, Jul 16 '14 at 08:13
@StockOverflaw : sorry I took so long, but I edit my message with what you wanted. I just thought it could be a relational problem. I'm going to search in that direction and I let you know ;-) Thank for your time btw — Mortagus, Jul 17 '14 at 08:08
Try and have a look at [this question](http://stackoverflow.com/questions/13163059/subquery-in-join-with-doctrine-dql) that seems to assert sub-queries are not allowed in FROM/JOIN clauses. Rewrite your `buildDeleteAllUnused` query to avoid joining a sub-query or, if not possible, use straight SQL or multiple queries. Hope it helps! — Stock Overflaw, Jul 17 '14 at 09:32

rocky · Answer 1 · 2015-06-30T15:16:43.713

2

To Fix this general issue use DQL statement 
createQuery Instead of using createNativeQuery

I had faced this error when i try to execute delete query using createNativeQuery but after reading this link we changed it to createQuery and it worked for me.

edited Jun 30 '15 at 15:16

answered Jun 30 '15 at 08:24

rocky

631
5
14

1

I had faced this error when i try to execute delete query using createNativeQuery but after reading this [link](http://www.doctrine-project.org/jira/browse/DDC-962) we changed it to createQuery and it worked for me. – rocky Jun 30 '15 at 09:23
1

Good, so you should mention the reason in the answer itself and link to that documentation for more info as you did in the comment. Then the answer would be of good quality and will receive upvotes... – Marki555 Jun 30 '15 at 13:42

score 1 · Answer 2 · answered Apr 22 '21 at 03:20

From Doctrine's docs

If you want to execute DELETE, UPDATE or INSERT statements the Native SQL API cannot be used and will probably throw errors. Use EntityManager#getConnection() to access the native database connection and call the executeUpdate() method for these queries.

SQLSTATE[HY000]: General error with doctrine2 and no error

2 Answers2