JPA table "sequence" does not exist

10
votes

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.

5
javajpaeclipselink
Well, the message says that you need to have a sequence table. Why don't you create it? – JB Nizet
But why would I need this table if is not supposed to be there? – TGM

5 Answers

14
votes

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.

6
votes

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" -->
0
votes

@GeneratedValue(strategy=GenerationType.AUTO) looks like a default value provided by IDE on entity creation. However, if you are using org.eclipse.persistence.jpa.PersistenceProvider, given the way it processes this annotation (leading to the exception being thrown), the simplest solution would be to just use @GeneratedValue(strategy=GenerationType.IDENTITY) instead.

0
votes

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>
  ...
-1
votes

In the configuration file, org.hibernate.dialect.oracle10gdialect line can be forgotten. Indeed, org.hibernate.dialect.mysqldialect should be.