Schemaupdate with MySQL Connector/J JDBC driver doesn't seem to work, it is creating existing tables all over again.
Is this problem related to the unimplemented calls in the MySQL Connector/J JDBC driver?

Has anybody got this working?

Gavin: Can you include the --text option with SchemaUpdate as in SchemaExport. I would like to be able to revise any changes SchemaUpdate generates.

Bas

Hi,

I have the same problem ... if you find out the problem, please let me know !

sylvain_2020

SchemaUpdate is, unfortunately, broken and generally doesn't work. I've tried it with Sybase and MySQL without success.

SchemaExport, however, seems to be fairly robust.

Therefore, ther is no way a update a table by adding a column to an existing database schema ?

I'm developing with Eclipse 2.1, MySQL 4.0.15, Hibernate v2.0.3 and SchemaUpdate and it's updatiing without any trouble. One thing I noticed is that removing fields from the *.hbm.xml files and then running SchemaUpdate will not remove the old fields, but adding them or modifying them works fine for me. What version Hibernate or MySQL connector are you using? I'm on the 3.0.8 connector, I think.

regards,
David

I use,

MySQL 3.32.x
Connector 3.0.9
Hibernate 2.0.3

Do you think the difference in MySQL version is the problem?

Bas

If you pass along your hibernate.cfg.xml/hibernate.properties and one of the *.hbm.xml files giving the problem, I'll try them out on my system and see what happens. Don't forget to change your database server name, MySQL username and MySQL password. :)

Also, have you turned on logging for MySQL and confirmed that your SchemaUpdate is actually connecting to MySQL?

Is this all done inside Eclipse or externally with ant? If so, how do you call it?

Regards,
David

Here you go,

I didn't look at the myqsl logs, but "Table 'ProjObject' already exists"
says it all :)

Bas

-ant target-



-hibernate mapping-



-hibernate.properties-



-schemaupdate result-



-</SNIP>-

1. I upgrade to the same mysql connector you listed (3.0.9 - I was previously on 3.0.8)

2. upgrade to Hibernate 2.0.3 (I seem to have been on 2.0.2)

3. I tried your supplied hbm.xmil

4. I'm using somewhat similar MySQL (my version is 4.0.15, not your 3.XX.XX) with a slightly different connection url. You listed:

jdbc:mysql://127.0.0.1/Proj2?user=X&password=X

while I'm using:

jdbc:mysql://localhost:3306/test?user=X&password=X

I compiled this from within Eclipse 2.1 as well as tried it externally with Ant v.1.5.4. My ant output is:

D:\Code\h\WEB-INF\src>ant SchemaUpdate
Buildfile: build.xml

SchemaUpdate:
[schemaupdate] [INFO] Environment - -Hibernate 2.0.3
[schemaupdate] [INFO] Environment - -hibernate.properties not found
[schemaupdate] [INFO] Environment - -using CGLIB reflection optimizer
[schemaupdate] [INFO] Environment - -JVM proxy support: true
[schemaupdate] [INFO] Configuration - -Mapping file: D:\Code\h\WEB-INF\src\hibernate\ProjObject.hbm.xml
[schemaupdate] [INFO] Binder - -Mapping class: ProjObject -> ProjObject
[schemaupdate] [INFO] Dialect - -Using dialect: net.sf.hibernate.dialect.MySQLDialect
[schemaupdate] [INFO] DriverManagerConnectionProvider - -Hibernate connection pool size: 20
[schemaupdate] [INFO] DriverManagerConnectionProvider - -using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://localhost:3306/test?user=X&password=X
[schemaupdate] [INFO] DriverManagerConnectionProvider - -connection properties: {user=X, password=X}
[schemaupdate] [INFO] Configuration - -processing one-to-many association mappings
[schemaupdate] [INFO] Configuration - -processing foreign key constraints

BUILD SUCCESSFUL
Total time: 2 seconds

Have you tried deleting the table and trying to run your ant project again so SchemaUpdate creates the missing table? Will it work after that? I'm wondering if the problem is related to your mysql table definition. Can you do a describe on the table? Mine shows:

mysql> describe projobject;
+----------+----------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------------------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| created | datetime| | | 0000-00-00 00:00:00 | |
+----------+----------+------+-----+---------------------+----------------+

I even tried it as the default type MyISAM and recreating the table type as ISAM and modifying the column name to 'created2'. No trouble that way either. So, that narrows it down to either the MySQL version you're using (seems doubtful) OR the hibernate tools you're using. Mine were from hibernate-extensions-2.0. Are you using hibernate-extensions-2.0.2? I'm downloading that now to try it out so the only different variable I know of, besides my build.xml file being different than yours, is MySQL.

Regards,
David

I just tried it against mysql-max-4.1.0, didn't work either,



schemaupdate is in hibernate2.jar not in the extentions package,
I use hibernate 2.0.3, can you post your ant schemaupdate task
and hibernate.properties file,

When the database is empty, schemaupdate nicely creates the
tables, then when I run it again it does not "see" the allready
created tables,

I also tried your (not so different) connection URL :)
(you specified a (default) port number and used localhost in stead of 127.0.0.1)

thanks,

Bas

