[DBAL-1020] Postgres and using Schema tool throws cardinality errors Created: 22/Oct/14  Updated: 23/Oct/14

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

Type: Bug Priority: Critical
Reporter: Dominic Watson Assignee: Marco Pivetta
Resolution: Unresolved Votes: 0
Labels: ddl, postgresql

Attachments: PNG File 6ZeW_jJFUbOCx5uGJ2feANtAsZOr72zhDL4szJ6p5VE.png     File pg_catalog_pg_class.csv    

 Description   

Postgres: 9.3.5.0 (Postgres App for OSX) w/ PostGIS extensions
doctrine/common: 2.4.x-dev ae92d076442e27b6910dd86a1292a8867cf5cfe4
doctrine/dbal: dev-master 1c9c24a7e2295b71249ae2a719ce38861fccd551
creof/doctrine2-spatial: https://github.com/intellix/doctrine2-spatial 4023ca8fbe703043012c31d6df26b9bc7b0a972d

It seems every now and again when I come to use the schema-tool I'm getting exceptions which can only be fixed by dropping the database and recreating from scratch.

The following SQL looks to be generated here: \Doctrine\DBAL\Platforms\AbstractPlatform::getListTableForeignKeysSQL

SELECT quote_ident(r.conname) as conname, pg_catalog.pg_get_constraintdef(r.oid, true) as condef
                  FROM pg_catalog.pg_constraint r
                  WHERE r.conrelid =
                  (
                      SELECT c.oid
                      FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n
                      WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND c.relname = 'state' AND n.nspname = ANY(string_to_array((select replace(replace(setting,'"$user"',user),' ','') from pg_catalog.pg_settings where name = 'search_path'),',')) AND n.oid = c.relnamespace
                  )
                  AND r.contype = 'f'

The full stack trace is as follows:

 ---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~--
  Dropping database schema...
      ./bin/doctrine-module orm:schema-tool:drop --force --verbose
---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~---~--
Dropping database schema...


                                                                                                                                                                                                       
  [Doctrine\DBAL\Exception\DriverException]                                                                                                                                                            
  An exception occurred while executing 'SELECT quote_ident(r.conname) as conname, pg_catalog.pg_get_constraintdef(r.oid, true) as condef                                                              
                    FROM pg_catalog.pg_constraint r                                                                                                                                                    
                    WHERE r.conrelid =                                                                                                                                                                 
                    (                                                                                                                                                                                  
                        SELECT c.oid                                                                                                                                                                   
                        FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n                                                                                                                          
                        WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND c.relname = 'state' AND n.nspname = ANY(string_to_array((select replace(replace(setting,'"$user"'  
  ,user),' ','') from pg_catalog.pg_settings where name = 'search_path'),',')) AND n.oid = c.relnamespace                                                                                              
                    )                                                                                                                                                                                  
                    AND r.contype = 'f'':                                                                                                                                                              
  SQLSTATE[21000]: Cardinality violation: 7 ERROR:  more than one row returned by a subquery used as an expression                                                                                     
                                                                                                                                                                                                       


Exception trace:
 () at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractPostgreSQLDriver.php:82
 Doctrine\DBAL\Driver\AbstractPostgreSQLDriver->convertException() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:116
 Doctrine\DBAL\DBALException::driverExceptionDuringQuery() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:833
 Doctrine\DBAL\Connection->executeQuery() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:761
 Doctrine\DBAL\Connection->fetchAll() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:319
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableForeignKeys() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:284
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:268
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTables() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:1039
 Doctrine\DBAL\Schema\AbstractSchemaManager->createSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:783
 Doctrine\ORM\Tools\SchemaTool->getDropSchemaSQL() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:727
 Doctrine\ORM\Tools\SchemaTool->dropSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/DropCommand.php:100
 Doctrine\ORM\Tools\Console\Command\SchemaTool\DropCommand->executeSchemaCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/AbstractCommand.php:65
 Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand->execute() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:252
 Symfony\Component\Console\Command\Command->run() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:891
 Symfony\Component\Console\Application->doRunCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:195
 Symfony\Component\Console\Application->doRun() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:126
 Symfony\Component\Console\Application->run() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module.php:58
 include() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module:4




                                                                                                                    
  [Doctrine\DBAL\Driver\PDOException]                                                                               
  SQLSTATE[21000]: Cardinality violation: 7 ERROR:  more than one row returned by a subquery used as an expression  
                                                                                                                    


