MySQL Truncated incorrect DOUBLE value: 'string' in Java Spring Repository

Question

We are currently developing a user-management system for class and we wrote a backend spring application, that saves password, name, email and roles in a mysql database.

We have problems updating rows from the user - table. The error is

SQLException: Truncated incorrect DOUBLE value: 'test@test.de'

test@test.de is a string, and saved in the database as varchar (1024). In Spring we have a entity class in which the email obviously also as string declared is. The query is the standard save() method from CrudRepository.

//EDIT

It seems like, this is a problem within mysql. I logged in via HeidiSQL on the database and tried changing any values manually, but I got the same error.

Following are the mysql tables:

user

role

role_user

The user entity class:

@Entity
@Table(name = "user")
public class UserEntity {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "ID")
  private int id;
  @Column(name = "Name")
  private String name;
  @Column(name = "Email")
  private String email;
  @Column(name = "Password")
  private String password;

  @Transient
  @JsonInclude
  private List<RoleEntity> roles;
...(Getter and Setter Methods) ...

The update method is located in a @Service class and called from a @RestController HttpRequest method. I don't think, there is a problem with the rest Controller or the CrudRepository since the RestController just calls this function and the CrudRepository is empty.

The service update method:

 /**
   * Edits the user with the provided userid, but only those,
   * which are not null.
   *
   * @param entity UserEntity containg to be edited informations, will override roles.
   * @return Error if to be edited params are wrong or new userEntity.
   */
  public ResponseEntity editUser(UserEntity entity) {

    Optional<UserEntity> userEntityOptional = userRepository.findById(entity.getId());
    if (userEntityOptional.isPresent() == false) {
      return new ResponseEntity(new ErrorResponse("Invalid userid"), HttpStatus.BAD_REQUEST);
    }

    //check if all parameters are

    UserEntity toBeEdited = userEntityOptional.get();
    if(entity.getEmail() != null){
      toBeEdited.setEmail(entity.getEmail());
      //return new ResponseEntity(new ErrorResponse("No email modification allowed"), HttpStatus.BAD_REQUEST);
    }
    if (entity.getName() != null) {
      if (entity.getName().length() < 2) {
        return new ResponseEntity(new ErrorResponse("Name too short"), HttpStatus.BAD_REQUEST);
      }
      toBeEdited.setName(entity.getName());
    }
    if (entity.getPassword() != null) {
      if (entity.getPassword().length() < 8) {
        return new ResponseEntity(new ErrorResponse("Password too short"), HttpStatus.BAD_REQUEST);
      }
      toBeEdited.setPassword(BCrypt.hashpw(entity.getPassword(), BCrypt.gensalt()));
    }
    if (entity.getRoles() != null) {
      // Create list for user to role mapping.
      List<RoleUserEntity> roleUserEntities = new ArrayList<>();
      for (RoleEntity role : entity.getRoles()) {
        RoleEntity roleEntity = roleRepository.findByRoleAndDomain(role.getRole(), role.getDomain());
        if (roleEntity == null) {
          // return error
          return new ResponseEntity(new ErrorResponse("Role \"" + role.getRole() + "\" does not exist in domain \"" + role.getDomain() + "\""), HttpStatus.BAD_REQUEST);
        }
        RoleUserEntity roleUserEntity = new RoleUserEntity();
        roleUserEntity.setRoleId(roleEntity.getId())
            .setUserId(toBeEdited.getId());

        roleUserEntities.add(roleUserEntity);

      }
      // Override all Userroles
      roleUserRepository.deleteAllByUserId(toBeEdited.getId());

      roleUserRepository.saveAll(roleUserEntities);
    }

    userRepository.save(toBeEdited);

    // Get user for returning.
    Optional<UserEntity> userEntityResponse = userRepository.findById(entity.getId());

    return new ResponseEntity(createUserResponseWithMessage(userEntityResponse.get(), "User Edited"), HttpStatus.OK);
  }

Answer 1

It seems like, this is a problem within mysql. I logged in via HeidiSQL on the database and tried changing any values manually, but I got the same error.

Yes and it was a problem in mysql. Another team used trigger within this database that create additional rows in another database when UPDATE commands are used.

These trigger looked something like this:

BEGIN
    UPDATE otherdatabase.user
    SET email = NEW.Email AND password = NEW.Password AND username = NEW.Name
    WHERE id = OLD.ID;
END

The Problem here is the AND within the SET phrase. Within a SET phrase, commas must be used instead of AND.

Another story of "just don't let anyone mess with your database".