No exception thrown while insert if the sequence is not found using postgresql

[Hendra-Huang]

My detail case is like this.

At first my entity looks like this

/**
 * @Table(name="x")
 */
class X
{
    /**
     * @Id
     * @Column(type="integer")
     * @GeneratedValue(strategy="IDENTITY")
     */
    private $id;
}

I generate the schema, so I have got table X in my database. Then I want to rename my table and schema. So my entity now looks like this

/**
 * @Table(name="y")
 */
class Y
{
    /**
     * @Id
     * @Column(type="integer")
     * @GeneratedValue(strategy="IDENTITY")
     */
    private $id;
}

I update the schema again and manually rename table X to table Y in the database. Everything seems working fine. But something weird happens. All insert operations to Class Y are not persisted to the database, meanwhile no exception thrown while persist and flush. We wasted several hours for finding the problem because no exception at all. At last, we find out that the problem is because there is a different between table name and sequence name. We rename the table without renaming the sequence name. I think its better if there is an exception thrown because of it.

[Ocramius]

If an SQL operation fails, an exception will be thrown.

What does the SQL log say? Why are those statements executed and fail, but exceptions are not bubbling up?

Can this be reproduced in isolation in DBAL?

[Hendra-Huang]

This issue can't be reproduced in DBAL and the data is persisted to the database. Meanwhile in ORM, the data is not persisted and there is no exception thrown.

This is the way i do it. I started a new symfony project with pdo_pgsql driver and create a new Entity.

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Table(name="a")
 * @ORM\Entity(repositoryClass="AppBundle\Repository\ARepository")
 */
class A
{
    /**
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @ORM\Column(name="name", type="string", length=255)
     */
    private $name;

    // ....

Then I run bin/console doctrine:database:create and bin/console doctrine:schema:create --force. As the result, I got a table named "a" in my database. I run a query in the database to show the fields and sequence of table "a".

SELECT table_name, column_name, column_default from information_schema.columns where table_name='a';      
 table_name | column_name |        column_default         
------------+-------------+-------------------------------
 a          | id          | nextval('a_id_seq'::regclass)
 a          | name        | 

I modified the DefaultController.php to insert into table "a".

