![]() Huge credit to Ryan Vincent for helping me debug this. INNER JOIN view".$rand." ON view".$rand.".id = table".$rand.".id SELECT table".$rand.".id, view".$rand.".foo FROM table".$rand." 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(ĭB::connection('mysql-emulate-prepares')->statement(" 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.Įureka! For anyone else facing this issue, the problem occurs due to Laravel setting the following PDO connection option: PDO::ATTR_EMULATE_PREPARES => false The same error occurs if just selecting from view1, rather than joining. [Grants for => GRANT ALL PRIVILEGES ON `dbname`.* TO below very simplified example also produces the same results: DB::statement(" The output of SHOW GRANTS from within the PHP application are as follows: [Grants for => GRANT USAGE ON *.* TO IDENTIFIED BY PASSWORD '****************************' LEFT OUTER JOIN company_paid AS company_paid ON company_paid.job_id = jobs.id LEFT OUTER JOIN customer_paid AS customer_paid ON customer_paid.job_id = jobs.id LEFT OUTER JOIN quote_response_count AS quote_response_count ON quote_response_count.job_id = jobs.id Price - IFNULL(customer_paid.total, 0) AS customer_owes,Ĭost - IFNULL(customer_paid.total, 0) AS owes_company,ĭeposit > 0 AND IFNULL(customer_paid.total, 0) >= deposit IFNULL(quote_response_count.total, 0) AS responses_received, IFNULL(company_paid.total, 0) AS company_paid, IFNULL(customer_paid.total, 0) AS customer_paid, WHERE category = 'company payment' AND is_verified = 1 WHERE category = 'customer payment' AND is_verified = 1 SELECT job_id, SUM(amount) as total FROM transactions INNER JOIN quote_responses on quote_responses.quote_request_id = quote_requests.id INNER JOIN quote_requests on quote_requests.quote_id = quotes.id SELECT job_id, COUNT(quotes.id) as total FROM quotes My code is below, slightly simplified, with the error being produced when running the 4th statement to create jobs_view. ![]() Extensive Googling hasn't returned anything similar. The user has full permissions (GRANT ALL). If I remove the joins to the views then everything works. When i try to menully import the file jcqm. ![]() ![]() sql file at phpmyadmin and it still cant import. I tryied to import the data by import the data via. Running the create statements straight in MySQL work correctly. 1 I have a script system that refuse to be installed because of error that breaks installation. SQLSTATE: SSyntax error or access violation: 1142 ANY command denied to user for table '/tmp/#sql_475_0' Note this is related to Grant permissions on views, deny select on tables but slightly different.I'm trying to create a MySQL view within PHP (specifically Laravel) and I'm experiencing a strange error: That would also allow them to hammer Production by executing the views in production instead of the read only database.Īre there any grants that would allow creating of views, but not executing a select statement or even using the view? (I also thought perhaps it was the SELECT * but even doing select column from. It looks like this same user needs SELECT capability granting that allows the view to be created. However, attempting to create a view gives this error: create view v_test as select * from tabItem ĮRROR 1142 (42000): ANY command denied to user for table 'tabItem' You can't create a view in a read only database (at least at RDS), so I attempted to create a user in the production database that can only create views. We have a data scientist making views to handle our OLAP use cases. We have a read only Amazon RDS database (MariaDB, 10.6.8 and up) that is synced to a production database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |