Mysql connector fails at creating table with composite primary key

Question

I'm trying to create entity with composite primary key that is used by spring framework, UserConnection. Exception and the classes as follows;

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'rank integer not null, refreshtoken varchar(512), secret varchar(512), primary k' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_161]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_161]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_161]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_161]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.Util.getInstance(Util.java:408) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912) ~[mysql-connector-java-5.1.46.jar:5.1.46]

@Entity
@IdClass(UserConnectionId.class)
@Table(name = "Userconnection")
public class UserConnection implements Serializable {

@Id
@Column(name = "Userid", unique = true, length = 255, nullable = false)
private String userId;

@Id
@Column(name = "Providerid", length = 255, nullable = false)
private String providerId;

@Id
@Column(name = "Provideruserid", length = 255, nullable = false)
private String providerUserId;

@Column(name = "Rank", nullable = false)
private int rank;

@Column(name = "Displayname", length = 255, nullable = true)
private String displayName;

@Column(name = "Profileurl", length = 512, nullable = true)
private String profileUrl;

@Column(name = "Imageurl", length = 512, nullable = true)
private String imageUrl;

@Column(name = "Accesstoken", length = 512, nullable = true)
private String accessToken;

@Column(name = "Secret", length = 512, nullable = true)
private String secret;

@Column(name = "Refreshtoken", length = 512, nullable = true)
private String refreshToken;

@Column(name = "Expiretime", nullable = true)
private Long expireTime;
}

and I have a class for primary key;

public class UserConnectionId implements Serializable {

    private int userId;
    private int providerId;
    private int providerUserId;

    public UserConnectionId(int userId, int providerId, int providerUserId) {
        this.userId = userId;
        this.providerId = providerId;
        this.providerUserId = providerUserId;
    }

    @Override
    public int hashCode() {
        return Objects.hash(userId, providerId, providerUserId);
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        UserConnectionId other = (UserConnectionId) obj;
        if (providerId != other.providerId)
            return false;
        if (providerUserId != other.providerUserId)
            return false;
        if (userId != other.userId)
            return false;
        return true;
    }
}

I got exception without using @IdClass. As a solution usage of @IdClass recommended by community. But still getting the exception. Have you ever got the exception?

Thanks in advance.

score 0 · Answer 1 · answered Jul 09 '18 at 15:55

0

Most likely because Rank is a MySQL reserved keyword (in versions 8+):

https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-R

Either change the column name to avoid conflicts or escape it. For the latter option there is some discussion here:

Creating field with reserved word name with JPA

answered Jul 09 '18 at 15:55

Alan Hay

22,665
4
56
110

1

Oh missed it thanks. It seems gonna work but have any idea If we solve it as that, will It block the spring's usage of the bean? I mean in the following link says "This table will be used automatically by Spring Social API" and "You must not change the structure of this table". [link](https://o7planning.org/en/11823/social-login-with-oauth2-in-spring-boot#a17245706) section 5- Entity & DAO – Jul 09 '18 at 16:49

Mysql connector fails at creating table with composite primary key

1 Answers1