php - PDOException 语法错误或访问冲突 1142，当创建引用其他 View 的 View 时

Question

我正在尝试在 PHP(特别是 Laravel)中创建一个 MySQL View ，但我遇到了一个奇怪的错误:

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

直接在 MySQL 中运行创建语句可以正常工作。如果我删除 View 的连接，那么一切正常。用户具有完全权限 (GRANT ALL)。广泛的谷歌搜索没有返回任何类似的东西。

我的代码在下面，稍微简化了，在运行第 4 条语句创建 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
    );
");

PHP 应用程序中 SHOW GRANTS 的输出如下:

[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'

下面非常简化的示例也产生相同的结果:

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
    );
");

如果只是从 view1 选择，而不是加入，也会出现同样的错误。

我遇到此问题的系统是运行 PHP 5.5.23 和 MySQL 5.5.41 的 Ubuntu 12.04 服务器。

Answer 1

Eureka !对于面临此问题的任何其他人，问题是由于 Laravel 设置了以下 PDO 连接选项而发生的:

PDO::ATTR_EMULATE_PREPARES => false

我的解决方案不是为我的整个应用程序启用 Emulate Prepares，而是克隆我的数据库配置，覆盖 PDO 选项，然后在创建我的 View 时使用该连接:

配置/数据库.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,
    ),
),

迁移

$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
    );
");

Ryan Vincent 的巨大功劳帮助我调试这个。