Exception trace:
 () at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:94
 Doctrine\DBAL\Driver\PDOConnection->query() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:830
 Doctrine\DBAL\Connection->executeQuery() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:761
 Doctrine\DBAL\Connection->fetchAll() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:319
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableForeignKeys() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:284
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:268
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTables() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:1039
 Doctrine\DBAL\Schema\AbstractSchemaManager->createSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:783
 Doctrine\ORM\Tools\SchemaTool->getDropSchemaSQL() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:727
 Doctrine\ORM\Tools\SchemaTool->dropSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/DropCommand.php:100
 Doctrine\ORM\Tools\Console\Command\SchemaTool\DropCommand->executeSchemaCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/AbstractCommand.php:65
 Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand->execute() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:252
 Symfony\Component\Console\Command\Command->run() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:891
 Symfony\Component\Console\Application->doRunCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:195
 Symfony\Component\Console\Application->doRun() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:126
 Symfony\Component\Console\Application->run() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module.php:58
 include() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module:4




                                                                                                                    
  [PDOException]                                                                                                    
  SQLSTATE[21000]: Cardinality violation: 7 ERROR:  more than one row returned by a subquery used as an expression  
                                                                                                                    


Exception trace:
 () at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:92
 PDO->query() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:92
 Doctrine\DBAL\Driver\PDOConnection->query() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:830
 Doctrine\DBAL\Connection->executeQuery() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:761
 Doctrine\DBAL\Connection->fetchAll() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:319
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableForeignKeys() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:284
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:268
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTables() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/dbal/lib/Doctrine/DBAL/Schema/AbstractSchemaManager.php:1039
 Doctrine\DBAL\Schema\AbstractSchemaManager->createSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:783
 Doctrine\ORM\Tools\SchemaTool->getDropSchemaSQL() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/SchemaTool.php:727
 Doctrine\ORM\Tools\SchemaTool->dropSchema() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/DropCommand.php:100
 Doctrine\ORM\Tools\Console\Command\SchemaTool\DropCommand->executeSchemaCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Console/Command/SchemaTool/AbstractCommand.php:65
 Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand->execute() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:252
 Symfony\Component\Console\Command\Command->run() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:891
 Symfony\Component\Console\Application->doRunCommand() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:195
 Symfony\Component\Console\Application->doRun() at /Users/dominicwatson/Sites/flatscanner/api/vendor/symfony/console/Symfony/Component/Console/Application.php:126
 Symfony\Component\Console\Application->run() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module.php:58
 include() at /Users/dominicwatson/Sites/flatscanner/api/bin/doctrine-module:4


orm:schema-tool:drop [--dump-sql] [-f|--force] [--full-database]


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

What are the contents of pg_catalog.pg_class ?

Comment by Dominic Watson [ 22/Oct/14 ]

Uploaded CSV of that table

Comment by Dominic Watson [ 22/Oct/14 ]

After running the subquery as suggested in IRC:

  SELECT c.oid                                                                                                                                                                   
                        FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n                                                                                                                          
                        WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND c.relname = 'state' AND n.nspname = ANY(string_to_array((select replace(replace(setting,'"$user"'  
  ,user),' ','') from pg_catalog.pg_settings where name = 'search_path'),',')) AND n.oid = c.relnamespace  

oid
-------
40152
39687

