[DBAL-1058] It seems that MSSQL syntax was changed Created: 05/Dec/14  Updated: 29/Jan/15  Resolved: 12/Jan/15

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.4, 2.5
Fix Version/s: 2.4.4, 2.6, 2.5.1

Type: Bug Priority: Blocker
Reporter: man4red Assignee: Marco Pivetta
Resolution: Fixed Votes: 0
Labels: dbal, sqlserver

Issue Links:
Reference
is referenced by DBAL-1060 [GH-736] [DBAL-1058] Fix database and... Resolved
is referenced by DBAL-1061 [GH-737] [DBAL-1058] [2.4] Fix databa... Resolved

 Description   

I'm using dblib, MSSQL (2012).
So, problem is here:

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

Doctrine\DBAL\Driver\PDOException: SQLSTATE[HY000]: General error: 20018 Invalid object name 'SYS.SCHEMAS'. [20018] (severity 16) [SELECT name FROM SYS.SCHEMAS WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')] in /var/www/domains/internal.dc.hayas.ru/data/partners.zf2/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php on line 106

So it seems, that problems is here:

Doctrine\DBAL\Platforms\SQLServerPlatform.php
At Line 1036

    public function getListNamespacesSQL()
    {
        return "SELECT name FROM SYS.SCHEMAS WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')";
    }

SQL Server >= 2005 uses sys.schemas (lowercase)

Maybe need to add to SQLServer2005Platform.php

SELECT name FROM sys.schemas ...

and also at line 1028 SQLServerPlatform.php

    public function getListDatabasesSQL()
    {
        return 'SELECT * FROM SYS.DATABASES';
    }

add to SQLServer2005Platform.php

    public function getListDatabasesSQL()
    {
        return 'SELECT * FROM sys.databases';
    }


 Comments   
Comment by Steve Müller [ 05/Dec/14 ]

man4red thanks for reporting. I'll have a look at it this evening. Weird that the functional tests pass though in my setup :S

Comment by Marco Pivetta [ 05/Dec/14 ]

Steve Müller please note that he is using dblib, which (afaik) we do not officially support.

Comment by man4red [ 05/Dec/14 ]

I've checked by direct query to SQL via SQL Management Studio.
Got multiple servers with a diffirent versions.

Here some test

QUERY:

 SELECT name FROM SYS.SCHEMAS WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys') 

9.0.5069 (SQL Server 2005 Service Pack 4) PASS
10.50.4000.0 (2008 R2 SP2) FAIL
11.0.5058 (SQL Server 2012) FAIL

QUERY:

SELECT name FROM sys.schemas WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')

9.0.5069 (SQL Server 2005 Service Pack 4) PASS
10.50.4000.0 (2008 R2 SP2) PASS
11.0.5058 (SQL Server 2012) PASS

I've tested on 5 servers 11.0.5058 (SQL Server 2012).
QUERY:

SELECT name FROM SYS.SCHEMAS WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')

Failed on each of them

Other tests:

QUERY:

SELECT * FROM SYS.DATABASES

9.0.5069 (SQL Server 2005 Service Pack 4) PASS
10.50.4000.0 (2008 R2 SP2) PASS
11.0.5058 (SQL Server 2012) PASS

QUERY:

SELECT * FROM sys.databases

9.0.5069 (SQL Server 2005 Service Pack 4) PASS
10.50.4000.0 (2008 R2 SP2) PASS
11.0.5058 (SQL Server 2012) PASS

by the way - is it neccessary to query * from SYS.DATABASES ?

Doctrine\DBAL\Platforms\SQLServerPlatform.php

Line 1030

    public function getListDatabasesSQL()
    {
        return 'SELECT * FROM SYS.DATABASES';
    }

Maybe need to query only names? (name field)
Just asking

Comment by man4red [ 05/Dec/14 ]

According to tests I've added next code to SQLServer2008Platform.php

    /**
     * {@inheritDoc}
     */
    public function getListNamespacesSQL()
    {
        return "SELECT name FROM sys.schemas WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')";
    }

And modified my ZF2 application doctrine config config/autoload/doctrine.local.php (platform added):

return array(
    'doctrine' => array(
        'connection' => array(
            'orm_default' => array(
                'driverClass' => 'class to work with dblib',
                'params' => array(
                    'host' => 'hostname',
                    'port' => 1433,
                    'user' => 'user',
                    'password' => 'pass',
                    'dbname' => 'database',
                    'platform' => new Doctrine\DBAL\Platforms\SQLServer2012Platform()
                )
            )
        )
    )
);

Now I've got no issues with MSSQL 2012
I hope my fix was correct

Comment by Steve Müller [ 05/Dec/14 ]

Patch provided: https://github.com/doctrine/dbal/pull/736

Comment by Doctrine Bot [ 05/Dec/14 ]

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

Comment by man4red [ 05/Dec/14 ]

Dear friends,

I'm new here, and I don't know how all this works here, but can you help me?
As always when one bug fixed - another two produced

Now I've got another problem.
ZendDeveloperTool throws Exception

Uncaught exception 'PDOException' with message 'You cannot serialize or unserialize PDO instances'

of course because of my

    'platform' => new Doctrine\DBAL\Platforms\SQLServer2012Platform()

ok... my mistake

let's fix it in ZF2 way

    'platform' => 'Doctrine\DBAL\Platforms\SQLServer2012Platform'

Now we got another exception:

Doctrine\DBAL\DBALException: Invalid 'platform' option specified, need to give an instance of \Doctrine\DBAL\Platforms\AbstractPlatform.

let's look to doctrine\dbal\lib\Doctrine\DBAL\Connection.php Line: 387

    private function detectDatabasePlatform()
    {
        ...
        } elseif ($this->_params['platform'] instanceof Platforms\AbstractPlatform) {
            $this->platform = $this->_params['platform'];
        } else {
            throw DBALException::invalidPlatformSpecified();
        }
        ...
    }

So my question is

Can we implemet a feature and change this

    private function detectDatabasePlatform()
    {
        if ( ! isset($this->_params['platform'])) {
            $version = $this->getDatabasePlatformVersion();

            if (null !== $version) {
                $this->platform = $this->_driver->createDatabasePlatformForVersion($version);
            } else {
                $this->platform = $this->_driver->getDatabasePlatform();
            }
        } elseif ($this->_params['platform'] instanceof Platforms\AbstractPlatform) {
            $this->platform = $this->_params['platform'];
        } else {
            throw DBALException::invalidPlatformSpecified();
        }

        $this->platform->setEventManager($this->_eventManager);
    }

to this (or similar)

    private function detectDatabasePlatform()
    {
        if (! isset($this->_params['platform'])) {
            $version = $this->getDatabasePlatformVersion();

            if (null !== $version) {
                $this->platform = $this->_driver->createDatabasePlatformForVersion($version);
            } else {
                $this->platform = $this->_driver->getDatabasePlatform();
            }
        } elseif ($this->_params['platform'] instanceof Platforms\AbstractPlatform) {
            $this->platform = $this->_params['platform'];
        } elseif (is_subclass_of($this->_params['platform'], 'Doctrine\DBAL\Platforms\AbstractPlatform')) {
            $this->platform = new $this->_params['platform']();
        } else {
            throw DBALException::invalidPlatformSpecified();
        }

        $this->platform->setEventManager($this->_eventManager);
    }

or this problem is only mine and I need to fix it by my self and to write some forks/mods etc?

Thx for your help anyway

Comment by Marco Pivetta [ 05/Dec/14 ]

man4red that seems to be related with DBAL-1057 - I'll mark this issue as resolved.

Comment by man4red [ 05/Dec/14 ]

Marco Pivetta, thx! Is there planned some big reworking of this section, am I right?
Am I need to post my last comment to DBAL-1057 thread?

Comment by Marco Pivetta [ 05/Dec/14 ]

man4red this section needs some work for 2.5.1, yes. As for posting to DBAL-1057, please do, but only the bits that may be relevant and that you feel that add up to the discussion without cluttering it.

Comment by Doctrine Bot [ 12/Jan/15 ]

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

Comment by Marco Pivetta [ 12/Jan/15 ]

Fixed in DBAL-1060

Comment by Doctrine Bot [ 23/Jan/15 ]

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

Comment by Doctrine Bot [ 29/Jan/15 ]

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





[DBAL-522] BC break : executeQuery with an array containing null value(s). Created: 20/May/13  Updated: 15/Sep/14  Resolved: 21/May/13

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.3.4
Fix Version/s: 2.4, 2.3.5

Type: Bug Priority: Blocker
Reporter: lemeunier Assignee: Marco Pivetta
Resolution: Fixed Votes: 0
Labels: dbal
Environment:

Mac OSX 10.8.3, Mysql 5.5.28, PHP5.4



 Description   

Hello, i have got an error with doctrine 2.3.4 when i try to run the following code :

 
    $conn->executeQuery(
        'INSERT INTO FOO (foo, bar) values (:foo, :bar)', 
         array('foo' => 1, 'bar' => null)
     );

Error : Value for :bar not found in params array. Params array key should be "bar"

This code worked with doctrine 2.3.3.

I think the error comes from the function 'extractParam' in SQLParserUtils.php (DBAL)

line 215 : if (isset($paramsOrTypes[$paramName]))

The key exists even if the value is null.
So it should be:

  if (array_key_exists($paramName, $paramsOrTypes)) 

I am not enough confident to try a PR.
Thanks in advance!



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

I suggested a hotfix at https://github.com/doctrine/dbal/pull/322

Comment by lemeunier [ 21/May/13 ]

Thanks for the hotfix.





[DBAL-36] Database selection with DSN Created: 21/Jul/10  Updated: 08/Sep/10  Resolved: 08/Sep/10

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.0.0-BETA4
Fix Version/s: None

Type: Bug Priority: Blocker
Reporter: Bostjan Oblak Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: None
Environment:

Windows, Sql Server 2008, Sql server drivers for PHP v2



 Description   

When construstring DSN in Doctrine\DBAL\Driver\PDOSqlsrv\Driver it should add selection of database in DSN.

So for example DSN will look like:

$dsn ="sqlsrv:Server=(local) ; Database = AdventureWorks "; 

As writen in chm manual which come with Sql server drivers for PHP v2



 Comments   
Comment by Bostjan Oblak [ 08/Sep/10 ]

I changed this Issue to Bug and give it priority of blocker, since with final relase of Sql Server Driver 2 for PHP it's not working correctly.

Solution can be found on my blog: http://bostjan.muha.cc/2010/08/doctrine-2-mssql/

Comment by Benjamin Eberlei [ 08/Sep/10 ]

This was fixed in Beta4, but will probably be changed again for the next testrelease because of DBAL-49.





[DBAL-35] PDO_Sqlsrv bug in _constructPdoDsn() Created: 21/Jul/10  Updated: 31/Aug/10  Resolved: 31/Aug/10

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.0.0-BETA2
Fix Version/s: 2.0.0-BETA4

Type: Bug Priority: Blocker
Reporter: Bostjan Oblak Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 1
Labels: None
Environment:

Windows 7, Zend Server CE, Sql Server driver for PHP v2



 Description   

When connection to mssql server with pdo_sqlsrv driver you get:

 
[PDOException]
SQLSTATE[IMSSP]: The DSN string ended unexpectedly.

Sample $connectionOptions:

$connectionOptions = array(
    'dbname' => 'AdventureWorks',
    'user' => 'sa',
    'password' => 'sa_password',
    'host' => 'local',
    'driver' => 'pdo_sqlsrv'
);

Error is in Doctrine\DBAL\Driver\PDOSqlsrv\Driver in function _constructPdoDsn(array $params).
On line 53 dsn starts with

$dsn = 'sqlsrv:(';

instead of

$dsn = 'sqlsrv:server=(';

as written in offical Microsoft help which come with SQL Server Driver For PHP v2.



 Comments   
Comment by Benjamin Eberlei [ 21/Jul/10 ]

@Juozas can you evaluate this and possibly apply a fix?

Comment by Benjamin Eberlei [ 31/Aug/10 ]

Fixed





[DBAL-244] Shema Tool is not working after DBAL-177 for postgresql (mysql working like before) Created: 25/Mar/12  Updated: 17/Apr/14  Resolved: 05/May/12

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.2, 2.2.1
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Margus Sipria Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

Ubuntu 10.10, Zend Server 5.5.0 with PHP 5.3.8



 Description   

After trying to upgrade 2.2.0 i found that schema tool wasn't working, so I switched back to 2.1.6, same thing with 2.2.1 and no bug report, so this is wats going on.

./doctrine orm:schema-tool:update --dump-sql # this will show full create table for schema even if tables are all ready there.

After git bisectin Doctrine ORM project i found that commit ea5108ea0f35fc0f7ed3a740995a590926045c6e wast to blame, but that was only submodule update so made bisect for Doctrine DBAL:

537de7ea6a34edbcc40bc6ca92e0a3f816b59330 .. 4410e4cec20b0f1f209578320e5b7d111e90c2a0 founding that 1ae87bf3e3ba93cb579a2a092b06b5a09b316542 was the problem.

[margus@laptop doctrine-dbal ((4410e4c...))]$ git reset --hard 1ae87bf3e3ba93cb579a2a092b06b5a09b316542
HEAD is now at 1ae87bf DBAL-177 - Make sure schema.table syntax is supported in Assets for quoted assets
[margus@laptop doctrine-dbal ((1ae87bf...))]$ git submodule update --recursive
Submodule path 'lib/vendor/doctrine-common': checked out 'd6e4c8b22af9800db4fd9d679ce98538da028168'

    1. shema tool printing full schema

[margus@laptop doctrine-dbal ((1ae87bf...))]$ git reset --hard HEAD^1
HEAD is now at bb84496 DBAL-144 - Dont throw exception when no primary key exists
[margus@laptop doctrine-dbal ((bb84496...))]$ git submodule update --recursive

    1. works fine

[margus@laptop build (master)]$ ./doctrine orm:schema-tool:update --dump-sql
Nothing to update - your database is already in sync with the current entity metadata.

with commit 1ae87bf3e3ba93cb579a2a092b06b5a09b316542 schema starts with 3 NULL lines, and then schema, with 2.2.0, extra "NULL" lines aren't there anymore.

Using MySQL there isn't any problem, but with PostgreSQL (i have 8.4.11) this issue appears.



 Comments   
Comment by Benjamin Eberlei [ 30/Mar/12 ]

Increase priority, will be fixed this weekend and in the next bugifx release

Comment by Benjamin Eberlei [ 30/Mar/12 ]

Are you using Postgresql Schema? Can you provide some information about your database tables? I need some more information to try reproducing this.

Comment by Nikolai Spassoff [ 03/May/12 ]

I'm experiencing the same issue.
I looked at the mentioned commit and found out that the SQL query in getSchemaNames() does not return any namespaces.
After some research I came with the following query to list all non-system namespaces in Postgres:

SELECT nspname as schema_name FROM pg_namespace WHERE nspname !~ '^pg_.*' and nspname != 'information_schema'

This fixed the issue for me and the schema-tool works again.

Comment by Benjamin Eberlei [ 05/May/12 ]

Fixed, but couldn't verify as the previous statement worked for me.





[DBAL-129] Doctrine\ORM\Mapping\ClassMetadataInfo does not properly handle identifier quoting Created: 10/Jun/11  Updated: 17/Jun/11  Resolved: 15/Jun/11

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.4
Fix Version/s: 2.0.6

Type: Bug Priority: Critical
Reporter: Patrick Schwisow Assignee: Juozas Kaziukenas
Resolution: Fixed Votes: 1
Labels: None


 Description   

