php 5.3/win, doctrine 1.2 svn, ATTR_QUOTE_IDENTIFIER = true, ATTR_USE_DQL_CALLBACKS = true

Attached test case.

I find this issue as critical one now, because when I use e.g.

$this->option('orderBy', 'name');

in a model's definition (not ref class), then when other model is related m2m with it, a query loading both of them with relations will fail because of name column duplicated in ref table.

I am having the same issue with an equivalent test case. For some reason, the 'orderBy' option on the target of the join (set by calling the option() function inside the setUp() method of the model class, not the ref class) is being applied to the relation table. After digging through the 1.2.2 tag a bit, I found altering line 1319 of Query.php as follows seems to fix the problem (at least against my test cases):

if (isset($map['relation'])) {
if (isset($map['relation']) && $map['relation']->getTable() === $map['table']) {

I'll do some more thorough testing and submit a patch if I find time.

I had this issue as well. suhock's solution fixed it for me. It would be nice if this was merged in to a bug fix release of Doctrine 1.x.

I ended up creating a new ticket, DC-651, which addresses a more general problem with the orderBy feature. You should use the attached Ticket_DC651.patch instead, as I found the solution I provided here is not completely correct and does not pass all test cases.

Windows Seven - WAMP 2.0i

Windows Server 2003. PHP 5.2.12. MS SQL Server 2008

Should mention I upgraded from 1.1.6 to 1.2.2, so the problem might be present in earlier 1.2.x versions as well.

XP Xamp

Is this still a problem? I am not sure if this can be patched easily. The limit subquery algorithm is flawed deeply but also a core part of the current way Doctrine 1 works.

Hi Jon

This bug is still an issue for me but I think it may be VERY hard to patch since it seems very core to the way Doctrine 1 works.

The project I am working on at the moment is a visual query builder that is highly reliant on Doctrine. In order to prevent users from making queries that would trigger this bug I am currently giving an alert when ever a query that would trigger this bug is created.

It would be great if this were patchable but if not I should be able to get by.

Does Doctrine 2 fix this problem?

Thanks for the help.

Will Ferrer

In Doctrine 2 the limit subquery algorithm does not exist. It is now up to the developer to write the query to handle the scenario instead of Doctrine "trying" to automate it for you. Since the situation where it is needed it is so rare, and each case can be slightly different it is better to let the developer handle it in those cases.

Hi Jon

Does that mean that Doctrine 2 can no longer intelligently handle limits with many relationships, and doesn't have the ability to hydrate a return with sub arrays in it for many relationships?

Thanks again for your help.

Will Ferrer

That is correct. We do not automatically try and limit the relationships with a "limit subquery" as it causes more problems then it helps.

Hi Jon

I was thinking about what you said here – that the "limit subquery" causes more harm than good. It occur to me that I really do like being able to use this method (it comes in very handy very often) but some times it would really help to be able to turn it off (the bug in this thread is a good example such a time). So I figured why not just make an option to turn it off and have the best of both worlds? I looked at the code and found it was really very easy to put in a property which can be set on the query object to enable or disable the use of the limit subquery.

I made the change in my code base and found it very useful for several situations I was dealing with in my own project.

The one thing I couldn't do is make a test case for this new feature – I couldn't find an existing test case that was actually triggering the limit subquery as I understood it to work (I couldn't find a test case that would put an WHERE IN constraint with all the ids gathered in the limit subquery. I tried some test cases that I THOUGHT would activate this feature but it didn't seem that they did).

After adding this feature to my code base I also wanted a new hydrator – one that worked like scalar but would put in array key names that were the same as the ones you would see in HYDRATE_ARRAY. I noticed that HYDRATE_SCALAR already had the ability to do this but it required that a value of false be passed into the $aliasPrefix argument of the _gatherRowData function. I made a custom hydrator I call HYDRATE_ARRAY_SHALLOW that passes in this false value. I then realized this might be handy to add to doctrine so I integrated it into my code base and made a few test cases for it.

There are 2 patches I am now attaching this to thread:

disableLimitSubquery_2010-06-10_Doctrine_1.2_SVN.patch – this patch adds the disableLimitSubquery property to query objects so that users can turn off the use of the subquery for those times when they don't want to use that behavior (fixing the bug in this thread and probably others)

and

disableLimitSubquery_and_HYDRATE_ARRAY_SHALLOW_2010-06-10_Doctrine_1.2_SVN.patch – this is the same as the first patch but also contains the HYDRATE_ARRAY_SHALLOW hydration type I mentioned above (which tends to go well with disableLimitSubquery turned on).

I put these in 2 patches incase you liked 1 feature but disliked the other.

If you like either of this features I would be very happy to see them added to doctrine.

Also if you have any advice on how to improve these features (or info on how to make a good test case for disableLimitSubquery) please let me know.

Hope you are well.

Will Ferrer

Reopened because I added a patch that I think in essence fixes the issues.

Thanks for the issue and patches.

Fixed here http://github.com/doctrine/doctrine1/commit/2ad78e62e360133efc04bf6897bf679c7f3d833b

individual patch for this issue with out other features included

adding test cases for these features

reopened because I posted some test cases to add to doctrine along with the patchs previously posted

symfony 1.4.5, postgresql 8.4, debian lenny, nginx

There are 2 solutions to your problem. The first is to change the sequence name in the schema, the second is to change the doctrine configuration.

The default behavior of doctrine is to add a "_seq" to each sequence name. If you remove this part from you sequence name then it sould work as expected. The second option is to change the behavior of doctrine with the following configuration parameter:

 
Doctrine_Manager::getInstance()->setAttribute(Doctrine_Core::ATTR_SEQNAME_FORMAT, '%s'); 

default: Doctrine_Manager::getInstance()->setAttribute(Doctrine_Core::ATTR_SEQNAME_FORMAT, '%s_seq');

Problem are quotes in sequence name.
Sequence names in schema built from existing database have quotes in their names, e.g ' "Address_seq" ' and Doctrine try to execute sequence with quotes but in database names exist without quotes.

Temporarly I fixed it by add str_replace() into importer from PostgreSQL - now in schema sequence names are without quotes.

PHP 5.2.3 / Symfony 1.4.6

Same here,

If i use this yaml file:

Page:
actAs:
NestedSet:
hasManyRoots: true
rootColumnName: root_id
Versionable:
versionColumn: version
className: %CLASS%Version
auditLog: true
Timestampable:
created:
name: created_at
type: timestamp
format: Y-m-d H
updated:
name: updated_at
type: timestamp
format: Y-m-d H
I18n:
fields: [name]
columns:
name: string(255)

PageVersion and PageTranslation Models don't get generated if i use 'build-models-yaml'. Have to create the Models manually then it works fine.

WinXP, Apache, PHP 5.2.14

Windows 2003 Server, Oracle 10g, Symfony 1.4.8

Some very similar issue were reported and resolved here http://trac.doctrine-project.org/ticket/1038.

Reason of issue was founded. It appears because i'm using oci8 driver, and this drivername not be listed in if statement on line 1401 in Doctrine/Query.php:

LINE 1401: if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'mssql' || $driverName == 'odbc') {

I changed to:

LINE 1401: if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'oci8' || $driverName == 'mssql' || $driverName == 'odbc') {

Sorry, but i don't know how to create patch diff file.

Tested on CentOS 5, Ubuntu 10.4, MYSQL and MSSQL databases

Ubuntu 10.04 + PHP 5.3.3 + Symfony 1.4.8

Same ticket on symfony trac because I'm not sure whose fault is it

http://trac.symfony-project.org/ticket/9398

Also some new error path there

Test Case of Non-Equal Self-Referencing Relations, based on #DC-329.

Failure occures in line 75 of the test case file. This should not happen!
Only the parent object is modified in line 73 and saving should not interfere with the relations.

All

upping to blocker since this breaks very basic queries

MySQL 5, PHP 5

windows ,wamp,php

PHP Version 5.2.14
Apache 2
MySQL
Windows Xp

MySQL 5.1.37
PHP 5.2.11
symfony 1.2.11 DEV

symfony 1.4.13
PHP 5.3.6
Postgres 8.4.8

Original issue: DC-363 (http://www.doctrine-project.org/jira/browse/DC-363)

There are some additional comments in there that explain the issue as currently being seen in released versions of Doctrine 1.2. Can I help verify that this is the same ticket?

What is needed to help with debug (in addition to the extra information already provided)?

meet the same issue and it really blocked my project, seems no one will maintain 1.x. I'm considering whether to replace symfony 1.4 with 2.0.

Windows 7 64 bits, PHP 5.2.11, Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi, Symfony 1.4.13

Windows 7 64 bits, PHP 5.2.11, Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi, Symfony 1.4.13

Windows XP SP3, Apache 2, PHP 5.3, MySQL 5.1.36, Symfony 1.4.8, Doctrine 1.2.3

I fail to see why you would use an alias that is equal to the model name.

To clarify again: Using an alias equal to the model name is the same as not using an alias at all. The built sql is the same afterwards (and the addPendingJoinCondition behaviour is the same as well).

I would not use an alias equal to the model name myself, but sometimes this is used in Doctrine core, e.g. in Doctrine_Relation_ForeignKey around line 60. So to solve the problem either all the core queries, where no alias or an alias equal to the model name is used, have to be rewritten or the behaviour of a respective query has to be changed somehow so that applying an addPendingJoinCondition is possible even in the given case.

Symfony 1.4, Doctrine Version: 1.2.0-BETA3

Any comments? Will it be fixed??

Hi, I'd like to take a look but can you make a failing test case that I can run so that I can see if I can come up with a patch that fixes your case and doesn't break anything else.

Hi

I had similar problem but after several hours i did work it out

Try to make get method in your model for getting field which has NULL value in database

public function getUserRealId()

by making second argument false u force doctrine not to lazy load value and extra sql query is not created

regards

Has anyone been able to reproduce this in a test case? I am not having much luck so far.

I've seen this a ton of times. Basically when it loads related records through the Hydrator using leftJoin() and gets NULLs back. BUT it doesn't save the fact that the related records are NULL. So when you actually do call to getRelated objects it sees that it doesn't have the value cached and runs the query again.

Let me know if I should show you the problem in the Doctrine code base.

PHP 5.2.11

I second this.

When you UPDATE row with save() method of Record after setting identifier by assignIdentifier() doctrine removes columns updates from SQL which are set to default values same as it would do with INSERT. Other columns are updated correctly. This is sure a bug.

You can workaround this by setting the value as NULL instead of the actual default value. (Which I wouldn't recommend).

If you look at Doctrine_Collection::toHierarchy() you'll notice that there is NO reference to root_id, therefore it treats the entire collection as 1 tree (which is false). The bug is 100% repeatable. I've made a fast walkaround ba adding a multi-tree hydrator:

class MultiRootHydrator extends Doctrine_Hydrator_RecordDriver
{
  public function hydrateResultSet($stmt)
  {
    $result = parent::hydrateResultSet($stmt);

    $collection = array();
    foreach ($result as $item)
    {
      if (!isset($collection[$item->root_id]))
        $collection[$item->root_id] = new Doctrine_Collection($result->getTable());

      $collection[$item->root_id]->add($item);
    }

    $result = new Doctrine_Collection($result->getTable());
    foreach ($collection as $tree)
    {
      $tree = $tree->toHierarchy();
      $record = $tree->getFirst();

      $result->add($record, $record->root_id);
    }

    return $result;
  }
}

It should clarify the problem.

I think it was intended that you would only convert a single tree to a hierarchy. What would the structure of the returned data be like?

A Doctrine_Collection with root nodes seems to work fine. It allows to traverse the tree for each root.

Windows XP
Apache 2.2
PHP 5.3
Doctrine 1.2.1
Symfony 1.4

Doctrine is installed as a Symfony plugin. Using the latest Symfony from SVN.

Is there any patch that could be provided meanwhile? This is quite a problem on a live website.

Is this not a critical issue for Doctrine's cache? It's been up for 2 weeks with not even a comment...

Hi, what are you calling that is invoking _getCacheKeys()? The only methods that call it are the deleteBy*() methods. It is expected that these methods have to get the entire list of cache keys from the driver in order to perform the delete by operation. These cache clearing operations should probably be done in the CLI environment where the memory limits are higher. If you want to avoid _getCacheKeys() being invoked, then you must not use the deleteBy*() methods.

Thank you for commenting. Yes, I am using deleteByRegex() since I need to expire some result cache entries upon an update operation. What other choice do I have if I wish to keep using the result cache offered by Doctrine? Is there any other mechanism?

Can't _getCacheKeys() be optimized some way?

No, it is not able to be optimized anymore. It has to load all the keys into a php array in memory in order to loop over them to compare against the regex. You should probably not be doing cache clearing operations in the browser under apache. If you do, you'll need to raise your memory limit.

My code actually had a few of these calls and I've now removed use of the result cache with Doctrine. What you're writing means the result cache is not usable for dynamic websites. IMHO, it's a good practice to cache results and remove them once an update is made to the data (which naturally can happen due to an update from a user). However, if that by itself creates an overload on the server (and as you know even a temporary memory abuse leads to an overload), I cannot see how it can be useful.
Please tell me if you think there's a way the results cache can still be usable for a dynamic website.

Thanks

This is the only way to allow more complex delete functionality. How you use it, is not up to us. We intended that cache clearing is done from the command line or in an environment where the memory limit is high enough to be able to load all those keys. It may not be able to be used by everyone, if it is not working for how you are using it then you will need to think of another solution I suppose.

Thank you for your response and I'll think of another solution for my application.

I did dive into the code and there's a relevant optimization that could be made.

_getCacheKeys() is actually creating another array for all the cache keys which needlessly increases the memory used.
If the deleteBy*() method would be implemented at the driver level (such as with Apc.php) and not at the general level (Driver.php as it is now) this array would not have to be created. It won't be such a code bloat and would surely lessen memory use.

There could be a way around the problem which also implements another feature I miss with the results cache. By allowing some sort of cache tagging to mark the items that may need to be deleted we could easily delete relevant entries. I'll describe the interface here.

Instead of
$q = $q->useResultCache(true, 86400);

There should be
$q = $q->useTagResultCache('SomeTag', true, 86400);
which does the same PLUS update a cached variable (such as 'Doctrine_Result_Cache_Tag_SomeTag') which references the result cache keys of 'SomeTag'.

We can then easily implement deletion of relevant result cache entries with

deleteByTag('SomeTag')

which would read 'Doctrine_Result_Cache_Tag_SomeTag' to figure out which entries should be removed from the cache.

I'm pretty sure my usage scenario is not marginal but let me know what you think.

This is already possible if I understand what you describe.

$q->useResultCache(true, 3600, 'key_to_store_cache_under');

Now you can do:

$cacheDriver->delete('key_to_store_cache_under');

Also what you describe useTagResultCache() and keeping up with our own list of cache keys is the way it used to be and was changed to this after worse performance problems were discovered with that approach.

Perhaps I've been misunderstood so I'll try explain from the start.

In my system a few queries do relate to the same pieces of information. That information can be updated by a user and thus I would need to remove anywhere between 0 and 50 related result cache variables. I cannot easily name each and every one of my queries thus giving a specific key name doesn't help. So what I did was to prefix the name of each of the queries to indicate that I'll know how to remove them. I may have thousands of results cached and would need to clear just a few. That's why I use the deleteBy*() which proves to be extremely inefficient as it retrieves ALL the keys in my cache driver and not only the Doctrine related ones.

I really don't know how it has been implemented before but what I suggest wouldn't hurt performance as tagging would be an optional addition managed with another variable. If you think that won't b useful to other Doctrine users, I'll simply implement it for my system.

Thanks

I think the best solution is the one you suggested earlier. That each cache driver should directly implement this functionality and bypass the creation of the array. What do you think? It is backwards compatible so that way we can commit it in 1.2.

Bypassing the array is a required optimization which is easy to implement but it's not really a solution to the problem I'm facing and I believe is common enough (Zend_Cache for example implements tagging) and need to be offered. As it'll be 2 new functions that will implement tagging only when specifically requested, it'll also be backward compatible. The only thing I'm not sure about is if an implementation of some locking mechanism would be needed for the cached variable which would hold the list of cache keys for a specific tag.

Let me know what you come up with and we'll have a look at including it in the next 1.2.x release.

Bypassing the extra array is still not good enough and IMHO the whole idea of deleteBy() should NOT be used if many such requests could be made, as is my case.

What I've done now is what I mentioned before with a patch that is quite ugly.

In Doctrine/Query/Abstract.php right after the line

$cacheDriver->save($hash, $cached, $this->getResultCacheLifeSpan());

I've added

                if (!empty($GLOBALS['rcache_users_in_query'])) {
                	MyCache::keepRelatedCacheKey($GLOBALS['rcache_users_in_query'], $hash);
                }

Which saves another cache key which holds the hash tags that would have to be deleted on an update.
My global variable is actually an array as a Doctrine query result may be associated with more than one user and possibly other parameters.
Before calling the $q->execute(), I simply update this variable.

When a user on my system does the update, I then delete all relevant Doctrine keys with something like

		if (is_null($cacheDriver)) $cacheDriver = Doctrine_Manager::getInstance()->getAttribute(Doctrine_Core::ATTR_RESULT_CACHE);
		
		foreach($arKeys as $key) {
			$cacheDriver->delete($key);			
		}

and then delete my other cache key.

This solution works well for me. Sorry I cannot make a nice Doctrine patch for it as I'm not well versed with your code. I still believe it should be supported by Doctrine with an optional extra parameter for $q->useResultCache()

Thanks

Hi Amir,

Zend_Cache does not implement tagging for either APC or Memcached backends, see the documentation. It also likely never will, all requests for this functionality have been closed with Wont Fix.

I don't think the deleteBy methods should have ever been implemented. When initially implemented they cached a "doctrine_cache_keys" variable to store the keys known to Doctrine. This however led to a crippling bug that would crash my production servers after a few hours. Not even a friendly "out of memory" limit, but a slowdown and eventual crash. Please see DDC-460 for details. Note that I don't use the magic delete methods, just simple saves with timeouts and this was affecting me.

I fixed the solution as you've seen using the _getCacheKeys() method. I don't believe this functionality should have ever been added to Doctrine to begin with, but this is what we have to work with. It should be the responsibility of the cache store to handle tagging and such, not poorly hacked on with application code.

As it stands, the current implementation doesn't affect people who aren't even using this functionality, as it should be. As Jon suggested, you shouldn't be using this in the context of a page request. Use a CLI script or work on another solution. Your idea of tracking your keys in application code is a good idea, but it doesn't belong in Doctrine imo.

Thanks David for your comment.

I agree with you that my implementation should not belong in Doctrine and that tagging should have been a part of the cache backends.

Continuing with the same logic you've presented, deleteBy...() functionality **should be removed** from Doctrine if it causes the system to crash as it does so in an obnoxious way so that it would take too long for most developers to notice this is where the problem lies. It has certainly taken too much of my time and efforts and I'd rather save the pain from others.

We had the exact same problem. We used a "deleteAll()" of a ApcCache object and ran into the "allowed memory size exhausted" pitfall. We helped ourselves with a new class that extends ApcCache and uses the simpler apc_clear_cache function.

 
namespace Foo\Cache;

class ApcCache extends \Doctrine\Common\Cache\ApcCache
{
    /**
     * Delete all cache entries. Memory saving version...
     *
     * @return bool
     */
    public function deleteAll()
    {
        return apc_clear_cache('user');
    }
}

 
use Foo\Cache\ApcCache as Apc;
...
$this->_apc = new Apc();
$this->_apc->deleteAll();

This doesn't return the ids of the deleted entries like the original function but we don't need that. So this works fine for us.

symfony-1.3.4 and doctrine-1.2.2

Can you provide your changes as a patch/diff with a test case?

Hi Jonathan,

I'm not so hot at making patches or test cases, but it should be fairly easy if you know what you're doing...

Just try to create and save two records with the same hard-coded slug and the second one will fail with an ugly MySQL crash.

If you add the lines

        } else { // Still check for slug uniqueness when you insert
            $record->$name = $this->buildSlugFromSlugField($record);

to the Sluggable::preInsert() method then this problem is averted and the test cases will pass.

I have been running with this modification in our production version of Doctrine since I first reported this in May and it all seems to work well.

If you really need me to figure out how to make a patch and test case please re-comment on this ticket and I'll see what I can do when I have some free time.

C

Zend Framework, Ubuntu 9.10, MySQL

Are you able to reproduce this in a test case?

We reverted to Doctrine 1.2.1 after realising the bug to confirm it was Doctrine 1.2.2 that was the cause for the problem. And as a result the records with NULL dates in the DB became NULL in the Models.
But when using Doctrine 1.2.2, the NULL dates became '0000-00-00' in the Models.
I don't have any other way to reproduce this error.

Were you able to identity which changeset it was? You can read about creating test cases here http://www.doctrine-project.org/documentation/manual/1_2/en/unit-testing

So far I am not able to reproduce the error you described.

I'd like to fix this. Did you ever figure out which changeset introduced the issue? I've been trying to figure it out myself.

With 1.2.3 this works for me fine with both TIMESTAMP and DATE fields.

YAML

        date_of_birth:
            type: date

BASE MODEL

        $this->hasColumn('date_of_birth', 'date', null, array(
             'type' => 'date',

             // try these two
             // 'notnull' => false,
             // 'default' => null
         ));

YAML

        exported_at:
            type: timestamp(25)
            notnull: false
            default: null

            # in this model I have everything to make sure it accepts and defaults to NULL

BASE MODEL

        $this->hasColumn('exported_at', 'timestamp', 25, array(
             'type' => 'timestamp',
             'notnull' => false,
             'length' => '25',
             ));

You may try adding these to your YAML and (base) models

YAML

    fieldname:
         . . .
        notnull: false
        default: null

BASE MODEL

        $this->hasColumn('fieldname', . . .
             . . .
             'notnull' => false, // <<<<<<<
             // 'default' => null, // <<< maybe, probably not needed
             ));

I hope it helps.

We made a bunch of changes/fixes related to oracle. This was to fix another bug I believe. I can't remember the user that is responsible for these changes. Does anyone else remember or know anything?

PHP 5.3.1 (cli) (built: Feb 11 2010 02:32:22)
mysql Ver 14.14 Distrib 5.1.41, for apple-darwin9.5.0 (i386) using readline 5.1
Doctrine version 1.2.2 from SVN: http://doctrine.mirror.svn.symfony-project.com/tags/1.2.2/lib/Doctrine.php

Test case for the bug

Mac OS X 10.6

added testcase here
https://github.com/rahx/doctrine1/commit/ba5628abaa5b3d60638d833d90b1cf439504d560

Window, PHP5, Symfony

this is the same issue as DC-735

doctrine 1.2.4, symfony 1.4, snow leopard, php 5.3.1, postgresql 8.3

Here is the test updated with the current ticket number.

Updated. Now it should work/not work as expected.

This isn't a blocker anymore because of the workaround i've found.

• remove autoincrement
• add sequence name manually

Example:

Authority:
columns:
a_id:

name:

Current HEAD of Doctrine 1.2

This seems to be a regression caused by the fix for DC-558 which added the MODEL_CLASS_PREFIX to the $_toPrefix in Doctrine_Migration_Diff::generateChanges.

While this fixed the issue when generating diff from models to YAML, it has now created the reverse issue for generating diffs from YAML to YAML - as the models generated for the "from" schema do not get MODEL_CLASS_PREFIX prepended and so now this command will drop all existing tables and recreate.

I believe the fix may be to amend as:

        $from = $this->_generateModels(
            Doctrine_Manager::getInstance()->getAttribute(Doctrine_Core::ATTR_MODEL_CLASS_PREFIX) . self::$_fromPrefix,
            $this->_from);
        $to = $this->_generateModels(
            Doctrine_Manager::getInstance()->getAttribute(Doctrine_Core::ATTR_MODEL_CLASS_PREFIX) . self::$_toPrefix,
            $this->_to                
        );

Since it seems that when presented with a folder of models _generateModels ignores the prefix anyway. However, I'm not sure of other impacts possible as a result?

I've been using and testing the modified version above locally for some time and it seems to work as expected. Any chance of this making it into core? Otherwise, the migrations feature is completely unusable when working YAML-YAML and using model prefixes on the newly released 1.2.3

Has anyone been able to produce this in a test case?

I've attached a diff file with the DC755TestCase and the required from and to YAML schema files to reproduce this bug. I wasn't sure whether you prefer like this or as a git commit?

Also attached a diff file of my proposed change to Doctrine_Migration_Diff to resolve this, but as I say unsure if it has implications on other migration types.

Fixed by http://github.com/acoulton/doctrine1/tree/DC-755

Windows XP sp3

Anyone?

Am having the same issue!

Eventually found the orderBy option on both the model and on relationships and was over joyed as I'd been trying to find a way of doing this, but it didn't work!!

Found the patch http://www.doctrine-project.org/jira/browse/DC-651 which solved the first problem I encountered but now I'm getting this.

Had a look at the code and tried only setting the orderBy if the current component is actually referenced in the from sql part of the query.

Looking at what is selected from, $this->_sqlParts['from'], it appears that the main table ($map['table']->getTableName()) and $sqlAlias is include with a zero index and joined tables are keyed by their $alias (or at least the content of these variables within the loop).

From this I believe I could detect if the current entry in the loop was from a table that was in the current "FROM" part of the query by looking for the table name and alias as an entry or the current alias as a key in the "FROM" array.

Within my sub query the orderBy valeus would still get applied but only in the subquery and not in the main query where they were included before, out of scope.

With patch DC-651 applied my code in Query.php at line 1315 noew looks as follows:

                // Note: Only include orderBy values for tables we're actually selecting from (both the root table or
                // tables referenced from it)
                if (in_array("{$map['table']->getTableName()} {$sqlAlias}", $this->_sqlParts['from']) || array_key_exists($alias, $this->_sqlParts['from'])) {
                    if (isset($map['relation'])) {
                        if (isset($map['ref'])) {
                            $orderBy = $map['relation']['refTable']->processOrderBy($sqlAlias, $map['relation']['orderBy'], true);
                            if ($map['relation']['orderBy'] && $orderBy == $map['relation']['orderBy']) {
                                $orderBy = $map['relation']->getOrderByStatement($sqlAlias, true);
                            }
                        } else {
                            $orderBy = $map['relation']->getOrderByStatement($sqlAlias, true);
                            if ($orderBy == $map['relation']['orderBy']) {
                                $orderBy = null;
                            }
                        }
                    } else {
                        $orderBy = $map['table']->getOrderByStatement($sqlAlias, true);
                    }
                } else {
                        $orderBy = null;
                }

Am I correct in my assumptions?

All

Test case attached.

There needs to be some kind of caching on the pre/postHydrate calls, which is done throughout the Doctrine_Hydrator_Graph, except for when the relation is one-to-one.
This is done just above, for relations that are not one-to-one.

Unfortunately, I couldn't work out how to implement it for one-to-one.
Probably a simple fix for someone familiar with the code

Correct Test Case

Correct Test Case

I was probably a little too tired to think this through clearly on a Friday after a long weeks work!
Patched attached, which doesn't break any of the existing unit tests.

php 5.3, doctrine 1.2, Symfony 1.4, mysql

Doctrine 1.2, Symfony 1.4.6, MySQL, Postgresql

to complete this bug, I think it's also a problem on DC 2..

There is already an (undocumented?) option "prefix" that allows this.

$cacheDriver = new Doctrine_Cache_Apc(array('prefix' => 'MY UNIQUE SALT'));

windows

Windows 7 32 bits with Apache 2.2.x, PHP 5.2.17, Sql Server 2008, Symfony 1.4.11 and Doctrine 1.2.4

Windows server 2003 PHP 5.2.17 / XP PRO 32bits XAMPP PHP 5.3.5
MSSQL / MYSQL
Symfony 1.4.6

Windows 7 64Bit
PHP 5.3
Symfony 1.4

Suggested patch

PHP 5.3.5-1ubuntu7.2ppa1~lucid with Suhosin-Patch (cli) (built: May 7 2011 03:12:27)
Zend Engine v2.3.0
Xdebug v2.0.5
Turnkey LAMP 10.04 LTS x86_64
Symfony 1.4.11
mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1

Linux, Ubuntu 12, php 5.4

latest doctrine 1.1 revision, linux x64, PostgeSQL 8.4, php 5.2.10

here is the working test case:

class Doctrine_Ticket_DC49_TestCase extends Doctrine_UnitTestCase 
{
        public static $query;
        public static $params;

        public function init()
        {
                $this->dbh = new Doctrine_Adapter_Mock('pgsql');
                $this->conn = Doctrine_Manager::getInstance()->openConnection($this->dbh);
                $this->conn->addListener(new PPB_QueryListener());
        }
        
        public function prepareTables()
        {
                $this->tables = array('PPB_Section', 'PPB_Post', 'PPB_PostVote');
                
                parent::prepareTables();
        }
        
        public function testBug()
        {
                $q = Doctrine_Query::create()->from('PPB_Post p')
                                             ->where('p.section_id = ?', 1)
                                             ->addWhere('p.id = ?', 15)
                                             ->leftJoin('p.Votes v WITH v.user_id = ?', 10)
                                             ->limit(10);
                                             
                $q->execute();
                
                $this->assertEqual(self::$query, "SELECT p.id AS p__id, p.section_id AS p__section_id, p2.id AS p2__id, p2.post_id AS p2__post_id, p2.user_id AS p2__user_id FROM p_p_b__post p LEFT JOIN p_p_b__post_vote p2 ON p.id = p2.post_id AND (p2.user_id = ?) WHERE p.id IN (SELECT doctrine_subquery_alias.id FROM (SELECT DISTINCT p3.id FROM p_p_b__post p3 LEFT JOIN p_p_b__post_vote p4 ON p3.id = p4.post_id AND (p4.user_id = ?) WHERE p3.section_id = ? AND p3.id = ? LIMIT 10) AS doctrine_subquery_alias) AND (p.section_id = ? AND p.id = ?)");
                
                $this->assertEqual(implode(",", self::$params), '10,10,1,15,1,15'); // correct order of params
        }
}

class PPB_Post extends Doctrine_Record
{
        public function setTableDefinition()
        {
                $this->hasColumn('section_id', 'integer');
        }
        
        public function setUp()
        {
                $this->hasOne('PPB_Section', array('local' => 'section_id',
                                                   'foreign' => 'id'));
                                                   
                $this->hasMany('PPB_PostVote as Votes', array('local' => 'id',
                                                              'foreign' => 'post_id'));
        }
}

class PPB_Section extends Doctrine_Record
{
        public function setTableDefinition()
        {
        }
}

class PPB_PostVote extends Doctrine_Record
{
        public function setTableDefinition()
        {
                $this->hasColumn('post_id', 'integer');
                $this->hasColumn('user_id', 'integer');
        }
}

class PPB_QueryListener extends Doctrine_EventListener
{
        public function postStmtExecute(Doctrine_Event $event)
        {
                //var_dump($event->getQuery());
                //var_dump($event->getParams());
                
                Doctrine_Ticket_DC49_TestCase::$query = $event->getQuery();
                Doctrine_Ticket_DC49_TestCase::$params = $event->getParams();
        }
}

I fixed this bug. Patch in attachment. I hope it's clear enough.

PHP 5.2.8, Doctrine 1.2.0 Alpha1, Vista, Sandbox 1.2.0 Alpha1

The same with Alpha2

Running the tests results in one failure:

Doctrine Unit Tests
===================
Doctrine_Template_Taggable_TestCase.............................................failed

Unexpected Doctrine_Connection_Sqlite_Exception thrown in [Doctrine_Template_Taggable_TestCase] with message [SQLSTATE[HY000]: General error: 1 no such column: a__2] in C:\Users\user\www\extens.dev\lib\Doctrine\Connection.php on line 1067

Trace
-------------

#0 C:\Users\user\www\extens.dev\lib\Doctrine\Connection.php(1015): Doctrine_Connection->rethrowException(Object(PDOException), Object(Doctrine_Connection_Sqlite))
#1 C:\Users\user\www\extens.dev\lib\Doctrine\Query\Abstract.php(936): Doctrine_Connection->execute('SELECT t.id AS ...', Array)
#2 C:\Users\user\www\extens.dev\lib\Doctrine\Query\Abstract.php(990): Doctrine_Query_Abstract->_execute(Array)
#3 C:\Users\user\www\extens.dev\tools\sandbox\extensions\Taggable\lib\Doctrine\Template\TaggableTag.php(76): Doctrine_Query_Abstract->execute(Array, 2)
#4 [internal function]: Doctrine_Template_TaggableTag->getPopularTagsTableProxy(Array)
#5 C:\Users\user\www\extens.dev\lib\Doctrine\Record.php(2569): call_user_func_array(Array, Array)
#6 [internal function]: Doctrine_Record->__call('getPopularTagsT...', Array)
#7 [internal function]: TaggableTag->getPopularTagsTableProxy(Array)
#8 C:\Users\user\www\extens.dev\lib\Doctrine\Table.php(2605): call_user_func_array(Array, Array)
#9 [internal function]: Doctrine_Table->__call('getPopularTags', Array)
#10 C:\Users\user\www\extens.dev\tools\sandbox\extensions\Taggable\tests\Template\TaggableTestCase.php(216): Doctrine_Table->getPopularTags(Array)
#11 C:\Users\user\www\extens.dev\tests\DoctrineTest\UnitTestCase.php(144): Doctrine_Template_Taggable_TestCase->testGetPopularTags()
#12 C:\Users\user\www\extens.dev\tests\DoctrineTest\GroupTest.php(61): UnitTestCase->run()
#13 C:\Users\user\www\extens.dev\tests\DoctrineTest.php(180): GroupTest->run(Object(DoctrineTest_Reporter_Cli), '')
#14 C:\Users\user\www\extens.dev\tools\sandbox\extensions\Taggable\tests\run.php(29): DoctrineTest->run()
#15

Tested: 1 test cases.
Successes: 26 passes.
Failures: 1 fails.
Number of new Failures: 0
Number of fixed Failures: 0

Tests ran in 1 seconds and used 15279.15625 KB of memory

The tests all pass now.

http://www.doctrine-project.org/extension/Taggable/1_2-1_0/tests

It still does not work on my environment (Windows or Ubuntu 9.10, PHP 5.3/5.2.6)
The test result is a bit different, but still fails:

Doctrine Unit Tests
===================
Doctrine_Template_Taggable_TestCase.............................................failed

Unexpected Doctrine_Record_UnknownPropertyException thrown in [Doctrine_Template_Taggable_TestCase] with message [Unknown record property / related component "total_num" on "TaggableTag"] in /home/user/doctrine12b/lib/Doctrine/Record/Filter/Standard.php on line 55

Trace
-------------

#0 /home/taat/doctrine12b/lib/Doctrine/Record.php(1359): Doctrine_Record_Filter_Standard->filterGet(Object(TaggableTag), 'total_num')
#1 /home/taat/doctrine12b/lib/Doctrine/Record.php(1318): Doctrine_Record->_get('total_num', true)
#2 /home/taat/doctrine12b/lib/Doctrine/Access.php(117): Doctrine_Record->get('total_num')
#3 /home/taat/doctrine12b/tools/sandbox/extensions/Taggable/tests/Template/TaggableTestCase.php(217): Doctrine_Access->offsetGet('total_num')
#4 /home/taat/doctrine12b/tests/DoctrineTest/UnitTestCase.php(144): Doctrine_Template_Taggable_TestCase->testGetPopularTags()
#5 /home/taat/doctrine12b/tests/DoctrineTest/GroupTest.php(61): UnitTestCase->run()
#6 /home/taat/doctrine12b/tests/DoctrineTest.php(180): GroupTest->run(Object(DoctrineTest_Reporter_Cli), '')
#7 /home/taat/doctrine12b/tools/sandbox/extensions/Taggable/tests/run.php(29): DoctrineTest->run()
#8

Tested: 1 test cases.
Successes: 26 passes.
Failures: 1 fails.
Number of new Failures: 0
Number of fixed Failures: 0

Did you update Doctrine? I fixed something in Doctrine 1.2 that fixes it.

Yes, the results above ("total_num") I get after updating to 1.2.0-BETA1.
At 1.2.0-ALPHA2 it was "no such column: a__2" (see above comments).

Do the extensions work with 2.0, or they are 1.2 specific?

1.2 specific. I don't get any fails now, can you try and troubleshoot it and figure out why it fails for you? They don't fail for me anywhere.

Doctrine_Template_Taggable_TestCase::testGetPopularTags()
line 214:
$test = Doctrine::getTable('TaggableTag')->getPopularTags(array('ArticleTaggableTest', 'ArticleTaggableTest2'));

// compare this with your results
echo serialize($test);
result:
C:19:"Doctrine_Collection":169:{a:7:{s:4:"data";a:0:{}s:6:"_table";s:11:"TaggableTag";s:9:"_snapshot";a:0:{}s:14:"referenceField";N;s:9:"keyColumn";s:4:"name";s:8:"_locator";N;s:10:"_resources";a:0:{}}}

When I try to var_dump($test),
I get circular dependency.
I tried to save results of var_dump($test) to disk, by I get 1,5GB file, and still growing...

Maybe this article helps somehow:
http://www.bigroom.co.uk/blog/php-nesting-level-too-deep-recursive-dependency

Can I see the fix you mentioned above?

I'm sorry, but I do not know what else I can do.
Any ideas?

I am not sure what to say since it is working for me on all the machines I test. I'll be happy to look if I can get something to fail so I can troubleshoot it.

I've tested on PLD Linux too.
The same thing.

Could you provide sample server/php configuration on which the tests are passing?

Can you describe the fix you've made to correct the errors with ''Unknown method Doctrine_Table::getPopularTags'.
Maybe I can manage to investigate it, but I do not know where to start.

Sorry for reopening this again.

After upgrading to BETA2 all Taggable tests are passing OK for me too.

However, in the code I still get 'Unknown method BlogPostTable::getPopularTags' in /tools\sandbox\lib\Doctrine\Table.php:2793'
Isn't it a regression of the bug you've already fixed before ALPHA2?

Here is the description of how I came to this exception.

At the very end of config.php:

$manager->registerExtension('Taggable');

In the index.php:

<?php

require_once('config.php');

$models = Doctrine_Core::loadModels('models');

$post = new BlogPost();
$post->title = 'This is a sample blog post';
$post->description = 'Cool!';
$post->setTags('2009, symfony, doctrine');
$post->save();

$table = Doctrine::getTable('BlogPost');
echo get_class($table); // echoes Doctrine_Collection
$table->getPopularTags(); // this throws the exception

Then in the console:
php doctrine.php create-db
php doctrine.php generate-models-yaml (BaseBlogPost, BlogPost, BlogPostTable were generated)
php doctrine.php generate-sql (see SQL below)
php doctrine.php create-tables
(all ok)

php index.php

result:

BlogPostTablePHP Fatal error: Uncaught exception 'Doctrine_Table_Exception' with message 'Unknown method BlogPostTable::getPopularTags' in C:\Users\user\www\extens.dev\tools\sandbox\lib\Doctrine\Table.php:2793
Stack trace:
#0 [internal function]: Doctrine_Table->__call('getPopularTags', Array)
#1 C:\Users\user\www\extens.dev\tools\sandbox\index.php(15): BlogPostTable->getPopularTags()
#2

thrown in C:\Users\user\www\extens.dev\tools\sandbox\lib\Doctrine\Table.php on line 2793

And here is the generated SQL:

CREATE TABLE taggable_tag (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255) UNIQUE);
CREATE TABLE blog_post_taggable_tag (id INTEGER, tag_id INTEGER, PRIMARY KEY(id, tag_id));
CREATE TABLE blog_post (id INTEGER PRIMARY KEY AUTOINCREMENT, title VARCHAR(255) NOT NULL, description VARCHAR(255) NOT NULL);

When I test your code, it is working for me. You sure you don't have some old versions of PHP or something?

Everything is up to date.
Tested on different servers, with different operating systems, different versions PHP, different versions of Doctrine, by different people.

Common thing for all these machines is that PHP (>5.2.10 <= 5.3.1) was installed from precompiled builds, not from source.

The error I get now is the same error I got earlier (at the beginning of this topic), which disapperared after your fix.
Then appeared the second error, which was fixed in BETA2, but the previous error is back. (strange, but the tests are passing now, despite the errors in the regular code).

When I find some spare time I'll try to trace the changes you've made since the beginning of the topic.

Maybe the models are generated not properly.
Here are my models (three) for the reproduction procedure (above):

class BlogPostTable extends Doctrine_Table {}

class BlogPost extends BaseBlogPost {}

abstract class BaseBlogPost extends Doctrine_Record
{
public function setTableDefinition()

public function setUp()

}

I have everything generated the same as you.

Also getting this. Using doctrine 1.2.0-beta3 and also tried with latest SVN. Taggable is latest available.

PHP 5.3.0 running on Zend Server version 5.0.0Beta2

No changes after upgrade to 1.2.0-RC1.

Maybe this helps somehow:

// Following line throws:
// Doctrine_Table_Exception: Unknown method Doctrine_Table::getPopularTags in /home/user/doctrine12rc1/lib/Doctrine/Table.php on line 2799
$tags = Doctrine::getTable('BlogPost')->getPopularTags();

// This line returns collection of tags, as expected
$tags = Doctrine::getTable('TaggableTag')->getPopularTags('BlogPost');

$rel = Doctrine::getTable('BlogPost')->getRelations(); // is int(2)
$rel = Doctrine::getTable('TagableTag')->getRelations(); // is int(2)

Symfony 1.3 SVN updated as of this post.

Ubuntu Karmic with PHP 5.3.1

I have installed Taggable in mysymfonyproject/lib/doctrine_extensions/Taggable

I have two example tables defined with Taggable:

Video:
actAs:

columns:
id:

content_id:

title:

description:

relations:
Content:

Photo:
actAs:

columns:
id:

content_id:

title:

description:

uri:

relations:
Content:

The following code functions and populates the database:
$video = new Video();
...
$video->setTags(implode(", ", $tags));
$video->save();

The following code errors:
$tags = Doctrine::getTable('Video')->getPopularTags();

500 | Internal Server Error | Doctrine_Table_Exception
Unknown method VideoTable::getPopularTags

The following code errors:
$tags = Doctrine::getTable('TaggableTag')->getPopularTags('Video');

500 | Internal Server Error | Doctrine_Exception
Couldn't find class TagableTag

Dont know if its related to this issue or not, but for some reason Doctrine::getTable('TaggableTag') throws a "

Couldn't find class TaggableTag" exception

I understand that you would normally do this:
Doctrine::getTable('Video')->getTags()
but I just want plain access to the TaggableTag model and Doctrine doesnt seem to generate any model classess for TaggableTag

I have tested everything everyone has provided and it is all working for me. The only thing I can suggest now is that someone creates a Doctrine failing test case so that I can run it to see the failure.

Will create a test case this weekend for this and a few other problems I've across (with fixes). Nothing more frustrating than not being able to reproduce a problem, so I feel your pain.

I think this bug i related to the bug in the documentation http://www.doctrine-project.org/jira/browse/DC-378 to get the popular tags you will first have to load the models you want the tags for.

// load models that you want the tags from
Doctrine::getTable('blog');
Doctrine::getTable('products');
// then you can get the tags for the models currently loaded
$tags = Doctrine::getTable('TaggableTag')->getPopularTags();

This works for me:

// load models that you want the tags from
Doctrine::getTable('blog');
Doctrine::getTable('products');
// then you can get the tags for the models currently loaded
$tags = Doctrine::getTable('TaggableTag')->getPopularTags();

Okie... Creating test cases for this turned out to be more complicated than I can handle - myself not being that familiar with phpunit.
Although within the test it is written as:
$test = Doctrine::getTable('TaggableTag')->getPopularTags(array('ArticleTaggableTest', 'ArticleTaggableTest2'));
Rather than:
$test = Doctrine::getTable('ArticleTaggableTest')->getPopularTags();
which is what this problem is about.

Although I do believe I've nailed the source of the problem - as well as others I've experienced.

• Problems are introduced when using pear style model names. Indirectly fixed with: http://pastebin.com/DKGcgAht - links to fix in Doctrine/Record/Generator.php
• Problems are introduced when using custom names for the taggable field. Such as: http://pastebin.com/JdhqeG38 - example
• Problems are introduced when using inheritance for models. Such as: http://pastebin.com/Qwuwk0z1 - example
• Problems are introduced when using taggable relations when the record has not been inserted yet. Such as: http://pastebin.com/ahPpqTdK - example

Having either of the above causes the system to fall down. I've applied a bunch of fixes/changes to taggable to ensure it works with my systems, such as http://sos.7dots.co.uk/ (still in staging) which uses all of the above mentioned problems, not sure how to do a diff but here is the file:
http://www.2shared.com/file/12385197/c5911e42/Taggable.html

Hope that helps some people. Any changes I've made code can come under the same licence as doctrine, so no worries there.

With 1.2.2 there is some progress.
There was still 1 test failing (CASCADE),
but with the version:
http://www.2shared.com/file/12385197/c5911e42/Taggable.html
provided by Benjamin Arthur Lupton
all test are passing now.

But now when I run:
getTable('ArticleTaggableTest')->getPopularTags();
I get: ERROR: column "i2__1" does not exist

The failing query on PostgreSql is:

SELECT t.id AS t_id, t.name AS tname, COUNT(DISTINCT i.id) AS i_0,
(COUNT(DISTINCT i.id)) AS i__1
FROM taggable_tag t
LEFT JOIN cms_model_image_taggable_tag c ON (t.id = c.tag_id)
LEFT JOIN image i ON i.id = c.id
WHERE t.id IN
(SELECT doctrine_subquery_alias.id
FROM
(SELECT DISTINCT t2.id, (COUNT(DISTINCT i2.id)) AS i2__1
FROM taggable_tag t2
LEFT JOIN cms_model_image_taggable_tag c2 ON (t2.id = c2.tag_id)
LEFT JOIN image i2 ON i2.id = c2.id
GROUP BY t2.id HAVING i2__1 > 0
ORDER BY i2__1 DESC LIMIT 10) AS doctrine_subquery_alias)
GROUP BY t.id HAVING i__1 > 0
ORDER BY i__1 DESC

Seems that this just a PostgreSql issue.
On Mysql it runs just fine.

The last comment is concerning how the poular tags are found and rated (on PostgreSql). I think this is unrelated to the rest of this issue and should be moved to a separate issue. (also I only got junk ads when I tried to get the file you are linking to).

I have created separated issue for this:
http://www.doctrine-project.org/jira/browse/DC-634

And yes, this link is quite odd...
There is a lot of crap,
however there is a small link, to download improved code:

Look for: "Save file to your PC: click here"

Mac OS X 10.6.1

Sorry this is just not enough information to produce the problem. I ran some basic tests looking for what you pointed out but I didn't see any issues. Plus our tests cover this functionality so I imagine that if it were broke we'd get some failures. However, I could be wrong so if you could re-open and provide a failing test case for us that would help with getting it fixed.

I actually ran into this problem once again in another problem.

Schema:

Story:
columns:
id:
type: integer(4)
unsigned: true
primary: true
autoincrement: true

Picture:
columns:
id:
type: integer(4)
unsigned: true
primary: true
autoincrement: true
story_id:
type: integer(4)
unsigned: true
notnull: true
is_selected:
type: boolean
notnull: true
default: false
relations:
Story:
foreignAlias: Pictures
local: story_id
foreign: id
type: one
foreignType: many
onDelete: CASCADE

Query:

Doctrine_Query::create()
->from('Story s')
->leftJoin('s.Pictures p WITH p.is_selected = ?', true)
->where('s.id = ?', 5)
->limit(10);

Outputted SQL:

SELECT "s"."id" AS "s_id", "s"."first_name" AS "sfirst_name", "s"."last_name" AS "slast_name", "s"."country" AS "scountry", "s"."email" AS "semail", "s"."content" AS "scontent", "s"."title" AS "stitle", "s"."summary" AS "ssummary", "s"."is_published" AS "sis_published", "s"."is_rejected" AS "sis_rejected", "s"."is_promoted" AS "sis_promoted", "s"."published_at" AS "spublished_at", "s"."created_at" AS "screated_at", "s"."updated_at" AS "supdated_at", "p"."id" AS "pid", "p"."story_id" AS "pstory_id", "p"."filename_original" AS "pfilename_original", "p"."filename_large" AS "pfilename_large", "p"."filename_thumb" AS "pfilename_thumb", "p"."mime_type" AS "pmime_type", "p"."is_selected" AS "pis_selected", "p"."created_at" AS "pcreated_at", "p"."updated_at" AS "p_updated_at" FROM "story" "s" LEFT JOIN "picture" "p" ON "s"."id" = "p"."story_id" AND ("p"."is_selected" = '1') WHERE "s"."id" IN (SELECT DISTINCT "s2"."id" FROM "story" "s2" LEFT JOIN "picture" "p2" ON "s2"."id" = "p2"."story_id" AND ("p2"."is_selected" = '5') WHERE "s2"."id" = '1' ORDER BY "s2"."published_at" DESC LIMIT 10) AND ("s"."id" = '5') ORDER BY "s"."published_at" DESC

Notice the WHERE "s2"."id" = '1' !

Can you provide a test case we can run?

I could not figure out what test to write but I did narrow the problem down.

It seems that the order of the generated SQL clauses does not match the order of the passed parameters, so in the final SQL query the values are mixed up. You can actually see that happening in the SQL query above. I also noticed there a special cases hardcoded in the code for MySQL and PgSQL as far as these "limit subqueries" go, so that might be another reason why you havent bumped into problem before. I was using an SQLite driver.

I suppose something goes wrong in the SQL generation, but I don't know the core good enough to find out where exactly it does go wrong, so I'm afraid I can't attach a test case, but it should be easy to reproduce now anyway.

Windows XP, WampServer Version 2.0

Is anybody looking into this issue ?

Can you provide a test case that shows the issue? It is hard to look into the issue without a test to run When I look at the code and our tests everything is passing and fine so I am not sure what your issue could be. Re-open if you have more information to provide.

ok, I attach the test case

Have you already been able to look at the testcase ??

Any news ??

This issue is still valid for Doctrine 1.2. Doctrine_Locking_Manager_Pessimistic is UNUSABLE without this bug fixed!

Having a look at "Doctrine_Locking_Manager_Pessimistic::getLock()" it becomes clear what causes this misbehaviour:

    public function getLock(Doctrine_Record $record, $userIdent)
    {
        $objectType = $record->getTable()->getComponentName();
        $key        = $record->getTable()->getIdentifier();

        $gotLock = false;
        $time = time();

        if (is_array($key)) {
            // Composite key
            $key = implode('|', $key);
        }

        try {
            $dbh = $this->conn->getDbh();
            $this->conn->beginTransaction();

            $stmt = $dbh->prepare('INSERT INTO ' . $this->_lockTable
                                  . ' (object_type, object_key, user_ident, timestamp_obtained)'
                                  . ' VALUES (:object_type, :object_key, :user_ident, :ts_obtained)');

            $stmt->bindParam(':object_type', $objectType);
            $stmt->bindParam(':object_key', $key);
            $stmt->bindParam(':user_ident', $userIdent);
            $stmt->bindParam(':ts_obtained', $time);

There is NO hint about the Record's identifier VALUE but only about the identifier's NAME (mostly "id") which appears to be redundant information. Instead of ...

        $key = $record->getTable()->getIdentifier();

..there should be something like ..

        $key = $record->get($record->getTable()->getIdentifier());

In case of composite keys a string concatenation, prefixed by identifier's name might work but I would recommend using "md5()" on resulting value to limit its length since field "object_key" is limited to 250 chars.

Based on the previous comment by Markus Wößner i created a patch for row based locking.

It concatenates the PK fields and their values to a string and calculates the sha-1 hash as a unique string representing that record. This string is then used as key so that we'll only lock the single Record and not the whole table.

I hope you'll give this patch a try - It solved this problem for me.

I applied patch from Mr. Florian Zumkeller-Quast and provided testcase didn't fail anymore. I think this should do it. By the way I find it strange that this issue isn't already fixed. I guess locking is not very much used by Doctrine users.

I applied patch too and it works now. I guess too that nobody use Lockings but when you use it ... without the patch it fails.

Duplicate of http://www.doctrine-project.org/jira/browse/DC-984

Local: Windows/Cygwin/XAMPP
Stage & Prod: CentOS
Apache / MySQL
Framework: Zend

a quick fix for this is to enclose the field in parentheses when selecting it, then use an alias to reference it inside the having clause. eg

$q = Doctrine_Query::create()
->select("u.id, u.first_name, u.last_name, (u.is_active) u_is_active")
->addSelect('COUNT(u.id) as rolecount')
->from('User u')
->leftJoin('u.Roles r')
->groupBy('u.id')
->having("rolecount > 2 and u_is_active = 1");