Is it possible to change H2 in-memory database to PostgreSQL mode?

1,434 views
Skip to first unread message

Matt Raible

unread,
Oct 5, 2011, 11:49:50 PM10/5/11
to play-fr...@googlegroups.com
I'm running my Play application on Heroku, which uses PostgreSQL for its database. I have my database setup as follows:

db=mem
%prod.db=${DATABASE_URL}

When I deploy to Heroku, my app fails to start b/c my evolutions are not applied:

2011-10-06T02:52:53+00:00 app[web.1]: 02:52:53,579 INFO ~ Connected to jdbc:postgresql://ec2-107-20-239-133.compute-1.amazonaws.com/lniqlrwlpj
2011-10-06T02:52:53+00:00 app[web.1]: 02:52:53,756 ERROR ~
2011-10-06T02:52:53+00:00 app[web.1]:
2011-10-06T02:52:53+00:00 app[web.1]: Exception in thread "main" play.db.Evolutions$InconsistentDatabase
2011-10-06T02:52:53+00:00 app[web.1]: at play.db.Evolutions.checkEvolutionsState(Evolutions.java:327)
2011-10-06T02:52:53+00:00 app[web.1]: at play.db.Evolutions.onApplicationStart(Evolutions.java:203)
2011-10-06T02:52:53+00:00 app[web.1]: ~ _ _
2011-10-06T02:52:53+00:00 app[web.1]: ~ _ __ | | __ _ _ _| |
2011-10-06T02:52:53+00:00 app[web.1]: ~ | '_ \| |/ _' | || |_|
2011-10-06T02:52:53+00:00 app[web.1]: ~ | __/|_|\____|\__ (_)
2011-10-06T02:52:53+00:00 app[web.1]: ~ |_| |__/
2011-10-06T02:52:53+00:00 app[web.1]: ~
2011-10-06T02:52:53+00:00 app[web.1]: ~ play! 1.2.3, http://www.playframework.org
2011-10-06T02:52:53+00:00 app[web.1]: ~ framework ID is prod
2011-10-06T02:52:53+00:00 app[web.1]: ~
2011-10-06T02:52:53+00:00 app[web.1]: ~ Ctrl+C to stop
2011-10-06T02:52:53+00:00 app[web.1]: ~
2011-10-06T02:52:53+00:00 app[web.1]:
2011-10-06T02:52:54+00:00 heroku[web.1]: Process exited
2011-10-06T02:52:55+00:00 heroku[web.1]: State changed from starting to crashed

So then I run heroku run to apply them, but it fails b/c my evolutions are in MySQL syntax.

$ heroku run "play evolutions:apply --%prod"
Running play evolutions:apply --%prod attached to terminal... up, run.7
~ _ _
~ _ __ | | __ _ _ _| |
~ | '_ \| |/ _' | || |_|
~ | __/|_|\____|\__ (_)
~ |_| |__/
~
~ play! 1.2.3, http://www.playframework.org
~ framework ID is prod
~
~ Connected to jdbc:postgresql://ec2-107-20-239-133.compute-1.amazonaws.com/lniqlrwlpj
~ Application revision is 1 [cf762e4] and Database revision is 0 [da39a3e]
~
~ Applying evolutions:

# ------------------------------------------------------------------------------

# --- Rev:1,Ups - cf762e4

CREATE TABLE User (
id bigint(20) NOT NULL AUTO_INCREMENT,
email varchar(255) NOT NULL,
password varchar(255) NOT NULL,
firstName varchar(100) NOT NULL,
lastName varchar(100) NOT NULL,
PRIMARY KEY (id)
);

# ------------------------------------------------------------------------------

21:53:10,758 ERROR ~ Can't apply evolution
org.postgresql.util.PSQLException: ERROR: syntax error at or near "User"
Position: 14
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366)
at play.db.Evolutions.execute(Evolutions.java:464)
at play.db.Evolutions.applyScript(Evolutions.java:257)
at play.db.Evolutions.main(Evolutions.java:120)
~
~ Can't apply evolutions...

Is it possible to change H2 in-memory database to PostgreSQL mode?

I tried changing application.conf to have:

db=jdbc:h2:mem:play;MODE=PostgreSQL

But when I run "play evolutions", it still connects in MySQL mode.

21:48:33,937 INFO ~ Listening for HTTP on port 9000 (Waiting a first request to start) ...
21:48:55,057 INFO ~ Connected to jdbc:h2:mem:play;MODE=MYSQL;LOCK_MODE=0

Is it possible to change h2 to PostgreSQL mode? Or is there a better way to manage a dev in-memory database and a prod PostgreSQL database?

Thanks,

Matt

Pascal Voitot Dev

unread,
Oct 6, 2011, 2:47:52 AM10/6/11
to play-fr...@googlegroups.com
Hi,