Comment by Marco Pivetta [ 22/Oct/14 ]

Can you run the query:

SELECT
    c.*
FROM
   pg_catalog.pg_class c, pg_catalog.pg_namespace n
WHERE
    n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
    AND c.relname = 'state'
    AND n.nspname = ANY(string_to_array((
        select replace(replace(setting,'"$user"', user), ' ', '')
        from pg_catalog.pg_settings
        where name = 'search_path'
    ),','))
    AND n.oid = c.relnamespace
Comment by Dominic Watson [ 22/Oct/14 ]
  relname varchar,
  relnamespace oid,
  reltype oid,
  reloftype oid,
  relowner oid,
  relam oid,
  relfilenode oid,
  reltablespace oid,
  relpages int,
  reltuples real,
  relallvisible int,
  reltoastrelid oid,
  reltoastidxid oid,
  relhasindex bool,
  relisshared bool,
  relpersistence char(1),
  relkind char(1),
  relnatts smallint,
  relchecks smallint,
  relhasoids bool,
  relhaspkey bool,
  relhasrules bool,
  relhastriggers bool,
  relhassubclass bool,
  relispopulated bool,
  relfrozenxid xid,
  relminmxid xid,
  relacl _aclitem,
  reloptions _text

state,2200,40154,0,10,0,40152,0,0,0,0,0,0,true,false,p,r,2,0,false,true,false,true,false,true,6694,1,NULL,NULL
state,39587,39689,0,10,0,39687,0,0,0,0,39694,0,true,false,p,r,15,3,false,true,false,false,false,true,6629,1,NULL,NULL
Comment by Dominic Watson [ 22/Oct/14 ]

My ZF2 onBootstrap as well, in case it changes anything:

Unable to find source-code formatter for language: php. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml
<?php
namespace Flatscanner;

use Doctrine\ORM\Mapping\UnderscoreNamingStrategy;
use ZF\Apigility\Provider\ApigilityProviderInterface;
use Zend\Uri\UriFactory;
use Doctrine\DBAL\Types\Type;

class Module implements ApigilityProviderInterface
{
    public function getConfig()
    {
        return include __DIR__ . '/../../config/module.config.php';
    }

    public function onBootstrap($e)
    {
        Type::addType('geometry', 'CrEOF\Spatial\DBAL\Types\GeometryType');
        Type::addType('point', 'CrEOF\Spatial\DBAL\Types\Geometry\PointType');
        UriFactory::registerScheme('chrome-extension', 'Zend\Uri\Uri');

        // Set naming strategy
        $em = $e->getTarget()->getServiceManager()->get('doctrine.entitymanager.orm_default');
        $em->getConnection()->getDatabasePlatform()->registerDoctrineTypeMapping("_text", "text"); // assuming it is a text LOB
        $em->getConfiguration()->setNamingStrategy(new UnderscoreNamingStrategy(CASE_LOWER));
    }

    public function getAutoloaderConfig()
    {
        return array(
            'ZF\Apigility\Autoloader' => array(
                'namespaces' => array(
                    __NAMESPACE__ => __DIR__,
                ),
            ),
        );
    }
}
Comment by Steve Müller [ 23/Oct/14 ]

Most probably also affects 2.4 as the codebase has not changed at the critical places. Possibly 2.3 is also affected by this. Could need a check.





[DBAL-95] Interbase/Firebird support Created: 26/Feb/11  Updated: 13/Nov/14

Status: Awaiting Feedback
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 5
Labels: None


 Description   

Implemented support for Interbase/Firebird dialects






[DBAL-999] Get a Sql Server error on Order By - Symfony2 Created: 08/Oct/14  Updated: 31/Dec/14

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

Type: Bug Priority: Major
Reporter: Maël SOURISSEAU Assignee: Marco Pivetta
Resolution: Unresolved Votes: 0
Labels: orderBy, query, sqlserver


 Description   

