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.

I think there is a similar stack here: http://stackoverflow.com/questions/966815/mysql-max-join-size-errors — Clayton Bell, Dec 12 '12 at 01:11

score 4 · Answer 1 · answered Dec 12 '12 at 06:24

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.

I doubt you need to hack the core as there are countless people using cake on shared hosting already. you could easily add `$db->query('SET SQL_BIG_SELECTS=1');` to the beforeFind callback of even overload the DBO if needed. Having to modify the core is only because you have not taken the time to understand the tools you are using. Fixing the query would be the clear winner. — dogmatic69, Dec 18 '12 at 15:46

score 3 · Accepted Answer · answered Jun 03 '13 at 07:41

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');
}

Vikash Sharma · Answer 3 · 2019-06-04T06:12:34.537

1

For Cakephp 3 following works:

    'Datasources' => [
        'default' => [
            'init'   => array(
                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET SESSION SQL_BIG_SELECTS=1',
                ), // Add this to the existing array

edited Jun 04 '19 at 06:12

answered Apr 14 '17 at 05:55

Vikash Sharma

49
7

Having trouble running cakephp app on remote server

3 Answers3