Table not found in MySQL8


stephane
 
Edited

Hi,

I'm facing some issue to use a fresh new mysql8 server. DN doesn't find the tables because of case sensitivity.

Tables are created in lowercase while DN search for upper case.

Debugging schema introspection:
- First, DN load database options/features: JDBC metadata storesUpperCaseQuotedIdentifiers and supportsMixedCaseIdentifiers returns TRUE which forces DN to upper case tables, columns, ... names (in DatastoreIdentifierImpl)

- Then DN loads database objects (tables, columns, ...) matching these upper case names with lower case names returned by the database (RDBMSSchemaHandler getTableType) and doesn't find any match


There are probably a lot of good reasons to have this algorithm, but on one hand (tables names coming from the mapping) DN forces upper case and on the other hand
 (tables names coming from the database).

Could that makes sense to force the name of database objects (table column, index, ...) in the same way DN forces these names of tables, columns, indexes, ... coming from the mapping
, applying IdentifierFactory.getIdentifierInAdapterCase on rs.getString(3) here in RDBMSSchemaHandler getTableType:

if ((insensitive && tableName.equalsIgnoreCase(rs.getString(3))) ||
(!insensitive && tableName.equals(rs.getString(3))))

thanks,

I haven't seen so far any settings to override this behavior to force lower case or force upper case or just leave names unchanged ?

I uses DN 5.2.1, MySQL 8.0.23 and tested the 2 drivers JDBC 5.1.40 and 8.0.23. The connection pool is HikaryCP.

--
Stephane


Andy
 
Edited

DN applies whatever it is told to use by the user, such as via "datanucleus.identifier.case", as defined in the docs.

I've never had a need for changing the code to use MariaDB, so have to assume that your use of persistence properties is the issue. The only way code will get changed for such an area (that has been used for the last 17 years), is to provide valid testcase, including start up database data.


stephane
 

Sure it's an amazing amount of good quality work you did those past 17 years !

Here I don't know how to make a test case. It's linked to the database settings at the schema creation time, and how they are translated into jdbc metadata by drivers. My feeling is upgrade process from 5.7 to 8 doesn't give the same result than creating the schema on a fresh install of mysql8.

What I saw is the mapping made by the driver changed over time about lowercase_table_names variable to the jdbc metadata properties. Now, with mysql8, there are also changes in the dictionary and joins ... and recommendation to upgrade the jdbc driver... 

thanks for the option, I missed it in the code. I finally rolled back to mysql 5.7 recreate everything with lowercase_table_names = 1. Application seems to work again, but I don't know yet how to upgrade to mysql 8...

thanks
--
Stephane