Using Symfony with Sql Server and from what I've read, it seems that the connection to the database is not stable.

As soon as I use the orderBy method I get an error :

Here's an example :

Unable to find source-code formatter for language: php. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml
  $qStores =
        $this->getManager()
             ->createQueryBuilder()
             ->select('rpdv')
             ->from('MainBundle:PointDeVenteReference', 'rpdv')
             ->andWhere( 'rpdv.partenaireClient = :id_partner ' )
                 ->setParameter( 'id_partner', $this->getUser()->getPartenaire()->getIdPartenaire() )
             ->orderBy( 'rpdv.idPointDeVenteReference' , 'DESC' )
             ->setFirstResult( 0 )
             ->setMaxResults( 30 );

And the error :

An exception has been thrown during the rendering of a template ("An exception occurred while executing
'SELECT DISTINCT TOP 30 id_point_de_vente_reference0
FROM ( SELECT p0_.id_point_de_vente_reference AS id_point_de_vente_reference0,
p0_.reference AS reference1,
p0_.date_derniere_modification AS date_derniere_modification2,
p0_.blocage AS blocage3
FROM point_de_vente_reference p0_
WHERE p0_.id_partenaire_client = ?
ORDER BY p0_.id_point_de_vente_reference DESC ) dctrn_result
ORDER BY id_point_de_vente_reference0 DESC'
with params [2829]:SQLSTATE[42000]:
[Microsoft][SQL Server Native Client 11.0][SQL Server]
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions,
unless TOP, OFFSET or FOR XML is also specified.") in MainBundle:Default:store/list.html.twig at line 79.
I tried to change the class SQLServerPlatform with corrections found on the net, without success.

Do you have any idea?

Thx !



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

Which version of DBAL are you using? A lot of fixes have been applied to SQL Server's LIMIT/OFFSET query rewriting in DBAL during the last months.

Comment by Maël SOURISSEAU [ 08/Oct/14 ]

2.4 for DBAL.

Under my request, I have :

$stores = new Paginator( $qStores, TRUE );

In passing the second parameter to FALSE, I have no error.

Comment by Marco Pivetta [ 19/Oct/14 ]

I'd suggest checking ORM+DBAL latest to see if the issue still exists, as those component have suffered from radical changes in the last few months.

Comment by Maël SOURISSEAU [ 29/Dec/14 ]

I currently still have the anomaly with the following configuration :

"doctrine/doctrine-bundle": "1.3.*@dev"
"doctrine/dbal": "~2.5"
"doctrine/orm": "~2.4"

Comment by Maël SOURISSEAU [ 29/Dec/14 ]

The problem apparently comes from the class SQLServerPlatform (doModifyLimitQuery)

Comment by Marco Pivetta [ 29/Dec/14 ]

Maël SOURISSEAU that kind of information is insufficient: please pick exact versions from your composer.lock instead.

Comment by Maël SOURISSEAU [ 30/Dec/14 ]
Unable to find source-code formatter for language: json. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml
...
{
    "name": "doctrine/dbal",
    "version": "v2.5.0",
    "source": {
        "type": "git",
        "url": "https://github.com/doctrine/dbal.git",
        "reference": "71140662c0a954602e81271667b6e03d9f53ea34"
    },
    "dist": {
        "type": "zip",
        "url": "https://api.github.com/repos/doctrine/dbal/zipball/71140662c0a954602e81271667b6e03d9f53ea34",
        "reference": "71140662c0a954602e81271667b6e03d9f53ea34",
        "shasum": ""
    },
    "require": {
        "doctrine/common": ">=2.4,<2.6-dev",
        "php": ">=5.3.2"
    },
    "require-dev": {
        "phpunit/phpunit": "4.*",
        "symfony/console": "2.*"
    },
    "suggest": {
        "symfony/console": "For helpful console commands such as SQL execution and import of files."
    },
    "bin": [
        "bin/doctrine-dbal"
    ],
    "type": "library",
    "extra": {
        "branch-alias": {
            "dev-master": "2.5.x-dev"
        }
    },
    "autoload": {
        "psr-0": {
            "Doctrine\\DBAL\\": "lib/"
        }
    },
    "notification-url": "https://packagist.org/downloads/",
    "license": [
        "MIT"
    ],
    "authors": [
        {
            "name": "Roman Borschel",
            "email": "roman@code-factory.org"
        },
        {
            "name": "Benjamin Eberlei",
            "email": "kontakt@beberlei.de"
        },
        {
            "name": "Guilherme Blanco",
            "email": "guilhermeblanco@gmail.com"
        },
        {
            "name": "Jonathan Wage",
            "email": "jonwage@gmail.com"
        }
    ],
    "description": "Database Abstraction Layer",
    "homepage": "http://www.doctrine-project.org",
    "keywords": [
        "database",
        "dbal",
        "persistence",
        "queryobject"
    ],
    "time": "2014-12-04 21:57:15"
},
{
    "name": "doctrine/doctrine-bundle",
    "version": "dev-master",
    "source": {
        "type": "git",
        "url": "https://github.com/doctrine/DoctrineBundle.git",
        "reference": "3beb3a780485ab01f86941f4892cd23ef8c39c6b"
    },
    "dist": {
        "type": "zip",
        "url": "https://api.github.com/repos/doctrine/DoctrineBundle/zipball/3beb3a780485ab01f86941f4892cd23ef8c39c6b",
        "reference": "3beb3a780485ab01f86941f4892cd23ef8c39c6b",
        "shasum": ""
    },
    "require": {
        "doctrine/dbal": "~2.3",
        "doctrine/doctrine-cache-bundle": "~1.0",
        "jdorn/sql-formatter": "~1.1",
        "php": ">=5.3.2",
        "symfony/doctrine-bridge": "~2.2",
        "symfony/framework-bundle": "~2.2"
    },
    "require-dev": {
        "doctrine/orm": "~2.3",
        "phpunit/php-code-coverage": "~1.2",
        "phpunit/phpunit": "~3.7",
        "phpunit/phpunit-mock-objects": "~1.2",
        "satooshi/php-coveralls": "~0.6.1",
        "symfony/validator": "~2.2",
        "symfony/yaml": "~2.2",
        "twig/twig": "~1"
    },
    "suggest": {
        "doctrine/orm": "The Doctrine ORM integration is optional in the bundle.",
        "symfony/web-profiler-bundle": "to use the data collector"
    },
    "type": "symfony-bundle",
    "extra": {
        "branch-alias": {
            "dev-master": "1.3.x-dev"
        }
    },
    "autoload": {
        "psr-4": {
            "Doctrine\\Bundle\\DoctrineBundle\\": ""
        }
    },
    "notification-url": "https://packagist.org/downloads/",
    "license": [
        "MIT"
    ],
    "authors": [
        {
            "name": "Symfony Community",
            "homepage": "http://symfony.com/contributors"
        },
        {
            "name": "Benjamin Eberlei",
            "email": "kontakt@beberlei.de"
        },
        {
            "name": "Doctrine Project",
            "homepage": "http://www.doctrine-project.org/"
        },
        {
            "name": "Fabien Potencier",
            "email": "fabien@symfony.com"
        }
    ],
    "description": "Symfony DoctrineBundle",
    "homepage": "http://www.doctrine-project.org",
    "keywords": [
        "database",
        "dbal",
        "orm",
        "persistence"
    ],
    "time": "2014-11-28 08:32:03"
},
{
    "name": "doctrine/orm",
    "version": "v2.4.7",
    "source": {
        "type": "git",
        "url": "https://github.com/doctrine/doctrine2.git",
        "reference": "2bc4ff3cab2ae297bcd05f2e619d42e6a7ca9e68"
    },
    "dist": {
        "type": "zip",
        "url": "https://api.github.com/repos/doctrine/doctrine2/zipball/2bc4ff3cab2ae297bcd05f2e619d42e6a7ca9e68",
        "reference": "2bc4ff3cab2ae297bcd05f2e619d42e6a7ca9e68",
        "shasum": ""
    },
    "require": {
        "doctrine/collections": "~1.1",
        "doctrine/dbal": "~2.4",
        "ext-pdo": "*",
        "php": ">=5.3.2",
        "symfony/console": "~2.0"
    },
    "require-dev": {
        "satooshi/php-coveralls": "dev-master",
        "symfony/yaml": "~2.1"
    },
    "suggest": {
        "symfony/yaml": "If you want to use YAML Metadata Mapping Driver"
    },
    "bin": [
        "bin/doctrine",
        "bin/doctrine.php"
    ],
    "type": "library",
    "extra": {
        "branch-alias": {
            "dev-master": "2.4.x-dev"
        }
    },
    "autoload": {
        "psr-0": {
            "Doctrine\\ORM\\": "lib/"
        }
    },
    "notification-url": "https://packagist.org/downloads/",
    "license": [
        "MIT"
    ],
    "authors": [
        {
            "name": "Roman Borschel",
            "email": "roman@code-factory.org"
        },
        {
            "name": "Benjamin Eberlei",
            "email": "kontakt@beberlei.de"
        },
        {
            "name": "Guilherme Blanco",
            "email": "guilhermeblanco@gmail.com"
        },
        {
            "name": "Jonathan Wage",
            "email": "jonwage@gmail.com"
        }
    ],
    "description": "Object-Relational-Mapper for PHP",
    "homepage": "http://www.doctrine-project.org",
    "keywords": [
        "database",
        "orm"
    ],
    "time": "2014-12-16 13:45:01"
},
...
Comment by Steve Müller [ 31/Dec/14 ]

I think this issue is being dealt with in this PR: https://github.com/doctrine/doctrine2/pull/1220
It's basically an issue with the ORM paginator because it uses the ORDER BY clause in the derived table which is invalid in SQL Server.





[DBAL-292] Multiple use of named parameter doesn't work Created: 12/Jun/12  Updated: 28/Dec/13

Status: Awaiting Feedback
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.2.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Jürgen Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

IIS 7.5, MS-SQL-Server 2005, PHP 5.4.0



 Description   

In the example in the documentation http://doctrine-dbal.readthedocs.org/en/2.0.x/reference/data-retrieval-and-manipulation.html#dynamic-parameters-and-prepared-statements there is the following example:

$sql = "SELECT * FROM users WHERE name = :name OR username = :name";
$stmt = $conn->prepare($sql);
$stmt->bindValue("name", $name);
$stmt->execute();

When I try this example using pdo_sqlsrv I get the following error:
PDOException: SQLSTATE[07002]: [Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error

When I use instead the parameters name1 and name2 the query works as expected.



 Comments   
Comment by Alexander [ 07/Jul/12 ]

Are you sure you were using the 2.2.2 version of the ORM? Can you try to reproduce this with the latest master?

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

Jürgen ping. I cannot reproduce this error, either. Can you please try to reproduce this with the latest master branch? Otherwise I will close this ticket.





[DBAL-1107] Doctrine Migrations diff gets different table name for up and down Created: 06/Jan/15  Updated: 12/Jan/15

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

Type: Bug Priority: Minor
Reporter: Krzysztof Hasiński Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

I've changed one field in my model from not null to default null, generated a migration using diff and got:

class Version20150105175136 extends AbstractMigration
{
    public function up(Schema $schema)
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');

        $this->addSql('ALTER TABLE badge ALTER company_id DROP NOT NULL');
    }

    public function down(Schema $schema)
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'postgresql', 'Migration can only be executed safely on \'postgresql\'.');

        $this->addSql('ALTER TABLE Badge ALTER company_id SET NOT NULL');
    }
}

