-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 13 posts ] 
Author Message
 Post subject: SchemaUpdate does not create column indexes (MySql)
PostPosted: Tue Mar 08, 2005 2:43 pm 
Newbie

Joined: Tue Mar 08, 2005 1:53 pm
Posts: 2
Hibernate version: 2.1.8
MySQL version: 4.0.20
MySQL driver: mysql-connector-java-3.0.10-stable (also verified with 3.0.16 and 3.1.7)

SchemaUpdate does not create column indexes specified using the <column> element in mapping file. There is no error message, and SchemaUpdate exits successfully.

The problem can be demonstrated on the command line, using the files supplied as part of the Hibernate 2.1.8 distribution.

To do this:

1) modify the ./test/org/hibernate/test/Simple.hbm.xml from:
Code:
<property name="name">

to:
Code:
<property name="name">
            <column name="name" index="test_idx"/>
</property>


2) Modify etc/hibernate.properties to point at the MySQL instance.

3) Invoke SchemaUpdate on the command line:
Code:
java -cp <hibernate jar+jars from /lib+mysqlconnector jar+etc directory> net.sf.hibernate.tool.hbm2ddl.SchemaUpdate ./test/org/hibernate/test/Simple.hbm.xml


The table (simple) is created, but the index test_idx is not created. If the test is repeated using SchemaExport, the index is created correctly.

I believe this is a bug in SchemaUpdate.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Mar 09, 2005 4:37 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
probabably a bug yes. put it in jira with a testcase.

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject:
PostPosted: Wed Mar 09, 2005 12:33 pm 
Newbie

Joined: Tue Mar 08, 2005 1:53 pm
Posts: 2
Now subject of bug HB-1458. http://opensource.atlassian.com/projects/hibernate/browse/HB-1458


Top
 Profile  
 
 Post subject:
PostPosted: Thu May 12, 2005 4:58 am 
Newbie

Joined: Tue Apr 26, 2005 9:30 am
Posts: 3
Hmmm Strange...
I have exactly the same behaviour with Hibernate 3.03...

Using:
<property name="ReferenceId" type="java.lang.String">
<column
name="ReferenceID"
index="idx_referenceId"
/>
will also not create an index in my for this property. Neither the ID is indexed:
<id name="PersistenceID">
<generator class="native"/>
</id>
which should be automatically indexed afaik.

this happens with Postgres 8 (using postgresql-8.0-310.jdbc2.jar)

cu
Christian Wilhelm


Top
 Profile  
 
 Post subject:
PostPosted: Thu May 12, 2005 7:46 am 
Newbie

Joined: Tue Apr 26, 2005 9:30 am
Posts: 3
also the Index for property "ReferenceId" is not created by using Oracle 10g (ojdbc14.jar) but there the ID will be indexed.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jun 20, 2005 10:29 am 
Newbie

Joined: Mon Jun 20, 2005 10:21 am
Posts: 1
This could be related to the following code in net.sf.hibernate.cfg.Configuration.generateSchemaUpdateScript():
(Configuration.java,v 1.42 2005/01/29 03:03:18 oneovthafew)
... maybe.

This method is called from hbm2ddl to generate SQLs, but part for index SQLs generation and is commented out.


Code:
         /*//broken, 'cos we don't generate these with names in SchemaExport
         subIter = table.getIndexIterator();
         while ( subIter.hasNext() ) {
            Index index = (Index) subIter.next();
            if ( !index.isForeignKey() || !dialect.hasImplicitIndexForForeignKey() ) {
               if ( tableInfo==null || tableInfo.getIndexMetadata( index.getName() ) == null ) {
                  script.add( index.sqlCreateString(dialect, mapping) );
               }
            }
         }
         //broken, 'cos we don't generate these with names in SchemaExport
         subIter = table.getUniqueKeyIterator();
         while ( subIter.hasNext() ) {
            UniqueKey uk = (UniqueKey) subIter.next();
            if ( tableInfo==null || tableInfo.getIndexMetadata( uk.getName() ) == null ) {
               script.add( uk.sqlCreateString(dialect, mapping) );
            }
         }*/



Top
 Profile  
 
 Post subject:
PostPosted: Thu Oct 20, 2005 1:32 pm 
Beginner
Beginner

Joined: Wed Jul 21, 2004 6:50 pm
Posts: 27
Is there any reason why using the SchemaExport code to generate indexes would not work here?

Thus it would become:

