[DBAL-1214] MySQL has gone away using ImportCommand Created: 29/Apr/15  Updated: 29/Apr/15

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

Type: Bug Priority: Major
Reporter: Sylvain Filteau Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Importing a fairly large SQL file (a MySQL dump) fails with a MySQL has gone away error.

When reading the ImportCommand code, I see that this issue is already "fixed" by checking if the connection is an instance of `\Doctrine\DBAL\Driver\PDOConnection`.

The problem is that I don't see how the connection could be an instance of this class since it doesn't extend `\Doctrine\DBAL\Connection`.

If I'm wrong, then I don't know how to configure my connection to extend the PDOConnection class. Thanks for your help on that






[DBAL-1081] Paginator - Query Limit for SQL Server - SqlServerPlatform.php Created: 16/Dec/14  Updated: 16/Jun/15

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

Type: Bug Priority: Major
Reporter: Maciej Grajcarek Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dbal, dql, orderBy, sqlserver
Environment:

Windows



 Description   

Hi!
I have a problem with Query results limit when ordering by SUM of a field.

My query looks like this:

            SELECT ypk.name keyword, SUM(ypk.value) total
            FROM YpBundle:YellowPageKeyword ypk
              JOIN ypk.yellowpage yp
              JOIN yp.listing l
            WHERE l.customer = :customerId
              AND ypk.originDate >= :dateFrom
              AND ypk.originDate <= :dateTo
            GROUP BY ypk.nameCrc, ypk.name
            ORDER BY SUM(ypk.value) DESC

First I was catching following error:
message: "[Syntax Error] line 0, col 395: Error: Expected known function, got 'SUM'"
class: Doctrine\ORM\Query\QueryException

It only accourse if SUM is used in ORDER BY clause.

I have registered new class Sum which extends FunctionNode.

Now, query is build and executed but it has an error:

'SELECT * FROM 
     (SELECT y0_.name AS name_0, SUM(y0_.value) AS sclr_1, ROW_NUMBER() OVER (ORDER BY value) DESC) AS doctrine_rownum FROM yellowpage_keywords y0_ INNER JOIN yellowpages y1_ ON y0_.yellowpage_id = y1_.id INNER JOIN listings l2_ ON y1_.listing_id = l2_.id WHERE l2_.customer_id = ? AND y0_.origin_date >= ? AND y0_.origin_date <= ? GROUP BY y0_.name_crc, y0_.name) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 10 ORDER BY doctrine_rownum' with params ["111", "2013-01-01 00:00:00.000000", "2014-12-31 23:59:59.000000"]

The line :

ROW_NUMBER() OVER (ORDER BY value) DESC)

should look like

ROW_NUMBER() OVER (ORDER BY SUM(y0_.value) DESC)

In doModifyLimitQuery method I have modified:

                $pattern = sprintf('/%s\.%s\s+(?:AS\s+)?([^,\s)]+)/i', $column['table'], $column['column']);

to:

                $pattern = sprintf('/%s\.%s\s+(?:AS\s+)?([^,\s)]+)/i', str_replace('(', '\(', $column['table']), str_replace(')', '\)', $column['column']));

Now preg_match founds matching strings and OVER part of query is build correctly.

I checked other issues about this problem (which are marked as already fixed) and I have no idea why it's not working for me.

Thanks in advance!



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

Seems like a bit of information is missing: Is this issue related to the paginator API or not?

Comment by Maciej Grajcarek [ 17/Dec/14 ]

First of all - thank you for formatting my issue.
Secondly yes - issue is directly connected with paginator API.

Here is a code which should help you to replicate the problem:

            SELECT ypk.name keyword, SUM(ypk.value) total
            FROM YpBundle:YellowPageKeyword ypk
              JOIN ypk.yellowpage yp
              JOIN yp.listing l
            WHERE l.customer = :customerId
              AND ypk.originDate >= :dateFrom
              AND ypk.originDate <= :dateTo
            GROUP BY ypk.nameCrc, ypk.name
            ORDER BY SUM(ypk.value) DESC

        $dql = $this->getEntityManager()->createQuery($query);
        $dql
            ->setParameter('customerId', $customerId)
            ->setParameter('dateFrom', $dateFrom)
            ->setParameter('dateTo', $dateTo);
        $dql->setMaxResults(10);    

        $keywords = $dql->getArrayResult(); 
Comment by Marco Pivetta [ 17/Dec/14 ]

That doesn't involve the paginator, just DQL.

