Schema name in Create index statement while generating datanucleus JDO schema

Question

I am trying to generate schema from the DataNucleus SchemaTool for a mysql database, that will store countries and states. Here is a sample of that code:

@PersistenceCapable
Public class State{
    private String shortCode;
    private String fullName;
    @Column(allowsNull = "true",name="country_id")
    private Country countryId;
}

The following are my schemaGeneration properties:

datanucleus.ConnectionDriverName=com.mysql.jdbc.Driver
datanucleus.ConnectionURL=jdbc:mysql://localhost:3306/geog
datanucleus.ConnectionUserName=geog
datanucleus.ConnectionPassword=geogPass
datanucleus.schema.validateTables=true
datanucleus.mapping.Catalog=geog
datanucleus.mapping.Schema=geog

In my Country class as well, I have a mapping from a Collection, so that the FK reference for States to the Country table is built correctly.

But there is one problem. In the SQL script generated, the Index part has the Schema name as part of the index name itself, which fails the entire script. Here is that piece:

CREATE INDEX `GEOG`.`MST_STATE_N49` ON `GEOG`.`MST_STATE` (`COUNTRY_ID`);

Notice the schema name in the GEOG.MST_STATE_N49 part of the index' name.

I tried setting the schema and catalog name to blank but that yields a ''.MST_STATE_N49 which still fails.

I am using MySQL Server 5.7.17 using the 5.1.42 version of the JDBC driver (yes, not the latest) on Data nucleus JDO 3.1

Any hints on how I can get rid of the schema/catalog name in the generated DDL?

Answer 1

Why are you putting "datanucleus.mapping.Schema" when using MySQL ? MySQL doesnt use schema last I looked. Similarly the "datanucleus.mapping.Catalog" is effectively defined by your URL! MySQL only actually supports JDBC catalog, mapping on to "database", as per this post. Since DataNucleus simply uses the JDBC driver then catalog is the only useful input.

Consequently removal of both schema and catalog properties will DEFAULT to the right place.