Code:
   /**
    * Generate DDL for altering tables
    *
    * @see org.hibernate.tool.hbm2ddl.SchemaUpdate
    */
   public String[] generateSchemaUpdateScript(Dialect dialect, DatabaseMetadata databaseMetadata) throws HibernateException {
      secondPassCompile();

      String defaultCatalog = properties.getProperty(Environment.DEFAULT_CATALOG);
      String defaultSchema = properties.getProperty(Environment.DEFAULT_SCHEMA);

      ArrayList script = new ArrayList( 50 );

      Iterator iter = getTableMappings();
      while ( iter.hasNext() ) {
         Table table = ( Table ) iter.next();
         if ( table.isPhysicalTable() ) {

            TableMetadata tableInfo = databaseMetadata.getTableMetadata( table.getName(),
                  table.getSchema(),
                  table.getCatalog() );
            if ( tableInfo == null ) {
               script.add( table.sqlCreateString(
                     dialect,
                     mapping,
                     defaultCatalog,
                     defaultSchema ) );
            }
            else {
               Iterator subiter = table.sqlAlterStrings(
                     dialect,
                     mapping,
                     tableInfo,
                     defaultCatalog,
                     defaultSchema );
               while ( subiter.hasNext() ) script.add( subiter.next() );
            }

            Iterator comments = table.sqlCommentStrings(dialect, defaultCatalog, defaultSchema);
            while ( comments.hasNext() ) {
               script.add( comments.next() );
            }
            
         }
      }

      iter = getTableMappings();
      while ( iter.hasNext() ) {
         Table table = ( Table ) iter.next();
         if ( table.isPhysicalTable() ) {

            TableMetadata tableInfo = databaseMetadata.getTableMetadata( table.getName(),
                  table.getSchema(),
                  table.getCatalog() );

              //generate indexes
              Iterator subIter = table.getIndexIterator();
               while ( subIter.hasNext() ) {
                  Index index = ( Index ) subIter.next();
                  script.add( index.sqlCreateString( dialect,
                        mapping,
                        defaultCatalog,
                        defaultSchema ) );
               }

            if ( dialect.hasAlterTable() ) {
               subIter = table.getForeignKeyIterator();
               while ( subIter.hasNext() ) {
                  ForeignKey fk = ( ForeignKey ) subIter.next();
                  if ( fk.isPhysicalConstraint() ) {
                     boolean create = tableInfo == null || (
                           tableInfo.getForeignKeyMetadata( fk.getName() ) == null && (
                           //Icky workaround for MySQL bug:
                           !( dialect instanceof MySQLDialect ) ||
                           tableInfo.getIndexMetadata( fk.getName() ) == null
                           )
                           );
                     if ( create ) script.add( fk.sqlCreateString(
                           dialect,
                           mapping,
                           defaultCatalog,
                           defaultSchema ) );
                  }
               }
            }

         }

      }

      iter = iterateGenerators( dialect );
      while ( iter.hasNext() ) {
         PersistentIdentifierGenerator generator = ( PersistentIdentifierGenerator ) iter.next();
         Object key = generator.generatorKey();
         if ( !databaseMetadata.isSequence( key ) && !databaseMetadata.isTable( key ) ) {
            String[] lines = generator.sqlCreateStrings( dialect );
            for ( int i = 0; i < lines.length; i++ ) script.add( lines[i] );
         }
      }

      return ArrayHelper.toStringArray( script );
   }


cheers,

David


Top
 Profile  
 
 Post subject:
PostPosted: Thu Oct 20, 2005 1:37 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
much easier if you posted patches so i could see what you have actually changed/added. ;)

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 08, 2006 6:20 pm 
Newbie

Joined: Mon Dec 05, 2005 1:03 pm
Posts: 10
Hi all,
I've written a patch for this based on hay7777's post.

Well, I actually wrote a subclass of Configuration and inserted the code snippet below into the 2nd while loop of the overridden generateSchemaUpdateScript() method.

Code:
// Hack that generates indexes that are omitted when using
// hibernate.hbm2ddl.auto=update. See commented-out code below.
Iterator idxIter = table.getIndexIterator();
while (idxIter.hasNext()) {
   Index index = (Index) idxIter.next();
   
   // Skip if index already exists
   if (tableInfo != null) {
      IndexMetadata meta = tableInfo.getIndexMetadata(index.getName());
      if (meta != null) {
         continue;
      }
   }
   
   script.add(index.sqlCreateString(dialect,
                                    getMapping(),
                                    defaultCatalog,
                                    defaultSchema));
}


Below is the entire source code of my DBConfiguration class. It's a bit kludgey, I know.

