[DMIG-40] DBAL Enum fields migration issue / PostgreSQL Created: 24/Apr/13  Updated: 24/Apr/13  Resolved: 24/Apr/13

Status: Resolved
Project: Doctrine Migrations
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: jos de witte Assignee: Marco Pivetta
Resolution: Invalid Votes: 0
Labels: postgresql, schematool
Environment:

PostgreSQL and migrations



 Description   

When using Custom Doctrine DBAL Enums the migration created using diff

works fine the first time.

However the next time it generates a SQL statement trying to change to field type to INT from integer; (Redundant)
and a truncated statement:

"ALTER schemaname.fieldname SET" .. And that's it.



 Comments   
Comment by Marco Pivetta [ 24/Apr/13 ]

This is a DBAL issue. Please verify the issue using the DBAL schema tools (latest master) only and eventually open a DBAL issue.





[DMIG-39] Issue when using serjal columns in PostgreSQL with Doctrine ORM Identity fields Created: 24/Apr/13  Updated: 24/Apr/13  Resolved: 24/Apr/13

Status: Resolved
Project: Doctrine Migrations
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: jos de witte Assignee: Marco Pivetta
Resolution: Invalid Votes: 0
Labels: postgresql, schematool
Environment:

PostgreSQL and migrations



 Description   

When using Doctrine ORM mapping fields like this:

    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     * @ORM\SequenceGenerator(sequenceName="schemaname.tablename_id_seq", allocationSize=1, initialValue=1)
     */
    private $id;

It first creates the migration perfectly as a serial column with the correct schema.

However when making a new migration diff it generates DROP statements for every sequence for these id, so we have to remove them manually every time.



 Comments   
Comment by Marco Pivetta [ 24/Apr/13 ]

This is a DBAL problem.

Please verify this with the DBAL diff tools (latest master) only and eventually report a DBAL issue without involving migrations.





[DMIG-37] PostgreSQL generated SQL statements in migrations that use doublequotes create syntax errors in PHP Created: 28/Mar/13  Updated: 28/Mar/13

Status: Open
Project: Doctrine Migrations
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: jos de witte Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: PostgreSQL, schematool
Environment:

PostgreSQL backend, using migration versions with field or schemanames that use reserved names.



 Description   

When using some reserved field names in PostgreSQL DBAL, the generated SQL statements will quote those field names. (Make a table with fieldname "left" for example)

When the migrationversion is generated, the doublequote is not escaped:

addSQL("ALTER table x add column "left" integer");

This does not compile of course, should be

addSQL("ALTER table x add column \"left\" integer");

Regards!






[DDC-3381] orm:schema-tool:update shows incorrect changes with MasterSlaveConnection wrapper class Created: 09/Nov/14  Updated: 10/Nov/14

Status: Open
Project: Doctrine 2 - ORM
Component/s: Tools
Affects Version/s: 2.4.6
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Pieter Vogelaar Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: connection, ddl, master-slave, schematool


 Description   

My entities and database are in sync. If I use the default Doctrine connection configuration it shows "Nothing to update - your database is already in sync with the current entity metadata.". This is what I would expect and is correct.

'doctrine' => array(
        'connection' => array(
            'orm_default' => array(
                'driverClass' => 'Doctrine\DBAL\Driver\PDOMySql\Driver',
                'params' => array(
                    'host'     => 'localhost',
                    'port'     => '3306',
                    'user'     => 'myuser',
                    'password' => 'mypassword',
                    'dbname'   => 'example',
                    'charset'  => 'utf8',
                ),
        ),
),

But with the MasterSlaveConnection configuration, I always get the same changes which are a lot, you would think the database is empty at the moment.

'doctrine' => array(
        'connection' => array(
            'orm_default' => array(
                'wrapperClass' => 'Doctrine\DBAL\Connections\MasterSlaveConnection',
                'params' => array(
                    'driver' => 'pdo_mysql',
                    'master' => array(
                        'host' => 'localhost',
                        'port' => '3306',
                        'user' => 'myuser',
                        'password' => 'mypassword',
                        'dbname' => 'example',
                        'charset' => 'utf8',
                    ),
                    'slaves' => array(
                        array(
                            'host' => 'localhost',
                            'port' => '3306',
                            'user' => 'myuser',
                            'password' => 'mypassword',
                            'dbname' => 'example',
                            'charset' => 'utf8',
                        ),
                    ),
                ),
            ),
        ),
    ),
),


 Comments   
Comment by Marco Pivetta [ 10/Nov/14 ]

Can you come up with a functional test case for this? Couldn't reproduce from here.





[DDC-3333] doctrine:schema:update --complete does not detect old index Created: 02/Oct/14  Updated: 02/Oct/14

Status: Open
Project: Doctrine 2 - ORM
Component/s: Tools
Affects Version/s: 2.4.4
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Grégoire Paris Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: postgresql, schematool
Environment:

Ubuntu 14.04, PostgreSQL 9.3



 Description   

When changing the name of an index and using the symfony proxy command doctrine:schema:update --complete --dump-sql, the output only shows a CREATE statement. I would expect to also see a DROP statement. Here is what my index definition looks like :

    indexes:
        admin_entity_created_at_index:
            columns: [ createdAt ]





[DDC-3299] Possibility to define Entities in separate Bounded Context Created: 09/Sep/14  Updated: 13/Sep/14  Resolved: 10/Sep/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: Tools
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major
Reporter: Piotr Deszyński Assignee: Marco Pivetta
Resolution: Invalid Votes: 0
Labels: schematool


 Description   

It would be great to have a possibility to define two entities in different Bounded Context. For now it's possible without a problem to use such an Entities but it's impossible to use schema tools, when two separate Entities map to the same table.



 Comments   
Comment by Marco Pivetta [ 10/Sep/14 ]

This feature would break a very important invariant existing in the ORM: only one entity for a particular identifier existing in memory at any time.

This cannot be implemented, and actually goes against the rules imposed by the ORM.

The only solution to this problem would be to have a writable view, and mapping an entity to it, and an entity to the actual table: that's still making some of the transactions in your application very risky.

Comment by Piotr Deszyński [ 12/Sep/14 ]

But wouldn't it be possible to implement it really similar to single table inheritance but without a DiscriminatorColumn? Which instance would be returned would depend on the repository used.

Comment by Marco Pivetta [ 13/Sep/14 ]

Piotr Deszyński that wouldn't prevent having two instances of the same entity in memory, which is a problem.





[DDC-3289] Better exception description on some mapping errors Created: 31/Aug/14  Updated: 01/Sep/14

Status: Open
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.4
Fix Version/s: None

Type: Improvement Priority: Minor
Reporter: Luciano Mammino Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: mapping, orm, schematool


 Description   

Mapping problems does not always throw very explicit exceptions.

I had this feeling with a very common error:

This behaviour is (currently) not supported by Doctrine 2"

I essentially forgot to add a "mapped-by" attribute in my mappings and, given this generic message, I admit I had to spend a bit of time before being able to spot the mistake.

That's the code that detected the problem and triggered the proper exception.

