Syntax error dropping database in Flyway Script

Woodsman 0
Viewed 13

I'm not sure which H2 database commands are also valid JDBC commands. My Flyway initialization script drops and re-creates the database and users. I think this error below message will say everything needed to enlighten me. Is it valid to drop and re-create a database in Flyway or does that need to be done ahead of time? I've had Flyway working for ordinary table manipulation. FWIW, the Flyway initialization file is saved with UNIX EOL characters.

Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: Migration V1__init_database.sql failed
--------------------------------------
SQL State  : 42001
Error Code : 42001
Message    : Syntax error in SQL statement "drop [*]database if exists ordersdb\000adrop user if exists `ordersadmin`@`%`\000adrop user if exists `ordersuser`@`%`\000acreate database if not exists ordersdb character set utf8mb4 collate utf8mb4_unicode_ci\000acreate user if not exists `ordersadmin`@`%` identified with mysql_native_password by 'password'\000a\000aGRANT Alter ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Create ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Create view ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Delete ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Drop ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Grant option ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Index ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Insert ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT References ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Select ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Show view ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Trigger ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Update ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Alter routine ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Create routine ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Create temporary tables ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Execute ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Lock tables ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Grant option ON ordersdb.* TO 'ordersadmin'@'%'\000a\000agrant select          on ordersdb.* to `ordersadmin`@`%`\000agrant insert          on ordersdb.* to `ordersadmin`@`%`\000agrant update          on ordersdb.* to `ordersadmin`@`%`\000agrant delete          on ordersdb.* to `ordersadmin`@`%`\000agrant create          on ordersdb.* to `ordersadmin`@`%`\000agrant drop            on ordersdb.* to `ordersadmin`@`%`\000agrant references      on ordersdb.* to `ordersadmin`@`%`\000agrant index           on ordersdb.* to `ordersadmin`@`%`\000agrant alter           on ordersdb.* to `ordersadmin`@`%`\000agrant execute         on ordersdb.* to `ordersadmin`@`%`\000agrant create view     on ordersdb.* to `ordersadmin`@`%`\000agrant show view       on ordersdb.* to `ordersadmin`@`%`\000agrant create routine  on ordersdb.* to `ordersadmin`@`%`\000agrant alter routine   on ordersdb.* to `ordersadmin`@`%`\000agrant event           on ordersdb.* to `ordersadmin`@`%`\000agrant trigger         on ordersdb.* to `ordersadmin`@`%`\000acreate user if not exists `ordersuser`@`%` identified with mysql_native_password by 'password'\000agrant select          on ordersdb.* to `ordersuser`@`%`\000agrant insert          on ordersdb.* to `ordersuser`@`%`\000agrant update          on ordersdb.* to `ordersuser`@`%`\000agrant delete          on ordersdb.* to `ordersuser`@`%`\000aGRANT References ON ordersdb.* TO 'ordersuser'@'%'\000aGRANT Show view ON ordersdb.* TO 'ordersuser'@'%'\000aGRANT Execute ON ordersdb.* TO 'ordersuser'@'%'\000aGRANT Create temporary tables ON ordersdb.* TO 'ordersuser'@'%'\000a\000aflush privileges"; expected "TABLE, INDEX, USER, SEQUENCE, CONSTANT, TRIGGER, VIEW, ROLE, ALIAS, SCHEMA, ALL OBJECTS, DOMAIN, TYPE, DATATYPE, AGGREGATE, SYNONYM"; SQL statement:
drop database if exists ordersdb
drop user if exists `ordersadmin`@`%`
drop user if exists `ordersuser`@`%`
create database if not exists ordersdb character set utf8mb4 collate utf8mb4_unicode_ci
create user if not exists `ordersadmin`@`%` identified with mysql_native_password by 'password'

GRANT Alter ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Create ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Create view ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Delete ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Drop ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Grant option ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Index ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Insert ON ordersdb.* TO 'ordersadmin'@'%'
GRANT References ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Select ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Show view ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Trigger ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Update ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Alter routine ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Create routine ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Create temporary tables ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Execute ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Lock tables ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Grant option ON ordersdb.* TO 'ordersadmin'@'%'