Please note the name Badge and badge in up and down migrations. Is this a bug?

I've got response from Doctrine Migrations project:

"This should be reported in the issue tracker of the DBAL project, because the Migrations project is not responsible for computing the schema changes."

Link to this issue on github: https://github.com/doctrine/migrations/issues/197



 Comments   
Comment by Steve Müller [ 07/Jan/15 ]

Can you please provide the ORM mapping information before and after the migrations:diff command?
Did you maybe remove or add explicit quotes from/to your table name mapping? Really hard to evaluate without further information...

Comment by Krzysztof Hasiński [ 07/Jan/15 ]

Sure thing:

Entity (imports + header):

use Doctrine\ORM\Mapping as ORM;
use JsonSerializable;
use Iphp\FileStoreBundle\Mapping\Annotation as FileStore;
use Symfony\Component\Validator\Constraints as Assert;
/**
 * Badge
 * @FileStore\Uploadable
 * @ORM\Table()
 * @ORM\Entity
 */
class Badge implements JsonSerializable

Field before change:

    /**
     * @ORM\ManyToOne(targetEntity="Company")
     * @ORM\JoinColumn(nullable=false)
     */
    private $company;

Field after change:

    /**
     * @ORM\ManyToOne(targetEntity="Company")
     * @ORM\JoinColumn(nullable=true)
     */
    private $company;

