Saturday, May 7, 2016

Heroku Database Problems

I have been using Heroku for over a year now and have learned quite a bit along the way. I have a very simple Java web application for my work softball team, TAP Softball. It uses a Postgres database and contains basic information like the teams, schedule and standings. The database connection is configured using Spring and Hibernate, with the database URL and credentials hard coded right into the Spring context file. Using Eclipse and the Tomcat plugin I can deploy the web application locally to test changes prior to deploying to the server. I also have a local database I use for testing database changes before pushing to the live database. In the Spring context file I have a commented out localhost datasource defined, which I swap in when testing locally.

Yesterday, I received an email from Heroku that my database was going to be upgraded with the latest Postgres changes. A few minutes later an email confirmed the upgrade finished successfully. I had seen these messages before and thought nothing of it until this morning when I tried to use the site and got a big fat stack trace.

It didn’t take long to see the database URL and credentials had changed, which as you probably figured out was problematic since I had them hard coded. I quickly scrambled to fix the issue because I didn’t have access to my MacBook. The Dropbox deployment method didn’t work so I installed the Heroku Toolbelt and was able to update the database connection information. The site was back online, so I took a breather and then emailed support to find out what was going on.

Support promptly responded to my message and explained that if an upgrade doesn’t go perfectly the database will move to a different address and reset the credentials. He also said I should be loading my database connection information from the environment variables, so it will start up correctly should the database move again. I did a little searching and found Connecting to Relational Databases on Heroku with Java. After scrolling down I quickly found that I had to add a new Spring bean that would instantiate a Java URI using the DATABASE_URL environment variable. I also had to update my existing dataSource bean to get the host, port, path, username and password from the URI bean. I pushed the change up to Heroku and it worked.

My next challenge was to get my development environment working again, because now Tomcat was getting a null pointer exception when trying to generate the URI bean. After some trial and error I figured out that I needed to modify the run configuration for the Tomcat server in Eclipse to have a new entry in the Environment tab for the DATABASE_URL. The value to use came from my app’s database page on the Heroku site, in the last item called URL. Making this change and starting the server loaded the homepage, but when I attempted to load a page that used the database I received an error about SSL.

I remembered having problems connecting to the database originally, and to fix it I added ?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory to the end of the path. Unfortunately just sticking that onto the end of my Run Configuration’s environment variable didn’t work. I also had to add + '?' + @dbUrl.getQuery() to the end of the url property in my datasource bean. In the URI the part after the question mark is called the query and by appending it to the end of the path it fixed my issue. I pushed the change up to the server and it still worked fine. The best part is now I can easily create separate run configurations for the remote and local databases rather than randomly updating one of my Spring configuration files.

No comments:

Post a Comment