Code:
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.TreeMap;

import org.hibernate.HibernateException;
import org.hibernate.MappingException;
import org.hibernate.cfg.Configuration;
import org.hibernate.cfg.Environment;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.MySQLDialect;
import org.hibernate.engine.Mapping;
import org.hibernate.id.IdentifierGenerator;
import org.hibernate.id.PersistentIdentifierGenerator;
import org.hibernate.mapping.Collection;
import org.hibernate.mapping.ForeignKey;
import org.hibernate.mapping.IdentifierCollection;
import org.hibernate.mapping.Index;
import org.hibernate.mapping.PersistentClass;
import org.hibernate.mapping.RootClass;
import org.hibernate.mapping.Table;
import org.hibernate.tool.hbm2ddl.DatabaseMetadata;
import org.hibernate.tool.hbm2ddl.IndexMetadata;
import org.hibernate.tool.hbm2ddl.TableMetadata;
import org.hibernate.util.ArrayHelper;

public class DBConfiguration extends Configuration {
   
   /**
    * Generate DDL for altering tables
    *
    * @see org.hibernate.tool.hbm2ddl.SchemaUpdate
    */
   public String[] generateSchemaUpdateScript(Dialect dialect, DatabaseMetadata databaseMetadata)
         throws HibernateException {
     
      secondPassCompile();

      String defaultCatalog = getProperties().getProperty(Environment.DEFAULT_CATALOG);
      String defaultSchema = getProperties().getProperty(Environment.DEFAULT_SCHEMA);

      ArrayList script = new ArrayList( 50 );

      Iterator iter = getTableMappings();
      while ( iter.hasNext() ) {
         Table table = ( Table ) iter.next();
         if ( table.isPhysicalTable() ) {

            TableMetadata tableInfo = databaseMetadata.getTableMetadata( table.getName(),
                  table.getSchema(),
                  table.getCatalog() );
            if ( tableInfo == null ) {
               script.add( table.sqlCreateString(
                     dialect,
                     getMapping(),
                     defaultCatalog,
                     defaultSchema ) );
            }
            else {
               Iterator subiter = table.sqlAlterStrings(
                     dialect,
                     getMapping(),
                     tableInfo,
                     defaultCatalog,
                     defaultSchema );
               while ( subiter.hasNext() ) script.add( subiter.next() );
            }

            Iterator comments = table.sqlCommentStrings(dialect, defaultCatalog, defaultSchema);
            while ( comments.hasNext() ) {
               script.add( comments.next() );
            }
           
         }
      }

      iter = getTableMappings();
      while ( iter.hasNext() ) {
         Table table = ( Table ) iter.next();
         if ( table.isPhysicalTable() ) {

            TableMetadata tableInfo = databaseMetadata.getTableMetadata( table.getName(),
                  table.getSchema(),
                  table.getCatalog() );

            // Hack that generates indexes that are omitted when using
            // hibernate.hbm2ddl.auto=update. See commented-out code below.
            Iterator idxIter = table.getIndexIterator();
            while (idxIter.hasNext()) {
               Index index = (Index) idxIter.next();
               
               // Skip if index already exists
               if (tableInfo != null) {
                  IndexMetadata meta = tableInfo.getIndexMetadata(index.getName());
                  if (meta != null) {
                     continue;
                  }
               }
               
               script.add(index.sqlCreateString(dialect,
                                                getMapping(),
                                                defaultCatalog,
                                                defaultSchema));
            }

            if ( dialect.hasAlterTable() ) {
               Iterator subIter = table.getForeignKeyIterator();
               while ( subIter.hasNext() ) {
                  ForeignKey fk = ( ForeignKey ) subIter.next();
                  if ( fk.isPhysicalConstraint() ) {
                     boolean create = tableInfo == null || (
                           tableInfo.getForeignKeyMetadata( fk.getName() ) == null && (
                           //Icky workaround for MySQL bug:
                           !( dialect instanceof MySQLDialect ) ||
                           tableInfo.getIndexMetadata( fk.getName() ) == null
                           )
                           );
                     if ( create ) script.add( fk.sqlCreateString(
                           dialect,
                           getMapping(),
                           defaultCatalog,
                           defaultSchema ) );
                  }
               }
            }

         }

            /*//broken, 'cos we don't generate these with names in SchemaExport
            subIter = table.getIndexIterator();
            while ( subIter.hasNext() ) {
                Index index = (Index) subIter.next();
                if ( !index.isForeignKey() || !dialect.hasImplicitIndexForForeignKey() ) {
                    if ( tableInfo==null || tableInfo.getIndexMetadata( index.getFilterName() ) == null ) {
                        script.add( index.sqlCreateString(dialect, mapping) );
                    }
                }
            }
            //broken, 'cos we don't generate these with names in SchemaExport
            subIter = table.getUniqueKeyIterator();
            while ( subIter.hasNext() ) {
                UniqueKey uk = (UniqueKey) subIter.next();
                if ( tableInfo==null || tableInfo.getIndexMetadata( uk.getFilterName() ) == null ) {
                    script.add( uk.sqlCreateString(dialect, mapping) );
                }
            }*/
      }

      iter = iterateGenerators( dialect );
      while ( iter.hasNext() ) {
         PersistentIdentifierGenerator generator = ( PersistentIdentifierGenerator ) iter.next();
         Object key = generator.generatorKey();
         if ( !databaseMetadata.isSequence( key ) && !databaseMetadata.isTable( key ) ) {
            String[] lines = generator.sqlCreateStrings( dialect );
            for ( int i = 0; i < lines.length; i++ ) script.add( lines[i] );
         }
      }
     
      return ArrayHelper.toStringArray( script );
   }
   
