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, RECURSIVE and others - as well as SYSTEM, EMPTY, JSON_TABLE, LATERAL, ROW, FUNCTION, QUALIFY, TABLESAMPLE, the LIBRARY of MySQL 9 and a few that MySQL 5.7 reserved already, such as LINEAR or VIRTUAL.

  • 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, USE or LONG, and on MySQL NTILE.

  • On MariaDB, the words of MySQL 8.0 that MariaDB reserves as well - ACCESSIBLE, LINEAR, READ_WRITE and ROW_NUMBER - and those that only MariaDB reserves, such as OFFSET, RETURNING, PORTION, VECTOR, TO_DATE or STATS_PERSISTENT. In a table name also the names of built-in functions, such as NTILE, RANK, NOW or GROUP_CONCAT, which MariaDB reserves under the IGNORE_SPACE SQL 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 0 appended is now the delimited word itself: a column LIMIT0, RANGE0 or KEYS0 on MySQL and MariaDB becomes limit, range or keys, and on MariaDB every name that is a reserved word changes alike, e.g. a column ORDER0 becomes order and a table USER0 becomes User. The names built from such a name change the same way: the default join and collection tables and join columns, e.g. the join table ORDER0_ITEMS of an entity Order on MariaDB becomes Order_items and its join column ORDER_ID becomes Order_ID, and on MySQL the join table LINEAR_ITEMS of an entity Linear becomes Linear_items. The schema no longer matches the mapping: reading fails with an unknown column or table, and openjpa.jdbc.SynchronizeMappings adds 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 with lower_case_table_names=0, the default on Linux: there a table WINDOW that an earlier release created on MySQL 5.7 is not the table Window.

To keep the earlier names, set openjpa.Compatibility=UpdatedReservedWords=false:

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.