H2 org.h2.jdbc.JdbcSQLSyntaxErrorException occurs when executing a script file in a h2 database

I have used java -cp h2-1.4.199.jar org.h2.tools.RunScript -url jdbc:h2:mem:db1 -script infra_params.sql command to execute below sql script in a H2 database.

infra_params.sql file:-

DROP TABLE IF EXISTS `infrastructure_parameter`;

CREATE TABLE `infrastructure_parameter` (
`id` varchar(36) NOT NULL,
`created_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`modified_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`NAME` varchar(255) DEFAULT NULL,
`PROPERTIES` varchar(255) DEFAULT NULL,
`ready` tinyint(1) DEFAULT '0',
`TYPE` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `UNQ_infrastructure_parameter_0` (`NAME`,`PROPERTIES`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

LOCK TABLES `infrastructure_parameter` WRITE;

But it gives following exception:-

Exception in thread "main" org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "

LOCK[*] TABLES `INFRASTRUCTURE_PARAMETER` WRITE "; SQL statement:


LOCK TABLES `infrastructure_parameter` WRITE [42000-199]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:451)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:427)
at org.h2.message.DbException.get(DbException.java:205)
at org.h2.message.DbException.get(DbException.java:181)
at org.h2.message.DbException.getSyntaxError(DbException.java:229)
at org.h2.command.Parser.getSyntaxError(Parser.java:989)
at org.h2.command.Parser.parsePrepared(Parser.java:951)
at org.h2.command.Parser.parse(Parser.java:788)
at org.h2.command.Parser.parse(Parser.java:764)
at org.h2.command.Parser.prepareCommand(Parser.java:683)
at org.h2.engine.Session.prepareLocal(Session.java:627)
at org.h2.engine.Session.prepareCommand(Session.java:565)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1292)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:217)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:205)
at org.h2.tools.RunScript.process(RunScript.java:261)
at org.h2.tools.RunScript.process(RunScript.java:192)
at org.h2.tools.RunScript.process(RunScript.java:328)
at org.h2.tools.RunScript.runTool(RunScript.java:143)
at org.h2.tools.RunScript.main(RunScript.java:70)

Any help on how to fix this issue would be appreciated.

Answer

LOCK TABLES is a MySQL-specific command, it isn’t supported by H2.

You need to remove it from your file.

If you really need to use the same script in both MySQL and H2 and need this command in MySQL, you can try to wrap it into executable comment. MySQL, unlike other databases, executes code in /*! … */ comments.

/*! LOCK TABLES `infrastructure_parameter` WRITE; */