    private Iterator iterateGenerators(Dialect dialect) throws MappingException {

        TreeMap generators = new TreeMap();
        String defaultCatalog = getProperties().getProperty(Environment.DEFAULT_CATALOG);
        String defaultSchema = getProperties().getProperty(Environment.DEFAULT_SCHEMA);

        Iterator iter = classes.values().iterator();
        while ( iter.hasNext() ) {
            PersistentClass pc = ( PersistentClass ) iter.next();

            if ( !pc.isInherited() ) {

                IdentifierGenerator ig = pc.getIdentifier()
                    .createIdentifierGenerator(
                            dialect,
                            defaultCatalog,
                            defaultSchema,
                            (RootClass) pc
                    );

                if ( ig instanceof PersistentIdentifierGenerator ) {
                    generators.put( ( ( PersistentIdentifierGenerator ) ig ).generatorKey(), ig );
                }

            }
        }

        iter = collections.values().iterator();
        while ( iter.hasNext() ) {
            Collection collection = ( Collection ) iter.next();

            if ( collection.isIdentified() ) {

                IdentifierGenerator ig = ( ( IdentifierCollection ) collection ).getIdentifier()
                        .createIdentifierGenerator(
                                dialect,
                                defaultCatalog,
                                defaultSchema,
                                null
                        );

                if ( ig instanceof PersistentIdentifierGenerator ) {
                    generators.put( ( ( PersistentIdentifierGenerator ) ig ).generatorKey(), ig );
                }

            }
        }

        return generators.values().iterator();
    }
   
    protected Mapping getMapping() throws HibernateException {
      Mapping m = null;
      try {
         Field f = Configuration.class.getDeclaredField("mapping");
         f.setAccessible(true);
         m = (Mapping) f.get(this);
      } catch (Exception x) {
         throw new HibernateException("failed to reflect mapping field", x);
      }

      return m;
   }
}


I've tested it on MySQL 4.1.7 (InnoDBDialect) and haven't encountered any issue yet.

Let me know if it works for you guys.

Luke


Top
 Profile  
 
 Post subject:
PostPosted: Thu Feb 09, 2006 4:14 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 6:10 am
Posts: 8615
Location: Neuchatel, Switzerland (Danish)
that is not a patch ...

_________________
Max
Don't forget to rate


Top
 Profile  
 
 Post subject:
PostPosted: Tue Feb 14, 2006 2:17 pm 
Newbie

Joined: Thu Jan 19, 2006 1:08 pm
Posts: 3
Hi Luke!

I'm working on a proper patch so your changes can be accepted by Max.

However, I have a doubt:

You have changed some calls that use the variable "mapping" to a call to the method getMapping(), that you have created, even in the calls that are outsize your "hack".

Is this correct?

The patch *without* the other mapping -> getMapping changes is:


Code:
--- Configuration.java   Tue Feb 14 17:59:04 2006
+++ ConfigurationNew.java   Tue Feb 14 18:08:38 2006
@@ -816,6 +816,20 @@
      return ArrayHelper.toStringArray( script );
   }

