PDOException Syntax error or access violation 1142, when creating view referencing other views

Question

I'm trying to create a MySQL view within PHP (specifically Laravel) and I'm experiencing a strange error:

[PDOException]
SQLSTATE[42000]: SSyntax error or access violation: 1142 ANY command denied to user 'user'@'localhost' for table '/tmp/#sql_475_0'

Running the create statements straight in MySQL work correctly. If I remove the joins to the views then everything works. The user has full permissions (GRANT ALL). Extensive Googling hasn't returned anything similar.

My code is below, slightly simplified, with the error being produced when running the 4th statement to create jobs_view.

DB::statement("
    CREATE VIEW quote_response_count AS (
        SELECT job_id, COUNT(quotes.id) as total FROM quotes 
        INNER JOIN quote_requests on quote_requests.quote_id = quotes.id 
        INNER JOIN quote_responses on quote_responses.quote_request_id = quote_requests.id
        GROUP BY job_id
    );
");

DB::statement("
    CREATE VIEW customer_paid AS (
        SELECT job_id, SUM(amount) as total FROM transactions 
        WHERE category = 'customer payment' AND is_verified = 1
        GROUP BY job_id, category
    );
");

DB::statement("
    CREATE VIEW company_paid AS (
        SELECT job_id, SUM(amount) as total FROM transactions 
        WHERE category = 'company payment' AND is_verified = 1
        GROUP BY job_id, category
    );
");

DB::statement("
    CREATE VIEW jobs_view AS (
        SELECT 
            jobs.*,         
            IFNULL(customer_paid.total, 0)              AS customer_paid,
            IFNULL(company_paid.total, 0)               AS company_paid,
            IFNULL(quote_response_count.total, 0)       AS responses_received,                  
            price - IFNULL(customer_paid.total, 0)      AS customer_owes,
            cost  - IFNULL(customer_paid.total, 0)      AS owes_company,
            (
                deposit > 0 AND IFNULL(customer_paid.total, 0) >= deposit
            )                                           AS deposit_paid             

         FROM jobs

         LEFT OUTER JOIN quote_response_count   AS quote_response_count ON quote_response_count.job_id  = jobs.id
         LEFT OUTER JOIN customer_paid          AS customer_paid        ON customer_paid.job_id         = jobs.id
         LEFT OUTER JOIN company_paid           AS company_paid         ON company_paid.job_id          = jobs.id
    );
");

The output of SHOW GRANTS from within the PHP application are as follows:

[Grants for user@localhost] => GRANT USAGE ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD '****************************'

[Grants for user@localhost] => GRANT ALL PRIVILEGES ON `dbname`.* TO 'user'@'localhost'

The below very simplified example also produces the same results:

DB::statement("
    CREATE TABLE table1 (
        id int(11) NOT NULL AUTO_INCREMENT,
        foo varchar(45) DEFAULT NULL,
        PRIMARY KEY (id)
    );
");
DB::statement("
    CREATE VIEW view1 AS (
        SELECT id, foo FROM table1
    );
");
DB::statement("
    CREATE VIEW view2 AS (
        SELECT table1.id, view1.foo FROM table1
        INNER JOIN view1 ON view1.id = table1.id
    );
");

The same error occurs if just selecting from view1, rather than joining.

The system which I'm experiencing this problem on is a Ubuntu 12.04 server running PHP 5.5.23 and MySQL 5.5.41.

Answer 1

Eureka! For anyone else facing this issue, the problem occurs due to Laravel setting the following PDO connection option:

PDO::ATTR_EMULATE_PREPARES => false

Rather than enable Emulate Prepares for my whole application, my solution was to clone my database configuration, override the PDO option, and then use that connection when creating my view:

config/database.php

'mysql' => array(
    'driver'    => 'mysql',
    'host'      => 'localhost',
    'database'  => 'database',
    'username'  => 'user',
    'password'  => 'password',
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => '',
),
'mysql-emulate-prepares' => array(
    'driver'    => 'mysql',
    'host'      => 'localhost',
    'database'  => 'database',
    'username'  => 'user',
    'password'  => 'password',
    'charset'   => 'utf8',
    'collation' => 'utf8_unicode_ci',
    'prefix'    => '',
    'options'   => array(
        PDO::ATTR_EMULATE_PREPARES => true,
    ),
),

Migration

$rand = rand(10000, 99999);

DB::statement("
    CREATE TABLE table".$rand." (
        id int(11) NOT NULL AUTO_INCREMENT,
        foo varchar(45) DEFAULT NULL,
        PRIMARY KEY (id)
    );
");
DB::statement("
    CREATE VIEW view".$rand." AS (
        SELECT id, foo FROM table1
    );
");
DB::connection('mysql-emulate-prepares')->statement("
    CREATE VIEW view".($rand+2)." AS (
        SELECT table".$rand.".id, view".$rand.".foo FROM table".$rand."
        INNER JOIN view".$rand." ON view".$rand.".id = table".$rand.".id
    );
");

Huge credit to Ryan Vincent for helping me debug this.