Hi all,

We've recently switched over to Hibernate (latest) and MySQL 5.1. We have an unidirectional ManyToMany relationship between two objects Student and Module. A Student has a Set of Modules, but the modules have no knowledge of the Students. There are many Students and many Modules.

Our classes are set up as follows;

@Entity
public class Student extends User implements java.io.Serializable {

@ManyToMany(cascade = {CascadeType.MERGE, CascadeType.PERSIST},fetch = FetchType.EAGER)
@JoinTable(
name="Student_EcafModule",
joinColumns = @JoinColumn( name="studentId"),
inverseJoinColumns = @JoinColumn( name="moduleId")
)
private Set<Module> modules;
etc etc...

@Entity
public class Module extends LightEntity implements java.io.Serializable {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
etc etc...

The code used to add a module to a student is as follows and is within an EJB;

public void addModule(Student student, Module module) {
student = em.merge(student);
module = em.merge(module);
student.addModule(module);
}

For example when two students take the same module the join table holds;

studentId | moduleId
______________________
46 | 1
47 | 1

If the first student tries to take another module, then 46 , 2 should be added to the table, however we get this error instead;

417906 [httpSSLWorkerThread-8080-1] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 1062, SQLState: 23000
417906 [httpSSLWorkerThread-8080-1] ERROR org.hibernate.util.JDBCExceptionReporter - Duplicate entry '46' for key 'students_id'
417906 [httpSSLWorkerThread-8080-1] ERROR org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
etc etc...

For some reason it is not letting us add another student --> module relationship for the same student. I cannot understand why this is so when it is a ManyToMany relationship. Is there something wrong with the way I add the module to the student? The table creation from MySQL is as follows;

DROP TABLE IF EXISTS `student_ecafmodule`;
CREATE TABLE `student_ecafmodule` (
`studentId` bigint(20) NOT NULL,
`moduleId` bigint(20) NOT NULL,
PRIMARY KEY (`studentId`,`moduleId`),
KEY `FKF7D006538203EAFF` (`moduleId`),
KEY `FKF7D00653EC387445` (`studentId`),
CONSTRAINT `FKF7D00653EC387445` FOREIGN KEY (`studentId`) REFERENCES `student` (`id`),
CONSTRAINT `FKF7D006538203EAFF` FOREIGN KEY (`moduleId`) REFERENCES `ecafmodule` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

So the primary key is a composite of studentId and moduleId and therefore the insert should work, but it doesn't.

Does anyone have any thoughts?

Many thanks

Stuart

When trying to persist, are you invoking save method (you should not) or saveOrUpdate method (you should)?

Hi, thanks for getting back

Well the Student and Module objects are already persisted in previous operations, however we're not using hibernate specific code so we don't actually use save or saveOrUpdate anywhere, we instead use em.persist and em.merge.

In the example we get the latest versions of the objects using merge, then add the Module to the Set<Modules> stored within the Student object and that is it.

At the end of the method the error occurs when Hibernate tries to save the changes to the join table.

Any thoughts?

Thanks

Does anyone have any ideas? I'm not sure whether this is some sort of Hibernate bug? Is there a workaround?
Thank you.

Surely someone must have experienced this problem, I have a feeling it must be related to the way Hibernate works with MySQL 5.1?

I was having this same problem, although my db was HSQLDB. The message turned out to be a red herring, the actual problem was that I had an extra not null column in my many to many join table (left over from a previous implementation choice that I was trying to clean up) that I forgot to remove. So basically, it looks like any kind (or at least multiple kinds) of error on insert will give this message.

My table is defined differently than yours however. Instead of using a compound key, I have no key on the table, just a unique constraint on the combination of the two mapped values and then foreign keys out to the data tables. Give that a try and see if it helps.

@stuart, I have the same problem. Do you have any suggestions please?