Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement;

Question

I am following up this tutorial here http://hellokoding.com/registration-and-login-example-with-spring-xml-configuration-maven-jsp-and-mysql/ to create a registration and signup process using spring mvc and maven. I have created the relationship mapped to user_role between role table and user table. Everything seems to be fine, but when I try to create a new user the application throws error.

User.java

@ManyToMany
    @JoinTable(name = "user_role", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "role_id"))
    public Set<Role> getRoles() {
        return roles;
    }

Role.java

@ManyToMany(mappedBy = "roles")
    public Set<User> getUsers() {
        return users;
    }

This is the pictoral representation of the relationship between role table to role_user table and user table to role_user table

Please why am I getting this error Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement;

this is the comlete stacktrace

HTTP Status 500 - Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not execute statement
org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not execute statement
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:982)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:729)

Addition of more stacktrace

root cause
org.hibernate.exception.DataException: could not execute statement
    org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:69)
    org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:211)


root cause

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'password' at row 1
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3845)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
    com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
    com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
    com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
    com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1901)
    com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2113)
    com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2049)
    com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2034)

The above stacktrace occurs when I am trying to create a new user

Please what am I getting wrong? Many thanks

@LearningPhase http://chat.stackoverflow.com/rooms/109905/discussion-between-learningphase-and-jng — Blaze, Apr 25 '16 at 08:10
Here you see the reason: `Data truncation: Data too long for column 'password' at row 1` Your Input for Password is longer than the column — Jens, Apr 25 '16 at 08:51
The length of the password is varchar 45 and the password I am inputing is 8 characters — Blaze, Apr 25 '16 at 08:58
Try to debug. Maybe there are trailing blanks or something like that — Jens, Apr 25 '16 at 10:01
i think the password may be hashed / encrypted and this generates more chars as expected. please check this — Hisham Khalil, Apr 25 '16 at 10:08
looked in the code and yes PASSWORD is beeing encrypted . " user.setPassword(bCryptPasswordEncoder.encode(user.getPassword()));" — Hisham Khalil, Apr 25 '16 at 10:13
@LearningPhase http://stackoverflow.com/questions/36842257/jdbc4-mysqlintegrityconstraintviolationexception-cannot-add-or-update-a-child — Blaze, Apr 25 '16 at 13:43
@Hishamkh http://stackoverflow.com/questions/36842257/jdbc4-mysqlintegrityconstraintviolationexception-cannot-add-or-update-a-child — Blaze, Apr 25 '16 at 13:43

score 1 · Accepted Answer · answered Apr 25 '16 at 10:26

1

issue is clear "Data too long for column 'password' at row 1"

in the example you use the UserService encrypts the password before saving the user.

here UserServiceImpl.class

@Override
    public void save(User user) {
        user.setPassword(bCryptPasswordEncoder.encode(user.getPassword()));
        user.setRoles(new HashSet<>(roleRepository.findAll()));
        userRepository.save(user);
    }

try this link to see the produced encrypted password and expand your column accordingly.

I hope this helps.

answered Apr 25 '16 at 10:26

Hisham Khalil

1,054
8
9

So what particular varchar length do you suggest I use on my db – Blaze Apr 25 '16 at 10:33
here explained : http://stackoverflow.com/questions/5881169/what-column-type-length-should-i-use-for-storing-a-bcrypt-hashed-password-in-a-d and summary CHAR(60) – Hisham Khalil Apr 25 '16 at 10:39
I think my error has changed com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'accounts.user_role' doesn't exist – Blaze Apr 25 '16 at 10:40
did you check your db? – Hisham Khalil Apr 25 '16 at 11:33
yeah, the table is there – Blaze Apr 25 '16 at 12:59

Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement;

1 Answers1