Symfony2 : Uncaught PHP Exception Doctrine when trying to insert into database

Question

My website is in production and sometimes for some people there is the following error:

[2015-02-04 09:05:24] request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\NotNullConstraintViolationException: "An exception occurred while executing 'INSERT INTO query (query, google, scpo, jstor, cairn, worldcat, convertion, date) VALUES (?, ?, ?, ?, ?, ?, ?, ?)' with params [null, 0, 0, 0, 0, 0, 0, "2015-02-04 09:05:24"]: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'query' cannot be null" at /home/biblishazj/www/biblishare/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 112 {"exception":"[object] (Doctrine\DBAL\Exception\NotNullConstraintViolationException(code: 0): An exception occurred while executing 'INSERT INTO query (query, google, scpo, jstor, cairn, worldcat, convertion, date) VALUES (?, ?, ?, ?, ?, ?, ?, ?)' with params [null, 0, 0, 0, 0, 0, 0, \"2015-02-04 09:05:24\"]:\n\nSQLSTATE[23000]: Integrity constraint violation: 1048 Column 'query' cannot be null at /home/biblishazj/www/biblishare/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:112, Doctrine\DBAL\Driver\PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'query' cannot be null at /home/biblishazj/www/biblishare/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:93, PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'query' cannot be null at /home/biblishazj/www/biblishare/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:91)"} []

Here is my controller code:

$research = $_POST['query'];

    $session = $request->getSession();

    $session->set('query', $research);

    $query = new Query();
        $query->setQuery($research);
        if (isset($_POST['google'])){
            $query->setGoogle(1);
        } else {
            $query->setGoogle(0);
        }

        if (isset($_POST['scpo'])){
            $query->setScpo(1);
        } else {
            $query->setScpo(0);
        }

        if (isset($_POST['jstor'])){
            $query->setJstor(1);
        } else {
            $query->setJstor(0);
        }

        if (isset($_POST['cairn'])){
            $query->setCairn(1);
        } else {
            $query->setCairn(0);
        }

        if (isset($_POST['worldcat'])){
            $query->setWorldcat(1);
        } else {
            $query->setWorldcat(0);
        }

        $query->setConvertion(0);
        $query->setDate(new \DateTime());
        $em = $this->getDoctrine()->getManager();
        $em->persist($query);
        $em->flush();

How can I fix that? Thank you

UPDATe : here are the 3 lines for the problem:

[2015-02-04 16:09:27] security.INFO: Populated SecurityContext with an anonymous Token [] [] [2015-02-04 16:09:27] request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\NotNullConstraintViolationException: "An exception occurred while executing 'INSERT INTO query (query, google, scpo, jstor, cairn, worldcat, convertion, date) VALUES (?, ?, ?, ?, ?, ?, ?, ?)' with params [null, 0, 0, 0, 0, 0, 0, "2015-02-04 16:09:27"]: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'query' cannot be null" at /home/biblishazj/www/biblishare/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 112 {"exception":"[object] (Doctrine\DBAL\Exception\NotNullConstraintViolationException(code: 0): An exception occurred while executing 'INSERT INTO query (query, google, scpo, jstor, cairn, worldcat, convertion, date) VALUES (?, ?, ?, ?, ?, ?, ?, ?)' with params [null, 0, 0, 0, 0, 0, 0, \"2015-02-04 16:09:27\"]:\n\nSQLSTATE[23000]: Integrity constraint violation: 1048 Column 'query' cannot be null at /home/biblishazj/www/biblishare/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:112, Doctrine\DBAL\Driver\PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'query' cannot be null at /home/biblishazj/www/biblishare/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:93, PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'query' cannot be null at /home/biblishazj/www/biblishare/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:91)"} [] [2015-02-04 16:09:27] security.DEBUG: Write SecurityContext in the session [] []

Maybe it's about the security.yml?

# you can read more about security in the related section of the documentation
# http://symfony.com/doc/current/book/security.html
security:
    encoders:
        FOS\UserBundle\Model\UserInterface: sha512

    role_hierarchy:
        ROLE_ADMIN:       ROLE_USER
        ROLE_SUPER_ADMIN: ROLE_ADMIN

    providers:
        fos_userbundle:
            id: fos_user.user_provider.username

    # the main part of the security, where you can set up firewalls
    # for specific sections of your app

    firewalls:
        main:
            pattern: ^/
            form_login:
                provider: fos_userbundle
                csrf_provider: form.csrf_provider
                success_handler: utilisateurs_utilisateurs.listener.authentication_success_handler
            logout:       true
            anonymous:    true

    access_control:
        - { path: ^/login$, role: IS_AUTHENTICATED_ANONYMOUSLY }
        - { path: ^/register, role: IS_AUTHENTICATED_ANONYMOUSLY }
        - { path: ^/resetting, role: IS_AUTHENTICATED_ANONYMOUSLY }
        - { path: ^/admin/, role: ROLE_ADMIN }

I guess $research is empty also using symfony then use its request object to get desired values — M Khalid Junaid
I don't understand, the error was for a real request so $research should not be empty. It seems this occurs just sometimes and for few people — nico_lrx
It says 1048 Column 'query' cannot be null but I don't see you're handling it in your code. Something like ->setQuery('Whatever-it-is') is missing I guess. — BentCoder

Hajri Aymen Hajri Aymen · Accepted Answer · 2015-02-04T15:29:05

Try to var_dump($research) just to test if it is null. Otherwise if setting query attribute as null doesn't make any probleme for you logic then you have to add nullable: true in the Query entity or do the test:

if ($research != null){
    $query->setQuery($research);
}
else {
    //$default_value used when $research is null
    $query->setQuery($default_value);
}

if it was null set it to a default value.

Symfony2 : Uncaught PHP Exception Doctrine when trying to insert into database

2 Answers