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.  [ 2 posts ] 
Author Message
 Post subject: hs-quickstart-3.1.1.GA MySQL a foreign key constraint fails
PostPosted: Thu Jan 21, 2010 9:43 pm 
Newbie

Joined: Mon Oct 13, 2008 12:01 pm
Posts: 2
I have downloaded hibernate-search-quickstart-3.1.1.GA.jar and pom and imported
into Eclipse 3.4.2. I can run the IndexAndSearchTest.java testcases successfully when
using the default HSQLDialect ... properties but it blows up when I switch to using MySQL 5.1.

It seems like an annotations/configuration issue on the Book.java class.
I can see that its a foreign key constraint problem, but I can't figure out what the correct
annotation combination is.

Book has a set of Authors
Authors doesn't contain a reference to Book

It fails on the first insertion of import.sql into the join-table (BOOK_AUTHOR) with:
    Caused by: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`quickstartx`.`book_author`, CONSTRAINT `FK_AUTHOR_ID` FOREIGN KEY (`AUTHOR_ID`) REFERENCES `author` (`id`))

Can someone tell me how to correct this?
Why does it work for HSQL and not MySQL?

Thanks!

    ...
    14:01:34,312 DEBUG SchemaExport:377 - alter table BOOK_AUTHOR drop foreign key FK_AUTHOR_ID
    14:01:34,437 DEBUG SchemaExport:377 - alter table BOOK_AUTHOR drop foreign key FK_BOOK_ID
    14:01:34,500 DEBUG SchemaExport:377 - drop table if exists Author
    14:01:34,515 DEBUG SchemaExport:377 - drop table if exists BOOK_AUTHOR
    14:01:34,515 DEBUG SchemaExport:377 - drop table if exists Book
    14:01:34,531 DEBUG SchemaExport:377 - create table Author (id integer not null auto_increment, name varchar(255), primary key (id))
    14:01:34,562 DEBUG SchemaExport:377 - create table BOOK_AUTHOR (BOOK_ID integer not null, AUTHOR_ID integer not null, primary key (BOOK_ID, AUTHOR_ID))
    14:01:34,578 DEBUG SchemaExport:377 - create table Book (id integer not null, publicationDate datetime, subtitle varchar(255), title varchar(255), primary key (id))
    14:01:34,609 DEBUG SchemaExport:377 - alter table BOOK_AUTHOR add index FK_AUTHOR_ID (AUTHOR_ID), add constraint FK_AUTHOR_ID foreign key (AUTHOR_ID) references Author (id)
    14:01:34,671 DEBUG SchemaExport:377 - alter table BOOK_AUTHOR add index FK_BOOK_ID (BOOK_ID), add constraint FK_BOOK_ID foreign key (BOOK_ID) references Book (id)
    14:01:34,734 INFO SchemaExport:310 - Executing import script: /import.sql
    14:01:34,734 DEBUG SchemaExport:327 - INSERT INTO BOOK VALUES (0, '2007-06-26', '(Theory in Practice (O''Reilly))', 'Beautiful Code: Leading Programmers Explain How They Think')
    14:01:34,765 DEBUG SchemaExport:327 - INSERT INTO AUTHOR VALUES (0, 'Greg Wilson')
    14:01:34,781 DEBUG SchemaExport:327 - INSERT INTO BOOK_AUTHOR VALUES (0, 0)
    14:01:34,796 ERROR SchemaExport:274 - schema export unsuccessful
    ...


Code:
package com.bla;

import org.hibernate.search.annotations.*;
import org.apache.solr.analysis.*;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.*;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

/**
*
*/
@Entity
@AnalyzerDef(name = "customanalyzer",
      tokenizer = @TokenizerDef(factory = StandardTokenizerFactory.class),
      filters = {
         @TokenFilterDef(factory = LowerCaseFilterFactory.class),
         @TokenFilterDef(factory = SnowballPorterFilterFactory.class,
            params = { @Parameter(name = "language", value = "English") }) })
@Indexed
public class Book {

   private Integer id;
   private String title;
   private String subtitle;
   private Set<Author> authors = new HashSet<Author>();
   private Date publicationDate;
   

/*
[b]Original[/b]

  @IndexedEmbedded
    @ManyToMany
    public Set<Author> getAuthors() {
        return authors;
    }

*/
//[b] My attempt to fix it[/b]
   @IndexedEmbedded
   @ManyToMany
   @JoinTable(
         name = "BOOK_AUTHOR",
         joinColumns = {@JoinColumn(name = "BOOK_ID")},
         inverseJoinColumns = {@JoinColumn(name = "AUTHOR_ID")}
         )
   @org.hibernate.annotations.ForeignKey(
         name = "FK_BOOK_ID",
         inverseName = "FK_AUTHOR_ID")
   public Set<Author> getAuthors() {
      return authors;
   }

// I tried this also    @ManyToMany(cascade = CascadeType.PERSIST)





   public void setAuthors(Set<Author> authors) {
      this.authors = authors;
   }

   public Book() {
   }

   @Field(index = Index.TOKENIZED, store = Store.YES)
   @Analyzer(definition = "customanalyzer")
   public String getTitle() {
      return title;
   }

   public void setTitle(String title) {
      this.title = title;
   }

   @Id
   @DocumentId
   public Integer getId() {
      return id;
   }

   public void setId(Integer id) {
      this.id = id;
   }

   @Field(index = Index.TOKENIZED, store = Store.NO)
   @Analyzer(definition = "customanalyzer")
   public String getSubtitle() {
      return subtitle;
   }

   public void setSubtitle(String subtitle) {
      this.subtitle = subtitle;
   }

   @Field(index = Index.UN_TOKENIZED, store = Store.YES)
   @DateBridge(resolution = Resolution.DAY)
   public Date getPublicationDate() {
      return publicationDate;
   }

   public void setPublicationDate(Date publicationDate) {
      this.publicationDate = publicationDate;
   }
}

Code:
package com.bla;

import org.hibernate.search.annotations.*;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

/*
*
*/
@Entity
public class Author {
    @Id
    @GeneratedValue
    private Integer id;
    private String name;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    @Field(index = Index.TOKENIZED, store = Store.YES)
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}


Top
 Profile  
 
 Post subject: Re: hs-quickstart-3.1.1.GA MySQL a foreign key constraint fails
PostPosted: Fri Jan 22, 2010 3:33 pm 
Newbie

Joined: Mon Oct 13, 2008 12:01 pm
Posts: 2
This problem is related to the

Code:
public class Author {
    @Id
@GeneratedValue
    private Integer id;


MySQL begins its id with 1 and the data supplied in import.sql uses/assumes 0.
I looked for a way to set the initial value, but I couldn't find an answer other that in some
cases Hibernate ignores initial-value.

In the end I just commented out the @GeneratedValue annotation, for purposes of the passing the
supplied tests this works fine.

Code:
public class Author {
    @Id
//@GeneratedValue
    private Integer id;



There is another problem with the supplied data, import.sql line 227
INSERT INTO BOOK VALUES (57, '', '', 'In die Wildnis');

Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'publicationDate' at row 1

My Fix:
INSERT INTO BOOK VALUES (57, '2000-04-04', '', 'In die Wildnis');


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 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:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.