cleardb (mySQL) connection problem witth Heroku (spring)

Question

I am creating a springboot application which has data stored in a mysql database. I have used cleardb as an add-on and have deployed the app to Heroku. When I run the app (i.e heroku open), the main page (index.html) displays successfully as it does not require access to the mySQL database. However, all of the pages that do require access to the mySQL database do not display and return the error below:

There was an unexpected error (type=Internal Server Error, status=500). Failed to obtain JDBC Connection; nested exception is com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

Here is some of my code:

Databaseconfig

@Configuration

public class DatabaseConfig {

@Bean
public NamedParameterJdbcTemplate namedParameterJdbcTemplate(DataSource dataSource) {
    return new NamedParameterJdbcTemplate(dataSource);
}

@Bean
public DataSource dataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://localhost/NBA");
    dataSource.setUsername("root");
    dataSource.setPassword("root");
    return dataSource;
}

}

pom.xml plugins

<plugins>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <configuration>
                <source>1.8</source>
                <target>1.8</target>
            </configuration>
        </plugin>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
    </plugins>

database_url (heroku config)

CLEARDB_DATABASE_URL: mysql://b---------9:9--------@us-cdbr-iron-east-05.cleardb.net/heroku_f61b74207636b77?reconnect=true
DATABASE_URL:         'mysql://b---------9:9--------@us-cdbr-iron-east-05.cleardb.net/heroku_f61b74207636b77?reconnect=true'

Some things to take note of:

My application.properties file is currently empty.
The application runs perfectly when I run it on localhost:8080.

Any and all help would be greatly appreciated.

score 2 · Answer 1 · answered Jan 01 '20 at 12:01

2

For your case your must change the dataSource configuration according your database_url(heroku config):

dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://us-cdbr-iron-east-05.cleardb.net/heroku_f61b74207636b77?reconnect=true");
dataSource.setUsername("b05ee51c5eec59");
dataSource.setPassword("94a4e9eb");

I also suggest you to use following in your application.properties instead of using datasource bean:

spring.datasource.username=
spring.datasource.password=
spring.datasource.url=

Also very important: change your database password in the heroku and dont write it on the internet

answered Jan 01 '20 at 12:01

alexej K

152
3

Thanks for the reply! I made the changes you noted, but now I am getting a PreparedStatementCallback; bad SQL grammar error. The error page (status 500) says: ' nested exception is java.sql.SQLSyntaxErrorException: Table 'heroku_f61b74207636b77.players' doesn't exist' – Carnageta Jan 01 '20 at 16:49
^ I am assuming this error is because the 'players' table does not exist in the clearDB database. Is there a way to clone the current mySQL tables onto the cleardb database? – Carnageta Jan 01 '20 at 17:48
1

Yes. You have 2 possibilities 1)With connection data that you posted above(database_url. username and password) you can connect to clearDB and execute create players table statement from for example mysql workbench. 2) You create an Entity class Players with Table and Entity Annotations and put spring.jpa.hibernate.ddl-auto=create in application.properties. Then after each start of application the table will be created. – alexej K Jan 01 '20 at 19:38
I got everything to work smoothly. Thank you so much for all of the help. It is greatly appreciated! – Carnageta Jan 02 '20 at 01:26

cleardb (mySQL) connection problem witth Heroku (spring)

1 Answers1