Several methods in Doctrine\ORM\Mapping\ClassMetadataInfo assume that table names and field names may be quoted. In all places, logic assumes that the quote character will always be ` (backtick). There seems to be no way to properly quote table names with [] (square brackets) when working with SQL Server databases.



 Comments   
Comment by Benjamin Eberlei [ 11/Jun/11 ]

Just use `foo` as quotes, its just an abstract concept, doctrine will translate that to [foo] in MsSQL.

Comment by Patrick Schwisow [ 13/Jun/11 ]

I don't see how that would work. It appears that DBAL\Platforms\AbstractPlatform::quoteIdentifier assumes that the opening and closing quotes are the same. In the case of MS SQL, it appears to use the double quote, not square brackets.

Comment by Benjamin Eberlei [ 15/Jun/11 ]

Assigned to Juokaz

Comment by Benjamin Eberlei [ 15/Jun/11 ]

Fixed





[DBAL-114] MsSqlPlatform - getListTablesSQL() get's sysdiagrams table Created: 26/Apr/11  Updated: 29/Aug/11  Resolved: 29/Aug/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.0.4
Fix Version/s: 2.1.2, 2.2

Type: Bug Priority: Critical
Reporter: Bostjan Oblak Assignee: Juozas Kaziukenas
Resolution: Fixed Votes: 1
Labels: None
Environment:

Windows 7, PHP 5.3.5, MSSQL 2008



 Description   

getListTablesSQL() function in MsSqlPlatform.php list all tables in database.

If you have saved Database Diagrams it returns "sysdiagrams" table too.
This table have field "name" with type "sysname" which have no mapping.

If you run orm:validate-schema you get:
[Doctrine\DBAL\DBALException]
Unknown database type sysname requested, Doctrine\DBAL\Platforms\MsSqlPlatform may not support it.

Best solution is that when getting tables to ignore "sysdiagrams" tables.



 Comments   
Comment by Bostjan Oblak [ 26/Apr/11 ]

One solution is to just add "AND name != 'sysdiagrams' " to sql statement

Comment by Benjamin Eberlei [ 30/Apr/11 ]

Assigned to Jouzas

Comment by Yaroslav Zenin [ 07/Jul/11 ]

Why it is not fixed yet? The solution is so easy. Please add to repository. Because on every release I should change it manually

Comment by Benjamin Eberlei [ 29/Aug/11 ]

Fixed





[DBAL-111] MySQL Driver possibly subject to sql injections with PDO::quote() Created: 18/Apr/11  Updated: 14/May/11  Resolved: 14/May/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.0.0-BETA2, 2.0.0-BETA3, 2.0.0-BETA4, 2.0.0-RC1-RC3, 2.0-RC4, 2.0-RC5, 2.0, 2.0.1, 2.0.2, 2.0.3, 2.0.4, 2.0.5, 2.1
Fix Version/s: 2.0.4, 2.0.5, 2.1

Type: Bug Priority: Critical
Reporter: Anthony Ferrara Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

MySQL Drivers



 Description   

Prior to 5.3.6, the MySQL PDO driver ignored the character set parameter to options. Due to MySQL's C api (and MySQLND), this is required for the proper function of mysql_real_escape_string() (the C API call). Since PDO uses the mres() C call for PDO::quote(), this means that the quoted string does not take into account the connection character set.

Starting with 5.3.6, that was fixed. So now if you pass the proper character set to PDO via driver options, sql injection is impossible while using the PDO::quote() api call.

PDO proof of concept
$dsn = 'mysql:dbname=INFORMATION_SCHEMA;host=127.0.0.1;charset=GBK;';
$pdo = new PDO($dsn, $user, $pass);
$pdo->exec('SET NAMES GBK');
$string = chr(0xbf) . chr(0x27) . ' OR 1 = 1; /*';
$sql = "SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME LIKE ".$pdo->quote($string)." LIMIT 1;";
$stmt = $pdo->query($sql);
var_dump($stmt->rowCount());

Expected Result: `int(0)`.
Actual Result: `int(1)`.

There are 2 issues to fix. First, the documentation does not indicate that you can pass the `charset` option to the MySQL Driver. This should be fixed so that users are given the proper option to set character sets.

Secondly, `Connection::setCharset()` should be modified for MySQL to throw an exception, since the character set is only safely setable using the DSN with PDO. This is a limitation of the driver and could be asked as a feature request for the PHP core. Either that, or a big warning should be put on the documentation of the API to indicate the unsafe character set change



 Comments   
Comment by Anthony Ferrara [ 19/Apr/11 ]

Note: issued same bug report for Doctrine1 as it's also affected: http://www.doctrine-project.org/jira/browse/DC-998

Comment by Anthony Ferrara [ 29/Apr/11 ]

Also note that prepared statements in PDO will suffer the same bug since PDO always emulates prepared statements for the mysql driver (even though it fully supports them in the source). See: http://bugs.php.net/bug.php?id=54638

Comment by Benjamin Eberlei [ 14/May/11 ]

Fixed, updated the docs





[DBAL-80] Connection::_bindTypedValues() error when $types[0] is null Created: 01/Jan/11  Updated: 01/Jan/11  Resolved: 01/Jan/11

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0
Fix Version/s: 2.0.1, 2.1

Type: Bug Priority: Critical
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

When $types[0] = null (default) then _bindTypedValues starts with the wrong offset and binds the wrong types against the wrong params.



 Comments   
Comment by Benjamin Eberlei [ 01/Jan/11 ]

Fixed





[DBAL-54] Incorrect sequence dropping in PostgreSQL Created: 21/Sep/10  Updated: 09/Oct/12  Resolved: 23/Sep/10

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.2.2
Fix Version/s: 2.0.0-RC1-RC3

Type: Bug Priority: Critical
Reporter: Tomasz Jędrzejewski Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

postgresql 8.4.3, Arch Linux 64-bit



 Description   

Currently, DBAL drops the PostgreSQL sequences using the query:

DROP SEQUENCE sequencename

While it is quite correct at the first look, it fails, if the sequence is actually used by a table. Because Doctrine 2 ORM tries to drop the sequences before the tables, it makes impossible to drop a database schema in PostgreSQL due to the following exception:


$ php53 doctrine.php orm:schema-tool:drop
Dropping database schema...
PDOException
SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR: cannot drop sequence admins_id_seq because other objects depend on it
DETAIL: default for table admins column id depends on sequence admins_id_seq
HINT: Use DROP ... CASCADE to drop the dependent objects too.
/.../Libs/Doctrine/DBAL/Connection.php
Line 570
Trace:
0. PDO::query on line 570
1. Doctrine\DBAL\Connection::executeQuery on line 484
2. Doctrine\ORM\Tools\SchemaTool::dropSchema on line 78
3. Doctrine\ORM\Tools\Console\Command\SchemaTool\DropCommand::executeSchemaCommand on line 59
4. Doctrine\ORM\Tools\Console\Command\SchemaTool\AbstractCommand::execute on line 159
5. Symfony\Component\Console\Command\Command::run on line 205
6. Symfony\Component\Console\Application::doRun on line 117
7. Symfony\Component\Console\Application::run on line 7


A solution is simply to add the "CASCADE" keyword at the end of the query.

Although I encountered this problem on DBAL 2.0-beta4, I checked the most up-to-date code on Git, and the problem is still present there.



 Comments   
Comment by Benjamin Eberlei [ 21/Sep/10 ]

Would it help to drop sequences after tables? If then i would just move the code blocks.

Comment by Tomasz Jędrzejewski [ 21/Sep/10 ]

In this particular case - yes, it would help. But consider that different database engines may have different dependencies between schema elements and be more or less restrictive, so they may require different order of code blocks. I'd recommend to make a bit deeper investigation here in order not to cause potential problems with other database engines.

Comment by Benjamin Eberlei [ 23/Sep/10 ]

Fixed

Comment by Jon Wadsworth [ 20/Jun/12 ]

This issue is happening again with Doctrine 2.2.2 on Postgres 9.1.3. when trying to drio a database I get this message even with --full-database

> php doctrine.php orm:schema-tool:drop --force --full-database
Dropping database schema...

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

I would love to help diagnose, just let me know what you need and I will be more than happy to help.

Comment by Jon Wadsworth [ 09/Oct/12 ]

I forgot to update this for anyone else who might have had my problem. The issue was solved for me when I was reviewing some models. The project was originally intended for MySQL and Generated Value Strategy was not set to Auto. Upon changing it to auto, everything worked correctly.





[DBAL-52] Schema tools will alter tables with custom column definitions forever Created: 15/Sep/10  Updated: 15/Sep/10  Resolved: 15/Sep/10

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

Type: Bug Priority: Critical
Reporter: Lars Strojny Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 0
Labels: None
Environment:

MySQL 5.1, PDO MySQL



 Description   

When using custom column definition, schema tool will execute the same update statements over and over. It looks like schema tool can't find out yet, if the custom column definition has already been applied.
As MySQL does full table rebuilds quite often (sometimes for no or at least debatable reasons), this makes the schema tool for large migrations quite hard to use in a production system deployment scenario.



 Comments   
Comment by Benjamin Eberlei [ 15/Sep/10 ]

Yes this is a known issue, i plan to fix this with DDC-42, however it will take until 2.1 i guess. It annoys me too

Maybe we should also add a note to the manual, schema-tool incremental update is a development tool and should not be used on the production server.





[DBAL-44] nullable is not working for all datatypes Created: 30/Aug/10  Updated: 16/May/14  Resolved: 30/Aug/10

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: 2.0.0-BETA3

Type: Bug Priority: Critical
Reporter: Daniel Freudenberger Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: None

Attachments: Text File nullable.patch    
Issue Links:
Reference
is referenced by DDC-1045 Schema-tool update missbehavior: Not ... Closed

 Description   

The nullable=true annotation is ignored from at least following Types:

  • SmallIntType
  • DecimalType
  • BooleanType (not sure if nullable makes sense here)


 Comments   
Comment by Roman S. Borschel [ 30/Aug/10 ]

This looks like it has been fixed for a while already. See:

http://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Types/SmallIntType.php
http://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Types/DecimalType.php
http://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Types/BooleanType.php

Comment by Daniel Freudenberger [ 30/Aug/10 ]

I'll take a look at the master next time before submitting a bug report. Anyway I think it would be better to not fix bugs without an existing ticket for the fixed bug

Comment by Daniel Freudenberger [ 30/Aug/10 ]

has already been fixed





[DBAL-50] PgSQL driver does not create indexes on foreign key columns Created: 18/Aug/10  Updated: 09/Jan/15  Resolved: 11/Sep/10

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.0-BETA4
Fix Version/s: 2.0.0-RC1-RC3

Type: Bug Priority: Critical
Reporter: Petr Motejlek Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Reference
is referenced by DBAL-1063 Exceptions from SchemaTool when runni... Resolved
is referenced by DDC-3478 [GH-1239] Fix index duplication for u... Resolved

 Description   

The PostgreSQL database does not create indexes for foreign key columns, the user has to create them by hand. I think that indexes for foreign keys should be created automatically... On my system, an index will not be created automatically for the group_id column in the user table.

/**
 * @Entity
 */
class User {
    /**
     * @ManyToOne(targetEntity="Group", inversedBy="users")
     */
    protected $group;
}

/**
 * @Entity
 */
class Group {
    /**
     * @OneToMany(targetEntity="User", mappedBy="group")
     */
    protected $users;

    public function __construct() {
        $this->users = new \Doctrine\Common\Collections\ArrayCollection();
    }
}

I am using current git clone and PgSQL 8.4.



 Comments   
Comment by Petr Motejlek [ 09/Sep/10 ]

I'd just like to add that there's an even worse problem with this – the indices are not created even for tables that Doctrine creates automatically – for example the joining tables...

Comment by Benjamin Eberlei [ 09/Sep/10 ]

i'll look into it.

Comment by Benjamin Eberlei [ 11/Sep/10 ]

Fixed in master, leading to several follow up bugs that all had to be fixed:

1. generate identifier allowed first char to be a number
2. postgresql composite foreign key detection left a space in the second (and more) column names
3. Index column names were not sanitized to lower-case, leading to comparison bugs.

There has been a major refactoring now such that, for each foreign key there is always an explicit index being created. On SQLite, Postgres and Oracle this can lead to quite some additional indexes being created now using SchemaTool --update. MySQL already did this implicitly.

There are now heuristics that detect duplicate indexes (based on columns indexed) and override rules (adding primary on columns foo, bar will delete index on columns foo bar).

Comment by Benjamin Eberlei [ 11/Sep/10 ]

Note, this commit will not get into Doctrine ORM master unless you update the git-submodule explicitly:

cd lib/vendor/doctrine-dbal
git checkout master

For RC-1 this will be visible to the ORM trunk/master also.





[DBAL-1085] Custom Type Compare Fails To Generate Correct Migrations Created: 19/Dec/14  Updated: 24/Dec/14  Resolved: 24/Dec/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0, 2.1, 2.2, 2.3, 2.4
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Nicolas Vanheuverzwijn Assignee: Steve Müller
Resolution: Invalid Votes: 0
Labels: migrations
Environment:

Everywhere



 Description   

// From doctrine 2.1
public function diffColumn(Column $column1, Column $column2)
{
$changedProperties = array();
if ( $column1->getType() != $column2->getType() )

{ $changedProperties[] = 'type'; }

...
}
The $column1->getType() will return the underlying platform object but the $column2->getType() will return the custom object type.

Because of the way the php compare function works, a custom type will always generate a changed property over the type of a column.

http://stackoverflow.com/questions/26964367/symfony2-doctrine-custom-types-generating-unneeded-migrations/27557785#27557785



 Comments   
Comment by Steve Müller [ 24/Dec/14 ]

Nicolas Vanheuverzwijn I think you will have to mark your custom type as requiring a SQL comment, otherwise the schema manager cannot distinguish between DateTime type and your custom type because both map to the same native SQL type. See here:

https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Types/Type.php#L327-L340

You will have to add the following to your custom type implementation:

/**
 * {@inheritdoc}
 */
public function requiresSQLCommentHint(AbstractPlatform $platform)
{
    return true;
}

Also I think it might be required to give your custom type a distinct name like:

/**
 * {@inheritdoc}
 */
public function getName()
{
    return 'datetime_utc';
}
Comment by Steve Müller [ 24/Dec/14 ]

See also: http://doctrine-orm.readthedocs.org/en/latest/cookbook/custom-mapping-types.html

Comment by Nicolas Vanheuverzwijn [ 24/Dec/14 ]

Wow, I shall take a look at this. This might be it ! Thanks a lot for your reply.

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

You're welcome. Can you please report if that fixed your problem so we can closse the issue eventually? Thanks.

Comment by Nicolas Vanheuverzwijn [ 24/Dec/14 ]

Yes sir. The problem I have was that I am using doctrine 2.2. I shall migrate my stuff to doctrine 2.5 and use that feature.

Comment by Nicolas Vanheuverzwijn [ 24/Dec/14 ]

This feature is implemented in version >=2.3 of Doctrine/DBAL.

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

Yes. 2.2 is EOL anyways and I think 2.3 is now only getting security fixes so an upgrade to at least 2.4 is highly recommended.





[DBAL-1062] upgrade from v2.4.3 to v2.5.0 is forcing recreating Indexes making Doctrine unusable Created: 05/Dec/14  Updated: 13/Jan/15  Resolved: 26/Dec/14

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

Type: Bug Priority: Major
Reporter: gondo Assignee: Steve Müller
Resolution: Fixed Votes: 1
Labels: index, rename
Environment:

any


Issue Links:
Reference
is referenced by DBAL-1063 Exceptions from SchemaTool when runni... Resolved
is referenced by DBAL-1092 [GH-756] [DBAL-1062] Fix renaming ind... Resolved

 Description   

after executing 'composer update' i was upgraded to dbal v2.5.0
(im using "doctrine/orm": "~2.2,>=2.2.3" in composer.json)
im using Symfony 2.6.*

now when i try 'app/console doctrine:schema:update --dump-sql' i see that doctrine wants to recreate indexes on some tables for no practical reason
nothing changed in the code.

example:
DROP INDEX idx_a604da13a76ed395 ON table1;
CREATE INDEX IDX_B7E704F0A76ED395 ON table1 (user_id);
DROP INDEX uniq_b3319c7d77153098 ON table2;
CREATE UNIQUE INDEX UNIQ_C984F95777153098 ON table2 (code);

however when i try to execute this update, im getting this error:
General error: 1553 Cannot drop index 'IDX_A604DA13A76ED395': needed in a foreign key constraint

this essentially prevents me from using automatic doctrine database mapping or using migration tools.



 Comments   
Comment by Marco Pivetta [ 05/Dec/14 ]

Downgraded to "Major", as this doesn't prevent usage of the DBAL at runtime.

You can still upgrade those indexes manually after having removed the FKs (to re-add them later on).

Seems like a case sensitivity issue of your setup: consider adding environment details.

Comment by Steve Müller [ 05/Dec/14 ]

Which database vendor are you using? Also interestingly the indexes get recreated with a different name. Thought of case sensitivity, too. I think the comparator nevertheless has to be adjusted to compare with identical case on index names.

Comment by gondo [ 05/Dec/14 ]

well it IS preventing me from using doctrine as it is.
i just downgraded dbal to v2.4.3 by adding "doctrine/dbal": "2.4.*", to my composer.js and everything is working fine. by that i mean, no commands to drop and create indexes.

im using mysql Ver 14.14 Distrib 5.6.21, for osx10.10 (x86_64) using EditLine wrapper

im developoing on OSX 10.10 and production is running CentOS.
i know that OSX is using case insensitive file system (i had to deal with it in the past when i was deploying to production)
but this time there is NO change in my code. zero. nothing.

if there was case sensitivity changes in dbal itself, that might be the problem, however that is nothing i can fix.

i would love to upgrade those indexes manualy, however i have no idea how to.
should i change something in the code? indexes were created and named by doctrine, not by me.
if you recommend updating database, that seems to be failing. i assume dropping indexes on live data might cause problems.
or is it safe to just drop and create these indexes in mysql cmd?

Comment by gondo [ 05/Dec/14 ]

i can not drop those indexes directly in mysql, im getting erros:
ERROR 1553 (HY000): Cannot drop index 'IDX_A604DA13A76ED395': needed in a foreign key constraint

Comment by Steve Müller [ 05/Dec/14 ]

okay just checked, casing is not a problem as identifiers will be lowercased automatically in Doctrine\DBAL\Schema\Table for comparison.
Need further information about the underlying database being used...

Comment by Steve Müller [ 05/Dec/14 ]

gondo please for now don't try to solve the issue automatically because it looks like a real issue we need to figure out. Otherwise there will be little chance we get to know the real cause of the issue...

Comment by gondo [ 05/Dec/14 ]

@Steve Müller for now i've solved it by downgrading dbal to v2.4.*
do you need some more information from me?
unfortunately i can't give you all the code, company policy + its quite big. but i can dump you database schemas and entity declarations of affected tables if that will help. please let me know.

Comment by Steve Müller [ 05/Dec/14 ]

Hmm cannot reproduce the problem. What I did:

composer create-project symfony/framework-standard-edition path/

then added 'doctrine/dbal": "2.4.*"' to composer.json

composer install

then created entity with unique column name (to force auto index generation)

php app/console doctrine:database:create
php app/console doctrine:schema:create

then changed 'doctrine/dbal": "2.4."' to 'doctrine/dbal": "2.5."' in composer.json

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

Tried that with pdo_mysql on ubuntu 14.04 x86_64.

Comment by gondo [ 05/Dec/14 ]

were you creating some tables with foreign keys?

Comment by gondo [ 05/Dec/14 ]

here is the list of all indexes what tries to be recreated:
http://pastebin.com/nFYp6pnp

here are the definitions of some entities + their schemas and indexes from mysql:
notification_channel
http://pastebin.com/EfkcyUnf
http://pastebin.com/Ngd1Lkbe

online_payment_option
http://pastebin.com/R5waCF35
http://pastebin.com/ti4TzyKX

user_settings
http://pastebin.com/gxed5kjY
http://pastebin.com/EiCBWKNQ

i've also tried to specify index with custom name as per http://doctrine-orm.readthedocs.org/en/latest/reference/annotations-reference.html#annref-index
to prevent this index recreation but without no luck, it was ignored.
i've tried to change the definition of `online_payment_option` table like this:
@ORM\Table(name="online_payment_option", options=

{"collate"="utf8_unicode_ci", "charset"="utf8"}

, indexes={@ORM\Index(name="TEST", columns=

{"code"}

)})
but after trying schema:update im still getting the same output http://pastebin.com/nFYp6pnp

Comment by Steve Müller [ 12/Dec/14 ]

Okay I think I get the problem now. I don't get any suggested update statements when upgrading from a DBAL 2.4 created schema to DBAL 2.5.
I assume this is because even in DBAL 2.4 the indexes are created with the name your update command suggests. For example:

DROP INDEX idx_a604da13a76ed395 ON notification_channel;
CREATE INDEX IDX_B7E704F0A76ED395 ON notification_channel (user_id);

You have an index named idx_a604da13a76ed395 in your database, the index name DBAL generates is IDX_B7E704F0A76ED395. Even in 2.4 this is the name that is generated by DBAL. The reason why you get those update statements is because index names are compared since DBAL 2.5 as part of the new index renaming feature.
I guess that the index name generation has changed in an earlier version of DBAL (can't prove that right now). So you probably created the index with a much earlier DBAL version back then and now it wants to rename it. This should be a one time "upgrade" step.
The reason why manually defining an index in your entity with a custom name has no effect is because ORM's schema tool prefers auto generated indexes over custom indexes if both fullfill the same criteria. This is something to be fixed in ORM then.
The foreign key problem is indeed something we have to deal with in DBAL. The update schema command should create SQL to first drop FKs, then rename indexes and afterwards recreate FKs again.
Hope that helps for now. Sorry for the upgrade circumstances...

Comment by gondo [ 12/Dec/14 ]

thank you very much for looking into this and spending time on it!
it is very likely that those indexes were created in older version, however I'm 100% that it is not older than 1 year.

if i understand correctly, there are several things what needs to be fixed (manual overwriting of indexes and generating proper update schema command)

is this something what will be fixed? or does this fall into "edge case" bucket and will be left until more people experience same problem?
so far I'm fine staying on 2.4.3 but eventually i would like to upgrade. if the fix is planned, i can wait. if not, than i can create manual update now.

thanks one more time

Comment by Steve Müller [ 12/Dec/14 ]

I am already on that foreign key issue. As soon as that is fixed, the generated update SQL should be valid so that it can safely be run and you don't need to update your index names manually then. As what the manual index preference is concerned that needs to be fixed in ORM. I might also have a look into this issue afterwards. I think it won't take long until DBAL 2.5.1 as there is another critical issue that needs to be adressed sonner than later.
With 2.5.1 you should be safe to update your schema automatically.

Comment by gondo [ 12/Dec/14 ]

perfect!
thats much sooner than i expected
thanks again

Comment by Gábor Tóth [ 22/Dec/14 ]

This is a deal breaker for us. We cannot use 2.5 branch until it is not fixed.

Comment by Marco Pivetta [ 22/Dec/14 ]

Gábor Tóth you are not forced to upgrade for now: consider sending a patch if it is that critical to you.

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

Gábor Tóth I provide a patch here: https://github.com/doctrine/dbal/pull/756

Comment by Doctrine Bot [ 26/Dec/14 ]

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

Comment by gondo [ 13/Jan/15 ]

i've just updated to 2.5.1 and the problem remains

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

gondo can you please post the SQL created by the schema tool and the error you get? Thanks.

Comment by gondo [ 13/Jan/15 ]

same as on the beginning, basically nothing changed for me :/
http://pastebin.com/NqP9aEae

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

Do you get an error when executing the SQL? I can see that foreign keys are now dropped before dropping and recreating the index which is part of the patch. The reason why the schema manager is still outputting upgrade SQL was discussed here before (index name mismatch). However this should only happen once now and it should work without an error.

Comment by gondo [ 13/Jan/15 ]

ah i see.
i was expecting that there will be no SQL update needed after this patch, but now i understand what you mean.
i was only doing `app/console doctrine:schema:update --dump-sql`
after trying `app/console doctrine:schema:update --force` (on localhost only) everything seems to be fine

perfect! i will do some more testing, hopefully i ll not destroy production database with this

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

Unfortunately we cannot prevent SQL generation for users that have different index names in their database than those created by the mapping. Those users will have to "resync" index names once and should be fine afterwards. If we would not compare index names, the index renaming feature would not be possible.
You should be safe to run the SQL in production as it is just dropping and recreating indexes / foreign keys.





[DBAL-1005] Timezones of DateTime instances are ignored when persisting dates Created: 15/Oct/14  Updated: 15/Oct/14  Resolved: 15/Oct/14

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

Type: Bug Priority: Major
Reporter: Brent Shaffer Assignee: Marco Pivetta
Resolution: Won't Fix Votes: 0
Labels: None


 Description   

When a DateTime instance, e.g. "2011-02-16 00:00:00 America/New_York" is written into the DB, the timezone is ignored and only "2011-02-16" is persisted. When fetching the date, it is written into a DateTime with the server's timezone, resulting in for example "2011-02-16 00:00:00 Europe/Berlin" which is not correct!

To fix this issue, Doctrine should convert dates to the server's timezone (if their own timezone differs) before persisting them or before executing queries containing DateTime instances.



 Comments   
Comment by Brent Shaffer [ 15/Oct/14 ]

I would like to reopen this issue - Doctrine is expecting the incoming DateTime objects to have the system's default_timezone. If they do NOT use the default timezone (let's say they, instead use UTC), then the date is saved in the format of the default timezone anyway, and upon hydration, the UNIX timestamp changes.

I don't see how this could ever be considered expected behavior. Doctrine is essentially modifying the timestamp being persisted.

Doctrine should set the timezone of the DateTime object prior to persistence using the date_default_timezone_get() method. Either that, or the persisted string should contain the timezone identifier of the initial DateTIme object.

Comment by Marco Pivetta [ 15/Oct/14 ]

See http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/cookbook/working-with-datetime.html

The current DateTime type completely ignores timezones and we will keep it like that for now.

Comment by Brent Shaffer [ 15/Oct/14 ]

@Marco thank you for your quick reply. Can you help me understand why the decision was made to expect the timezone to be the default instead of just setting it to be that way before persistence? It seems like all these woes could have been easily avoided...

Comment by Marco Pivetta [ 15/Oct/14 ]

Brent Shaffer it was indeed a mistake to not use stricter rules on DateTime instances, but due to the amount of code depending on this behavior right now, we cannot change the Doctrine\DBAL\Types\DateTimeType anymore.

Instead, we may consider introducing a new UTC-based datetime-type for 3.x.

A change is not going to be applied on existing logic.





[DBAL-911] Property access not yet allowed in path/to/MysqliConnection.php Created: 21/May/14  Updated: 22/May/14  Resolved: 22/May/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.5
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Till Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 0
Labels: None


 Description   

Updated doctrine/dbal from 0a7df7c58aeab4d1cef55a78e5ca50299a12a62b to 2.5.0-beta3 and received the following warning:

PHP Warning:  Doctrine\DBAL\Driver\Mysqli\MysqliConnection::__construct(): Property access is not allowed yet in /path/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/Mysqli/MysqliConnection.php on line 60


 Comments   
Comment by Till [ 22/May/14 ]

This duplicates DBAL-912 and can be closed.





[DBAL-834] SQLServer modifyLimitQuery does not work with aggregate functions in ORDER BY Created: 10/Mar/14  Updated: 05/May/14  Resolved: 05/May/14

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

Type: Bug Priority: Major
Reporter: Francesco Montefoschi Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: paginator
Environment:

SQL Server 2008 SP3


Issue Links:
Duplicate
duplicates DBAL-788 ORDER BY with function COUNT() fails Resolved
is duplicated by DBAL-875 [GH-573] [DBAL-834] Fix order by with... Resolved

 Description   

Starting with Doctrine 2.4, the `modifyLimitQuery` method does not work anymore with query using ORDER BY MAX(...)
See this example:

$sql = "SELECT MAX(heading_id) aliased, code
	FROM operator_model_operator
	GROUP BY code
	ORDER BY MAX(heading_id) DESC
";
$sql = $this->em->getConnection()->getDatabasePlatform()->modifyLimitQuery(
	$sql, 1, 0
);

Doctrine generates this SQL, which is invalid:

SELECT * FROM (SELECT MAX(heading_id) aliased, code
, ROW_NUMBER() OVER (ORDER BY MAX(heading_id) AS doctrine_rownum FROM operator_model_operator GROUP BY code) DESC
) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 1

The ORDER BY in moved into the OVER(), but the `preg_replace` in SQLServerPlatform.php stops to replace at the closing ")".



 Comments   
Comment by Francesco Montefoschi [ 10/Mar/14 ]

It is not possible to write `ORDER BY aliased` because it leads to a syntax error in SQL Server.

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

Francesco Montefoschi There have been some modifications to the modifyLimitQuery() method in SQL Server lately for 2.5 which address some problems with subqueries and aggregate functions. Not sure if that might already solve your issue. Can you please check if the problem also exists in the current master branch of DBAL?
See commit: https://github.com/doctrine/dbal/commit/9f3cb437c0f491599de4e1bd847235965f98ffd4

Comment by Francesco Montefoschi [ 11/Mar/14 ]
  - Removing doctrine/common (v2.4.1)
  - Installing doctrine/common (2.4.x-dev 9a7e20e)
    Cloning 9a7e20e779360f3b8a02c27a89d47d5a6fdce8d1

  - Removing doctrine/dbal (v2.4.2)
  - Installing doctrine/dbal (dev-master c61361d)
    Cloning c61361d8fcf65a977d8610ba78eb542a1d2f44b4

  - Removing doctrine/orm (v2.4.2)
  - Installing doctrine/orm (2.4.x-dev a949e87)
    Cloning a949e87ca88299cde368d2b574740753526b62c9

Same issue.

Comment by Flip [ 14/Mar/14 ]

on this line here https://github.com/doctrine/dbal/blob/9f3cb437c0f491599de4e1bd847235965f98ffd4/lib/Doctrine/DBAL/Platforms/SQLServerPlatform.php#L1164

try the following stuffs:

Puts "DESC" in a second capturing group (closer to the original regex, but not sure why you want to do this)
/ORDER\s+BY\s+([^)(]*(?:\(\w+\))?)(.*)/

Includes "DESC" in the first capturing group
/ORDER\s+BY\s+([^)(]*(?:\(\w+\))?.*)/

Same as last one, except this one stops capturing when it hits a ")" after "DESC"
/ORDER\s+BY\s+([^)(]*(?:\(\w+\))?[^)]*)/
Comment by M.K. [ 21/Mar/14 ]

This not only affects Queries with aggregate functions, but every Query that uses a limit and order by an entity-field alias.

Try this Testcase:

$query = $this->em->createQuery("
	SELECT a.id AS test
	FROM prim\\entity\\Article a
	ORDER BY test ASC
");
$query->setMaxResults(10);
echo "<h3>DQL</h3>";
var_dump($query->getDQL());
echo "<br><h3>SQL</h3>";
var_dump($query->getSQL());
echo "<br><h3>Result</h3>";
var_dump($query->getResult());
Comment by Flip [ 21/Mar/14 ]

The test case is incomplete as we don't have `kare\\entity
Article`. Please try the 3 proposed solutions and show the results from those adjustments.

Comment by M.K. [ 21/Mar/14 ]

This is just a sample Query for illustration. Replace it with whatever Entity you like.

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

Patch supplied in PR: https://github.com/doctrine/dbal/pull/573

Francesco Montefoschi Flip M.K. please review.

Comment by Francesco Montefoschi [ 28/Apr/14 ]

Hi Steve,

I tried 2.5.*@dev now and everything works for me.
About the code, I am not a regexp guru.

Just for review purpose, can you explain this regexp?
https://github.com/deeky666/dbal/commit/df1f3e4ce13eed6d0a406f34ea3174711531edae#diff-8a544d213159863ef39497f4b139b420R1155

Thank you.

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

The regex replaces the ORDER BY including nested parentheses expressions (uses recursion for that) until a terminating sequence is detected (for example a closing parenthesis that has no corresponding opening parenthesis from a previous ORDER BY expression.
This might not be perfect and complete but it is an improvement. It does not stop matching after the first closing parenthesis found.
Please note that the PR has not been merged yet so I am not sure whether you had the patch applied in your 2.5.*@dev version constraint.

Comment by Francesco Montefoschi [ 29/Apr/14 ]

Thank you Steve. I confirm you I tested your code (manually copied and pasted your patch to SQLServerPlatform in 2.5.*@dev ).
Maybe it is not perfect, but surely a huge improvement.

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

Alright. Thanks for testing.

Comment by M.K. [ 02/May/14 ]

I've had no time for testing this yet, but i read your code changes and i think this is definitely a big step forward. But it would be a lot nicer if you could always ORDER BY an alias in DQL. DBAL's goal is abstract away database specific language and for now users still have to worry about the Platform while writing DQL queries with ORDER BY :/

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

I'm not sure if I understand correctly. Can you please give an example of what you mean? It sounds like it's another issue?

Comment by M.K. [ 02/May/14 ]
SELECT MAX(heading_id) aliased, code
FROM operator_model_operator
GROUP BY code
ORDER BY aliased DESC

This Query won't work with modifyLimitQuery

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

M.K. Okay I think I get what you mean but that is another issue IMO. There should be a new ticket for this.

Comment by Francesco Montefoschi [ 05/May/14 ]

Can we merge this in master/2.5?

Comment by Doctrine Bot [ 05/May/14 ]

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

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

Fixed in commit: https://github.com/doctrine/dbal/commit/4a7ff71ec3b57af7d70f1180897502f8a156d59b





[DBAL-825] ALTER COLUMN on mssql is failing if default constraint is attached Created: 03/Mar/14  Updated: 08/May/14  Resolved: 08/May/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: 2.5

Type: Bug Priority: Major
Reporter: Thomas Müller Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: None
Environment:

MSSQL


Issue Links:
Duplicate
is duplicated by DBAL-826 [GH-536] [WIP] unit test for DBAL-825 Resolved
is duplicated by DBAL-833 [GH-542] [DBAL-825] Drop default cons... Resolved

 Description   

Here is the unit test - implemented in class SchemaManagerFunctionalTestCase

 
    public function testChangeColumnsTypeWithDefault()
    {
        $table = new \Doctrine\DBAL\Schema\Table('column_change_type_test');
        $table->addColumn('id', 'integer', array('default' => 5));

        $this->_sm->createTable($table);

        $columns = $this->_sm->listTableColumns("column_change_type_test");
        $this->assertEquals(1, count($columns));
        $this->assertInstanceOf('Doctrine\DBAL\Types\IntegerType', $columns['id']->getType());

        $tableDiff = new \Doctrine\DBAL\Schema\TableDiff('column_change_type_test');
        $tableDiff->changedColumns['id'] = new \Doctrine\DBAL\Schema\ColumnDiff(
            'id', new \Doctrine\DBAL\Schema\Column(
                'id', \Doctrine\DBAL\Types\Type::getType('smallint'), array('default' => 5)
            ),
            array('type'),
            new \Doctrine\DBAL\Schema\Column(
                'id', \Doctrine\DBAL\Types\Type::getType('integer'), array('default' => '5')
            )
        );

        $this->_sm->alterTable($tableDiff);

        $columns = $this->_sm->listTableColumns("column_change_type_test");
        $this->assertEquals(1, count($columns));
        $this->assertInstanceOf('Doctrine\DBAL\Types\SmallIntType', $columns['id']->getType());
        $this->assertSame('', $columns['id']->getDefault());
    }

Causes following result

 
Exception : [Doctrine\DBAL\DBALException] An exception occurred while executing 'ALTER TABLE column_change_type_test ALTER COLUMN id SMALLINT NOT NULL':

SQLSTATE [42000, 5074]: [Microsoft][SQL Server Native Client 11.0][SQL Server]The object 'DF_A74995E2_BF396750' is dependent on column 'id'.
SQLSTATE [42000, 4922]: [Microsoft][SQL Server Native Client 11.0][SQL Server]ALTER TABLE ALTER COLUMN id failed because one or more objects access this column.

With queries:
5. SQL: 'ALTER TABLE column_change_type_test ALTER COLUMN id SMALLINT NOT NULL' Params: 
4. SQL: 'SELECT    col.name,
                          type.name AS type,
                          col.max_length AS length,
                          ~col.is_nullable AS notnull,
                          def.definition AS [default],
                          col.scale,
                          col.precision,
                          col.is_identity AS autoincrement,
                          col.collation_name AS collation,
                          CAST(prop.value AS NVARCHAR(MAX)) AS comment -- CAST avoids driver error for sql_variant type
                FROM      sys.columns AS col
                JOIN      sys.types AS type
                ON        col.user_type_id = type.user_type_id
                JOIN      sys.objects AS obj
                ON        col.object_id = obj.object_id
                JOIN      sys.schemas AS scm
                ON        obj.schema_id = scm.schema_id
                LEFT JOIN sys.default_constraints def
                ON        col.default_object_id = def.object_id
                AND       col.object_id = def.parent_object_id
                LEFT JOIN sys.extended_properties AS prop
                ON        obj.object_id = prop.major_id
                AND       col.column_id = prop.minor_id
                AND       prop.name = 'MS_Description'
                WHERE     obj.type = 'U'
                AND       (obj.name = 'column_change_type_test' AND scm.name = SCHEMA_NAME())' Params: 
3. SQL: 'ALTER TABLE column_change_type_test ADD CONSTRAINT DF_A74995E2_BF396750 DEFAULT 5 FOR id' Params: 
2. SQL: 'CREATE TABLE column_change_type_test (id INT NOT NULL)' Params: 

Trace:
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Connection.php:988
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php:971
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php:612
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\SQLServerSchemaManager.php:232
C:\projects\doctrine\dbal\tests\Doctrine\Tests\DBAL\Functional\Schema\SchemaManagerFunctionalTestCase.php:621
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:976
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:831
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestResult.php:648
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:776
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php:775
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php:745
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\TextUI\TestRunner.php:349
C:\Program Files (x86)\PHP\v5.3\pear\PHPUnit\TextUI\Command.php:176
C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php:268
C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php:506

#0 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php(946): Doctrine\Tests\DbalFunctionalTestCase->onNotSuccessfulTest(Object(Doctrine\DBAL\DBALException))
#1 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestResult.php(648): PHPUnit_Framework_TestCase->runBare()
#2 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php(776): PHPUnit_Framework_TestResult->run(Object(Doctrine\Tests\DBAL\Functional\Schema\SQLServerSchemaManagerTest))
#3 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php(775): PHPUnit_Framework_TestCase->run(Object(PHPUnit_Framework_TestResult))
#4 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php(745): PHPUnit_Framework_TestSuite->runTest(Object(Doctrine\Tests\DBAL\Functional\Schema\SQLServerSchemaManagerTest), Object(PHPUnit_Framework_TestResult))
#5 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\TextUI\TestRunner.php(349): PHPUnit_Framework_TestSuite->run(Object(PHPUnit_Framework_TestResult), false, Array, Array, false)
#6 C:\Program Files (x86)\PHP\v5.3\pear\PHPUnit\TextUI\Command.php(176): PHPUnit_TextUI_TestRunner->doRun(Object(PHPUnit_Framework_TestSuite), Array)
#7 C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php(268): PHPUnit_TextUI_Command->run(Array, true)
#8 C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php(506): IDE_Base_PHPUnit_TextUI_Command::main()
#9 {main}



 Comments   
Comment by Thomas Müller [ 03/Mar/14 ]

Possible solution: http://www.select-sql.com/mssql/how-to-alter-column-with-default-constraint-in-mssql.html

Comment by Thomas Müller [ 03/Mar/14 ]

Here is the unit test: https://github.com/DeepDiver1975/dbal/commit/53238301f7e124d31232e9b3eab774c32c9e04c4

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

Thomas Müller Thanks for reporting. Which version of SQL Server is affected by this?

Comment by Thomas Müller [ 03/Mar/14 ]

SQL Server 2012 Express Edition

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

Fixed in commit: https://github.com/doctrine/dbal/commit/171a8762673ee61a89e3d6ce891cd2b475e7b5f7





[DBAL-809] Decimal type: not convert to double variable type Created: 09/Feb/14  Updated: 09/Feb/14  Resolved: 09/Feb/14

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

Type: Bug Priority: Major
Reporter: Vitaliy Zhuk Assignee: Benjamin Eberlei
Resolution: Can't Fix Votes: 0
Labels: None


 Description   

Hi.

The doctrine DBAL type "decimal" not convert value to "double" variable type in PHP. And if use events for control changes set, we have a changes for field.

For example:

/** Entity class **/
/** @ORM\Column(name="field", type="decimal")
private $field;

/** Create entity **/
$entity = new MyEntity();
$entity->setField(1);
$em->persist($entity);
$em->flush($entity);

/** Load entity **/
$entity = $em->field('MyEntity', 1);
var_dump($entity->getField()); // Then we have a string type
$entity->setField(1); // Set a integer type

Listener (onFlush):

$em = $event->getEntityManager();
$uow = $em->getUnitOfWork();

$entity = $event->getEntity();
$changes = $uow->getEntityChangeSet($entity);

var_dump($changes); // Changes exists, because variable type not equals

https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Types/DecimalType.php#L52



 Comments   
Comment by Vitaliy Zhuk [ 09/Feb/14 ]

Sorry, i not seen the attention section in docs: http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/types.html#decimal





[DBAL-615] Escape platform identifiers in ALTER queries Created: 26/Sep/13  Updated: 09/Oct/13  Resolved: 26/Sep/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.4, 2.4.1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Arthur Bodera Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: None


 Description   

PR302 introduced platform quoting for manipulation of PK, FK and indexes. Alter queries are left unquoted, which means that ALTER with table names that happen to be reserved platform keywords, will fail.

For example:

ALTER TABLE order DROP FOREIGN KEY FK_F5299398D5289B7F;
ALTER TABLE set DROP FOREIGN KEY FK_E61425DC1BF22D93;
ALTER TABLE storage ADD CONSTRAINT FK_547A1B3487068541 FOREIGN KEY (`addressId`) REFERENCES `address` (`id`);


 Comments   
Comment by Arthur Bodera [ 26/Sep/13 ]

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

Comment by Doctrine Bot [ 26/Sep/13 ]

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

Comment by Arthur Bodera [ 09/Oct/13 ]

It's not invalid.

Discussion here: https://github.com/doctrine/dbal/pull/379





[DBAL-565] MySQL 5.6, Doctrine\DBAL\DBALException: Unknown database type bit requested. Created: 22/Jul/13  Updated: 22/Jul/13  Resolved: 22/Jul/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3.4
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Arthur Bodera Assignee: Benjamin Eberlei
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

Mysql 5.6.12, MacOS, PHP 5.4.16.



 Description   

Given the following db table connected with an ORM entity:

CREATE TABLE `foo` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `bitField` bit(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Running something like orm:validate-schema will result in the following exception.

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

BIT datatype is available since mysql 5.0.3 but D2 Mysql platform doesn't seem to understand it



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

Arthur Bodera the platforms only support base types (common to all platforms) out of the box. If you want additional types, you need to register them.

Comment by Arthur Bodera [ 22/Jul/13 ]

It seems that BIT(M) column represents packed bits and is not directly transferable to "boolean", nor to (unpacked) "binary" d2 types. This leaves this column type in an awkward position, where no d2 built-in type matches it perfectly. Mapping it to "boolean" limits its scope and could result in data loss. Mapping it to "binary" could result in unexpected behavior.

Right now, the only 2 valid solutions I see are:





[DBAL-558] Incorrect extracting of placeholder positions from statement Created: 15/Jul/13  Updated: 29/Dec/13  Resolved: 29/Dec/13

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.3
Fix Version/s: 2.5, 2.4.2

Type: Bug Priority: Major
Reporter: Yevhen Shyshkin Assignee: Steve Müller
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux Ubuntu 12.04 x64
PHP 5.3.10-1ubuntu3.6 with Suhosin-Patch
PostgreSQL 9.1.9



 Description   

I'm trying to execute statement:

SELECT Count(*)
FROM   (SELECT DISTINCT o6_.id                          AS id0,
                        COALESCE(t3_.content, o2_.name) AS name1,
                        o7_.postal_code                 AS postal_code2,
                        CASE
                          WHEN o7_.state_text IS NOT NULL THEN o7_.state_text
                          ELSE COALESCE(t5_.content, o4_.name)
                        END
                        || ''                           AS sclr3,
                        CASE
                          WHEN o6_.id IN ( ? )
                               AND o6_.id NOT IN ( ? ) THEN true
                          ELSE false
                        END                             AS sclr4
        FROM   orocrm_contact o6_
               LEFT JOIN orocrm_contact_value o8_
                      ON o6_.id = o8_.entity_id
               LEFT JOIN oro_flexibleentity_attribute o0_
                      ON o8_.attribute_id = o0_.id
               LEFT JOIN orocrm_contact_value_option o10_
                      ON o8_.id = o10_.value_id
               LEFT JOIN oro_flexibleentity_attribute_option o9_
                      ON o9_.id = o10_.option_id
               LEFT JOIN oro_flexibleentity_attribute_option_value o11_
                      ON o9_.id = o11_.option_id
               LEFT JOIN orocrm_contact_address o7_
                      ON o6_.id = o7_.owner_id
                         AND ( o7_.is_primary = true )
               LEFT JOIN orocrm_contact_to_contact_group o13_
                      ON o6_.id = o13_.contact_id
               LEFT JOIN orocrm_contact_group o12_
                      ON o12_.id = o13_.contact_group_id
               LEFT JOIN oro_dictionary_country o2_
                      ON o7_.country_code = o2_.iso2_code
               LEFT JOIN oro_dictionary_region o4_
                      ON o7_.region_code = o4_.combined_code
               LEFT JOIN oro_flexibleentity_attribute_translation t1_
                      ON t1_.locale = 'ru'
                         AND t1_.field = 'label'
                         AND t1_.object_class =
                             'Oro\Bundle\FlexibleEntityBundle\Entity\Attribute'
                         AND t1_.foreign_key = o0_.id
               LEFT JOIN oro_dictionary_country_translation t3_
                      ON t3_.locale = 'ru'
                         AND t3_.field = 'name'
                         AND t3_.object_class =
                             'Oro\Bundle\AddressBundle\Entity\Country'
                         AND t3_.foreign_key = o2_.iso2_code
               LEFT JOIN oro_dictionary_region_translation t5_
                      ON t5_.locale = 'ru'
                         AND t5_.field = 'name'
                         AND t5_.object_class =
                             'Oro\Bundle\AddressBundle\Entity\Region'
                         AND t5_.foreign_key = o4_.combined_code
        WHERE  ( CASE
                   WHEN o6_.id IN ( ? )
                        AND o6_.id NOT IN ( ? ) THEN true
                   ELSE false
                 END <> false )
               AND CASE
                     WHEN o7_.state_text IS NOT NULL THEN o7_.state_text
                     ELSE COALESCE(t5_.content, o4_.name)
                   END
                   || '' LIKE ?) AS e  

When this statement passes to Doctrine\DBAL\SQLParserUtils::getPlaceholderPositions, it extracts only three placeholder positions instead of five.

As a result, Doctrine can't map parameters and types properly, so SQL request fails.

It looks like this unexpected behavior appeared because of regular expression in SQLParserUtils::getUnquotedStatementFragments, that incorrectly extracts unquoted statements.



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

Patch supplied in PR: https://github.com/doctrine/dbal/pull/480

Comment by Doctrine Bot [ 29/Dec/13 ]

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





[DBAL-452] range is a reserved word in several platforms Created: 21/Feb/13  Updated: 15/Mar/13  Resolved: 14/Mar/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3.3
Fix Version/s: 2.3.3

Type: Bug Priority: Major
Reporter: Michael Cummings Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux 64bit



 Description   

'range' is a reserved word for MySQL, Oracle, and on future reserved list for SQL Server as well so should really be added for escaping. Working on a new project using Doctrine accessing an external API which used it as a field and got an error when running orm:schema-tool:create on my entity with MySQL.



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

Added for MySQL and Oracle. Didn't find data on when its added to SQL Server.

Comment by Michael Cummings [ 15/Mar/13 ]

http://msdn.microsoft.com/en-us/library/ms189822.aspx under Future Keywords





[DBAL-397] PostgreSQL - getDateTimeTzFormatString() Created: 12/Dec/12  Updated: 17/Dec/12  Resolved: 16/Dec/12

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3.1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Phill Pafford Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: datetime, dql, format, postgresql
Environment:

PostgreSQL Database



 Description   

In:

  • vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/PostgreSqlPlatform.php

The function:

getDateTimeTzFormatString()

only returns one datetime format:

    /**
     * {@inheritDoc}
     */
    public function getDateTimeTzFormatString()
    {
        //return 'Y-m-d H:i:sO'; // original format
        return 'Y-m-d H:i:s.uO'; // format also needed
    }


Here is the error I get using the original format:

Could not convert database value "2012-12-07 16:01:52.580789-05" to Doctrine Type datetimetz. Expected format: Y-m-d H:i:s.O

If I use this format: 'Y-m-d H:i:s.uO' it works.



 Comments   
Comment by Phill Pafford [ 12/Dec/12 ]

adding html tags for code to display correctly

Comment by Benjamin Eberlei [ 16/Dec/12 ]

DateTimeTz is created as 'TIMESTAMP(0) WITH TIME ZONE' on DBAL. If you have 'TIMESTAMP(6) WITH TIME ZONE' instead, then you have to create your own datatye.

Comment by Phill Pafford [ 17/Dec/12 ]

Why would this be a new data type? I understand how to fix the problem with creating my own data type to handle the formatting issue and would even suggest just overriding the data type with

        Type::overrideType('datetimetz', 'Doctrine\DBAL\Types\VarDateTimeType');
    Type::overrideType('datetime', 'Doctrine\DBAL\Types\VarDateTimeType');

But it's not a new data type, it's a formatting issue.





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

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

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

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



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

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

cli-config.php:

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

require_once 'Doctrine/Common/ClassLoader.php';

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

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

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

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

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

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

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

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

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

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

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


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

Formatting

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

Should be fixed with following PR:

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

Comment by Doctrine Bot [ 13/Nov/13 ]

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





[DBAL-335] Is MasterSlaveConnection implemented correctly - seems to overwrite master connection on transaction methods? Created: 31/Aug/12  Updated: 16/Oct/14  Resolved: 17/Sep/12

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

Type: Bug Priority: Major
Reporter: Jonathan Ingram Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Dependency
is required for DBAL-1006 [GH-690] Backport [DBAL-717] Fix bug ... Resolved

 Description   

Forgive me to doubt, but I think there may be a bug in MasterSlaveConnection.

It's easier to understand what I'm saying by debugging and tracing the flow, but I'll illustrate it with gists.

First, here is a simple service method to create a user. It opens up a transaction, persists the user, commits and returns. On error, if there is an active transaction, rollback. Here is the gist:

https://gist.github.com/3547674

The "$conn->beginTransaction();" line is where we trace through (the remainder of the service method is now irrelevant). Looking into MasterSlaveConnection.php, we see the method tries to connect to the master connection (call this point ###):

https://gist.github.com/3547720

Now looking in the next gist, we see what happens when "$this->connect('master');" is called. At this point it's not that interesting, the internal "$this->_conn" property is set to "master".

https://gist.github.com/3547750

Now here lies the bug I believe. "parent::beginTransaction();" is called. When looking into this method, we see that another call is made to connect but this time without "master" as the argument (i.e. connect to slave). This call to connect is made before incrementing the transaction nesting level.

https://gist.github.com/3547808

Now, I won't do another gist for "MasterSlaveConnection::connect", but if you refer to the file at line 13 https://gist.github.com/3547750#file_master_slave_connection.php, you will see that it checks the transaction nesting level and if it is there, forces master. However, we don't increment the level until after the method returns, so the slave is used. Ultimately, this results in the internal "$this->_conn" property set to the "slave" connection which violates our original action at ### above where we said we want to connect to "master".

Am I missing something here? Here is a gist the is a basic attempt at fixing this one method. It simply copies the code from the parent method except does not connect twice. I believe the same would have to occur for all the other methods unless it can be fixed once at the "MasterSlaveConnection::connect" level.

https://gist.github.com/3547880

I've just fleshed out "beginTransaction", "commit" and "rollBack" in "MasterSlaveConnection" by basically copying and pasting the code from the parent class and for my failing use case, this fixes the issue. However, it did require updating "Connection" slightly so that I had access to some private variables.



 Comments   
Comment by Lars Strojny [ 31/Aug/12 ]

This looks indeed like a bug. From a first glimpse the fix would be to use master, if master is already connected.

Comment by Benjamin Eberlei [ 05/Sep/12 ]

This only happens when "keepSlave" = true, because then the master is not written into the slave property aswell:

   } else {
     $this->connections['slave'] = $this->_conn = $this->connectTo($connectionName);
   } 

Are you using keepSlave = true?

Comment by Jonathan Ingram [ 05/Sep/12 ]

Yes I am. Does that render this moot or still a bug?

Comment by Benjamin Eberlei [ 06/Sep/12 ]

Its still a bug, but it helps to know why this happens.

Comment by Benjamin Eberlei [ 17/Sep/12 ]

Fixed in master and 2.3, can you test it?

Comment by Jonathan Ingram [ 19/Sep/12 ]

Thanks for doing this. I will test it shortly.

Comment by Ivan Andric [ 22/Sep/12 ]

Hi,

not sure if you managed to test this but now test on mysql database fails with results below.
Probably some typo.

There was 1 error:

1) Doctrine\Tests\DBAL\Functional\MasterSlaveConnectionTest::testKeepSlaveBeginTransactionStaysOnMaster
Exception: [Doctrine\DBAL\DBALException] An exception occurred while executing 'INSERT INTO master_slave_table (test_int) VALUES ' with params

{"1":30}

:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '30' for key 'PRIMARY'

With queries:
2. SQL: 'CREATE TABLE master_slave_table (test_int INT NOT NULL, PRIMARY KEY(test_int)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB' Params:

Trace:
/home/ivan/git/dbal2/dbal/lib/Doctrine/DBAL/Connection.php:793
/home/ivan/git/dbal2/dbal/lib/Doctrine/DBAL/Connections/MasterSlaveConnection.php:231
/home/ivan/git/dbal2/dbal/lib/Doctrine/DBAL/Connection.php:539
/home/ivan/git/dbal2/dbal/lib/Doctrine/DBAL/Connections/MasterSlaveConnection.php:285
/home/ivan/git/dbal2/dbal/tests/Doctrine/Tests/DBAL/Functional/MasterSlaveConnectionTest.php:92

/home/ivan/git/dbal2/dbal/tests/Doctrine/Tests/DbalFunctionalTestCase.php:73
/home/ivan/git/dbal2/dbal/lib/Doctrine/DBAL/Connection.php:793
/home/ivan/git/dbal2/dbal/lib/Doctrine/DBAL/Connections/MasterSlaveConnection.php:231
/home/ivan/git/dbal2/dbal/lib/Doctrine/DBAL/Connection.php:539
/home/ivan/git/dbal2/dbal/lib/Doctrine/DBAL/Connections/MasterSlaveConnection.php:285
/home/ivan/git/dbal2/dbal/tests/Doctrine/Tests/DBAL/Functional/MasterSlaveConnectionTest.php:92

Caused by
Doctrine\DBAL\DBALException: An exception occurred while executing 'INSERT INTO master_slave_table (test_int) VALUES ' with params

{"1":30}

:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '30' for key 'PRIMARY'

/home/ivan/git/dbal2/dbal/lib/Doctrine/DBAL/DBALException.php:47
/home/ivan/git/dbal2/dbal/lib/Doctrine/DBAL/Connection.php:786
/home/ivan/git/dbal2/dbal/lib/Doctrine/DBAL/Connections/MasterSlaveConnection.php:231
/home/ivan/git/dbal2/dbal/lib/Doctrine/DBAL/Connection.php:539
/home/ivan/git/dbal2/dbal/lib/Doctrine/DBAL/Connections/MasterSlaveConnection.php:285
/home/ivan/git/dbal2/dbal/tests/Doctrine/Tests/DBAL/Functional/MasterSlaveConnectionTest.php:92

Caused by
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '30' for key 'PRIMARY'

/home/ivan/git/dbal2/dbal/lib/Doctrine/DBAL/Connection.php:786
/home/ivan/git/dbal2/dbal/lib/Doctrine/DBAL/Connections/MasterSlaveConnection.php:231
/home/ivan/git/dbal2/dbal/lib/Doctrine/DBAL/Connection.php:539
/home/ivan/git/dbal2/dbal/lib/Doctrine/DBAL/Connections/MasterSlaveConnection.php:285
/home/ivan/git/dbal2/dbal/tests/Doctrine/Tests/DBAL/Functional/MasterSlaveConnectionTest.php:92





[DBAL-278] add support for lastInsertId method on OCI8 Driver Created: 16/May/12  Updated: 22/May/12  Resolved: 22/May/12

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.2.2
Fix Version/s: 2.3

Type: Improvement Priority: Major
Reporter: Franek Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

oci8



 Description   

The method lastInsertId() is not defined for OCI8 Driver in Doctrine\DBAL\Driver\OCI8\OCI8Connection.php :

OCI8Connection.php
public function lastInsertId($name = null)
{
    //TODO: throw exception or support sequences?
}

I propose this method to handle lastInsertId for sequence :

OCI8Connection.php
public function lastInsertId($name = null)
 {
        // For sequence
        if (is_string($name)) {
            // We can check eventually check the presence of the sequence in the table
            // USER_SEQUENCES
            $sql = 'SELECT ' . $name . '.CURRVAL FROM DUAL';
            // will throw an exception if this sequence does not exist
            $stmt = $this->query($sql);
            $result = $stmt->fetch(\PDO::FETCH_ASSOC);
            if ($result !== false && isset($result['CURRVAL'])) {
                return (int) $result['CURRVAL'];
            }
    }
    // OCI8 driver does not provide support of lastInsertId
    return null;
}

Thanks,






[DBAL-230] Custom types not taken into account when running $ doctrine orm:validate-schema Created: 05/Mar/12  Updated: 14/Mar/12  Resolved: 14/Mar/12

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.2
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Maxime MARAIS Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: None
Environment:

Postgres



 Description   

Hi,

I created a class to handle PostgreSQL "inet" type and setup bootstape in order to register this new type.

PostgresInetType.php
namespace Doctrine\DBAL\Types;

class PostgresInetType extends Type {

    const INET = 'inet'; // modify to match your type name

    public function getSqlDeclaration(array $fieldDeclaration, AbstractPlatform $platform) {
        return 'Inet';
    }

    public function convertToPHPValue($value, AbstractPlatform $platform) {
        return new $value;
    }

    public function convertToDatabaseValue($value, AbstractPlatform $platform) {
        if (null === $value) {
            return null;
        } elseif (preg_match("/^((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?).(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?).(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?).(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?))|((([0-9A-Fa-f]{1,4}:){7}[0-9A-Fa-f]{1,4})|(([0-9A-Fa-f]{1,4}:){6}:[0-9A-Fa-f]{1,4})|(([0-9A-Fa-f]{1,4}:){5}:([0-9A-Fa-f]{1,4}:)?[0-9A-Fa-f]{1,4})|(([0-9A-Fa-f]{1,4}:){4}:([0-9A-Fa-f]{1,4}:){0,2}[0-9A-Fa-f]{1,4})|(([0-9A-Fa-f]{1,4}:){3}:([0-9A-Fa-f]{1,4}:){0,3}[0-9A-Fa-f]{1,4})|(([0-9A-Fa-f]{1,4}:){2}:([0-9A-Fa-f]{1,4}:){0,4}[0-9A-Fa-f]{1,4})|(([0-9A-Fa-f]{1,4}:){6}((b((25[0-5])|(1d{2})|(2[0-4]d)|(d{1,2}))b).){3}(b((25[0-5])|(1d{2})|(2[0-4]d)|(d{1,2}))b))|(([0-9A-Fa-f]{1,4}:){0,5}:((b((25[0-5])|(1d{2})|(2[0-4]d)|(d{1,2}))b).){3}(b((25[0-5])|(1d{2})|(2[0-4]d)|(d{1,2}))b))|(::([0-9A-Fa-f]{1,4}:){0,5}((b((25[0-5])|(1d{2})|(2[0-4]d)|(d{1,2}))b).){3}(b((25[0-5])|(1d{2})|(2[0-4]d)|(d{1,2}))b))|([0-9A-Fa-f]{1,4}::([0-9A-Fa-f]{1,4}:){0,5}[0-9A-Fa-f]{1,4})|(::([0-9A-Fa-f]{1,4}:){0,6}[0-9A-Fa-f]{1,4})|(([0-9A-Fa-f]{1,4}:){1,7}:))$/", $value)) {
            return $value;
        } else {
            throw new PostgresInetTypeException($value);
        }
    }

    public function getName() {
        return self::INET;
    }

}
bootstrap.php
// ... other Doctrine setup stuff

// Define Database driver
$objDBALConfig = new \Doctrine\DBAL\Configuration();

$arrConnectionOptions = array(
    'dbname' => 'my_pg_database',
    'user' => 'itsme',
    'host' => '127.0.0.1',
    'driver' => 'pdo_pgsql',
);

$objDBALConnection = DriverManager::getConnection($arrConnectionOptions, $objDBALConfig);

// Add special PostgresType INET
Type::addType('inet', 'Doctrine\DBAL\Types\PostgresInetType');
$objDBALConnection->getDatabasePlatform()->registerDoctrineTypeMapping('Inet', 'inet');

// Instanciate EntityManager

$objDoctEntityManager = EntityManager::create($objDBALConnection, $objDoctConfig);

// ...

When running

php doctrine.php orm:validate-schema --verbose

I get the following:

[Mapping]  OK - The mapping files are correct.


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


Exception trace:
 () at /var/www/vhosts/workflow/project/Doctrine/DBAL/Platforms/AbstractPlatform.php:261
 Doctrine\DBAL\Platforms\AbstractPlatform->getDoctrineTypeMapping() at /var/www/vhosts/workflow/project/Doctrine/DBAL/Schema/PostgreSqlSchemaManager.php:285
 Doctrine\DBAL\Schema\PostgreSqlSchemaManager->_getPortableTableColumnDefinition() at /var/www/vhosts/workflow/project/Doctrine/DBAL/Schema/AbstractSchemaManager.php:672
 Doctrine\DBAL\Schema\AbstractSchemaManager->_getPortableTableColumnList() at /var/www/vhosts/workflow/project/Doctrine/DBAL/Schema/AbstractSchemaManager.php:159
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableColumns() at /var/www/vhosts/workflow/project/Doctrine/DBAL/Schema/AbstractSchemaManager.php:254
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails() at /var/www/vhosts/workflow/project/Doctrine/DBAL/Schema/AbstractSchemaManager.php:242
 Doctrine\DBAL\Schema\AbstractSchemaManager->listTables() at /var/www/vhosts/workflow/project/Doctrine/DBAL/Schema/AbstractSchemaManager.php:830
 Doctrine\DBAL\Schema\AbstractSchemaManager->createSchema() at /var/www/vhosts/workflow/project/Doctrine/ORM/Tools/SchemaTool.php:689
 Doctrine\ORM\Tools\SchemaTool->getUpdateSchemaSql() at /var/www/vhosts/workflow/project/Doctrine/ORM/Tools/SchemaValidator.php:258
 Doctrine\ORM\Tools\SchemaValidator->schemaInSyncWithMetadata() at /var/www/vhosts/workflow/project/Doctrine/ORM/Tools/Console/Command/ValidateSchemaCommand.php:80
 Doctrine\ORM\Tools\Console\Command\ValidateSchemaCommand->execute() at /var/www/vhosts/workflow/project/Doctrine/Symfony/Component/Console/Command/Command.php:187
 Symfony\Component\Console\Command\Command->run() at /var/www/vhosts/workflow/project/Doctrine/Symfony/Component/Console/Application.php:194
 Symfony\Component\Console\Application->doRun() at /var/www/vhosts/workflow/project/Doctrine/Symfony/Component/Console/Application.php:118
 Symfony\Component\Console\Application->run() at /var/www/vhosts/workflow/project/Doctrine/ORM/Tools/Console/ConsoleRunner.php:39
 Doctrine\ORM\Tools\Console\ConsoleRunner::run() at /var/www/vhosts/workflow/project/doctrine.php:36


orm:validate-schema

I throw an eye to DBAL/Platforms/AbstractPlatform.php abstract class and DBAL/Platforms/PostgresSqlPlatform.php implementation. It appears initializeDoctrineTypeMappings() does not care about user custom types that might be declared.



 Comments   
Comment by Benjamin Eberlei [ 14/Mar/12 ]

Yes, you have to register them manually using AbstractPlatform#registerDoctrineTypeMapping.





[DBAL-194] BlobType should implement getBindingType() Created: 01/Jan/12  Updated: 03/Jan/12  Resolved: 02/Jan/12

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.2-BETA1
Fix Version/s: 2.2-BETA2, 2.2

Type: Bug Priority: Major
Reporter: Nikolai Spassoff Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

PHP 5.3.8
PostgreSQL 9.1


Attachments: Text File fix.patch    

 Description   

BlobType should implement getBindingType() and return PDO::PARAM_LOB

Currently (2.2-BETA1) DBAL uses PDO::PARAM_STR and thus stores the data incorrectly.

Working patch is attached.



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

Fixed





[DBAL-161] Character Set of Database not UTF-8 Created: 07/Sep/11  Updated: 20/Nov/12  Resolved: 05/May/12

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

Type: Bug Priority: Major
Reporter: Hari K T Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

Ubuntu



 Description   

Hi Guys,

I was working with symfony command line and created the database with the app/console doctrine:create:database

Though the characterset I specified was UTF-8 , it created Latin character set .

@elliot was right too, the create database is not using any character set https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/MySqlPlatform.php#L328

You can see the issue created at https://github.com/symfony/symfony/issues/2044 and later Fabien confirmed the bug is related to Doctrine .

I am not sure its a Bug or a feature I am asking .

Thanks



 Comments   
Comment by Benjamin Eberlei [ 05/May/12 ]

Yes these are not connected, however we changed the default collation to UTF-8 for DBAL 2.3

Comment by Ivan Borzenkov [ 20/Nov/12 ]

not fixed or broken now
doctrine:create:database still create database whis latin1 charset

https://github.com/doctrine/DoctrineBundle/issues/49





[DBAL-158] Sqlite Platform doesn't set a proper mapping for "double precision" to act as float. Created: 24/Aug/11  Updated: 30/Oct/11  Resolved: 30/Oct/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.1
Fix Version/s: 2.1.5

Type: Bug Priority: Major
Reporter: Doron Gutman Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 1
Labels: None
Environment:

Ubuntu Linux 11.04
PHP 5.3.5


Attachments: Text File 0001-Fix-for-DBAL-158-where-double-precision-was-not-work.patch    

 Description   

I have a column in an entity, defined as "float" type.
When I run my phpunit tests (which are defined to use sqlite, whereas my production and development environment uses mysql), I get the following exception:

Doctrine\DBAL\DBALException: Unknown database type double precision requested, Doctrine\DBAL\Platforms\SqlitePlatform may not support it

Looking at AbstractPlatform.php, line 1972, function "getFloatDeclarationSQL" returns 'DOUBLE PRECISION'.
Where other platforms have in their "initializeDoctrineTypeMappings" method a mapping such as:

'double precision' => 'float',

, SqlitePlatform.php doesn't have such a mapping.



 Comments   
Comment by Steven Rosato [ 11/Oct/11 ]

I have encountered the same issue while using sqlite. I have made a simple fix in the platform class, as seen in the patch I provided which hopefully fixes the issue.

Comment by Benjamin Eberlei [ 30/Oct/11 ]

Was fixed





[DBAL-151] Github-PR-50 by dteoh: Made custom Oracle NLS_DATE_FORMAT more compatible with Doctrine. Created: 21/Aug/11  Updated: 22/Nov/11  Resolved: 22/Nov/11

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

Type: Improvement Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Won't Fix Votes: 0
Labels: None


 Description   

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

{username}

:

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

Message:



 Comments   
Comment by Christophe Coevoet [ 22/Nov/11 ]

The corresponding PR has been closed





[DBAL-153] Github-PR-48 by phekmat: Added a regression test case for recently fixed PostgreSQLSchemaManager bug Created: 21/Aug/11  Updated: 20/Nov/13  Resolved: 20/Nov/13

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

Type: Improvement Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

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

{username}

:

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

Message:

Regression test for the following change: https://github.com/doctrine/dbal/commit/2434d95aab231273eea8fb555155e9e9c195bcc9



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

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

Comment by Benjamin Eberlei [ 24/Mar/12 ]

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

Comment by Benjamin Eberlei [ 24/Mar/12 ]

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





[DBAL-152] Github-PR-49 by juokaz: Ignore sysdiagrams table from a list Created: 21/Aug/11  Updated: 02/Dec/14  Resolved: 22/Nov/11

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

Type: Improvement Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

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

{username}

:

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

Message:

Fix for the http://www.doctrine-project.org/jira/browse/DBAL-114



 Comments   
Comment by Christophe Coevoet [ 22/Nov/11 ]

This PR has been merged

Comment by Doctrine Bot [ 26/Nov/14 ]

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

Comment by Doctrine Bot [ 02/Dec/14 ]

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





[DBAL-148] Foreign key creation fails with MySQL 5.1.54 Created: 16/Aug/11  Updated: 18/Aug/11  Resolved: 18/Aug/11

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

Type: Bug Priority: Major
Reporter: Frej Connolly Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

MySQL 5.1.54, PHP 5.3.5, Ubuntu 11.04, Zend Framework 1.11.10


Attachments: Text File git_commit_9242fb332baaaeb81be4.txt     File Job.php     Text File orm_schema-tool_create--dump-sql.txt     File Position.php    

 Description   

doctrine orm:schema-tool:create

returns

[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 'Position(id)' at line 1

The problem seem to be that the create commande creates the following SQL syntax
ALTER TABLE Job ADD FOREIGN KEY (position_id) REFERENCES Position(id);

which doesn't work. If I use the following (added a space between Postion and (id) in the end) it works.
ALTER TABLE Job ADD FOREIGN KEY (position_id) REFERENCES Position (id);

Patch for AbstractPlatform.php attached.



 Comments   
Comment by Benjamin Eberlei [ 18/Aug/11 ]

Fixed and merged 2.1.x





[DBAL-146] Mssql platform TOP and DISTINCT ordering issue Created: 10/Aug/11  Updated: 09/Jan/12  Resolved: 09/Jan/12

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.1
Fix Version/s: 2.1.6, 2.2

Type: Bug Priority: Major
Reporter: Karl Southern Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

Windows 2008 R2 SqlSrv 2008 R2 IIS 7.5, fully patched


Attachments: Text File FixDisctinctTopOrderingIssue.patch    

 Description   

When doing a limit and a distinct query, DBAL generates an SQL statement in the form of SELECT TOP X DISTINCT, which SqlSrv does not like at all. Simply moving the the DISTINCT back to the start fixes this issue.

As far as I can see this is caused by the preg_replace in doModifyLimitQuery. Attached is a patch that makes it slightly more aware. There may be other phrases to check for, but none that I've come across yet.

Patch attached.



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

Fixed





[DBAL-147] PostgreSqlSchemaManager fails to set foreign key onDelete/Update option properly if table has 'SET NULL' or 'SET DEFAULT' foreign key constraints Created: 15/Aug/11  Updated: 27/Aug/11  Resolved: 27/Aug/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.1
Fix Version/s: 2.1.2

Type: Bug Priority: Major
Reporter: Payam Hekmat Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 1
Labels: None


 Description   

This table structure:
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY(id));
CREATE TABLE t2 (id INT NOT NULL, fk_id INT DEFAULT NULL);
ALTER TABLE t2 ADD FOREIGN KEY (fk_id) REFERENCES t1(id) ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE;

will cause the listTableForeignKeys to produce incorrect results for the onDelete/Update constraints (the 'DEFAULT' or 'NULL' in 'SET DEFAULT' or 'SET NULL' respectively gets cut off).

This looks to be fixed in the main branch.



 Comments   
Comment by Kenny Millington [ 26/Aug/11 ]

Seems to be fixed in:-

https://github.com/doctrine/dbal/commit/208f995607a544f9606c83d36752a9fd96ed9e64

Would definitely be nice to have that ported to the 2.1.x branch.

Comment by Benjamin Eberlei [ 27/Aug/11 ]

Fixed and merged for 2.1.2





[DBAL-144] Oracle tables without indices are not handled during convert - this behavior should be tolerant since Oracle does not require indicies. Created: 02/Aug/11  Updated: 14/Nov/11  Resolved: 14/Nov/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.1
Fix Version/s: 2.1.5

Type: Bug Priority: Major
Reporter: Ed Anderson Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS 5.0, PHP 5.3.6, Oracle 11g EE, Symfony 2.0 Doctrine 2.1



 Description   

While it is good practice to always have at least one index defined on every table, in some cases (such as temporary tables) indices are not necessary. Oracle does not enforce creating indices for every table, and it is common to create some tables without them. The Table.php (line 556) method throws an exception if an index is not found for a given table. It's obvious there are ramifications for findByPK( ) auto-generated methods - these should be generated for every case where PK exists to accommodate Oracle and tolerate variances from accepted best-practices with most other database platforms.



 Comments   
Comment by Benjamin Eberlei [ 30/Oct/11 ]

When does this error happen?

This method is called through Table::getPrimaryKey(). Does this happen during the "doctrine:schema*" toolchain?

Comment by Ed Anderson [ 30/Oct/11 ]

This happens in the doctrine:schema toolchain.

Comment by Benjamin Eberlei [ 14/Nov/11 ]

Fixed.





[DBAL-142] Mapping Driver for Oracle does not know what to do with blobs, throws DBAL Exception for types "blob" and "long raw". Created: 02/Aug/11  Updated: 13/Mar/12  Resolved: 30/Oct/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.1
Fix Version/s: 2.2

Type: Bug Priority: Major
Reporter: Ed Anderson Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS 5.0, PHP 5.3.6, Oracle 11g EE, Symfony 2.0, Doctrine 2.1


Issue Links:
Duplicate
duplicates DBAL-6 MySQL BLOB datatypes throw DoctrineEx... Resolved

 Description   

When attempting to reverse engineer an existing Oracle 11g database an exception is thrown (by DBAL) complaining that the type blob is unknown. I believe this is coming from AbstractPlatform.php. I attempted to solve the problem by add a type mapping for blob in PDOOracle\Driver.php (blob => text) and it now complains that "long raw" is unknown.

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



 Comments   
Comment by Benjamin Eberlei [ 30/Oct/11 ]

Implemented blob support

Comment by Sergio Andres Diaz Oviedo [ 13/Mar/12 ]

Sorry. i am new in it, i am developing and app in symfony 2, and when i try to import the entities from the database i get the error:

[Doctrine\DBAL\DBALException]
Unknown database type blob requested, Doctrine\DBAL\Platform may not support it.

i am using oracle 11g, i did try (blob => 'text') in oraclepatform.php but is later send me more errors, i cant understand, i want know how can i fix it i anyway to at least can import the entities, thank you





[DBAL-141] PDO Connection Failure through TNS - PDOOracle/Driver.php line 56 Created: 01/Aug/11  Updated: 30/Oct/11  Resolved: 30/Oct/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.1
Fix Version/s: 2.2

Type: Bug Priority: Major
Reporter: Ed Anderson Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS 6.0, Symfony 2.0.0 (RC), PHP 5.3.6, Oracle 11g Enterprise, Oracle InstantClient 11.0.2


Issue Links:
Duplicate
is duplicated by DBAL-136 OCI8 Driver MUST support connections ... Resolved

 Description   

On line 56 of the file ./vendor/doctrine-dbal/lib/Doctrine/DBAL/Driver/PDOOracle/Driver.php:

The line reads:
$dsn .= '))(CONNECT_DATA=(SID=' . $params['dbname'] . ')))'; should read $dsn .= '))(CONNECT_DATA=(SERVICE_NAME=' . $params['dbname'] . ')))';

If both SID and SERVICE_NAME need to be available for use, then logic should be inserted to determine which method is being used.



 Comments   
Comment by Benjamin Eberlei [ 18/Aug/11 ]

Whats the difference? the Use of SID works for me.

Comment by Daniel Lima [ 18/Aug/11 ]

Hi Benjamin,

There are some Oracle connections that are setup from SERVICE_NAME (without a SID).
In my company we always use SERVICE_NAME to setup a connection and we needed to modify the OCI connection driver from Doctrine to works fine in our environment

Take a look in: http://stackoverflow.com/questions/43866/how-sid-is-different-from-service-name-in-oracle-tnsnames-ora

Comment by Ed Anderson [ 19/Aug/11 ]

There's a subtle difference between SID and SERVICE_NAME and I think the code should somehow account for the difference. Here's why... An SID points to a physical instance at the database level (Service Identifier). If you ask for a SID you only get one shot at the connection (and hopefully the database is up). That's the legacy method of connecting to Oracle.

However, for environments that have HA installations of Oracle using RAC, the SERVICE_NAME is what is used to point to a database instance (instead of a physical Service ID). If a given database instance is down and you're using SID - meaning you're looking to connect to a specific database, the connection fails. If you're in an environment with multiple instances of the target database, then RAC figures out where to send you if you're using SERVICE_NAME and you will land on an useable instance. This is the typical way to connect when Oracle is load-balanced and in a high-availability environment.

Comment by Benjamin Eberlei [ 04/Sep/11 ]

How can we solve this issue in a BC way? I wouldn't know if I can just change it the way suggested and it will work for everyone.

Comment by Benjamin Eberlei [ 30/Oct/11 ]

Fixed by adding a new parameter 'service' which has to be true to use SERVICE_NAME instead of SID.





[DBAL-140] Problem with null-length string comparison in Schema Created: 31/Jul/11  Updated: 31/Jul/11  Resolved: 31/Jul/11

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.1
Fix Version/s: 2.1.1

Type: Bug Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

If you compare a column with a string type and length of null to its database value with the default length then Comparator reports a difference. It should automatically convert NULL to 255 (default for all platforms).



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

Fixed.





[DBAL-139] Oracle's sequences with NOCACHE Created: 29/Jul/11  Updated: 22/Dec/13  Resolved: 22/Dec/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.0.6
Fix Version/s: 2.5

Type: Improvement Priority: Major
Reporter: Augusto Ximenes de Souza Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 1
Labels: None


 Description   

Hi community,

Today I tried to generate Oracle's sequences with "NOCACHE", but I checked that method "getCreateSequenceSQL" don't accept. Can we improvement this method to allow? Below an example of the sintaxe:

CREATE SEQUENCE seq_test START WITH 1 INCREMENT BY 1 NOCACHE



 Comments   
Comment by Ivan Andric [ 22/Sep/12 ]

Hi,

If cache value is specified then syntax should contain CACHE keyword that follows the value and if value is not specified then it should go without CACHE keyword.
In this case sequence will be created with CACHE value 20 by default.
Min cache value is 2 and for determine max value we should have sequence max value defined which is by default 10^27 for an ascending sequence and -1 for a descending sequence.
Now the max cache value is calculated by the formula (CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT).

My Idea is to add cache attribute to Sequence object and then under the previous story, set cache to given value if value is greater than 1 and less than calculation by the given formula.

For nocache,
I would use value of 0 so we will have values in domain (0,1<x<=(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)).

Some public function will return NOCACHE or CACHE n and that function could be called in getCreateSequenceSQL method from Sequence input parameter.

Anyone have a better idea or I can code this and request pull from my fork?

Comment by Ramon Henrique Ornelas [ 08/Nov/12 ]

Duplicate issue http://www.doctrine-project.org/jira/browse/DBAL-348

Greetings
Ramon Ornelas





[DBAL-137] Setting empty host in PostgreSQL prevents connection to socket Created: 24/Jul/11  Updated: 24/Jul/11  Resolved: 24/Jul/11

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.1
Fix Version/s: 2.1.1

Type: Bug Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

Reported on https://github.com/symfony/symfony/issues/1788#issuecomment-1640104






[DBAL-138] Connection::quote should convert non PDO types Created: 25/Jul/11  Updated: 31/Jul/11  Resolved: 31/Jul/11

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

Type: Improvement Priority: Major
Reporter: Alexander Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

It currently doesn't so $connection->quote('foo', Type::STRING) doesn't work.



 Comments   
Comment by Alexander [ 25/Jul/11 ]

PR on github for this: https://github.com/doctrine/dbal/pull/39.

Comment by Benjamin Eberlei [ 31/Jul/11 ]

Merged PR into master





[DBAL-136] OCI8 Driver MUST support connections by SERVICE_NAME Created: 19/Jul/11  Updated: 18/Aug/11  Resolved: 18/Aug/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.1
Fix Version/s: 2.1

Type: New Feature Priority: Major
Reporter: Daniel Lima Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates DBAL-141 PDO Connection Failure through TNS - ... Resolved

 Description   

The existing oci8 driver connection only supports connections from database name configuraton.
It will be very good if is possible set a connection by service name.

Take a look in this DSN:

(DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=192.168.0.1) (PORT=1521) ) (CONNECT_DATA= (SERVICE_NAME=TH01) ) )

More info:
http://stackoverflow.com/questions/43866/how-sid-is-different-from-service-name-in-oracle-tnsnames-ora






[DBAL-134] Doctrine\DBAL\Connection::query() no logging Created: 24/Jun/11  Updated: 28/Jun/11  Resolved: 28/Jun/11

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.6
Fix Version/s: 2.0.7, 2.1

Type: Bug Priority: Major
Reporter: Kirill chEbba Chebunin Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

Doctrine\DBAL\Connection::query() does not use SqlLogger.

I guess it may be fixed like this:

    public function query()
    {
        $this->connect();

        $args = func_get_args();

        $logger = $this->getConfiguration()->getSQLLogger();
        if ($logger) {
            $logger->startQuery($args[0]);
        }

        $statement = call_user_func_array(array($this->_conn, 'query'), $args);

        if ($logger) {
            $logger->stopQuery();
        }

        return $statement;
    }


 Comments   
Comment by Benjamin Eberlei [ 28/Jun/11 ]

Fixed





[DBAL-135] Index::overrules() does not detect pks overruling unique indexes Created: 26/Jun/11  Updated: 26/Jun/11  Resolved: 26/Jun/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.6
Fix Version/s: 2.1

Type: Bug Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Comments   
Comment by Benjamin Eberlei [ 26/Jun/11 ]

Fixed in https://github.com/doctrine/dbal/commit/2cb22496a732029373d98ff9e4d54a7187ee9bb8





[ORACLE] Speed improvement of mechanism for limiting query results (DBAL-93)

[DBAL-130] Add tests for modify limit query functionality Created: 17/Jun/11  Updated: 17/Jun/11  Resolved: 17/Jun/11

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

Type: Sub-task Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None





[DBAL-128] Additional types are not registered in the platforms when creating a connection Created: 16/Jun/11  Updated: 16/Jun/11  Resolved: 16/Jun/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.0.5
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Christophe Coevoet Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: None


 Description   

New types have to be registered in the platforms using registerDoctrineMappingType but the driver does not call this for type that are registered using Type::addType
This issue has been reported by some Symfony user here https://github.com/symfony/symfony/issues/1349 but the bug is indeed in DBAL.

Btw, this also affects the master branch but this project does not allow to select it (the ORM project does)



 Comments   
Comment by Benjamin Eberlei [ 16/Jun/11 ]

This is not a "bug". This methods have to called indepdentatly.

Comment by Christophe Coevoet [ 16/Jun/11 ]

When I asked you about this some times ago for the DoctrineBundle integration, you told me that calling Type:addType was enough

Comment by Benjamin Eberlei [ 16/Jun/11 ]

cant remember, i must have been tired sorry





[DBAL-132] wrong mysql dump generation for foreign keys Created: 22/Jun/11  Updated: 31/Jul/11  Resolved: 31/Jul/11

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.5
Fix Version/s: 2.0.7, 2.1.1

Type: Bug Priority: Major
Reporter: Stefan Zerkalica Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 1
Labels: None
Environment:

PHP 5.3.3-7+squeeze1 with Suhosin-Patch, Debian GNU/Linux 6.0.1 (squeeze)



 Description   

Problem in methods Doctrine\DBAL\Platforms\AbstractPlatform::getDropForeignKeySQL() and Doctrine\DBAL\Platforms\AbstractPlatform::getCreateForeignKeySQL()

This methods do not receive FK name and don't put it in sql dump. While executing dump, mysql autogenerate FK names, but at the migration generation moment doctrine knowns nothing about them.

I think, doctrine must generate this names and put it into the schema.

Autogenerated migrations in mysql with fk are unusable.

Doctrine migrations:migrate in current doctrine version:
up:
ALTER TABLE Catalog_Field ADD CONSTRAINT FOREIGN KEY (groupId) REFERENCES Catalog_FieldGroup(id)
down:
ALTER TABLE Catalog_Field DROP FOREIGN KEY

should be:
up:
ALTER TABLE Catalog_Field ADD CONSTRAINT Catalog_Field_ibfk_2 FOREIGN KEY (groupId) REFERENCES Catalog_FieldGroup(id)
down:
ALTER TABLE Catalog_Field DROP FOREIGN KEY catalog_Field_ibfk_2

see: https://github.com/doctrine/migrations/issues/32

https://github.com/doctrine/migrations/issues/35



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

Fixed





[DBAL-126] schema-tool creates primary keys as unique constraints Created: 09/Jun/11  Updated: 26/Jun/11  Resolved: 26/Jun/11

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.5
Fix Version/s: 2.0.7, 2.1

Type: Bug Priority: Major
Reporter: arnaud-lb Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

The schema tool calls AbstractPlatform::getCreateIndexSQL() to create new indexes. When the index is primary, this creates a unique key instead.



 Comments   
Comment by Karsten Dambekalns [ 25/Jun/11 ]

While the description here is very sparse, I think it is about the following problem.

When a primary key is to be created for MySQL, the statement generated is

ADD UNIQUE INDEX PRIMARY ON foo (bar)

This fails with MySQL stating primary is an invalid name. Since "ADD INDEX" is mapped to "ALTER TABLE" anyway, I just made that

ALTER TABLE foo ADD PRIMARY KEY (bar)

which works fine. Also

DROP INDEX primary ON foo

doesn't work, unless I quote primary (since it is a reserved word), but there

ALTER TABLE foo DROP PRIMARY KEY

seems better as well.

Comment by Benjamin Eberlei [ 26/Jun/11 ]

Fixed





[DBAL-119] Outdated Console component Created: 11/May/11  Updated: 14/May/11  Resolved: 14/May/11

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.4
Fix Version/s: 2.0.5

Type: Bug Priority: Major
Reporter: Christophe Coevoet Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

The DBAL submodule uses an outdated version of the Console component (before the switch to private properties). But the Migrations project defines its command using the latest interface which means that it cannot be used with the DBAL out-of-the-box as it calls the getApplication method which does not exist in the old version of the component.



 Comments   
Comment by Benjamin Eberlei [ 14/May/11 ]

Fixed for 2.0.x aswell





[DBAL-122] Impossible to save data to image/binary/varbinary Created: 16/May/11  Updated: 07/Jan/14  Resolved: 06/Jan/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.0.4
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Martin Weise Assignee: Steve Müller
Resolution: Can't Fix Votes: 0
Labels: None
Environment:

XAMP, MsSQL-Server 2008, PHP 5.3.x, MS pdo_sqlsrv_vc6_ts.dll


Issue Links:
Reference
relates to DBAL-711 [GH-450] [DBAL-122] Fix BLOB type map... Resolved
relates to DBAL-714 [GH-452] Introduce BinaryType Resolved

 Description   

When trying to insert a value into a Column with type 'image', 'binary' or 'varbinary' the SQLServer states that this is not possible.
When trying to insert into 'binary' or 'varbinary' the error message is:
=> Implicit conversion from nvarchar(max) datatype to varbinary(max) is not allowed.

When trying to insert into 'image' the error message is:
=> Operand collision: nvarchar(max) is incompatible with image.

Doctrine prepares the image/binary/varbinary column in the statements as nvarchar(max) which is wrong.

The cause of this error is that in the MsSQLPlatform::getVarcharTypeDeclarationSQLSnippet($length, $fixed),
or in the datatype mapping which is to 'text'.

The documentation for the MsSQLServer states following conversions (http://207.46.16.252/de-de/library/ms187928.aspx):

*char => binary/varbinary : Explicit conversion
nchar/nvarchar => image : IMPOSSIBLE

So the solution would be, either to leave the datatype blank or use the char/varchar datatype when saving into image/binary/varbinary, which would cause an extra datatype as those would collide with 'text' I guess.



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

I get the problem, but i don't understand the solutions

Can you explain a bit more?

1. how do i leave a datatype empty? and which one?
2. how do i use char/varchar when saving?

I think this is just a problem of unspecific descriptions

Comment by Martin Weise [ 22/Feb/12 ]

Hi Benjamin

Sorry, for this long delay.
I had a deeper inspection what happens when I persist data into a field of type varbinary(MAX) and I got this (using the SQL profiler):

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,
N'@P1 nvarchar(36),@P2 nvarchar(max),@P3 nvarchar(34)',
N'INSERT INTO mc_dokument_data (id, data, mc_dokument_id) VALUES (@P1, @P2, @P3)',
N'03DDAAD1-4BFA-416B-A0C0-9B8B7148F31C',
N'0x3c3f786d6c20766572736...',
N'mwe3bc2c0da6543d1f48d7c83e64f5c449'
select @p1

But it has to be ( removed '-quotes and @P2 changed to varbinary(MAX) ):

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,
N'@P1 nvarchar(36),@P2 varbinary(max),@P3 nvarchar(34)',
N'INSERT INTO mc_dokument_data (id, data, mc_dokument_id) VALUES (@P1, @P2, @P3)',
N'03DDAAD1-4BFA-416B-A0C0-9B8B7148F31C',
0x3c3f786d6c20766572736...,
N'mwe3bc2c0da6543d1f48d7c83e64f5c449'
select @p1

I am not really sure any more if this is caused from Doctrine or from the 'MS SQL-Server PHP-PDO-Driver 2.0.1' .
But I guess this comes from the driver.
If you can validate this, I will make a bug report to them.

The funny thing is if I do this via executeQuery it works...

$data = unpack("H*" , __some__data__);
$data = '0x'.$data[1];

$em->executeQuery(
    "INSERT INTO mc_dokument_data (id, mc_dokument_id, data)". 
   "VALUES('".$id."' , '" . $documentId . "', " . $data . " )"
);

Hopefully I could clarify my problem.

Regards
Martin Weise

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

Is this now a bug in Doctrine? Can you please confirm this? I was not able to reproduce this issue. The only thing I can see is that back then we did not have a BLOB type in Doctrine that supplies the correct binding type for prepared statements. I don't know HOW you actually inserted the data which lead to an error but I guess it was a binding type problem in the driver coming from a wrong Doctrine type mapping (text instead of blob).
BLOB type mapping for SQL Server platform was introduced in this commit: https://github.com/doctrine/dbal/commit/854a67da503a9fd5a21bece282e3e32581d24d75
What is STILL wrong is the image and binary mapping. I will fix the image and binary type mappings to blob.

Comment by Marco Pivetta [ 19/Dec/13 ]

Steve Müller is this solved with https://github.com/doctrine/dbal/commit/c727c032a876e703ab964848ebf0a1eefed32a9a ?

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

Marco PivettaI don't know really. I cannot reproduce this error and I have to less additional information to do so. Obviously there have been wrong type mappings for the binary database types which al resolved to TextType. I think we have to wait for Feedback from Martin Weisethen.

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

Martin Weise As you did not provide further feedback and we introduced dedicate binary/varbinary Doctrine types that also provide the correct param mapping types and fixed the image/blob type mappings, I consider this ticket as resolved. We also have a lot of tests covering this. If you still encounter this issue, feel free to reopen
Anyways, thanks for reporting this!

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

Lol okay funny thing. I got to reproduce this now but only with PDO_SQLSRV. This is definitely a bug in the driver. See here:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/5a755bdd-41e9-45cb-9166-c9da4475bb94/how-to-set-null-for-varbinarymax-using-bindvalue-using-pdosqlsrv?forum=sqldriverforphp

Benjamin Eberlei What to do? Mention it anywhere in the docs? I don't know if it is fixed in a newer version. I am using version 3.0.3421.0. The native driver does not have this problem.

Comment by Benjamin Eberlei [ 04/Jan/14 ]

Steve Müller There is a known problems sections in the docs, we should mention it there. And then also mention this problem to the SQL Server team I guess.

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

Benjamin Eberlei I am currently fixing the functional test suites for the SQL Server drivers and will add that to the docs. Also I will skip the BlobTests for pdo_sqlsrv then for now. I think (according to the link I provided) the SQL Server team is already aware of that. It seems they haven't released a new version for quite a long time. But I will see if I can ping them again on this.

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

btw: See: http://sqlsrvphp.codeplex.com/SourceControl/latest#pdo_sqlsrv/pdo_stmt.cpp and search for "// TODO: This will eventually be changed to SQLSRV_PHPTYPE_STREAM when output streaming is implemented.". Then you know why varbinary/lob binding does not work.

Comment by Martin Weise [ 06/Jan/14 ]

Hi @all

Sorry for the long delay, but the project I am working on, did not update Doctrine for a long time and I started with a the latest version on another project only a month ago. Therefore I could not provide any further feedback and due the fact, that there were some Xmas things going on...

Anyway, it seems that the reason for this 'bug' is found. Is there anything I can do to help you ?

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

Martin Weise There is nothing you can do about when using pdo_sqlsrv. We added a notice to the docs to stick with sqlsrv if possible instead and skipped the BLOB related tests in the test suite. See: https://github.com/doctrine/dbal/commit/637ef6a1826a6937a5ab4fc4c73b8ede46732a73

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

I will close this for now as we cannot fix this driver bug. This might be reopended as soon as Microsoft fixes the bug in a newer version and we maybe can handle it properly.

Comment by Martin Weise [ 07/Jan/14 ]

Ok... Thank you for your help. Hopefully MS will fix this...





[DBAL-120] MySql platform getAlterTableSQL does not quote existing column names Created: 13/May/11  Updated: 19/Jun/11  Resolved: 19/Jun/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.0.4
Fix Version/s: 2.0.4

Type: Bug Priority: Major
Reporter: Devon Weller Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 0
Labels: None

Attachments: Text File quote-mysql-change-colname.patch     Text File quote-mysql-change-keyname.patch    
Issue Links:
Duplicate
duplicates DBAL-96 Make approach towards identifier quot... Open

 Description   

When creating alter table SQL, the MySqlPlatform class does not quote names for columns of an existing table when building a CHANGE query. This can cause SQL errors.

It creates queries like this:

ALTER TABLE my_table CHANGE key `key` INTVARCHAR(255) DEFAULT '' NOT NULL';

Attached is a patch to fix this.

As an aside, is this the best way to contribute a patch? Or is a GitHub pull request better?



 Comments   
Comment by Devon Weller [ 13/May/11 ]

I also discovered a similar issue with key names. A second patch is attached.

Comment by Benjamin Eberlei [ 19/Jun/11 ]

This problem is a little more complicated, it will be fixed in DBAL-96 probably for 2.2





[DBAL-118] When speaking about security do not rely on default link in mysql_* function calls Created: 11/May/11  Updated: 14/May/11  Resolved: 14/May/11

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

Type: Documentation Priority: Major
Reporter: Ulf Wendel Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

The documentation about escaping reads:

"Consider the previous query, now parameterized to fetch only a single article by id. Using ext/mysql (still the primary choice of MySQL access for many developers) you had to escape every value passed into the query using mysql_real_escape_string() to avoid SQL injection:

<?php
$sql = "SELECT * FROM articles WHERE id = '" . mysql_real_escape_string($id) . "'";
$rs = mysql_query($sql);",
http://www.doctrine-project.org/docs/dbal/2.0/en/reference/data-retrieval-and-manipulation.html

Please, do not rely on MySQL default links when discussing security issues. One of major differences between the mysql and the later mysqli extension is that mysqli forces users to explicitly specify a connection handle. There is no concept of default links and magical global connection handles in mysqli any more. The convenience of not having to specify a connection handle has been removed from mysqli. This was done to increase security, for example, when escaping strings. Escaping needs to take the current charset of the connection into account. Thus, it is recommended to explicitly specify the connection and so not use default connection.

"string mysql_real_escape_string ( string $unescaped_string [, resource $link_identifier ] )",
http://de.php.net/mysql_real_escape_string

Please, change the example:

<?php
$sql = "SELECT * FROM articles WHERE id = '" . mysql_real_escape_string($id, $link) . "'";
$rs = mysql_query($sql);",

($link added)

Thanks!



 Comments   
Comment by Benjamin Eberlei [ 14/May/11 ]

Changed, the new docs will be rolled up sometime this weekend.





[DBAL-115] SchemaManager doesn't quote table names for MySQL Created: 28/Apr/11  Updated: 19/Jun/11  Resolved: 19/Jun/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.4
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Zoran Zaric Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 1
Labels: None
Environment:

Linux Kernel 2.6.38
MySQL 5.5.11
PHP 5.3.6



 Description   

Using a reserved word as a table name only works for schema creation not dropping.

Example Enity:
/**

  • @Entity
  • @Table(name="`call`")
    */

class Call {
/**

  • @Column(name="id", type="integer", nullable=false)
  • @Id
  • @GeneratedValue(strategy="IDENTITY")
  • @var integer $id
    */
    private $id;
    }

The table name is quoted. creation works, dropping doesn't even after changing the table name to a not reserved word.



 Comments   
Comment by Benjamin Eberlei [ 19/Jun/11 ]

This problem is a little more complicated, it will be fixed in DBAL-96 probably for 2.2





[DBAL-112] Sequence alteration does not alter. Created: 26/Apr/11  Updated: 19/Jun/11  Resolved: 19/Jun/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.4
Fix Version/s: 2.1

Type: Bug Priority: Major
Reporter: Thomas Lundquist Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux & Postgesql



 Description   

When the schema diff decides there are differences between the existing SEQUENCE and the "new" one it alters the SEQUENCE by a DROP and then a CREATE.

The CREATE is done with the fromSchema as a base which results in a no-changed SEQUENCE.

Line 102 in Doctrine/DBAL/Schema/Comparator.php uses fromSchema but shuld have been using toSchema or just $sequence

This will fix the immediate issue but still require the sequence name being the same.



 Comments   
Comment by Benjamin Eberlei [ 19/Jun/11 ]

Fixed





[DBAL-110] No way to get platform specific values from old schema to schema create sql Created: 13/Apr/11  Updated: 13/Apr/11  Resolved: 13/Apr/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.4
Fix Version/s: None

Type: Improvement Priority: Major
Reporter: Peter Jasiulewicz Assignee: Benjamin Eberlei
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

ubuntu 10.10 with php 5.3.3



 Description   

Even if I create custom types to support non-default types in Mysql (enum,set) I cant get the values from the schema manager to the type object due to encapsulation:

type declaration
<?php
use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Platforms\AbstractPlatform;

class EnumType extends Type
{
const MYTYPE = 'Enum'; /
public function getSqlDeclaration(array $fieldDeclaration, AbstractPlatform $platform)

{ return "ENUM(HERE IS THE PROBLEM)"; }

public function getName()

{ return self::MYTYPE; }

}
?>

db connection construction:
<?php
//...

\Doctrine\DBAL\Types\Type::addType('enum', 'CustomTypes\EnumType');
$conn->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'enum');

?>

There is no way to create even an extension to better support mysql databases, and since its a pretty commonly used rdbms this issue lowers the value of Doctrine2 for mysql (since you get errors by going by the reference guide).

related issues:
http://www.doctrine-project.org/jira/browse/DBAL-4
http://www.doctrine-project.org/jira/browse/DBAL-89



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

MySQL is a common database yes, people tend to use enums yes, but enums suck! They have tons of disadvantages that far outweight the simple benefit.

There is just no way to create a generic Enum type and never will be, you can only create one enum type per class of values as described here:

http://www.doctrine-project.org/docs/orm/2.0/en/cookbook/mysql-enums.html

Comment by Peter Jasiulewicz [ 13/Apr/11 ]

Yes I understad that ENUMs aren't the best possible but since enums are internally represented as numbers not strings "Solution 1: Mapping to Varchars" makes it even worse and can result in making a database huge and slow, and botch solutions with "Solution 2: Defining a Type" pretty much renders DBAL schema tools and Migrations useless (which is the main part I wanted to use)

I'm not saying that you should follow bad practices but if Doctrine wats to be a mature tool it should be also be highly extensible. Currently it just lacks the capability to truly abstract the thing it was written to be an abstraction layer of.

cheers,
Peter

Comment by Benjamin Eberlei [ 13/Apr/11 ]

Types are flyweight instances, this makes enum support impossible. So the choice for us was, have a highly customizable type system that doesnt support enums, or throw performance to hell. We picked one.

DBAL and Migrations firstly serve the ORM, then ship with their own features, SchemaTool was never designed to support every legacy application out there.

Btw: Mapping to varchars does not make the the database huge and slow, using columnDefinition this would still be a real ENUM. Just doctrine thinks its a string (aka varchar).

Comment by Peter Jasiulewicz [ 13/Apr/11 ]

Thanks for expalining, still I wanted to use it mainly as a schema creation/comparator tool so this does'nt help much. Will migrate to doctrine when the project has matured.

Cheers,
Peter





[DBAL-121] Mapping, decimal returns float. Created: 12/Apr/11  Updated: 14/May/11  Resolved: 14/May/11

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.3
Fix Version/s: 2.0.5

Type: Bug Priority: Major
Reporter: Tom Van Looy Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux Ubuntu 10.10 x86_64, PHP 5.3.3-1ubuntu9.3, mysql Ver 14.14 Distrib 5.1.49



 Description   

I have a Doctrine 2 entity and the value is mapped like this (with regular getter/setter):

 
/**
 * @Column(type="decimal", precision=40, scale=30)
 */
protected $someValue;

/**
 * @return decimal
 */
public function getSomeValue()
{
    return $this->someValue;
}

/**
 * @param decimal $someValue
 */
public function setSomeValue($someValue)
{
    $this->someValue = $someValue;
}

When I set that value from my code, the value gets written into the database correctly. But, and that is my problem, when I get the value (via getter or \Doctrine\Common\Util\Debug::dump()), it always gives me a number with maximum 14 digits, and it rounds the value. I read the record with the default findById().

eg: with value 1234567890.012345678901234567890123456789 I have 1234567890.0123
eg: with value 890.0123456789012345678901234567890123456 I have 890.01234567890

I of course want all digits, not just 14. The field in MySQL is declared like this:

 
    someValue decimal(40,30) NOT NULL,

When I get the value with raw PHP and mysql_query(), it returns correctly.

It seems like the problem is that Doctrine returns a float: ["someValue":protected]=> float(234567890.01235)

What's wrong, what should I check next, how to fix, any clues?
(also posted on http://stackoverflow.com/questions/5635500/doctrine-2-decimal-can-only-contain-14-digits)



 Comments   
Comment by Benjamin Eberlei [ 12/Apr/11 ]

ah yes, this is a bug.

Comment by Benjamin Eberlei [ 14/May/11 ]

fixed





[DBAL-109] Doctrine/DBAL/Platforms/MySqlPlatform.php is missing test coverage around _getCreateTableSQL method Created: 12/Apr/11  Updated: 28/Dec/13  Resolved: 28/Dec/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.1
Fix Version/s: 2.1

Type: Improvement Priority: Major
Reporter: Wil Moore III Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

2.1.0-DEV



 Description   

In "Doctrine/DBAL/Platforms/MySqlPlatform.php", the following methods are public: getShowDatabasesSQL, getCreateDatabaseSQL, getDropDatabaseSQL, getDropTableSQL (easy to test); however, the "_getCreateTableSQL" method is not public.

Is there a specific reason for this difference?

As a result of this method not being tested, there is a "quote" method being called which doesn't exist.

See: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/MySqlPlatform.php#L410

The only way at this time to test for this case is to get an error when doing: $table->addOption('comment', '...');

I would suggest making the protected method public so it can be tested. I also suggest moving the options parsing out of _getCreateTableSQL into a helper method...this method seems a bit overloaded.

If agreed, I'm happy to take care of the cleanup. Just wanted to get some feedback on the ideas first.



 Comments   
Comment by Benjamin Eberlei [ 18/Nov/11 ]

This is an improvement

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

Is this issue still an issue? I neither see a "quote" method nor a "$table->addOption()" here...

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

Undefined method call fixed in commit: https://github.com/doctrine/dbal/commit/79e04e98895648e2a72075377dac13a3e7c7fd53





[DBAL-107] Graphviz Schema Visitor Created: 07/Apr/11  Updated: 07/Apr/11  Resolved: 07/Apr/11

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

Type: New Feature Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

We should include a Graphviz Schema Visitor.

Features:

  • Display Tables and Columns
  • Highlight Primary Keys
  • Connect tables through foreign keys


 Comments   
Comment by Benjamin Eberlei [ 07/Apr/11 ]

Implemented





[DBAL-106] Doctrine\DBAL\Schema\Comparator false positives Created: 29/Mar/11  Updated: 06/Apr/11  Resolved: 06/Apr/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.2
Fix Version/s: 2.0.4

Type: Bug Priority: Major
Reporter: Lee Feistel Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 1
Labels: None
Environment:

Doctrine 2.0.3, PHP 5.3.5, MySQL 5.5.9



 Description   

I am using the model class annotations for my Doctrine schema definition and a MySQL database for my target. When testing code that uses Doctrine\DBAL\Schema\Comparator, either by using $schemaTool->updateSchema($classes) which is defined in the core functionality or by running doctrine migrations:diff from the Doctrine Migrations project, a set of bugs are revealed. The symptoms of this are that immediately after calling $schemaTool->createSchema($classes) any call that uses the Comparator class to calculate the diffs between the real database and the schema definition produce a large set of false positives. Most of these false positives can be traced back to the logic in Doctrine\DBAL\Schema\Comparator::diffColumn(Column $column1, Column $column2). There are two issues here I can see right away:

One is that if the model annotations rely on defaults rather than explicitly defining certain attributes, this function sees them as different. For example, if I have a column defined like this: @Column(type="decimal", scale=2, nullable=true) then the Comparator believes it is different from the real database because precision is 10 in the real data and 0 in my schema. If I change the line to this: if (($column1->getPrecision() ?: 10) != ($column2->getPrecision() ?: 10)) then it works, but I am sure a real solution needs to determine what the defaults actually are.

The second issue is when I use the 'columnDefinition' attribute with a custom type to declare an enum or set, for instance. Since this function does not actually check what the columnDefinition field is set to at all, it has no way to know if I have actually made a change to the list of values, or anything else about that columnDefinition. I have been reading that using the comments field might be another way to do this, but I am not yet clear on what the best practice really is here. I know that http://www.doctrine-project.org/jira/browse/DBAL-42 touches on this with regard to storing Array type metadata in the comments field. I need more direction on how this should work in custom field types.

In addition, I am getting this strange set of diffs on the index for a ManyToMany association's join table:

        $this->addSql("CREATE INDEX IDX_44B8C6D89AF7860 ON firm_firm (firm_id)");
        $this->addSql("DROP INDEX primary ON firm_firm");
        $this->addSql("CREATE UNIQUE INDEX primary ON firm_firm (firm_id, firm_id)");

I am not sure why this only happens on this one table and not others, but it seems to be another case of the Comparator getting a false positive for some reason this time in Comparator::diffIndex(Index $index1, Index $index2). The model annotations for this association are:

  /**
   * @var Firm
   * @ManyToMany(targetEntity="Firm")
   */
  protected $Children = array();

I will add more to this as I learn more details about the issues, but hopefully this is enough to start grokking the issues in Comparator. Getting the Comparator class in the core Doctrine\DBAL library working properly for calculating diffs will go a long way toward making well defined migrations a reality for Doctrine2 projects running in production. Hopefully, someone will also be able to address this minor issue in the Doctrine Migrations (http://www.doctrine-project.org/jira/browse/DMIG-21) so that it can be deployed without needing a patch.

Thanks to everyone for Doctrine2, and I am looking forward to being among the first to put this new library into production on a real world project in a few months.



 Comments   
Comment by Benjamin Eberlei [ 06/Apr/11 ]

Can you tell me how the indexes on the join table look like?

Comment by Lee Feistel [ 06/Apr/11 ]

Now that I look at this schema, I think I need to apologize for my oversight because I didn't explain the problem properly. The real issue is that this self-referential ManyToMany association needs to have the id fields explicitly defined or it will not work. Doctrine2 is generating this table by apparently assuming both id fields have the same name, and so the second index that is created gets a randomly generated name assigned to it. There is no way this association is actually working properly at all, so I'll talk to the programmer that wrote this. I think we can fix this part of the problem ourselves, but perhaps some error checking here would be helpful. The first half of the problem (in diffColumn) remains. Thanks again for looking into this.

CREATE TABLE IF NOT EXISTS `firm_firm` (
`firm_id` int(11) NOT NULL,
PRIMARY KEY (`firm_id`),
KEY `IDX_44B8C6D89AF7860` (`firm_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `firm_firm`
ADD CONSTRAINT `firm_firm_ibfk_1` FOREIGN KEY (`firm_id`) REFERENCES `firm` (`id`) ON DELETE CASCADE;