Look in DBPlugin.java, I think it is possible.

        if ("mem".equals(p.getProperty("db")) && p.getProperty("db.url") == null) {
            p.put("db.driver", "org.h2.Driver");
            p.put("db.url", "jdbc:h2:mem:play;MODE=MYSQL");
            p.put("db.user", "sa");
            p.put("db.pass", "");
        }

Just remove the "db=mem" and create a classic db config with the right MODE (those H2 modes are funny but not 100% compliant so I had to scratch my head for play-siena around them ;) )

Pascal


--
You received this message because you are subscribed to the Google Groups "play-framework" group.
To post to this group, send email to play-fr...@googlegroups.com.
To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.


canavar

unread,
Oct 6, 2011, 6:44:15 AM10/6/11
to play-fr...@googlegroups.com

BTW user is a reserved word in Postgresql. Change your table name to users.

@Table(name="users")

06 Eki 2011 09:47 tarihinde "Pascal Voitot Dev" <pascal.v...@gmail.com> yazdı:

> Hi,
>
> Look in DBPlugin.java, I think it is possible.
>
> if ("mem".equals(p.getProperty("db")) && p.getProperty("db.url") ==
> null) {
> p.put("db.driver", "org.h2.Driver");
> p.put("db.url", "jdbc:h2:mem:play;MODE=MYSQL");
> p.put("db.user", "sa");
> p.put("db.pass", "");
> }
>
> Just remove the "db=mem" and create a classic db config with the right MODE
> (those H2 modes are funny but not 100% compliant so I had to scratch my head
> for play-siena around them ;) )
>
> Pascal
>
> On Thu, Oct 6, 2011 at 5:49 AM, Matt Raible <mra...@gmail.com> wrote:
>
>> I'm running my Play application on Heroku, which uses PostgreSQL for its
>> database. I have my database setup as follows:
>>
>> db=mem
>> %prod.db=${DATABASE_URL}
>>
>> When I deploy to Heroku, my app fails to start b/c my evolutions are not
>> applied:
>>
>> 2011-10-06T02:52:53+00:00 app[web.1]: 02:52:53,579 INFO ~ Connected to
>> jdbc:postgresql://ec2-107-20-239-133.compute-1.amazonaws.com/lniqlrwlpj
>> 2011-10-06T02:52:53+00:00<http://ec2-107-20-239-133.compute-1.amazonaws.com/lniqlrwlpj%0A2011-10-06T02:52:53+00:00>app[web.1]: 02:52:53,756 ERROR ~

Pascal Voitot Dev

unread,
Oct 6, 2011, 6:46:17 AM10/6/11
to play-fr...@googlegroups.com
no DON'T use "USERS", it's reserved in H2 MEM :D:D:D
really, I spent a few time last week on this issue :):):)

Pascal

canavar

unread,
Oct 6, 2011, 6:52:44 AM10/6/11
to play-fr...@googlegroups.com

Haha:) i never had problem with h2 on fs. so if you use h2 on mem you may use smth like user_tbl:)

06 Eki 2011 13:46 tarihinde "Pascal Voitot Dev" <pascal.v...@gmail.com> yazdı:

sas

unread,
Oct 6, 2011, 4:21:00 PM10/6/11
to play-framework
with postgresql I did it like this, and it worked ok


https://github.com/opensas/play-demo/blob/14-deploy_to_gae/app/models/User.java

---
import javax.persistence.*;
import java.util.*;

@Entity()
@Table(name="\"User\"")
public class User extends Model {

public String name;
public String avatarUrl;
---

saludos

sas


On 6 oct, 07:52, canavar <fehmican.sag...@gmail.com> wrote:
> Haha:) i never had problem with h2 on fs. so if you use h2 on mem you may
> use smth like user_tbl:)
>  06 Eki 2011 13:46 tarihinde "Pascal Voitot Dev" <
> pascal.voitot....@gmail.com> yazdı:
>
>
>
> > no DON'T use "USERS", it's reserved in H2 MEM :D:D:D
> > really, I spent a few time last week on this issue :):):)
>
> > Pascal
>
> > On Thu, Oct 6, 2011 at 12:44 PM, canavar <fehmican.sag...@gmail.com>
> wrote:
>
> >> BTW user is a reserved word in Postgresql. Change your table name to
> users.
>
> >> @Table(name="users")
> >> 06 Eki 2011 09:47 tarihinde "Pascal Voitot Dev" <
> >> pascal.voitot....@gmail.com> yazdı:
>
> >> > Hi,
>
> >> > Look in DBPlugin.java, I think it is possible.
>
> >> > if ("mem".equals(p.getProperty("db")) && p.getProperty("db.url") ==
> >> > null) {
> >> > p.put("db.driver", "org.h2.Driver");
> >> > p.put("db.url", "jdbc:h2:mem:play;MODE=MYSQL");
> >> > p.put("db.user", "sa");
> >> > p.put("db.pass", "");
> >> > }
>
> >> > Just remove the "db=mem" and create a classic db config with the right
> >> MODE
> >> > (those H2 modes are funny but not 100% compliant so I had to scratch my
> >> head
> >> > for play-siena around them ;) )
>
> >> > Pascal
>
> >> > On Thu, Oct 6, 2011 at 5:49 AM, Matt Raible <mrai...@gmail.com> wrote:
>
> >> >> I'm running my Play application on Heroku, which uses PostgreSQL for
> its
> >> >> database. I have my database setup as follows:
>
> >> >> db=mem
> >> >> %prod.db=${DATABASE_URL}
>
> >> >> When I deploy to Heroku, my app fails to start b/c my evolutions are
> not
> >> >> applied:
>
> >> >> 2011-10-06T02:52:53+00:00 app[web.1]: 02:52:53,579 INFO ~ Connected to
> >> >> jdbc:postgresql://
>
> ec2-107-20-239-133.compute-1.amazonaws.com/lniqlrwlpj>> >> 2011-10-06T02:52:53+00:00<
>
> http://ec2-107-20-239-133.compute-1.amazonaws.com/lniqlrwlpj%0A2011-1...
> >> >> ~ play! 1.2.3,http://www.playframework.org
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement. java:366)