grant select          on ordersdb.* to `ordersadmin`@`%`
grant insert          on ordersdb.* to `ordersadmin`@`%`
grant update          on ordersdb.* to `ordersadmin`@`%`
grant delete          on ordersdb.* to `ordersadmin`@`%`
grant create          on ordersdb.* to `ordersadmin`@`%`
grant drop            on ordersdb.* to `ordersadmin`@`%`
grant references      on ordersdb.* to `ordersadmin`@`%`
grant index           on ordersdb.* to `ordersadmin`@`%`
grant alter           on ordersdb.* to `ordersadmin`@`%`
grant execute         on ordersdb.* to `ordersadmin`@`%`
grant create view     on ordersdb.* to `ordersadmin`@`%`
grant show view       on ordersdb.* to `ordersadmin`@`%`
grant create routine  on ordersdb.* to `ordersadmin`@`%`
grant alter routine   on ordersdb.* to `ordersadmin`@`%`
grant event           on ordersdb.* to `ordersadmin`@`%`
grant trigger         on ordersdb.* to `ordersadmin`@`%`
create user if not exists `ordersuser`@`%` identified with mysql_native_password by 'password'
grant select          on ordersdb.* to `ordersuser`@`%`
grant insert          on ordersdb.* to `ordersuser`@`%`
grant update          on ordersdb.* to `ordersuser`@`%`
grant delete          on ordersdb.* to `ordersuser`@`%`
GRANT References ON ordersdb.* TO 'ordersuser'@'%'
GRANT Show view ON ordersdb.* TO 'ordersuser'@'%'
GRANT Execute ON ordersdb.* TO 'ordersuser'@'%'
GRANT Create temporary tables ON ordersdb.* TO 'ordersuser'@'%'

flush privileges [42001-214]
Location   : db/migration/V1__init_database.sql (/home/bpracht/rsi/jpa-training/repos/orders/target/classes/db/migration/V1__init_database.sql)
Line       : 1
Statement  : drop database if exists ordersdb
drop user if exists `ordersadmin`@`%`
drop user if exists `ordersuser`@`%`
create database if not exists ordersdb character set utf8mb4 collate utf8mb4_unicode_ci
create user if not exists `ordersadmin`@`%` identified with mysql_native_password by 'password'

GRANT Alter ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Create ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Create view ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Delete ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Drop ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Grant option ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Index ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Insert ON ordersdb.* TO 'ordersadmin'@'%'
GRANT References ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Select ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Show view ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Trigger ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Update ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Alter routine ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Create routine ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Create temporary tables ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Execute ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Lock tables ON ordersdb.* TO 'ordersadmin'@'%'
GRANT Grant option ON ordersdb.* TO 'ordersadmin'@'%'

grant select          on ordersdb.* to `ordersadmin`@`%`
grant insert          on ordersdb.* to `ordersadmin`@`%`
grant update          on ordersdb.* to `ordersadmin`@`%`
grant delete          on ordersdb.* to `ordersadmin`@`%`
grant create          on ordersdb.* to `ordersadmin`@`%`
grant drop            on ordersdb.* to `ordersadmin`@`%`
grant references      on ordersdb.* to `ordersadmin`@`%`
grant index           on ordersdb.* to `ordersadmin`@`%`
grant alter           on ordersdb.* to `ordersadmin`@`%`
grant execute         on ordersdb.* to `ordersadmin`@`%`
grant create view     on ordersdb.* to `ordersadmin`@`%`
grant show view       on ordersdb.* to `ordersadmin`@`%`
grant create routine  on ordersdb.* to `ordersadmin`@`%`
grant alter routine   on ordersdb.* to `ordersadmin`@`%`
grant event           on ordersdb.* to `ordersadmin`@`%`
grant trigger         on ordersdb.* to `ordersadmin`@`%`
create user if not exists `ordersuser`@`%` identified with mysql_native_password by 'password'
grant select          on ordersdb.* to `ordersuser`@`%`
grant insert          on ordersdb.* to `ordersuser`@`%`
grant update          on ordersdb.* to `ordersuser`@`%`
grant delete          on ordersdb.* to `ordersuser`@`%`
GRANT References ON ordersdb.* TO 'ordersuser'@'%'
GRANT Show view ON ordersdb.* TO 'ordersuser'@'%'
GRANT Execute ON ordersdb.* TO 'ordersuser'@'%'
GRANT Create temporary tables ON ordersdb.* TO 'ordersuser'@'%'

