JPA EclipseLink DatabaseException: 'table foo.SEQUENCE doesn't exist'

Question

I've updated the question so that both tables now use auto-increment. Is perhaps the problem in persisting to the MESSAGES table a problem with the database schema?

In trying to persist a MessageBean as so:

private void persist(MessageBean messageBean) throws Exception {
    LOG.info("loading.." + messageBean);

    Messages message = new Messages(messageBean);

    emf = Persistence.createEntityManagerFactory("USENETPU");
    em = emf.createEntityManager();

    em.getTransaction().begin();
    em.persist(message);
    em.getTransaction().commit();
}

Stack trace:

run:
Jul 27, 2012 3:04:06 PM net.bounceme.dur.usenet.controller.CommentsDefaultListModel persist
INFO: loading..floor installer (cultas lake)
[EL Info]: 2012-07-27 15:04:10.006--ServerSession(30409723)--EclipseLink, version: Eclipse Persistence Services - 2.3.0.v20110604-r9504
[EL Info]: 2012-07-27 15:04:11.78--ServerSession(30409723)--file:/home/thufir/NetBeansProjects/USENET/build/classes/_USENETPU login successful
[EL Warning]: 2012-07-27 15:04:12.072--ClientSession(29574192)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'nntp.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 = ?")
Jul 27, 2012 3:04:12 PM net.bounceme.dur.usenet.controller.CommentsDefaultListModel <init>
SEVERE: null
Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'nntp.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 = ?")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)

From MySql:

mysql> 
mysql> 
mysql> show tables;
+----------------+
| Tables_in_nntp |
+----------------+
| comments       |
| messages       |
+----------------+
2 rows in set (0.00 sec)

mysql> 
mysql> show create table comments;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                  |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| comments | CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message_id` int(11) NOT NULL,
  `comment` text NOT NULL,
  `stamp` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `message_id` (`message_id`),
  CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`message_id`) REFERENCES `messages` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> show create table messages;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                               |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| messages | CREATE TABLE `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `newsgroup` text NOT NULL,
  `subject` text NOT NULL,
  `content` text NOT NULL,
  `number` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

and the fields for Messages:

package net.bounceme.dur.usenet.controller;

import java.io.Serializable;
import java.util.Collection;
import javax.persistence.*;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlTransient;

@Entity
@Table(name = "messages", catalog = "nntp", schema = "")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "Messages.findAll", query = "SELECT m FROM Messages m"),
    @NamedQuery(name = "Messages.findById", query = "SELECT m FROM Messages m WHERE m.id = :id")})
public class Messages implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "id", nullable = false)
    @GeneratedValue
    private Integer id;
    @Basic(optional = false)
    @Lob
    @Column(name = "newsgroup", nullable = false, length = 65535)
    private String newsgroup;
    @Basic(optional = false)
    @Lob
    @Column(name = "subject", nullable = false, length = 65535)
    private String subject;
    @Basic(optional = false)
    @Lob
    @Column(name = "content", nullable = false, length = 65535)
    private String content;
    @Basic(optional = false)
    @Lob
    @Column(name = "number", nullable = false, length = 65535)
    private String number;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "messageId")
    private Collection<Comments> commentsCollection;

    public Messages() {
    }

And the Comments fields:

package net.bounceme.dur.usenet.controller;

import java.io.Serializable;
import java.util.Date;
import javax.persistence.*;
import javax.xml.bind.annotation.XmlRootElement;

@Entity
@Table(name = "comments", catalog = "nntp", schema = "")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "Comments.findAll", query = "SELECT c FROM Comments c"),
    @NamedQuery(name = "Comments.findById", query = "SELECT c FROM Comments c WHERE c.id = :id"),
    @NamedQuery(name = "Comments.findByStamp", query = "SELECT c FROM Comments c WHERE c.stamp = :stamp")})
public class Comments implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "id", nullable = false)
    @GeneratedValue
    private Integer id;
    @Basic(optional = false)
    @Lob
    @Column(name = "comment", nullable = false, length = 65535)
    private String comment;
    @Basic(optional = false)
    @Column(name = "stamp", nullable = false)
    @Temporal(TemporalType.DATE)
    private Date stamp;
    @JoinColumn(name = "message_id", referencedColumnName = "id", nullable = false)
    @ManyToOne(optional = false)
    private Messages messageId;

    public Comments() {
    }

Answer 1

For mysql I would recommend you following:

At you table messages at field id add declaration auto_increment:

create table messages(
...
id  int not null auto_increment,
...
primary key (id)
)

At entity declaration use

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;   

This talks to JPA use auto-increment feature of MySQL

If it is not applicable (for example you may want to create related another entity in the same transaction) use TABLE strategy (for more details see http://www.objectdb.com/java/jpa/entity/generated )

Answer 2

The AUTO strategy is an alias for NATIVE if your database supports it, or SEQUENCE if your database supports it, or TABLE if your database doesn't support any of those.

So if the database doesn't support NATIVE and SEQUENCE, then you need to create the table that EclipseLink uses to generate IDs.

With MySQL, NATIVE should be supported. You need to make the ID column an auto_increment column, though. Make sure to configure the appropriate DatabasePlatform as well.