我正在尝试在 PHP(特别是Laravel)中创建一个MysqL视图,我遇到了一个奇怪的错误：

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

在MysqL中直接运行create语句可以正常工作.如果我删除了视图的连接,那么一切正常.用户具有完全权限(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
    );
");

程序中的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服务器.

PDO::ATTR_EMULATE_PREPARES => false

'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','options'   => array(
        PDO::ATTR_EMULATE_PREPARES => true,

$rand = rand(10000,99999);

DB::statement("
    CREATE TABLE table".$rand." (
        id int(11) NOT NULL AUTO_INCREMENT,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
    );
");