DBAL-510: Schema tool does not recognize existing columns #1715

Closed
doctrinebot opened this Issue Oct 31, 2012 · 10 comments

2 participants

@doctrinebot

Jira issue originally created by user mlohr:

I'm using doctrine with symfony. The first schema update is working, the table exists with all defined columns.
When i now run the schema update a second time (without code changes), doctrine tries to create the columns - but they already exists:

$ php app/console doctrine:schema:update --dump-sql
CREATE SEQUENCE usersidseq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE TABLE users (id INT NOT NULL, email VARCHAR(255) NOT NULL, password VARCHAR(255) DEFAULT NULL, salt VARCHAR(255) DEFAULT NULL, realname VARCHAR(255) NOT NULL, roles TEXT NOT NULL, PRIMARY KEY(id))
$ php app/console doctrine:schema:update --force
Updating database schema...
Database schema updated successfully! "2" queries were executed
$ php app/console doctrine:schema:update --dump-sql
ALTER TABLE users ADD id INT NOT NULL;
ALTER TABLE users ADD email VARCHAR(255) NOT NULL;
ALTER TABLE users ADD password VARCHAR(255) DEFAULT NULL;
ALTER TABLE users ADD salt VARCHAR(255) DEFAULT NULL;
ALTER TABLE users ADD realname VARCHAR(255) NOT NULL;
ALTER TABLE users ADD roles TEXT NOT NULL;
ALTER TABLE users ADD PRIMARY KEY (id)

Doctrine seems to ignore existing columns.

PHPUnit segfaults because of a backslash prepended to a FQCN and has a failure in the tests run so far (see attached PHPUnit output).

@doctrinebot

Comment created by @beberlei:

Can i see your entity definition? This works normally, there must be something different in your environment.

@doctrinebot

Comment created by mlohr:

I have the same problem in another project, so i'll give you the definitions from there. Here's the console output.

$ php -v
PHP 5.3.10-1ubuntu3.4 with Suhosin-Patch (cli) (built: Sep 12 2012 18:59:41) 
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies

$ psql --version
psql (PostgreSQL) 9.1.7
contains support for command-line editing

$ php app/console doctrine:schema:update --dump-sql
CREATE TABLE invoices (id INT NOT NULL, number VARCHAR(255) NOT NULL, PRIMARY KEY(id));
CREATE TABLE items (id INT NOT NULL, label VARCHAR(255) NOT NULL, price DOUBLE PRECISION DEFAULT NULL, withTax BOOLEAN NOT NULL, PRIMARY KEY(id));
CREATE TABLE clients (id INT NOT NULL, label VARCHAR(255) NOT NULL, PRIMARY KEY(id))

$ php app/console doctrine:schema:update --force
Updating database schema...
Database schema updated successfully! "3" queries were executed

$ php app/console doctrine:schema:update --dump-sql
ALTER TABLE invoices ADD id INT NOT NULL;
ALTER TABLE invoices ADD number VARCHAR(255) NOT NULL;
ALTER TABLE invoices ADD PRIMARY KEY (id);
ALTER TABLE items ADD id INT NOT NULL;
ALTER TABLE items ADD label VARCHAR(255) NOT NULL;
ALTER TABLE items ADD price DOUBLE PRECISION DEFAULT NULL;
ALTER TABLE items ADD withTax BOOLEAN NOT NULL;
ALTER TABLE items ADD PRIMARY KEY (id);
ALTER TABLE clients ADD id INT NOT NULL;
ALTER TABLE clients ADD label VARCHAR(255) NOT NULL;
ALTER TABLE clients ADD PRIMARY KEY (id)

$ php app/console doctrine:schema:update --force
Updating database schema...

  [Doctrine\DBAL\DBALException]                                                                   
  An exception occurred while executing 'ALTER TABLE invoices ADD id INT NOT NULL':               

  SQLSTATE[42701]: Duplicate column: 7 ERROR:  column "id" of relation "invoices" already exists  


  [PDOException]                                                                                  
  SQLSTATE[42701]: Duplicate column: 7 ERROR:  column "id" of relation "invoices" already exists  
<?php