SUM() and computed values are not supported in the ORDER BY clause: you have to select them first. Try:

            SELECT ypk.name keyword, SUM(ypk.value) total
            FROM YpBundle:YellowPageKeyword ypk
              JOIN ypk.yellowpage yp
              JOIN yp.listing l
            WHERE l.customer = :customerId
              AND ypk.originDate >= :dateFrom
              AND ypk.originDate <= :dateTo
            GROUP BY ypk.nameCrc, ypk.name
            ORDER BY total DESC
Comment by Maciej Grajcarek [ 18/Dec/14 ]

If I will do it, it will result in a following query:

SELECT *
FROM (
	SELECT y0_.name AS name_0, SUM(y0_.value) AS sclr_1, ROW_NUMBER() OVER (ORDER BY sclr_1 DESC) AS doctrine_rownum 
	FROM yellowpage_keywords y0_
	INNER JOIN yellowpages y1_ ON y0_.yellowpage_id = y1_.id
	INNER JOIN listings l2_ ON y1_.listing_id = l2_.id
	WHERE l2_.customer_id = 111
		AND y0_.origin_date >= '2014-01-01'
		AND y0_.origin_date <= '2014-12-01'
	GROUP BY y0_.name_crc, y0_.name
) AS doctrine_tbl
WHERE doctrine_rownum BETWEEN 1 AND 10
ORDER BY doctrine_rownum

It's an incorrect query for an SQL Server. Take a look on this part:

ROW_NUMBER() OVER (ORDER BY sclr_1 DESC) AS doctrine_rownum

SQL Server do not support aliasing in OVER clause.

It should look like this, to work:

ROW_NUMBER() OVER (ORDER BY SUM(y0_.value) DESC) AS doctrine_rownum

It looks like this is the copy of this issue: http://www.doctrine-project.org/jira/browse/DBAL-788
I'm on doctrine version 2.5 which has patch from that issue included, but I have no idea why it's not working for me.

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

Maciej Grajcarek looks like it is an issue with your SUM function implementation. If you change your DQL to use ORDER BY COUNT(ypk.value) instead of ORDER BY SUM(ypk.value), does it work then? If so, there is something wrong with your SUM function and therefore not an issue with DBAL.

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

well forget about it I think I am wrong here.

Comment by Maciej Grajcarek [ 29/Dec/14 ]

Hi,
I will try to use COUNT as soon as I will be able to do that.

But I already tried other aggregation functions before and the result was exactly the same.

Comment by Luca Cerretani [ 16/Jun/15 ]

I get a similar error using this sql:

$sql = "SELECT table_one.id, table_one.number, table_two.name " . 
           "FROM table_one " .
	   "LEFT JOIN table_two	ON table_two.table_one_id = table_one.id " .
           "ORDER BY table_one.id DESC";

using the doModifyLimitQuery i get the uncorrect sql

SELECT * FROM (SELECT table_one.id, table_one.number, table_two.name, ROW_NUMBER() OVER (ORDER BY id DESC) AS doctrine_rownum  FROM table_one LEFT JOIN table_two ON table_two.table_one_id = table_one.id) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 1 ORDER BY doctrine_rownum

it should be

SELECT * FROM (SELECT table_one.id, table_one.number, table_two.name, ROW_NUMBER() OVER (ORDER BY table_one.id DESC) AS doctrine_rownum  FROM table_one LEFT JOIN table_two ON table_two.table_one_id = table_one.id) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 1 ORDER BY doctrine_rownum




[DBAL-512] Update schema not working on MsSql due to no support for alter identity Created: 06/May/13  Updated: 16/Dec/13

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

Type: Bug Priority: Major
Reporter: Flip Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: None
Environment:

Symfony 2.1, SQL Server 2008, driver: pdo_sqlsrv



 Description   

When running: php app/console doctrine:schema:update --force

[Doctrine\DBAL\DBALException]
An exception occurred while executing 'ALTER TABLE tableName ALTER COLUMN id INT IDENTITY NOT NULL':

SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'IDENTITY'.

According to this stackoverflow http://stackoverflow.com/a/1049305/1833322 MSSQL does not support this query.



 Comments   
Comment by Steve Müller [ 25/Nov/13 ]

Obviously SQL Server doesn't support this. So what solution would you expect here? IMO such a scenario should be avoided as there is no reasonable way to deal with changing identity columns. I guess most people are not even aware of this. The solutions available are very risky (creating new schema and then migrating data for example) and can take very long to be finished and is extremly error prone. I don't know if we should support one of those workarounds.

Comment by Flip [ 27/Nov/13 ]

The bug is about the SQL statement that does not work.
1. One solution could be to prevent generating this statement and issue a warning instead. (not preferred)

Two other solutions are mentioned in that Stackoverflow post I mentioned, namely:
2. Create a new table with identity & drop the existing table
3. Create a new column with identity & drop the existing column

