Error on upgrade of h2 from 1.4.200 to 2.0.204

[Glen Peterson]

Syntax error in SQL statement "CREATE TABLE USER[*]

Could this be because `user` is a reserved word?

http://www.h2database.com/html/advanced.html#keywords

Looks like it created a bunch of other tables just fine.  MySQL and MariaDB don't care.

ebean version: 12.14.0
Loaded properties from []
DataSourcePool [mj_online] autoCommit[false] transIsolation[READ_COMMITTED] min[2] max[200] in[139ms]

Executing mj_online-drop-all.sql - 121 statements, autoCommit:true
Executing mj_online-create-all.sql - 121 statements, autoCommit:false

==============================================================================
ERROR: Initial JPA entityManagerFactory creation failed:
javax.persistence.PersistenceException: Failed to run script
ERROR: Initial JPA entityManagerFactory creation failed:
javax.persistence.PersistenceException: Failed to run script

==============================================================================
Caused by:
java.sql.SQLException: Error executing stmt[create table user (
  id                            bigint generated by default as identity not null,
  deleted                       boolean default false not null,
  last_name                     varchar(255) not null,

...

)] error[Syntax error in SQL statement "CREATE TABLE USER[*] (\000a  ID                            BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,\000a  DELETED                       BOOLEAN DEFAULT FALSE NOT NULL,\000a  LAST_NAME                     VARCHAR(255) NOT NULL,...

[Glen Peterson]

I renamed my User table to User1 and h2 2.0.204 worked perfectly.

--

---
You received this message because you are subscribed to a topic in the Google Groups "Ebean ORM" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ebean/_sdyThBB1QI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ebean+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/aa14a634-ca52-4bf0-892e-d69a53298d0an%40googlegroups.com.

[Rob Bygrave]

Noting that "Proper" support for H2database v2.x is going to be in Ebean 12.15.0.  There were a number of changes in H2Database 2.x around identity(), sequence syntax, array syntax.

Cheers, Rob.

You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/CA%2BLnoUmOk1E%3DuaA9LBwBsSO5ZEFFq6ehkk1VBaP5%3DknFSR7wHQ%40mail.gmail.com.

[Oje Preradovic]

Same issue, but using latest Ebean (13.6.6)

Works just fine in H2 1.4.200 but same errors when trying to use 2.1.214

We are setting the provider in code :

import io.ebean.platform.h2.H2Platform;

DatabaseConfig serverConfig = new DatabaseConfig();

serverConfig.setDatabasePlatform(new H2Platform());

Is there a new switch or class for H2 V2 ??

Errors like:

Caused by: javax.persistence.PersistenceException: Failed to run script
Caused by: java.sql.SQLException:
Error executing stmt[create table picklist_item (
  id                            integer not null,
  parent_id                     integer,
  display_order                 integer,
  display_text                  varchar(255),
  value                         varchar(255),
  description                   varchar(255),
  item_level                    integer,
  picklist_id                   integer not null
)] error[Syntax error in SQL statement "create table picklist_item (\000a  id                            integer not null,\000a  parent_id                     integer,\000a  display_order                 integer,\000a  display_text                  varchar(255),\000a  [*]value                         varchar(255),\000a  description                   varchar(255),\000a  item_level                    integer,\000a  picklist_id                   integer not null\000a)"; expected "identifier"; SQL statement:

Hopefully just a switch of config setting ...

[Oje Preradovic]

Rob any update or tips on H2 Version 2 with Ebeans version 13?? Version 1 has lots of security issues.

Thank you

On Tuesday, February 1, 2022 at 2:02:06 PM UTC-8 Rob Bygrave wrote:

[Glen Peterson]

My solution has been to stick with h2 1.4.200.  It's test-scoped, so I'm less worried about vulnerabilities.  I'd like to upgrade, but it's not a priority.

To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/8a163566-84bf-4905-b929-3971eaab374en%40googlegroups.com.

[Rob Bygrave]

Note that latest Ebean currently depends on h2database version 2.1.212

The jdbc url used in ebean testing has the NON_KEYWORDS parameter set.

e.g.

datasource.h2.url=jdbc:h2:mem:testsMem;DB_CLOSE_ON_EXIT=FALSE;NON_KEYWORDS=KEY,VALUE

Pretty sure it would work with NON_KEYWORDS=USER if you wanted to try that.

Cheers, Rob.

To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/CA%2BLnoUmOv51pCC66n5kF1RbdMDtTBwgydKjug-eJ_u86tcLu5w%40mail.gmail.com.

[Glen Peterson]

Looks like that worked.  Thank you Rob!  For this, and for ebean!

val dsc = DataSourceConfig()
dsc.platform = "h2"
dsc.username = "SOMEUSER"
dsc.password = "SOMEPASSWORD"
dsc.url = "jdbc:h2:mem:myDatabase"
dsc.driver = "org.h2.Driver"
dsc.addProperty("NON_KEYWORDS", "USER")
dbConfig.dataSourceConfig = dsc

To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/CAC%3Dts-EhjCqobv0KjMvHCAC5sKbUOEg6BHfZZpdgtoYgHwo0_Q%40mail.gmail.com.

[Rob Bygrave]

Cool.

Note that all Ebean users need to be aware of:

https://stackoverflow.com/questions/73377190/bytecode-transforming-record-class-to-be-mutable

 

Cheers, Rob.

To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/CA%2BLnoUk-CfbAgntL1TgxSyJL65CJEZ5vo%3DtNjTb1MJdFUH84Jw%40mail.gmail.com.

[Oje Preradovic]

So Latest Ebean 13,9,2 with h2 2.1.212 still gives same error when creating tables:

Error executing stmt[create table picklist_item (
  id                            integer not null,
  parent_id                     integer,
  display_order                 integer,
  display_text                  varchar(255),
  value                         varchar(255),
  description                   varchar(255),
  item_level                    integer,
  picklist_id                   integer not null
)] error[Syntax error in SQL statement "create table picklist_item (\000a  id                            integer not null,\000a  parent_id                     integer,\000a  display_order                 integer,\000a  display_text                  varchar(255),\000a  [*]value                         varchar(255),\000a  description                   varchar(255),\000a  item_level                    integer,\000a  picklist_id                   integer not null\000a)"; expected "identifier"; SQL statement:
create table picklist_item (
  id                            integer not null,
  parent_id                     integer,
  display_order                 integer,
  display_text                  varchar(255),
  value                         varchar(255),
  description                   varchar(255),
  item_level                    integer,
  picklist_id                   integer not null

) [42001-212]]

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException:

This code used to work on H2 V1:

DatabaseConfig serverConfig = new DatabaseConfig();

serverConfig.setName("default");
serverConfig.setDdlGenerate(true);
serverConfig.setDdlRun(true);
serverConfig.setDatabasePlatform(new H2Platform());
serverConfig.addPackage("com.sl.microservice.service.orm.inventory");
serverConfig.addPackage("com.sl.microservice.service.orm.system");
serverConfig.addPackage("com.sl.server.entity");
HikariConfig config = new HikariConfig();
config.setDataSourceClassName("org.h2.jdbcx.JdbcDataSource");
config.setConnectionTestQuery("VALUES 1");
config.addDataSourceProperty("URL", "jdbc:h2:mem:ebeans_test");
HikariDataSource ds = new HikariDataSource(config);
serverConfig.setDataSource(ds);

_ebeanServer = DatabaseFactory.create(serverConfig);  

Note same exception when using Driver instead of DataSource

[Rob Bygrave]

h2 changed such that "value" is now a keyword and in the jdbc URL configuration that your app is using we see:

> config.addDataSourceProperty("URL", "jdbc:h2:mem:ebeans_test");

Specifically there is no use of the NON_KEYWORDS option in the URL. 

e.g.

datasource.h2.url=jdbc:h2:mem:testsMem;DB_CLOSE_ON_EXIT=FALSE;NON_KEYWORDS=KEY,VALUE

So the options are to use the NON_KEYWORDS option in the jdbc url or move away from using value as a database column name.

Cheers, Rob.

[Oje Preradovic]

Hi Rob!

Thank you for looking into this. So obviously nothing to do with ebeans just my ignorance about h2.

It works thank you so much !!!!

Hopefully this helps some other who have switched to the new version of H2 ...

Cheers!

[Rob Bygrave]

Yeah, H2 changed their keywords and I'd suggest this is probably going to trip up a lot of people over the next year or so.

I believe they did it to be more SQL compliant so that seems reasonable justification but I suspect lots of people are going to get caught out by this and it isn't super obvious unless you know what you are looking for.

Apologies I didn't get to this earlier.  I'm finding the odd google group email is going into my gmail spam folder and don't know why that is yet.

Cheers, Rob.

To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/321cc268-419e-4686-8390-1d14a874bec8n%40googlegroups.com.

[Oje Preradovic]

No worries Rob thank you for getting back to me! Everyone is freaking due to security issues with H2 V1 (and even a lot of the V2 apparently) so this has saved us!

Cheers!

[Glen Peterson]

Thanks for Ebean.  Whenever someone mentions another ORM, I have almost a compulsion to say, "Ebean!"

When I use mysqldump, it puts back-ticks around each table or column name, whether reserved or not:

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) DEFAULT NULL,
  `middle_name` varchar(255) DEFAULT NULL,
  `phone_number` varchar(255) DEFAULT NULL,

Would it make sense for Ebean to do something similar with all create/drop statements and queries?  Would that be compatible with most or all SQL versions?  That should remove most naming restrictions, which is particularly nice if you swap database vendors (for unit testing, or any other reason).

To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/f93a8532-eff5-49f7-86cc-37431c8dabe5n%40googlegroups.com.

[Rob Bygrave]

> Would it make sense for Ebean to do something similar with all create/drop statements and queries? 

Ebean has the allQuotedIdentifiers configuration option which means all table names and all column names are quoted with the database platform specific quotes (like backtick for MySql and [] for SqlServer etc).

> Would that be compatible with most or all SQL versions?  That should remove most naming restrictions, which is particularly nice if you swap database vendors (for unit testing, or any other reason).

Yes and I think this is the main reason that people would use this option.

Now noting that this isn't the default option for Ebean or my personal preference and that's because my formative years were as "An Oracle Database guy" which has the bias against using quoted identifiers. The main argument against quoted identifiers as I see it is that they are viral in the sense that if we adopt allQuotedIdentifiers then that tends towards ALL the tooling that will use that db schema ALSO must use quoted identifiers (Reporting tools, ETL tools, etc) - its the train that you can't easily stop and that every tool using the database needs to get on.

This is also maybe slightly Old School in that it comes from the days where DBAs were active in the Database Design process - these folks knew not to create a table called "order" etc. In the more current world Devs do all this and DBAs are doing more ops or automated away or third parties and are no longer part of the DB design process. 

So I'd personally not choose allQuotedIdentifiers unless I had to but other folks with a more SqlServer bias would be more likely to use it.

It would be nice if Ebean warned folks as part of DDL generation when ANSI SQL keywords are being used.

Cheers, Rob.

To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/CA%2BLnoUnjTcaawbsdirauCsow52OzDa0Gx4RZb0gpdaKA5TR6sg%40mail.gmail.com.

[Oje Preradovic]

Oh I just noticed that gmail put the rest of this conversation in SPAM!!

Annoying!!!

[Glen Peterson]

On Tue, Oct 4, 2022 at 3:42 PM Rob Bygrave <robin....@gmail.com> wrote:

> Would it make sense for Ebean to do something similar with all create/drop statements and queries? 

Ebean has the allQuotedIdentifiers configuration option which means all table names and all column names are quoted with the database platform specific quotes (like backtick for MySql and [] for SqlServer etc).

Oooh.  Nice!  I did not know!

 

The main argument against quoted identifiers as I see it is that they are viral in the sense that if we adopt allQuotedIdentifiers then that tends towards ALL the tooling that will use that db schema ALSO must use quoted identifiers (Reporting tools, ETL tools, etc) - its the train that you can't easily stop and that every tool using the database needs to get on.

Thank you, that's enlightening.  I'm sure that if you quote enough reserved words with enough databases and tools you'll find weird, hard to diagnose bugs where the quotes aren't respected, or are interpreted differently somewhere.  Also you mentioned that different databases use different quotes, so when you switch databases, Ebean might still work, but your backup script, database migrations, hand-coded JDBC queries and other tools might not work without changing them, or even maintaining multiple versions of your queries for them!  Great argument for avoiding these words instead of quoting them!

It would be nice if Ebean warned folks as part of DDL generation when ANSI SQL keywords are being used.

Great idea!

Also, "Ebean Identifier Quoting" might deserve a paragraph or two on the website: What Ebean can do, and why it's turned off by default.  Let me know if you want that and if I can help with it.

 

To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/CAC%3Dts-GARCT7X0%2B0Y6feuM3f0As%3DpgGSSRTrPPzr23nnUv7zTg%40mail.gmail.com.