+   protected Mapping getMapping() throws HibernateException {
+        Mapping m = null;
+        try {
+           Field f = Configuration.class.getDeclaredField("mapping");
+           f.setAccessible(true);
+           m = (Mapping) f.get(this);
+        } catch (Exception x) {
+           throw new HibernateException("failed to reflect mapping field", x);
+        }
+
+        return m;
+     }
+
+   
   /**
    * Generate DDL for altering tables
    *
@@ -880,6 +894,26 @@
                  table.getCatalog()
               );

+            // Hack that generates indexes that are omitted when using
+            // hibernate.hbm2ddl.auto=update. See commented-out code below.
+            Iterator idxIter = table.getIndexIterator();
+            while (idxIter.hasNext()) {
+               Index index = (Index) idxIter.next();
+               
+               // Skip if index already exists
+               if (tableInfo != null) {
+                  IndexMetadata meta = tableInfo.getIndexMetadata(index.getName());
+                  if (meta != null) {
+                     continue;
+                  }
+               }
+               
+               script.add(index.sqlCreateString(dialect,
+                                                getMapping(),
+                                                defaultCatalog,
+                                                defaultSchema));
+            }
+            
            if ( dialect.hasAlterTable() ) {
               Iterator subIter = table.getForeignKeyIterator();
               while ( subIter.hasNext() ) {



Thanks!



Top
 Profile  
 
 Post subject:
PostPosted: Tue Feb 14, 2006 3:20 pm 
Newbie

Joined: Mon Dec 05, 2005 1:03 pm
Posts: 10
Quote:
You have changed some calls that use the variable "mapping" to a call to the method getMapping(), that you have created, even in the calls that are outsize your "hack".

Is this correct?


Hi Ze,

I had to change to getMapping() because I was creating a subclass of Configuration, in which the mapping attribute is private. If I changed the Configuration class directly, that wouldn't have been necessary.

I'm sorry I didn't create a patch. I'm very new to Hibernate and even newer to these forumns, therefore I haven't had time to find out. I just wanted to share the code with those that may need it.

Luke


Top
 Profile  
 
 Post subject:
PostPosted: Tue Feb 14, 2006 6:10 pm 
Newbie

Joined: Thu Jan 19, 2006 1:08 pm
Posts: 3
Hi Luke!

Ok! I've removed the getMapping() call, and made the final patch.

This patch works directly when applied to Configuration.java from CVS.

I've tested it and it works fine.

Max: Could you please take a look at the patch? Thanks!

Code:
Index: Configuration.java
===================================================================
RCS file: /cvsroot/hibernate/Hibernate3/src/org/hibernate/cfg/Configuration.java,v
retrieving revision 1.116
diff -u -r1.116 Configuration.java
--- Configuration.java   27 Jan 2006 16:46:34 -0000   1.116
+++ Configuration.java   14 Feb 2006 22:01:30 -0000
@@ -79,6 +79,7 @@
import org.hibernate.mapping.UniqueKey;
import org.hibernate.secure.JACCConfiguration;
import org.hibernate.tool.hbm2ddl.DatabaseMetadata;
+import org.hibernate.tool.hbm2ddl.IndexMetadata;
import org.hibernate.tool.hbm2ddl.TableMetadata;
import org.hibernate.type.SerializationException;
import org.hibernate.type.Type;
@@ -880,6 +881,26 @@
                  table.getCatalog()
               );

+            // Hack that generates indexes that are omitted when using
+            // hibernate.hbm2ddl.auto=update. See commented-out code below.
+            Iterator idxIter = table.getIndexIterator();
+            while (idxIter.hasNext()) {
+               Index index = (Index) idxIter.next();
+               
+               // Skip if index already exists
+               if (tableInfo != null) {
+                  IndexMetadata meta = tableInfo.getIndexMetadata(index.getName());
+                  if (meta != null) {
+                     continue;
+                  }
+               }
+               
+               script.add(index.sqlCreateString(dialect,
+                                                mapping,
+                                                defaultCatalog,
+                                                defaultSchema));
+            }
+            
            if ( dialect.hasAlterTable() ) {
               Iterator subIter = table.getForeignKeyIterator();
               while ( subIter.hasNext() ) {



Thanks!



lukeda wrote:
Quote:
You have changed some calls that use the variable "mapping" to a call to the method getMapping(), that you have created, even in the calls that are outsize your "hack".

Is this correct?


Hi Ze,

I had to change to getMapping() because I was creating a subclass of Configuration, in which the mapping attribute is private. If I changed the Configuration class directly, that wouldn't have been necessary.

I'm sorry I didn't create a patch. I'm very new to Hibernate and even newer to these forumns, therefore I haven't had time to find out. I just wanted to share the code with those that may need it.

Luke
[/code]


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 13 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
cron
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.