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

        Matthias Lohr created issue -
        Marco Pivetta made changes -
        Field Original Value New Value
        Summary schema update does not recognize existing columns Schema tool does not recognize existing columns
        Environment Ubuntu Linux with PHP 5.3.2 and PostgreSQL 8.4/9.1 (errors on both versions) 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.
        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).
        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 ).
        Benjamin Eberlei made changes -
        Project Doctrine 2 - ORM [ 10032 ] Doctrine DBAL [ 10040 ]
        Key DDC-2110 DBAL-510
        Affects Version/s 2.3 [ 10184 ]
        Affects Version/s 2.3 [ 10185 ]
        Component/s ORM [ 10012 ]
        Benjamin Eberlei made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 2.3.4 [ 10421 ]
        Resolution Fixed [ 1 ]
        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)
        Christian Eikermann made changes -
        Affects Version/s 2.4.1 [ 10527 ]

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DBAL-510, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

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

            Dates

            • Created:
              Updated:
              Resolved: