Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-510

Schema tool does not recognize existing columns

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.3, 2.4.1
    • Fix Version/s: 2.3.4
    • Component/s: None
    • Security Level: All
    • Labels:
      None
    • Environment:
      Ubuntu 8.04 with PHP 5.3.2 and PostgreSQL 8.4/9.1 (errors on both versions)

      Description

      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 users_id_seq 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).

        Activity

        Hide
        Benjamin Eberlei added a comment -

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

        Show
        Benjamin Eberlei added a comment - Can i see your entity definition? This works normally, there must be something different in your environment.
        Hide
        Matthias Lohr added a comment -

        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  
        
        Client.php
        <?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;
        	}
        
        }
        
        Invoice.php
        <?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;
        
        }
        
        Item.php
        <?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;
        	}
        
        }
        
        Show
        Matthias Lohr added a comment - 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 Client.php <?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; } } Invoice.php <?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; } Item.php <?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; } }
        Hide
        Adrien Brault added a comment -

        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 search_path `ALTER USER youruser SET search_path = foo,yourschema,public;`.

        Show
        Adrien Brault added a comment - 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 search_path `ALTER USER youruser SET search_path = foo,yourschema,public;`.
        Hide
        Adrien Brault added a comment -

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

        Show
        Adrien Brault added a comment - 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 ...).
        Hide
        Matthias Lohr added a comment -

        Hey, thank you very much for for your information!

        Show
        Matthias Lohr added a comment - Hey, thank you very much for for your information!
        Hide
        Matthias Lohr added a comment -

        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.

        Show
        Matthias Lohr added a comment - 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.
        Hide
        Matthias Lohr added a comment -
        Show
        Matthias Lohr added a comment - Fixed it ( https://github.com/doctrine/dbal/pull/305 ).
        Hide
        Piotr Antosik added a comment -

        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.

        Show
        Piotr Antosik added a comment - 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.
        Hide
        Christian Eikermann added a comment - - edited

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

        Show
        Christian Eikermann added a comment - - edited 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 user_id_seq 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)

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Matthias Lohr
          • Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: