[DBAL-444] OraclePlatform getSequenceNextValSQL not handling case/quoting properly on 11g Created: 10/Feb/13  Updated: 03/Jan/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3.2
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Major
Reporter: Max Milaney Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: oci8, oracle, sequence
Environment:

PHP version 5.4.11
Oracle 11g Instant Client version 11.2.0.3.0
Oracle Database 11g Enterprise Edition version 11.2.0.3.0 (x64)
OCI8 DBAL driver


Attachments: File example.php    

 Description   

I have an installer script that uses ORM SchemaTool to create the entities in the DB and then populates with basic data using basic EM->persist calls via ORM.

Sequence objects are created, and when using the 10g Instant Client everything worked correctly, however, upon upgrade to latest version of the Instant Client Oracle seems to be expecting consistent case for these schema objects. It appears as if they are being created with a quoted name as they are created in lowercase. OraclePlatform::getSequenceNextValSQL, however, generates "SELECT entity_id_seq.nextval FROM DUAL" and this fails with error "General error: 2289 OCIStmtExecute: ORA-02289: sequence does not exist".

Executing "SELECT "entity_id_seq".nextval FROM DUAL" directly on the DB returns the correct value.

I believe this may also impact the code in http://www.doctrine-project.org/jira/browse/DBAL-278



 Comments   
Comment by Max Milaney [ 10/Mar/13 ]

Hi there,
Wondering if there is any update on this? I'm having to use a workaround in my applications.
Cheers,
Max

Comment by Benjamin Eberlei [ 14/Mar/13 ]

Can you maybe show an entity definition with its sequence mapping?

Comment by Max Milaney [ 17/Mar/13 ]

Here you are mate. Please see attachment.

Comment by Benjamin Eberlei [ 04/Apr/13 ]

I cant seem to find the problem, in DBAL "lib/Doctrine/DBAL/Platforms/OraclePlatform.php" on line 171, the sequence statement is created with $sequence->getQuotedName($platform), but this only works if quoting is requrested for the sequence.

How do you actually create the sequence? Your entity doesnt have @GeneratedValue.

What does the create schema command say with "--dump-sql" flag? Is the SQL quoted?

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

Max Milaney Can you please test if this still exists in the current master branch? If so, can you please provide the information requested by Benjamin Eberlei ? Otherwise hunting this down is rather hard... Thank you!





[DBAL-474] SchemaManager / Connection on PostgreSQL platform does not respect filterExpression for sequences Created: 27/Mar/13  Updated: 16/May/14

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

Type: Bug Priority: Major
Reporter: jos de witte Assignee: Steve Müller
Resolution: Unresolved Votes: 1
Labels: postgresql, schematool
Environment:

Windows & Linux



 Description   

Dear Symfony team,

the filterExpression on AbstractSchemaManager seems not to work for sequences.

This only happens under postgres.

It seems the way the sequences are handled are the culprit: It tries to get min_value etc of sequences without matching sequence names to the filter expression in advance.

If for example access to the sequences is denied, (Different schema without permissions for the current entity manager), any higher-level ORM operations like generating migration versions fail.

--------------------- UPDATE

the context is when using migrations. Positive regexp expressions do not limit the migration to a single schema. eg ^schemaname.$
Instead, all sequences on the current database are returned.
When trying to limit a migration to a single schema consecutively this doesn't work.
We are using a per-schema connection, so this results in a lot of hassle for us.



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

Can you paste an exception trace? I see that filtering is applied to sequences, but your description seems to indicate this happens due to an SQL query much earlier?

Comment by jos de witte [ 24/Apr/13 ]

Dear Benjamin,

the context is when using migrations. Positive regexp expressions do not limit the migration to a single schema. eg ^schemaname.$
Instead, all sequences on the current database are returned.
When trying to limit a migration to a single schema consecutively this doesn't work.
We are using a per-schema connection, so this results in a lot of hassle for us.

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

jos de witte I think your issue got fixed in commit: https://github.com/doctrine/dbal/commit/8beb732fe9d5cd40a0d677f250d2be325482744f
This patch was first introduced in 2.3. Can you please confirm that this is fixed? Otherwise can you please provide a concrete example so that we can reproduce you issue?

Comment by Arnout Standaert [ 29/Jan/14 ]

I believe we are bumping into the same issue. Our webapp uses Migrations, but for our webapp we are limited to a certain schema within a bigger PostgreSQL database. We only have permissions on our own schema and public.
Now, listSequences in AbstractSchemaManager does filter the asset names correctly with the mentioned fix.

But the problem is with the step before, _getPortableSequencesList (see line 135 of AbstractSchemaManager):

        return $this->filterAssetNames($this->_getPortableSequencesList($sequences));

