从1.4.200升级到2.1.212后，H2自增不起作用。

Question

我们正在将一个应用程序从spring-boot 2.4.3升级到2.7.0。Hibernate核心版本为5.6.9.Final。 2.7.0是第一个使用H2数据库2.1.212的版本，我们正在用它进行测试。 我按照他们的迁移指南做了一些更改，但我遇到了一个问题，至今没有找到解决方案。

在某些集成测试中，我们使用SQL脚本在运行该类中的测试之前插入数据。例如，脚本在表中插入3个项目，然后测试插入第4个项目并更新已经存在的最初的3个项目之一。

进行插入的测试失败，并显示以下错误。

DataIntegrityViolationException. Error message: could not execute statement; SQL [n/a]; constraint ["PRIMARY KEY ON studio.exercise(id) ( /* key:1 */ 1, 'Test Exercise', FALSE, 'TEST', NULL, 1, 'TEST', TIMESTAMP '2021-01-01 16:00:13', NULL, NULL)"; 
SQL statement: insert into exercise (id, created_by, created_on, modified_by, modified_on, archived, image, organization_id, title, type) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-212]]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

它抱怨无法插入ID为1的项目，因为另一个具有该ID的项目已存在（由脚本插入）。但在之前的H2数据库版本中，这不是问题。

我认为这个帖子与我的有关，但我看不懂提出的解决方案H2数据库中的AUTO_INCREMENT在使用Postman请求时不起作用。

- Cristian

Answer 1

AUTO_INCREMENT是MySQL及其派生版本的一个功能。

H2的新版本仅具备标准的身份列(GENERATED { BY DEFAULT | ALWAYS } AS IDENTITY)。H2仍然可以在某些兼容模式下接受AUTO_INCREMENT和其他一些特定于供应商的子句，但是它们都会被转换为身份列。

GENERATED BY DEFAULT AS IDENTITY列也允许手动插入值(GENERATED ALWAYS AS IDENTITY不允许)。当您插入这样的值时，默认情况下不会调整序列生成器的基本值，这就是为什么会出现异常的原因。此行为对于标准身份列是正确的。通常，您不应该为它们提供自己的值。

CREATE TABLE TEST(
    ID BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    A INTEGER,
    B INTEGER);
-- Bad insertion, may create problems in the future
INSERT INTO TEST(ID, A, B) VALUES (1, 10, 11), (2, 12, 13);
-- Correct insertion
INSERT INTO TEST(A, B) VALUES (10, 11), (12, 13);
-- Alternative syntax for correct insertion
INSERT INTO TEST(ID, A, B) VALUES (DEFAULT, 10, 11), (DEFAULT, 12, 13);

在使用者提供的值插入行之后，身份列生成器的基础值可以被固定：

-- Bad insertion
INSERT INTO TEST(ID, A, B) VALUES (1, 10, 11), (2, 12, 13);
-- Fix
ALTER TABLE TEST ALTER COLUMN ID RESTART WITH (SELECT MAX(ID) FROM TEST) + 1;

在某些兼容模式下，H2仍然像MySQL和其他一些数据库系统一样运行，请参考它们的文档了解更多细节。在这些模式下，插入用户提供的值会自动调整标识列的生成器，就像旧版本的不受支持的H2中那样。

- Evgenij Ryazanov

---

我们正在使用的是 MSSQLServer 模式，而不是 MySQL。不过，这在 H2 2.x 开始发生了变化？ - Cristian

是的，但是在H2的MSSQLServer兼容模式中启用了基值调整技巧，您可能希望启用它。 - Evgenij Ryazanov

我不得不在MSSQLServer兼容模式下启用“限制”，因此我也启用了其他选项，但没有一个起作用。你说的是哪一个？ - Cristian

抱歉，我之前的说法是错误的。你可以将 Mode.updateSequenceOnManualIdentityInsertion 改为 true，但这些标志没有得到任何支持，并且可能在任何版本的 H2 中被修改而不发出警告。 - Evgenij Ryazanov

首先，我会尝试在不启用此模式的情况下修复所有测试。只有在正确的方法需要太长时间时，我才会启用它。感谢您的帮助。 - Cristian

在您的评论中，“GENERATED ALWAYS BY IDENTITY” 应该改为 “GENERATED ALWAYS AS IDENTITY”。 - user2163960

Answer 2

如果您有脚本插入初始数据，请不要自己插入ID。

例如：

如果您正在使用

insert into exercise (id, created_by, created_on, ...)

请替换为

insert into exercise (created_by, created_on, ...)

我在升级到使用H2 2.1.212的Spring Boot 2.7后遇到了相同的情况，并通过从data.sql中删除插入语句中的ID来解决了这个问题。

- user3586286

---

谢谢！我一直在为此苦恼。阅读了您的答案后，我找到了一些测试数据，正好可以解决这个问题。非常感谢。 - crea1

Answer 3

当我迁移到SpringBoot 2.7并将H2版本从1.4.200更改为2.1.214时，我遇到了同样的问题。我需要在启动时插入一个配置中的ID，所以不能使用H2的默认值。而且代码需要在H2和MySQL数据库上运行。 最终有效的方法是在数据源URL中添加Mode=MySQL。

spring:
  datasource:
    url: "jdbc:h2:mem:testdb;Mode=MySQL"

- meh

---

这对于我的一个项目似乎有效，但对其他项目无效。H2似乎希望将null作为插入脚本的一部分插入ID列。出现错误：Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: 列"ACC_PROV_GRP_ID"不允许为NULL；SQL语句： insert into account_group (acc_grp_id, account_manager, is_active, last_request_timestamp, odc_account_id, operation_type) values (null, ?, ?, ?, ?, ?) [23502-220][acc_grp_id被定义为auto_increment] - Jalpesh