MySQL Error 1142 - SELECT command denied to user

Question

I m having troubles with a certain query on one of my servers  On all other places I ve tested it it works completely fine but on the server i want to use it it isn t working   It s about the following SQL   SELECT facturen id            AS fid          projecten id           AS pid          titel          facturen totaal bedrag AS totaal          betaald          datum  FROM   facturen          projecten  WHERE  facturen project id   projecten id         AND projecten eigenaar    1   ORDER  BY datum DESC    This is the error code I get from it   SELECT command denied to user  marco   localhost  for table  projecten   The tables  facturen   CREATE TABLE IF NOT EXISTS  facturen       id  int 11  NOT NULL auto increment     project id  int 11  NOT NULL     datum  int 11  NOT NULL     lever datum  int 11  NOT NULL     totaal bedrag  decimal 9 2  NOT NULL     btw  decimal 9 2  NOT NULL     bedrijf  varchar 40  NOT NULL     contactpersoon  varchar 60  NOT NULL     adres  varchar 60  NOT NULL     postcode  varchar 7  NOT NULL     plaats  varchar 30  NOT NULL     betaald  int 11  NOT NULL    PRIMARY KEY    id     ENGINE MyISAM  DEFAULT CHARSET latin1 AUTO INCREMENT 201200006     projecten   CREATE TABLE IF NOT EXISTS  projecten       id  int 11  NOT NULL auto increment     titel  varchar 80  NOT NULL     eigenaar  int 11  NOT NULL     creatie datum  int 11  NOT NULL     eind datum  int 11  NOT NULL     totaal bedrag  decimal 9 2  NOT NULL     btw  decimal 9 2  NOT NULL    PRIMARY KEY    id     ENGINE MyISAM  DEFAULT CHARSET latin1 AUTO INCREMENT 201200004     The strange part is that every other query on both the  projecten  table and the  facturen  table works completely fine  also this query works fine on two other servers of mine

User · Answer

This is th privileges issue in your database users  first check and grant permission to user  marco  in localhost

User · Answer

This error happened on my server when I imported a view with an invalid definer   Removing the faulty view fixed the error   The error message didn t say anything about the view in question  but was  complaining  about one of the tables  that was used in the view

User · Answer

You need to grant SELECT permissions to the MySQL user who is connecting to MySQL  same question as here Error  select command denied to user   39  lt userid gt   39    39  lt ip-address gt   39  for table   39  lt table-name gt   39   see answers of the link

User · Answer

So the issue I ran into was this      the application I used to grant the permissions converted the Schema TableName into a single DB statement in the wrong table  so the grant was indeed wrong  but looked correct when we did a SHOW GRANTS FOR UserName if you weren t paying very close attention to GRANT SELECT vs GRANT TABLE SELECT   Manually correcting the Grant Select on Table w  proper escaping of Schema Table solved my issue      May be unrelated  but I can imagine if one client does this wrong  another might too   Hope that s helpful

User · Answer

I just emptied my session data then it worked again  Here is where you find the button

User · Answer

You need to give privileges to the particular user by giving the command mysql gt  GRANT ALL PRIVILEGES   To  username   localhost   and then give FLUSH PRIVILEGES  command  Then it won t give this error    hope it helps thank you

User · Answer

In MySQL query browser go to Tools tab MySQL Administrator    User Administration and then give the privileges to user

User · Answer

This error also arises for a syntax error occurred due to aliasing tablename   For instance  when executed below query       select   from a table1  b table2 where a table1  b table2   below error occurs      MySQL Error   1142  Response form the database  SELECT command denied to user  username ip  for table  table1    Solution   Syntax to alias tablename should be used proper  syntax solution for above instance  select   from table1 a   table2 b where a table1  b table2

User · Answer

I faced the same situation but its funny that reason for the error was due to the use of  the incorrect database or schema name   Its true that multiple issues can lead to error you have mentioned

User · Answer

I had this problem too and for me  the problem was that I moved to a new server and the database I was trying to connect to with my PHP code changed from  my Database  to  my database

User · Answer

I run into this problem as well  the case with me was incorrect naming   I was migrating from local server to online server  my SQL command had  quot database tablename column quot  structure  the name of database in online server was different  for example my code was  quot pet item name quot   while it needed to be  quot pet app item name quot  changing database name solved my problem

User · Answer

You should have to just clear sessions data thats it everything will work

[mysql] MySQL Error: #1142 - SELECT command denied to user

Examples related to mysql

Examples related to select

Examples related to command