I'm playing with computer-database-jpa (Java) Play Framework 2.1 sample application. Everything works fine when I'm using H2 in memory database but I had problems when I want to connect the application with MySQL.
Some one had the same problem (Help wanted getting sample app connected to MySQL) but there was no solution.
I've added mysql-connector
(Build.scala):
val appDependencies = Seq(
....
"mysql" % "mysql-connector-java" % "5.1.18"
)
and edited application.conf:
db.default.url="jdbc:mysql://password:user@localhost/my-database"
db.default.driver=com.mysql.jdbc.Driver
When I start the applications and apply 1.sql (evolution script) I get an error:
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 'sequence company_seq
start with 1000' at line 1 [ERROR:1064, SQLSTATE:42000]
Does anyone have an idea how to solve the problem?
I've found the solution - https://github.com/opensas/openshift-play2-computerdb.
Syntax used in evolution scripts isn't conform with MySQL
:
List of changes needed to port computer-database sample app from H2 to mysql
conf/evolutions/default/1.sql
- added engine=innodb, to enable referential integrity
- replaced sequences with autoincrement for id fields
- replaced 'SET REFERENTIAL_INTEGRITY' command with 'SET FOREIGN_KEY_CHECKS'
- replaced timestamp fields with datetime
conf/evolutions/default/2.sql
- splitted the computer data between 2.sql and 3.sql file (avoid bug in evolutions running on mysql)
models/Models.scala
- removed 'nulls last' from Computer.list sql query
- modified Computer.insert to skip id field (because is auto-assigned by mysql)
Because I was playing with Java
and not Scala
version I'd to change Company.java
and Computer.java
files. I've added @GeneratedValue
annotation:
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
public Long id;
Here you can find modified evolution scripts: https://github.com/opensas/openshift-play2-computerdb/tree/master/conf/evolutions/default
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With