I faced issues to use the SchemaTool with MySQL 8 failing to upgrade the schema or to recreate it from scratch, with the error "Index/candidate part #0".
In a debug session of the SchemaTool, I observed the IndexInfo is filled with information of another schema/catalog/database.
I have a schema A and a schema B. Both of them have the same database objects (table, columns, indexes, ...). When SchemaTool load this IndexInfo it loads it from schema B, while later it "matches" table columns of the schema A (the one I want to work with).
So I added schema and catalog :
- on the command line => no effet
- in the properties (here JPA persistence.xml) => introspection is good, some schema updates works but index creation are failing
Index creation failing with catalog and schema because the SQL prefixes the index name with catalog name, which is not supported by MySQL (8 at least)
Solution: specify datanucleus.mapping.Schema with the name of the database.
ex: the database/schema in mysql is demo
<property name="datanucleus.ConnectionDriverName" value="com.mysql.cj.jdbc.Driver"/>
<property name="datanucleus.ConnectionURL" value="jdbc:mysql://localhost:3306/demo"/>
<property name="datanucleus.mapping.Schema" value="demo"/>