How to convert batch/batchsamples to use MariaDB instead of H2?

[djangofan]

My question should be a simple one: How can I convert batch/batchsamples to use MariaDB instead of H2?

When I run the mvn clean install, the tests use a embedded H2 database to do the copy from CSV into H2 table

I just want to convert it to using my MariaDB Docker instance instead.

Is this an easy change? I tried to put datasource properties in the application.properties files BUT it didn't work. Looks like it's not that simple. Also, billsetuptask seems to fail at creating the table: BILL_STATEMENTS

[djangofan]

Here is what I tried, but it doesn't work fo rme. :

5. docker exec -it test-mariadb mariadb --user root -ppass
6. CREATE DATABASE jsoncopy;

then add this class to both Mvn task projects:

@Configuration
public class DataSourceConfig {
    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.mariadb.jdbc.Driver");
        dataSource.setUrl("jdbc:mariadb://localhost:3306/jsoncopy");
        dataSource.setUsername("root");
        dataSource.setPassword("pass");
        return dataSource;
    }
}

After doing this, the billsetuptask will pass and appears to work but the billrun task fails , with the following error:

Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]; nested exception is java.sql.SQLSyntaxErrorException: (conn=146) Table 'jsoncopy.BATCH_JOB_INSTANCE' doesn't exist
Caused by: java.sql.SQLSyntaxErrorException: (conn=146) Table 'jsoncopy.BATCH_JOB_INSTANCE' doesn't exist

My code example that reproduces this is here:
https://github.com/djangofan/spring-cloud-data-example-0

[onobc]

HI @djangofan ,
Glad you are continuing your SCDF adventure!

Does the jsoncopy.BATCH_JOB_INSTANCE exist? SCDF bootstrap that using Flyway (see src/main/java/org/springframework/cloud/dataflow/server/db/migration/db2/V1__Initial_Setup.java). My guess is that it bootstrapped it before using a diff db than jsoncopy so did not do it once you switched to jsoncopy.

[djangofan]

Ah ok. I've used FlyWay a lot but because I didn't see it in the pom anywhere, I wouldn't have guessed that is how you had it setup. If the pom.xml had a FlyWay config in it, then I would have got it right away.

The tests pass and when the tests run I do see FlyWay running BUT it is not apparent how FlyWay is loading due to the word FlyWay not appearing anywhere in this source code.

I'll probably be able to eventually figure this out, but wanting help since it is not documented well.

The enablement of this seems to be related to these application.properties settings? This is my best guess as I start to investigate.

spring.datasource.initialization-mode=always
spring.batch.initialize-schema=always

[djangofan]

I am close:

1. On Windows

   1. Everything seems to work. I think i figured out how to switch the database, but i need more time to verify. the issue is probably that i am trying to do it with the application.properties (due to wanting my tests to also use the relational db) , and not by passing on the command line, which i may still need to try.

2. On Mac, 2 problems:

   1. adding the file:// url to import billtask as source (via the UI) isn't working where that same type of path worked on my Windows machine. nothing seen in the log. the exception around this is probably being swallowed, unfortunately.
   2. my Docker MariaDB doesnt work with the billrun and billsetuptask because of what appears to be a db case sensitivity issue. i'll probably make an attempt at using PostGres instead. hopefully that gets me working.