@Index indexes not getting generated when table is created

tnine · **Joined:** Fri Feb 13, 2004 10:02 pm **Posts:** 90

Hi all,
I believe I've defined my indexes correctly, but they're not getting created by my integration tests. I have the session factory configuration property "hibernate.hbm2ddl.auto" set to update. My class extends "Auditable", which extends Identity. Identity and Auditable only have @Basic types for java.util.Date and java.lang.Long. The only indexes that are getting created are the primary key, and the processInformation_Id foreign key. I'm sure this is just my error, but I can't seem to find what I'm missing. Any help would be greatly appreciated.

Thanks,
Todd

Hibernate version: 3.2.6.ga
Annotation version: 3.3.0.ga
Database version: MySQL 5, MySQLInnoDBDialect

Mapping documents: See classes below

The generated SQL (show_sql=true):

Annotated Account class

Code:

/**
 * Bean used to track account information and when it has been run
 * 
 * @author Todd Nine
 * 
 */
@Entity
@Table(name="Account")
@org.hibernate.annotations.Table(appliesTo="Account",  indexes = {
      @Index(columnNames = { "accountNumber", "processInformation_Id" }, name = "accountNumberProcessInformation"),
      @Index(columnNames = { "state", "processInformation_Id" }, name = "stateProcessInformation") })
public class Account extends Auditable {

   /**
    * The account number
    */
   private String accountNumber;

   /**
    * The last status of its processing. See the above status
    */
   private AccountState state;

   /**
    * The associated process information
    */
   private ProcessInformation processInformation;

   /**
    * Default constructor
    */
   public Account() {
      super();
   }

   /**
    * @return the accountNumber
    */
   @Basic
   @Column(name="accountNumber", nullable = false)
   public String getAccountNumber() {
      return accountNumber;
   }

   /**
    * @param accountNumber
    *            the accountNumber to set
    */
   public void setAccountNumber(String accountNumber) {
      this.accountNumber = accountNumber;
   }

   /**
    * @return the status
    */
   @Embedded
   @Column(name="state", nullable = false)
   public AccountState getState() {
      return state;
   }

   /**
    * @param status
    *            the status to set
    */
   public void setState(AccountState state) {
      this.state = state;
   }

   /**
    * @return the processInformation
    */
   @ManyToOne
   @JoinColumn(nullable = false, name="processInformation_Id")
   @OptimisticLock(excluded = true)
   public ProcessInformation getProcessInformation() {
      return processInformation;
   }

   /**
    * @param processInformation
    *            the processInformation to set
    */
   public void setProcessInformation(ProcessInformation processInformation) {
      this.processInformation = processInformation;
   }

Annotated Auditable Class

Code:

@MappedSuperclass
public abstract class Auditable extends Identity {

   /**
    * The date this object was created
    * 
    * @uml.property name="createDate"
    */
   private Date createDate;

   /**
    * The update timestamp
    * 
    * @uml.property name="updateDate"
    */
   private Date updateDate;
   
   public Auditable() {
      super();
   }

   

   /**
    * Getter of the property <tt>createDate</tt>
    * 
    * @return Returns the createDate.
    * @uml.property name="createDate"
    */
   @Basic
   @Column(name="createDate",  nullable=false)
   public Date getCreateDate() {
      return createDate;
   }

   /**
    * Setter of the property <tt>createDate</tt>
    * 
    * @param createDate
    *            The createDate to set.
    * @uml.property name="createDate"
    */
   public void setCreateDate(Date createDate) {
      this.createDate = createDate;
   }

   /**
    * Getter of the property <tt>updateDate</tt>
    * 
    * @return Returns the updateDate.
    * @uml.property name="updateDate"
    */
   @Basic
   @Column(name="updateDate", nullable=false)
   public Date getUpdateDate() {
      return updateDate;
   }

