Allowed memory size exhausted with Propel2 custom query

Question

(updates at bottom)

I'm trying to get the latest entry in my table called "VersionHistory", and since the ID is set to auto increment, I was trying to get the max id. Trying to stay away from sorting the whole table in descending order and taking the top as I want to minimize the computation required for this query as the table grows, and this table will probably get pretty huge fast.

class VersionHistoryQuery extends BaseVersionHistoryQuery {
    public function getLatestVersion() {
        return $this
            ->withColumn('MAX(version_history.id)')
            ->limit(1);
    }
}

I'm calling the function in my VersionHistory constructor as below:

class VersionHistory extends BaseVersionHistory {
    public function __construct($backupPath = "") {
        $lastVersion = VersionHistoryQuery::create()
            ->getLatestVersion()
            ->find();
        $lastVersion->setBackupPath("backup/" . $backupPath);
        $lastVersion->save();
        parent::setImportedAt(date("Y-m-d H:i:s"));
    }    
}

This outputs a "Allowed memory size exhausted" error in php. Any idea why? Commenting out the query in the VersionHistory constructor fixes the error, so it's somewhere in the query. I tried setting up a custom query following the instructions here: http://propelorm.org/documentation/03-basic-crud.html#using-custom-sql. But I couldn't get that to work. Running:

SELECT * FROM version_history WHERE id = (SELECT MAX(id) FROM version_history)

From MySQL workbench works fine and quickly.

Any ideas of what I'm doing wrong?

What I tried

Updated the code to:

    public function getLatestVersion() {
        return $this
        ->orderById('desc')
        ->findOne();
    }

Still get the same memory allocation error.

Updated the code to:

        $lastVersion = VersionHistoryQuery::create()
        ->orderById('desc')
        ->findOne();

Removed the custom function, turned on propel debug mode, it outputs that this query is run:

[2015-10-11 17:26:54] shop_reporting_db.INFO: SELECT `version_history`.`id`, `version_history`.`imported_at`, `version_history`.`backup_path` FROM `version_history` ORDER BY `version_history`.`id` DESC LIMIT 1 [] []

Still runs into a memory overflow.

It looks as though `propel is doing the correct query and you memory issues are elsewhere.` — Ryan Vincent, Oct 11 '15 at 21:30
I thought so too, but removing query removes all issues. It's only when I add in the query that any issues occur. — user2317084, Oct 11 '15 at 21:31
what is the table definition? are there proper indexes on the field? — William, Oct 28 '15 at 00:45
Yup, they are! It's weird because I can run other queries on it, I think that it's just I was writing with Propel incorrectly. — user2317084, Oct 28 '15 at 03:42

score 1 · Answer 1 · answered Oct 11 '15 at 20:32

1

Thats all:

SELECT * FROM version_history ORDER BY id DESC LIMIT 1;

answered Oct 11 '15 at 20:32

Bernd Buffen

14,525
2
24
39

The reason why I don't want to use that is because I know that the table will get very large as time goes on, so I want to make sure that it's efficient, which is why I was using SELECT * FROM version_history WHERE id = (SELECT MAX(id) FROM version_history) but the question is how to execute this through propel. – user2317084 Oct 11 '15 at 20:42
Thats the best way : put "EXPLAIN " before the query and you see that mySQL only reads ONE row. It can do this with the Primary Key on the field id – Bernd Buffen Oct 11 '15 at 20:44
Didn't know that either. Thanks. I updated it to: public function getLatestVersion() { return $this ->orderById('desc') ->findOne(); } still get the same error. – user2317084 Oct 11 '15 at 20:55

score 1 · Answer 2 · answered Oct 11 '15 at 20:39

From the documentation, withColumn does the following:

Propel adds the 'with' column to the SELECT clause of the query, and uses the second argument of the withColumn() call as a column alias.

So, it looks like you are actually querying every row in the table, and also every row is querying the max ID.

I don't know anything about propel (except what I just googled), but it looks like you need a different way to specify your where condition.

score 0 · Answer 3 · answered Aug 11 '17 at 19:19

Your raw SQL and your Propel Query are different / not equivalent.

In the propel query merely added a column, whereas is your raw SQL your actually have two queries with one being a sub-query to the other.

So you need to do the equivalent in Propel:

$lastVersionID = VersionHistoryQuery::create()
    ->withColumn('MAX(id)', 'LastVersionID')
    ->select('LastVersionID')
    ->findOne();

$lastVersion = VersionHistoryQuery::create()
    ->filterByVersionHistory($lastVersionID)
    ->find();

Note the ->select('LatestVersionID') since you only need a scalar value and not an entire object, as well as the virtual column (alias in SQL) using withColumn()

Qiniso, while that is correct, I don't think that solves the problem. I believe the root issue is that the VersionHistory constructor is calling itself recursively through the find method within the constructor. What do you think? — Ben, Aug 17 '17 at 03:28

Allowed memory size exhausted with Propel2 custom query

What I tried

3 Answers3