Podróż na żywo – Confitura ’18

Nie ma to jak wyjazd z zaskoczenia! W środę zwolnił się jeden bilet na Confiturze, na którą wybiera się też Kasia, więc ekspresowo zorganizowałem podróż dla mnie. Weekend spędzam więc częściowo w Warszawie, a relacja na żywo z Confitury i ogólnie wyjazdu będzie tu na blogu. Zaczynamy dziś około szesnastej. Zapraszam na wspólną podróż na żywo!

Using reserved MySQL words as Hibernate field/table names

As I came back to Matchlogger development today after a short break, I wanted to implement adding matches. I added a Liquibase migration, created all the required repositories and an entity class that looked like this:

When I opened the application and triggered a controller method that aimed to get all matches from the database, I saw an error message and a stack trace that had a cryptic message at the top:

2017-04-24 20:08:29.337 WARN 3190 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1064, SQLState: 42000
2017-04-24 20:08:29.337 ERROR 3190 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match match0_' at line 1
2017-04-24 20:08:29.357 ERROR 3190 --- [nio-8080-exec-4] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match match0_' at line 1
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_121]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_121]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_121]
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_121]
...

Not exactly the most meaningful message. So I added more logs into application.properties (Spring Boot FTW):

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true

Right now I at least had an SQL that I could run:

sql> select
  match0_.id as id1_1_,
  match0_.first_team_id as first_te3_1_,
  match0_.match_date as match_da2_1_,
  match0_.second_team_id as second_t4_1_
from
  match match0_