   /**
    * Setter of the property <tt>updateDate</tt>
    * 
    * @param updateDate
    *            The updateDate to set.
    * @uml.property name="updateDate"
    */
   public void setUpdateDate(Date updateDate) {
      this.updateDate = updateDate;
   }

Annotated Identity Class

Code:

@MappedSuperclass
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public abstract class Identity {

   /**
    * The id to map to the identity column of the table No setter is used to
    * avoid programmer error. Only the database should set the id.
    * 
    * @uml.property name="id"
    */
   private Long id = null;
   private Long version = null;

   /**
    * Getter of the property <tt>id</tt>
    * 
    * @return Returns the id.
    * @uml.property name="id"
    */
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   // override and use direct field access to avoid creating a setter since
   // only hibernate should set the id from the id generated by the db
   @AccessType("field")
   @Column(name="id")
   public Long getId() {
      return id;
   }
   
   
   // override and use direct field access to avoid creating a setter since
   // only hibernate should set the version into the db
   @AccessType("field")
   @Version
   @Column(name="version", nullable = false)
   public Long getVersion(){
      return version;
   }

   /*
    * (non-Javadoc)
    * 
    * @see java.lang.Object#hashCode()
    */
   @Override
   public int hashCode() {
      final int PRIME = 31;
      int result = 1;
      result = PRIME * result + ((id == null) ? 0 : id.hashCode());
      return result;
   }

   /*
    * (non-Javadoc)
    * 
    * @see java.lang.Object#equals(java.lang.Object)
    */
   @Override
   public boolean equals(Object obj) {
      if (this == obj)
         return true;
      if (obj == null)
         return false;
      if (getClass() != obj.getClass())
         return false;
      final Identity other = (Identity) obj;
      if (id == null) {
         if (other.id != null)
            return false;
      } else if (!id.equals(other.id))
         return false;
      return true;
   }

Debug level Hibernate log excerpt:

Debugging output when the table is getting created

Code:

INFO: JDBC driver: MySQL-AB JDBC Driver, version: mysql-connector-java-5.0.5 ( $Date: 2007-03-01 00:01:06 +0100 (Thu, 01 Mar 2007) $, $Revision: 6329 $ )
Apr 23, 2008 4:57:04 PM org.hibernate.dialect.Dialect <init>
INFO: Using dialect: org.hibernate.dialect.MySQLInnoDBDialect
Apr 23, 2008 4:57:04 PM org.hibernate.transaction.TransactionFactoryFactory buildTransactionFactory
INFO: Using default transaction strategy (direct JDBC transactions)
Apr 23, 2008 4:57:04 PM org.hibernate.transaction.TransactionManagerLookupFactory getTransactionManagerLookup
INFO: No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic flush during beforeCompletion(): disabled
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic session close at end of transaction: disabled
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC batch size: 15
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC batch updates for versioned data: disabled
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Scrollable result sets: enabled
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC3 getGeneratedKeys(): enabled
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Connection release mode: auto
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Maximum outer join fetch depth: 2
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default batch fetch size: 1
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Generate SQL with comments: disabled
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Order SQL updates by primary key: disabled
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Order SQL inserts for batching: disabled
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory createQueryTranslatorFactory
INFO: Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
Apr 23, 2008 4:57:04 PM org.hibernate.hql.ast.ASTQueryTranslatorFactory <init>
INFO: Using ASTQueryTranslatorFactory
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query language substitutions: {}
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JPA-QL strict compliance: disabled
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Second-level cache: enabled
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query cache: disabled
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory createCacheProvider
INFO: Cache provider: org.hibernate.cache.NoCacheProvider
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Optimize cache for minimal puts: disabled
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Structured second-level cache entries: disabled
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Echoing all SQL to stdout
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Statistics: disabled
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Deleted entity synthetic identifier rollback: disabled
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default entity-mode: pojo
Apr 23, 2008 4:57:04 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Named query checking : enabled
Apr 23, 2008 4:57:04 PM org.hibernate.search.Version <clinit>
INFO: Hibernate Search 3.0.0.CR1
Apr 23, 2008 4:57:04 PM org.hibernate.impl.SessionFactoryImpl <init>
INFO: building session factory
Apr 23, 2008 4:57:05 PM org.hibernate.impl.SessionFactoryObjectFactory addInstance
INFO: Not binding factory to JNDI, no JNDI name configured
Apr 23, 2008 4:57:05 PM org.hibernate.tool.hbm2ddl.SchemaUpdate execute
INFO: Running hbm2ddl schema update
Apr 23, 2008 4:57:05 PM org.hibernate.tool.hbm2ddl.SchemaUpdate execute
INFO: fetching database metadata
Apr 23, 2008 4:57:05 PM org.hibernate.tool.hbm2ddl.SchemaUpdate execute
INFO: updating schema
Apr 23, 2008 4:57:05 PM org.hibernate.tool.hbm2ddl.DatabaseMetadata getTableMetadata
INFO: table not found: Account
Apr 23, 2008 4:57:05 PM org.hibernate.tool.hbm2ddl.DatabaseMetadata getTableMetadata
INFO: table not found: CreditUnion
Apr 23, 2008 4:57:05 PM org.hibernate.tool.hbm2ddl.DatabaseMetadata getTableMetadata
INFO: table not found: ProcessInformation
Apr 23, 2008 4:57:05 PM org.hibernate.tool.hbm2ddl.DatabaseMetadata getTableMetadata
INFO: table not found: RateInformation
Apr 23, 2008 4:57:05 PM org.hibernate.tool.hbm2ddl.DatabaseMetadata getTableMetadata
INFO: table not found: Account
Apr 23, 2008 4:57:05 PM org.hibernate.tool.hbm2ddl.DatabaseMetadata getTableMetadata
INFO: table not found: CreditUnion
Apr 23, 2008 4:57:05 PM org.hibernate.tool.hbm2ddl.DatabaseMetadata getTableMetadata
INFO: table not found: ProcessInformation
Apr 23, 2008 4:57:05 PM org.hibernate.tool.hbm2ddl.DatabaseMetadata getTableMetadata
INFO: table not found: RateInformation
Apr 23, 2008 4:57:05 PM org.hibernate.tool.hbm2ddl.SchemaUpdate execute

sanne.grinovero · **Posted:** Wed Apr 23, 2008 2:47 am

Hi,
I'm not expert but had solved this same problem before in either of two ways (sorry, I don't remember which one):

1)

Quote:

@Index(columnNames = { "accountNumber", "processInformation_Id" }, name = "accountNumberProcessInformation"),

Maybe the columnname should not refer to the physical mapping, but name the entity property: so "accountNumber" is ok but you could try removing the "_Id" from "processInformation_Id".

2)The problem could be the "hibernate.hbm2ddl.auto"; it appears indexes are only created at schema creation. Try using "create".

As I said, I may be wrong, but hope to give you some ideas.

tnine · **Joined:** Fri Feb 13, 2004 10:02 pm **Posts:** 90

Thanks for the reply, you second suggestion helped. I receive an error if my column names in my @Index annotation don't match the column name I've assigned in the @Column annotation. As far as the schema creation, all my tables are dropped before my integration tests start. So there is only an empty database with no tables when my session factory is started and the tables are created. However, you are correct. When I have "update" in the property it doesn't work, even if there are no tables. When I have "create" it does work. Why is this? I would assume that if indexes don't exist they would be created during an update operation, regardless of the existence of tables.

sanne.grinovero · **Posted:** Wed Apr 23, 2008 6:16 am

Nice you solved.

"Update" can't do miracles, AFAIK it only adds new columns; I suppose it does because of safety.. imagine it automatically dropping columns or entire tables; it could be dangerous.. so it's useful for fast developing, but you should create-drop when you want to verify the generated schema.