Matt Raible

unread,
Oct 18, 2011, 6:58:29 PM10/18/11
to play-framework
Quoting "User" seems to work:

CREATE TABLE "User" (
id bigint(20) NOT NULL AUTO_INCREMENT,
email varchar(255) NOT NULL,
password varchar(255) NOT NULL,
firstName varchar(100) NOT NULL,
lastName varchar(100) NOT NULL,
PRIMARY KEY (id)
);

#
------------------------------------------------------------------------------

~
~ Evolutions script successfully applied!

To make this work, I had to modify my User object:

object User extends Magic[User](Some("\"User\"")) {

And quote it in SQL statements:

join "User" u on w.user_id = u.id

However, I'm not sure how to reference it my evolutions as a foreign
key. I tried:

FOREIGN KEY (user_id) REFERENCES "User"(id),

But this results in the following error from Anorm:

RuntimeException occured : ColumnNotFound("User".id)

play.exceptions.JavaExecutionException: ColumnNotFound("User".id)
at play.mvc.ActionInvoker.invoke(ActionInvoker.java:229)
at Invocation.HTTP Request(Play!)
Caused by: java.lang.RuntimeException: ColumnNotFound("User".id)

If anyone has any tips, I'd love to hear them. Of course, if this is
not possible, I'll try renaming my database table.

Thanks!

Matt

On Oct 6, 2:21 pm, sas <open...@gmail.com> wrote:
> with postgresql I did it like this, and it worked ok
>
> https://github.com/opensas/play-demo/blob/14-deploy_to_gae/app/models...
>
> ---
> import javax.persistence.*;
> import java.util.*;
>
> @Entity()
> @Table(name="\"User\"")
> public classUserextends Model {
>
>     public String name;
>     public String avatarUrl;
> ---
>
> saludos
>
> sas
>
> On 6 oct, 07:52, canavar <fehmican.sag...@gmail.com> wrote:
>
>
>
>
>
>
>
> > Haha:) i never had problem with h2 on fs. so if you use h2 on mem you may
> > use smth like user_tbl:)
> >  06 Eki 2011 13:46 tarihinde "Pascal Voitot Dev" <
> > pascal.voitot....@gmail.com> yazdı:
>
> > > no DON'T use "USERS", it's reserved in H2 MEM :D:D:D
> > > really, I spent a few time last week on this issue :):):)
>
> > > Pascal
>
> > > On Thu, Oct 6, 2011 at 12:44 PM, canavar <fehmican.sag...@gmail.com>
> > wrote:
>
> > >> BTWuseris a reserved word in Postgresql. Change your table name to
> > users.
>
> > >> @Table(name="users")
> > >> 06 Eki 2011 09:47 tarihinde "Pascal Voitot Dev" <
> > >> pascal.voitot....@gmail.com> yazdı:
>
> > >> > Hi,
>
> > >> > Look in DBPlugin.java, I think it is possible.
>
> > >> > if ("mem".equals(p.getProperty("db")) && p.getProperty("db.url") ==
> > >> > null) {
> > >> > p.put("db.driver", "org.h2.Driver");
> > >> > p.put("db.url", "jdbc:h2:mem:play;MODE=MYSQL");
> > >> > p.put("db.user", "sa");
> > >> > p.put("db.pass", "");
> > >> > }
>
> > >> > Just remove the "db=mem" and create a classic db config with the right
> > >> MODE
> > >> > (those H2 modes are funny but not 100% compliant so I had to scratch my
> > >> head
> > >> > for play-siena around them ;) )
>
> > >> > Pascal
>
Reply all
Reply to author
Forward
0 new messages