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

Open
opened 2026-01-22 15:08:01 +01:00 by admin · 7 comments
Owner

Originally created by @Hendra-Huang on GitHub (Mar 7, 2017).

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.

Originally created by @Hendra-Huang on GitHub (Mar 7, 2017). My detail case is like this. At first my entity looks like this ```php /** * @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 ```php /** * @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.
admin added the BugMissing Tests labels 2026-01-22 15:08:01 +01:00
Author
Owner

@Ocramius commented on GitHub (Mar 12, 2017):

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?

@Ocramius commented on GitHub (Mar 12, 2017): 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?
Author
Owner

@Hendra-Huang commented on GitHub (Mar 13, 2017):

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
@Hendra-Huang commented on GitHub (Mar 13, 2017): 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. ```php 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". ```php 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 ```
Author
Owner

@Ocramius commented on GitHub (Mar 13, 2017):

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

On 13 Mar 2017 14:13, "Hendra-Huang" notifications@github.com 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
https://github.com/doctrine/doctrine2/issues/6327#issuecomment-286102929,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAJakIGaskO62kzPUBZfV3OLIttVxxD4ks5rlUDmgaJpZM4MVFS8
.

@Ocramius commented on GitHub (Mar 13, 2017): The insert statement is there: are you looking at the correct db/schema? On 13 Mar 2017 14:13, "Hendra-Huang" <notifications@github.com> 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 > <https://github.com/doctrine/doctrine2/issues/6327#issuecomment-286102929>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/AAJakIGaskO62kzPUBZfV3OLIttVxxD4ks5rlUDmgaJpZM4MVFS8> > . >
Author
Owner

@Hendra-Huang commented on GitHub (Mar 13, 2017):

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.

@Hendra-Huang commented on GitHub (Mar 13, 2017): 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.
Author
Owner

@Ocramius commented on GitHub (Mar 24, 2017):

@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

@Ocramius commented on GitHub (Mar 24, 2017): @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
Author
Owner

@msztolcman commented on GitHub (Jul 5, 2017):

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 :)

@msztolcman commented on GitHub (Jul 5, 2017): 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 :)
Author
Owner

@Ocramius commented on GitHub (Jul 7, 2017):

Relevant:

@Ocramius commented on GitHub (Jul 7, 2017): Relevant: * https://github.com/doctrine/dbal/pull/2648 * https://github.com/doctrine/dbal/pull/2765
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5448