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

This exception can occur even if you use @GeneratedValue(strategy=GenerationType.IDENTITY)!

From the EclipseLink Wiki:

Note: IDENTITY strategy is database specific and not supported on all database. IDENTITY is supported on Sybase, DB2, SQL Server, MySQL, Derby, JavaDB, Informix, SQL Anywhere, H2, HSQL, Access, and Postgres databases.

I changed the database server and client from MySQL to MariaDB which is not supported for GenerationType.IDENTITY as stated above. So I had to add the following property to the persistence.xml:

<persistence ...
  <persistence-unit ...
  ...
  <properties>
    <property name="eclipselink.target-database" value="MySQL"/>
  </properties>
  ...

Answer 2

I had never had this error before despite doing this kind of thing a hundred times. I found this error was because I had a hibernate property for creating tables in the persistence.xml but was using EclipseLink:

It was:

    <property name="hibernate.hbm2ddl.auto" value="create"/>

I changed to the following to fix the error:

    <property name="eclipselink.ddl-generation" value="drop-and-create-tables"/>
    <!-- or just value="create-tables" -->

Answer 3

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.