h2数据库版本升级,RunScript 报错CREATE PRIMARY KEY SYSTEM_LOB_STREAM_PRIMARY_KEY ON SYSTEM_LOB_STREAM(ID, PART) [42001-214]

2022-08-23 06:37

准备工作

将低版本的*.mv.db 文件比如 h2.mv.db,放到一个目录下比如C:/download,

将生成此文件的jar包找到,放到同一目录下,比如 h2-1.4.197.jar

再找到要使用的2.x版本的jar包,比如h2-2.1.214.jar 也放到一起。

导出

导出备份为backup.zip文件,打开cmd,执行以下Script 命令

java -cp .\h2-1.4.197.jar org.h2.tools.Script -url "jdbc:h2:file:C:/download/h2" -user admin -password admin123 -script backup.zip -options compression zip

生成新格式

在cdm中执行以下RunScript 命令,将backup.zip 生成 新格式文件h2_new

java -cp .\h2-2.1.214.jar org.h2.tools.RunScript -url "jdbc:h2:file:C:/download/h2_new" -user admin -password admin123 -script backup.zip -options compression zip VARIABLE_BINARY

会生成一个新的mv.db文件,此时这个文件就可以替换原始文件。

升级就成功了。

遇到问题

执行生成新格式时候遇到报错:

Exception in thread "main" org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "\000d\000aCREATE [*]PRIMARY KEY SYSTEM_LOB_STREAM_PRIMARY_KEY ON SYSTEM_LOB_STREAM(ID, PART)"; expected "OR REPLACE, FORCE, VIEW, ALIAS, SEQUENCE, USER, TRIGGER, ROLE, SCHEMA, CONSTANT, DOMAIN, TYPE, DATATYPE, AGGREGATE, LINKED, MEMORY, CACHED, LOCAL TEMPORARY, GLOBAL TEMPORARY, TEMP, TEMPORARY, TABLE, SYNONYM, UNIQUE, HASH, SPATIAL, INDEX"; SQL statement:

CREATE PRIMARY KEY SYSTEM_LOB_STREAM_PRIMARY_KEY ON SYSTEM_LOB_STREAM(ID, PART) [42001-214]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:502)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
at org.h2.message.DbException.getSyntaxError(DbException.java:261)
at org.h2.command.Parser.getSyntaxError(Parser.java:900)
at org.h2.command.Parser.read(Parser.java:5667)
at org.h2.command.Parser.parseCreate(Parser.java:6812)
at org.h2.command.Parser.parsePrepared(Parser.java:763)
at org.h2.command.Parser.parse(Parser.java:689)
at org.h2.command.Parser.parse(Parser.java:666)
at org.h2.command.Parser.prepare(Parser.java:537)
at org.h2.engine.SessionLocal.prepare(SessionLocal.java:581)
at org.h2.engine.SessionLocal.prepare(SessionLocal.java:565)
at org.h2.command.dml.RunScriptCommand.execute(RunScriptCommand.java:103)
at org.h2.command.dml.RunScriptCommand.update(RunScriptCommand.java:70)
at org.h2.command.CommandContainer.update(CommandContainer.java:169)
at org.h2.command.Command.executeUpdate(Command.java:252)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:252)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:223)
at org.h2.tools.RunScript.processRunscript(RunScript.java:273)
at org.h2.tools.RunScript.runTool(RunScript.java:137)
at org.h2.tools.RunScript.main(RunScript.java:66)

打开backup.zip解压,修改里面的script.sql文件,找到这里创建主键语法错误。我们改成建表的时候就创建主键:

CREATE TABLE IF NOT EXISTS SYSTEM_LOB_STREAM(ID INT NOT NULL, PART INT NOT NULL, CDATA VARCHAR, BDATA BINARY);
CREATE PRIMARY KEY SYSTEM_LOB_STREAM_PRIMARY_KEY ON SYSTEM_LOB_STREAM(ID, PART);  

这两行,合并为一行,改为

CREATE TABLE IF NOT EXISTS SYSTEM_LOB_STREAM(ID INT NOT NULL, PART INT NOT NULL, CDATA VARCHAR, BDATA BINARY, PRIMARY KEY(ID, PART));

再将script.sql打包回backup.zip重新执行生成命令,成功。


详见 https://h2database.com/html/tutorial.html#upgrade_backup_restore

# mysql