[DBAL-510] Schema tool does not recognize existing columns Created: 31/Oct/12  Updated: 01/May/13  Resolved: 01/May/13

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.3
Fix Version/s: 2.3.4
Security Level: All

Type: Bug Priority: Major
Reporter: Matthias Lohr Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

Ubuntu 8.04 with PHP 5.3.2 and PostgreSQL 8.4/9.1 (errors on both versions)

Attachments: Text File phpunit_with_tap.txt    

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

 Comments   
Comment by Benjamin Eberlei [ 06/Jan/13 ]

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

Comment by Matthias Lohr [ 06/Jan/13 ]

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;
	}

}
Comment by Adrien Brault [ 15/Apr/13 ]

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;`.

Comment by Adrien Brault [ 15/Apr/13 ]

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

Comment by Matthias Lohr [ 15/Apr/13 ]

Hey, thank you very much for for your information!

Comment by Matthias Lohr [ 15/Apr/13 ]

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.

Comment by Matthias Lohr [ 15/Apr/13 ]

Fixed it (https://github.com/doctrine/dbal/pull/305).

Generated at Wed May 22 01:10:35 UTC 2013 using JIRA 5.2.7#850-sha1:b2af0c8dc8537b36121c6a579fabbdf79fc919e5.