[2017-04-24 20:09:08] [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match match0_' at line 7

As you can see, the message is still cryptic. There is a mention of error 1064 here. I found a reference that suggested that it might be because of reserved words. DB engines use some specific words that may be part of their syntax and using them usually requires adding backticks to the name. Is match such a word? Turns out it is.

How to solve that?

The solution is simple. The only thing I needed to do was to add quotation marks to the table name in entity class:

You will see the same issue especially with users. An obvious choice is always naming the table with users records as user which is a reserved, well, anywhere I remember. I did foresee that one and named the table ml_user, but didn’t expect to come across the same issue with matches.

Where can I deploy my Java web app?

One of the very important topics in process of creating an application is distributing it to the end users. In most cases, a web application needs to be accessible, well, from the Web. That means that we want to have a place and an address where it will be available. Usually, we’d also like to have a separate test environment where we could test changes in a production-like environment, as „works on my machine” is not enough. With Matchlogger, I will have some very specific requirements for the environment providers:

  • The environment needs to be able to run a Spring Boot app, so it needs to support Java;
  • I use a MySQL database – there should be a way to provide one;
  • I will be using a custom domain name – I need to make sure I can connect a domain I already bought with my application;
  • I need to be able to add a certificate to support connecting through HTTPS;
  • It would be nice if I didn’t have to pay a lot for all that, so my maximum budget is for now about $10 per month. I don’t run a company for Matchlogger, so I expect small costs – at least at the beginning where it’s just an app I thought might be nice for me.

So, what options do we have? There are two main approaches we could use – we could either buy a service from one of the platform-as-a-service providers, like Heroku, Google App Engine or Openshift, or we could configure our own environment at one of the VPS providers. The first approach is easier for a developer (and saves you the hassle of making sure everything is up to date and configured correctly), but also has its restrictions and usually is more costly.

In case you happen to know any others that you’d recommend, feel free to leave a note.

Heroku

For some time, it was the go-to platform for many developers. It feels like the hype has waned a bit, but it’s still one of the main developer-friendly platforms. One doesn’t have to worry too much about configuration specifics – you need to define a Procfile and git push it onto the remote.

  • Java support? Out of the box.
  • MySQL? Here it gets complicated.
    • You can use a ClearDB addon that adds a database as a service. In free tier, your database can have 5 MB of size and it allows 10 parallel connections. Paid plans start at $9.99 per month for a 1GB database with 15 connections. Not too amused.
    • Another option is to use the database you already have with your hosting account if you have one and you’re able to connect from outside. I did that before with Heroku, connecting to my linuxpl.com account. It worked, but I don’t have too much free space left there.
  • You can add a custom domain to Heroku both in free and paid plans. There’s a guide that describes the process.
  • SSL: With custom domains, apparently you need to use SSL endpoint addon. Price: $20 per month. Woah.
  • Cost: Well, it depends.
    • Free tier is limited to 1 node with 1 process. It can use 1000 hours of activity per month (the longest month has 744 hours – so you can run more than one app for some time). What is activity time? It’s when somebody’s using your app so it isn’t asleep. Heroku free containers start sleep mode after 30 minutes of inactivity. It means that first user that trips onto inactive application will experience delays as the app will start up. Of course, there are ways to circumvent that (say, have a task that will call your app every x minutes), but they might likely be breaking their Terms of Service. My colleagues were also complaining that free service is incredibly slow.
    • Hobby tier which might be more suitable for that app is $7 per dyno/month. There is a part saying that it contains free SSL and automated certificate management for custom domains, so if I understand it correctly, that might save the $20 per month payment.

In the case of Heroku-based setup for Matchlogger, we’re expecting payments between $7 and $37 per month. This is more than I expected, but let’s take a look at other options.

Openshift

Another platform that is mentioned as a good one, especially among Java developers. It can also deploy an app by git push. Its new next-gen service is based on Docker images which might also be an asset. You can have up to three gears (likely their equivalent of Heroku’s dyno) for free and you pay for using more.

  • Java? Sure.
  • MySQL? Sure, a private database with full control. One important note is that each of the gears has only 1 GB of space, presumably for everything that’s related to the application. Additional gigabyte costs $1 per month.
  • Custom domain? In a bronze plan that’s hours-based and (if I understand correctly) includes three free gears.
  • SSL? As with custom domains.
  • Cost: In a bronze plan that would be more than enough for my purposes (I’d likely keep myself within 3 free gears) I might be able to use free tier only. In case I need to buy one gear to work 24/7 and, say, 3 GB of space, the cost would be about $17 per month.

Looks like Red Hat’s offer for Openshift is way better than Heroku’s.

Google App Engine

One doesn’t have to introduce Google. They, too, offer their platform as a service.

  • Java is supported
  • MySQL is offered through Google Cloud SQL. Their estimate for 5GB of 24/7, 1-instance DB is $8.54 per month. With 1GB instead of 5GB the estimation is $7.84.
  • Custom domain and SSL seem to be free of charge.
  • Instance – looks like it might fit in the free tier. At least that’s what I figured out from their calculator.

In this case, the only cost that’s visible upfront might be related to their DB services. Doesn’t look bad, but one must take a better look at the calculated values. They also have their own datastore that they like to promote instead of their SQL services.

Amazon AWS

One of the most popular services for application hosting. Their free tier offers 750 hours of computing time each month for a year. That’s enough to host one instance for a year – no costs attached. Afterwards, they still have a lot of other free tiers that might be helpful.

  • Java support – there are tutorials on Amazon website on how to deploy Spring Boot app on EC2.
  • MySQL is offered via Amazon Relational Database Service. This is also included in the free tier, with 20 GB of space and 750h of DB instance time. After that period, 5 GB with db.t2.micro instance should still be free of charge – if I understand their calculator well.
  • Domain and SSL – while it looks like to be possible (but hard) without it, the best way would be to work with Amazon Route 53. That’s their managed cloud DNS service. With 1 zone, the likely cost is $0.50 per month. SSL can be provisioned by AWS Certificate Manager, apparently free of charge.
  • Instance costs – I will likely want a reserved (though small) instance for Matchlogger. Their t2.nano instance hosted in Germany with 0.5 GB of memory after free year would cost $40 per year if paid entirely upfront. t2.micro with 1GB of memory would be $77.

EC2 is basically a VPS – Virtual Private Server. I don’t have much experience with EC2 yet, but I’d expect that I’ll need to take care of all required upgrades. Deployment can be resolved with AWS CodeDeploy that’s free of charge if you’re deploying to EC2.

I must say that this offer looks really nice. With a small application like I expect Matchlogger to be, I might be able to fit in $4 per year and have a one-year period of free or almost free usage at the beginning.

Other VPS providers

With VPS, you get access to a virtual environment that shares a physical machine with others. You usually don’t experience that, though – the only limitation are the constraints that are imposed on the machine. You usually have SSH access and full freedom to install whatever you wish. With great power comes great responsibility, of course – if you don’t feel well with system administration, this might not be your piece of cake.

There are a lot of VPS providers out there. You can find sites that do VPS comparisons and find out the best deals. As the topic is vast, I will describe only three providers that I am considering for Matchlogger.

DigitalOcean

$5 per month for their basic instance. They are well established in the market and once recommended to me by one of the admins at my company. If you sign up from Docker Cloud link, you get $10 credit for free.

OVH

Very popular hosting provider, both for normal hosting accounts and VPS. Starts at 15 PLN per month. Slightly cheaper than DigitalOcean.

Time4VPS

Hosted in Europe (Vilnius, Lithuania) and surprisingly cheap. 1,24 EUR per month if you pay annually. My colleague suggested it to me and I find the price to be the most catchy factor.

What to choose?

So, I’m still wondering. Looks like Amazon has a really nice ecosystem and one year of free tier, which is appealing. A simple VPS would be the cheapest solution, yet I have some doubts about the fragility of some of the ultra-cheap VPS providers.

What would you choose? Do you suggest any other platforms? Leave a comment!