Names that MySQL and MariaDB reserve
MySQLDictionary and MariaDBDictionary now know the words that the servers reserve, taken from MySQL 8.4 and 9.7 and from MariaDB 10.11 and 12.3:
-
On MySQL, the words that MySQL 8.0 and later reserve: those that came with window functions and common table expressions -
RANK,DENSE_RANK,ROW_NUMBER,LAG,LEAD,GROUPS,WINDOW,OVER,CUME_DIST,PERCENT_RANK,FIRST_VALUE,LAST_VALUE,NTH_VALUE,RECURSIVEand others - as well asSYSTEM,EMPTY,JSON_TABLE,LATERAL,ROW,FUNCTION,QUALIFY,TABLESAMPLE, theLIBRARYof MySQL 9 and a few that MySQL 5.7 reserved already, such asLINEARorVIRTUAL. -
On both, the words that the earlier lists knew as invalid column names only and that the servers reserve in a table name as well, such as
DUAL,KEYS,LIMIT,RANGE,LOCK,RELEASE,SHOW,USEorLONG, and on MySQLNTILE. -
On MariaDB, the words of MySQL 8.0 that MariaDB reserves as well -
ACCESSIBLE,LINEAR,READ_WRITEandROW_NUMBER- and those that only MariaDB reserves, such asOFFSET,RETURNING,PORTION,VECTOR,TO_DATEorSTATS_PERSISTENT. In a table name also the names of built-in functions, such asNTILE,RANK,NOWorGROUP_CONCAT, which MariaDB reserves under theIGNORE_SPACESQL mode that MariaDB Connector/J sets for its connections:CREATE TABLE Ntile (…)fails there.
Both dictionaries delimit a table, column or other name that is a reserved word with back ticks in the SQL they generate - MariaDB now as MySQL does since Reserved word handling and MySQL delimiting - and keep the name, also when it is given explicitly in @Table, @Column or orm.xml; MariaDBDictionary no longer appends a 0 to it.
So the table of an entity named Window, Linear or Dual, or with an attribute rank, limit or offset, can be created, written to and queried; before, CREATE TABLE and INSERT failed with a syntax error.
A schema that an earlier release created can be affected in two ways:
-
A name that got a
0appended is now the delimited word itself: a columnLIMIT0,RANGE0orKEYS0on MySQL and MariaDB becomeslimit,rangeorkeys, and on MariaDB every name that is a reserved word changes alike, e.g. a columnORDER0becomesorderand a tableUSER0becomesUser. The names built from such a name change the same way: the default join and collection tables and join columns, e.g. the join tableORDER0_ITEMSof an entityOrderon MariaDB becomesOrder_itemsand its join columnORDER_IDbecomesOrder_ID, and on MySQL the join tableLINEAR_ITEMSof an entityLinearbecomesLinear_items. The schema no longer matches the mapping: reading fails with an unknown column or table, andopenjpa.jdbc.SynchronizeMappingsadds the new column or table next to the old one. -
A delimited name keeps the case it is given in, for a default table name that of the entity, e.g.
Window, where an undelimited default name is upper case, e.g.WINDOW; so does a name built from it, e.g.Linear_items. Column names are case insensitive on MySQL and MariaDB, but table names are case sensitive on a server withlower_case_table_names=0, the default on Linux: there a tableWINDOWthat an earlier release created on MySQL 5.7 is not the tableWindow.
To keep the earlier names, set openjpa.Compatibility=UpdatedReservedWords=false:
-
On MariaDB this restores the reserved words and the naming of OpenJPA 4.1.x: no name is delimited, and a name that is a reserved word gets a
0appended. The other 4.2 changes of Reserved word handling and MySQL delimiting and Locale-independent case conversion still apply. -
On MySQL it removes the words listed above, and a name that is such a word is generated as in 4.1.x:
LIMIT0orNTILE0for a column, an undelimitedRANKorWINDOW, which MySQL 8.0 and later reject. The words that were reserved before are still delimited, see Reserved word handling and MySQL delimiting.
Alternatively give a single name explicitly in the earlier form, e.g. @Column(name = "LIMIT0") or @Table(name = "WINDOW"), which is delimited as given.
Whether a reserved word is delimited is decided by the dictionary of each persistence unit, so persistence units on MySQL or MariaDB and on another database in the same JVM each get the names they get alone, whichever dictionary is configured first.
The identifiers that are created without a dictionary, e.g. with DBIdentifier.newColumn(String), do not delimit reserved words: a custom MappingDefaults or strategy that builds names from a name that may be a reserved word should pass the identifier through DBDictionary.normalize(DBIdentifier) first, as OpenJPA does, so that the name is delimited, keeps its case and is not renamed on MySQL and MariaDB.
MySQLDictionary and MariaDBDictionary now share their common code in the new AbstractMySQLDictionary. Their public fields, such as TableType, UseClobs, DriverDeserializesBlobs and OptimizeMultiTableDeletes, keep their names and defaults and are still set in openjpa.jdbc.DBDictionary; they are now declared by the superclass, which is only observable through reflection on the declared members of the two classes.