flush privileges

    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.handleException(DefaultSqlScriptExecutor.java:275) ~[flyway-core-9.3.1.jar:na]
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:222) ~[flyway-core-9.3.1.jar:na]
    at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.execute(DefaultSqlScriptExecutor.java:126) ~[flyway-core-9.3.1.jar:na]
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.executeOnce(SqlMigrationExecutor.java:68) ~[flyway-core-9.3.1.jar:na]
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.lambda$execute$0(SqlMigrationExecutor.java:57) ~[flyway-core-9.3.1.jar:na]
    at org.flywaydb.core.internal.database.DefaultExecutionStrategy.execute(DefaultExecutionStrategy.java:27) ~[flyway-core-9.3.1.jar:na]
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:56) ~[flyway-core-9.3.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.doMigrateGroup(DbMigrate.java:376) ~[flyway-core-9.3.1.jar:na]
    ... 36 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "drop [*]database if exists ordersdb\000adrop user if exists `ordersadmin`@`%`\000adrop user if exists `ordersuser`@`%`\000acreate database if not exists ordersdb character set utf8mb4 collate utf8mb4_unicode_ci\000acreate user if not exists `ordersadmin`@`%` identified with mysql_native_password by 'password'\000a\000aGRANT Alter ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Create ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Create view ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Delete ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Drop ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Grant option ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Index ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Insert ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT References ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Select ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Show view ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Trigger ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Update ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Alter routine ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Create routine ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Create temporary tables ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Execute ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Lock tables ON ordersdb.* TO 'ordersadmin'@'%'\000aGRANT Grant option ON ordersdb.* TO 'ordersadmin'@'%'\000a\000agrant select          on ordersdb.* to `ordersadmin`@`%`\000agrant insert          on ordersdb.* to `ordersadmin`@`%`\000agrant update          on ordersdb.* to `ordersadmin`@`%`\000agrant delete          on ordersdb.* to `ordersadmin`@`%`\000agrant create          on ordersdb.* to `ordersadmin`@`%`\000agrant drop            on ordersdb.* to `ordersadmin`@`%`\000agrant references      on ordersdb.* to `ordersadmin`@`%`\000agrant index           on ordersdb.* to `ordersadmin`@`%`\000agrant alter           on ordersdb.* to `ordersadmin`@`%`\000agrant execute         on ordersdb.* to `ordersadmin`@`%`\000agrant create view     on ordersdb.* to `ordersadmin`@`%`\000agrant show view       on ordersdb.* to `ordersadmin`@`%`\000agrant create routine  on ordersdb.* to `ordersadmin`@`%`\000agrant alter routine   on ordersdb.* to `ordersadmin`@`%`\000agrant event           on ordersdb.* to `ordersadmin`@`%`\000agrant trigger         on ordersdb.* to `ordersadmin`@`%`\000acreate user if not exists `ordersuser`@`%` identified with mysql_native_password by 'password'\000agrant select          on ordersdb.* to `ordersuser`@`%`\000agrant insert          on ordersdb.* to `ordersuser`@`%`\000agrant update          on ordersdb.* to `ordersuser`@`%`\000agrant delete          on ordersdb.* to `ordersuser`@`%`\000aGRANT References ON ordersdb.* TO 'ordersuser'@'%'\000aGRANT Show view ON ordersdb.* TO 'ordersuser'@'%'\000aGRANT Execute ON ordersdb.* TO 'ordersuser'@'%'\000aGRANT Create temporary tables ON ordersdb.* TO 'ordersuser'@'%'\000a\000aflush privileges"; expected "TABLE, INDEX, USER, SEQUENCE, CONSTANT, TRIGGER, VIEW, ROLE, ALIAS, SCHEMA, ALL OBJECTS, DOMAIN, TYPE, DATATYPE, AGGREGATE, SYNONYM"; SQL statement:

I'm using H2's Mysql Compatibility mode. Here's the relevant Spring Boot application.properties lines:

spring.datasource.url=jdbc:h2:mem:ordersdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=MYSQL;DATABASE_TO_LOWER=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

If certain options are not supported in H2 (even under compatibility mode), is it possible to have a separate H2 specific Flyway script and if so, how?
flyway h2 jdbc mysql
0 Answers
Related