Play Framework SQL evolution script - error with integer column?

Question

I got the following error:

We got the following error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'integer not null, constraint pk_checkpoint primary key (id))' at line 9 [ERROR:1064, SQLSTATE:42000], while trying to run this SQL script:

The generated SQL:

1 # --- Rev:1,Ups - 491c235

2

3 create table checkpoint (

4 id                        bigint auto_increment not null,

5 name                      varchar(80) not null,

6 longitude                 double not null,

7 latitude                  double not null,

8 points                    integer not null,

9 message                   varchar(160) not null,

10 scenario_id               bigint,

11 index                     integer not null,

12 constraint pk_checkpoint primary key (id))

13;

My model:

@Entity
public class Checkpoint extends Model {

    @Id
    public Long id;

    @Column(length = 80, nullable = false)
    public String name;

    @Column(nullable = false)
    public double longitude;

    @Column(nullable = false)
    public double latitude;

    @Column(nullable = false)
    public int points;

    @Column(length = 160, nullable = false)
    public String message;

    @OneToMany(cascade = CascadeType.ALL)
    public List<CheckpointAnswer> possibleAnswers = new ArrayList<CheckpointAnswer>();

    @ManyToOne(cascade = CascadeType.PERSIST)
    public Scenario scenario;

    @Column(nullable = false)
    public int index;
}

What's wrong?

index is a reserved word, try changing it slightly. – dwjv Jan 15 '14 at 13:18 — dwjv, Jan 15 '14 at 13:18

score 0 · Answer 1 · edited May 23 '17 at 12:03

To add to the above, and not to detract from the point that avoiding reserved words is probably a good idea, if you really wanted the column to be called index, you can manually create the table with MySQL workbench or your sql tool of choice using a backtick around the column name (`index`). In Scala/anorm at least, this doesn't change how you refer to the column in the source code. (For cross-database compatability, configuring MySQL to use double quotes rather than backticks would probably also be a good idea. Further discussion here.)

Play Framework SQL evolution script - error with integer column?

1 Answers1