Doctrine/ORM/Tools/SchemaTool.php
} elseif ($mapping['type'] == ClassMetadata::ONE_TO_MANY && $mapping['isOwningSide']) {
                //... create join table, one-many through join table supported later
                throw ORMException::notSupported();

Within the $mapping variable I see we have a lot of useful information about the problem. Why don't use them to create a very helpful exception message that reports the exact mapped entity and field that raised the error?

IMHO this will grant a better developer experience, especially for doctrine newcomers!



 Comments   
Comment by Marco Pivetta [ 01/Sep/14 ]

Hey Luciano Mammino, could you come up with an exception message that makes sense to you? This kind of improvement is trivial to implement, so it should be quick...

Comment by Luciano Mammino [ 01/Sep/14 ]

Hi Marco Pivetta
I think something like:

One to Many relationships without "mapped-by" attribute are not allowed. (Found in "\Foo\Bar\SomeEntity", field "somefield")

Will be good enough to give developers some clue about where the problem lies. Anyway I'm not sure it covers all the possible cases for this exception and that it is consistent with other error messages within the codebase.





[DDC-3228] ORM\Tools\Export\Driver\PhpExporter.php does not properly export manyToOne associations Created: 24/Jul/14  Updated: 24/Jul/14

Status: Open
Project: Doctrine 2 - ORM
Component/s: Mapping Drivers, ORM, Tools
Affects Version/s: Git Master
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Dan V Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: mapping, orm, schematool
Environment:

PHP 5.5.9-1ubuntu4 (cli)



 Description   

PhpExporter.php fails to check the association for manyToOne/oneToOne and exports all associations as oneToOne.

See https://github.com/doctrine/doctrine2/tree/master/lib/Doctrine/ORM/Tools/Export/Driver/PhpExporter.php#L116 where oneToOne is hardcoded if the bitmask matches either manyToOne or oneToOne.

As opposed to YamlExporter.php:

https://github.com/doctrine/doctrine2/tree/master/lib/Doctrine/ORM/Tools/Export/Driver/YamlExporter.php#L165

Which does roughly the same thing, but then properly sets the association type by checking the actual association on lines 186 through one 190:

https://github.com/doctrine/doctrine2/tree/master/lib/Doctrine/ORM/Tools/Export/Driver/YamlExporter.php#L186-L190






[DDC-3105] Doctrine Console Error (ORMPurger) Created: 29/Apr/14  Updated: 29/Apr/14

Status: Open
Project: Doctrine 2 - ORM
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Carlos Mendoza Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: Cli, schematool


 Description   

In symfony2 the command doctrine:fixtures:load Fails One-To-Many, Self-referencing using Doctrine\Common\DataFixtures\Purger\ORMPurger in example:

class DescriptionArea

{ //.. /** * @ORM\OneToMany(targetEntity="DescriptionArea", mappedBy="parent") */ protected $descriptionAreas; /** * @ORM\ManyToOne(targetEntity="DescriptionArea", inversedBy="descriptionAreas") */ protected $parent; //.. }

Throw error:
[Doctrine\DBAL\DBALException]

An exception occurred while executing 'DELETE FROM prefix_DescriptionArea':

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (sigtec_dev.prefix_DescriptionArea,

CONSTRAINT FK_7265873E727ACA70 FOREIGN KEY (parent_id) REFERENCES prefix_DescriptionArea (id))

Before running the query should delete the index when the table has self-reference.






[DDC-3040] doctrine:schema:update datetimetz field type not null Created: 19/Mar/14  Updated: 07/Apr/14  Resolved: 07/Apr/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.4.2
Fix Version/s: 2.4.2
Security Level: All

Type: Bug Priority: Major
Reporter: Coroliov Oleg Assignee: Benjamin Eberlei
Resolution: Can't Fix Votes: 0
Labels: console, orm, schematool


 Description   

I have some fields like

    /**
     * Adding date in format ISO-8601 YYYY-MM-DDThh:mm:ss±hhmm
     *
     * @var \DateTime
     *
     * @ORM\Column(name="added_at", type="datetimetz", nullable=false)
     */
    private $addedAt;

    /**
     * Expire date in format ISO-8601 YYYY-MM-DDThh:mm:ss±hhmm
     *
     * @var \DateTime
     *
     * @ORM\Column(name="expired_at", type="datetimetz", nullable=false)
     */
    private $expiredAt;

@ORM\Column -> nullable=false

In database this field already not null
But when i execute in console:

./app/console doctrine:schema:update --dump-sql --env=dev

answer:

ALTER TABLE test CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL;

If I change datetimetz to datetime type fot $expiredAt
and execute:

./app/console doctrine:schema:update --dump-sql --env=dev

answer:

ALTER TABLE test CHANGE added_at added_at DATETIME NOT NULL;



 Comments   
Comment by Coroliov Oleg [ 05/Apr/14 ]

have some news about this problem ?

Comment by Marco Pivetta [ 06/Apr/14 ]

Coroliov Oleg does the DDL update statement persist in the diffs even after running it?

Comment by Coroliov Oleg [ 06/Apr/14 ]
$ ./app/console doctrine:schema:update --dump-sql --env=dev
ALTER TABLE device CHANGE added_at added_at DATETIME NOT NULL;
ALTER TABLE device_audit CHANGE added_at added_at DATETIME DEFAULT NULL;
ALTER TABLE package_activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire_at DATETIME NOT NULL;
ALTER TABLE package_activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire_at DATETIME DEFAULT NULL;
ALTER TABLE package CHANGE created_at created_at DATETIME NOT NULL;
ALTER TABLE package_audit CHANGE created_at created_at DATETIME DEFAULT NULL;
ALTER TABLE coupon CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL;
ALTER TABLE coupon_audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired_at DATETIME DEFAULT NULL;
ALTER TABLE company CHANGE added_at added_at DATETIME NOT NULL;
ALTER TABLE company_audit CHANGE added_at added_at DATETIME DEFAULT NULL;
$ ./app/console doctrine:schema:update --force
Updating database schema...
Database schema updated successfully! "10" queries were executed
$ ./app/console doctrine:schema:update --dump-sql --env=dev
ALTER TABLE device CHANGE added_at added_at DATETIME NOT NULL;
ALTER TABLE device_audit CHANGE added_at added_at DATETIME DEFAULT NULL;
ALTER TABLE package_activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire_at DATETIME NOT NULL;
ALTER TABLE package_activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire_at DATETIME DEFAULT NULL;
ALTER TABLE package CHANGE created_at created_at DATETIME NOT NULL;
ALTER TABLE package_audit CHANGE created_at created_at DATETIME DEFAULT NULL;
ALTER TABLE coupon CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL;
ALTER TABLE coupon_audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired_at DATETIME DEFAULT NULL;
ALTER TABLE company CHANGE added_at added_at DATETIME NOT NULL;
ALTER TABLE company_audit CHANGE added_at added_at DATETIME DEFAULT NULL;

or if i use additional bundle

$ ./app/console doctrine:schema:update --dump-sql --env=dev
ALTER TABLE device CHANGE added_at added_at DATETIME NOT NULL;
ALTER TABLE device_audit CHANGE added_at added_at DATETIME DEFAULT NULL;
ALTER TABLE package_activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire_at DATETIME NOT NULL;
ALTER TABLE package_activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire_at DATETIME DEFAULT NULL;
ALTER TABLE package CHANGE created_at created_at DATETIME NOT NULL;
ALTER TABLE package_audit CHANGE created_at created_at DATETIME DEFAULT NULL;
ALTER TABLE coupon CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL;
ALTER TABLE coupon_audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired_at DATETIME DEFAULT NULL;
ALTER TABLE company CHANGE added_at added_at DATETIME NOT NULL;
ALTER TABLE company_audit CHANGE added_at added_at DATETIME DEFAULT NULL;
$ ./app/console doctrine:migrations:diff
Generated new migration class to "/Users/ruscon/projects/xxx/app/DoctrineMigrations/Version20140407004542.php" from schema differences.
$ ./app/console doctrine:migrations:migrate

                    Application Migrations


WARNING! You are about to execute a database migration that could result in schema changes and data lost. Are you sure you wish to continue? (y/n)y
Migrating up to 20140407004542 from 20140405144932

  ++ migrating 20140407004542

     -> ALTER TABLE device CHANGE added_at added_at DATETIME NOT NULL
     -> ALTER TABLE device_audit CHANGE added_at added_at DATETIME DEFAULT NULL
     -> ALTER TABLE package_activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire_at DATETIME NOT NULL
     -> ALTER TABLE package_activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire_at DATETIME DEFAULT NULL
     -> ALTER TABLE package CHANGE created_at created_at DATETIME NOT NULL
     -> ALTER TABLE package_audit CHANGE created_at created_at DATETIME DEFAULT NULL
     -> ALTER TABLE coupon CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL
     -> ALTER TABLE coupon_audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired_at DATETIME DEFAULT NULL
     -> ALTER TABLE company CHANGE added_at added_at DATETIME NOT NULL
     -> ALTER TABLE company_audit CHANGE added_at added_at DATETIME DEFAULT NULL

  ++ migrated (3.46s)

  ------------------------

  ++ finished in 3.46
  ++ 1 migrations executed
  ++ 10 sql queries
$ ./app/console doctrine:schema:update --dump-sql --env=dev
ALTER TABLE device CHANGE added_at added_at DATETIME NOT NULL;
ALTER TABLE device_audit CHANGE added_at added_at DATETIME DEFAULT NULL;
ALTER TABLE package_activated CHANGE start_at start_at DATETIME NOT NULL, CHANGE expire_at expire_at DATETIME NOT NULL;
ALTER TABLE package_activated_audit CHANGE start_at start_at DATETIME DEFAULT NULL, CHANGE expire_at expire_at DATETIME DEFAULT NULL;
ALTER TABLE package CHANGE created_at created_at DATETIME NOT NULL;
ALTER TABLE package_audit CHANGE created_at created_at DATETIME DEFAULT NULL;
ALTER TABLE coupon CHANGE added_at added_at DATETIME NOT NULL, CHANGE expired_at expired_at DATETIME NOT NULL;
ALTER TABLE coupon_audit CHANGE added_at added_at DATETIME DEFAULT NULL, CHANGE expired_at expired_at DATETIME DEFAULT NULL;
ALTER TABLE company CHANGE added_at added_at DATETIME NOT NULL;
ALTER TABLE company_audit CHANGE added_at added_at DATETIME DEFAULT NULL;
Comment by Steve Müller [ 07/Apr/14 ]

I assume from the syntax that you are using MySQL. MySQL does not have a native type for DateTimeTz, therefore the mapping always falls back to the native DATETIME type. See the DBAL documentation (footnote 15): http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/types.html#mapping-matrix

The problem here is not the nullable flag but the type you use. Therefore you get the irrgeular update statements from the schema tool. Use Doctrine's "datetime" type instead and you'll be fine. You won't be able to store time zone information for a date time in MySQL anyways as it does not have a type for this.

Comment by Coroliov Oleg [ 07/Apr/14 ]

Steve Müller, you right. Thanks.





[DDC-2989] ORM should allow custom index names for foreign associations. Created: 07/Feb/14  Updated: 20/Nov/14

Status: Open
Project: Doctrine 2 - ORM
Component/s: ORM, Tools
Affects Version/s: Git Master
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Jonathon Suggs Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: orm, schematool

Issue Links:
Duplicate
is duplicated by DBAL-1047 doctrine:schema:update ignores index ... Resolved
Reference
relates to DBAL-234 Index names are not synchronized by C... Resolved
relates to DBAL-566 Schema Comparator does not identify r... Resolved
is referenced by DDC-2990 [GH-956] Foreign association index names Open

 Description   

Now that the DBAL allows/checks for renamed indexes, the ORM implementation needs to be enhanced to allow custom naming of the indexes for foreign associations.



 Comments   
Comment by Jonathon Suggs [ 07/Feb/14 ]

Here is a full example
https://github.com/jsuggs/schema-issue

I think this mainly has to do with the doctrine auto-generated indexes.

Comment by Marco Pivetta [ 15/Feb/14 ]

IIRC, index names cannot currently be assigned, and are computed automatically - that's why the ORM is behaving like that.

I don't think this needs a fix right now - lowering priority

Comment by Steve Müller [ 15/Feb/14 ]

Jonathon Suggs I also could not reproduce this bug in DBAL. I wonder if that is related to foreign key declarations (ORM relations) only. In fact, you cannot define index names for relations (foreign keys) in ORM at the moment. Therefore ORM always generates index names automatically. If you use custom names for those indexes in your online schema, the comparator will surely output the index renaming statement. I guess defining the index at table level in your mapping manually won't help here either.
Whatsoever I think this is an ORM only issue. If you manually changed your index names "online", you will now get some index rename statements when using the schema tool. Other users explicitly requested this feature. Schemas not in sync have to be upgraded, I guess. So IMO this is rather a "won't fix" in DBAL. However there is a possible improvement to ORM for being able to define index names for foreign keys. The whole topic is rather tricky concerning the comparator. Imagine you have (for whatever reason) multiple indexes with different names that span the exact same columns. How would you expect the comparator to output changes here? Which indexes should be renamed, which shouldn't?

Comment by Jonathon Suggs [ 18/Feb/14 ]

I guess I get it, but its kinda a big deal and I'll have to old off upgrading as a result. I personally find this more of a regression due to the unintended consequences. The schema diff is now 100% useless (to me) as I rename all of my indexes to a more semantic name.

My off the cuff thought on ORM index naming is that you expand the naming strategy to include support for the indexes. Given the full set of criteria you should be able to come up with some sort of semantic naming and only on namespace collision to you "fallback" to a hash of the columns (solves your issues and mine).

Comment by Jonathon Suggs [ 19/Feb/14 ]

To state differently, I (personally) value the schema diff tool much more than custom index (re)naming since the (overall) ability to use custom index names is not complete (due to the ORM issues Steve outlined above).

If the support to (re)name foreign key indexes can't go out in parallel to this, I'd like to offer a middle ground of making the index renaming configurable (ie ability to opt-out of functionality).

Sorry to be negative towards a new feature, but its really an inconvenience for me, and I suspect others since Strate also expressed concerns in the PR.

Let me know how I can be of help in working towards a resolution.

Comment by Benjamin Eberlei [ 19/Feb/14 ]

Jonathon Suggs What do you mean with "just upgraded"? Which to which version? The index renaming and coverage feature is very old already, not sure what is happening. Do you define an index for a relation column?

Comment by Steve Müller [ 19/Feb/14 ]

Benjamin Eberlei This definitely has to do with the comparator now comparing index names also.
Jonathon Suggs Can you please confirm my assumption that this problem is only related to relations and their foreign key indexes? Also can you confirm that it only affects those foreign key indexes which you gave a custom name? Can you please check that?
I don't see any reasonable solution in DBAL for this. The real solution IMO would be to allow custom names in ORM mappings for foreign key realation indexes.

Comment by Jonathon Suggs [ 19/Feb/14 ]

Sorry for the confusion.

Here is the PR that introduced the functionality in question.
https://github.com/doctrine/dbal/pull/473

Benjamin, I had just upgraded DBAL to the latest 2.5 master. This is only indirectly related to the ORM functionality as the ORM SchemaTool uses the DBAL Comparator to generate is schema diff.
Steve, yes this is only related to relations and foreign key indexes that were given a custom name. I documented the use case/scenario here: https://github.com/jsuggs/schema-issue

Steve, I see three potential solutions (in my personal order of preference).
1) Expand the NamingStrategy to allow it to handle the default names of foreign key relation indexes
2) As you suggested, allow for ORM mappings to specify foreign key relation indexes
3) Add a configuration directive that essentially allows for you to ignore renamed indexes (ie. fallback to the previous behavior, prior to PR 473).

I realize that #1 would be a bit more work, but I think offers enhanced functionality. #2 is a good, but would require additional annotations/mappings. #3 is probably the least dev effort but just doesn't feel right (to me).

Again, I don't want to seem critical of the dev effort, but its an unintended consequence that will keep me from being able to upgrade DBAL to 2.5.

Comment by Steve Müller [ 19/Feb/14 ]

Jonathon Suggs You are right the comparator is somewhat responsible for the "unnecesarry" schema diffs it now creates. However its functionality is completely working IMO. Try creating and comparing your schema example on DBAL level only (without utilizing ORM). You will see that it works. I see and understand that those index name updates are annoying but they are not WRONG. If you have a mapping for a relation with an unnamed index and rename this index in your online schema manually, I would even expect the schema tool to generate this diff. Because this is the whole point of the index rename feature. The problem we have here is that there currently is no way in ORM to define the index name on association mappings. There the following happens in your example when comparing the online and offline schema with the schema tool:

(abbreviated to the important steps, chronological order)

1. Collect mapping information and evaluate offline schema
1.1. Schema tool collects relation SQL for Bar -> Foo association and adds an unnamed index "IDX_76FF8CAA8E48560F" to table "bar"
1.2. Schema tool collects custom indexes defined in the mapping for "bar", detects the custom index "IDX_MANY_TO_ONE", tries to add it to the table "bar, but discards it because there is already another index "IDX_76FF8CAA8E48560F" fulfilling the exact same columns (this is how DBAL works ever since to avoid duplicate indexes)

2. Reverse-engineer online schema from database
2.1 Fetch all defined indexes for table "bar" -> adds "idx_many_to_one" to table "bar"

3. Comapre evaluated online schema to evaluated offline schema
3.1 Compare index "idx_many_to_one" (online schema) to index "IDX_76FF8CAA8E48560F" (offline schema) -> suggest index rename from "idx_many_to_one" to "IDX_76FF8CAA8E48560F" because the mapping is your definition and takes precedence.

Just to clarify what happens under the hood and that it is an ORM issue, not DBAL!

Comment by Jonathon Suggs [ 19/Feb/14 ]

Here is a first shot at basic support for allowing the mapping of the index name.
https://github.com/jsuggs/doctrine2/compare/foreign-association-index-names

Comment by Steve Müller [ 19/Feb/14 ]