Comment by Benjamin Eberlei [ 06/Apr/11 ]

Fixed the precision issue, please open a new ticket for the many to many.

Comment by Lee Feistel [ 06/Apr/11 ]

Thanks. At least the precision issue was easy, so glad to have that one out of the way. Is there any hope for a way to handle the use of columnDefiniton? I guess the alter statements being generated are just changing it to the same value, so it might not actually be broken in and of itself. Those statements could take a small amount of time to execute what is essentially a noop. Maybe we just have to manually go through and clean out the unwanted alter statements for the time being. It might be workable.

Comment by Benjamin Eberlei [ 06/Apr/11 ]

Updated version

Comment by Benjamin Eberlei [ 06/Apr/11 ]

columnDefinition always comes up in the diff. There is just no way around it. The problem is that there is no generic way to create a "columnDefinition" from the existing databse, so no good value to compare it too. If you can think of a solution for this problem i would be very happy, but we havent come up with one yet.





[DBAL-105] Schema Comparator produces invalid SQL Created: 29/Mar/11  Updated: 06/Apr/11  Resolved: 06/Apr/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.2
Fix Version/s: 2.0.4

Type: Bug Priority: Major
Reporter: Johannes Schmitt Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 1
Labels: None
Environment:

Windows7, PHP 5.3.3



 Description   

