JPA table "sequence" does not exist

Question

Database:

user_account
id(pk)
email
password
...

user_detail
id(pk fk)
name_first
name_last
...

Entity

@Entity
@Table(name="user_account")     
@SecondaryTable(name="user_detail", pkJoinColumns=@PrimaryKeyJoinColumn())
public class UserAccount implements Serializable{
    private static final long serialVersionUID = -2606506548742732094L;

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Integer id;
    private String email;
    private String password;
    private String tab;
    private String shortcut;
    private String setting;
    private Integer role;

    @Column(table="user_detail", name="name_first")
    private String nameFirst;
    @Column(table="user_detail", name="name_last")
    private String nameLast;
    @Column(table="user_detail")
    private String occupation;
    @Column(table="user_detail")
    @Temporal(TemporalType.DATE)
    private Date birth;
    ....
}

Action

    try{
        EntityTransaction transaction = em.getTransaction();
        transaction.begin();
        em.persist(currentUser);

        transaction.commit();
    } catch (Exception e){
    }

Error

INFO: [EL Warning]: 2012-01-06 18:45:46.77--ClientSession(17472935)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'mazedb.sequence' doesn't exist Error Code: 1146 Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ? bind => [2 parameters bound] Query: DataModifyQuery(name="SEQUENCE" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")

INFO: ERROR: Internal Exception: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'mazedb.sequence' doesn't exist Error Code: 1146 Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ? bind => [2 parameters bound] Query: DataModifyQuery(name="SEQUENCE" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")

I tried the other way around, having two different entities merged by @PrimaryKeyJoinColumn but I got the same error.

Answer 1

If EclipseLink tries to access this table, that means that it's supposed to be there. GenerationType.AUTO means that EclipseLink chooses the most appropriate generation type for your database (MySQL). In this case, the choice is to use a table-based generator, which needs a table. See http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Entities/Ids/GeneratedValue.

If you don't want to use this strategy, choose another one.