Jonathon Suggs I have worked on a PR for this already using the exact same approach you just mentioned. However I am not quite sure about ManyToMany yet (because you would have to be able to define both index names there) and also I talked to Benjamin Eberlei and he does not seem to be happy with this approach. So I stopped work on this for the moment.

Comment by Steve Müller [ 19/Feb/14 ]

Moved this to ORM issues.

Comment by Jonathon Suggs [ 19/Feb/14 ]

Steve I both agree and disagree.

I think my underlying issue is your step 1.1. The ORM creates the index named "IDX_76FF8CAA8E48560F" and there is NO extension point for being able to override that behavior. FWIW, I've never been fond of the way that the ORM uses the AbstractAsset::_generateIdentifierName for generating the index and foreign key.

So I definitely agree that its is an ORM issue not DBAL issue . However, as I stated previously, until the ORM is updated/patched to allow for index naming, this (in my opinion) is a regression despite it working the way it currently does (correct or not).

I can close out this issue and open one for ORM. Do you have a old branch and/or ORM ticket that I can reference?

Thanks for engaging in the dialog! I hope to be of assistance in helping come up with a solution that gets everyone happy.

Comment by Steve Müller [ 19/Feb/14 ]

Jonathon Suggs Thanks for updating the description and thanks for assisting on this issue. We will find a solution for this before the final release of 2.5. If we cannot find a good solution until then we might also consider reverting the index renaming feature on DBAL (but I would like to avoid that). I will discuss this issue with the other core devs again and see what we can come up with.

Comment by Jonathon Suggs [ 19/Feb/14 ]

No problem, here is the start of a PR to maybe address the index names
https://github.com/doctrine/doctrine2/pull/956





[DDC-2894] on-update cascade for one-to-one association Created: 08/Jan/14  Updated: 13/Nov/14

Status: Open
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.3.4, 2.4.1
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: I. S. Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: orm, schematool, xml


 Description   

I'm trying to use on-update cascade in a one-to-one association but it ends in on-update restrict when I update the database tables.
I'm using XML Definition an my definition for this specific association looks like this:

<one-to-one field="scope" target-entity="Application\Entity\Scope">
<join-column name="scope_id" referenced-column-name="id" nullable="false" on-delete="CASCADE" on-update="CASCADE" />
<cascade>
<cascade-persist />
<cascade-remove />
</cascade>
</one-to-one>

This works pretty well when I use on-update="CASCADE" in a many-to-one association, but one-to-one just ignores it.
When I change the database manually everything works well, but the next update from command line will override it.



 Comments   
Comment by Pradeep Krishnan [ 12/Nov/14 ]

Any updates on this one?

Comment by Marco Pivetta [ 13/Nov/14 ]

We don't support on-update anymore, as identifiers should be immutable.





[DDC-2788] Create Table If Not Exists - doctrine:schema:update Created: 11/Nov/13  Updated: 11/Nov/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: Tools
Affects Version/s: 2.2.3
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Minor
Reporter: jayem Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: command, schematool


 Description   

I am not positive if this issue is in the correct project. Sorry if I placed it in the wrong area.

I was wondering if it would be possible to have the doctrine:schema:update command updated to use CREATE IF NOT EXISTS for tables that already exist.

For example, here is my setup:

Author.php
    /**
     * The relationship between an author and its associated book entities.
     *
     * @ORM\ManyToMany(targetEntity="Book")
     * @ORM\JoinTable(name="authors_books",
     *     joinColumns={@ORM\JoinColumn(name="book_id", referencedColumnName="id")},
     *     inverseJoinColumns={@ORM\JoinColumn(name="author_id", referencedColumnName="id")}
     * )
     */
    protected $books;

The above code is an example, so the exact column names may not be 100% correct. The import aspect is the join table name. If that table already exists in the database, I will get the following error when I run the doctrine:schema:update --dump-sql command:

[Doctrine\DBAL\Schema\SchemaException]
The table with the name 'authors_books' already exists.

This is because it is trying to CREATE TABLE 'authors_books' and that table is already in the database. Could the command instead use CREATE TABLE IF NOT EXISTS 'authors_books' when "IF NOT EXISTS" is available for the configured database type?

Thanks!






[DDC-2742] 2 ManyToMany relations to the same target entity make the schema update fail by default Created: 15/Oct/13  Updated: 15/Oct/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: Mapping Drivers, Tools
Affects Version/s: 2.4
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Christophe Coevoet Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: mapping, schematool


 Description   

