[Middlegen-user] mysql/innodb: table relationships are there, but middlegen can't see them

Discussion:

sabat

2003-06-09 00:34:05 UTC

I'm having a basic problem with middlegen, trying to get it to recognize
db relationships.

- mysql 4.0.13
- linux 2.4.20
- jboss 3.2.2RC1
- middlegen 2.0 b1

I've included more details below, but in a nutshell, if I do this in mysql:

===
create table duh1 (id smallint primary key, blah varchar(50)) type=InnoDB;
create table duh2 (id smallint primary key, blah varchar(50)) type=InnoDB;

create table duhjoin (duh1id smallint references duh1(id), duh2id
smallint references duh2(id), primary key (duh1id,duh2id)) type=InnoDB;

insert into duh1 (1, 'blah');
insert into duh1 (2, 'blah');

insert into duh2 (11, 'blah');
insert into duh2 (12, 'blah');

insert into duhjoin(1,11);
insert into duhjoin(1,12);
insert into duhjoin(2,11);
===

and tweak the middlegen sample project to look at these tables:

===
<table generate="true" name="duh1"/>
<table generate="true" name="duh2"/>
<table generate="false" name="duhjoin"/>

<many2many>
<tablea generate="true" name="duh1"/>
<jointable name="duhjoin" generate="false"/>
<tableb generate="true" name="duh2"/>
</many2many>

===

I end up with this error at the bottom of its run:

===
[middlegen] (middlegen.MiddlegenPopulator 222 ) WARNING:
Middlegen couldn't find any relations between any tables. This may be
intentional from the design of the database, but it may also be because
you have incorrectly defined the relationships. It could also be because
the JDBC driver you're using doesn't correctly implement
DatabaseMetaData. See the samples (for an example on how to define
relationships) and verify that your driver correctly implements
DatabaseMetaData.
[middlegen] (middlegen.MiddlegenPopulator 828 ) The many2many
relation duh1-duhjoin-duh2 was declared, but not identified.
===

I've tried a bunch of things, like making sure I'm creating InnoDB
tables, changing JDBC drivers, mucking around with table names, tweaking
the table names in build.xml, etc. No joy.

Am I doing something stupid that just escapes me? The docs do say that
relation-detection does work with mysql.

TIA!

Steve

==> ant output:

Buildfile: build.xml

check-driver-present:

panic-if-driver-not-present:

middlegen:
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin simple->middlegen.Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin java->middlegen.java.JavaPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin plugin->middlegen.Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin cmp20->middlegen.plugins.entitybean.CMP20Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin bmp20->middlegen.plugins.entitybean.BMP20Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin html->middlegen.plugins.html.HTMLReportPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin sunjdo10->middlegen.plugins.jdo.sun.SunJDO10Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin servlet->middlegen.plugins.servlet.ServletPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin struts->middlegen.plugins.struts.StrutsPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin jsp->middlegen.plugins.struts.JspPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin cmp20->middlegen.plugins.entitybean.CMP20Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin bmp20->middlegen.plugins.entitybean.BMP20Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin html->middlegen.plugins.html.HTMLReportPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin sunjdo10->middlegen.plugins.jdo.sun.SunJDO10Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin servlet->middlegen.plugins.servlet.ServletPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin struts->middlegen.plugins.struts.StrutsPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin jsp->middlegen.plugins.struts.JspPlugin
[middlegen] Database URL:jdbc:mysql://localhost/test
[middlegen] (middlegen.MiddlegenPopulator 222 ) WARNING:
Middlegen couldn't find any relations between any tables. This may be
intentional from the design of the database, but it may also be because
you have incorrectly defined the relationships. It could also be because
the JDBC driver you're using doesn't correctly implement
DatabaseMetaData. See the samples (for an example on how to define
relationships) and verify that your driver correctly implements
DatabaseMetaData.
[middlegen] (middlegen.MiddlegenPopulator 828 ) The many2many
relation duh1-duhjoin-duh2 was declared, but not identified.
[middlegen] (middlegen.Middlegen 396 ) Validating cmp20
[middlegen] (entitybean.JBoss ? ) WARNING (cmp20):
JBoss 3.0 doesn't support fields that are part of CMR to be CMP at the
same time. You might want to set fkcmp="false" in the cmp20 plugin.
[middlegen] (middlegen.Middlegen 396 ) Validating sunjdo10
[middlegen] (middlegen.Middlegen 396 ) Validating html
[middlegen] (middlegen.Middlegen 396 ) Validating simple
[middlegen] (middlegen.Middlegen 396 ) Validating struts
[middlegen] (middlegen.Middlegen 396 ) Validating myjsp
[middlegen] ********************************************************
[middlegen] * CTRL-Click relations to modify their cardinality *
[middlegen] * SHIFT-Click relations to modify their directionality *
[middlegen] ********************************************************

