Having trouble running cakephp app on remote server

Question

If you get:

Error: SQLSTATE[42000]: Syntax error or access violation: 1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

After uploading cake php app and database from xampp localhost to a remote server.

Having tried importing the cake database into a new db on local machine and works fine. So I couldn't see it being the information imported.

Had no idea how to fix this. Its a simple and common problem with an easy fix as below.

Answer 1

After much hair pulling I managed to find the problem/fix with the help of my good friend ten1 on cakephp IRC chat.

When this is a cakephp specific issue which it was in my case you need to do the un-thinkable and edit the core.

The file you need to edit is AclNode.php Located here: /lib/Cake/Model/AclNode.php

You need to add a line before line 113

112    }
                $db->query('SET SQL_BIG_SELECTS=1'); //Add this line
113             $result = $db->read($this, $queryData, -1);
114             $path = array_values($path);

This is generally only a problem on servers with shared hosting.

Answer 2

Rather than editing core file you could add a beforeFind method to your app/models/app_model.php file, if wanted it to affect all over or to your particular model file, like the following:

function beforeFind()   {
    $this->query('SET SQL_BIG_SELECTS=1');
}