Before:

/**
 * @orm:Entity
 * @orm:Table(name="twitter_users")
 */
class TwitterUser implements UserInterface
{
    const ROLE_DEFAULT = 'ROLE_TWITTER_USER';

    /**
     * @orm:Id
     * @orm:GeneratedValue(strategy="AUTO")
     * @orm:Column(type="integer")
     */
    private $id;

    /**
     * @orm:Column(type="integer", unique=true, nullable=false)
     */
    private $twitterId;

    /**
     * @orm:Column(type="string", unique=true, nullable=false)
     */
    private $displayName;
}

After:

/**
 * @orm:Entity
 * @orm:Table(name="twitter_users")
 */
class TwitterUser implements UserInterface
{
    const ROLE_DEFAULT = 'ROLE_TWITTER_USER';

    /**
     * @orm:Id
     * @orm:GeneratedValue(strategy="AUTO")
     * @orm:Column(type="integer")
     */
    private $id;

    /**
     * @orm:Column(type="integer", unique=true, nullable=false, name="twitter_id")
     */
    private $twitterId;

    /**
     * @orm:Column(type="string", unique=true, nullable=false, name="display_name")
     */
    private $displayName;

    /**
     * @orm:Column(type="datetime", name="last_logged_in_at", nullable=true)
     */
    private $lastLoggedInAt;
}