At some point in the Doctrine releases (I don't remember which version it was), the default naming of the join table of a ManyToMany relation changed from using the property name to using the name of the target entity.

This makes it impossible to use multiple ManyToMany relations to the same target entity in a class without naming join tables explicitly. A SchemaException is thrown by the SchemaTool when trying to update the schema.

Note that this issue is not caught by the SchemaValidator. It will display us that the mapping files are correct (before throwing the above exception in the second step when trying to compare it to the existing database)






[DDC-2560] Schema tool invalid DDL syntax for default values Created: 19/Jul/13  Updated: 19/Jul/13

Status: Reopened
Project: Doctrine 2 - ORM
Component/s: None
Affects Version/s: 2.3.4
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Ben Davies Assignee: Marco Pivetta
Resolution: Unresolved Votes: 0
Labels: schematool
Environment:

Postgresql



 Description   

Not sure if this is Postgresql specific or not, but here are the reproduction steps:

Have a table with a column setup like so:

ALTER TABLE usr ADD COLUMN contact_count integer;
ALTER TABLE usr ALTER COLUMN contact_count SET NOT NULL;
ALTER TABLE usr ALTER COLUMN contact_count SET DEFAULT 0;

Have a Entity Column Definition like so:

    /**
     * @var integer $contactCount
     *
     * @ORM\Column(name="contact_count", type="integer", nullable=false)
     */
    private $contactCount = 0;

orm:schema-tool:update will generate the following SQL:

ALTER TABLE usr ALTER contact_count SET ;

adding

options={"default":0}

to the column definition results in no sql being generated (correctly I assume)



 Comments   
Comment by Marco Pivetta [ 19/Jul/13 ]

The ORM doesn't support default column values.
That's fine-tuning you can do on the column definitions.

Your "workaround" is actually the correct way of dealing with this kind of DDL change.

Comment by Ben Davies [ 19/Jul/13 ]

I realise the ORM doesn't support default values, but the point is that the schematool picks up a diff in the columns, tries to generate a diff, and generates an invalid sql statement. Surely that is a bug?

Comment by Marco Pivetta [ 19/Jul/13 ]

Ben Davies the DBAL is responsible for generating this wrong DDL... Are you able to reproduce this in DBAL only?

Comment by Ben Davies [ 19/Jul/13 ]

Happy to try.

Comment by Ben Davies [ 19/Jul/13 ]

here you go
https://github.com/bendavies/dbal/blob/DBAL-561/tests/Doctrine/Tests/DBAL/Functional/Ticket/DBAL561Test.php





[DDC-2551] schema-tool does not match join columns with identifier options Created: 13/Jul/13  Updated: 16/Jul/13  Resolved: 15/Jul/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: None
Affects Version/s: 2.3.4
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Atans Chiu Assignee: Marco Pivetta
Resolution: Can't Fix Votes: 0
Labels: schematool
Environment:

Zend Framework 2
Doctrine ORM 2.3.4



 Description   
/**
 * Role
 *
 * @ORM\Entity
 * @ORM\Table(name="rbac_role", options={"collate"="utf8_general_ci"})
 * @package User\Entity
 */
class Role
{
    /**
     * @ORM\Id
     * @ORm\Column(name="role_id", type="integer", options={"unsigned"=true})
     * @ORM\GeneratedValue(strategy="AUTO")
     * @var int
     */
    protected $id;

    /**
     * @ORM\Column(name="role_name", name="string", length=32, nullable=true)
     * @var string
     */
    protected $name = null;


    /**
     * @ORM\ManyToOne(targetEntity="Role")
     * @ORM\JoinColumn(name="parent_role_id", referencedColumnName="id", nullable=true)
     * @var Role
     */
    protected $parentRole = null;
$ php vendor/doctrine/orm/bin/doctrine.php orm:schema-tool:update --force

Updating database schema...



  [Doctrine\DBAL\DBALException]
  An exception occurred while executing 'ALTER TABLE rbac_role ADD CONSTRAINT
   FK_C55D6FF2A44B56EA FOREIGN KEY (parent_role_id) REFERENCES rbac_role (id)
  ':

  SQLSTATE[HY000]: General error: 1005 Can't create table 'rbac.#sql-98_24b'
  (errno: 150)






  [PDOException]
  SQLSTATE[HY000]: General error: 1005 Can't create table 'rbac.#sql-98_24b'
  (errno: 150)

Problem here: ====================================================

ALTER TABLE rbac_role ADD CONSTRAINT
   FK_C55D6FF2A44B56EA FOREIGN KEY (parent_role_id) REFERENCES rbac_role (id)

 ==> `rbac_role (id)` should be `rbac_role (role_id)


 Comments   
Comment by Marco Pivetta [ 15/Jul/13 ]

You can make your join column unsigned by using the `columnDefinition` property.

Comment by Marco Pivetta [ 15/Jul/13 ]

The schema tool can't use the column options to match join columns with identifier columns.

Column options are one step further and cannot really be applied transitively to join columns, since they may contain vendor specific settings.

Comment by Atans Chiu [ 15/Jul/13 ]

Thanks.


@ORM\JoinColumn(
 name="parent_role_id",
  referencedColumnName="id",
  nullable=true,
  columnDefinition="ALTER TABLE rbac_role ADD CONSTRAINT FK_C55D6FF2A44B56EA FOREIGN KEY (parent_role_id) REFERENCES rbac_role (role_id)"
)

Is this right ?

Comment by Marco Pivetta [ 15/Jul/13 ]

No. Your problem is that the join coulm is signed, whereas the referenced identifier column is unsigned.

Comment by Atans Chiu [ 15/Jul/13 ]

I do not understand, how can I do ?

Comment by Marco Pivetta [ 15/Jul/13 ]

Simple solution: just drop the

options={"unsigned"=true}

from your annotations

Comment by Atans Chiu [ 16/Jul/13 ]

Thanks Marco Pivetta.

Comment by Atans Chiu [ 16/Jul/13 ]

Marco Pivetta, It is the same problem (2.4.0-RC2).

/**
 * Role
 *
 * @ORM\Entity
 * @ORM\Table(name="rbac_role", options={"collate"="utf8_general_ci"})
 * @package User\Entity
 */
class Role
{
    /**
     * @ORM\Id
     * @ORm\Column(name="role_id", type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     * @var int
     */
    protected $id;

    /**
     * @ORM\Column(name="role_name", name="string", length=32, nullable=true)
     * @var string
     */
    protected $name = null;


    /**
     * @ORM\ManyToOne(targetEntity="Role")
     * @ORM\JoinColumn(name="parent_role_id", referencedColumnName="id", nullable=true)
     * @var Role
     */
    protected $parentRole = null;

    /**
     * @ORM\ManyToMany(targetEntity="Permission")
     * @ORM\JoinTable(
     *  name="rbac_role_permission",
     *  joinColumns={@ORM\JoinColumn(name="role_id", referencedColumnName="id")},
     *  inverseJoinColumns={@ORM\JoinColumn(name="perm_id", referencedColumnName="id")}
     * )
     * @var Permission[]
     */
    protected $permissions;
$ php vendor/doctrine/orm/bin/doctrine.php orm:schema-tool:update --dump-sql
ALTER TABLE rbac_role ADD CONSTRAINT FK_C55D6FF2A44B56EA FOREIGN KEY (parent
e_id) REFERENCES rbac_role (id);
ALTER TABLE rbac_role_permission CHANGE role_id role_id INT NOT NULL;
ALTER TABLE rbac_role_permission ADD CONSTRAINT FK_C31A0CF0D60322AC FOREIGN
(role_id) REFERENCES rbac_role (id);
ALTER TABLE rbac_role_permission ADD CONSTRAINT FK_C31A0CF0FA6311EF FOREIGN
(perm_id) REFERENCES rbac_permission (id);

rbac_role (id) does not change to rbac_role (role_id)

Comment by Marco Pivetta [ 16/Jul/13 ]

That's normal. I already marked the issue as "won't fix". Please don't use the issue tracker as a helpdesk.

Comment by Atans Chiu [ 16/Jul/13 ]

Oh, sorry





[DDC-2489] Missing semicolon in schema update tool, using dump-sql argument Created: 05/Jun/13  Updated: 08/Sep/13  Resolved: 08/Sep/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM, Tools
Affects Version/s: None
Fix Version/s: 2.4

Type: Bug Priority: Minor
Reporter: Robert-Jan Assignee: Guilherme Blanco
Resolution: Fixed Votes: 0
Labels: Cli, schematool


 Description   

When executing the schema-tool update script, using the --dump-sql argument (e.g. "app/console doctrine:schema:update --dump-sql" in a symfony2 project), the semicolon behind the last query is missing.



 Comments   
Comment by Guilherme Blanco [ 12/Jun/13 ]

Fixed in https://github.com/doctrine/doctrine2/commit/0d834d0bd4015de2c103a03592c1543399f1b363





[DDC-2469] SQLite handling for ENUM-Fields Created: 24/May/13  Updated: 24/May/13  Resolved: 24/May/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: Mapping Drivers
Affects Version/s: Git Master
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Andy Rosslau Assignee: Marco Pivetta
Resolution: Invalid Votes: 0
Labels: query, schematool, sqlite


 Description   

SQLite doesn't support ENUMS!

But when I try create the schema of the following Entity Doctrine generates this "CREATE TABLE" - Statement:

CREATE TABLE Entity ([...] NOT NULL, taxation ENUM('incl', 'excl'), maxNumbe[...]
class Entity {
...

    /**
     * @var string
     *
     * @ORM\Column(type="string", columnDefinition="ENUM('incl', 'excl')")
     */
    private $taxation = self::TAXATION_INCL;

...
}

Produces this error:

SQLSTATE[HY000]: General error: 1 near "'incl'": syntax error'



 Comments   
Comment by Marco Pivetta [ 24/May/13 ]

Usage of

columnDefinition

in annotations or generally metadata mappings is all about vendor specific syntax. `columnDefinition` is designed to allow overriding the default ORM column generated DDL to build vendor specific syntax/types, therefore the issue is invalid





[DDC-2464] useless index for the middle table of many-to-many relationship Created: 21/May/13  Updated: 21/May/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: Tools
Affects Version/s: Git Master
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Minor
Reporter: scourgen Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: ddl, schematool


 Description   

I have entity A and B, the relationship between A and B is many-to-many. which means Doctrine2 will generate a middle table called AB for me.

entity A:

class Station {
    /**
     * @ORM\ManyToMany(targetEntity="Fun", mappedBy="stations")
     */
    protected $funs;
}

entity B:

class Fun {
    /**
     * @ORM\ManyToMany(targetEntity="Station", inversedBy="funs")
     * @ORM\JoinTable(name="stations_have_funs")
     */
    protected $stations;
}

the schema of middle table stations_have_funs:

CREATE TABLE `stations_have_funs` (
  `fun_id` int(11) NOT NULL,
  `station_id` int(11) NOT NULL,
  PRIMARY KEY (`fun_id`,`station_id`),
  KEY `IDX_45C921911CA4BE49` (`fun_id`),
  KEY `IDX_45C9219121BDB235` (`station_id`),
  CONSTRAINT `FK_45C921911CA4BE49` FOREIGN KEY (`fun_id`) REFERENCES `funs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_45C9219121BDB235` FOREIGN KEY (`station_id`) REFERENCES `stations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I noticed that there are 2 useless index(fun_id and station_id). Since fun_id and station_id are the primary key of this table. Do we really need 2 extra/duplicated index ?






[DDC-2425] Parent entity sometimes fails to load when validating/updating schema. Created: 03/May/13  Updated: 03/May/13  Resolved: 03/May/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Trivial
Reporter: Scott Gibson Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: Cli, orm, schematool
Environment:

Debian 6.0.6 x64



 Description   

Should not have reported, was a stupid mistake on my part.






[DDC-2421] Many-To-Many relation creation failed when using non PK entity field Created: 29/Apr/13  Updated: 10/Sep/13  Resolved: 01/May/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: Mapping Drivers, Tools
Affects Version/s: Git Master, 2.3.3
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Bruno CHALOPIN Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: schematool
Environment:

Ubuntu linux 12.04, php 5.4.9



 Description   

Given these entities :

/**
 * Class Domain
 *
 * @ORM\Entity
 * @ORM\Table(name="profils_domains")
 */
class Domain
{
    /**
     * @var string
     *
     * @ORM\Id
     * @ORM\Column(type="string", length=22, nullable=false)
     */
    protected $name = '';
}
/**
 * Class Web
 *
 * @ORM\Entity
 * @ORM\Table(name="profils_webs",
 *          uniqueConstraints={@ORM\UniqueConstraint(name="web_unique",columns={"name", "domain"})}
 * )
 */
class Web
{
    /**
     * @var integer
     *
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer", nullable=false)
     */
    protected $id;

    /**
     * @var string
     *
     * @ORM\Column(type="string", length=22, nullable=false)
     */
    protected $name = '';

    /**
     * @var Domain
     *
     * @ORM\ManyToOne(targetEntity="Domain", fetch="LAZY")
     * @ORM\JoinColumn(name="domain", referencedColumnName="name", nullable=false, onDelete="CASCADE")
     */
    protected $domain;
}
/**
 * Class WebsGroup
 *
 * @ORM\Entity
 * @ORM\Table(name="profils_websgroups",
 *          uniqueConstraints={@ORM\UniqueConstraint(name="websgroup_unique",columns={"name", "domain"})}
 * )
 */
class WebsGroup
{
    /**
     * @var integer
     *
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer", nullable=false)
     */
    protected $id;

    /**
     * @var string
     *
     * @ORM\Column(type="string", length=22, nullable=false)
     */
    protected $name = '';

    /**
     * @var Domain
     *
     * @ORM\ManyToOne(targetEntity="Domain", fetch="LAZY")
     * @ORM\JoinColumn(name="domain", referencedColumnName="name", nullable=false, onDelete="CASCADE")
     */
    protected $domain;

    /**
     * @var ArrayCollection
     *
     * @ORM\ManyToMany(targetEntity="Web", indexBy="id", fetch="EXTRA_LAZY")
     * @ORM\JoinTable(name="profils_websgroups_webs",
     *      joinColumns={
     * @ORM\JoinColumn(name="websgroup_id", referencedColumnName="id", onDelete="CASCADE"),
     * @ORM\JoinColumn(name="domain", referencedColumnName="domain", onDelete="CASCADE")
     *          },
     *      inverseJoinColumns={
     * @ORM\JoinColumn(name="web_id", referencedColumnName="id", onDelete="CASCADE"),
     * @ORM\JoinColumn(name="domain", referencedColumnName="domain", onDelete="CASCADE")
     *          }
     *      )
     */
    protected $webs;
}

I've got a domain, some web sites per domain and websgroups which group web sites. I want to be sure in my database that a web group from a domain D can contain only web sites from the very same domain but when calling the console tool for creating my schema it raise :

[Doctrine\ORM\ORMException]                                                                                                                                      
  Column name `domain` referenced for relation from Entity\WebsGroup towards Entity\Web does not exist. 

It's because domain is already an association to an entity which and is not part of the primary key.

I've quick fixed getDefiningClass from Doctrine\ORM\Tools\SchemaTool to make it work but i really don't know if it's the proper way :

    private function getDefiningClass($class, $referencedColumnName)
    {
        $referencedFieldName = $class->getFieldName($referencedColumnName);

        if ($class->hasField($referencedFieldName)) {
            return array($class, $referencedFieldName);
        } else if (in_array($referencedColumnName, $class->getIdentifierColumnNames())) {
            // it seems to be an entity as foreign key
            foreach ($class->getIdentifierFieldNames() as $fieldName) {
                if ($class->hasAssociation($fieldName) && $class->getSingleAssociationJoinColumnName($fieldName) == $referencedColumnName) {
                    return $this->getDefiningClass(
                        $this->em->getClassMetadata($class->associationMappings[$fieldName]['targetEntity']),
                        $class->getSingleAssociationReferencedJoinColumnName($fieldName)
                    );
                }
            }
        } else if (in_array($referencedColumnName, $class->getAssociationNames())) {
            return $this->getDefiningClass(
                $this->em->getClassMetadata($class->associationMappings[$referencedColumnName]['targetEntity']),
                $class->getSingleAssociationReferencedJoinColumnName($referencedColumnName)
            );
        }

        return null;
    }


 Comments   
Comment by Fabio B. Silva [ 29/Apr/13 ]

Bruno CHALOPIN Except for some CS this fix seems good.

If you have time you can send as pull request

Comment by Bruno CHALOPIN [ 30/Apr/13 ]

I've start making a PR and a test case but it is linked to http://www.doctrine-project.org/jira/browse/DDC-2413
I'm looking in making a proper fix to DDC-2413 first.

Comment by Benjamin Eberlei [ 01/May/13 ]

You cannot use a reference column that is not a primary key. Doctrine does not support this.

Comment by Bavo Janss [ 10/Sep/13 ]

I stumbled on this issue when experiencing a similar bug.
There is something seriously wrong with the referencedColumn in a ManyToMany relationship.
When you use a PK as 'id' and set your referenced column to 'id' everything is fine.
But when you use 'productId' as PK and reference it properly creation fails because "column 'id' doens't exists in table", which is very strange. After introducing a bogus column 'id' to the entity, but still referencing 'productId' a linking table is created but it has FK's to 'id' not to productId. When changing the referenced column to 'bogus' the creation also fails because: "column 'bogus' doesn't exists in table".

To me doctrine checks the the referencedColumn for existence but after that just creates everything assuming the default PK 'id'.





[DDC-2288] Schema Tool doesn't update collation on table level Created: 08/Feb/13  Updated: 08/Feb/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: Mapping Drivers, Tools
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Minor
Reporter: Rickard Andersson Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: collation, schematool


 Description   

In Symfony2, when updating the collation option of a table, the schema tool doesn't recognize the change:

Changing from:

 
* @ORM\Table()

To:

 
* @ORM\Table(options={"collate"="utf8_swedish_ci"})

Results in:

 
$ php app/console doctrine:schema:update --dump-sql
Nothing to update - your database is already in sync with the current entity metadata.





[DDC-2238] doctrine:schema:update partially broken Created: 11/Jan/13  Updated: 01/May/13  Resolved: 01/May/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: Tools
Affects Version/s: 2.3
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Tom Vogt Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 0
Labels: postgresql, schematool
Environment:

OS X 10.7.5
PHP 5.4.4
Postgres SQL 9.1.6

also confirmed on:
Linux (Debian testing, Kernel 2.6.32)
PHP 5.4.4
PostgreSQL 9.1.9
Doctrine 2.3.2


Attachments: Text File schemadiff.txt    
Issue Links:
Duplicate
duplicates DBAL-504 DBAL Enum fields migration issue / Po... Resolved

 Description   

the app/console doctrine:schema:update command generates a seemingly random number of statements like these:

ALTER TABLE geodata ALTER humidity SET ;
ALTER TABLE geodata ALTER lake SET ;
ALTER TABLE message ALTER translate SET ;

which are obvious invalid SQL commands. The mappings are fine, validate and the application works just fine. Here's an example from the mapping files including two of the above statements:

<field name="coast" type="boolean"/>
<field name="lake" type="boolean"/>
<field name="river" type="boolean"/>
<field name="humidity" type="float"/>
<field name="biome" type="string"/>

I am using doctrine2-spatial as an extension for GIS information. This problem shows up both in entities using spatial data and entities not using spatial data.

I'll gladly help debug this, as right now I can't update my dev database with --force, I need to use --dump-sql and filter out the invalid lines.



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

Can you dump the SchemaDiff/ColumnDiff instances that are returned from lib/Doctrine/DBAL/Schema/Comparator.php?

Comment by Tom Vogt [ 12/Jan/13 ]

requested dump of SchemaDiff

Comment by Tom Vogt [ 12/Jan/13 ]

added in the file schemadiff.txt - I added this in Schematool.php - getUpdateSchemaSql():

$comparator = new Comparator();
$schemaDiff = $comparator->compare($fromSchema, $toSchema);
\Doctrine\Common\Util\Debug::dump($schemaDiff, 6);

if you need other output, just tell me what entity to dump and where. I'll be happy to help.

The file also contains the buggy update statements it creates towards the end. There's a few non-crucial bugs included, where it alters the geospatial columns to themselves (i.e. river.course already is a geometry/linestring). I don't worry about those because they don't break anything.

Comment by Tom Vogt [ 15/Apr/13 ]

Is there any update on this? I'm still having this issue, with many different entities, always the same problem, for example:

ALTER TABLE event ALTER priority SET ;

Which is an integer field on an entity that doesn't have any GIS elements, so I'm not even sure if it's caused by that anymore.

Comment by Benjamin Eberlei [ 01/May/13 ]

Tom Vogt I added a fix for PostgreSQL today, can you verify again if this works? Its included in the 2.3 branch.

Comment by Benjamin Eberlei [ 01/May/13 ]

Duplicate of http://www.doctrine-project.org/jira/browse/DBAL-504





[DDC-2135] Setting column defaults using options in the annotations causes redundant alter statements Created: 09/Nov/12  Updated: 01/May/13  Resolved: 01/May/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.2.2
Fix Version/s: 2.3.4
Security Level: All

Type: Bug Priority: Major
Reporter: Cory Comer Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 0
Labels: Cli, schematool


 Description   

When using the Column annotation on an entity and passing the options parameter to set a default value for a column, the doctrine cli will generate the alter statement every time the schema-tool is run.

This doesn't break the functionality of updating the schema using the cli but when you have multiple entities using default values in this manner it becomes cumbersome to investigate issues during updates that fail and you need to dump the generated sql to examine.

Use case: We have a number of entities that include 'boolean' flags. The data for these entities is inserted into the database through an integration process handled by another application. For all of the flags we want to set a default value of 0 to avoid having to modify the integration scripts when a new flag is added to an entity and the data is not available yet.

Example entity:

/**
 * @ORM\Entity
 * @ORM\Table(name="example")
 */
class Example
{
    
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     */
    protected $id;
    
    /** @ORM\Column(type="integer", options={"default" = 0}) */
    protected $disabled;
    
}
$ php bin/doctrine orm:schema-tool:update --dump-sql
ALTER TABLE example CHANGE disabled disabled TINYINT(1) DEFAULT '0' NOT NULL;

This alter statement is generated on every run of the schema-tool even though the schema has already been altered.



 Comments   
Comment by Benjamin Eberlei [ 01/May/13 ]

This is fixed for 2.3.4





[DDC-2119] Problem with inheritance type: INHERITANCE_TYPE_NONE and INHERITANCE_TYPE_TABLE_PER_CLASS Created: 03/Nov/12  Updated: 08/Apr/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: DQL, Tools
Affects Version/s: 2.1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: SergSW Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dql, schematool

Attachments: File dump.sql     File SSWTestBundle.rar    

 Description   

I tried to create inheritance entities with save policy table per class.
Simple fileds was created normally, but a field with ManyToOne type was lost.

I had found a solution.

In Doctrine\ORM\Tools\SchemaTool
...

private function _gatherRelationsSql($class, $table, $schema)
    {
        foreach ($class->associationMappings as $fieldName => $mapping) {

           // if (isset($mapping['inherited'])) { // - old version

	/**
             * SSW
             * It's the solution
             */
	if (isset($mapping['inherited']) && !$class->isInheritanceTypeNone() && !$class->isInheritanceTypeTablePerClass() ) {
                continue;
            }            

            $foreignClass = $this->_em->getClassMetadata($mapping['targetEntity']);
...

But it was enough. In DQL query a simple query was made wrong.

I had found a solution again.
In Doctrine\ORM\Query\SqlWalker
...

public function walkSelectExpression($selectExpression)
...

                // original => if (isset($mapping['inherited'])){
                // It's the solution
                if (isset($mapping['inherited']) && !$class->isInheritanceTypeNone() && !$class->isInheritanceTypeTablePerClass()) {
                    $tableName = $this->_em->getClassMetadata($mapping['inherited'])->table['name'];
                } else {
                    $tableName = $class->table['name'];
                }
...

This problems are topical for inheritance type: INHERITANCE_TYPE_NONE and INHERITANCE_TYPE_TABLE_PER_CLASS.

I don't know, may be my solutions are wrong. But some programmers want to correctly work with INHERITANCE_TYPE_TABLE_PER_CLASS.

Sorry for my english.



 Comments   
Comment by Fabio B. Silva [ 05/Nov/12 ]

Hi SergSW

Could you try to write a failing test case ?

Thanks

Comment by SergSW [ 06/Nov/12 ]

SSW/TestBundle with the problem

Comment by SergSW [ 07/Nov/12 ]

I install the Symfony v2.0.18. and made small TestBundle.
I made schema database, by CLI "console doctrine:schema:update --force"
Result: Database schema updated successfully!
But I saw that I lost a field 'user_id' in a table 'AttachTree' (see Attach)

Comment by SergSW [ 07/Nov/12 ]

MySQL dump

Comment by Benjamin Eberlei [ 12/Nov/12 ]

Adjusted example formatting, don't apologize for your English, thanks for the report!

Comment by Benjamin Eberlei [ 24/Dec/12 ]

What version of 2.1 are you using? We don't actually support 2.1 anymore. Inheritance has always worked as used in hundrets of unit-tests, this changes look quite major a bug to have been missed before. I can't really explain whats happening here.

Comment by Marco Pivetta [ 23/Jan/13 ]

SergSW news?





[DDC-2011] Schema tool fail to handle ManyToMany relation with an existing joinTable Created: 04/Sep/12  Updated: 09/Feb/13  Resolved: 09/Feb/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: Tools
Affects Version/s: Git Master
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Erwan Richard Assignee: Benjamin Eberlei
Resolution: Won't Fix Votes: 2
Labels: schematool


 Description   

The schemaTool fail with a Doctrine\DBAL\Schema\SchemaException when trying to handle ManyToMany relation with an existing joinTable.

My use case :

I'm using the Tree DoctrineExtension with the Closure strategy. It create a mapping between my Category entity and a CategoryClosure entity.

For conveniency, I've setup a ManyToMany relation between Category and Category with the CategoryClosure table as joinTable.

Doctrine ORM handle perfectly this case but the schema tool is failing with :
The table with name 'category_closure' already exists.



 Comments   
Comment by Alexander [ 09/Feb/13 ]

You can't expect the schematool to play nice if you use the table of an entity as m2m table.





[DBAL-1047] doctrine:schema:update ignores index names set on Entity via Annotation Created: 20/Nov/14  Updated: 20/Nov/14  Resolved: 20/Nov/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.4
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: webDEVILopers Assignee: Marco Pivetta
Resolution: Duplicate Votes: 0
Labels: schematool
Environment:

"php": ">=5.3.3",
"symfony/symfony": "2.5.*",
"doctrine/orm": "~2.4",
"doctrine/doctrine-bundle": "~1.2"


Issue Links:
Duplicate
duplicates DDC-2989 ORM should allow custom index names f... Open

 Description   

I am naming my indexes via annotations in my entity:

/**
 * @ORM\Entity
 * @ORM\Table(name="delivery_notes", indexes={
 *  @ORM\Index(name="location_idx", columns={"location_id"}),
 *  @ORM\Index(name="user_idx", columns={"user_id"}),
 *  @ORM\Index(name="compartment_idx", columns={"compartment_id"})
 * })
 */
class DeliveryNote

When running doctrine:schema:update the names are ignored (at least when they don't exist yet):

CREATE INDEX IDX_1110401E64D218E ON delivery_notes (location_id);
CREATE INDEX IDX_1110401EA76ED395 ON delivery_notes (user_id);
CREATE INDEX IDX_1110401E6136A935 ON delivery_notes (compartment_id);

Is this an expected behaviour?



 Comments   
Comment by Marco Pivetta [ 20/Nov/14 ]

Duplicate of DDC-2989





[DBAL-1017] Altering a foreign key column is not done properly for MySQL Created: 21/Oct/14  Updated: 21/Oct/14

Status: Open
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.5, 2.4.3
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Christophe Coevoet Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: mysql, schematool


 Description   

Altering a foreign key column column (for instance to make it not-nullable) or the index of a foreign key does not work on MySQL (to be exact, it does not work on some MySQL setups, but I haven't found the config setting impacting it yet). Making it work requires dropping the foreign key before altering the column/index and readding it after



 Comments   
Comment by Steve Müller [ 21/Oct/14 ]

Christophe Coevoet DBAL-732 related?





[DBAL-1016] [GH-700] [DBAL-1016] Fix explicitly quoted table identifiers in ALTER TABLE statements Created: 20/Oct/14  Updated: 26/Oct/14  Resolved: 21/Oct/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.4.3
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Marco Pivetta
Resolution: Fixed Votes: 0
Labels: ddl, quoting, schemadiff, schematool

Issue Links:
Reference
is referenced by DBAL-753 Evaluate owncloud patch for Oracle qu... Resolved

 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/700

Message:

Another improvement to the neverending quotation issues.
This patch fixes explicitly quoted table identifiers in `ALTER TABLE` statements.
Additionally some minor fixes had to be applied such as misc fixing of foreign key constraint statements order in the sequence of statements necessary to alter a table.



 Comments   
Comment by Doctrine Bot [ 20/Oct/14 ]

A related Github Pull-Request [GH-700] was assigned:
https://github.com/doctrine/dbal/pull/700

Comment by Doctrine Bot [ 21/Oct/14 ]

A related Github Pull-Request [GH-700] was closed:
https://github.com/doctrine/dbal/pull/700





[DBAL-1013] [GH-696] [DBAL-1013] Fix table diff's new name if it is not set Created: 17/Oct/14  Updated: 19/Oct/14  Resolved: 19/Oct/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.5
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Minor
Reporter: Doctrine Bot Assignee: Marco Pivetta
Resolution: Fixed Votes: 0
Labels: ddl, schematool


 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/696

Message:

The `TableDiff` should not wrap `TableDiff::$newName` into an `Identifier` if it is not set.



 Comments   
Comment by Doctrine Bot [ 19/Oct/14 ]

A related Github Pull-Request [GH-696] was closed:
https://github.com/doctrine/dbal/pull/696

Comment by Doctrine Bot [ 19/Oct/14 ]

A related Github Pull-Request [GH-696] was assigned:
https://github.com/doctrine/dbal/pull/696





[DBAL-1011] [GH-694] [DBAL-1011] Fix column comments containing string literal chars on SQL Server Created: 16/Oct/14  Updated: 16/Oct/14  Resolved: 16/Oct/14

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

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Marco Pivetta
Resolution: Fixed Votes: 0
Labels: ddl, escaping, schematool


 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/694

Message:

When trying to create or update a column with a comment containing the string literal quote character `'`, SQL generation is invalid and fails on execution. This patch now quotes the particular comment to come around this issue.






[DBAL-1010] [GH-693] [DBAL-1010] Fix renaming column with default value on SQL Server Created: 16/Oct/14  Updated: 16/Oct/14  Resolved: 16/Oct/14

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

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Marco Pivetta
Resolution: Fixed Votes: 0
Labels: ddl, schematool

Issue Links:
Reference
relates to DBAL-830 [GH-539] unit test added for altering... Resolved

 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/693

Message:

Doctrine throws a fatal error on renaming a column with a default value set because of nested `Identifier` objects as old column name.
This issue was introduced by https://github.com/doctrine/dbal/pull/672.



 Comments   
Comment by Doctrine Bot [ 16/Oct/14 ]

A related Github Pull-Request [GH-693] was assigned:
https://github.com/doctrine/dbal/pull/693

Comment by Doctrine Bot [ 16/Oct/14 ]

A related Github Pull-Request [GH-693] was closed:
https://github.com/doctrine/dbal/pull/693





[DBAL-1009] [GH-692] [DBAL-1009] Fix column comment lifecycle Created: 16/Oct/14  Updated: 19/Oct/14  Resolved: 19/Oct/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms, Schema Managers
Affects Version/s: 2.4.3
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Marco Pivetta
Resolution: Fixed Votes: 0
Labels: comments, ddl, schematool


 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/692

Message:

The lifecycle of a column comment is not correct.
The comparator would not detect added comments. Also platforms did not handle the licecycle consistently.



 Comments   
Comment by Doctrine Bot [ 19/Oct/14 ]

A related Github Pull-Request [GH-692] was assigned:
https://github.com/doctrine/dbal/pull/692

Comment by Doctrine Bot [ 19/Oct/14 ]

A related Github Pull-Request [GH-692] was closed:
https://github.com/doctrine/dbal/pull/692





[DBAL-1002] [GH-687] [DBAL-1001] Fix NULL / NOT NULL clause for column alterations in Oracle Created: 14/Oct/14  Updated: 19/Oct/14  Resolved: 19/Oct/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.4.3
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Marco Pivetta
Resolution: Fixed Votes: 0
Labels: ddl, oracle, schematool

Issue Links:
Dependency
is required for DBAL-1001 NULL / NOT NULL clause always appende... Resolved

 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/687

Message:

Due to commit https://github.com/doctrine/dbal/commit/0782e9251a1df353ba589d32effbf75f646ca78f altering a column in Oracle now always appends a `NULL` / `NOT NULL` clause to the column alteration SQL which is wrong if the nullable status has not changed.
During column alteration the clause must only be appended if the nullable status really has changed.
See also: https://github.com/doctrine/dbal/pull/676#issuecomment-57643477



 Comments   
Comment by Doctrine Bot [ 19/Oct/14 ]

A related Github Pull-Request [GH-676] was assigned:
https://github.com/doctrine/dbal/pull/676

Comment by Doctrine Bot [ 19/Oct/14 ]

A related Github Pull-Request [GH-687] was assigned:
https://github.com/doctrine/dbal/pull/687

Comment by Doctrine Bot [ 19/Oct/14 ]

A related Github Pull-Request [GH-687] was closed:
https://github.com/doctrine/dbal/pull/687





[DBAL-1001] NULL / NOT NULL clause always appended to column alteration declaration in Oracle Created: 14/Oct/14  Updated: 19/Oct/14  Resolved: 19/Oct/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.5
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Major
Reporter: Steve Müller Assignee: Marco Pivetta
Resolution: Fixed Votes: 0
Labels: ddl, oracle, schematool

Issue Links:
Dependency
depends on DBAL-1002 [GH-687] [DBAL-1001] Fix NULL / NOT N... Resolved
Reference
relates to DBAL-472 Oracle schema modification - incorrec... Resolved

 Description   

Due to commit https://github.com/doctrine/dbal/commit/0782e9251a1df353ba589d32effbf75f646ca78f altering a column in Oracle now always appends a NULL / NOT NULL clause to the column alteration SQL which is wrong if the nullable status has not changed.
During column alteration the clause must only be appended if the nullable status really has changed.



 Comments   
Comment by Marco Pivetta [ 19/Oct/14 ]

Solved in DBAL-1002





[DBAL-954] Custom QuoteStrategy doesn't work when creating new schema Created: 30/Jul/14  Updated: 30/Jul/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.4.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Piotr Łyczba Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: schematool


 Description   

Implementing custom quote strategy:

class SingleQuoteStrategy extends DefaultQuoteStrategy implements QuoteStrategy
{
    public function getColumnName($fieldName, ClassMetadata $class, AbstractPlatform $platform)
    {
        return isset($class->fieldMappings[$fieldName]['quoted'])
            ? $platform->quoteSingleIdentifier($class->fieldMappings[$fieldName]['columnName'])
            : $class->fieldMappings[$fieldName]['columnName'];
    }
}

doesn't work when a new schema is created.

The problem is that in class file mapping I have column that has dot in its name escaped with back single quotes:

<field name="anuncianteId_delete" type="integer" column="`anunciante_id.delete`" nullable="true"/>

My custom quote strategy is necessary to force Doctrine to respect that. Otherwise Doctrine treats it as: namespace.name.

If I already have table with that column in my database update schema tool doesn't complains, the problem occurs only when I try to create new schema.

The error in resulting SQL Statement:

(SQLSTATE[HY000]: General error: 1 near ".": syntax error' while executing DDL: CREATE TABLE account ( (...) "anunciante_id"."delete" INTEGER DEFAULT NULL, (...) )





[DBAL-899] Escape table name when update schema Created: 07/May/14  Updated: 26/Jun/14  Resolved: 26/Jun/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.4.2
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Major
Reporter: Guilherme Santos Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: mysql, orm, schematool


 Description   

I have a table called by load and a field called by release, but in MySQL these are reserved names, and should be called inside of ``. In my annotations I use like that:

@ORM\Table(name="`load`") and
@ORM\Column(name="`release`", length=15)

These lines work good when use ORM, but when I use schema-tool the `` are ignored and it's generate something like that:

ALTER TABLE load CHANGE version release VARCHAR(60) DEFAULT NULL;

And to MySQL it's wrong, the right statement is:

ALTER TABLE `load` CHANGE version `release` VARCHAR(60) DEFAULT NULL;



 Comments   
Comment by Marco Pivetta [ 07/May/14 ]

Guilherme Santos can you verify if master (dbal+orm) fixes this? There has been some work on this issue.

Comment by Steve Müller [ 07/May/14 ]

Moved to DBAL, this is unrelated to ORM.

The table quotation issue has been fixed in 2.5/master in commit: https://github.com/doctrine/dbal/commit/75d35f5809095b37cb7085a9289eca4aa9c6df68

The column quotation issue is weird. There has been a fix in 2.5/master in commit: https://github.com/doctrine/dbal/commit/5156391b5686a2b3bba628bb0bc1fece63409a44 for the OLD column name but according to your example the NEW column name is not quoted. But that is working for ages already.

Can you please verify with the latest master as suggested by Marco Pivetta and maybe post the exact generated SQL by the schema tool?

Comment by Guilherme Santos [ 07/May/14 ]

It was fixed, a lot of index was changed too, but the quotation works!
Thanks...





[DBAL-819] Schema-tools does not work on multiple Oracle's schemas Created: 21/Feb/14  Updated: 22/Feb/14

Status: Open
Project: Doctrine DBAL
Component/s: Platforms, Schema Managers
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Antoine Descamps Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: Cli, oracle, orm, schematool
Environment:

DB: Oracle 11g



 Description   

The schema-tools, used via the CLI, is not able to detect schema's changes when working on multiple schemas.

For instance, the ORM is configured with a "global" Oracle's user, having permissions on every schemas. To specify which entities belong to which schema, I've prefixed the table name with the corresponding schema.

When trying to do the following command:

orm:schema-tool:update --dump-sql

Doctrine returns me the following message:

Nothing to update - your database is already in sync with the current entity metadata.

If, instead of using the "global" user in the Doctrine's configuration, I set the user of the specific schema I'm trying to generate a table on based from an entity, it works.



 Comments   
Comment by Steve Müller [ 21/Feb/14 ]

Moved this to DBAL for now. It seems to be related to schema prefixed table names not being evaluated in the platforms when generating the SQL for reverse engineering the database schema.

Comment by Steve Müller [ 22/Feb/14 ]

Antoine Descamps Okay after having investigated on this in detail and thinking about the possibilities we have to find a solution for this, I came to the following conclusion:
ORM's schema tool and DBAL's schema introspection are designed to be bound to what Doctrine defines as "database" for each platform. This is the scope of the whole operation. In case of Oracle it is the "user". You cannot break out of this scope in any way as the current DBAL implementation is not designed for a "complete" schema introspection and therefore does not allow it at some points. Fully understanding your concern I am afraid we cannot find a reasonable solution for your use case at this point in development (2.x). Furthermore there is a good reason for limiting the schema introspection to a certain layer. If the schema tool would introspect the "complete" schema without regard to the database it is connected to, it would suggest a lot of schema changes from other databases that do not belong to the context of the entity manager / database. This behaviour would even cause a lot more annoyance to users as it is the default use case that users are only interested in one database per entity manager.
Also this problem is completely independant from your mapping definitions. So it doesn't matter whether you prefix your table names or not. When running the "update" operation on the schema tool, it is the online schema introspection part that is preventing the schema tool from behaving as you would wish.
Changing the schema introspection behaviour in DBAL would completely break BC and is at some places in the code not even possible without changing the API.
I am sorry that I have to disappoint you with this conclusion but we I am afraid we cannot do anything about your issue until we start developing 3.x. We might reevaluate your use case their and see what we can do.





[DBAL-812] SchemaTool ignores multi-columns (composite) indexes when creating a foreign key Created: 13/Feb/14  Updated: 15/Feb/14

Status: Open
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.4.2
Fix Version/s: None

Type: Improvement Priority: Minor
Reporter: Peter Huynh Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: schematool
Environment:

MySQL



 Description   

When using the schema tool to generate the differences between the db and the entities, I notice the following:

ALTER TABLE sales ADD CONSTRAINT FK_36D222EF603EE73 FOREIGN KEY (vid) REFERENCES vendor (id);
CREATE INDEX IDX_36D222EF603EE73 ON sales (vid);

Normally, it doesn't bother me, however I have had an existing composite index consists of (vid, submit). This leads to the redundant index IDX_36D222EF603EE73 and therefore forcing unnecessary overheads.

It would be nice to have a mean to disable this from happening.

The code in question can be found at https://github.com/doctrine/dbal/blob/594e326bd58d1d7af578f0dc3143655b9d119d45/lib/Doctrine/DBAL/Schema/Table.php#L543.

A helpful member from #doctrine IRC also pointed out that it does not check the unique constraints also.

Regards,



 Comments   
Comment by Marco Pivetta [ 13/Feb/14 ]

Just a hint on this: looks like the current logic just checks hashed indexes, ignoring the "uniq" prefixed indexes to avoid duplicates.

Comment by Steve Müller [ 15/Feb/14 ]

Peter Huynh I get the point here. However after having a quick look into this it seems this behaviour exists for a good reason. See here: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/Index.php#L191-L195
The question is how could this reasonable extended to fulfil the needs you explained. I am not entirely sure about this and this peace of code is rather sensitive and critical. I don't want to rush any assumptions here.

Comment by Peter Huynh [ 15/Feb/14 ]

Hi Steve,

I fully understand your reservation.

Thanks for looking into things.

Peter





[DBAL-761] Driver\ResultStatement::fetchAll() returns empty array on a seemingly valid Driver\PDOStatement object Created: 03/Jan/14  Updated: 08/Jan/14

Status: Open
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.4
Fix Version/s: None

Type: Improvement Priority: Minor
Reporter: Dennis Matveyev Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: mysql, orm, query, schematool
Environment:

Windows 7 64 bit, Zend Server/Studio, PHP 5.4.16,
Server: Apache/2.2.22 (Win32) mod_ssl/2.2.22 OpenSSL/0.9.8x



 Description   

I came across a weird issue, where when running:

vendor/bin/doctrine-module orm:schema-tool:update

I would get:

[Doctrine\DBAL\Schema\SchemaException]
There is no column with name 'resource_id' on table 'role_resource'.

But I did have a column with the above name in the above table, so that was a weird message for me. So I traced it all the way to this line of code:

https://github.com/doctrine/dbal/blob/2.4/lib/Doctrine/DBAL/Connection.php#L630

If I remove "->fetchAll()" from that line, I get this object:

object(Doctrine\DBAL\Driver\PDOStatement)#531 (1)

{ ["queryString"]=> string(332) "SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type, IS_NULLABLE AS `Null`, COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS Extra, COLUMN_COMMENT AS Comment, CHARACTER_SET_NAME AS CharacterSet, COLLATION_NAME AS CollactionName FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'loginauth' AND TABLE_NAME = 'role_resource'" }

Which has a valid SQL query that runs fine and shows Field names on my local machine's MySQL prompt. But when I add ->fetchAll() back in that line of code, an empty array is returned, field names are not returned, and a SchemaException is generated. I found this odd and wanted to report it. Whether it is a bug or not, hopefully I can find the cause of this issue.

For loads more info, please see this: http://stackoverflow.com/questions/20907491/doctrine-2-there-is-no-column-with-name-columnname-on-table-table



 Comments   
Comment by Steve Müller [ 03/Jan/14 ]

Dennis Matveyev It seems you are using MySQL. Can you please tell which version you use?

Comment by Steve Müller [ 03/Jan/14 ]

Dennis Matveyev Please also provide whether you use mysqli or PDO_MYSQL.

Comment by Steve Müller [ 03/Jan/14 ]

Dennis Matveyev Sounds dumb, but also are you sure that the vendor/bin/doctrine-module orm:schema-tool:update uses the correct connection parameters and does not by error connect to another database server or something? Because otherwise everything looks good IMO.

Comment by Dennis Matveyev [ 07/Jan/14 ]

Yes, I am using:
Server version: 5.5.23-log MySQL Community Server (GPL)
mysql client Ver 14.14 Distrib 5.5.23, for Win32 (x86)

Doctrine connection string for 'driverClass' is 'Doctrine\DBAL\Driver\PDOMySql\Driver', so it is PDO_MYSQL.

I am connecting to the right database, but your last suggestion is what helped to uncover the issue. When connecting to the database with the very same user/pass, and running the command, here is what I saw:

mysql> select resource_id from role_resource;
ERROR 1142 (42000): SELECT command denied to user 'login'@'localhost' for table 'role_resource'

Running a GRANT command to allow SELECT for this user solved the problem.

To improve debugging of similar issues, I'd see if there is a way to propagate the error from MySQL server to ./doctrine-module,
or
improve the error message of SchemaException, to i.e. "There is no column with name 'action_id' on table 'role_action', or database permissions prevent table access."

Thanks!

Comment by Steve Müller [ 07/Jan/14 ]

Dennis Matveyev Thank you for reporting this and I'm glad I could help. I will mark this as improvement, though. You are right in that the root cause of the error should be propagated to the user instead.

Comment by Steve Müller [ 08/Jan/14 ]

Dennis Matveyev Okay I found the root cause of the problem. See what MySQL states about access to the information_schema table for retrieving metadata about a certain database:

Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA.ROUTINES table), users who have insufficient privileges will see NULL.

So it returns NULL instead of raising an error and this is why Doctrine is not able to propagate the proper exception in this case. I'm not sure if changing the exception message in the SchemaException class is the proper way of handling this. Also please note that this is not only about columns (where you got stuck at) but can occurr for almost every list*() action called in the SchemaManager I suppose. Furthermore this might be a MySQL specific issue, not sure about that.

The proper solution would be to throw an exception much earlier, to have a decent behaviour in such an edge case. I think we should throw an exception here: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php#L267 because a table cannot live without columns and indicates an error somewhere. But I would like to hear the opinion of Benjamin Eberlei on that issue.

Comment by Steve Müller [ 08/Jan/14 ]

I checked the SQL Server documentation and it seems they behave exactly the same: http://technet.microsoft.com/en-us/library/ms187113.aspx

In SQL Server 2005 and later, the visibility of metadata is limited to securables that a user either owns or on which the user has been granted some permission. For example, the following query returns a row if the user has been granted a permission such as SELECT or INSERT on the table myTable.

However, if the user does not have any permission on myTable, the query returns an empty result set.





[DBAL-662] Supporting PHP 5.5 DateTimeImmutable Created: 14/Nov/13  Updated: 02/Jul/14

Status: Open
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: New Feature Priority: Minor
Reporter: Christophe Coevoet Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 3
Labels: mapping, schematool


 Description   

Introducing new types converting dates into a DateTimeImmutable rather than a DateTime could be useful for applications prefering the use of immutable datetimes (and relying on PHP 5.5+).

The existing types already support setting a DateTimeImmutable in a field mapped with the datetime type, as it does not check the value against DateTime but only expects a format method. but the conversion from DB to PHP will always produce a mutable DateTime instance, thus preventing to use the immutable flavour consistently.

Not that this is a low priority issue as any code interacting with third party packages will probably need to use DateTime anyway because of the typehints (typehinting DateTimeInterface would not work if you need to keep compatibility with 5.4)

If DBAL 3.0 bumps the minimal supported version to 5.5 (which might be possible depending of the release date of 3.0), we could decide to break BC and use the immutable flavour in the datetime type directly.



 Comments   
Comment by Steve Müller [ 03/Jan/14 ]

Christophe Coevoet Should we mark that for 3.0 as suggested? Or do you see any need/possibility of implementing this already in 2.x branch?

Comment by Yaroslav Nechaev [ 02/Jul/14 ]

Please add this feature. Mutable DateTime causes too much trouble: now we have to use clone in every getter and setter just in case someone modifies the value afterwards and spoils the value stored in entity.

Comment by Steve Müller [ 02/Jul/14 ]

Yaroslav Nechaev I don't think we can support this before Doctrine 3.0 without breaking BC as Christophe Coevoet already stated because it would require us to bump the minimal supported PHP version to 5.5 (which currently is 5.3.2).





[DBAL-569] json_array/simple_array columns constantly updated by schema-tool Created: 25/Jul/13  Updated: 18/Dec/13  Resolved: 18/Dec/13

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

Type: Bug Priority: Minor
Reporter: Jonathan Campbell Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: schematool
Environment:

IIS 7.5, PHP 5.4.11, SQL Server 2012


Issue Links:
Reference
is referenced by DBAL-632 MSSQL Diff Resolved

 Description   

I have the following columns defined:

    /**
     * @ORM\Column(type="json_array")
     * @var array
     */
    protected $feedKey = array();

    /**
     * @ORM\Column(type="simple_array")
     * @var array
     */
    protected $privileges = array('none');

Every time I run " .\vendor\bin\doctrine-module orm:schema-tool:update --dump-sql", these two columns are "updated":

ALTER TABLE RoleResource ALTER COLUMN [privileges] VARCHAR(MAX) NOT NULL;
ALTER TABLE FeedEntity ALTER COLUMN feedKey VARCHAR(MAX) NOT NULL

When I browse to that table in Microsoft SQL Server Management Studio, the column definitions are already "privileges (varchar(max), not null)" and "feedKey (varchar(max), not null)".

The repeated update from schema-tool continues even after I let it run with --force.



 Comments   
Comment by Steve Müller [ 25/Nov/13 ]

There is WIP PR to support column comments in SQL Server which resolves this issue:

https://github.com/doctrine/dbal/pull/426

Comment by Doctrine Bot [ 18/Dec/13 ]

A related Github Pull-Request [GH-426] was closed:
https://github.com/doctrine/dbal/pull/426

Comment by Steve Müller [ 18/Dec/13 ]

Fixed in commit: https://github.com/doctrine/dbal/commit/28264a15a404496155d84a0abfa3404d01302905





[DBAL-550] Complete/non-overridable event dispatching in DDL methods of AbstractPlatform Created: 22/Jun/13  Updated: 23/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Improvement Priority: Major
Reporter: Stéphane Klein Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: migrations, schematool


 Description   

The default implementation provided by the AbstractPlatform class dispatches several events related to schema modifications (create table, change column and so on) and offers the ability to prevent the default behaviour by setting a flag on the dispatched event. However, it appears that some DDL operations aren't covered by this mechanism (foreign keys creation, for example, doesn't seem to be observable). Furthermore, the concrete platforms can override any of these methods, potentially leading to inconsistancies in components that expect those events to be dispatched.

I'm working on a large modular application build on top of Symfony, in which each bundle must have its own migration classes (i.e. migrations on a per bundle basis, not at the application level), possibly with references to other parts of the schema. I used to write those classes manually using the dbal api, but this is tiedous and error-prone, and I'd like to take advantage of the mapping-based schema generation. I found that using the event system and "filtering" ddl operations to keep only the operations related to a given bundle is a clean and easy way to achieve this goal, but the issues I mentionned prevent me to complete the implementation.

I'd be glad to hear your opinion on that topic and, providing you're interested in it, contribute in a way or another to improve the existing code.



 Comments   
Comment by Benjamin Eberlei [ 22/Jun/13 ]

This is not a blocker

Comment by Benjamin Eberlei [ 22/Jun/13 ]

The DBAL event system is not in its best shape and definately could use some improvement. Your easiest way however would be to hook the process of generating the whole schema, and then dropping tables from those two to only contain the ones you are interested in.

Comment by Stéphane Klein [ 23/Jun/13 ]

That's indeed the simplest solution. Thanks a lot for your answer.

Comment by Steve Müller [ 23/Dec/13 ]

Benjamin Eberlei Is there anything to do here? Should the missing events be added or is that not necessary?





[DBAL-483] default values make orm:validate-schema fail Created: 04/Apr/13  Updated: 01/May/13  Resolved: 01/May/13

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

Type: Bug Priority: Major
Reporter: Till Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 1
Labels: schematool
Environment:

MySQL, PHP 5.3, Doctrine 2.3



 Description   
% ./bin/doctrine.php --env=development orm:schema-tool:update --dump-sql                                                                                                  
ALTER TABLE groups CHANGE active active TINYINT(1) DEFAULT '1' NOT NULL;
ALTER TABLE module ADD root_order INT DEFAULT 0 NOT NULL
% ./bin/doctrine.php --env=development orm:schema-tool:update --force
Updating database schema...
Database schema updated successfully! "2" queries were executed
% ./bin/doctrine.php --env=development orm:schema-tool:update --dump-sql                                                                                                  ALTER TABLE groups CHANGE active active TINYINT(1) DEFAULT '1' NOT NULL;
ALTER TABLE module CHANGE root_order root_order INT DEFAULT 0 NOT NULL

My entities define these columns like this:

Group
    /**
     * @ORM\Column(name="active", type="boolean", options={"default":true})
     */
    private $active = true;
Module
    /**
     * @ORM\Column(name="root_order", type="integer", options={"default":0})
     */
    private $rootOrder = 0;


 Comments   
Comment by Felix Kaser [ 30/Apr/13 ]

We are having the same issue. Is there any workaround for this?

Comment by Felix Kaser [ 30/Apr/13 ]

I've investigated a bit and noticed several things:

  • looks like doctrine does not (officially?) support setting of default options in the annotation. It is recommended to use the instance default ($rootOrder = 0).
  • in the comparator class the Column->getDefault() is compared, but I found out that in one table (I guess the one from mysql) the default is set directly, in the other one (I guess the doctrine one generated from the annotations) the default is empty but the customSchemaOptions["default"] is set.

So based on this I would say:

  • the $column->getDefault needs to be checked against the other $column->getCustomSchemaOption("default") and vice versa

Please correct me if I'm wrong





[DBAL-482] SQL Server Schema Manager returns incorrect value for autoincrement on IDENTITY columns Created: 03/Apr/13  Updated: 01/May/13  Resolved: 01/May/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.2, 2.3, 2.3.3
Fix Version/s: 2.3.4
Security Level: All

Type: Bug Priority: Minor
Reporter: William Schaller Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: schematool, sqlserver, sqlsrv
Environment:

SQL Server



 Description   

When calculating table diffs, SQLServerSchemaManager returns column definitions for identity columns with _autoincrement set to FALSE.

This causes the schema update SQL generation to pump out a
ALTER TABLE x ALTER COLUMN id INT IDENTITY NOT NULL
for every single identity column in the schema.

The culprit is in DBAL\Schema\SQLServerSchemaManager, starting at line 43:

        $dbType = strtolower($tableColumn['TYPE_NAME']);
        $dbType = strtok($dbType, '(), ');

        $autoincrement = false;
        if (stripos($dbType, 'identity')) {
            $dbType = trim(str_ireplace('identity', '', $dbType));
            $autoincrement = true;
        }

When the column in question is an identity int column, the TYPE_NAME is "int identity". The second line of the snippet drops the "identity" signifier, causing the following lines that determine autoincrement to do nothing.

I simply moved the second line to below the autoincrement block ie:

        $dbType = strtolower($tableColumn['TYPE_NAME']);

        $autoincrement = false;
        if (stripos($dbType, 'identity')) {
            $dbType = trim(str_ireplace('identity', '', $dbType));
            $autoincrement = true;
        }

        $dbType = strtok($dbType, '(), ');

This change solves this issue for me, and as far as I can tell, has no other consequences.



 Comments   
Comment by Benjamin Eberlei [ 01/May/13 ]

Fixed for 2.3.4 and was fixed for 2.4 in a different way already.





[DBAL-474] SchemaManager / Connection on PostgreSQL platform does not respect filterExpression for sequences Created: 27/Mar/13  Updated: 16/May/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.2.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: jos de witte Assignee: Steve Müller
Resolution: Unresolved Votes: 1
Labels: postgresql, schematool
Environment:

Windows & Linux



 Description   

Dear Symfony team,

the filterExpression on AbstractSchemaManager seems not to work for sequences.

This only happens under postgres.

It seems the way the sequences are handled are the culprit: It tries to get min_value etc of sequences without matching sequence names to the filter expression in advance.

If for example access to the sequences is denied, (Different schema without permissions for the current entity manager), any higher-level ORM operations like generating migration versions fail.

--------------------- UPDATE

the context is when using migrations. Positive regexp expressions do not limit the migration to a single schema. eg ^schemaname.$
Instead, all sequences on the current database are returned.
When trying to limit a migration to a single schema consecutively this doesn't work.
We are using a per-schema connection, so this results in a lot of hassle for us.



 Comments   
Comment by Benjamin Eberlei [ 14/Apr/13 ]

Can you paste an exception trace? I see that filtering is applied to sequences, but your description seems to indicate this happens due to an SQL query much earlier?

Comment by jos de witte [ 24/Apr/13 ]

Dear Benjamin,

the context is when using migrations. Positive regexp expressions do not limit the migration to a single schema. eg ^schemaname.$
Instead, all sequences on the current database are returned.
When trying to limit a migration to a single schema consecutively this doesn't work.
We are using a per-schema connection, so this results in a lot of hassle for us.

Comment by Steve Müller [ 06/Jan/14 ]

jos de witte I think your issue got fixed in commit: https://github.com/doctrine/dbal/commit/8beb732fe9d5cd40a0d677f250d2be325482744f
This patch was first introduced in 2.3. Can you please confirm that this is fixed? Otherwise can you please provide a concrete example so that we can reproduce you issue?

Comment by Arnout Standaert [ 29/Jan/14 ]

I believe we are bumping into the same issue. Our webapp uses Migrations, but for our webapp we are limited to a certain schema within a bigger PostgreSQL database. We only have permissions on our own schema and public.
Now, listSequences in AbstractSchemaManager does filter the asset names correctly with the mentioned fix.

But the problem is with the step before, _getPortableSequencesList (see line 135 of AbstractSchemaManager):

        return $this->filterAssetNames($this->_getPortableSequencesList($sequences));

This function goes out and does a _getPortableSequenceDefinition on every sequence in the unfiltered list. For every sequence, the _getPortableSequenceDefinition in PostgreSqlSchemaManager performs a SELECT:

        $data = $this->_conn->fetchAll('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));

Now, our user role in the database doesn't have SELECT permissions on these sequences in other schemas, so the migration fails with a privilege error.

There should be some kind of filtering on the sequence list BEFORE the SELECT statement in the _getPortableSequenceDefinition function are performed, no?

Comment by Arnout Standaert [ 30/Jan/14 ]

I currently have a workaround running locally, which filters the sequences list before creating the PortableSequence's. This is probably hackish and a poor workaround, just posting here as a temporary solution and further illustration of the problem.

Altered line 135 in AbstractSchemaManager:

        return $this->_getPortableSequencesList($this->filterSequenceNames($sequences));

I added function filterSequenceNames() in AbstractSchemaManager for appropriate sequence filtering:

    /**
     * Filters sequence names if they are configured to return only a subset of all
     * the found elements.
     *5
     * @param array $sequenceNames
     *
     * @return array
     */
    protected function filterSequenceNames($sequenceNames)
    {
        $filterExpr = $this->getFilterSchemaAssetsExpression();
        if ( ! $filterExpr) {
            return $sequenceNames;
        }
        
        return array_values ( array_filter($sequences, function ($sequenceName) use ($filterExpr) {
                $sequenceName = $sequenceName["schemaname"].".".$sequenceName["relname"];
                return preg_match($filterExpr, $sequenceName);
            })
        );

    }

After these modifications, doctrine:migrations:migrate operations complete succesfully, even with our limited-permission database account.

Comment by Steve Müller [ 21/Feb/14 ]

Arnout Standaert Your fix looks promising and reasonable. Can you create a PR on the DBAL repo for that?

Comment by Viktor Sidochenko [ 15/Mar/14 ]

Why this fix is not in master?

Comment by Steve Müller [ 15/Mar/14 ]

Viktor Sidochenko because nobody has fixed it yet Feel free to provide a patch on GitHub.

Comment by Arnout Standaert [ 17/Mar/14 ]

I haven't gotten around to doing the PR on GitHub yet, I'm not yet too familiar with that.
I'll try to find some time for this the coming days.

Comment by Viktor Sidochenko [ 17/Mar/14 ]

Will be good. I`m not professional developer to make patches to upstream. So just voted for this issue.

Comment by Steve Müller [ 19/Mar/14 ]

Patch supplied in PR: https://github.com/doctrine/dbal/pull/546
jos de witte, Arnout Standaert, Viktor Sidochenko can you please test if the supplied PR fixes the problem?

Comment by Doctrine Bot [ 16/May/14 ]

A related Github Pull-Request [GH-546] was closed:
https://github.com/doctrine/dbal/pull/546





[DBAL-420] Schema Drop SQL incorrect on PostgreSQL with entities with GeneratedValue(strategy="IDENTITY") Created: 23/Jan/13  Updated: 14/Apr/13  Resolved: 14/Apr/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.3.2
Fix Version/s: 2.3.4
Security Level: All

Type: Bug Priority: Major
Reporter: Adam Ashley Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 1
Labels: postgresql, schematool
Environment:

Symfony 2.1, PHP5.4, PostgreSQL 9.1 on Ubuntu 12.04



 Description   

This problem is probably related to #DBAL-54. However that was closed by the raiser as he changed his Entity model and it went away.

When schema drop is run the following error occurs:
Doctrine\DBAL\DBALException: An exception occurred while executing 'DROP SEQUENCE radacct_radacctid_seq':

SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR: cannot drop sequence radacct_radacctid_seq because other objects depend on it
DETAIL: default for table radacct column radacctid depends on sequence radacct_radacctid_seq
HINT: Use DROP ... CASCADE to drop the dependent objects too.

The source of this problem is the difference between strategy="IDENTITY" and strategy="SEQUENCE"

With SEQUENCE doctrine creates the table schema with field type BIGINT and no specified. It then creates a seperate sequence and as far as I can tell takes care of getting and inserting the next id number itself.

With IDENTITY doctrine creates the table schema with field type BIGSERIAL and no specified default. Now postgres automatically creates a sequence and creates the column with type BIGINT and sets the DEFAULT to the pgpsql statement required to get the nextval from the sequence.

At this point the two differently configured tables will work successfully and identically, except SEQUENCE tables will only get a correct new ID when run through the doctrine code while IDENTITY tables will get the correct new ID whenever an insert is done to the table.

Because in the case of an IDENTITY field postgresql creates the field with a default value refering to the sequence the sequence can not be deleted before the table reference is removed.

For my case I need the IDENTITY fields to work as we have a RADIUS server that needs to insert into one table which is managed and mapped to an entity in Doctrine.

Swapping the order of DROP TABLE and DROP SEQUENCE commands in Doctrine/DBAL/Schema/Visitor/DropSchemaSqlCollector.php in getQueries() line 159. Does not work as a quick fix. The following error occurs as the sequence is quite correctly be dropped along with the table.

Doctrine\DBAL\DBALException: An exception occurred while executing 'DROP SEQUENCE radacct_radacctid_seq':

SQLSTATE[42P01]: Undefined table: 7 ERROR: sequence "radacct_radacctid_seq" does not exist



 Comments   
Comment by Adam Ashley [ 24/Jan/13 ]

This issue also appears to affect Doctrine_Migrations. Generated migrations try to drop and recreate automatically generated sequences associated with SERIAL fields making a mess of the database.

Comment by Benjamin Eberlei [ 22/Mar/13 ]

A related Github Pull-Request [GH-289] was opened
https://github.com/doctrine/dbal/pull/289

Comment by Adrien Crivelli [ 12/Apr/13 ]

@Adam Ashley, could you test whether https://github.com/doctrine/dbal/pull/289 solve your issue ?

Comment by Doctrine Bot [ 14/Apr/13 ]

A related Github Pull-Request [GH-289] was closed:
https://github.com/doctrine/dbal/pull/289





[DBAL-411] Schema updater breaks when using backticks in tablenames. Created: 08/Jan/13  Updated: 22/Dec/13  Resolved: 22/Dec/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.3.2
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Minor
Reporter: Endaco Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 1
Labels: schematool
Environment:

Debian Linux 6.0, PHP 5.3.3, MySQL 5.1.63



 Description   

When using backticks around table-names (for example "`Order`"), the Doctrine schema update tool wants to recreate all foreign keys on every run.

This error was introduced in commit cb3ec49cb4401bd1c8be6ba9671f651802586eaf



 Comments   
Comment by Steve Müller [ 22/Dec/13 ]

Fixed in commit: https://github.com/doctrine/dbal/commit/1af95fc20b6c345ee82d69082d6c58f7fcdde28e
Related PR: https://github.com/doctrine/dbal/pull/302





[DBAL-367] Reverse engnering do not work with Oracle DB Created: 18/Oct/12  Updated: 21/Nov/13  Resolved: 21/Nov/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers, Schema Managers
Affects Version/s: 2.3
Fix Version/s: 2.5

Type: Bug Priority: Major
Reporter: Zelenin Alexandr Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: Cli, oracle, schematool
Environment:

PHP 5.3.3-1ubuntu9.10 with Suhosin-Patch (cli) (built: Feb 11 2012 06:21:15)
oci8-1.4.7 as PHP extension builded from pecl repository with instantclient-basic-linux.x64-11.2.0.3.0.zip and instantclient-sdk-linux.x64-11.2.0.3.0.zip
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production



 Description   
$ doctrine orm:convert-mapping --filter="ms$ions" xml .

  [Doctrine\DBAL\DBALException]
  Unknown database type binary_float requested, Doctrine\DBAL\Platforms\OraclePlatform may not support it.

cli-config.php:


use Doctrine\Common\Annotations\AnnotationReader;
use Doctrine\Common\Annotations\AnnotationRegistry;

require_once 'Doctrine/Common/ClassLoader.php';

define('APPLICATION_ENV', "development");
error_reporting(E_ALL);

$classLoader = new \Doctrine\Common\ClassLoader('Doctrine');
$classLoader->register();

$config = new \Doctrine\ORM\Configuration();
$config->setProxyDir(__DIR__);
$config->setProxyNamespace('Proxies');

$config->setAutoGenerateProxyClasses((APPLICATION_ENV == "development"));

AnnotationRegistry::registerFile("Doctrine/ORM/Mapping/Driver/DoctrineAnnotations.php");
$reader = new AnnotationReader();
$driverImpl = new \Doctrine\ORM\Mapping\Driver\AnnotationDriver($reader, array(__DIR__ . "/../php/ru/niifhm/bioinformatics/biodb/model"));
$config->setMetadataDriverImpl($driverImpl);

if (APPLICATION_ENV == "development") {
    $cache = new \Doctrine\Common\Cache\ArrayCache();
} else {
    $cache = new \Doctrine\Common\Cache\ApcCache();
}

$config->setMetadataCacheImpl($cache);
$config->setQueryCacheImpl($cache);

$connectionOptions = array(
    'driver'   => 'oci8',
    'host'     => 'host.name',
    'dbname'   => 'db.name',
    'user'     => 'user.name',
    'password' => 'user.password'
);

$em = \Doctrine\ORM\EntityManager::create($connectionOptions, $config);
$platform = $em->getConnection()->getDatabasePlatform();
$platform->registerDoctrineTypeMapping('enum', 'string');

$em->getConfiguration()->setMetadataDriverImpl(
    new \Doctrine\ORM\Mapping\Driver\DatabaseDriver(
        $em->getConnection()->getSchemaManager()
    )
);

$helperSet = new \Symfony\Component\Console\Helper\HelperSet(array(
    'db' => new \Doctrine\DBAL\Tools\Console\Helper\ConnectionHelper($em->getConnection()),
    'em' => new \Doctrine\ORM\Tools\Console\Helper\EntityManagerHelper($em)
));


 Comments   
Comment by Marco Pivetta [ 23/Jan/13 ]

Formatting

Comment by Steve Müller [ 07/Nov/13 ]

Should be fixed with following PR:

https://github.com/doctrine/dbal/pull/405

Comment by Doctrine Bot [ 13/Nov/13 ]

A related Github Pull-Request [GH-405] was closed:
https://github.com/doctrine/dbal/pull/405





[DBAL-346] Generated schema fails on MySQL (BLOB/TEXT cant have DEFAULT value) Created: 19/Sep/12  Updated: 20/Sep/12  Resolved: 20/Sep/12

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.2.2
Fix Version/s: 2.3
Security Level: All

Type: Bug Priority: Major
Reporter: Sascha Ahmann Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: Cli, schematool
Environment:

Symfony 2.1.x (dev/master), Doctrine 2.2.3, MySQL 5.5.x



 Description   

In my symfony 2.1 project i was including JMSPaymentCoreBundle and did the vanilla installation.

After that i ran

php app/console doctrine:schema:update --dump-sql
php app/console doctrine:schema:update --force

A lot of schema updates were done. I ran it again, and two schema updates were still showing up.
Running the command again, and they still show up.

I then tried to run the two schema updates against my database and MySQL complains with the following error:

Error: 1101 - BLOB/TEXT column 'extended_data' can't have a default value

According to the Documentation BLOB/TEXT indeed cannot have default values. I am not sure why Doctrine thinks it has to set this default value.

The statements look like this:

sascha@debian:/var/www/myproject/Symfony$ php app/console doctrine:schema:update --dump-sql
ALTER TABLE payment_instructions CHANGE extended_data extended_data LONGTEXT NOT NULL COMMENT '(DC2Type:extended_payment_data)';
ALTER TABLE financial_transactions CHANGE extended_data extended_data LONGTEXT DEFAULT NULL COMMENT '(DC2Type:extended_payment_data)'

I already reported this problem in the JMSPaymentCoreBundle Issue Queue where i then was referred to over here.

Also, i am not sure if Doctrine DBAL is the best match for this issue, so if it is wrong please move it to the right project issue queue.

Thank you very much and best of Regards!
Sascha






Generated at Sat Nov 22 11:58:23 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.