[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-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-910] QueryBuilder fails when using alias in having with like expr Created: 21/May/14  Updated: 26/Jun/14

Status: Open
Project: Doctrine DBAL
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?





[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-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 Tue Jul 22 15:02:20 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.