namespace LohrTec\PureInvoiceBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/****
 * @ORM\Entity
 * @ORM\Table(name="clients")
 */
class Client {

    /****
     * client id
     *
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     * @var int
     */
    private $id;

    /****
     * client label
     *
     * @ORM\Column(type="string")
     * @var string
     */
    private $label;


    public function getId() {
        return $this->id;
    }

    public function getLabel() {
        return $this->label;
    }

    public function setLabel($label) {
        $this->label = $label;
    }

}
<?php

namespace LohrTec\PureInvoiceBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/****
 * @ORM\Entity
 * @ORM\Table(name="invoices")
 */
class Invoice {

    /****
     * client id
     *
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     * @var int
     */
    private $id;

    /****
     * invoice number
     *
     * @ORM\Column(type="string")
     * @var string
     */
    private $number;

}
<?php

namespace LohrTec\PureInvoiceBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/****
 * @ORM\Entity
 * @ORM\Table(name="items")
 */
class Item {

    /****
     * item id
     *
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     * @var int
     */
    private $id;

    /****
     * item label
     *
     * @ORM\Column(type="string")
     * @var string
     */
    private $label = '';

    /****
     * item default price
     *
     * @ORM\Column(type="float", nullable=true)
     * @var float
     */
    private $price = null;

    /****
     * false: pre tax price, true: post tax price
     *
     * @ORM\Column(type="boolean")
     * @var boolean
     */
    private $withTax = 1;

    public function **construct($label, $price = null, $withTax = null) {
        $this->setLabel($label);
        $this->setPrice($price, $withTax);
    }

    public function getId() {
        return $this->id;
    }

    public function getLabel() {
        return $this->label;
    }

    public function getPrice() {
        return $this->price;
    }

    public function priceIsTaxed() {
        return $this->withTax;
    }

    public function setLabel($label) {
        $this->label = $label;
    }

    public function setPrice($price, $withTax = null) {
        $this->price = $price;
        if ($withTax !== null) $this->withTax = ($withTax?true:false);
    }

    public function setPriceIsTaxed($isTaxed) {
        $this->withTax = $isTaxed;
    }

}
@doctrinebot

Comment created by adrienbrault:

This happens when the schema in which your tables are, is the first one in the list of show search_path;.
A simple workaround I'm using is to add a dumb schema at the beginning of searchpath `ALTER USER youruser SET searchpath = foo,yourschema,public;`.

@doctrinebot

Comment created by adrienbrault:

FYI, \Doctrine\DBAL\Platforms\PostgreSqlPlatform::getTableWhereClause doesnt seem to work when the $table has no "." in it. (Which happen when the schema is the first in the search_path ...).

@doctrinebot

Comment created by mlohr:

Hey, thank you very much for for your information!

@doctrinebot

Comment created by mlohr:

Ok, it's not the problem with the position of your schema, but the default schema is "$user" and that seems not to be parsed/replaced with the username! 'ALTER USER youruser SET search_path TO yoursearchpath, public;' works for me.

@doctrinebot

Comment created by mlohr:

Fixed it (#305).

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot

Comment created by piotrantosik:

This issue currently exist.

psql (PostgreSQL) 9.1.10
PHP 5.5.3-1ubuntu2 (cli) (built: Oct  9 2013 14:49:12)

After set search_path suggested by Adrien Brault schema update properly.

@doctrinebot

Comment created by ceikermann:

I can also confirm, that this bug still exists.
The workaround does not work for me

PHP: 5.3.37
PostgreSQL: 8.4.4

php app/console doctrine:schema:update --dump-sql
CREATE SEQUENCE useridseq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE TABLE "user" (id INT NOT NULL, name VARCHAR(255) NOT NULL, PRIMARY KEY(id))

php app/console doctrine:schema:update --force
Updating database schema...
Database schema updated successfully! "2" queries were executed

php app/console doctrine:schema:update --dump-sql
ALTER TABLE user ADD id INT NOT NULL;
ALTER TABLE user ADD name VARCHAR(255) NOT NULL;
ALTER TABLE user ADD PRIMARY KEY (id)

@doctrinebot doctrinebot added the Bug label Dec 6, 2015
@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot added this to the 2.3.4 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment