Helidon CLI - database sample does not work with MySQL due to case sensitivity

[daffodilistic]

Environment Details

MacBook-Air-2:database-se daffodilistic$ helidon version
build.date 2022-02-04 12:37:38 PST
build.version 2.3.3
build.revision 600f89b3
project.version 1.0-SNAPSHOT
project.helidon.version 2.5.0
project.flavor SE
latest.helidon.version 2.5.0
MacBook-Air-2:database-se daffodilistic$ javac --version
javac 17.0.1
MacBook-Air-2:database-se daffodilistic$ uname -a
Darwin MacBook-Air-2.local 21.4.0 Darwin Kernel Version 21.4.0: Fri Mar 18 00:47:26 PDT 2022; root:xnu-8020.101.4~15/RELEASE_ARM64_T8101 arm64
MacBook-Air-2:database-se daffodilistic$ docker --version
Docker version 20.10.13, build a224086

---

Problem Description

The database quickstart code generated by Helidon CLI fails to run if the MySQL JDBC connector is used. It appears to have hung with no detailed debug logs/output, even when starting helidon dev with the --verbose argument. It turns out that the generated quickstart code calls row.column() with uppercase column names (e.g. ID_TYPE in PokemonMapperProvider.java), which causes this issue.

Steps to reproduce

1. Install Helidon CLI
2. Create a starter Helidon project with helidon init, select the database quickstart sample
3. Run the project with helidon dev --verbose. Observe that accessing http://localhost:8080/pokemon works as expected. Terminate the Helidon process with ^C
4. Start a fresh MySQL Docker container (Docker Compose file follows):

version: '3.3'
services:
  mysql:
    image: mysql:8
    environment:
      MYSQL_DATABASE: 'pokemon'
      MYSQL_USER: 'user'
      MYSQL_PASSWORD: 'password'
      MYSQL_ROOT_PASSWORD: 'password'
    ports:
      - '3306:3306'

Confirm that the MySQL container is up and running by using a MySQL client to connect to it using MySQL Workbench, mysql CLI client, DBeaver, etc.
5. Add the MySQL JDBC connector to pom.xml:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

6. Set the db prop in application.yaml:

db:
  source: jdbc
  connection:
    url: jdbc:mysql://127.0.0.1:3306/pokemon
    username: user
    password: password
    poolName: mysql

7. Run the project with helidon dev --verbose again. Observe that accessing http://localhost:8080/pokemon times out, despite the fact that Helidon has already started up with WEB server is up! http://localhost:8080/pokemon being the last line logged to the console. Verify that the tables have already been created at this point in time via the MySQL client. Terminate the Helidon process
8. Change all db.column calls to use lowercase names, e.g. ID_TYPE to id_type in PokemonMapper.read().
9. Restart Helidon via helidon dev --verbose, and access http://localhost:8080/pokemon. This should work as per normal.

[ljnelson]

Hello; to make sure I'm understanding fully, you are effectively suggesting changing these lines in the Helidon SE database archetype, where various DbClient things are generated:

https://github.com/oracle/helidon/blob/b5905959f5b743c43be93f0a563b78a5a86a793c/archetypes/helidon/src/main/archetype/se/database/files/src/main/java/__pkg__/PokemonMapperProvider.java.mustache#L39-L41

Is that correct?

[ljnelson]

Furthermore, indeed MySQL identifiers are stored and processed on disk as lowercase. Offhand this seems like a reasonable change. I'm not familiar with this code but I'll see what I can do.

[ljnelson]

(And then of course the archetype should ideally work on all databases on all platforms. Maybe changing these identifiers to lower case will work on MySQL but I don't know enough to know if that will work on other databases. Researching.)

[daffodilistic]

Hello!

I think that as a low-hanging fruit, the sample code can be changed to follow the one from the example directory here:
https://github.com/oracle/helidon/blob/b094d6766b5ba39bba7b38a77832c9ce3c3a9407/examples/dbclient/pokemons/src/main/java/io/helidon/examples/dbclient/pokemons/PokemonMapper.java#L34-L36

Additionally, updating the JavaDoc for the DbRow.column() method for the time being with a caveat on case-sensitivity would be useful, especially for newcomers to the Helidon project. :)

As a longer-term goal, yup, ideally DbClient should work across all databases universally as much as possible without having to create/configure settings for most use cases.