我正在尝试在
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中选择而不是加入,则会发生同样的错误.
找到了！对于遇到此问题的任何其他人,由于Laravel设置以下PDO连接选项而出现问题：
PDO::ATTR_EMULATE_PREPARES => false
我的解决方案是克隆我的数据库配置,覆盖PDO选项,然后在创建视图时使用该连接,而不是为我的整个应用程序启用Emulate Prepares：
配置/ 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','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
);
");
为了帮助我调试这个,我很荣幸获得Ryan Vincent.
