[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: 21/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


 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: 2
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 Thu Oct 23 15:22:37 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.