I'm getting this error on all the tables that have foreign references:


Sep 22, 2003 3:33:30 PM net.sf.hibernate.cfg.Configuration secondPassCompile
INFO: processing foreign key constraints
Sep 22, 2003 3:33:30 PM net.sf.hibernate.tool.hbm2ddl.SchemaUpdate execute
FINE: alter table usersys_User add index (id), add constraint FK42F982E8D1B fore
ign key (id) references consys_Contact (id)
Sep 22, 2003 3:33:30 PM net.sf.hibernate.tool.hbm2ddl.SchemaUpdate execute
SEVERE: Unsuccessful: alter table usersys_User add index (id), add constraint FK
42F982E8D1B foreign key (id) references consys_Contact (id)
java.sql.SQLException: Invalid argument value, message from server: "Too many k
eys specified. Max 32 keys allowed"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:928)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1871)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1805)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1231)
at com.mchange.v2.c3p0.impl.C3P0Statement.executeUpdate(C3P0Statement.ja
va:100)
at net.sf.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:
118)
at net.sf.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.ja
va:182)
at net.sf.hibernate.cfg.Configuration.buildSessionFactory(Configuration.
java:647)
at com.spaceprogram.usersystem.impls.hibernate.UserManagerHibernateImpl.
<init>(UserManagerHibernateImpl.java:53)



Any ideas? looks like a bug to me.

I too get alter table errors when I use SchemaUpdate with MySQL (MyISAM table type):

Error while executing alter table Submission add index (membership), add constraint FKFB08176CB01D87D6 foreign key (membership) references Membership (id)
java.sql.SQLException: Invalid argument value, message from server: "Too many keys specified. Max 32 keys allowed"

I am running: Hibernate 2.0.3, MySQL Connector/J 3.0.8, MySQL 4.0.12

The Hibernate manual -- http://www.hibernate.org/hib_docs/refer ... guide-s1-6 -- points out:

"SchemaUpdate depends heavily upon the JDBC metadata API, so it will not work with all JDBC drivers"

I'm assuming these errors are a side effect of the fact that MySQL MyISAM tables do not support foreign keys. If you look at the SQL that SchemaUpdate is trying to run...

alter table Submission add index (membership), add constraint FKFB08176CB01D87D6 foreign key (membership) references Membership (id)

...and then read the MySQL manual for ALTER TABLE -- http://www.mysql.com/doc/en/ALTER_TABLE.html -- you will find:

"The FOREIGN KEY, CHECK, and REFERENCES clauses don't actually do anything, except for InnoDB type tables..."

So my question is: is this an inherent/known limitation or a bug?

If this is a known limitation, it would be nice if the manual included a list of the popular databases and their compatibility with SchemaUpdate.

Thanks.

Or at least have some options to turn off foreign keys and such and just have it make the tables.

Gavin or a developer, can you add Listener functionality to the Configuration so that I can use the crossdb version from a while back and others can too? Or if someone else wants to make their own version of automatic db creation, then they too can do it.

This would be very helpful since schema update doesn't work properly.

do you think the old crossdb works better than the current schemaupdate ?

last time I checked crossdb did not support half the db's schemaupdate did ....what is the problem with schemaupdate ? I think it would be better to change/improve/fix it's features/errors instead....so, feel free to suggest/contribute fixes/patches.

Yes, for my purposes it did using mysql. It worked better in the sense that it actually worked. ;-)

Fixing the shemaupdate would be fine, but whoever made it doens't seem to want to do it. I and other have posted the errors we get and nobody has responded. Not even to say the good ol' open source credo: "If you want something fixed, then fix it". Which tells me that nobody really cares of this thing works or not, so I suggest the listener thing so I can plug in my own simple thing that works for me. And in this way, the schemaupdate could be a listener in the same way. Just adds more flexibility and offers people different implementations for doing this.

On a site note, i just did a mysqldump and see that I have 30 foreign keys (which is the max allowed) for all my tables. Gotta love that.

Its not so much that no-one cares; its more a problem that it seems to be just about impossible to make SchemaUpdate run correctly on more than two platforms at once. I'm waiting for JDBC drivers to improve their support for JDBC metadata.

This is not critical functionality. It is a development-time "convenience".

P.S. if you want to take on responsibility for this tool then be my guest. But "responsibility" means that you will test any patch on at least:

* Oracle
* DB2
* MySQL
* PostgreSQL
* Microsoft SQL Server 2000

And, preferrably

* HSQLDB

I have seen that not many people are prepared to take on something like that.

And in the JIRA I could only find 2 issues is reported for SchemaExport/Update.....none of them very critical.

So, if you want attention for this stuff - please submit testcase and even possible a solution to the JIRA....

I won't promise anything - but the JIRA is much better at keeping track of these things compared to the forum ;)

"test any patch on at least: "

...and not Sybase????

For most purposes sybase = sql server. In this case, probably not, but I was producing a "minimal" list of the most popular platforms. I'm not especially concerned if SchemaUpdate doesn't run on less popular dbs. It is not core functionality.

"doesn't run on less popular dbs."

OK, but when did Sybase become less popular?

I'm taking an informed guess as to what percentage of our users use each platform.

May I suggest a poll - "What database do you use?"

That would be interesting :-)

Was this ever resolved?

Here is what I'm using
hibernate-2.1
mysql 4.0.18
Tomcat 5.0.19
struts 1.1

I am doing a simple bank system for practice, and everytime I deploy my project to Tomcat 5.0 Hibernate adds an index on one of the fields.

After doing this 30 times, I get the mysql "too many indexes" error.

How do I fix this? What is going wrong?

Here is my hibernate mapping file for Account...




<hibernate-mapping>
<class name="edu.byu.cid.ssp.bank.Account" table="ACCOUNTS">
<id name="id" column="uid" type="long">
<generator class="increment"/>
</id>
<property name="open" type="boolean"/>
<property name="balance" type="double"/>
<property name="acctNumber" type="string"/>

</class>

</hibernate-mapping>


THanks!
--
Nathan