0

When i run tests the following migration file causes an

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "ALTER TABLE ACCOUNT
 ADD IS_PROVIDER_ROOT_ACCOUNT VARCHAR(1) NOT NULL,[*]
 ADD PROVIDER_ORGANISATION_ID VARCHAR(255) NULL"; SQL statement:
alter table account
 add is_provider_root_account varchar(1) not null,
 add provider_organisation_id varchar(255) null [42000-200]

error

alter table account
 add is_provider_root_account varchar(1) not null,
 add provider_organisation_id varchar(255) null;

The thing is, if I remove any one of the adds there are no errors. So what can I do here?

My testing configuration file:

spring.datasource.url=jdbc:h2:mem:testdb:MODE=MYSQL
spring.datasource.username=sa
spring.datasource.password=secret
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true

1 Answers1

0

Looking at the H2 syntax (note parentheses), when adding multiple columns, one should do:

alter table account
add (
    is_provider_root_account varchar(1) not null,
    provider_organisation_id varchar(255) null
);
vladtkachuk
  • 656
  • 4
  • 13
  • This does fix my issue, but the thing is it can potentially break the code that relied on the previous version. How can I make H2 understand the migration file without changing it (like with configurations)? –  Jun 21 '22 at 10:58
  • Which H2 version do you have? I doubt that the syntax of this command changed recently. Are you sure it worked before? – vladtkachuk Jun 21 '22 at 11:24
  • Non-standard MySQL-style multi-column `ALTER TABLE` commands aren't supported and were never supported by H2. – Evgenij Ryazanov Jun 21 '22 at 11:41
  • H2 version is 1.4.200 –  Jun 21 '22 at 11:44
  • @EvgenijRyazanov are there any alternatives to H2 that support them? –  Jun 21 '22 at 11:47
  • Neither of three well known pure Java DBMS support it. This syntax is only valid for MySQL, MariaDB, PostgreSQL and their forks, maybe for few others. If you really need to use different database systems (that usually isn't the best idea by itself) you also may need to use the standard syntax only in some cases. In this case it is a two separate commands. – Evgenij Ryazanov Jun 21 '22 at 11:57
  • Sorry @Newbee, I do not get your issue, there was a syntax error in your code which I pointed out to. Maybe you share the initial issue, otherwise we are solving the [XY problem](https://xyproblem.info/). Why do you need a DB with a specific syntax? – vladtkachuk Jun 21 '22 at 12:05
  • The thing is, I didn't write the migration queries, it's companies code and they told me to not change it –  Jun 21 '22 at 13:17
  • got you. maybe there is a typo in your config: `spring.datasource.url=jdbc:h2:mem:testdb:MODE=MYSQL` looks like there should be a separation by semicolon for those extra options `spring.datasource.url=jdbc:h2:mem:testdb;MODE=MYSQL` see [this](https://stackoverflow.com/questions/42364935/how-to-add-the-mode-mysql-to-embedded-h2-db-in-spring-boot-1-4-1-for-datajpates) – vladtkachuk Jun 21 '22 at 13:44
  • Thank you for understanding. After changing the test code and properties to match the link you provided I get "Driver com.mysql.cj.jdbc.Driver claims to not accept jdbcUrl, jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=MYSQL" which is still an error but it's nice to see some change after so long :) –  Jun 21 '22 at 14:00
  • more info http://www.h2database.com/html/features.html#compatibility, "MySQL Compatibility Mode" section, and do not forget to google ;) – vladtkachuk Jun 21 '22 at 14:06