Make some codes as entity, repository and application.yml (includes H2 database info).
|
1 2 3 4 5 6 7 8 9 10 |
@Data @Entity public class Member { @Id @GeneratedValue private Long id; @Column(nullable = false) private String name; } |
|
1 2 |
public interface MemberRepository extends JpaRepository<Member, Long> { } |
|
1 2 3 4 5 6 7 8 9 |
spring: h2: console: enabled: true jpa: show-sql: true datasource: hikari: jdbc-url: jdbc:h2:mem:testdb;MODE=MySQL; # MODE=MySQL is the point |
And then, you try to run the test bellow.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
@SpringBootTest class DemoApplicationTests { @Autowired private MemberRepository memberRepository; @Test void test() { memberRepository.save(new Member()); // try to insert null-value into the database System.out.println(memberRepository.findAll()); } } |
The result of the test is success. It’s contrary to what you expected.
The column is declared as not null. But the result is success to insert null-value into the column.
|
1 2 3 4 5 6 |
Hibernate: create table member (id bigint not null, name varchar(255) not null, primary key (id)) ... Hibernate: call next value for hibernate_sequence Hibernate: insert into member (name, id) values (?, ?) Hibernate: select member0_.id as id1_0_, member0_.name as name2_0_ from member member0_ [Member(id=1, name=)] |
I tried to search from google and stack over flow. And I found a bit of the clue.
When inserting data, if a column is defined to be NOT NULL and NULL is inserted, then a 0 (or empty string, or the current timestamp for timestamp columns) value is used. Usually, this operation is not allowed and an exception is thrown. 1
And also I read the official documents of mysql compatibility of h2 databases.
I think the article of the stack-over-flow quoted the official documents. But there is not.
So I guess there is something changed recently. (the article of the stack-over-flow posted at 2016)
I check the github issues of h2 database.
This compatibility code was written a long time ago when MySQL was very different from others.
MySQL got a STRICT mode that disables different legacy tricks. It was enabled by default some time ago: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict
There is no normal way to toggle convertInsertNullToZero in H2, only the whole MySQL compatibility mode can be enabled. And the MySQL compatibility mode is the only one user of that compatibility code.
I’m not sure what to do with it. Because default behavior of MySQL was changed in 2015 we may want to remove this flag and all related code now for better compatibility with default behavior of modern versions. But somebody may still use this trick, of course.yeah, lets tracks the behaviour of the current stable version of MySQL, we’re not aiming to be perfect with these other-database modes 2
Just as I expected 🙂 3
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
@SpringBootTest class DemoApplicationTests { @Autowired private MemberRepository memberRepository; @Test void test() { memberRepository.save(new Member()); // try to insert null-value into the database String result = memberRepository.findAll().get(0).getName(); assertFalse(result == null); // it's not null assertTrue(result == ""); // actually it's empty string } } |
It is inserted 0 or empty-string until mysql 5.6 and bellow, if you try to insert null-value into column having not null constraint. but it’s changed since mysql 5.7, using strict mode as default. 4
By the way, It is supported at h2 dabatase using mysql compatibility recently, since 14 oct 2019 as version 1.4.200.
Issue #1942: MySQL Mode: convertInsertNullToZero should be turned off by default?
Issue #1940: MySQL Mode: Modify column from NOT NULL to NULL syntax 5
And is that applied at springboot? Sure.
Upgrade to H2 1.4.200 #18724 6
H2 1.4.200 is applied since springboot 2.2.1.RELEASE and 2.1.10.RELASE.
If you are using lastest version of springboot, then not null constraint is operated as you expected, even if you are using h2 database with mysql compatiblity.
And if you have some trouble to upgrade the springboot version, you can do upgrade just h2 library version to 1.4.200 as com.h2database:h2:1.4.200.
- issue of stack-over-flow – https://stackoverflow.com/questions/20013154/h2-does-not-enforce-not-null-in-mysql-compatibility-mode ↩
- H2 gitHub issue – https://github.com/h2database/h2database/issues/1942 ↩
- H2 gitHub PR – https://github.com/h2database/h2database/pull/1944/files ↩
- overview H2 mysql compatibility – https://makandracards.com/makandra/51169-understanding-sql-compatibility-modes-in-mysql-and-mariadb ↩
- H2 Release Note – https://h2database.com/html/changelog.html ↩
- SpringBoot Release Note – https://github.com/spring-projects/spring-boot/releases ↩