Comment by Steve Müller [ 09/Jan/15 ]

How is your table named in the database? Is it "Badge" or "badge"? I assume that your table was created with the name "Badge" (with a capital letter). If this is the case, did you have your table name explicitly quoted in the mapping some time before like `Badge` or "Badge" (including backticks/quotes)?

Comment by Krzysztof Hasiński [ 09/Jan/15 ]

Created using migrations:diff, relevant line (note the capital letter):

        $this->addSql("CREATE TABLE Badge (id INT NOT NULL, user_id INT DEFAULT NULL, team_id VARCHAR(63) NOT NULL, name VARCHAR(255) NOT NULL, image VARCHAR(512) NOT NULL, condition TEXT NOT NULL, PRIMARY KEY(id))");

In all subsequent migrations any ALTER TABLE (generated) is using this mixed case for up and down.

Status in db now (from psql):

khasinski=> \d badge
                          Table "public.badge"
   Column    |          Type           |            Modifiers            
-------------+-------------------------+---------------------------------
 id          | integer                 | not null
 user_id     | integer                 | 
 company_id  | character varying(63)   | not null
 name        | character varying(255)  | not null
 image       | text                    | not null
 condition   | text                    | 
 description | character varying(1024) | default NULL::character varying
Indexes:
    "badge_pkey" PRIMARY KEY, btree (id)
    "idx_3f316719296cd8ae" btree (company_id)
    "idx_3f316719a76ed395" btree (user_id)
