I´m using MySQL ClearDB Heroku add-on. I´m using the credentials provided by ClearDB and I can succesfully connect to the database using MySQL Workbench and my Spring Boot Application running in Localhost.
I deployed the same Spring code into Heroku, using the same application.properties file but I get this error from Heroku log (after "restarting all Dynos"):
2020-03-10T01:36:59.915901+00:00 app[web.1]: 2020-03-10 01:36:59.914 ERROR 4 --- [ main] o.a.tomcat.jdbc.pool.ConnectionPool : Unable to create initial connections of pool.
2020-03-10T01:36:59.915915+00:00 app[web.1]:
2020-03-10T01:36:59.915915+00:00 app[web.1]: java.sql.SQLException: Access denied for user 'bf8f34598eb4cc'@'ec2-54-173-188-68.compute-1.amazonaws.com' (using password: YES)
2020-03-10T01:36:59.915916+00:00 app[web.1]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-03-10T01:36:59.915916+00:00 app[web.1]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-03-10T01:36:59.915917+00:00 app[web.1]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-03-10T01:36:59.915917+00:00 app[web.1]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:873) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-03-10T01:36:59.915917+00:00 app[web.1]: at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1710) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
After reading this post (using % for host when creating a MySQL User), it seems to me that the problem comes from this: @'ec2-54-173-188-68.compute-1.amazonaws.com
I would like to add that not long ago I was able to connect to the database from my localhost, and from Heroku and I never had this problem before.
My application.properties file:
spring.datasource.url=jdbc:mysql://us-cdbr-iron-east-05.cleardb.net/mydatabase
spring.datasource.username=bf8f34598eb4cc
spring.datasource.password=mypassword
some weeks ago I restarted the password, and I´m sure I´m using the correct one. Also, I was using config vars in application.properties, like this:
spring.datasource.url=${JDBC_DATABASE_URL}
spring.datasource.username=${JDBC_DATABASE_USERNAME}
spring.datasource.password=${JDBC_DATABASE_PASSWORD}
At first I thought that this config vars were not working, but after replacing them with the actual values for url username and password it still doesn´t work.
UPDATE:
Some things I checked in MySQL Workbench:
select current_user();
bf8f34598eb4cc@%
show grants for current_user;
Grants for bf8f34598eb4cc@%
---------------------------
GRANT USAGE ON *.* TO \'bf8f34598eb4cc\'@\'%\' IDENTIFIED BY PASSWORD <secret> WITH MAX_QUERIES_PER_HOUR 3600 MAX_USER_CONNECTIONS 10
GRANT ALL PRIVILEGES ON `MyDatabase`.* TO 'bf8f34598eb4cc'@'%'
SHOW GRANTS FOR 'bf8f34598eb4cc'@'ec2-54-173-188-68.compute-1.amazonaws.com';
Error Code: 1141. There is no such grant defined for user 'bf8f34598eb4cc' on host 'ec2-54-173-188-68.compute-1.amazonaws.com'
GRANT USAGE ON . TO 'bf8f34598eb4cc'@'ec2-54-173-188-68.compute-1.amazonaws.com' IDENTIFIED BY PASSWORD ' WITH MAX_QUERIES_PER_HOUR 3600 MAX_USER_CONNECTIONS 10;
GRANT ALL PRIVILEGES ON heroku_02499bf04b215f2.* TO 'bf8f34598eb4cc'@'ec2-54-173-188-68.compute-1.amazonaws.com';
Error Code: 1045. Access denied for user 'bf8f34598eb4cc'@'%' (using password: YES)
UPDATE2: Now I tried select user(); again in MySQL Workbench. Here is the result:
bf8f34598eb4cc@ec2-54-173-188-68.compute-1.amazonaws.com
It´s the same remote host I was having trouble before, but know it´s shown in ClearDB as a "current Host". Why does this remote host suddenly have acces?
I tried to access from Heroku, but now the access is denied to this remote host:'ec2-18-206-248-191.compute-1.amazonaws.com'. Here is Heroku Log:
2020-03-10T15:38:45.624024+00:00 app[web.1]: 2020-03-10 15:38:45.623 ERROR 4 --- [io-59874-exec-2] o.a.tomcat.jdbc.pool.ConnectionPool : Unable to create initial connections of pool.
2020-03-10T15:38:45.624025+00:00 app[web.1]:
2020-03-10T15:38:45.624026+00:00 app[web.1]: java.sql.SQLException: Access denied for user 'bf8f34598eb4cc'@'ec2-18-206-248-191.compute-1.amazonaws.com' (using password: YES)
2020-03-10T15:38:45.624027+00:00 app[web.1]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-03-10T15:38:45.624027+00:00 app[web.1]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-03-10T15:38:45.624028+00:00 app[web.1]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-03-10T15:38:45.624028+00:00 app[web.1]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:873) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
2020-03-10T15:38:45.624029+00:00 app[web.1]: at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1710) ~[mysql-connector-java-5.1.44.jar!/:5.1.44]
In summary, I´m using the same user for MySQL Workbench and Heroku, but there is access denied for every "remote host" used from Heroku. Why does this happen even if bf8f34598eb4cc@% has "All Privileges" as shown before?
Any help will be deeply appreciated.
Thanks!
