Primary key column check may need to be case-insensitive for MySQL/MariaDB

[esnhysythh]

Primary key column check may need to be case-insensitive for MySQL/MariaDB

This is related to issue #310, but I think I found the root cause.

Problem

When using Hive Metastore with MySQL 9.x or MariaDB JDBC driver, and the database server has lower_case_table_names=1, DataNucleus throws this error:

org.datanucleus.exceptions.NucleusUserException: Table "columns_v2" has been specified with a primary-key to include column "COLUMN_NAME" but this column is not found in the table. Please check your <primary-key> column specification. 
at org.datanucleus.store.rdbms.table.ElementContainerTable.applyUserPrimaryKeySpecification(ElementContainerTable.java:215) 
at org.datanucleus.store.rdbms.table.CollectionTable.initialize(CollectionTable.java:235) 
at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.initializeClassTables(RDBMSStoreManager.java:3260)

Just like issue #310

Root Cause

In ElementContainerTable.applyUserPrimaryKeySpecification(), the code compares column names using equals() without ignoring case.

The expected column name is uppercase (e.g., "COLUMN_NAME"), but the actual column name from elementMapping.getDatastoreMapping(j).getColumn().getIdentifier().getName() is lowercase ("column_name") because of the namingCase setting in IdentifierFactory.

However, MySQL column names are case-insensitive by default. The comparison should not fail because of case difference.

Why this happens with MySQL 9.x / MariaDB drivers ?

In class BaseDataStoreAdaptor, the code checks DatabaseMetaData.storesUpperCaseQuotedIdentifiers().

• MySQL 5.7~8.x JDBC driver: returns true → it will run supportedOptions.add(IDENTIFIERS_UPPERCASE);
• MySQL 9.x and MariaDB JDBC driver: returns false

When it returns false, AbstractIdentifierFactory will fall back namingCase into lowercase.

and DataNucleus shows this warning:

[main] WARN  DataNucleus.Persistence - Identifier Factory required identifiers in "UPPERCASE" but datastore adapter doesnt support this exactly, so using "LOWERCASE" instead

This behavior change in MySQL 9.x was introduced in this commit: mysql/mysql-connector-j@be06152

Suggested Fix

The column name comparison in applyUserPrimaryKeySpecification() should be case-insensitive for databases that don't care about case for columns (like MySQL).

Alternatively, using getColumnMetaData().getName() instead of getIdentifier().getName() could also work, since it preserves the original case from metadata.

Reproduce Steps

1. Set up MySQL 5.7 with lower_case_table_names=1
2. Use Hive Metastore 2.3.10
3. Test with different JDBC drivers:
   • MySQL 5.7/8.x driver: works fine
   • MySQL 9.x or MariaDB driver: throws error when starting Metastore