[DDC-3735] Problem with Collate Created: 13/May/15  Updated: 13/May/15

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

Type: Bug Priority: Major
Reporter: Hugo Henrique Assignee: Marco Pivetta
Resolution: Unresolved Votes: 0
Labels: dbal, mapping, mysql, schematool
Environment:

development



 Description   

I'm using Migrations and always when a new version changes in my schema in action `up` SQL removes the definition of the table COLLATE for example:

Version20150513194922.php
public function up(Schema $schema)
{
    $this->addSql('ALTER TABLE customer CHANGE user_id user_id CHAR(36) DEFAULT NULL COMMENT \'(DC2Type: guid)\'');
}

public function down(Schema $schema)
{
    $this->addSql('ALTER TABLE customer CHANGE user_id user_id CHAR(36) NULL DEFAULT COLLATE utf8_unicode_ci COMMENT \'(DC2Type: guid)\'');
}
User.php
/**
 * @ORM\Table(name="user")
 * @ORM\HasLifecycleCallbacks
 */
class User
{
    /**
     * @ORM\Id
     * @ORM\Column(type="guid", options={"unsigned"=true})
     * @ORM\GeneratedValue(strategy="UUID")
     */
    protected $id;

Customer.php
/**
 * @ORM\Entity
 * @ORM\Table(name="customer")
 */
class Customer
{
    /**
     * @ORM\Id
     * @ORM\Column(type="bigint", options={"unsigned"=true})
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @ORM\OneToOne(targetEntity="User")
     * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
     */
    protected $user;

schema.sql
CREATE TABLE `user` (
  `id` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '(DC2Type:guid)',
  `name` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `username` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `username_canonical` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email_canonical` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `salt` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_8D93D649E7927C74` (`email`),
  UNIQUE KEY `UNIQ_8D93D649F85E0677` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `customer` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '(DC2Type:guid)',
  `gender` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `zipcode` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_705B3727A76ED395` (`user_id`),
  CONSTRAINT `FK_705B3727A76ED395` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;





[DDC-3732] Entity with string @id can't be fetches Created: 10/May/15  Updated: 11/May/15  Resolved: 10/May/15

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

Type: Bug Priority: Blocker
Reporter: Peter Zwegat Assignee: Marco Pivetta
Resolution: Invalid Votes: 0
Labels: mysql, orm
Environment:

MySQL



 Description   

I defined an entity id field with the annotation:

@ORM\Id
@ORM\Column(type="string", length=2)

It's not possible to fetch the entity neither with ->find() nor manually with a query builder (quoted etc., tried everything).

$qb->where('l.id = ?1');
$qb->setParameters([
1 => $this->getEntityManager()->getConnection()->quote($id)
]);

This return just null even if the entity with the given id exists.

When I not quote the passed id value it, I become this error:

Doctrine\ORM\Query\QueryException: [Semantical Error] line 0, col 121 near 'fi': Error: 'fi' is not defined. (/..../vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php: 63)

'fi' ist just the country identifier here that is the entity id.



 Comments   
Comment by Marco Pivetta [ 10/May/15 ]

Quoting parameters explicit is not necessary.

Also, quoting parameters is not needed in DQL, as DQL is translated by looking at mappings anyway.

Comment by Peter Zwegat [ 10/May/15 ]

Yeah I only quoted the id for test purposes.
I go a step back:
->find('de') throws the exception that I posted in the issue.

String Id's are possible normally not?

Comment by Marco Pivetta [ 10/May/15 ]

Peter Zwegat yes, it is allowed, but what exception are you getting?

Comment by Peter Zwegat [ 11/May/15 ]

This one when I try to find an entity by it's id:

Doctrine\ORM\Query\QueryException: [Semantical Error] line 0, col 121 near 'fi': Error: 'fi' is not defined. (/..../vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php: 63)

'fi' is here the key. Just used the ->find method.

Comment by Marco Pivetta [ 11/May/15 ]

Please show some actual code in a gist - I'm doing guesswork here :-P





[DDC-3718] Inifinite schema diff when using decimal with options unsigned Created: 29/Apr/15  Updated: 29/Apr/15

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

Type: Bug Priority: Major
Reporter: Khang Minh Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: mysql, orm, schematool
Environment:

Php 5.6.7 (cli)
MariaDB 10.0.17
Symfony 2.6.1



 Description   

When using a mapping similar to:

        field_name:
            type: decimal
            scale: 2
            options:
                unsigned: true

I keep getting this when issuing app/console doctrine:schema:update --dump-sql (symfony console):

ALTER TABLE table_name CHANGE field_name field_name NUMERIC(10, 2) NOT NULL;

could be related to: http://www.doctrine-project.org/jira/browse/DC-752






[DDC-3680] [GH-1377] Failing test case for broken paginator case Created: 08/Apr/15  Updated: 09/Apr/15  Resolved: 09/Apr/15

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

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Bill Schaller
Resolution: Fixed Votes: 0
Labels: limitsubqueryoutputwalker, mysql, paginator


 Description   

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

Url: https://github.com/doctrine/doctrine2/pull/1377

Message:

http://www.doctrine-project.org/jira/browse/DDC-3679



 Comments   
Comment by Doctrine Bot [ 08/Apr/15 ]

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

Comment by Doctrine Bot [ 08/Apr/15 ]

A related Github Pull-Request [GH-1377] was labeled:
https://github.com/doctrine/doctrine2/pull/1377

Comment by Doctrine Bot [ 09/Apr/15 ]

A related Github Pull-Request [GH-1377] was merged:
https://github.com/doctrine/doctrine2/pull/1377





[DDC-3679] Paginator generates broken SQL when paginating ordered query Created: 08/Apr/15  Updated: 09/Apr/15

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

Type: Bug Priority: Major
Reporter: Filip Procházka Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 1
Labels: dql, mysql, orm


 Description   

I have a following DQL

SELECT ra FROM Damejidlo\Recipes\RecipeAuthor ra ORDER BY ra.id ASC

Which generates following SQL

SELECT r0_.id AS id_0, r0_.name AS name_1, r0_.perex AS perex_2, r0_.is_active AS is_active_3, r0_.on_homepage AS on_homepage_4, r0_.content AS content_5, r0_.photo AS photo_6, r0_.link AS link_7 FROM recipes_authors r0_ ORDER BY r0_.id ASC

Now I wanna paginate it

$query = $em->createQuery($dql);
$paginated = new \Doctrine\ORM\Tools\Pagination\Paginator($query, TRUE);

the generated SQL Count query is

SELECT COUNT(*) AS dctrn_count FROM (SELECT DISTINCT id_0 FROM (SELECT r0_.id AS id_0, r0_.name AS name_1, r0_.perex AS perex_2, r0_.is_active AS is_active_3, r0_.on_homepage AS on_homepage_4, r0_.content AS content_5, r0_.photo AS photo_6, r0_.link AS link_7 FROM recipes_authors r0_ ORDER BY r0_.id ASC) dctrn_result) dctrn_table

Next executed query is the query that selects ids for where in

SELECT DISTINCT id_0 FROM (SELECT r0_.id AS id_0, r0_.name AS name_1, r0_.perex AS perex_2, r0_.is_active AS is_active_3, r0_.on_homepage AS on_homepage_4, r0_.content AS content_5, r0_.photo AS photo_6, r0_.link AS link_7 FROM recipes_authors r0_) dctrn_result ORDER BY r0_.id ASC LIMIT 5 OFFSET 0

And there goes my problem

Doctrine\DBAL\Exception\InvalidFieldNameException

An exception occurred while executing '...': SQLSTATE[42S22]: Column not found: 1054 Unknown column 'r0_.id' in 'order clause'

I'm going to investigate more, I just wanned to open an issue for now so you're aware.



 Comments   
Comment by Filip Procházka [ 08/Apr/15 ]
aa80c7d!orm $> bisect start
aa80c7d!orm $> bisect bad
aa80c7d!orm $> bisect good v2.4.7
Bisecting: a merge base must be tested
[29d6da0fa063d55d06117045f3446b2716202d2b] Merge pull request #703 from shulcsm/patch-1
29d6da0!orm $> bisect good
Bisecting: 912 revisions left to test after this (roughly 10 steps)
[a2e0133a94bf235498ee2e805c8ca9a147b7fa24] Adding DDC-3276 test group
a2e0133!orm $> bisect good
Bisecting: 456 revisions left to test after this (roughly 9 steps)
[dde09872df4f442f3a2a4c9ce6053badb8b49012] #1172 - writing a more concise test case about merging detached proxies
dde0987!orm $> bisect good
Bisecting: 233 revisions left to test after this (roughly 8 steps)
[d024193cc0d106d4939bbf698f840ef122602d64] Merge pull request #1272 from Ocramius/hotfix/DDC-2704-merge-inherited-transient-properties
d024193!orm $> bisect good
Bisecting: 116 revisions left to test after this (roughly 7 steps)
[6cf76158a07219529631f7b8fb43c3b92d3552be] Merge pull request #1329 from Wilt/patch-1
6cf7615!orm $> bisect good
Bisecting: 58 revisions left to test after this (roughly 6 steps)
[d1a695b42b54ed7deb2f9461fee7d02d32514c3a] Typo in phpdoc
d1a695b!orm $> bisect good
Bisecting: 28 revisions left to test after this (roughly 5 steps)
[b923c937e2194ae38c88e31cfcea8d06b58603fc] Merge branch 'hotfix/#1352-entity-generator-new-class-metadata-hotfix'
b923c93!orm $> bisect good
Bisecting: 14 revisions left to test after this (roughly 4 steps)
[6c5dbd8d4ca4fd68f0d5b2de467c3d2a59e0173c] #1353 #1347 #1351 - Removing double quotes (confusing)
6c5dbd8!orm $> bisect bad
Bisecting: 6 revisions left to test after this (roughly 3 steps)
[af3f5c5c5a93daf240c2daae4eddf9a0628eeb19] Add test for paginating on a query with a subquery in the where clause
af3f5c5!orm $> bisect good
Bisecting: 3 revisions left to test after this (roughly 2 steps)
[b76107e20f7fa893c2e4945a373ff624ed00c0d2] resolve review comments from @stof
b76107e!orm $> bisect bad
Bisecting: 0 revisions left to test after this (roughly 1 step)
[edcc0fc024e0d33a4f6159c006c0f393386dac3a] Fix paginator when ordering by while selecting entities using joined table inheritance
edcc0fc!orm $> bisect bad
Bisecting: 0 revisions left to test after this (roughly 0 steps)
[09d28819b520b9219bd6f41e8c6e0f4449a3807b] Fix issue where paginating on a query with a subquery in the where clause crashed
09d2881!orm $> bisect good
edcc0fc024e0d33a4f6159c006c0f393386dac3a is the first bad commit
commit edcc0fc024e0d33a4f6159c006c0f393386dac3a
Author: Bill Schaller <bill@zeroedin.com>
Date:   Mon Mar 30 12:16:50 2015 -0400

    Fix paginator when ordering by while selecting entities using joined table inheritance

:040000 040000 aa2bd1b6cb6dc10ef39c3ea1affe78c75bb2b778 4dc9a0e15f847bfc940096263a639d6f1a4eb618 M      lib
:040000 040000 fa9a72d3eaae021b0015e43bbe64a9cf7eadf46e 7b6821dd22bee36c808e4680a427aa3ec75faad0 M      tests
09d2881!orm $>
Comment by Filip Procházka [ 08/Apr/15 ]

I've narrowed down the problem to this piece of code

lib/Doctrine/ORM/Tools/Pagination/LimitSubqueryOutputWalker.php
+            $fieldMapping = $class->fieldMappings[$columnName];
+            if (isset($fieldMapping['declared']) && $fieldMapping['declared'] !== $class->name) {
+                // Field was declared in a parent class, so we need to get the proper SQL table alias
+                // for the joined parent table.
+                $otherClassMetadata = $this->_em->getClassMetadata($fieldMapping['declared']);
+                $sqlTableAliasForFieldAlias = $this->getSQLTableAlias($otherClassMetadata->getTableName(), $dqlAliasForFieldAlias);
+            }

This breaks the searchPatterns map

Because it thinks it needs to use different table alias for mapped superclass that contains the `id` property.

Comment by Filip Procházka [ 08/Apr/15 ]

I've opened a pullrequest with failing test case https://github.com/doctrine/doctrine2/pull/1377

Comment by Doctrine Bot [ 08/Apr/15 ]

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

Comment by Doctrine Bot [ 08/Apr/15 ]

A related Github Pull-Request [GH-1377] was labeled:
https://github.com/doctrine/doctrine2/pull/1377

Comment by Doctrine Bot [ 09/Apr/15 ]

A related Github Pull-Request [GH-1377] was merged:
https://github.com/doctrine/doctrine2/pull/1377





[DDC-3673] Foreign key ignores options on schema update Created: 07/Apr/15  Updated: 07/Apr/15  Resolved: 07/Apr/15

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

Type: Bug Priority: Major
Reporter: Yuri Pimenov Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: mysql, orm
Environment:

"php": ">=5.3.3", "doctrine/orm": "2.6.*@dev" (tried with several older versions with no success), Mysql configuration in attachment.


Attachments: File mysql_variables.csv    

 Description   

To reproduce create 2 tables:

CREATE TABLE `books` (
  `id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `authors` (
  `id` INT UNSIGNED NOT NULL,
  `book_id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

Pay attention that books.id is UNSIGNED and authors.book_id is not UNSIGNED (default). This is my legacy schema, I reverse engineered it to Doctrine entities like so:

Author.php
<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Table(name="authors")
 * @ORM\Entity
 */
class Author
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer", options={"unsigned"=true})
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="title", type="string", length=45)
     */
    private $name;

    /**
     * @var Book
     *
     * @ORM\ManyToOne(targetEntity="Book")
     * @ORM\JoinColumn(name="book_id", referencedColumnName="id")
     */
    private $book;
}

Book.php
<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Table(name="books")
 * @ORM\Entity
 */
class Book
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="title", type="string", length=45)
     */
    private $title;
}

Please notice

options={"unsigned"=true}

in Book.id definition. Actually it does not have an effect (I guess it must).

Then when I run

$> app/console doctrine:schema:update --dump-sql --force

get the mysql error:

ALTER TABLE authors ADD CONSTRAINT FK_8E0C2A5116A2B381 FOREIGN KEY (book_id) REFERENCES books (id);
CREATE INDEX IDX_8E0C2A5116A2B381 ON authors (book_id);
ALTER TABLE books CHANGE id id INT AUTO_INCREMENT NOT NULL, CHANGE title title VARCHAR(45) NOT NULL;

Updating database schema...

  [Doctrine\DBAL\Exception\DriverException]
  An exception occurred while executing 'ALTER TABLE authors ADD CONSTRAINT FK_8E0C2A5116A2B381 FOREIGN KEY (book_id) REFERENCES books (id)':
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

  [Doctrine\DBAL\Driver\PDOException]
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

  [PDOException]
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constrain

Than if I manually ALTER Author and then add foreign key constraint it works:

ALTER TABLE `authors` 
CHANGE COLUMN `book_id` `book_id` INT(11) UNSIGNED NULL DEFAULT NULL ;

ALTER TABLE authors ADD CONSTRAINT FK_8E0C2A5116A2B381 FOREIGN KEY (book_id) REFERENCES books (id)

But if I run doctrine:schema:update again it recreates definition with the same error:

ALTER TABLE authors DROP FOREIGN KEY FK_8E0C2A5116A2B381;
ALTER TABLE authors CHANGE book_id book_id INT DEFAULT NULL;
DROP INDEX fk_8e0c2a5116a2b381 ON authors;
CREATE INDEX IDX_8E0C2A5116A2B381 ON authors (book_id);
ALTER TABLE authors ADD CONSTRAINT FK_8E0C2A5116A2B381 FOREIGN KEY (book_id) REFERENCES books (id);
ALTER TABLE books CHANGE id id INT AUTO_INCREMENT NOT NULL, CHANGE title title VARCHAR(45) NOT NULL;

Updating database schema...

  [Doctrine\DBAL\Exception\DriverException]
  An exception occurred while executing 'ALTER TABLE authors ADD CONSTRAINT FK_8E0C2A5116A2B381 FOREIGN KEY (book_id) REFERENCES books (id)':
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

  [Doctrine\DBAL\Driver\PDOException]
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

  [PDOException]
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint


 Comments   
Comment by Yuri Pimenov [ 07/Apr/15 ]

Just checked test setup one more time. options=

{"unsigned":true}

were defined in wrong Entity.





[DDC-3573] DateTime objects casted to string when used in aggregate functions in query results Created: 18/Feb/15  Updated: 18/Feb/15

Status: Open
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: Ameer Antar Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dbal, mysql, orm, querybuilder
Environment:

MySQL, possibly others



 Description   

Similar to DDC-657: http://www.doctrine-project.org/jira/browse/DDC-657

DateTime objects are converted to strings when selecting them through an aggregate function.

Example:

Update.php Column definition
    /**
     * @var \DateTime
     * @ORM\Column(name="entryDate", type="datetime")
     */
    private $entryDate;
Query Builder
		$qb = $this->getEntityManager()->createQueryBuilder();
		$qb->select('u.code, max(u.entryDate) as updateDate');
		$qb->from('Update', 'u');
		$qb->addGroupBy('u.code');

		var_dump( $qb->getQuery()->getResult() );

The result contains the date/times as strings not DateTime objects as expected. This affects the query builder. I assume it also affects DQL queries.






[DDC-3364] QueryBuilder fails when using alias in having with like expr Created: 21/May/14  Updated: 26/Oct/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: webDEVILopers Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: GROUP_CONCAT, dql, expression, having, like, mysql
Environment:

Debian, MySQL, PHP, Zend Framework 2, Doctrine Module, Doctrine Extensions



 Description   

In my select I create an alias. I use a like expr on this alias in the having clause.

Trying several variations I still get the following error:
Expected '.' or '(', got 'long_name'

My example including variations inside comments:

$having = $qb->expr()->like('long_name', $qb->expr()->literal('%' . $term . '%'));

$qb->select(array(
    'b.id', 'b.name AS branch_name',
    'c.name AS company_name',
    'GroupConcat(b.name, \', \', c.name) AS long_name' // same for 'c.name AS long_name'
))
->from('Application\Entity\Branch', 'b')
->join('b.company', 'c')
->where('b.compartment = 1')
->having('(' . $having . ')') // same for having($having)
->groupBy('c.id')
->orderBy('c.name')
->addOrderBy('b.name');

I use a Doctrine Extension for the MySQL GROUP_CONCAT functionality:
https://github.com/beberlei/DoctrineExtensions/blob/master/lib/DoctrineExtensions/Query/Mysql/GroupConcat.php

This should have no effect on the result since I tried a simple alias - see comment - instead of it.

The problem seems to be using an alias inside the having clause when adding the like expr.
The following clause would work:

$having = $qb->expr()->like('c.name', $qb->expr()->literal('%' . $term . '%'));

I also tried this workaround using the GROUP_CONCAT method inside the where part:

->andWhere($qb->expr()->like('GroupConcat(b.name, \', \', c.name)', $qb->expr()->literal('%' . $term . '%')))

Allthough I used the groupBy part I got this error:
General error: 1111 Invalid use of group function



 Comments   
Comment by Marco Pivetta [ 26/Jun/14 ]

What is the actual failure/exception type? What about the generated SQL?

Comment by Steve Müller [ 26/Oct/14 ]

Moved to ORM, the error and use case is ORM related.





[DDC-3308] Cross platform support for DQL "WHERE ... IN" with multiple fields/columns Created: 13/Sep/14  Updated: 14/Sep/14

Status: Awaiting Feedback
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.4.2
Fix Version/s: None
Security Level: All

Type: New Feature Priority: Minor
Reporter: Markus Wößner Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dql, mysql, sqlite


 Description   

DQL does not support "WHERE ... IN" statements which refer to more than one field.

Example: While

... WHERE e.id IN (1, 2) ...

is valid DQL

... WHERE (e.id, e.name) IN ((1, "amy"), (2, "fred")) ...

is not.

This was discussed some years ago here https://groups.google.com/forum/#!msg/doctrine-user/bE9RfiF4ZGk/vaiEvsX5_rwJ and it appears that it is not SQL-99. Sqlite does not support it, MySQL does.

As http://sqlfiddle.com/#!7/6169b/1 shows it is not a big deal to transform such a query into an equal query which prevents the usage of "WHERE ... IN" constraints at all. I guess the case "pair(a,b) is not unique" can be safely ignored if redundant pairs are skipped on temp table insertion.



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

This looks everything but trivial to me, especially considering that it introduces usage of UNION, which we also do not support because of portability rules.

Comment by Markus Wößner [ 14/Sep/14 ]

As far as I understand the UNION statement would only be needed when doing an "inline table" approach.

Isn't it a usual technique in Doctrine "AST to SQL output walking" to write temp tables?

Comment by Marco Pivetta [ 14/Sep/14 ]

Yes, we use subqueries in a lot of places, but the paginator walkers are already a real mess from a maintenance PoV, so I don't think we want to go down that route.





[DDC-3270] abstract class database entity generation Created: 23/Aug/14  Updated: 19/Oct/14  Resolved: 19/Oct/14

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

Type: Bug Priority: Minor
Reporter: Yan Ni Assignee: Marco Pivetta
Resolution: Invalid Votes: 0
Labels: mysql, orm
Environment:

WAMP symfony2



 Description   

I create an abstract class A using @ORM annotations, then create class B which is a subclass of class A. When I use these to update the mysql database, however, a table for class A was also generated, which shouldn't have happened(because class A is an abstract class).



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

abstract types in the ORM are called MappedSuperclass}}es. The fact that a class is {{abstract doesn't mean that it has no concrete table representing it.





[DDC-3096] JoinColumn definition does not regard column type with value translation Created: 22/Apr/14  Updated: 22/Apr/14

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

Type: Bug Priority: Major
Reporter: Volker Nauruhn Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: entityPersister, mapping, mysql, orm
Environment:

Symfony 2.4.3



 Description   

I made a custom column type for doctrine which converts values between MySQL and PHP.

When I use a field with this column type as JoinColumn in a ManyToOne relation plus the column has a different name than the field, the BasicEntityPersister gets always "null" when he is asking for type of the given column name because he is ascing for given column name and not field name.

Example
=====================

Make.php:

/**
 @ORM\Column(name="language_code", type="locale")
 */
private $locale;

Foobar.php

/**
 @ORM\ManyToOne(targetEntity="Make")
 @ORM\JoinColumn(name="make_locale", referencedColumnName="language_code")
 */
private $makes;

The localeType translates between long and short language codes. For exmaple "de" (PHP) to "de_DE" (MySQL).



 Comments   
Comment by Marco Pivetta [ 22/Apr/14 ]

This is not a blocker, as you're really going into custom implementations.

You should probably provide a failing test case to clarify what you are doing





[DDC-2802] Mysql index length cannot be specified Created: 19/Nov/13  Updated: 23/May/14  Resolved: 20/Dec/13

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

Type: Bug Priority: Major
Reporter: Nikolay Konev Assignee: Benjamin Eberlei
Resolution: Won't Fix Votes: 0
Labels: mysql
Environment:

MS Windows 8, php 5.4



 Description   

MySQL don't allow creating indexes on BLOB/TEXT columns without speciifed size.

Trying to create index:

// colname is LONGTEXT
$tableScheme->addUniqueIndex(['colname'], 'index_name');

MySQL throws
> Syntax error or access violation: 1170 BLOB/TEXT column 'colname' used in key specification without a key length

I can suggest to improve parsing add*Index $columnNames params, for allowing per-column index size definition:

$tableScheme->addUniqueIndex(['colname(64)'], 'index_name');

It can be used not just in described case.



 Comments   
Comment by Nikolay Konev [ 19/Nov/13 ]

It matters, because indices definitions are part of tbale creating f.e. in MySQL.

For now it disallows to create table in one query.

Working on solution in forked repo now.

Comment by Benjamin Eberlei [ 20/Dec/13 ]

This is too specific a schema feature. Use EventListener to listen to events triggered from the MySQLPlatform to update the SQL accordingly.

Comment by Jesse Schalken [ 23/May/14 ]

Is it possible to get more information on how to solve this issue? Do I have to generate the CREATE TABLE statement myself? How does the ORM manage to apply indexes to text/string columns?

Thanks





[DDC-2747] Doctrine Checking Null but not default Created: 17/Oct/13  Updated: 17/Oct/13  Resolved: 17/Oct/13

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

Type: Bug Priority: Major
Reporter: Clay Garland Assignee: Marco Pivetta
Resolution: Won't Fix Votes: 0
Labels: default, mysql, nullable
Environment:

FreeBSD, PHP 5.4.13, MySQL 5.6.10



 Description   

Doctrine ORM checks fields for the acceptance of null values on entity commits. It does not, however, check if those same fields allow have default values. Our DBA likes to specify not-null, in addition to a default, so that a manual entry of a null value cannot take place. It seems that Doctrine should check that the field has a default value after it checks if it accepts null, in order to prevent errors on not-null fields with default values that are not passed explicit values.



 Comments   
Comment by Marco Pivetta [ 17/Oct/13 ]

Clay Garland this is not up to doctrine to check. If your schema enforces non-null fields, so should your object graph do as well.

That means that your setters or constructors should guarantee consistent state of the properties in your entities at any time, regardless of the ORM.

Comment by Marco Pivetta [ 17/Oct/13 ]

Also a side-note: the ORM always assumes that your data is valid. It just limits itself to saving and loading it.

Comment by Clay Garland [ 17/Oct/13 ]

Thank you for the clarification, I got a ticket from a dev and our DBA said it was doctrine was throwing the error, but now that I've looked at the Entity, he has no default value assigned to his private variable and doctrine is just doing what it's told.

Clay





[DDC-2658] Inserting NULL for Undefined Variables Created: 07/Sep/13  Updated: 07/Sep/13

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

Type: Bug Priority: Minor
Reporter: John Julien Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: default, mysql, null
Environment:

CentOS Linux, VMware, MySQL



 Description   

When a MySQL schema contains a line like this:
`failedattempts` int(11) NOT NULL DEFAULT '0',

If a new entity is created and the failedattempts variable is never set, Doctrine attempts to insert the value NULL which throws an SQL error. If a value is undefined for a new object, shouldn't it just be excluded from the insert fields?






[DDC-2632] Doctrine reverse engineer doesn't honor NOT NULL foreign keys Created: 24/Aug/13  Updated: 24/Aug/13

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

Type: Bug Priority: Minor
Reporter: Paolo Avezzano Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: engineer, mysql, null, nullable, reverse, yaml
Environment:

MAMP, latest version. Symfony up to date



 Description   

In this table, the two foreign keys (which I had to downgrade from primary keys because of Doctrine and then added a separate PK named 'Id') called 'Oggetto' and 'Sistema' have been set as NOT NULL.


-- -----------------------------------------------------
-- Table `mydb`.`Composizione`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`Composizione` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`Composizione` (
  `Id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `Oggetto` INT UNSIGNED NOT NULL ,
  `Sistema` INT NOT NULL ,
  INDEX `fk_Oggetto_has_Sistema_Sistema1_idx` (`Sistema` ASC) ,
  INDEX `fk_Oggetto_has_Sistema_Oggetto1_idx` (`Oggetto` ASC) ,
  PRIMARY KEY (`Id`) ,
  CONSTRAINT `fk_Oggetto_has_Sistema_Oggetto1`
    FOREIGN KEY (`Oggetto` )
    REFERENCES `mydb`.`Oggetto` (`Id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Oggetto_has_Sistema_Sistema1`
    FOREIGN KEY (`Sistema` )
    REFERENCES `mydb`.`Sistema` (`Id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Converting into Yaml here it is what I get.

    type: entity
    table: Composizione
    fields:
        id:
            id: true
            type: integer
            unsigned: true
            nullable: false
            column: Id
            generator:
                strategy: IDENTITY
    manyToOne:
        oggetto:
            targetEntity: Oggetto
            cascade:      {  }
            mappedBy:     null
            inversedBy:   null
            joinColumns:
                Oggetto:
                    referencedColumnName: Id
            orphanRemoval: false
        sistema:
            targetEntity: Sistema
            cascade:      {  }
            mappedBy:     null
            inversedBy:   null
            joinColumns:
                Sistema:
                    referencedColumnName: Id
            orphanRemoval: false
    lifecycleCallbacks: {  }

Basically it lost in translation the NOT NULL part.
To make it work I had to manually add two "nullable: false" lines as I did below:

    type: entity
    table: Composizione
    fields:
        id:
            id: true
            type: integer
            unsigned: true
            nullable: false
            column: Id
            generator:
                strategy: IDENTITY
    manyToOne:
        oggetto:
            targetEntity: Oggetto
            cascade:      {  }
            mappedBy:     null
            inversedBy:   null
            joinColumns:
                Oggetto:
                    referencedColumnName: Id
                    nullable:     false
            orphanRemoval: false
        sistema:
            targetEntity: Sistema
            cascade:      {  }
            mappedBy:     null
            inversedBy:   null
            joinColumns:
                Sistema:
                    referencedColumnName: Id
                    nullable:     false
            orphanRemoval: false
    lifecycleCallbacks: {  }

Is it a bug or am I missing something?

Regards,
Paolo Avezzano






[DDC-2555] Syntax Error when column is "separator" Created: 16/Jul/13  Updated: 16/Jul/13  Resolved: 16/Jul/13

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

Type: Bug Priority: Minor
Reporter: Tayniak Boris Assignee: Marco Pivetta
Resolution: Invalid Votes: 0
Labels: mysql, orm
Environment:

Symfony 2.3.1



 Description   

I had to made a column called "separator", 'Create Table' works, but when 'insert into':

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'separator) VALUES ('Test', 'Test', 'a:1:

{i:0;s:4:\"test\";}

', 'a:5:{s:10:\"http-' at line 1

After some searches ... Separator used by MySQL, so i changed the name:

php app/console d:s:u --force
Updating database schema...

[Doctrine\DBAL\DBALException]
An exception occurred while executing 'ALTER TABLE seo CHANGE separator link_separator VARCHAR(255) DEFAULT NULL':

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'separator link_separator VARCHAR(255) DEFAULT NULL' at line 1

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'separator link_separator VARCHAR(255) DEFAULT NULL' at line 1

Same Error... I will manualy update but for me it's a bug ...



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

Reserved SQL keywords need manual escaping by either using a custom quoting strategy or by marking the table name with backticks in mappings, such as:

"`separator`"

The ORM is not responsible for escaping identifiers out of the box.

Comment by Tayniak Boris [ 16/Jul/13 ]

I have never writen any SQL Request for this error
Only Doctrine, I re-use the request with ` but Doctrine create the table, the insert and alter the table

Create table: no error
Insert into: error
Alter table: error

use Doctrine\ORM\Mapping as ORM;
[...]
/**

  • @ORM\Column(name="separator", type="string", nullable=true)
    */
Comment by Marco Pivetta [ 16/Jul/13 ]

Tayniak Boris that's actually a problem in the DBAL layer. it should have failed during DDL execution





[DDC-2188] DQL arithmetic priority is not considered in expression 'a/(b*c)' Created: 05/Dec/12  Updated: 06/Apr/13  Resolved: 06/Apr/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL, ORM
Affects Version/s: 2.3
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Major
Reporter: Mahdi Dehghani Assignee: Fabio B. Silva
Resolution: Fixed Votes: 0
Labels: Cli, mysql


 Description   

A DQL query that contains an arithmetic expression like 'a/(b*c)' will be compiled to 'a/b*c' in SQL and therefore the result of '100/(2*2)' will be 100, instead of 25.

Note: I have tested it by mysql only



 Comments   
Comment by Marco Pivetta [ 16/Mar/13 ]

Being handled in DDC-2355





[DDC-2161] Moving entity relationship doesn't move foreign key in mysql table Created: 22/Nov/12  Updated: 03/Feb/14  Resolved: 25/Nov/12

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

Type: Bug Priority: Major
Reporter: Alessandro Tagliapietra Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: Cli, mysql
Environment:

Mac OSX, mysql 5.5.28, php 5.4.8, fresh symfony install with doctrine/orm 77d060ab74212919f1eaace72842ea43bf420f43


Attachments: File Argument.php     File Post.php     File Post2.php    
Issue Links:
Reference
relates to DBAL-392 Moving entity relationship doesn't mo... Resolved

 Description   

Edit: moved the bug to the dbal project, please delete this or set as invalid, new bug:

http://www.doctrine-project.org/jira/browse/DBAL-392

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

These are the steps I've tried to reproduce the problem:

  • composer create-project symfony/framework-standard-edition path/ 2.1.3
  • edited parameters for mysql db
  • created attached 'Post' entity (note the parent/children fields)
  • updated schema with doctrine:schema:create

I see that the foreign key in phpmyadmin is correctly set to Post.id which is correct.

Now:

  • created another entity 'Argument'
  • moving the children field from 'Post' to 'Argument' removing setters getters into 'Post' and changing target entity field accordingly
  • Updated DB schema (doctrine:schema:update --force)

The situation is now the one you can find in Post2 and Argument attachments.

The problem is that now, the foreign key in Post field it's still on Post.id and not on Argument.id and schema:update says the DB is in sync with the metadata.

Manually deleting the foreign key and running schema:update makes the key being correctly created into Argument.id



 Comments   
Comment by Benjamin Eberlei [ 24/Nov/12 ]

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

Comment by Benjamin Eberlei [ 25/Nov/12 ]

On wrong project, see DBAL-392

Comment by Doctrine Bot [ 03/Feb/14 ]

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





[DDC-2139] Table collate/charset doesn't inherits from database Created: 12/Nov/12  Updated: 25/Nov/12  Resolved: 25/Nov/12

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

Type: Bug Priority: Major
Reporter: William Knak Assignee: Benjamin Eberlei
Resolution: Won't Fix Votes: 0
Labels: charset, collate, collation, mysql, utf, utf8
Environment:

Symfony2



 Description   

Using symfony2 command doctrine:schema:create the tables and Varchar fields are Collate/Charset are not inherited from database. I changed MySQL defaults to latin1/latin1_general_ci; The doctrine:database:create works; but tables and fields don't. So I found there is a code on MySqlPlataform.php:

if ( ! isset($options['charset']))

{ $options['charset'] = 'utf8'; }

if ( ! isset($options['collate']))

{ $options['collate'] = 'utf8_unicode_ci'; }

$query .= 'DEFAULT CHARACTER SET ' . $options['charset'];
$query .= ' COLLATE ' . $options['collate'];

So if you don't pass collate and charset as entity options, it will put utf8 and utf8_unicode_ci instead of dabatase default charset.

My suggestion is to change a little bit to:

if (isset($options['charset']))

{ $query .= 'DEFAULT CHARACTER SET ' . $options['charset']; }

if (isset($options['collate']))

{ $query .= ' COLLATE ' . $options['collate']; }

 Comments   
Comment by Benjamin Eberlei [ 25/Nov/12 ]

If we change it, then it will be like before and users disliked that very much. The decision is to use UTF-8 as default and everybody else has to change the information instead of the other way around.





[DDC-1274] there is no way to set collate of the table charset ! Created: 13/Jul/11  Updated: 12/Nov/12  Resolved: 13/Jul/11

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

Type: Bug Priority: Critical
Reporter: sina miandashti Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: charset, collate, collation, mysql
Environment:

zend frame work 1.10 with doctrine orm 2 and dbal



 Description   

i searched a lot for this

there is no way to set the collate of table fields to for ex. utf8_persian_ci in doctrine settings



 Comments   
Comment by Benjamin Eberlei [ 13/Jul/11 ]

This is not a supported feature, if you set the collation in the CREATE DATABASE statement this will inherit to all fields automatically.

Comment by William Knak [ 12/Nov/12 ]

Actually Doctrine 2.3.0 does not allow inheritance. Please look at Issue #DDC-2139 . At least, if there's an option to set a global default collation (wich I didn't find), the problem will occurs every time a table of string/varchar field was created by Symfony2 Doctrine Command.





[DBAL-1182] No schema difference detected when changing length of a text field Created: 26/Mar/15  Updated: 26/Mar/15

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

Type: Bug Priority: Major
Reporter: Evan Sheffield Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dbal, mysql, schema-tool
Environment:

Database: MySQL 5.6



 Description   

I have a text field with a length specified that causes it to be chosen as TINYTEXT in MySQL.

/**  
 * @ORM\Column(name="message", type="text", length=255,  nullable=true)
 */
protected $message;

When I remove the length field from the annotation, I would expect the field to then be interpreted as LONGTEXT as specified in the documentation. However, when I run orm:schematool:update, it says that there is nothing to update.






[DBAL-1132] [GH-786] Fix removing autoincrement column from a primary key Created: 26/Jan/15  Updated: 10/Feb/15  Resolved: 10/Feb/15

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: 2.6, 2.4.5, 2.5.2
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: alter-table, autoincrement, mysql, primary-key

Issue Links:
Reference
relates to DBAL-464 MySQL fails when try to drop a primar... Resolved

 Description   

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

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

Message:

https://github.com/doctrine/dbal/pull/430 is only a partial fix for DBAL-464. This adds treating autoincrement columns that get removed when altering a primary key.



 Comments   
Comment by Doctrine Bot [ 01/Feb/15 ]

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

Comment by Doctrine Bot [ 10/Feb/15 ]

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





[DBAL-1116] [GH-774] Added SET and ENUM types for MySQL and fix issue with schema update tool Created: 14/Jan/15  Updated: 14/Jan/15  Resolved: 14/Jan/15

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

Type: New Feature Priority: Major
Reporter: Doctrine Bot Assignee: Marco Pivetta
Resolution: Won't Fix Votes: 0
Labels: ddl, enum, mysql, set, type

Issue Links:
Dependency
is required for DBAL-4 missing column type "enum" Resolved
Reference
relates to DBAL-89 MySqlPlatform does not handle enum a... Resolved

 Description   

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

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

Message:

Set and Enum data types are one of the most popular. However, addition of these data types in the project creates a problem when updating of the database structure.



 Comments   
Comment by Doctrine Bot [ 14/Jan/15 ]

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

Comment by Doctrine Bot [ 14/Jan/15 ]

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

Comment by Marco Pivetta [ 14/Jan/15 ]

See http://stackoverflow.com/a/9057352/347063

ENUM and SET are non-portable types that will not be implemented by the DBAL due to the complexity behind their handling.





[DBAL-1111] [GH-771] Fix unique index exception handling for an index on multiple columns in PHP 5.4 Created: 11/Jan/15  Updated: 11/Jan/15  Resolved: 11/Jan/15

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.5
Fix Version/s: 2.6, 2.5.1
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Marco Pivetta
Resolution: Fixed Votes: 0
Labels: exceptions, mysql, php-5.4, unique


 Description   

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

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

Message:

In SQLite under PHP 5.4, when I violate a unique index on multiple columns by creating a duplicate record, the PDO Exception thrown is "SQLSTATE[23000]: Integrity constraint violation: 19 columns X, Y are not unique". This PDO exception message is not parsed by the AbstractSQLiteDriver and converted to a UniqueConstraintViolationException as expected, and as happens in PHP 5.5, so only a generic DriverException is thrown, making error handling much harder.

This pull request adds "are not unique" as a string that is checked for in the exception handling code.



 Comments   
Comment by Doctrine Bot [ 11/Jan/15 ]

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

Comment by Doctrine Bot [ 11/Jan/15 ]

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





[DBAL-1091] [GH-755] Update MysqliStatement to support PDO::FETCH_OBJ Created: 23/Dec/14  Updated: 04/Jan/15  Resolved: 04/Jan/15

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

Type: New Feature Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: driver, fetch, mysql, mysqli, object


 Description   

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

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

Message:

Return a simple stdClass object if that fetch type is requested.



 Comments   
Comment by Doctrine Bot [ 04/Jan/15 ]

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





[DBAL-1083] [GH-749] [DBAL-1082] Fix SchemaTool does not generate SQL for MySQL unsigned float Created: 18/Dec/14  Updated: 03/Jan/15  Resolved: 03/Jan/15

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

Type: Improvement Priority: Minor
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: ddl, decimal, float, mysql, unsinged

Issue Links:
Reference
relates to DBAL-1082 SchemaTool does not generate SQL for ... Resolved

 Description   

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

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

Message:



 Comments   
Comment by Doctrine Bot [ 03/Jan/15 ]

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





[DBAL-1082] SchemaTool does not generate SQL for MySQL unsigned float Created: 18/Dec/14  Updated: 03/Jan/15  Resolved: 03/Jan/15

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

Type: Improvement Priority: Minor
Reporter: Daniel Chesterton Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: dbal, ddl, decimal, float, mysql, schematool, unsigned
Environment:

MySQL


Issue Links:
Reference
is referenced by DBAL-1083 [GH-749] [DBAL-1082] Fix SchemaTool d... Resolved

 Description   

The schema update tool does not consider the possibility that MySQL double/float fields can be unsigned.

When running the CLI tool, it recognises that the schemas differ but it doesn't add the appropriate 'UNSIGNED' SQL statement. For example when the database is SIGNED but the entity is marked as UNSIGNED, running the tool with --dump-sql will generate SQL similar to below:

ALTER TABLE tablename CHANGE field field DOUBLE PRECISION NOT NULL;

Running this has no effect on the database and subsequent calls will try to run the same SQL.

I have created a pull request in GitHub which fixes the issue.






[DBAL-1073] [GH-742] Take care about mariadb platform Created: 12/Dec/14  Updated: 24/Dec/14  Resolved: 24/Dec/14

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

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: detection, mariadb, mysql, platform, version


 Description   

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

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

Message:

Hi,
After upgrading to DBAL 2.5, I got an issue where I could not rename index while migrating because of MariaDB [versioning](http://en.wikipedia.org/wiki/MariaDB#Versioning) which outputs ```10.0.15-MariaDB-1~wheezy ``` as server version.

Because 10.x > 5.7 it loads new features from mysql 5.7 which are not available in mariadb ..



 Comments   
Comment by Doctrine Bot [ 13/Dec/14 ]

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

Comment by Doctrine Bot [ 24/Dec/14 ]

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





[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-1000] MySQL DB cannot be created from Cli, returns "QLSTATE[42000] [1049] Unknown database" Created: 14/Oct/14  Updated: 26/Oct/14

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

Type: Bug Priority: Critical
Reporter: Marcus Malka Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: Cli, mysql

Attachments: PNG File Screen Shot 2014-10-14 at 10.53.44.png    

 Description   

When trying to create a database via Symfony (tested 2.3, 2.4, 2.5) console using Doctrine, if returns

[Doctrine\DBAL\Exception\ConnectionException]
An exception occured in driver: SQLSTATE[42000] [1049] Unknown database 'livedb_ci_tco_dev'

This effectively prevents re-creating the database.

I traced the error, it seems database existence is either not checked or not functioning, and it tries to connect to the DB, resulting in an exception. In older versions the trace seemed like it checked if DB exists, and didn't try to connect.

I tested some different commit versions to assess where the bug was introduced - here is my brief list of working/non-working versions.

812dd9d (v.2.5.0-BETA3) fail
61eb1ee fail
3176f51 fail
da43b76 works
ce3a56e works
594e326 works
ba9aa63 (v.2.5.0-BETA2) works

So looking from the commit graph, to me it seems like it might have been introduced in commit 3176f51



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

Marcus Malka are you sure that you are running the correct command? If the DB is not there, I would expect an exception.

Comment by Christophe Coevoet [ 14/Oct/14 ]

Marco Pivetta This command is precisely about creating the database when it does not exist yet. I think this failure is related to the guessing of the platform version in DBAL 2.5, which will require connecting to the DB early.

Comment by Marco Pivetta [ 14/Oct/14 ]

Christophe Coevoet the screenshot shows the `drop` command being used

Comment by Marcus Malka [ 14/Oct/14 ]

I tested with multiple commands, mainly drop and create were most common.

The difference is in the error that gets produced with the different commits applied - other one gives the "The database is not there" kind of error you would expect. The other version gives an "can't do the operation because I can't connect to the database" even when you try to create it, and gives a similar "can't drop the database because I can't connect to the database" kind of error, which still says it tries to do something weird. My screenshot could've been taken from the create-command too, the error was identical.

I should have a breaking composer.json file in my version control on another machine. I'll try to add that later to facilitate debugging.

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

Christophe Coevoet you are right, that DBAL now connects early if you request the database platform but that should not be an issue as you may not specify a non-existing database name when connecting to creating a new database, anyways. And as far as I can see the CreateDatabaseDoctrineCommand even explicitly unsets the database name in the connection params here: https://github.com/doctrine/DoctrineBundle/blob/master/Command/CreateDatabaseDoctrineCommand.php#L71-L80 for a "temporary" connection.
Dropping a database still should require the database to be existent so connecting to it before dropping should be fine or am I missing something here?

Comment by Marcus Malka [ 15/Oct/14 ]

@Steve Muller - is it tries to create the connection and creates an error also when the database is not supposed to be there, for ex. in create action. Effectively breaking createDatabase command.

When I traced the code, it seemed to go in to the platform version checking, and seemed like it just didn't realize early enough that the DB isn't there (that was my guess - I don't know doctrine internals well enough to say if that's how it's planned to work or not).

Comment by Benjamin Eberlei [ 26/Oct/14 ]

This is an issue in DoctrineBundle that only appears in combination with DBAL 2.5.

The problem is Symfony apparently connects to the database in "Container::get" of the connection already. That has to be fixed.





[DBAL-936] Doctrine type not found exception thrown before checking the comment Created: 10/Jul/14  Updated: 11/Jul/14

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

Type: Bug Priority: Major
Reporter: Maxime Veber Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: mapping, mysql


 Description   

Here is the piece of code:

$type = $this->_platform->getDoctrineTypeMapping($dbType);

// In cases where not connected to a database DESCRIBE $table does not return 'Comment'
if (isset($tableColumn['comment'])) {
    $type = $this->extractDoctrineTypeFromComment($tableColumn['comment'], $type);
    $tableColumn['comment'] = $this->removeDoctrineTypeFromComment($tableColumn['comment'], $type);
}

The method `getDoctrineTypeMapping` throw an exception if the type is not found. But for example if you have an enum type (see the doctrine cookbook on the subject), the type is setted as comment.

Doctrine throw the exception before having the occasion to get the type via comment.

Here are two solutions:

  • Check for comment before throw the exception
  • Adding the enum type to the doctrine platform and mapping it to string





[DBAL-914] the pdo_mysql driver do not always trhow an error when mysql does Created: 29/May/14  Updated: 26/Jun/14  Resolved: 26/Jun/14

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

Type: Bug Priority: Major
Reporter: mikeSimonson Assignee: Marco Pivetta
Resolution: Won't Fix Votes: 0
Labels: mysql

Attachments: Text File 0001-testcase-for-a-bug-in-the-pdo_mysql-driver.patch    

 Description   

When a query get passed to mysql with the pdo_mysql driver and that the query end with a double semicolon "Create table test (test vachar(1));;". Mysql throw an error and stop the execution of the query there (aka in between the first and the second semicolon).
That problem does not exist with the mysqli driver that correctly throw an error.



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

Doesn't look like a DBAL bug to me.

As far as I know, PDO does not support multiple queries at all, while mysqli does.

Comment by mikeSimonson [ 30/May/14 ]

I suppose it does because if I insert a sql stmt, in between the two semicolon, it gets executed.

I discovered that bug because I had one statement that created 20 tables or so and that someone edited it manually adding the second semicolon by mistake.
And suddenly all that was after that double semicolon wasn't executed anymore.

To be exact, I'd discover the bug using doctrine migration.
I have made a little patch that you can use to test that case.
For the test to run you will need to adapt the credential found in the Doctrine\DBAL\Migrations\Tests\MigrationTestCase file (after applying my patch).
If you replace in that file pdo_mysql with mysqli the dirver correctly issue an error while the other does not.
I have the impression that the root issue is that when mysql is given a statment with 2 semicolons at the end, it throws an error but with an empty errno.
You can test that directly in mysql console.

Thanks

Comment by Marco Pivetta [ 06/Jun/14 ]

See https://bugs.php.net/bug.php?id=61613

Comment by Marco Pivetta [ 26/Jun/14 ]

Bug depends on a php-src bug





[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: 28/May/15

Status: Awaiting Feedback
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: 1
Labels: Cli, mysql, 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.

Comment by Pavlo Chipak [ 28/May/15 ]

Steve Müller I'm using Symfony2 and tying to do such things on MySQL (one entity manager, one user and multiple databases). I wrote my own realisation of schema:update based on code of original command. I have config parameter with list of schemas (databases) and then inside a command in the loop I reloading EM with selected database. Then, as usual, I'm getting diff of schema. At the end, a have diff (list of queries) for each database and can run it all at one. I know it's bad solution, but may be it can be done something similar and more elegant in the core? Like create white list of schemas parameter for EM. If thats can be done, there will not be a problem like:

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.

At next, if in EM config are set more then one schema we always need use schema.table for tabe naming in the Comparator and other core module, and in generated queries. As profit, it must be not hard to allow using cross-database FK.





[DBAL-803] SQLSTATE[HY093]: Invalid parameter number: parameter was not defined Created: 06/Feb/14  Updated: 06/Feb/14  Resolved: 06/Feb/14

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

Type: Bug Priority: Major
Reporter: Stefano Kowalke Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 0
Labels: mysql, querybuilder,
Environment:

OSX 10.8, PHP5.4, MySQL56


Issue Links:
Duplicate
duplicates DBAL-804 [GH-523] SQLSTATE[HY093]: Invalid par... Resolved

 Description   

This can be closed. I moved the description to http://www.doctrine-project.org/jira/browse/DBAL-804






[DBAL-802] Tablename quoting not working for ALTER TABLE Created: 06/Feb/14  Updated: 24/Apr/14  Resolved: 24/Apr/14

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

Type: Bug Priority: Major
Reporter: Dennis Birkholz Assignee: Steve Müller
Resolution: Duplicate Votes: 0
Labels: Quoting, TableDiff, mysql

Issue Links:
Duplicate
duplicates DBAL-555 Table name is not quoted despite bein... Resolved

 Description   

I use the orm:schema-tool:update to update the database schema of my model which contains a table with the name "Character".
Quoting for this table name works without the need to add backticks in foreign key definitions (references `Character`) but "ALTER Character" misses the quotes.
The reason is that the getAlterTableSQL method of the MySqlPlatform class uses the name property of the supplied TableDiff which does not contain a quoted name.
The original Table information that contained the quoting information is not available from the TableDiff.

A quick fix is to just force a name quoting with "$this->quoteIdentifier($diff->name)" in the getAlterTableSQL but this does ignore all quoting-decision-functionality of doctrine.



 Comments   
Comment by Dennis Birkholz [ 06/Feb/14 ]

Just checked on v2.4.2: the issue is still present there but the TableDiff now contains the original table information object so the fix may be a lot less hacky.

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

Dennis Birkholz This issue should have been fixed in 2.5, commit: https://github.com/doctrine/dbal/commit/75d35f5809095b37cb7085a9289eca4aa9c6df68
See here: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/MySqlPlatform.php#L600

Please check again with the current master.

Comment by Steve Müller [ 24/Apr/14 ]

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





[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-477] Just doublequote all schema names and field names in PostgreSQL sql command generation, and the same for MySQL Created: 28/Mar/13  Updated: 24/Dec/13

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

Type: Improvement Priority: Major
Reporter: jos de witte Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: mysql, postgresql
Environment:

Any PostgreSQL environment


Issue Links:
Reference
is referenced by DBAL-96 Make approach towards identifier quot... Open

 Description   

Generation of any SQL command to the database (From entities or migration versions) does not quote all the reserved keywords (For example a fieldname `right`.

Simple fix that always works: double-quote dbname, schemaname and fieldname

e.g "dbsecurity"."userschema"."users" or "tblusers"

MySQL : use the ` sign.

e.g `security`.`users` or `tblusers` (No support for schemas since I last checked some time ago)



 Comments   
Comment by Steve Müller [ 24/Jun/13 ]

If those are reserved keywords, they should be added to the "PostgreSQLKeywords" class and they will be quoted by Doctrine. As far as I can see those keywords mentioned are not present in this class. Maybe there is something missing?





[DBAL-442] Break the query building with multiple from parts Created: 10/Feb/13  Updated: 22/Apr/13  Resolved: 22/Apr/13

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

Type: Bug Priority: Major
Reporter: Denis Vasilev Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: mysql


 Description   

For example:

$queryBuilder
    ->select('DISTINCT c.id')
    ->from('Campaigns', 'c')
    ->leftJoin('c', 'CampaignOperations', 'od', 'od.campaignId = c.id AND od.operation = :operation')
    ->from('BannerGroups', 'bg')
    ->innerJoin('bg', 'BannerGroupStrategies', 'bgs', 'bgs.groupId = bg.id AND bgs.advSystem = :system')
    ->where('bg.campaignId = c.id');

Builded the query:

SELECT DISTINCT c.id FROM Campaigns s 
  LEFT JOIN CampaignOperations od ON (od.campaignId = c.id AND od.operation = :operation)
  INNER JOIN BannerGroupStrategies bgs ON (bgs.groupId = bg.id AND bgs.advSystem = :system), 
  BannerGroups bg
  WHERE (bg.campaignId = c.id)

If this query execute on mysql, we get error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'bg.id' in 'on clause'

Expected result:

SELECT DISTINCT c.id FROM Campaigns s 
  LEFT JOIN CampaignOperations od ON (od.campaignId = c.id AND od.operation = :operation),
  BannerGroups bg
  INNER JOIN BannerGroupStrategies bgs ON (bgs.groupId = bg.id AND bgs.advSystem = :system)
  WHERE (bg.campaignId = c.id)

Regression after patch https://github.com/doctrine/dbal/pull/175



 Comments   
Comment by Denis Vasilev [ 10/Feb/13 ]

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

Comment by Fabio B. Silva [ 22/Apr/13 ]

Fixed by : https://github.com/doctrine/dbal/commit/99574240f332a814ec193b6e7a88abb6a457f061





[DBAL-398] Native query does not allow mysql assignment operator := Created: 18/Dec/12  Updated: 03/Jan/13  Resolved: 22/Dec/12

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

Type: Bug Priority: Major
Reporter: David Ward Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: mysql
Environment:

Using Doctrine within Symfony 2.1.x


Attachments: Text File FixNativeAssignmentOperator.patch    

 Description   

When trying to use the mysql assignment operator in a native query one gets an exception as SqlParserUtils does not qualify the character after the : as being part of a valid parameter value.

Undefined index: in vendor/doctrine/dbal/lib/Doctrine/DBAL/SQLParserUtils.php line 156 (uncaught exception)

A simple example is
$rsm = new ResultSetMapping();
$rsm->addScalarResult('rank', 'rank');
$qry = $em->createNativeQuery("
SELECT (@rank := 1) AS rank
");
$result = $qry->getResult(Query::HYDRATE_ARRAY);

Or a more complicated example is (similar to actual use):

$rsm = new ResultSetMapping();
$rsm->addScalarResult('rank', 'rank');
$qry = $em->createNativeQuery("
SELECT rank FROM
(SELECT (@rank := @rank +1) AS rank FROM (SELECT @rank :=0) rnk2) rnk1
");
$result = $qry->getResult(Query::HYDRATE_ARRAY);

I have attached quick-fix patch, but it looks like the getPlaceholderPositions method is wanting something better overall (due to the TODO comment in it).



 Comments   
Comment by David Ward [ 18/Dec/12 ]

A pull request has been added at https://github.com/doctrine/dbal/pull/237 which also has tests added.

Comment by Bryson Armstrong [ 03/Jan/13 ]

I ran into this error and the fix caused other queries to have errors.

I fixed it by changing line 57 in vendor/doctrine/dbal/lib/Doctrine/DBAL/SQLParserUtils.php:

 
if ($statement[$i] == $match && !$inLiteral && (!$isPositional && $statement[$i+1] != '=')) {

to:

if ($statement[$i] == $match && !$inLiteral && ($isPositional || $statement[$i+1] != '=')) {




Generated at Thu May 28 10:02:13 UTC 2015 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.