Bugs in the generated SQL:

  • last_logged_in_at column is not added
  • displayName is dropped, and at the same time the column name "displayName" is changed to "display_name"
  • twitterId same as above


 Comments   
Comment by Benjamin Eberlei [ 06/Apr/11 ]

Fixed





[DBAL-101] Undefined index "length" in SqliteSchemaManager Created: 17/Mar/11  Updated: 06/Apr/11  Resolved: 06/Apr/11

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.1
Fix Version/s: 2.0.4

Type: Bug Priority: Major
Reporter: Karsten Dambekalns Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None

Attachments: Text File 0002-BUGFIX-Fix-undefined-index-error-on-SqliteSchemaMana.patch    

 Description   

If not giving length for numeric columns when using SQLite, I got an undefined index error around line 155 in SqliteSchemaManager. I fixed it with this:

             case 'numeric':
-                list($precision, $scale) = array_map('trim', explode(', ', $tableColumn['length']));
+                if (array_key_exists('length', $tableColumn)) {
+                    list($precision, $scale) = array_map('trim', explode(', ', $tableColumn['length']));
+                } else {
+                    $precision = $scale = null;
+                }
                 $length = null;
                 break;
 

Git patch attached.



 Comments   
Comment by Benjamin Eberlei [ 06/Apr/11 ]