==> build.xml (only slightly modified from the distributed version, so I
just included the differences):

<?xml version="1.0"?>

...

<!DOCTYPE project [
<!ENTITY database SYSTEM "file:./config/database/mysql.xml">
<!ENTITY ejb SYSTEM "file:./config/ejb/jboss.xml">
]>

...

<table generate="true" name="duh1"/>
<table generate="true" name="duh2"/>
<table generate="false" name="duhjoin"/>

<many2many>
<tablea generate="true" name="duh1"/>
<jointable name="duhjoin" generate="false"/>
<tableb generate="true" name="duh2"/>
</many2many>

Eivind Waaler

2003-06-09 08:44:02 UTC

Permalink

Mysql requires indexes on all foreign key columns. Try with the following
definition of your join table (or similar where you have index on all
foreign key columns):

create table duhjoin (
duh1id smallint NOT NULL,
duh2id smallint NOT NULL,
primary key (duh1id,duh2id),
INDEX duh1_ind(duh1id),
INDEX duh2_ind(duh2id),
FOREIGN KEY (duh1id) REFERENCES duh1(id),
FOREIGN KEY (duh2id) REFERENCES duh2(id)
) type=InnoDB;

At least this solved it for me last time I had similar problems.

.eivind

Post by sabat
I'm having a basic problem with middlegen, trying to get it to recognize
db relationships.
- mysql 4.0.13
- linux 2.4.20
- jboss 3.2.2RC1
- middlegen 2.0 b1
===
create table duh1 (id smallint primary key, blah varchar(50)) type=InnoDB;
create table duh2 (id smallint primary key, blah varchar(50)) type=InnoDB;
create table duhjoin (duh1id smallint references duh1(id), duh2id
smallint references duh2(id), primary key (duh1id,duh2id)) type=InnoDB;
insert into duh1 (1, 'blah');
insert into duh1 (2, 'blah');
insert into duh2 (11, 'blah');
insert into duh2 (12, 'blah');
insert into duhjoin(1,11);
insert into duhjoin(1,12);
insert into duhjoin(2,11);
===
===
<table generate="true" name="duh1"/>
<table generate="true" name="duh2"/>
<table generate="false" name="duhjoin"/>
<many2many>
<tablea generate="true" name="duh1"/>
<jointable name="duhjoin" generate="false"/>
<tableb generate="true" name="duh2"/>
</many2many>
===
===
Middlegen couldn't find any relations between any tables. This may be
intentional from the design of the database, but it may also be because
you have incorrectly defined the relationships. It could also be because
the JDBC driver you're using doesn't correctly implement
DatabaseMetaData. See the samples (for an example on how to define
relationships) and verify that your driver correctly implements
DatabaseMetaData.
[middlegen] (middlegen.MiddlegenPopulator 828 ) The many2many
relation duh1-duhjoin-duh2 was declared, but not identified.
===
I've tried a bunch of things, like making sure I'm creating InnoDB
tables, changing JDBC drivers, mucking around with table names, tweaking
the table names in build.xml, etc. No joy.
Am I doing something stupid that just escapes me? The docs do say that
relation-detection does work with mysql.
TIA!
Steve
Buildfile: build.xml
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin simple->middlegen.Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin java->middlegen.java.JavaPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin plugin->middlegen.Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin cmp20->middlegen.plugins.entitybean.CMP20Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin bmp20->middlegen.plugins.entitybean.BMP20Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin html->middlegen.plugins.html.HTMLReportPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin sunjdo10->middlegen.plugins.jdo.sun.SunJDO10Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin servlet->middlegen.plugins.servlet.ServletPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin struts->middlegen.plugins.struts.StrutsPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin jsp->middlegen.plugins.struts.JspPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin cmp20->middlegen.plugins.entitybean.CMP20Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin bmp20->middlegen.plugins.entitybean.BMP20Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin html->middlegen.plugins.html.HTMLReportPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin sunjdo10->middlegen.plugins.jdo.sun.SunJDO10Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin servlet->middlegen.plugins.servlet.ServletPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin struts->middlegen.plugins.struts.StrutsPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin jsp->middlegen.plugins.struts.JspPlugin
[middlegen] Database URL:jdbc:mysql://localhost/test
Middlegen couldn't find any relations between any tables. This may be
intentional from the design of the database, but it may also be because
you have incorrectly defined the relationships. It could also be because
the JDBC driver you're using doesn't correctly implement
DatabaseMetaData. See the samples (for an example on how to define
relationships) and verify that your driver correctly implements
DatabaseMetaData.
[middlegen] (middlegen.MiddlegenPopulator 828 ) The many2many
relation duh1-duhjoin-duh2 was declared, but not identified.
[middlegen] (middlegen.Middlegen 396 ) Validating cmp20
JBoss 3.0 doesn't support fields that are part of CMR to be CMP at the
same time. You might want to set fkcmp="false" in the cmp20 plugin.
[middlegen] (middlegen.Middlegen 396 ) Validating sunjdo10
[middlegen] (middlegen.Middlegen 396 ) Validating html
[middlegen] (middlegen.Middlegen 396 ) Validating simple
[middlegen] (middlegen.Middlegen 396 ) Validating struts
[middlegen] (middlegen.Middlegen 396 ) Validating myjsp
[middlegen] ********************************************************
[middlegen] * CTRL-Click relations to modify their cardinality *
[middlegen] * SHIFT-Click relations to modify their directionality *
[middlegen] ********************************************************
==> build.xml (only slightly modified from the distributed version, so I
<?xml version="1.0"?>
...
<!DOCTYPE project [
<!ENTITY database SYSTEM "file:./config/database/mysql.xml">
<!ENTITY ejb SYSTEM "file:./config/ejb/jboss.xml">
]>
...
<table generate="true" name="duh1"/>
<table generate="true" name="duh2"/>
<table generate="false" name="duhjoin"/>
<many2many>
<tablea generate="true" name="duh1"/>
<jointable name="duhjoin" generate="false"/>
<tableb generate="true" name="duh2"/>
</many2many>
-------------------------------------------------------
This SF.net email is sponsored by: Etnus, makers of TotalView, The best
thread debugger on the planet. Designed with thread debugging features
you've never dreamed of, try TotalView 6 free at www.etnus.com.
_______________________________________________
middlegen-user mailing list
https://lists.sourceforge.net/lists/listinfo/middlegen-user

sabat

2003-06-09 14:23:32 UTC

Permalink

Eivind:

Thanks for the info; I tried it, but unfortunately, that didn't help.
Same result. :/

Post by Eivind Waaler
Mysql requires indexes on all foreign key columns. Try with the following
definition of your join table (or similar where you have index on all
create table duhjoin (
duh1id smallint NOT NULL,
duh2id smallint NOT NULL,
primary key (duh1id,duh2id),
INDEX duh1_ind(duh1id),
INDEX duh2_ind(duh2id),
FOREIGN KEY (duh1id) REFERENCES duh1(id),
FOREIGN KEY (duh2id) REFERENCES duh2(id)
) type=InnoDB;
At least this solved it for me last time I had similar problems.
.eivind

-------------------------------------------------------
This SF.net email is sponsored by: Etnus, makers of TotalView, The best
thread debugger on the planet. Designed with thread debugging features
you've never dreamed of, try TotalView 6 free at www.etnus.com.
_______________________________________________
middlegen-user mailing list
https://lists.sourceforge.net/lists/listinfo/middlegen-user

sabat

2003-06-09 14:55:01 UTC

Permalink

Argh -- I tried going back to the JDBC driver that comes with Middlegen,
and presto, relationships. So my problem was not realizing that you need
indexes on intersection tables if you want relationships in mysql.
Thanks again to Eivind!

-Steve

Post by sabat
Thanks for the info; I tried it, but unfortunately, that didn't help.
Same result. :/

Post by sabat
I'm having a basic problem with middlegen, trying to get it to
recognize db relationships.
- mysql 4.0.13
- linux 2.4.20
- jboss 3.2.2RC1
- middlegen 2.0 b1
===
create table duh1 (id smallint primary key, blah varchar(50)) type=InnoDB;
create table duh2 (id smallint primary key, blah varchar(50)) type=InnoDB;
create table duhjoin (duh1id smallint references duh1(id), duh2id
smallint references duh2(id), primary key (duh1id,duh2id)) type=InnoDB;
insert into duh1 (1, 'blah');
insert into duh1 (2, 'blah');
insert into duh2 (11, 'blah');
insert into duh2 (12, 'blah');
insert into duhjoin(1,11);
insert into duhjoin(1,12);
insert into duhjoin(2,11);
===
===
<table generate="true" name="duh1"/>
<table generate="true" name="duh2"/>
<table generate="false" name="duhjoin"/>
<many2many>
<tablea generate="true" name="duh1"/>
<jointable name="duhjoin" generate="false"/>
<tableb generate="true" name="duh2"/>
</many2many>
===
===
Middlegen couldn't find any relations between any tables. This may
be intentional from the design of the database, but it may also be
because you have incorrectly defined the relationships. It could
also be because the JDBC driver you're using doesn't correctly
implement DatabaseMetaData. See the samples (for an example on how
to define relationships) and verify that your driver correctly
implements DatabaseMetaData.
[middlegen] (middlegen.MiddlegenPopulator 828 ) The many2many
relation duh1-duhjoin-duh2 was declared, but not identified.
===
I've tried a bunch of things, like making sure I'm creating InnoDB
tables, changing JDBC drivers, mucking around with table names,
tweaking the table names in build.xml, etc. No joy.
Am I doing something stupid that just escapes me? The docs do say
that relation-detection does work with mysql.
TIA!
Steve
Buildfile: build.xml
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin simple->middlegen.Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin java->middlegen.java.JavaPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin plugin->middlegen.Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin cmp20->middlegen.plugins.entitybean.CMP20Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin bmp20->middlegen.plugins.entitybean.BMP20Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin html->middlegen.plugins.html.HTMLReportPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin sunjdo10->middlegen.plugins.jdo.sun.SunJDO10Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin servlet->middlegen.plugins.servlet.ServletPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin struts->middlegen.plugins.struts.StrutsPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin jsp->middlegen.plugins.struts.JspPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin cmp20->middlegen.plugins.entitybean.CMP20Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin bmp20->middlegen.plugins.entitybean.BMP20Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin html->middlegen.plugins.html.HTMLReportPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin sunjdo10->middlegen.plugins.jdo.sun.SunJDO10Plugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin servlet->middlegen.plugins.servlet.ServletPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin struts->middlegen.plugins.struts.StrutsPlugin
[middlegen] (middlegen.Middlegen 352 ) Registering
plugin jsp->middlegen.plugins.struts.JspPlugin
[middlegen] Database URL:jdbc:mysql://localhost/test
Middlegen couldn't find any relations between any tables. This may
be intentional from the design of the database, but it may also be
because you have incorrectly defined the relationships. It could
also be because the JDBC driver you're using doesn't correctly
implement DatabaseMetaData. See the samples (for an example on how
to define relationships) and verify that your driver correctly
implements DatabaseMetaData.
[middlegen] (middlegen.MiddlegenPopulator 828 ) The many2many
relation duh1-duhjoin-duh2 was declared, but not identified.
[middlegen] (middlegen.Middlegen 396 ) Validating cmp20
[middlegen] (entitybean.JBoss ? ) WARNING
(cmp20): JBoss 3.0 doesn't support fields that are part of CMR to be
CMP at the same time. You might want to set fkcmp="false" in the
cmp20 plugin.
[middlegen] (middlegen.Middlegen 396 ) Validating sunjdo10
[middlegen] (middlegen.Middlegen 396 ) Validating html
[middlegen] (middlegen.Middlegen 396 ) Validating simple
[middlegen] (middlegen.Middlegen 396 ) Validating struts
[middlegen] (middlegen.Middlegen 396 ) Validating myjsp
[middlegen] ********************************************************
[middlegen] * CTRL-Click relations to modify their cardinality *
[middlegen] * SHIFT-Click relations to modify their directionality *
[middlegen] ********************************************************
==> build.xml (only slightly modified from the distributed version,
<?xml version="1.0"?>
...
<!DOCTYPE project [
<!ENTITY database SYSTEM "file:./config/database/mysql.xml">
<!ENTITY ejb SYSTEM "file:./config/ejb/jboss.xml">
]>
...
<table generate="true" name="duh1"/>
<table generate="true" name="duh2"/>
<table generate="false" name="duhjoin"/>
<many2many>
<tablea generate="true" name="duh1"/>
<jointable name="duhjoin" generate="false"/>
<tableb generate="true" name="duh2"/>
</many2many>
-------------------------------------------------------
This SF.net email is sponsored by: Etnus, makers of TotalView, The best
thread debugger on the planet. Designed with thread debugging features
you've never dreamed of, try TotalView 6 free at www.etnus.com.
_______________________________________________
middlegen-user mailing list
https://lists.sourceforge.net/lists/listinfo/middlegen-user