    public function indexAction(Request $request)
    {
        // It's working with DBAL
        //$conn = $this->get('database_connection');
        //$conn->insert('b', array('name' => 'last'));

        // It's not working with ORM
        $em = $this->getDoctrine()->getManager();
        $a = new A();
        $a->setName('asd');
        try {
            $em->persist($a);
            $em->flush($a);
        } catch (\Exception $e) {
            dump($e->getMessage());
            throw $e;
        }

        dump($em->getRepository('AppBundle:A')->findAll());

        // replace this example code with whatever you need
        return $this->render('default/index.html.twig', [
            'base_dir' => realpath($this->getParameter('kernel.root_dir').'/..').DIRECTORY_SEPARATOR,
        ]);
    }

It's working as expected. The data is persisted to table "a".
Then I rename my table "a" to "b" in my entity. @ORM\Table(name="a") to @ORM\Table(name="b"). I run SQL query from command line to update the table name. ALTER TABLE a RENAME TO b;. Then I try again and there is no error or exception at all and the data is not persisted. Here is my dev.log.

[2017-03-13 19:09:20] request.INFO: Matched route "homepage". {"route":"homepage","route_parameters":{"_controller":"AppBundle\\Controller\\DefaultController::indexAction","_route":"homepage"},"request_uri":"http://test.dev/app_dev.php/","method":"GET"} []
[2017-03-13 19:09:20] security.INFO: Populated the TokenStorage with an anonymous Token. [] []
[2017-03-13 19:09:20] doctrine.DEBUG: "START TRANSACTION" [] []
[2017-03-13 19:09:20] doctrine.DEBUG: INSERT INTO b (name) VALUES (?) {"1":"asd"} []
[2017-03-13 19:09:20] doctrine.DEBUG: "COMMIT" [] []
[2017-03-13 19:09:20] doctrine.DEBUG: SELECT t0.id AS id_1, t0.name AS name_2 FROM b t0 [] []
[2017-03-13 19:09:21] request.INFO: Matched route "_wdt". {"route":"_wdt","route_parameters":{"_controller":"web_profiler.controller.profiler:toolbarAction","token":"e4d4c5","_route":"_wdt"},"request_uri":"http://test.dev/app_dev.php/_wdt/e4d4c5","method":"GET"} []

This is the version I used for this project.

doctrine/annotations                 v1.2.7
doctrine/cache                       v1.6.1
doctrine/collections                 v1.3.0
doctrine/common                      v2.6.2
doctrine/dbal                        v2.5.12
doctrine/doctrine-bundle             1.6.7
doctrine/doctrine-cache-bundle       1.3.0
doctrine/inflector                   v1.1.0
doctrine/instantiator                1.0.5
doctrine/lexer                       v1.0.1
doctrine/orm                         v2.5.6

[Ocramius]

The insert statement is there: are you looking at the correct db/schema?

…

On 13 Mar 2017 14:13, "Hendra-Huang" ***@***.***> wrote: This issue can't be reproduced in DBAL and the data is persisted to the database. Meanwhile in ORM, the data is not persisted and there is no exception thrown. This is the way i do it. I started a new symfony project with pdo_pgsql driver and create a new Entity. namespace AppBundle\Entity;use Doctrine\ORM\Mapping as ORM;/** * @Orm\Table(name="a") * @Orm\Entity(repositoryClass="AppBundle\Repository\ARepository") */class A{ /** * @Orm\Column(name="id", type="integer") * @Orm\Id * @Orm\GeneratedValue(strategy="IDENTITY") */ private $id; /** * @Orm\Column(name="name", type="string", length=255) */ private $name; // .... Then I run bin/console doctrine:database:create and bin/console doctrine:schema:create --force. As the result, I got a table named "a" in my database. I run a query in the database to show the fields and sequence of table "a". SELECT table_name, column_name, column_default from information_schema.columns where table_name='a'; table_name | column_name | column_default ------------+-------------+------------------------------- a | id | nextval('a_id_seq'::regclass) a | name | I modified the DefaultController.php to insert into table "a". public function indexAction(Request $request) { // It's working with DBAL //$conn = $this->get('database_connection'); //$conn->insert('b', array('name' => 'last')); // It's not working with ORM $em = $this->getDoctrine()->getManager(); $a = new A(); $a->setName('asd'); try { $em->persist($a); $em->flush($a); } catch (\Exception $e) { dump($e->getMessage()); throw $e; } dump($em->getRepository('AppBundle:A')->findAll()); // replace this example code with whatever you need return $this->render('default/index.html.twig', [ 'base_dir' => realpath($this->getParameter('kernel.root_dir').'/..').DIRECTORY_SEPARATOR, ]); } It's working as expected. The data is persisted to table "a". Then I rename my table "a" to "b" in my entity. @Orm\Table(name="a") to @Orm\Table(name="b"). I run SQL query from command line to update the table name. ALTER TABLE a RENAME TO b;. Then I try again and there is no error or exception at all and the data is not persisted. Here is my dev.log. [2017-03-13 19:09:20] request.INFO: Matched route "homepage". {"route":"homepage","route_parameters":{"_controller":"AppBundle\\Controller\\DefaultController::indexAction","_route":"homepage"},"request_uri":"http://test.dev/app_dev.php/","method":"GET"} [] [2017-03-13 19:09:20] security.INFO: Populated the TokenStorage with an anonymous Token. [] [] [2017-03-13 19:09:20] doctrine.DEBUG: "START TRANSACTION" [] [] [2017-03-13 19:09:20] doctrine.DEBUG: INSERT INTO b (name) VALUES (?) {"1":"asd"} [] [2017-03-13 19:09:20] doctrine.DEBUG: "COMMIT" [] [] [2017-03-13 19:09:20] doctrine.DEBUG: SELECT t0.id AS id_1, t0.name AS name_2 FROM b t0 [] [] [2017-03-13 19:09:21] request.INFO: Matched route "_wdt". {"route":"_wdt","route_parameters":{"_controller":"web_profiler.controller.profiler:toolbarAction","token":"e4d4c5","_route":"_wdt"},"request_uri":"http://test.dev/app_dev.php/_wdt/e4d4c5","method":"GET"} [] This is the version I used for this project. doctrine/annotations v1.2.7 doctrine/cache v1.6.1 doctrine/collections v1.3.0 doctrine/common v2.6.2 doctrine/dbal v2.5.12 doctrine/doctrine-bundle 1.6.7 doctrine/doctrine-cache-bundle 1.3.0 doctrine/inflector v1.1.0 doctrine/instantiator 1.0.5 doctrine/lexer v1.0.1 doctrine/orm v2.5.6 — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#6327 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAJakIGaskO62kzPUBZfV3OLIttVxxD4ks5rlUDmgaJpZM4MVFS8> .

[Hendra-Huang]

Yeah there is the insert statement and it's using correct db and table. The problem is that the value of the inserted "id" column is always 1. Actually in my table, I have some records and the next id must not be 1. The problem is that I rename the table without rename the sequence. If I rename the sequence, then everything works as expected.

[Ocramius]

@Hendra-Huang doesn't look like an ORM bug to me then: if your DB silently accepts the value, that's expected behavior due to missing schema constraints.

If the problem is the lack of rename in the constraint, then I suggest writing a reproduction test case and reporting it against https://github.com/doctrine/dbal

[msztolcman]

I had same problem, with changed name of primary key field (without changed sequence name). Created entity, persist & flush, commit. Nothing in DB, no exception at all. But when I manually looked into PDO errors:

$this->logger->debug("SQL ERROR INFO: " . print_r($this->em->getConnection()->errorInfo(), 1));

There was info about invalid sequence. Fixing sequence name to expected by doctrine helps, but any exception from doctrine would be useful :)

[Ocramius]

Relevant:

• Fix last insert ID consistency accross drivers dbal#2648
• #2648 fix last insert id consistency dbal#2765