Fixed





[DBAL-100] Add Drizzle Support Created: 16/Mar/11  Updated: 21/Nov/13  Resolved: 21/Nov/13

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

Type: New Feature Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 1
Labels: None


 Description   

Drizzle is out, we should add support for the Dialect.

http://docs.drizzle.org/mysql_differences.html



 Comments   
Comment by Andreas Streichardt [ 22/Dec/11 ]

i have created some hackish fork and the whole testsuite is working already:

https://github.com/m0ppers/dbal

Still WIP but may be a start. I think the C extension is not really ready yet either. When i find time i will most likely have a look at it.

Comment by Benjamin Eberlei [ 22/Dec/11 ]

Can you branch it into something, like git checkout -bDrizzle then push it to your repo and open a Pull Request? Thats way easier to review and discuss.

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

Isn't this already implemented?

Comment by Kim Hemsø [ 02/Aug/13 ]

Yes it is, long time ago. Well.. difference is that Andreas here is using the now (dead?) native drizzle ext. Where dbal is using pdo for mysql.





[DBAL-102] SQLSRV PDO doesn't support DateTime as a PHP Object Created: 19/Mar/11  Updated: 15/Mar/13  Resolved: 14/Sep/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.1
Fix Version/s: 2.1.3

Type: Bug Priority: Major
Reporter: Aaron DM Assignee: Guilherme Blanco
Resolution: Fixed Votes: 0
Labels: None
Environment:

Windows 7, Microsoft SQL Server 2008, PHP SqlSrv Driver 2.0, PHP 5.3.6



 Description   

I have created an entity with has a datetime column

Entity.php
    /**
     * @orm:Column(name="deleteAt", type="datetime")
     */
    protected $deletedAt;

    /**
     * Get DeletedAt
     * @return	\DateTime	
     */
    public function getDeletedAt() {
    	return $this->deletedAt;
    }
    
    /**
     * Set DeletedAt
     * @param	\DateTime		$deletedAt
     */
    public function setDeletedAt(\DateTime $deletedAt) {
        $this->deletedAt = $deletedAt;
    }

This should work, however I get this error when trying to set a "deleteAt" date

$this->setDeletedAt(new \DateTime());
SQLSTATE[22007]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string.

This should work afaik.



 Comments   
Comment by Benjamin Eberlei [ 21/Mar/11 ]

Are yo using columns of type "datetime" or "datetime2" in MSSQL? The former is not supported.

Comment by Aaron DM [ 21/Mar/11 ]

I did not know datetime was not supported. I am using the column type "datetime"

Comment by Benjamin Eberlei [ 21/Mar/11 ]

In this case you have to add your own datetime type and change the timestamp format, or use the VarDateTime instead by calling "overrideType". Have a look at the Doctrine\DBAL\Types folder, escpecially the Type.php, DateTimeType.php and VarDateTimeType.php

Comment by Aaron DM [ 29/Mar/11 ]

I have now tried this with the column type "datetime2", which creates the following date format in the database
"2011-03-27 06:07:00.000000"

And I am still getting the same error
"SQLSTATE[22007]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string."

P.S. this time I tried doing a select
WHERE deletedAt > (datetime)

Comment by Benjamin Eberlei [ 29/Mar/11 ]

Assigned to juokaz

Comment by Aaron DM [ 29/Mar/11 ]

When i ran profile, this is what I see

" AND (a0_.deleted_at <= @P3) ORDER BY a0_.deleted_at DESC','810678','Object','Object'"

Expliclitly converting the DateTiem to a string works

$query->setParameter("deletedAt", $deletedAt->format("Y-m-d H:i:s.u"));

Comment by Juozas Kaziukenas [ 29/Mar/11 ]

Can you give a full code which you use to query this (or the smallest allowing to replicate). I can't see how DateTime object gets passed as a 'Object' to the query, this is handled by DBAL type system, not by mssql platform, which only specifies the date and/or time format.

Comment by Benjamin Eberlei [ 30/Mar/11 ]

Does the error happen during a DQL or during flush (UPDATE/INSERT) ?

Comment by Aaron DM [ 30/Mar/11 ]

https://gist.github.com/6b2a8b53ece6e75abf20

The code there works (its not fully complete, but I tested the statements with the same Entity and etc and I am unable to successfully query the database).

Looks like it doesn't work even for a simple select. However if I do retrieve an entry, the getCreatedAt() does return a DateTime object correctly.

Comment by Guilherme Blanco [ 13/Sep/11 ]

Is this issue still valid with new implementation of ParameterTypeInferer in ORM?

It seems it solved this issue.

Comment by Guilherme Blanco [ 14/Sep/11 ]

Closing since no response was given.

Comment by Michał Banaś [ 15/Mar/13 ]

Why is it close ? After one day of waiting for response?
Problem is not solved. It exist on ether datetime and datetime2 TSQL types.
And it is easy to check if you have sqlserver databes.
PLease repoen this issue.

Comment by Marco Pivetta [ 15/Mar/13 ]

Michał Banaś this actually works with the current implementation of the schema tools. Consider implementing additional formats a custom DBAL type instead.





[DBAL-103] Quoting is not supported in pdo_odbc driver Created: 22/Mar/11  Updated: 19/Jun/11  Resolved: 19/Jun/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.0.3
Fix Version/s: None

Type: Bug Priority: Major
Reporter: John Arevalo Assignee: Benjamin Eberlei
Resolution: Can't Fix Votes: 0
Labels: None
Environment:

System Windows NT 5.1 build 2600 (Windows XP Professional Service Pack 3) i586
Compiler MSVC6 (Visual C++ 6.0)
Apache Version Apache/2.2.17 (Win32) PHP/5.3.5



 Description   

trying to execute this dql:

SELECT p FROM my\namespace\entities\PQR p WHERE p.razon = 'TEL'

it built this native sql (iseries)

SELECT t0_.SGNUMÑ AS SGNUMÑ0, t0_.SGCAUS AS SGCAUS1, t0_.SGREAS AS SGREAS2 FROM SGMASTL7 t0_ WHERE t0_.SGCAUS =

because pdo is created with odbc[1], quote method[2] is not supported, and Doctrine/DBAL/Connection.php#quote()[3] method returns an empty value for <'TEL'> expression, so Database throws a Syntax error. It should be support for drivers which doesn't have implemented quote() method.

[1] with odbc: $pdo = new PDO("odbc:MY_ODBC_NAME", "USER", "PASSWORD");
[2] http://php.net/manual/en/pdo.quote.php
[3] https://github.com/doctrine/dbal/blob/2.0.3/lib/Doctrine/DBAL/Connection.php#L520



 Comments   
Comment by Benjamin Eberlei [ 23/Mar/11 ]

As a first trick you can use the wrapperClass parameter to the DriverManager factory and create your "MyConnection extends \Doctrine\DBAL\Connection" to solve this issue.

Comment by Benjamin Eberlei [ 19/Jun/11 ]

Closed, odbc is not a supported driver.





[DBAL-99] Proposal for better params-binding Created: 05/Mar/11  Updated: 07/Mar/11  Resolved: 05/Mar/11

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

Type: Improvement Priority: Major
Reporter: Konstantin Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: None


 Description   

Currently we have something like

    $count = $conn->executeUpdate('UPDATE user SET username = ? WHERE id = ?', array('jwage', 1));

I propose add prefixes to placeholders: i (toInt), f (toFloat), e (escape), q (quote), c (qouteIdent), j (join with comma).
eq - escape and quote. Arrays also can be escaped/quoted/converted to int (each element of array processed).

Examples:

    $count = $conn->executeUpdate('UPDATE user SET username = eq:0 WHERE id = i:1', array('jwage', 1)); // 0, 1 - keys of binded data
    $users = $conn->fetchAll('SELECT cj:cols FROM user WHERE id IN (ij:ids)', array('cols' => array('column1', 'column2'), 'ids' => array(1, 5, 9)));


 Comments   
Comment by Benjamin Eberlei [ 05/Mar/11 ]

There is a third parameters types where you can pass this into.

Comment by Konstantin [ 05/Mar/11 ]

Yep, but:
1) not all methods support this. fetchColumn has 3rd arg wich mean what column to fetch
2) in my opinion more usable write `i` than `\PDO::PARAM_INT`

Comment by Benjamin Eberlei [ 07/Mar/11 ]

Yes, but then we would have to implement a really powerful SQL parser. This costs losts of time. Additionally its weird for everybody using SQL, its just nothing that existed before.

Comment by Konstantin [ 07/Mar/11 ]

I'm using something like this http://pastie.org/1642551





[DBAL-98] BUG in Table.php or AbstractAsset Created: 02/Mar/11  Updated: 04/Mar/11  Resolved: 04/Mar/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Michał Strzelecki Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 0
Labels: None
Environment:

OS: Ubuntu 10.10
PHP: 5.3.3
WEB: NGINX



 Description   

In Class Table in method _addForeignKeyConstraint is written

// add an explicit index on the foreign key columns. If there is already an index that fullfils this requirements drop the request.
// In the case of __construct calling this method during hydration from schema-details all the explicitly added indexes
// lead to duplicates. This creates compuation overhead in this case, however no duplicate indexes are ever added (based on columns).

It is false, because duplicate indexes are added (based on columns) the cause of that is strange _generateIdentifierName method in AbstractAsset class.

Algorith for genereting indexes base on substring is causing that

for example
tablename: communications.out_emails
columnname: internal_contact_channel_id

generates: communications_out_emails_rnal_email_contact_channel_id_idx

and

tablename: communications.out_emails
columnname: external_contact_channel_id

generates: communications_out_emails_rnal_email_contact_channel_id_idx

Suprise!!!?? It's the same.

I think the better way was to use md5 or sha (I'm ceeding).



 Comments   
Comment by Benjamin Eberlei [ 04/Mar/11 ]

This issue was fixed already (for other reasons):

https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/AbstractAsset.php#L138

Fix will be in 2.0.2





[DBAL-92] Timezones of DateTime instances are ignored when persisting dates Created: 16/Feb/11  Updated: 26/Feb/11  Resolved: 26/Feb/11

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

Type: Bug Priority: Major
Reporter: Bernhard Schussek Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: None


 Description   

When a DateTime instance, e.g. "2011-02-16 00:00:00 America/New_York" is written into the DB, the timezone is ignored and only "2011-02-16" is persisted. When fetching the date, it is written into a DateTime with the server's timezone, resulting in for example "2011-02-16 00:00:00 Europe/Berlin" which is not correct!

To fix this issue, Doctrine should convert dates to the server's timezone (if their own timezone differs) before persisting them or before executing queries containing DateTime instances.



 Comments   
Comment by Benjamin Eberlei [ 26/Feb/11 ]

This would magically change the state of any DateTime instance upon "convertToDatabaseValue", i think this would be equally weird so better no changes at all.





[DBAL-85] Illegal quotes in SHOW FULL TABLES WHERE Table_type = "BASE TABLE" Created: 04/Feb/11  Updated: 11/Feb/11  Resolved: 11/Feb/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: 2.0.2

Type: Bug Priority: Major
Reporter: Karsten Dambekalns Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None

Attachments: Text File base_table.patch    
Issue Links:
Duplicate
is duplicated by DDC-1019 Double quotation mark mysql query err... Resolved

 Description   

When trying to generate update SQL on MySQL running in ANSI SQL mode you get the error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'BASE TABLE' in 'where clause'

This is caused by the use of identifier quotes (") instead of literal quotes (') in the query
SHOW FULL TABLES WHERE Table_type = "BASE TABLE"

This is in MySqlPlatform#getListTablesSQL()



 Comments   
Comment by Bernhard Schlas [ 07/Feb/11 ]

Here is a temporary dirty patch for this problem. ONLY FOR TESTING PURPOSE!!!

Comment by Karsten Dambekalns [ 08/Feb/11 ]

Why not simply change MySqlPlatform#getListTablesSQL()?

Comment by Bernhard Schlas [ 08/Feb/11 ]

Because did this quick and dirty patch before i read this issue. Actually i've attached a better one,

Comment by Karsten Dambekalns [ 09/Feb/11 ]

With this change it works for me.

Comment by Benjamin Eberlei [ 11/Feb/11 ]

Fixed.





[DBAL-89] MySqlPlatform does not handle enum and set data types Created: 10/Feb/11  Updated: 14/Jan/15  Resolved: 10/Feb/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.0
Fix Version/s: None

Type: Bug Priority: Major
Reporter: James Reed Assignee: Benjamin Eberlei
Resolution: Won't Fix Votes: 0
Labels: None

Attachments: File MySqlPlatform.php    
Issue Links:
Reference
relates to DBAL-4 missing column type "enum" Resolved
is referenced by DBAL-1116 [GH-774] Added SET and ENUM types for... Resolved

 Description   

If you do a reverse engineer of an existing database that includes enum and set columns the reverse engineer will fail. Ideally there could be a separate Doctrine type for these fields, but at a minimum it seems they should be treated as string values. I solved my reverse engineering problem by modifying the initializeDoctrineTypeMappings in the MySqlPlatform.php file (attached):

protected function initializeDoctrineTypeMappings()

{ $this->doctrineTypeMapping = array( 'tinyint' => 'boolean', 'smallint' => 'smallint', 'mediumint' => 'integer', 'int' => 'integer', 'integer' => 'integer', 'bigint' => 'bigint', 'tinytext' => 'text', 'mediumtext' => 'text', 'longtext' => 'text', 'text' => 'text', 'varchar' => 'string', 'string' => 'string', 'char' => 'string', 'date' => 'date', 'datetime' => 'datetime', 'timestamp' => 'datetime', 'time' => 'time', 'float' => 'float', 'double' => 'float', 'real' => 'float', 'decimal' => 'decimal', 'numeric' => 'decimal', 'year' => 'date', 'enum' => 'string', 'set' => 'string', ); }

 Comments   
Comment by Benjamin Eberlei [ 10/Feb/11 ]

There is a method on the platform "regsiterDoctrineMappingType" that modifies this array.

Enums and Sets cannot be generically supported by architectural choice.

Comment by James Reed [ 10/Feb/11 ]

Ah, didn't know about the regsiterDoctrineMappingType. Good solution. Thanks!

Comment by James Reed [ 10/Feb/11 ]

Actually, after searching the code the method is "registerDoctrineTypeMapping"





[DBAL-88] MySqlPlatform not escaping table names Created: 10/Feb/11  Updated: 26/Feb/11  Resolved: 26/Feb/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.0
Fix Version/s: 2.0.2, 2.1

Type: Bug Priority: Major
Reporter: James Reed Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None

Attachments: File MySqlPlatform.php    
Issue Links:
Duplicate
duplicates DBAL-91 orm:convert-mapping SchemaException o... Resolved

 Description   

Methods in the MySql Platform that use the table name are not escaping the table name. This causes problems when your table name is the same as a reserved word in MySql. Example:

public function getListTableConstraintsSQL($table)

{ return 'SHOW INDEX FROM ' . $table; }

If my table name is User this method will fail and cause a SQL error because the word User is a reserved word in MySql. You need to escape the table name with backticks to prevent these errors. Example:

public function getListTableConstraintsSQL($table)

{ return 'SHOW INDEX FROM `' . $table . '`'; }

I encountered this problem while reverse engineering my existing MySql database. I've attached a fixed version that solved my reverse engineering problems.



 Comments   
Comment by Benjamin Eberlei [ 11/Feb/11 ]

Duplicate of DDC-976

Comment by Benjamin Eberlei [ 12/Feb/11 ]

This issue is not a duplicate, only related to DBAL-91.

Comment by Benjamin Eberlei [ 26/Feb/11 ]

Fixed





[DBAL-84] Long string fields are being silently changed to 255 characters in the schema Created: 31/Jan/11  Updated: 20/Feb/11  Resolved: 20/Feb/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0, 2.0.1
Fix Version/s: 2.0.2, 2.1

Type: Bug Priority: Major
Reporter: Oleg Anashkin Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

I have an entity like this:

/**
 * @orm:Entity
 */
class Product
{
    /**
     * @orm:Id
     * @orm:Column(name="Merchant",type="string",length=50)
     */
    protected $merchant;

    /**
     * @orm:Column(name="Name",type="string",length=500)
     */
    protected $name;
}

When I change the length of $name field to 4000 it updates the schema like it is supposed to:

php.exe -f console doctrine:schema:update --dump-sql
ALTER TABLE product CHANGE Name Name VARCHAR(4000) NOT NULL

But when I change the length to 5000 it just silently without any warning uses value 255 instead:

php.exe -f console doctrine:schema:update --dump-sql
ALTER TABLE product CHANGE Name Name VARCHAR(255) NOT NULL

I had to look into actual table definition using mysql browser to find this issue because doctrine doesn't even warn about it. This could lead to some pretty serious bugs!



 Comments   
Comment by Benjamin Eberlei [ 20/Feb/11 ]

Fixed in master and 2.0.x





[DBAL-83] BigInt Type is returning string instead of integer Created: 28/Jan/11  Updated: 08/Sep/13  Resolved: 14/May/11

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

Type: Bug Priority: Major
Reporter: Miloslav "adrive" Kmet Assignee: Benjamin Eberlei
Resolution: Won't Fix Votes: 0
Labels: None


 Description   

I ran all tests with PostgreSQL and I got an error:

1) Doctrine\Tests\DBAL\Functional\TypeConversionTest::testIdempotentDataConversion with data set #3 ('bigint', 12345678, 'string')
The expected type from the conversion to and back from the database should be string
Failed asserting that <integer:12345678> is of type "string".

/usr/share/php/Doctrine2/dbal/tests/Doctrine/Tests/DBAL/Functional/TypeConversionTest.php:84

FAILURES!
Tests: 367, Assertions: 745, Failures: 1, Skipped: 8.

The BigInt Type is missing convertToPHPValue() method and returns string instead of int. I fixed this and also I fixed the test case for this. Patch will be availabe via github pull request.



 Comments   
Comment by Benjamin Eberlei [ 14/May/11 ]

Bigints have to be strings for int overflow reasons. PostgreSQL seems to detect 64 vs 32bit systems and casts correctly, this is unfortunate for the tests, i have to come up with a workdaround for this

Comment by Giovanni Lovato [ 08/Sep/13 ]

This behavior can't be correct:

php > var_dump($object->id);
int(201308090001)
php > $em->persist($object);
php > var_dump($em->find(get_class($object), 201308090001)->id);
string("201308090001");

The type should be consistent no matter what, otherwise the idempotent requirement breaks.

Comment by Benjamin Eberlei [ 08/Sep/13 ]

Giovanni Lovato This was fixed, bigint identity generation will always work with strings.





[DBAL-82] orderBy(), setFirstResult() bug with MSSQL Server Created: 16/Jan/11  Updated: 31/Jul/11  Resolved: 31/Jul/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.0
Fix Version/s: 2.0.7, 2.1.1

Type: Bug Priority: Major
Reporter: Aaron DM Assignee: Juozas Kaziukenas
Resolution: Fixed Votes: 1
Labels: None
Environment:

Windows 7, Apache2, PHP 5.3.5, Microsoft SQL Server 2008



 Description   
QueryBuilder
		$query = $this->createQueryBuilder('account')
					  ->select('account')
					  ->orderBy('account.id', 'DESC')
					  ->getQuery();

                $result = $query->setMaxResults($this->resultsPerPage+1)
        							->setFirstResult($this->offset)
        							->getResult();

The above, when using "setFirstResult" and "orderBy" (like that), results in an error like so:

SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]The multi-part identifier "t0_.id" could not be bound.

With a statement that looks something like so:

WITH outer_tbl AS (SELECT ROW_NUMBER() OVER (ORDER BY t0_.id DESC) AS "doctrine_rownum",   ...

The reason the error is occurring is because you apparently need to use the "alias" (e.g, SELECT t0_.id AS id0) "id0" in the "ORDER BY" clause.

So query will run with no problems if t0_.id as id0

WITH outer_tbl AS (SELECT ROW_NUMBER() OVER (ORDER BY id0 DESC) AS "doctrine_rownum",   ...

It looks like this behavior might only occur with Microsoft SQL Server, but it is a bug.

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

If you go into:
Doctrine\DBAL\Platforms\MsSqlPlatform

Find:

$over = preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby);

Add before:

                    # Get Columns
                    $columns = array();
                    if(preg_match_all('/([a-zA-Z][0-9]+_\.[a-zA-Z0-9\-_]+)\sAS\s([a-zA-Z0-9\-\_]+)/', $query, $matched)) {
                        for($i=0; $i<count($matched[1]); ++$i)
                        {
                            $columns[$matched[1][$i]] = $matched[2][$i];
                        }
                    }
                    
                    # Replace columns with their alias in the "orderby" statement
                    if(preg_match_all('/([a-zA-Z][0-9]+_\.[a-zA-Z0-9\-_]+)\s/i', $orderby, $matches)) {
                        foreach($matches[1] as $column) 
                        {
                            $orderby = preg_replace('/'.$column.'/', $columns[$column], $orderby);
                        }
                    }

Obviously this is a really ugly hack, but this resolves it.



 Comments   
Comment by Aaron DM [ 16/Jan/11 ]

Added the ugly hack fix.

Comment by Benjamin Eberlei [ 23/Jan/11 ]

Assigned to Juozas.

Comment by Minxuan GUO [ 05/May/11 ]

Thanks Aaron DM

Your code works perfectly

Comment by Jean-Marc Fontaine [ 20/Jun/11 ]

I am experiencing this bug too. Is there a way to get it fixed anytime soon? Maybe in the 2.1 release.

Comment by Aaron DM [ 24/Jun/11 ]

Here is what I think, a proper fix for this.

I re-wrote the query however it works the same and is pretty much the exact same in performance (from couple of tests I've done looking at profiler) + it fixes the issues regarding this ticket.

    /**
     * Adds an adapter-specific LIMIT clause to the SELECT statement.
     *
     * @param string $query
     * @param mixed $limit
     * @param mixed $offset
     * @link http://lists.bestpractical.com/pipermail/rt-devel/2005-June/007339.html
     * @return string
     */
    protected function doModifyLimitQuery($query, $limit, $offset = null)
    {
        if ($limit > 0) {
            $count = intval($limit);
            $offset = intval($offset);

            if ($offset < 0) {
                throw new DBALException("LIMIT argument offset=$offset is not valid");
            }

            if ($offset == 0) {
                $query = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . $count . ' ', $query);
            } else {
                $orderby = stristr($query, 'ORDER BY');

                if (!$orderby) {
                    $over = 'ORDER BY (SELECT 0)';
                } else {
                    $over = preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby);
                }

                // Remove ORDER BY clause from $query
                $query = preg_replace('/\s+ORDER BY(.*)/', '', $query);
                $query = preg_replace('/SELECT\s/', '', $query);

                $start = $offset + 1;
                $end = $offset + $count;

                // Limit query
                $query = "SELECT * FROM (SELECT ROW_NUMBER() OVER ($over) AS \"doctrine_rownum\", $query) AS doctrine_tbl WHERE \"doctrine_rownum\" BETWEEN $start AND $end";
            }
        }

        return $query;
    }

I had found this query a long time ago and found it to be fairly fast, however I'm not sure where exactly I found it.
I'm going to take a guess and assume it might be this comment:
http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server/comment-page-1/#comment-28594

Comment by Aaron DM [ 24/Jun/11 ]

Pull request: https://github.com/doctrine/dbal/pull/37

Comment by Benjamin Eberlei [ 31/Jul/11 ]

Fixed, merged pull request





[DBAL-91] orm:convert-mapping SchemaException on Postgres reserved words Created: 10/Jan/11  Updated: 12/Feb/11  Resolved: 12/Feb/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0
Fix Version/s: 2.0.2, 2.1

Type: Bug Priority: Major
Reporter: Emil Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOs 5.5, Postgres 8.1.22


Attachments: File test-creation.sql    
Issue Links:
Duplicate
is duplicated by DBAL-88 MySqlPlatform not escaping table names Resolved

 Description   

While execuiting

doctrine orm:convert-mapping --from-database yml /tmp/test.yml

I get presented an error

[Doctrine\DBAL\Schema\SchemaException]
Invalid index-name tablename_"input"_idx given, has to be [a-zA-Z0-9_]

The index "type" is quoted because this is a reserved word in Postgres. for example see the following definition:

FOREIGN KEY (something) REFERENCES somethings(id) ON UPDATE CASCADE
FOREIGN KEY (another) REFERENCES anothers(id) ON UPDATE CASCADE
FOREIGN KEY ("type") REFERENCES logtypes(id) ON UPDATE CASCADE

Only type will be quoted because it is a reserved word.



 Comments   
Comment by Benjamin Eberlei [ 10/Jan/11 ]

You have to quote with `` in Doctrine. That is translated to the underyling vendor quotation signs.

Comment by Emil [ 10/Jan/11 ]

@benjamin this considers a mapping error from database (Postgres) to YAML through the doctrine commandline tool, I do not think your comment is relevant for this issue.

Comment by Benjamin Eberlei [ 11/Jan/11 ]

@Emil it is, are you quoting the reserved word in the YML file using `` ? DBAL has some logic to fix escaping in these cases.

Comment by Emil [ 11/Jan/11 ]

@benjamin uhm, I might be baffled with the complexity of Doctrine or just plain wrong. But I'm trying to create a YML file from my database. How can I be making a mistake in the YML file with quotes?

The error message is presented because the Dcotrine ORM module tries to generate index names on columns, based on the database schema, but fails because the returned names (from the database schema) contain quotes (because they are reserved words).

Comment by Benjamin Eberlei [ 11/Jan/11 ]

Sorry, now i get it. I didnt relaize the usecase you were applying. Yes you are right

Comment by Benjamin Eberlei [ 23/Jan/11 ]