Foreign-key constraints:
    "fk_3f316719296cd8ae" FOREIGN KEY (company_id) REFERENCES company(id)
    "fk_3f316719a76ed395" FOREIGN KEY (user_id) REFERENCES guser(id)
Referenced by:
    TABLE "userbadge" CONSTRAINT "fk_9a64d968f7a2c2fc" FOREIGN KEY (badge_id) REFERENCES badge(id)
Comment by Marco Pivetta [ 09/Jan/15 ]

Can you please tell us the exact versions (see composer.lock) of the DBAL, ORM and migrations installed?

Comment by Krzysztof Hasiński [ 09/Jan/15 ]

Sure, always happy to help

DBAL:

            "name": "doctrine/dbal",
            "version": "v2.5.0",
            "source": {
                "type": "git",
                "url": "https://github.com/doctrine/dbal.git",
                "reference": "71140662c0a954602e81271667b6e03d9f53ea34"
            },
            "dist": {
                "type": "zip",
                "url": "https://api.github.com/repos/doctrine/dbal/zipball/71140662c0a954602e81271667b6e03d9f53ea34",
                "reference": "71140662c0a954602e81271667b6e03d9f53ea34",
                "shasum": ""
            },

ORM:

            "name": "doctrine/orm",
            "version": "v2.4.7",
            "source": {
                "type": "git",
                "url": "https://github.com/doctrine/doctrine2.git",
                "reference": "2bc4ff3cab2ae297bcd05f2e619d42e6a7ca9e68"
            },
            "dist": {
                "type": "zip",
                "url": "https://api.github.com/repos/doctrine/doctrine2/zipball/2bc4ff3cab2ae297bcd05f2e619d42e6a7ca9e68",
                "reference": "2bc4ff3cab2ae297bcd05f2e619d42e6a7ca9e68",
                "shasum": ""
            },

