Add missing on start fails with mysql8

Technical Discussion
1

I am facing an issue while setting up new moqui instance with mysql8
If dev environment has an existing database setup for moqui, it fails to create tables for new database.
Steps to regenerate:
Database server : mysql 8
Database drive : mysql 8.0.30

  • Create new database named moqui, configure it with moqui instnace and load data (everything working fine)
  • Create another database named moqui1, configure it with moqui instance and load data
    This process will give following error
23:53:08.483  INFO         main           o.moqui.i.e.EntityFacadeImpl Found entities in 47 files in 52ms
Error loading or running Moqui.loadData with args [{load=, types=all}]: java.lang.reflect.InvocationTargetException
java.lang.reflect.InvocationTargetException
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at MoquiStart.main(MoquiStart.java:152)
Caused by: java.util.ServiceConfigurationError: org.moqui.context.ExecutionContextFactory: Provider org.moqui.impl.context.ExecutionContextFactoryImpl could not be instantiated
        at java.base/java.util.ServiceLoader.fail(ServiceLoader.java:582)
        at java.base/java.util.ServiceLoader$ProviderImpl.newInstance(ServiceLoader.java:804)
        at java.base/java.util.ServiceLoader$ProviderImpl.get(ServiceLoader.java:722)
        at java.base/java.util.ServiceLoader$3.next(ServiceLoader.java:1395)
        at org.moqui.Moqui.loadData(Moqui.java:122)
        ... 5 more
Caused by: Error finding list of DbViewEntity by null [42S02]
        at org.moqui.impl.entity.EntityFindBase.listInternal(EntityFindBase.groovy:1150)
        at org.moqui.impl.entity.EntityFindBase.list(EntityFindBase.groovy:999)
        at org.moqui.impl.entity.EntityFacadeImpl.loadAllEntityLocations(EntityFacadeImpl.groovy:528)
        at org.moqui.impl.entity.EntityFacadeImpl.postFacadeInit(EntityFacadeImpl.groovy:162)
        at org.moqui.impl.context.ExecutionContextFactoryImpl.postFacadeInit(ExecutionContextFactoryImpl.groovy:585)
        at org.moqui.impl.context.ExecutionContextFactoryImpl.<init>(ExecutionContextFactoryImpl.groovy:237)
        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
        at java.base/java.util.ServiceLoader$ProviderImpl.newInstance(ServiceLoader.java:780)
        ... 8 more
Caused by: java.sql.SQLSyntaxErrorException: Table 'moqui1.db_view_entity' doesn't exist
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:972)
        at com.mysql.cj.jdbc.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:582)
        at bitronix.tm.resource.jdbc.proxy.PreparedStatementJavaProxy.executeQuery(PreparedStatementJavaProxy.java:102)
        at org.moqui.impl.entity.EntityQueryBuilder.run(EntityQueryBuilder.java:109)
        at org.moqui.impl.entity.EntityQueryBuilder.executeQuery(EntityQueryBuilder.java:138)
        at org.moqui.impl.entity.EntityFindImpl.iteratorExtended(EntityFindImpl.java:150)
        at org.moqui.impl.entity.EntityFindBase.listInternal(EntityFindBase.groovy:1149)

After initial debugging found while check db DatabaseMetaData.getTables returns list of existing tables, so it does not create new tables hence fails to load data.

This issue will be generate only if multiple moqui databases exists for same mysql user.

2

Here is the workaround for this issue, use con.getCatalog() while fetch tables from database.

tableSet1 = dbData.getTables(con.getCatalog(), ed.getSchemaName(), ed.getTableName(), types)
3

Funny, but not funny. We experienced the same issue and just did a similar workaround. Your solution looks more elegant. Thanks for sharing.

4

Thanks Deepak, getting the catalog from the Connection object is probably the best approach. I added this to the 3 calls to getTables() and the 3 calls to getColumns() in EntityDbMeta.groovy in commit 86edb3e5.

While researching this a bit yesterday (Lawrence mentioned this was happening) I ran across this StackOverflow question where the first answer has a summary of catalog vs schema in MySQL, Postgres, Oracle, and MS SQL Server:

How ‘catalog’, ‘schema’, and ‘database’ are defined in different database servers is a bit messy so specifying a catalog concerns me a bit without testing on other databases, but if there are errors they should be pretty obvious (ie fail quickly) so hopefully this won’t be too problematic if it does turn out that it causes issues in other databases.

2 Likes
5

Thanks David!!