Page 1 of 1

I don't understand why this query errors out...

Posted: Wed Mar 31, 2010 10:53 pm
by kevinwinters
Here is the query that produces an error

Code: Select all

SELECT inventory.*, categories.name AS furname
 FROM furniture.inventory INNER JOIN furniture.categories ON (inventory.perm_category_id = categories.id)
  WHERE inventory.sales_status !='sold' AND inventory.date_last_modified  <= DATE_SUB(CURDATE(),INTERVAL 5 DAY) AND inventory.category_id =20 AND inventory.perm_category_id !=20
in SQLYog
The error returned is:
Query : SELECT inventory.*, categories.name as furname FROM furniture.inventory INNER JOIN furniture.categories ON (inventory.perm_cat...
Error Code : 1142
SELECT command denied to user: 'xxxxxxxxxx' for table 'inventory'


But I can execute this query with no problem (it is happily using the select command on the inventory table)

Code: Select all

SELECT *
	 FROM inventory
	  WHERE sales_status ='sold' 
	  AND date_last_modified  <= DATE_SUB(CURDATE(),INTERVAL 7 DAY)
	  AND date_sold  <= DATE_SUB(CURDATE(),INTERVAL 7 DAY)
I can only conclude that there is something wrong with the first query that gives me that error and not anything to do with permissions
Any help with this would be appreciated, thank you in advance

Re: I don't understand why this query errors out...

Posted: Wed Mar 31, 2010 11:09 pm
by requinix
kevinwinters wrote:and not anything to do with permissions
I'll accept that as a possibility if you can give me an interpretation of "command denied to user" that does not involve permissions.

Spoiler: it does have to do with permissions.
kevinwinters wrote:But I can execute this query with no problem
Ah, but from where? And as what user?

Re: I don't understand why this query errors out...

Posted: Wed Mar 31, 2010 11:29 pm
by kevinwinters
thank you for that quick reply, yah, it didn't have anything to do with permissions, just a sloppy query with (I think, incorrect quotes at the wrong places, sorry I did not post the PHP part but after re-arranging the query like this: (this time with the PHP part)

Code: Select all

$oCat = $db->setQuery(
            sprintf("SELECT
    inventory.*
    , categories.name as furname
FROM
    inventory
    INNER JOIN categories 
        ON (inventory.perm_category_id = categories.id)
        WHERE inventory.sales_status !='sold' AND inventory.date_last_modified  <= DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND inventory.category_id =20 AND inventory.perm_category_id !=20"
 )
);
It works fine

Re: I don't understand why this query errors out...

Posted: Thu Apr 01, 2010 3:09 am
by phu
Without attempting to reproduce your environment, the very trivial fact is that tasairis is right: mySQL says it's a permissions issue.

It seems pretty clear that you have access issues. Run...
grant all on 'dbname'.* to 'dbuser'@'localhost' identified by 'dbpassword';

... and if you still have problems, they should at least be new ones.