SELECT command denied to user

[Dinko Ivanov]

Hi,

I'm trying to add some reporting functionality to my Spring Java application @ appfog. The CRUD screens work well on appfog, also reporting works good locally.
When I try to execute the select in appfog I get the following error:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command denied to user 'xxxxxxxxxxxxx'@'10.0.33.54' for table 'XXX'

Do you know what does this error mean?

Thanks!
Best Regards,
Dinko

[Dinko Ivanov]

I figured out what the problem was - I was referring the database schema in the SQL query. Removing the schema prefix from table names solved the problem.

Best Regads,
Dinko

[Stanimir Marinov]

Hi, I have the same issue..
I have import DB structure only to DB -> mysql://
10.0.61.97:3306/df4a702104d454964bcf2c2899a1dea5d
I have use phpmyadmin for importing and tables are selectable and editable
from PHP application script. But I have upload Java Spring app with bind to
the same DB service. Java App DataSource is autoconfigured successful but
is seems that DB user don`t have permission to Select from the same DB
df4a702104d454964bcf2c2899a1de

a5d

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
SELECT command denied to user 'uCkjcfcFoQ4Fr'@'10.0.59.179' for table
'my_db_table'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown
Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2690)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1465)
at
org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at
org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at
org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:441)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)

I thinking that is not problem many applications PHP Java.. to read from
shared DB service ?

[Andy Burgess]

I get the feeling some DB user permissions have got screwed up somehow, seems to be affecting MySQL, Mongo 1.8 and Mongo 2.x services across PHP, Java and Ruby.

Have you tickets in with support as well? Mine have been in for over 48hrs with no response.

Andy

[Stanimir Marinov]

I have try to unbind and bind the MySQL service to Java Application but nothing changed only user is different now..

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command denied to user 'unhY06qjhUPcL'@'10.0.59.179' for table ..

[Stanimir Marinov]

I have disable autoconfig (choose Java Web insted of Java Spring) in app log I see DB credentials:

VCAP_SERVICES: {"mysql-5.1":[{"name":"service1-mysql-21234","label":"mysql-5.1","plan":"free","tags":["mysql","mysql-5.1","relational","mysql-5.1","mysql"],"credentials":{"name":"df4a702104d454964bcf2c2899a1dea5d","hostname":"10.0.61.97","host":"10.0.61.97","port":3306,"user":"uEZYKaZy1LM6R","username":"uEZYKaZy1LM6R","password":"pgz8UrJwYQErI"}}]}

connection to DB is successful but if I try SELECT query:

SELECT command denied to user 'uEZYKaZy1LM6R'@'10.0.22.27' for table 'system_properties'

    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)

    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2819)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2768)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1612)
    at com.jolbox.bonecp.StatementHandle.executeQuery(StatementHandle.java:464)

[Stanimir Marinov]

sorry the error is my. sql query include different DB name..

[Stanimir Marinov]

 This error appear if you send query to MySQL DB
with different DB name like "SELECT my_db_name.table_name.column_name ..."
instead of "SELECT df4a702104d454964bcf2c2899a1de.."
I have using JOOQ to generate queries for MySQL and I have regenerate it
with new DB name. Problem is fixed now.