Can you give me an Example Postgresql Schema with DDL SQL that produces this error?

Comment by Emil [ 10/Feb/11 ]

These are the table creation for a test table which is able to reproduce the error.

Comment by Emil [ 10/Feb/11 ]

I hope this is enough information. The attached SQL reproduces the following error:

  1. doctrine orm:convert-mapping --from-database yml /tmp/test.yml

[Doctrine\DBAL\Schema\SchemaException]
Invalid index-name something_"input"_idx given, has to be [a-zA-Z0-9_]

orm:convert-mapping [--filter="..."] [--from-database] [-extend[="..."]] [-num-spaces[="..."]] [-h|--help] [-q|--quiet] [-v|--verbose] [-V|--version] [-a|--ansi] [-n|--no-interaction] command to-type dest-path

Comment by Benjamin Eberlei [ 12/Feb/11 ]

I can't reproduce it

Can you run the command with --verbose ? Additionally in lib/Doctrine/ORM/Tools/Console/Command/ConvertMappingCommand.php can you var_dump() $metadata after line 119?

Comment by Benjamin Eberlei [ 12/Feb/11 ]

Ok i could reproduce it now. Input is not a keyword for me though, type also not. Coudl reproduce it with "table"

Comment by Benjamin Eberlei [ 12/Feb/11 ]

Ok, this is a DBAL issue.

Comment by Benjamin Eberlei [ 12/Feb/11 ]

This is fixed and will be included in 2.0.2





[DBAL-81] Add support for auto-commit = NO accross databases Created: 02/Jan/11  Updated: 12/Nov/13  Resolved: 12/Nov/13

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

Type: Improvement Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

Currently all databases are running in AUTO-COMMIT = Yes Mode. This means that you have to explicitly open a transaction to be able to use transactional features.

There should be support to run in auto-commit = no mode, which means after connect and after each commit a new transaction is opened automatically.



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

PR:

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

Comment by Doctrine Bot [ 12/Nov/13 ]

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





[DBAL-79] Add Schema\Table::hasPrimaryKey method Created: 30/Dec/10  Updated: 26/Feb/11  Resolved: 26/Feb/11

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

Type: Improvement Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

Currently the name of the primary key is internal to the Table instance so there is no way to check if the table has a primary key without getting all the indexes and iterating over them.



 Comments   
Comment by Benjamin Eberlei [ 26/Feb/11 ]

This is fixed in trunk.





[DBAL-78] Native support for parameter lists Created: 24/Dec/10  Updated: 05/Mar/11  Resolved: 05/Mar/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: None
Fix Version/s: 2.1

Type: Improvement Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

Prerequisites:

1. Detection of this mode has to be super-fast. It should not have an auto-detection but should be triggered explicitly.
2. This mode cannot be supported after $conn->prepare(). It is only viable for $conn->executeQuery() or $conn->executeUpdate().

Case 1: Positional

$stmt = $conn->executeQuery("SELECT a.id FROM articles a WHERE a.id IN (?) AND a.foo = ?",
    array($paramList, $foo), array( Connection::PARAM_ARRAY => PDO::PARAM_INT) , PDO:PARAM_STR ),
    Connection::FLAG_EXPAND_ARRAYS
);

Would internally, right before execution, be rewritten to:

$c = count($paramList); // 3!
"SELECT a.id FROM articles a WHERE a.id IN (?, ?, ?) AND a.foo = ?"

Case 2: Named

$stmt = $conn->executeQuery("SELECT a.id FROM articles a WHERE a.id IN (:id) AND a.foo = :bar",
    array('id' => $paramList, 'bar' => $foo),
    array( array(Connection::PARAM_ARRAY => PDO::PARAM_INT), PDO::PARAM_STR ),
    Connection::FLAG_EXPAND_ARRAYS
);

Would internally, right before execution, be rewritten to:

$c = count($paramList); // 3!
"SELECT a.id FROM articles a WHERE a.id IN (:id1, :id2, :id3) AND a.foo = :bar"


 Comments   
Comment by Benjamin Eberlei [ 05/Mar/11 ]

Implementing this for Named parameters is not really possible without re-parsing the query after every replacement just because the named parameters can occur more then once and this messes with the positions.

Comment by Benjamin Eberlei [ 05/Mar/11 ]

Using arrays as param list is a very bad idea performance wise (and also code handling).

Algorithm now:

1. New Constants Connection::PARAM_INT_ARRAY and PARAM_STR_ARRAY, being apart by Connection::ARRAY_TYPE_OFFSET from their PDO::PARAM_STR and PDO:PARAM_INT plain values.
2. Detect if there is one _ARRAY param. If not abort expansion (complexity O(T)) where T is the number of types.
3. Parse SQL statement for positional needle => character position
4. Iterate param list and inline arrays values

Comment by Benjamin Eberlei [ 05/Mar/11 ]

Implemented in https://github.com/doctrine/dbal/commit/df50f44a179028a3e30dcab02cd3ff33cc4f8498





[DBAL-74] OracleSchemaManager - incorrectly detected the length of the string columns Created: 07/Dec/10  Updated: 12/Dec/10  Resolved: 12/Dec/10

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.0-RC1-RC3
Fix Version/s: 2.0-RC5

Type: Bug Priority: Major
Reporter: Miloslav "adrive" Kmet Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

When I create a VARCHAR2 column with the length specified in char units (VARCHAR2(10 CHAR)) instead of the default byte, the metadata stored in all_tab_columns -> data_length is not 10, but 40. It is better to use char_length instead of data_length. Char_length is the same wheter the size unit is in CHAR or BYTE.

It is neccessary for me to use CHAR instead of the default BYTE, because I am using unicode charset.



 Comments   
Comment by Miloslav "adrive" Kmet [ 07/Dec/10 ]

https://github.com/milokmet/dbal/commit/a5087e8e229bfd2cde947e1d387ebe2e2b122b85

Comment by Benjamin Eberlei [ 07/Dec/10 ]

Can I appoint you to Doctrine's Oracle specialist?

Thank you very much, I will merge that as soon as i have time.

Comment by Benjamin Eberlei [ 12/Dec/10 ]

Merged





[DBAL-73] Could not convert database value "b:0;" to Doctrine Type object Created: 06/Dec/10  Updated: 12/Dec/10  Resolved: 12/Dec/10

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.0-RC1-RC3
Fix Version/s: 2.0-RC5

Type: Bug Priority: Major
Reporter: Marcus Speight Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None

Attachments: File Setting.php    

 Description   

The following class when value is set to false causes "Could not convert database value "b:0;" to Doctrine Type object" exception when loading the entity.

/** @orm:Entity */
class MyClass
{
/**

  • @orm:Column(name="value", type="object")
    */
    public $value;
    }


 Comments   
Comment by Benjamin Eberlei [ 06/Dec/10 ]

From the PHP manual about unserialize():

In case the passed string is not unserializeable, FALSE is returned and E_NOTICE is issued. 

The solution: Use NULL instead of false

Comment by Marcus Speight [ 06/Dec/10 ]

But the string "b:0;" is unserializable it returns false. Also from the php manual on unserialize

FALSE is returned both in the case of an error and if unserializing the serialized FALSE value. 
It is possible to catch this special case by comparing str with serialize(false) or by catching the issued E_NOTICE. 

I've submitted a pull request that handles it correctly.

Comment by Benjamin Eberlei [ 06/Dec/10 ]

I know, but this type is called "object", so setting it to false makes no sense. Setting it to null makes sense though.

I know that b:0; is false, but why do you set it to false anyways? Objects are either null or an instance of the object. Never false.

Comment by Marcus Speight [ 06/Dec/10 ]

I'm storing settings in a database. using a Namespace, Key => Value triplet. I don't know what might be stored in the value part. Currently strings, NULL and boolean data are stored. Using object seem to be the best approach. (ArrayTypes have the same problem).

If you think a different type would be better then please let me know.

Comment by Benjamin Eberlei [ 06/Dec/10 ]

how is such a triplet only b:0; ? or is your entity the tripplet and the variable that fails is the value?

Comment by Marcus Speight [ 06/Dec/10 ]

I've added my entity. The entity is the triplet. And it is the value that fails if false is stored.
It allows me to store the value(false) but the throws an exception on loading the entity. It should allow the loading of the value(false) or disallow the storing of a false value.

Comment by Benjamin Eberlei [ 12/Dec/10 ]

Although this should really be just array and object, not strange false types i added this patch to prevent future reports of this as a problem. The loosely typed language PHP can go with null or false, so why throw people stones in their way.





[DBAL-72] doctrine entities generation has a bug for OneToMany setter type Created: 06/Dec/10  Updated: 12/Dec/10  Resolved: 12/Dec/10

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.0-BETA4
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Amaury Leroux de Lens Assignee: Benjamin Eberlei
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

Apache2 + PHP5.3.3 on macosx (via macport)



 Description   

http://trac.symfony-project.org/ticket/9272

assuming i've this model :

class TaskList

{ /** *@Id *@Column(type="integer") *@GeneratedValue(strategy="AUTO") */ private $id; /** *@Column(type="string", length="255") */ protected $name; /** *@OneToMany(targetEntity="Task", mappedBy="TaskList") */ protected $Tasks; }

AND

class Task{

/**
*@Id
*@Column(type="integer")
*@GeneratedValue?(strategy="AUTO")
*/
protected $id;

/**
*@Column(type="string", length="255")
*@Validation(

{ @NotBlank }

)
*/
protected $name;

/**
*@Column(type="boolean")
*/
protected $done = false;

/**
*@ManyToOne(targetEntity="TaskList", inversedBy="Tasks")
*@JoinColumn(name="task_list_id", referencedColumnName="id")
*/
protected $TaskList;

}

in symfony2

the ./console doctrine:generate:entities will create a setter in each class :

public function setTaskList(\TaskList $taskList) {
$this->TaskList = $taskList;
}

AND

public function addTasks(\Task $tasks) {
$this->Tasks[] = $tasks;
}

the " \ " before the setter function's parameter type is wrong here we should have

public function setTaskList(TaskList $taskList) {
$this->TaskList = $taskList;
}

AND

public function addTasks(Task $tasks) {
$this->Tasks[] = $tasks;
}

this lead ./console doctrine:generate:proxies to throw a hardly understandable error:

[ReflectionException]
Class Bundle\ToDoListBundle\Entity\TaskList does not exist

if you typed

/**
*@OneToMany?(targetEntity="\Bundle\myBundle\Entity\Task", mappedBy="TaskList?")
*/

the doctrine:generate:entities do not create this problem.
(my apologizes for inacurrate english)



 Comments   
Comment by Benjamin Eberlei [ 06/Dec/10 ]

This was fixed in the RC1 release. Can you verify this bug does not occour with RC1 anymore?

Comment by Amaury Leroux de Lens [ 06/Dec/10 ]

i'll do asap,
was just migrating this issue from symfony2 trac

Comment by Benjamin Eberlei [ 12/Dec/10 ]

Closed, no feedback and this issue was fixed with RC1.





missing column type "float" (DBAL-41)

[DBAL-70] MySql Platform is missing FloatType mappings Created: 02/Dec/10  Updated: 12/Dec/10  Resolved: 12/Dec/10

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.0.0-RC1-RC3
Fix Version/s: 2.0-RC4

Type: Sub-task Priority: Major
Reporter: Steffen Vogel Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

All other platforms are mapping double precission columns to the FloatType type. MySql not...

https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/MySqlPlatform.php



 Comments   
Comment by Benjamin Eberlei [ 12/Dec/10 ]

This was fixed in RC4.





Length of a string column cannot exceed 255 (DBAL-62)

[DBAL-69] Varchar definition should automatically switch to CLOB for sizes larger than max varchar length. Created: 27/Nov/10  Updated: 23/Dec/13  Resolved: 23/Dec/13

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.0-RC1-RC3
Fix Version/s: 2.1

Type: Sub-task Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

In the future we would probably allow arbitrary large sizes here and switch to a CLOB definition automatically if the specifed string length is larger than max length.



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

Fixed in commit: https://github.com/doctrine/dbal/commit/e57e92e1c0ee7e7949c5e82236a5bb96973ab044





[DBAL-65] No way to store binary data in PostgreSQL with Doctrine Created: 20/Nov/10  Updated: 09/Oct/12  Resolved: 20/Nov/10

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

Type: Bug Priority: Major
Reporter: Tomasz Jędrzejewski Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: None
Environment:

PostgreSQL 8.4



 Description   

The type system introduced by Doctrine makes impossible to store binary data in PostgreSQL databases that use Unicode. The `text` type is mapped to `TEXT`, but any trial to place some binary data there ends up with a database error, like this:

SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0x9c

This is a critical limitation, because Doctrine cannot be used now in projects that for any reasons have to use PostgreSQL, and their databases must store binary data. Even if it cannot be fixed right now, it should be clearly pointed out in the documentation in "Known vendor issues".

A possible solution for this problem is creating an equivalent of 'text' field, called 'binary' or something like that. It must be a simple type that is mapped to the simplest, but large type available in the database engine without any form of data structure validation. For PostgreSQL, this could be 'blob', but other database engines can use different types.



 Comments   
Comment by Tomasz Jędrzejewski [ 20/Nov/10 ]

Just a small note why I consider this bug as quite serious: for many programmers and their projects the lack of both support for such content type and any information about the limitation can be very dangerous. It can be impossible to remove ORM, if such an issue is encountered in the implementation process, and trials to workaround it are time-consuming.

If I'm about to decide whether to use a particular ORM or not, I must have full information about ORM and database-specific limitations.

One more update: shame on me, obviously there is no "blob" type in PostgreSQL; in this database engine binary data could be represented by 'BYTEA'.

Comment by Benjamin Eberlei [ 20/Nov/10 ]

This is not an issue, there are two options to "solve" your problem in userland:

1. Create your own DBAL type - http://www.doctrine-project.org/projects/orm/2.0/docs/reference/basic-mapping/en#custom-mapping-types
2. Use columnDefinition Attribute of @column - http://www.doctrine-project.org/projects/orm/2.0/docs/reference/annotations-reference/en#ann_column

Comment by Tomasz Jędrzejewski [ 12/Dec/10 ]

I know I can create a custom type, but I'd like to have a portable binary type by default in Doctrine DBAL, not reinventing the wheel every time I want to have one. I consider binary data as one of the primitive types that every database engine supports.

Comment by Jon Wadsworth [ 09/Oct/12 ]

This is an old post but just in case somebody else finds it. There is no need to do any of the above to store binary data in Postgres. I had the same situation and was easily solved by compressing file, base64 encoding it, and finally serializing it.

public static function prepareFileforDatabase($file)

{ $compressor = new \Zend_Filter_Compress_Gz(); $file = $compressor->compress($file); $file = base64_encode($file); return serialize($file); }

We use Zend and you may be able to get away with not compressing if you wanted to avoid the extra overhead on your server. To undo it is exactly the opposite.

public static function prepareFileforPHP($file)

{ $compressor = new \Zend_Filter_Compress_Gz(); $file = unserialize($file); $file = base64_decode($file); return $compressor->decompress($file); }

Sorry for the code coming out in all one line, but you get the idea.





[DBAL-67] OCI8: schema-tool:update is modifying not null boolean - NUMBER(1) columns to not null Created: 24/Nov/10  Updated: 29/Nov/10  Resolved: 29/Nov/10

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.0-RC1-RC3
Fix Version/s: 2.0-RC4

Type: Bug Priority: Major
Reporter: Miloslav "adrive" Kmet Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

Oracle - Oci8



 Description   

When I have a boolean column(s) in my entities and I want to update schema with schema tool, I get an exception:

My entity:

/** @Entity @Table(name='st_node') */
class Node
{
    /** @Id @Column(type="integer") @GeneratedValue */
    protected $id;
    
    /** @Column(type="string", length="255") */
    protected $title;
    
    /** @Column(type="boolean", nullable=false) */
    protected $is_published;
    
    /** @Column(type="boolean") */
    protected $is_sticky;
    
    /** @Column(type="integer", length=4) */
    protected $hits;
    
    /** @Column(type="decimal", precision=4, scale=2) */
    protected $score;
}
./doctrine orm:schema-tool:update --force
Entities\Address
Entities\Node
Entities\Article
Entities\User
Entities\Page
Updating database schema...

  [Doctrine\DBAL\Driver\OCI8\OCI8Exception]                         
  ORA-01442: column to be modified to NOT NULL is already NOT NULL  
            

The same with --dump-sql option:

./doctrine orm:schema-tool:update --dump-sql
Entities\Address
Entities\Node
Entities\Article
Entities\User
Entities\Page
ALTER TABLE ST_NODE MODIFY (is_published  NUMBER(1) NOT NULL, is_sticky  NUMBER(1) NOT NULL)

The columns are already not nulls, but schema-tool can't recognize that.

This is the DQL of the table:

CREATE TABLE "DOCTRINE"."ST_NODE"
  (
    "ID"           NUMBER(10,0) NOT NULL ENABLE,
    "TITLE"        VARCHAR2(255 BYTE) NOT NULL ENABLE,
    "DISCR"        VARCHAR2(255 BYTE) NOT NULL ENABLE,
    "IS_PUBLISHED" NUMBER(1,0) NOT NULL ENABLE,
    "HITS"         NUMBER(10,0) NOT NULL ENABLE,
    "SCORE"        NUMBER(4,2) NOT NULL ENABLE,
    "IS_STICKY"    NUMBER(1,0) NOT NULL ENABLE,
    PRIMARY KEY ("ID")
);


 Comments   
Comment by Miloslav "adrive" Kmet [ 24/Nov/10 ]

Fixed in https://github.com/milokmet/dbal/commits/DBAL-67

Comment by Benjamin Eberlei [ 27/Nov/10 ]

Btw, with regard to the comment on the Fix on your repository.

What has the commit to do with the NULL / NOT NULL issue? it seems the changes are entirely unrelated to this?

Comment by Miloslav "adrive" Kmet [ 29/Nov/10 ]

Hi Benjamin, No the changes are not unrelated to this, but I discover, what the problem was.

The problem was, thet the changed function didn't map correctly the database column types to the doctrine's types.

Therefor, when I have is_published column of doctrine's boolean type, it is mapped to oracle's NUMBER(1), but when I want to update the schema, doctrine schema managers thinks, that existing column in the database is integer with precision 1, and tries to modify the column to boolean -> number(1), but the column already is number(1) and oracle complains.

The same is also with bigint and smallint.

Comment by Benjamin Eberlei [ 29/Nov/10 ]

Fixed





[DBAL-64] Fix identifier quoting in DBAL Schema Created: 17/Nov/10  Updated: 18/Nov/10  Resolved: 18/Nov/10

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.0-RC1-RC3
Fix Version/s: 2.0-RC4

Type: Bug Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

Identifier quoting using the Schema component is sort of borked.

The optional or none approach from ClassMetadata should be looked at. Introducing a common quoting identifier `` and sanitizing this from each asset + a new method "getQuotedName()" that optionally quotes the identifier given the platform.



 Comments   
Comment by Benjamin Eberlei [ 18/Nov/10 ]

Fixed





[DBAL-63] Custom Types always detected as changed when orm:schema-tool:update Created: 16/Nov/10  Updated: 16/Nov/10  Resolved: 16/Nov/10

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.0-BETA4
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Berny Cantos Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 0
Labels: None
Environment:

Ubuntu 10.10, MySQL 5.1.49, PHP 5.3.3



 Description   

I defined a custom type for DBAL and used it in an entity annotation. Every time I call doctrine orm:schema-tool:update, even when no changes are been made to the code, DBAL thinks the schema has changed and needs an update. The type declaration is done in cli-config.php, when creating the EntityManager.

See the code and output below:

Project/CustomType.php
namespace Project;

use \Doctrine\DBAL\Platforms\AbstractPlatform,
    \Doctrine\DBAL\Types\Type;

class CustomType extends Type
{
    const TYPE = 'custom';

    public function getSqlDeclaration(array $fieldDeclaration,
                                      AbstractPlatform $platform)
    {
        return $platform->getVarcharTypeDeclarationSQL($fieldDeclaration);
    }

    public function getName()
    {
        return self::TYPE;
    }
}
Project/Entity.php
/**
 * @entity
 */
namespace Project;
class Entity
{
    /**
     * @id
     * @column(type="integer")
     */
    private $id;

    /**
     * @column(type="custom")
     */
    private $custom;
}
cli-config.php
$cache = new \Doctrine\Common\Cache\ApcCache;
$config = new \Doctrine\ORM\Configuration;
$config->setMetadataCacheImpl($cache);
$config->setQueryCacheImpl($cache);

$driverImpl = $config->newDefaultAnnotationDriver(APPLICATION_PATH . '/Project');
$config->setMetadataDriverImpl($driverImpl);

$config->setProxyDir(APPLICATION_PATH . '/Proxies');
$config->setProxyNamespace('Proxies');
$config->setAutoGenerateProxyClasses(true);

$connectionOptions = array(
    'driver'   => 'pdo_mysql',
    'dbname'   => 'project',
    'user'     => '#user#',
    'password' => '#password#',
    'host'     => 'localhost',
);

\Doctrine\DBAL\Types\Type::addType('custom', 'Project\CustomType');
$em = \Doctrine\ORM\EntityManager::create($connectionOptions, $config);

$helperSet = new \Symfony\Component\Console\Helper\HelperSet(array(
    'db' => new \Doctrine\DBAL\Tools\Console\Helper\ConnectionHelper($em->getConnection()),
    'em' => new Doctrine\ORM\Tools\Console\Helper\EntityManagerHelper($em),
));
Console Output
#:~ doctrine orm:schema-tool:create --dump-sql
CREATE TABLE Entity (id INT NOT NULL, custom VARCHAR(255) NOT NULL, PRIMARY KEY(id)) ENGINE = InnoDB

#:~ doctrine orm:schema-tool:create
Creating database schema...
Database schema created successfully!

#:~ doctrine orm:schema-tool:update --dump-sql
ALTER TABLE Entity CHANGE custom custom VARCHAR(255) NOT NULL

#:~ doctrine orm:schema-tool:update --force
Updating database schema...
Database schema updated successfully!

#:~ doctrine orm:schema-tool:update --dump-sql
ALTER TABLE Entity CHANGE custom custom VARCHAR(255) NOT NULL


 Comments   
Comment by Benjamin Eberlei [ 16/Nov/10 ]

Yes, it thinks its a varchar and wants to update that definition. This is a duplicate of DBAL-42





[DBAL-62] Length of a string column cannot exceed 255 Created: 16/Nov/10  Updated: 27/Nov/10  Resolved: 27/Nov/10

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.0-RC1-RC3
Fix Version/s: 2.0-RC4

Type: Bug Priority: Major
Reporter: jules b Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

Mysql


Sub-Tasks:
Key
Summary
Type
Status
Assignee
DBAL-69 Varchar definition should automatical... Sub-task Resolved Benjamin Eberlei  

 Description   

I use the annotation mapping:
here's my mapping informations:

/** 
* @var mixed optionValue
* @orm:Column(nullable="true", name="option_value", length="2000", type="string") 
*/
protected $optionValue;

when i check the mysql column type it's still "varchar(255)".



 Comments   
Comment by Jani Hartikainen [ 26/Nov/10 ]

Confirmed on OS X 10.6, PHP 5.3.2, MySQL 5.1.51, DBAL RC3. Schema-tool always generates a VARCHAR(255) column for cases like this. 1.x used to generate TEXT, which I would assume is the correct behavior with MySQL in this case.

Comment by Benjamin Eberlei [ 27/Nov/10 ]

Fixed, the max length of varchars was set to 255.

However this is rather the default value. The maximum value is now 4000, which is the smallest common denominator between all supported vendors (Oracle is that).

In the future we would probably allow arbitrary large sizes here and switch to a CLOB definition automatically if the specifed string length is larger than max length.





[DBAL-60] OCI8Connection couldn't connect when charset parameter specified Created: 09/Nov/10  Updated: 29/Dec/12  Resolved: 11/Nov/10

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.0.0-BETA4
Fix Version/s: 2.0.0-RC1-RC3

Type: Bug Priority: Major
Reporter: Miloslav "adrive" Kmet Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

PHP Version 5.3.3-1ubuntu9, OCI8 1.4.1, Oracle 10.2.0.4.0



 Description   

When I am trying to connect to Oracle instance with another charset then specified in my environment variable NLS_LANG I got an error.

$connectionOptions['oracle'] = array(
    'driver' => 'oci8',
    'dbname' => 'BOOK',
    'user' => 'doctrine',
    'password' => 'doctrine',
    'charset' => 'AL32UTF8',
);

$em = EntityManager::create($connectionOptions['oracle'], $config, $evm);
$em->getConnection()->connect();

Produce error messages:

PHP Warning: oci_error() expects parameter 1 to be resource, boolean given in /www/test/doctrine2/vendor/dbal/lib/Doctrine/DBAL/Driver/OCI8/OCI8Connection.php on line 149
PHP Fatal error: Uncaught exception 'Doctrine\DBAL\Driver\OCI8\OCI8Exception' in /www/test/doctrine2/vendor/dbal/lib/Doctrine/DBAL/Driver/OCI8/OCI8Connection.php:42

There is bug in OCI8Connection::errorInfo() and ::errorCode(). When oci_connect failed, it returns a boolean - false. but oci_error() accepts only resource as parameter. Therefor it is neccessary to check, whether $this->_dbh is resource.

When I've fixed the error handling, I've got the right error, why I couldn't connect to Oracle:

PHP Fatal error: Uncaught exception 'Doctrine\DBAL\Driver\OCI8\OCI8Exception' with message 'ORA-12154: TNS:could not resolve the connect identifier specified' in /www/test/doctrine2/vendor/dbal/lib/Doctrine/DBAL/Driver/OCI8/OCI8Exception.php:28

That's because oci_connect accepts as connection string only TNS name, or Oracle Connection string. But ;charset=* in TNS name or connection string is invalid. oci_connect has 4th parameter charset for that purpose.



 Comments   