That is just the way things have to be done in SQL Server (unfortunately), so it wouldn't be a workaround. I think this is a very reasonable way and in fact similar solutions to similar problems have already been implemented into Doctrine. See http://www.doctrine-project.org/blog/doctrine-2-4-released.html "ALTER TABLE support for SQLite (by hason) by creating new tables, moving all the data and then renaming."

Comment by Benjamin Eberlei [ 13/Dec/13 ]

Flip not many people actually use Sqlite in production, its more of a "play database" with Doctrine, wheras SQL Server carries much more weigh. I don't want to implement this kind of workaround, Steve Müller what do you think is safe here?

Comment by Flip [ 13/Dec/13 ]

Not to argue .. but it's not a workaround

Really .. if it is ever implemented it would have to be in a few steps because SQL Server does not accept this one-line command like that. So basically when you don't want this solution it means that will never be implemented.

Talking about safe .. mind that the safety should also be ensured in other area's like 1. don't run --force on a production database 2. make backups. So when regarding the safety it's a very narrow area to take a look at. The operation could be setup in such a way that if the new table/column could not be created then the old one doesn't get deleted. That way you have always the safety like a database transaction.

I guess no point in making a Proof of Concept, because the general outline of the problem is actually not that complicated ..

Thx guys for the input on this issue.

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

Benjamin Eberlei we could of course use one of the approaches Flip mentioned, but the question is what to do in case something goes wrong during one of the steps required to alter an IDENTITY column. If it is acceptable that there exist "dead" tables/columns in case something goes wrong, we could try implementing that.





[DBAL-95] Interbase/Firebird support Created: 26/Feb/11  Updated: 20/May/15

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

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


 Description   

Implemented support for Interbase/Firebird dialects



 Comments   
Comment by Andreas Prucha [ 17/Apr/15 ]

Hi all,

I've create a driver (or two) for Firebird.

The development branch is available at

https://github.com/helicon-os/doctrine-dbal.git DBAL-95-firebird

There are two versions of the driver: One is based on the ibase-api, there other on Firebird PDO.

I'd consider the ibase-version as almost finished, the PDO-Version is rather experimental.

Just send me a comment and maybe we can merge it into the official doctrine dbal (propably just the ibase-version, not the PDO-Version - at least not as long the driver problems are not fixed)

I've tested it with FB 2.5, but not with the upcoming FB 3 or Interbase.

___________________
ibase_firebird

The Ibase-Driver passes the complete Doctrine-Testsuite.

___________________
pdo_firebird

The PDO-Version does not, and it has some limitations which may be related to Firebirds PDO interface itself:

  • BLOBs: Runs into memory leaks if BLOBs are used quite quickly
  • PDO Firebird's transaction handling is quite strange and the reason why it does not pass the testsuite.
  • Nested Transactions (Savepoints): Fails despite Firebird supports them. -
  • Turns a negative signed 32bit integer into a positive unsigned, if the client is running a 64bit-system. I fixed this with a special IntegerType Class in a project, but that's a ugly workaround.
Comment by Andreas Prucha [ 17/Apr/15 ]

One more comment:

I am not sure how to handle one firebird-specificity: Firebird does not preserve the case of identity-names and converts them all-upper, unless they are quoted. The behaviour looks quite similar to Oracle.

Currently I do not normalize names as the Oracle-driver does, which leads to the problem, that they are all-upper in the database anyway, but automatically quoted keywords are lowercase.

Which behaviour would you guys prefere:

Normalize them All-Upper if not quoted (including keywords)? This would allow case-insensitive references in queries, because Firebird handles unquoted names as all-upper.
Quote everything to preserve case: Unfortunately this would require manual quoting in every query.

Personally I do not really like this all-upper-pattern, but having to quote every identifier everywhere looks even more cumbersome, so it's propably the best to follow the behaviour of the oracle driver and assume uppercase if not quoted.

Does any platform have configuration-options? It might be a solution to let the user decide about the naming, e.g.

setNamingConvention(ALL_UPPER | PRESERVE_CASE)

BTW - Is the doctrine-dev mailinglist gone? I wanted to send this there, but it came back with an error.

Andreas

Comment by Andreas Prucha [ 06/May/15 ]

Is it possible to setup a firebird test environment at travis?

Comment by Jürgen [ 20/May/15 ]

Hello Andreas,

good to hear about your work!

As a long time firebird user I can say that for me it is common to use column names case insensitive. So if you normalize all SQL commands to upper case (except quoted names) is the natural usage in firebird for me.

Jürgen





[DBAL-76] PostgreSQL Platform list* SQL code is in need of serious love Created: 12/Dec/10  Updated: 08/Jun/11

Status: Open
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: Unresolved Votes: 0
Labels: None


 Description   