I've been using MySQL (4.0.15) and SchemaUpdate (from v2.0.2 and as of yesterday v2.0.3) with sucess for over a month (since I discovered what a timesaver it is).

I can call them from within Eclipse v2.1 as well as using ant v1.5.4 at a DOS prompt (programming on Tomcat from Within Eclipse using the SysDeo plugin on XP Home). FYI, I've used the MySQL connector 3.0.8 stable and, as of yesterday, 3.0.9 stable without any problems creating/updating tables automatically.

The only thing that annoys me about SchemaUpdate is if I change a column name, when it makes the new column, it doesn't know to remove the old column. But that's asking for too much intelligence.

The only non-standard thing I'm using is new versions of 4 commons libraries because I added the displaytag library this week. But, they were working fine with the versions that came with v2.0.2 for the month plus before that:

commons-beanutils-1.6.1.jar
commons-collections-2.1.jar
commons-lang-2.0.jar
commons-logging-1.0.3.jar

Since I've tried almost everyone's examples, here are the hbm2java SchemaUpdate code snippets from my build.xml file (hibernate.properties and hibernate.cfg.xml follows after):

<!-- Hibernate build -->
<target name="hbm2java"
description="Hibernate - build java classes from *.hbm.xml files">
<java classname="net.sf.hibernate.tool.hbm2java.CodeGenerator" fork="true">
<arg line="src/hibernate/ProjObject.hbm.xml src/hibernate/Misc.hbm.xml src/hibernate/Misc2.hbm.xml --output=src/" />
<classpath refid="compile.classpath"/>
</java>
</target>

<target name="SchemaUpdate">
<taskdef name="schemaupdate"
classname="net.sf.hibernate.tool.hbm2ddl.SchemaUpdateTask"
classpathref="compile.classpath"/>

<schemaupdate
properties="src/hibernate.properties"
quiet="no">
<fileset dir="src">
<include name="**/*.hbm.xml"/>
</fileset>
</schemaupdate>
</target>

My hibernate.cfg.xml file is:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">
<hibernate-configuration>
<!-- builds work fine without name="", I added that last night
because I'm using ctx.lookup() in a facade to hide having
to pass the SessionFactory -->
<session-factory name="dgf:/hibernate/SessionFactory">
<property name="dialect">net.sf.hibernate.dialect.MySQLDialect</property>
<property name="connection.username">X</property>
<property name="connection.password">Y</property>
<property name="connection.url">jdbc:mysql://localhost:3306/test?autoReconnect=true</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="show_sql">false</property>
<property name="use_outer_join">true</property>
<property name="transaction.factory_class">net.sf.hibernate.transaction.JDBCTransactionFactory</property>
<property name="dbcp.minIdle">1</property>
<!-- mapping files -->
<mapping resource="hibernate/Misc.hbm.xml"/>
<mapping resource="hibernate/Misc2.hbm.xml"/>
<mapping resource="hibernate/ProjObject.hbm.xml"/>
</session-factory>
</hibernate-configuration>

And finally, my hibernate.properties file is below. You don't need to add the "?user=X&password=&", I did that to match the configuration you listed in your earlier follow-up posts. It works fine with or without it.

hibernate.dialect=net.sf.hibernate.dialect.MySQLDialect
hibernate.connection.username=X
hibernate.connection.password=Y
hibernate.connection.url=jdbc:mysql://localhost:3306/test?user=X&password=Y
hibernate.connection.driver_class=com.mysql.jdbc.Driver

If it matters, I'm using hibernate-tools-2.0 not the newer one. I can't think of anything else you might need to know, except that this is a struts application so my WEB-INF/lib is filled with struts libraries (shouldn't make a difference, but if you're superstitious... LOL).

I'd joke and say you used an unstable Alpha version of MySQL (the 4.1.0 is a development branch right now) but you seem to have received the same error messages as you did when you used the 3.XX.XX version.

Regards,
David

Thanks for the insight, I can't think of any changes I should make to make it work on my side.

BTW: If I don't include a user and pass in MySQL URL of the properties file which schemaupdate uses, it can't connect

I'm on Redhat9, maybe that is the reason?

Maybe I'll dive into the SchemaUpdate source...

Hello all,

I also had the problem that my tables are dropped and re-created at each run.
The solution is to set "hibernate.hbm2ddl.auto=update" in hibernate.properties (or cfg).
I discovered that not setting hibernate.hbm2ddl.auto (as in the default hibernate.properties file) and "setting hibernate.hbm2ddl.auto=create" both do the same as setting "hibernate.hbm2ddl.auto=create-drop".

Hope this helps,

Ivo

This is not true. No setting means: don't do anything.

Instead of embedding the ?username=X&password=Y in the JDBC Url as you stated below, you can make them connection.username and connection.password in your config files (hibernate.properties or hibernate.cfg.xml). Taking them out of the JDBC url still works fine for me (in Ant 1.5.4 externally and Eclipse's Ant) by having it do the alter table when I change the column="newcolumn" in ProjOjbect.hbm.xml.

You can use (hibernate.properties)
hibernate.connection.username=X
hibernate.connection.password=Y
OR (hibernate.cfg.xml)
<property name="connection.username">X</property>
<property name="connection.password">Y</property>

Regards,
David