Comment by Miloslav "adrive" Kmet [ 09/Nov/10 ]

Fixed in https://github.com/milokmet/dbal/tree/DBAL-60

Comment by Benjamin Eberlei [ 11/Nov/10 ]

Fixed

Comment by Ruslan [ 29/Dec/12 ]

Can you help me. I'm having same problem. But your link doesn't work.

Comment by Miloslav "adrive" Kmet [ 29/Dec/12 ]

You must have very old source code. The bug was fixed 2 years ago. Just compare your OCI8 driver with https://github.com/doctrine/dbal/tree/master/lib/Doctrine/DBAL/Driver/OCI8





[DBAL-61] Incompatible transactions in OCI8 driver with other drivers (pdo_oci, pdo_mysql, pdo_pgsql) Created: 09/Nov/10  Updated: 11/Nov/10  Resolved: 11/Nov/10

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.0.0-BETA4
Fix Version/s: 2.0.0-RC1-RC3

Type: Bug Priority: Major
Reporter: Miloslav "adrive" Kmet Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

PHP Version 5.3.3-1ubuntu9, OCI8 1.4.1, Oracle 10.2.0.4.0



 Description   

When I try to insert some data, it is not saved into database.

$connectionOptions['oci8'] = array(
    'driver' => 'oci8',
    'dbname' => 'BOOK',
    'user' => 'doctrine',
    'password' => 'doctrine',
//    'charset' => 'AL32UTF8',
);

$em = EntityManager::create($connectionOptions['oci8'], $config, $evm);
$conn = $em->getConnection();
$conn->connect();
$conn->insert('my_table', array('my_column' => 'Some value'));

When I invoke the previous code on any other DBAL driver, like pdo_oci, pdo_mysql, pdo_pgsql, the row is inserted.

That's because Oracle is transactional, and everything need to be commited. This behavior prevent's me (and I think in a feature not only me), to use 3rd party code (bundles, models, plugins...) in my applications running oracle. This behavior prevents developers to write portable code with DBAL. The solution is to make OCI8Driver commit everything after invoking oci_execute(), the same way how Doctrine_Adapter_Oracle works in Doctrine1. (https://github.com/doctrine/doctrine1/blob/master/lib/Doctrine/Adapter/Oracle.php)



 Comments   
Comment by Benjamin Eberlei [ 09/Nov/10 ]

Scheduled for RC1, thanks for the patch - I will apply asap.

Comment by Benjamin Eberlei [ 11/Nov/10 ]

Fixed





[DBAL-56] Ship a compatibility wrapper like MDB2 Created: 29/Sep/10  Updated: 05/Mar/11  Resolved: 05/Mar/11

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.0.0-BETA4
Fix Version/s: 2.1

Type: New Feature Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

Database Portability is expensive but often necessary. We should support the important Compatibility Layer Options that MDB2 has:

http://pear.php.net/manual/en/package.database.mdb2.intro-portability.php

The following I think are doable:

  • Empty to Nulls
  • Change Case of Field-Assocs
  • Oracle RTRIM for Chars
  • Num Rows "hack" for Oracle
  • Delete Count (Some vendors rewrite a DELETE tablename into a TRUNCATE and dont report the delete count)

There should be a class extending "Doctrine\DBAL\Connection" making use of the 'wrapperClass' option. Two new classes are necessary:

Doctrine\DBAL\Portability\Connection
Doctrine\DBAL\Portability\Statement



 Comments   
Comment by Benjamin Eberlei [ 05/Mar/11 ]

Implemented.

To use define the following three parameters:

$params = array();
// ...
$params['wrapperClass'] = 'Doctrine\DBAL\Portability\Connection';
$params['portability'] = Doctrine\DBAL\Portability\Connection::PORTABILITY_ALL;
$params['fetch_case'] = \PDO::CASE_LOWER;
$conn = DriverManager::getConnection($params, $config, $evm);

This mode probably hits performance pretty hard,but it allows you to write code that works with all the quirks of fetching values from all database vendors.





[DBAL-55] Nested transaction emulation doesn't support rollback inside a nested transaction Created: 29/Sep/10  Updated: 01/Oct/10  Resolved: 01/Oct/10

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.0-BETA4
Fix Version/s: 2.0.0-RC1-RC3

Type: Bug Priority: Major
Reporter: Lukas Kahwe Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

It seems like beginTransaction() counts transaction nesting levels, but it doesnt set a savepoint when starting a new nesting level, nor does it therefore support rollbacks when rolling back inside a nested transaction. This means that I could call rollback() and it doesnt actually do anything, which imho is a bug.

The feature of using savepoints to emulate nested transactions properly seems to be present in Doctrine1.



 Comments   
Comment by Lukas Kahwe [ 30/Sep/10 ]

ok .. i am working on a patch

Comment by Benjamin Eberlei [ 30/Sep/10 ]

I guess we need new methods on AbstractPlatform to return the SQL to set, release and rollback a savepoint and then extend the Doctrine\DBAL\Connection to work with that.

Comment by Lukas Kahwe [ 30/Sep/10 ]

yup .. thats what i am doing

Comment by Lukas Kahwe [ 30/Sep/10 ]

did some initial work: http://github.com/lsmith77/dbal/tree/savepoints

not quite sure about the changes i had to do in the unittest.
probably should add new unit tests too.
also there are a couple of questions i have in the connection class

Comment by Benjamin Eberlei [ 01/Oct/10 ]

merged.





[DBAL-53] Missing convertToDatabase-calls when loading and persisting entities Created: 15/Sep/10  Updated: 15/Sep/10  Resolved: 15/Sep/10

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

Type: Bug Priority: Major
Reporter: Lars Strojny Assignee: Benjamin Eberlei
Resolution: Duplicate Votes: 0
Labels: None

Attachments: File 001-doctrine-type-conversion-support-for-onetomany.diff     File 002-doctrine-type-conversion-support-for-onetoone.diff     File 004-doctrine-type-conversion-support-delete.diff    
Issue Links:
Duplicate
duplicates DDC-1503 Add support for executing custom SQL ... Closed

 Description   

As we use binary encoded UUIDs as primary keys, we sometimes stumbled upon the issue of having incorrect SQL queries executed with unencoded database values. Currently we observed the wrong behavior for the following scenarios:

  • Deleting entities
  • OneToOne relations
  • OneToMany relations

I'm pretty sure this bug also exists for OneToMany relations, but we haven't used them yet.



 Comments   
Comment by Benjamin Eberlei [ 15/Sep/10 ]

Duplicate of DBAL-15





[DBAL-49] PDOSqlsrv's constructor breaks when using non "local" server host Created: 07/Sep/10  Updated: 23/Sep/10  Resolved: 23/Sep/10

Status: Resolved
Project: Doctrine DBAL
Component/s: Drivers
Affects Version/s: 2.0.0-BETA4
Fix Version/s: 2.0.0-RC1-RC3

Type: Bug Priority: Major
Reporter: Aaron DM Assignee: Juozas Kaziukenas
Resolution: Fixed Votes: 0
Labels: None
Environment:

Windows 7, Apache 2.1, PHP 5.3.3, Microsoft SQL Server 2008 Enterprise Edition



 Description   

Problem:

Currently, the _constructPdoDsn() for the PDOSqlsrv driver works fine only if the host provided is: local or localhost.
If using an IP address, this breaks. Reason is because _constructPdoDsn() applies ( and ) around the host.

        $dsn = 'sqlsrv:server=(';
        if (isset($params['host'])) {
            $dsn .= $params['host'];
        }
        $dsn .= ')';

This is fine for when $params['host'] = 'local'; because I believe local is a name pipe (I think) and to properly use it you need to add the brackets around it

However if $params['host'] = '127.0.0.1'; this breaks!

Fix:

Get rid of the forced brackets

        $dsn = 'sqlsrv:server=';
        if (isset($params['host'])) {
            $dsn .= $params['host'];
        }

That way, when supplying local as a host, you have to enter (local) (which makes more sense as that's how you would do it normally in other applications).



 Comments   
Comment by Benjamin Eberlei [ 23/Sep/10 ]

This is fixed in master.





[DBAL-46] Fix doctrine-dbal console command Created: 01/Sep/10  Updated: 02/Sep/10  Resolved: 02/Sep/10

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.0-BETA4
Fix Version/s: 2.0.0-RC1-RC3

Type: Bug Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

It still uses Symfony\Components with S.



 Comments   
Comment by Benjamin Eberlei [ 02/Sep/10 ]

fixed.





[DBAL-47] Incorrect filename -- EchoSqlLogger.php Created: 02/Sep/10  Updated: 02/Sep/10  Resolved: 02/Sep/10

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.0-BETA4
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Alexandr Torchenko Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: None


 Description   

I've got error:
require(/var/www/betportal/dev.betportal.org/application/libraries/Doctrine/DBAL/Logging/EchoSqlLogger.php) [function.require]: failed to open stream: No such file or directory

Doctrine/DBAL/Logging/EchoSQLLogger.php should be renamed to EchoSqlLogger.php.



 Comments   
Comment by Benjamin Eberlei [ 02/Sep/10 ]

the class is named EchoSQLLogger.





[DBAL-51] "No identifier/primary key specified for Entity" problem, Doctrine Beta 3 cannot find primary key Created: 31/Aug/10  Updated: 27/Nov/10  Resolved: 27/Nov/10

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.0-BETA4
Fix Version/s: 2.0.0-RC1-RC3

Type: Bug Priority: Major
Reporter: Minxuan GUO Assignee: Juozas Kaziukenas
Resolution: Fixed Votes: 0
Labels: None
Environment:

windows xp, sql server 2008, php 5.3



 Description   

when execute

php doctrine.php orm:convert-mapping --from-database annotation "Entities"

get error message

No identifier/primary key specified for Entity 'Classname(Tablename)'. Every Entity must have an identifier/primary key.

For resolving this problem
I've replaced the line

public function loadMetadataForClass($className, ClassMetadataInfo $metadata)
{
...
$indexes = $this->tables[$tableName]->getIndexes();
...
}

in function "loadMetadataForClass" in \\Doctrine\ORM\Mapping\Driver\DatabaseDriver.php by

public function loadMetadataForClass($className, ClassMetadataInfo $metadata)
{
...
$indexes = $this->_sm->listTableIndexes($tableName);
...
}

then the beta 3 works for my date base

It is because doctrine look for $indexes['prime'] to construit the primary key in entities. when we use "$this->tables[$tableName]>getIndexes()", the keys in $indexes are the real name of indexes of the data base, and in my database, the primary key indexes are named PK_TABLENAME. So I have to use $this>_sm->listTableIndexes($tableName) to pre-edit the indexes before get the list



 Comments   
Comment by Benjamin Eberlei [ 15/Sep/10 ]

This is an error in the MSSQL SchemaManager, it is not correctly setting the primary = true key in the index retrieve method. I move that issue to DBAL and reassign to juokaz

Comment by Juozas Kaziukenas [ 14/Nov/10 ]

I believe this is now fixed, but I'll test more https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/MsSqlSchemaManager.php#L118

Comment by Benjamin Eberlei [ 27/Nov/10 ]

Fixed.





[DBAL-45] Add CLI tool that checks for Reserved Keywords Created: 31/Aug/10  Updated: 27/Feb/11  Resolved: 27/Feb/11

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

Type: New Feature Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None

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

 Description   

Add a CLI tool that checks for reserved keywords in your current schema (for other databases vendors) to support cross vendor portability.



 Comments   
Comment by Benjamin Eberlei [ 27/Feb/11 ]

Implemented.





[DBAL-43] Doctrine\DBAL\Platforms\MsSqlPlatform Generates invalid T-SQL Created: 15/Aug/10  Updated: 27/Nov/10  Resolved: 27/Nov/10

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.0.0-BETA3
Fix Version/s: 2.0.0-RC1-RC3

Type: Bug Priority: Major
Reporter: Karl Southern Assignee: Juozas Kaziukenas
Resolution: Fixed Votes: 0
Labels: None
Environment:

Any version of SQLSrv that I have available (2000-2008 R2)



 Description   

MsSqlPlatform::getAlterTableSQL generates a reasonable quantity of invalid T-SQL (to my knowledge, I'd happily be corrected if wrong).

Unfortunately SqlSrv (to my knowledge) only accepts "ALTER old_old_name column_definition" and renames of tables and columns are only capable using a stored procedure.

I'm happy to produce a patch but I think I need a bit of guidance to do so more quickly

The major one is that getAlterTableSQL will need to produce multiple SQL statements, but in my testing I'm not able to get this to do so without some additional problems, and I've not yet had the opportunity to dive into the bowels of the rest of the schema bits and bobs to understand why. If anyone could point me in the vague direction that would be really handy



 Comments   
Comment by Juozas Kaziukenas [ 14/Nov/10 ]

Isn't this fixed in current master https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/MsSqlPlatform.php#L226 ?

Comment by Benjamin Eberlei [ 27/Nov/10 ]

Fixed





[DBAL-58] Schema tool does not see difference between onDelete="NO ACTION" and onDelete="RESTRICT" Created: 11/Aug/10  Updated: 28/Dec/13  Resolved: 28/Dec/13

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

Type: Improvement Priority: Major
Reporter: Jan Obrátil Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 1
Labels: None

Attachments: File Example.php    

 Description   

If database has ON DELETE CASCADE, schema tool detects change to NO ACTION and RESCRICT.
If database has ON DELETE RESCRICT, schema tool detects change only in case of CASCADE.
If database has ON DELETE NO ACTION, schema tool detect change only in case of CASCADE.

Detecting of changes is done by:
doctrine orm:schema-tool update --dump-sql
and
doctrine orm:validate-schema



 Comments   
Comment by Benjamin Eberlei [ 11/Aug/10 ]

which database version do yo uuse? can you paste an example schema?

Comment by Jan Obrátil [ 11/Aug/10 ]

This is example script with 2 testing entities.

Comment by Jan Obrátil [ 11/Aug/10 ]

I have attached script with two entities.

I have this database version:
$ mysql --version
mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1

Check line 44. There is CASCADE.
Updating schema by: $ doctrine orm:schema-tool:update

Change CASCADE to NO ACTION
$ doctrine orm:schema-tool:update --dump-sql
ALTER TABLE ArticleItem DROP FOREIGN KEY ArticleItem_ibfk_1;
ALTER TABLE ArticleItem ADD FOREIGN KEY (articleGroupId) REFERENCES ArticleGroup(id) ON DELETE NO ACTION

everything ok, so: $ doctrine orm:schema-tool:update

Change NO ACTION to RESTRICT
$ doctrine orm:schema-tool:update --dump-sql
// no output!!

Change RESTRICT to CASCADE
$ doctrine orm:schema-tool:update --dump-sql
ALTER TABLE ArticleItem DROP FOREIGN KEY ArticleItem_ibfk_1;
ALTER TABLE ArticleItem ADD FOREIGN KEY (articleGroupId) REFERENCES ArticleGroup(id) ON DELETE CASCADE

everything ok, so: $ doctrine orm:schema-tool:update

Change CASCADE to RESTRICT
$ doctrine orm:schema-tool:update --dump-sql
ALTER TABLE ArticleItem DROP FOREIGN KEY ArticleItem_ibfk_1;
ALTER TABLE ArticleItem ADD FOREIGN KEY (articleGroupId) REFERENCES ArticleGroup(id) ON DELETE RESTRICT

everything ok, do: $ doctrine orm:schema-tool:update

Change RESTRICT to NO ACTION
$ doctrine orm:schema-tool:update --dump-sql
// no output!!

So there is no way to update database schema from RESTRICT to NO ACTION and reverse!

Comment by Benjamin Eberlei [ 31/Oct/10 ]

Hm you are right, however for MySQL NO ACTION is the same as RESTRICT as stated by http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

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

Jan Obrátil As there internally is no difference between NO ACTION AND RESTRICT in MySQL I am closing this now. Doctrine generates the correct statements here when necessary.





[DBAL-42] Use column comments for further Doctrine Type Inference Created: 12/Aug/10  Updated: 20/Feb/11  Resolved: 20/Feb/11

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

Type: New Feature Priority: Major
Reporter: Benjamin Eberlei Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by DBAL-90 SchemaManager not able to reverse-eng... Resolved

 Description   

Any custom type could use vendors column comment functionalities to allow more details on the specific type of a column that can be used inside the "listTableColumns()" method.



 Comments   
Comment by Benjamin Eberlei [ 18/Feb/11 ]

MySQL, postfix column with "COMMENT 'The KEY obviously'"

Postgres, COMMENT syntax (another SQL statement, narf!): http://www.postgresql.org/docs/8.3/static/sql-comment.html

Oracle, COMMENT syntax (another SQL statement): http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_4009.htm

Sqlite: Nada

Mssql: (woah this is ugly) http://msdn.microsoft.com/en-us/library/ms180047.aspx

Comment by Benjamin Eberlei [ 20/Feb/11 ]

Implemented for Oracle, PostgreSQL and MySQL.

Now for Array and Object types a Column Comment is appended of the format "(DC2Type:$type)" which is parsed when reading from the database.





[DBAL-39] SQL Logger should log the last failed query Created: 04/Aug/10  Updated: 15/Aug/10  Resolved: 15/Aug/10

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.0-BETA3
Fix Version/s: 2.0.0-BETA4

Type: Improvement Priority: Major
Reporter: Michael Ridgway Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None


 Description   

The SQL Logger is only called after a query has been executed (this was changed to allow profiling of the queries). This makes it difficult to determine which query has failed.

The commit that changed the functionality is at: http://github.com/doctrine/dbal/commit/fe0f0e4b8c6c5308cab3d683c552f61317de7067

It seems that the logger interface needs to be changed to two functions, a pre-query function and a post-query function. Profiling should be handled by the logger if it chooses and not by the connection class.



 Comments   
Comment by Benjamin Eberlei [ 15/Aug/10 ]

After lots of thinking i changed the Logger again to have a startQuery() and a stopQuery() method now.





[DBAL-37] auto increment can not be added to a column, when postgresql is used. Created: 22/Jul/10  Updated: 25/Jul/10  Resolved: 25/Jul/10

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.0.0-BETA2
Fix Version/s: 2.0.0-BETA3

Type: Bug Priority: Major
Reporter: Ting Wang Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux


Attachments: File autoincrementPatch.diff    

 Description   

To add auto increment to on column using the following code in the
migration class:

$systemSettingsTable->changeColumn('setting_id',
array('autoincrement' => true));
or
$settingIdColumn = $systemSettingsTable->getColumn('setting_id');
$settingIdColumn->setAutoincrement(true);

This works fine when MySQL is used. But when PostGreSQL is used, this
statement doesn't create any SQL statement.

The patch in attachment could perhaps resolve this problem some way.



 Comments   
Comment by Benjamin Eberlei [ 25/Jul/10 ]

Fixed in current master





[DBAL-34] MySql getListTableForeignKeysSQL doesn't work for 5.0.xx Created: 19/Jul/10  Updated: 15/Aug/11  Resolved: 27/Jul/10

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.0.0-BETA2
Fix Version/s: 2.0.0-BETA3

Type: Bug Priority: Major
Reporter: Ting Wang Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

MySQL Server version: 5.0.51a-24+lenny4 (Debian)



 Description   

The sql contains mysql specific code. And for the mysql 5.0.xxx the sql statement has syntax error.

in /Doctrine/DBAL/Platforms/MySqlPlatform.php:
public function getListTableForeignKeysSQL($table, $database = null)
{
$sql = "SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, ".
"k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ ".
"FROM information_schema.key_column_usage k /*!50116 ".
"INNER JOIN information_schema.referential_constraints c ON ".
" c.constraint_name = k.constraint_name AND ".
" c.table_name = '$table' */ WHERE k.table_name = '$table'";

if ($database)

{ $sql .= " AND k.table_schema = '$database' AND c.constraint_schema = '$database'"; }

$sql .= " AND `REFERENCED_COLUMN_NAME` is not NULL";

return $sql;
}

For the mysql lower as 5.1.16 the SQL could be as the following:

SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, k.`REFERENCED_COLUMN_NAME` FROM information_schema.key_column_usage k WHERE k.table_name = 'some_table' AND k.table_schema = 'some database' AND c.constraint_schema = 'some database' AND `REFERENCED_COLUMN_NAME` is not NULL

In this statement there is no reference of c



 Comments   
Comment by Benjamin Eberlei [ 27/Jul/10 ]

Fixed

Comment by Aigars Gedroics [ 15/Aug/11 ]

Pity that because of this the Doctrine schema-tool update action reports incorrect change list.
It tries to drop/add foreign keys because "ON DELETE CASCADE" option isn't read from the database at all.

Comment by Benjamin Eberlei [ 15/Aug/11 ]

5.0.x has no way to export the CASCADE details. Its just not possible to get this data in 5.0





[DBAL-33] Doctrine2 fails handling microseconds from PostgreSQL record Created: 17/Jul/10  Updated: 24/Jul/10  Resolved: 24/Jul/10

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: None
Fix Version/s: 2.0.0-BETA3

Type: Bug Priority: Major
Reporter: Jan Tichý Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

Doctrine - GIT trunk (night build from 2010-07-17), Windows 7, PHP 5.3.2, PostgreSQL 8.4.3, Apache 2.2.15



 Description   

A column in database may be defined as both TIMESTAMP WITH TIMEZONE and TIMESTAMP WITHOUT TIMEZONE. If I insert a new value directly to the database through NOW() function, the value is stored including microseconds.

But then, when I am trying to load the record to Doctrine entity, following exception is thrown:

Doctrine\DBAL\Types\ConversionException

Could not convert database value "2010-07-17 15:29:57.762+02" to Doctrine Type datetimetz

File: C:\dev\etrener\library\Doctrine\DBAL\Types\ConversionException.php Line: 46

The same with both datetime and datetimetz columns.

The problem is probably in PostgreSqlPlatform::getDateTimeTzFormatString(), where is the following row:
public function getDateTimeTzFormatString()

{ return 'Y-m-d H:i:sO'; }

But PostgreSQL stores timestamps with microseconds, so format should be maybe something like:

{ return 'Y-m-d H:i:s.uO'; }

The problem is already posted in http://www.doctrine-project.org/jira/browse/DBAL-22?focusedCommentId=13574&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_13574, but that issue is already closed so maybe it was overlooked already then.

Thank you for fix!



 Comments   
Comment by Benjamin Eberlei [ 20/Jul/10 ]

Both DateTime and DateTimeTz assume that TIMESTAMP(0) is the definition, not the TIMESTAMP that implicitly degrades to TIMESTAMP(6). I am not sure how to handle this, I find this Postgres DateTime stuff rather annoying

Is there a global client side option for Postgres users to configure this?

Comment by Benjamin Eberlei [ 24/Jul/10 ]

@Jan: Did you use create the column manually yourself (TIMESTAMP WITHOUT TIMEZONE) or use the Doctrine Schema-Tool which defines TIMESTAMP(0) WITHOUT TIMEZONE.

Comment by Benjamin Eberlei [ 24/Jul/10 ]

Fixed in DBAL Trunk, see the following section of the DBAL manual to understand the workaround for PostgreSQL TIMESTAMP( n ) types where n > 0.

ORM always creates TIMESTAMP(0), so this is handled as a legacy database schema.

Comment by Benjamin Eberlei [ 24/Jul/10 ]
++ PostgreSQL

+++ DateTime, DateTimeTz and Time Types

Postgres has a variable return format for the datatype TIMESTAMP(n) and TIME(n)
if microseconds are allowed (n > 0). Whenever you save a value with microseconds = 0.
PostgreSQL will return this value in the format:

    2010-10-10 10:10:10 (Y-m-d H:i:s)

However if you save a value with microseconds it will return the full representation:

    2010-10-10 10:10:10.123456 (Y-m-d H:i:s.u)

Using the DateTime, DateTimeTz or Time type with microseconds enabled columns
can lead to errors because internally types expect the exact format 'Y-m-d H:i:s'
in combination with `DateTime::createFromFormat()`. This method is twice a fast
as passing the date to the constructor of `DateTime`.

This is why Doctrine always wants to create the time related types without microseconds:

* DateTime to `TIMESTAMP(0) WITHOUT TIME ZONE`
* DateTimeTz to `TIMESTAMP(0) WITH TIME ZONE`
* Time to `TIME(0) WITHOUT TIME ZONE`

If you do not let Doctrine create the date column types and rather use types with microseconds
you have replace the "DateTime", "DateTimeTz" and "Time" types with a more liberal DateTime parser
that detects the format automatically:

    [php]
    use Doctrine\DBAL\Types\Type;

    Type::overrideType('datetime', 'Doctrine\DBAL\Types\VarDateTime');
    Type::overrideType('datetimetz', 'Doctrine\DBAL\Types\VarDateTime');
    Type::overrideType('time', 'Doctrine\DBAL\Types\VarDateTime');
Comment by Jan Tichý [ 24/Jul/10 ]

@Benjamin: Yes, I have created the column manualy directly in database using my own CREATE TABLE definition.





[DBAL-30] Doctrine\DBAL\Connection::query() method does not connect automatically to the database Created: 29/Jun/10  Updated: 30/Jun/10  Resolved: 30/Jun/10

Status: Resolved
Project: Doctrine DBAL
Component/s: None
Affects Version/s: 2.0.0-BETA2
Fix Version/s: 2.0.0-BETA3

Type: Bug Priority: Major
Reporter: Tomasz Jędrzejewski Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux, PostgreSQL 8.4



 Description   

The database access methods in Doctrine\DBAL\Connection automatically connect to the database, if the connection has not been created yet. This is not true in case of Doctrine\DBAL\Connection::query() - if we forget to connect manually, we get:

Warning: call_user_func_array() expects parameter 1 to be a valid callback, first array member is not a valid class name or object in /.../Doctrine/DBAL/Connection.php on line 608

The problem is caused by the lack of

$this->connect();

line within the