SchemaUpdate does not create column indexes (MySql)

k-wall · **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:

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.

max · **Posted:** Wed Mar 09, 2005 4:37 am

probabably a bug yes. put it in jira with a testcase.

k-wall · **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

Christian Wilhelm · **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

Christian Wilhelm · **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.

dmazar · **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) );
            }
         }*/

hay7777 · **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

max · **Posted:** Thu Oct 20, 2005 1:37 pm

much easier if you posted patches so i could see what you have actually changed/added. ;)

lukeda · **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

max · **Posted:** Thu Feb 09, 2006 4:14 am

that is not a patch ...

jfreire · **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!

Zé

lukeda · **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

jfreire · **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!

Zé

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]