The Postgres Schema code is very hard to read and inconsistent across the board. Some use pg_class, some pg_tables. Namespaces /Schema are not always properly checked for. There should be a really unified way on how to approach schema query issues.



 Comments   
Comment by Denis [ 08/Jun/11 ]

I'm not sure what this ticket is about exactly, but...

"Namespaces /Schema are not always properly checked for."

Usually, one would not want to specify them and set the search_path instead. Or are you meaning the schema analysis queries used internally? (If so, yes, it kind of sucks in that case, but note that there are a bunch of *_is_visible() methods, e.g. pg_catalog.pg_table_is_visible(rel.oid) which will strip out invisible schemas directly. This may be simpler than injecting schema references all over the place in that it also processes permissions.)

Also, note that PG has a whole bunch of pg_catalog views, which are available in the information_schema.





[DBAL-31] Move Schema related Creation code from AbstractPlatform to AbstractSchemaManager Created: 04/Jul/10  Updated: 28/Dec/13

Status: Open
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: Unresolved Votes: 0
Labels: None


 Description   

Move Schema related Creation code from AbstractPlatform to AbstractSchemaManager



 Comments   
Comment by Benjamin Eberlei [ 08/Aug/10 ]

Scheduled for Beta4

Comment by Roman S. Borschel [ 16/Aug/10 ]

If this task is more complex and requires larger refactorings we can re-evaluate it in a post-2.0 release.

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

Benjamin Eberlei Do you still remember what is meant by this ticket? Could be solved already...





[DBAL-1286] Add Cassandra driver Created: 25/Aug/15  Updated: 25/Aug/15

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

Type: New Feature Priority: Major
Reporter: Oleg Andreyev Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

http://cassandra.apache.org/






[DBAL-1046] Broken link Created: 12/Nov/14  Updated: 02/Dec/14

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

Type: Task Priority: Minor
Reporter: ted bohus Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

On the website, the link to download Doctrine DBAL 2.3.5 doesn't work...

http://www.doctrine-project.org/downloads/DoctrineDBAL-2.3.5-full.tar.gz

Thanks



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

ted bohus for now please use this link: https://github.com/doctrine/dbal/archive/v2.3.5.zip
We are having a look into the issue.





[DBAL-319] Doctrine\DBAL\Types\Type Created: 12/Aug/12  Updated: 22/Dec/13

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

Type: Improvement Priority: Minor
Reporter: Till Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None


 Description   

The API could be improved in the next major release:

  • Type::add() instead of addType()
  • Type::isRegistered() instead of hasType() (has sounds weird)

Etc.. I think the 'type' in the methods is redundant since Type is already the object I am dealing with.

I'd also like a remove() method to unregister a type at runtime. Would make testing a little easier and I don't have to check with hasType() etc..



 Comments   
Comment by Marco Pivetta [ 12/Aug/12 ]

I don't think those namings are really important.
There's one major change to do, which is to somehow get rid of the staticness of the `Doctrine\DBAL\Types\Type` class, and it is not a simple task.
It would also be interesting to set the type objects manually, such as a database `password` type that does encryption/decryption based on a given service. That cannot be done without staticness right now, which renders two different connections requiring the same functionality but with different parameters very difficult.

Comment by Till [ 12/Aug/12 ]

I agree on the static. But I'd also like the API to be cleaned up and the remove method.

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

API cleanup and staticness can be addressed in 3.0 for the first time. Otherwise we cannot keep BC. You can use Doctrine\DBAL\Types\Type::overrideType('someType', null); as a workaround for removing a type from the registry.





[DBAL-150] noSQL Shema Management Created: 21/Aug/11  Updated: 21/Aug/11

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

Type: New Feature Priority: Minor
Reporter: Alexey Shatunov Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

all



 Description   

I have a few ideas to improve DBAL and ORM management for php:
1. Reading if needed ORM schemas definitions from YAML(existing plugin)
2. Then migration it into the one of NoSQL databases(such as MongoDB or Memcache/MemcacheDB)
3. Creating transactional temporary-changes data layer in NoSQL for a current-changes due the webapp launch and running for each run
4. Periodically migrations of a current changes with migration script into RDB(for example by cron)

So we get:
Fast automatic shemas management(Symphony users - I say you "haha" because you have to do some unuseful things like "doctrine:build-schema" etc)
All advantages of RDB (like JOINS) in a data extraction
Full-independence in data changing due to webapp launch(and no transactions are needed) in the current NoSQL data-layer
Low load to the server because after extration we have a lazy return data into the RDB
In feature a simple caching any fast-changed data into NoSQL

What do you thinking about it?






Generated at Sat Aug 29 14:59:09 EDT 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.