Unsuccessful: create table

[Rooftop]

Hi,

I am having problems persisting this class, in fact mysql product_group table cannot be created. It is related to this mysql topic:

http://forums.mysql.com/read.php?22,19755,19755#msg-19755

Console output:


Unsuccessful: create table product_group (id bigint not null auto_increment, version integer, index varchar(255), postfix bigint, prefix bigint, primary key (id))
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 'varchar(255), postfix bigint, prefix bigint, primary key (id))' at line 1
Unsuccessful: alter table product add index FKED8DCCEF701A29C6 (productgroup), add constraint FKED8DCCEF701A29C6 foreign key (productgroup) references product_group (id)
Can't create table '.\roo-a\#sql-468_20.frm' (errno: 150)
Unsuccessful: alter table product_group add index FK51F3772F1087ADAA (prefix), add constraint FK51F3772F1087ADAA foreign key (prefix) references prefix (id)
Table 'roo-a.product_group' doesn't exist
Unsuccessful: alter table product_group add index FK51F3772FF7C82724 (postfix), add constraint FK51F3772FF7C82724 foreign key (postfix) references postfix (id)
Table 'roo-a.product_group' doesn't exist



@Entity
@RooEntity
@RooJavaBean
@RooToString
public class ProductGroup {

@NotNull
@Size(min = 4, max = 4)
private String index;

@ManyToOne
@JoinColumn
private Prefix prefix;

@ManyToOne
@JoinColumn
private Postfix postfix;
}


Jira/mysql/Hibernate issue?

Another question, how can i stop roo from creating a new db every time i restart tc-server, or is this related to the above issue?

Regards,

Ronald Vermeire

[Stefan Schmidt]

Hi Ronald,

This Hibernate / MySQL problem is interesting. I managed to reproduce it and find a workaround. I also created a Jira issue for it on your behalf as I already had all scripts for replication in place: http://jira.springframework.org/browse/ROO-129

So it appears, the workaround for the issue is to adjust the @ManyToOne mapping annotation in ProductGroup to have an explicit 'targetEntity' attribute fixes the issue:



@ManyToOne(targetEntity=Prefix.class)
@JoinColumn
private Prefix prefix;


For both Prefix and Postfix.

So as a workaround we should include the targetEntity attribute by default.

Thanks for reporting this!

Now to your other question:


How can i stop roo from creating a new db every time i restart tc-server, or is this related to the above issue?

This can be adjusted in the perisitence.xml configuration. If you change


<property name="hibernate.hbm2ddl.auto" value="create"/>
to
<property name="hibernate.hbm2ddl.auto" value="update"/>

it should retain your data across restarts. However, keep in mind that this would only be used during development as it could have nasty side effects if you forget to turn this off. Even during development this is to be handled with care as you might add new fields which are then not mapped appropriately....

Regards,
Stefan

[Rooftop]

Hello,

Same issue as before now for @OneToMany instead of @ManyToOne.

script:

install jpa -database MYSQL -provider HIBERNATE

add dependency -groupId javax.annotation -artifactId jsr250-api -version 1.0

new persistent class jpa -name ~.key.Prefix -testAutomatically
new persistent class jpa -name ~.key.Postfix -testAutomatically
new persistent class jpa -name ~.key.Productgroup -testAutomatically
new persistent class jpa -name ~.key.Product -testAutomatically

add field string -class ~.key.Prefix -fieldName code -notNull -sizeMax 3

add field string -class ~.key.Postfix -fieldName code -notNull -sizeMax 3

add field string -class ~.key.Productgroup -fieldName name -notNull -sizeMax 30
add field number -class ~.key.Productgroup -fieldName index -type java.lang.Integer
add field set jpa -class ~.key.Productgroup -fieldName prefixes -element ~.key.Prefix
add field set jpa -class ~.key.Productgroup -fieldName postfixes -element ~.key.Postfix

add field string -class ~.key.Product -fieldName name -notNull -sizeMax 30
add field string -class ~.key.Product -fieldName prefix -notNull -sizeMax 3
add field string -class ~.key.Product -fieldName postfix -notNull -sizeMax 3
add field reference jpa -class ~.key.Product -fieldName productgroup -type ~.key.Productgroup

new integration test

new controller automatic -name ~.web.PrefixController -formBackingObject ~.key.Prefix
new controller automatic -name ~.web.PostfixController -formBackingObject ~.key.Postfix
new controller automatic -name ~.web.ProductgroupController -formBackingObject ~.key.Productgroup
new controller automatic -name ~.web.ProductController -formBackingObject ~.key.Product

new selenium test -controller ~.web.PrefixController
new selenium test -controller ~.web.PostfixController
new selenium test -controller ~.web.ProductgroupController
new selenium test -controller ~.web.ProductController

configure logging -level DEBUG -package WEB
install security

dbproperties:

database.password=roo
database.url=jdbc\:mysql\://localhost\:3306/roorc2_v1
database.username=roo
database.driverClassName=com.mysql.jdbc.Driver


I am using rc2. Hope you can find a soultion.


Kind regards,

Ronald

[Rooftop]

Hello,

Solved this one myself partly. It has to do with field name index, which seems to be a reserved word in MySQL, it is working in HSQL though. I thought i read something in the list of improvements about the use of reserved words, maybe 'index' should be added to it?

Kind regards,

Ronald Vermeire

[Stefan Schmidt]

Ronald,

Thanks for the research!

There is a Jira ticket for it under ROO-72 (http://jira.springframework.org/browse/ROO-72), feel free to add a comment and reopen it.

-Stefan