This function goes out and does a _getPortableSequenceDefinition on every sequence in the unfiltered list. For every sequence, the _getPortableSequenceDefinition in PostgreSqlSchemaManager performs a SELECT:

        $data = $this->_conn->fetchAll('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));

Now, our user role in the database doesn't have SELECT permissions on these sequences in other schemas, so the migration fails with a privilege error.

There should be some kind of filtering on the sequence list BEFORE the SELECT statement in the _getPortableSequenceDefinition function are performed, no?

Comment by Arnout Standaert [ 30/Jan/14 ]

I currently have a workaround running locally, which filters the sequences list before creating the PortableSequence's. This is probably hackish and a poor workaround, just posting here as a temporary solution and further illustration of the problem.

Altered line 135 in AbstractSchemaManager:

        return $this->_getPortableSequencesList($this->filterSequenceNames($sequences));

I added function filterSequenceNames() in AbstractSchemaManager for appropriate sequence filtering:

    /**
     * Filters sequence names if they are configured to return only a subset of all
     * the found elements.
     *5
     * @param array $sequenceNames
     *
     * @return array
     */
    protected function filterSequenceNames($sequenceNames)
    {
        $filterExpr = $this->getFilterSchemaAssetsExpression();
        if ( ! $filterExpr) {
            return $sequenceNames;
        }
        
        return array_values ( array_filter($sequences, function ($sequenceName) use ($filterExpr) {
                $sequenceName = $sequenceName["schemaname"].".".$sequenceName["relname"];
                return preg_match($filterExpr, $sequenceName);
            })
        );

    }

After these modifications, doctrine:migrations:migrate operations complete succesfully, even with our limited-permission database account.

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

Arnout Standaert Your fix looks promising and reasonable. Can you create a PR on the DBAL repo for that?

Comment by Viktor Sidochenko [ 15/Mar/14 ]

Why this fix is not in master?

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

Viktor Sidochenko because nobody has fixed it yet Feel free to provide a patch on GitHub.

Comment by Arnout Standaert [ 17/Mar/14 ]

I haven't gotten around to doing the PR on GitHub yet, I'm not yet too familiar with that.
I'll try to find some time for this the coming days.

Comment by Viktor Sidochenko [ 17/Mar/14 ]

Will be good. I`m not professional developer to make patches to upstream. So just voted for this issue.

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

Patch supplied in PR: https://github.com/doctrine/dbal/pull/546
jos de witte, Arnout Standaert, Viktor Sidochenko can you please test if the supplied PR fixes the problem?

Comment by Doctrine Bot [ 16/May/14 ]

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





[DBAL-423] Type GUID = VARCHAR(255) on platforms that don't have a native GUID support Created: 25/Jan/13  Updated: 08/Mar/14

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

Type: Improvement Priority: Minor
Reporter: amr Assignee: Steve Müller
Resolution: Unresolved Votes: 1
Labels: None


 Description   

I'm using MySQL with entities that have GUID ids. Therefore I'm using @ORM\Column(type="guid") for the ORM mapping. As MySQL does not have a native GUID data type, it gets mapped to type="string" with a default length of 255 -> VARCHAR(255). I don't really understand why we don't limit the length to 36, which is the fixed length for GUIDs. You could even think about using CHAR(36) for MySQL.

-> see Doctrine\DBAL\Platforms\AbstractPlatform -> getGuidTypeDeclarationSQL()



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

Patch PR: https://github.com/doctrine/dbal/pull/465

Comment by Michael Kühn [ 28/Feb/14 ]

With the latest support for the MyISAM-Engine merging this pull request would save some trouble.

Background/Steps to reproduce:
If you have 2 entities with guid/uuid as primary key, @ManyToMany/@JoinTable fails on MyISAM, because it would create a jointable with 2 VARCHAR(255) columns and would apply a combined primary key on these two columns. But MyISAM doesn't support keys longer than 1000 bytes so you can't create the jointable.

See: https://dev.mysql.com/doc/refman/5.6/en/myisam-storage-engine.html

The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.

In my opinion this isn't just a "minor" issue but a major because some people can't run on MySQL with InnoDB for some reason.

Comment by Marco Pivetta [ 28/Feb/14 ]

Michael Kühn MyISAM is niche support for the ORM - using a custom type is perfectly fine in such a case.

Comment by Michael Kühn [ 04/Mar/14 ]

Marco Pivetta I agree, but i don't see why we would assume a GUID/UUID - which is per definition 36 chars long - as a 255 char long string. It would save storage space (for platforms don't supporting a native uuid type) and circle around at least 1 barrier if using MyISAM.

By the way, the PR is missing something. While it works perfectly fine the first time, every orm:schema-tool:update would output the guid-columns every time if you don't specifiy "length=36" and "fixed=true" on every GUID-@Column. IMHO the GUID-Type should implicit this both attributes in this pull request so you don't get column updates that don't change anything.

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

Michael Kühn I get your point and thanks for pointing out the remaining issue. The problem is caused by the comparator which detects differences in the column definition because the length and fixed attribute are hardcoded in the platform which is beyond comparator's knowledge. Still I think this can be fixed easily but as long as Benjamin Eberlei is of the opinion that this change is a minor BC break, this PR won't make it into the master branch.





[DBAL-801] add SECOND, MINUTE, WEEK into DATE_SUB, DATE_ADD Created: 04/Feb/14  Updated: 16/May/14

Status: In Progress
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Minor
Reporter: gondo Assignee: Steve Müller
Resolution: Unresolved Votes: 0
Labels: None


 Description   

currently only HOUR, MONTH, YEAR options are implemented
would be nice to have all of them but for now at least the major one would be fine, so to complete the list, i would like to see:
SECOND, MINUTE, WEEK to be implemented

im not sure if all the platforms are capable of this, so if anyone can verify that would be great.
after that, implementation is simple copy/paste of existing code with very minor changes.



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

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

Comment by Doctrine Bot [ 16/May/14 ]

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





Generated at Mon Jul 28 22:41:45 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.