JnG JnG - 7 months ago 190
SQL Question

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

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
enter image description here

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

Answer

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.