Migrations:

            "name": "doctrine/doctrine-migrations-bundle",
            "version": "dev-master",
            "target-dir": "Doctrine/Bundle/MigrationsBundle",
            "source": {
                "type": "git",
                "url": "https://github.com/doctrine/DoctrineMigrationsBundle.git",
                "reference": "81575a4316951125ce408c70f30547c77d98f78a"
            },
            "dist": {
                "type": "zip",
                "url": "https://api.github.com/repos/doctrine/DoctrineMigrationsBundle/zipball/81575a4316951125ce408c70f30547c77d98f78a",
                "reference": "81575a4316951125ce408c70f30547c77d98f78a",
                "shasum": ""
            },
Comment by Steve Müller [ 09/Jan/15 ]

I think I get the problem here. Btw do the migrations actually fail? IMO this is not really an "issue" as casing of identifiers is ignored by PostgreSQL unless you explicitly quote identifiers. So:

ALTER TABLE badge ALTER company_id DROP NOT NULL;
ALTER TABLE Badge ALTER company_id DROP NOT NULL;
ALTER TABLE BADGE ALTER company_id DROP NOT NULL;

all do the same thing because PostgreSQL internally lowercases identifiers if they are not quoted.
Please see: http://www.alberton.info/dbms_identifiers_and_case_sensitivity.html#.VLAQal0z1C0

Comment by Steve Müller [ 09/Jan/15 ]

BTW if you find it annoying that migrations uses "Badge" instead of "badge" you'll just have to use another naming strategy.
See: http://doctrine-orm.readthedocs.org/en/latest/reference/namingstrategy.html

Comment by Krzysztof Hasiński [ 09/Jan/15 ]

I am well aware, that PostgreSQL use lowercase when it comes to table names without a quote, that's why I'm not considering a major bug, just some inconsistency, that I was curious about. It seems like up and down migrations should read the data from a single source of truth.

I might be mistaken, but it seems like migrations are important enough to care about this kind of minor details.

Comment by Steve Müller [ 12/Jan/15 ]

The DBAL schema manager that introspects your database does not know about your schema mappings you defned in ORM so it won't know that you created the table with an uppercase first letter. If you want consistency, please use another naming strategy in ORM or quote your table identifier explicitly in your mapping.
Doctrine will only quote reserved keyword identifiers and does not quote all identifiers automatically. There have been several discussions about this to why auto-quoting brings more problems than it solves.

Comment by Krzysztof Hasiński [ 12/Jan/15 ]

As I said - I am aware of that and it's probably ok

So my understanding is that it creates "up" migrations from ORM (which knows about the capital letter) and "down" using DBAL schema manager, is that correct?

Comment by Steve Müller [ 12/Jan/15 ]

I think you mean the other way around. See here: https://github.com/doctrine/migrations/blob/master/lib/Doctrine/DBAL/Migrations/Tools/Console/Command/DiffCommand.php#L101-L102
For "up" migrations the ORM schema mapping is compared against the database schema mapping (base). For "down" migrations it's the other way around.





Generated at Sat Mar 28 18:58:07 UTC 2015 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.