Invalid MySQL schema generated when used with Connector/J 8.0.30

[nscuro]

Bug Report

DataNucleus RDBMS 6.0.1 generates an invalid schema for MySQL, when Connector/J 8.0.30 is used.

With 8.0.29, DataNucleus generates the following:

-- ----------------------------------------------------------------
-- DataNucleus SchemaTool (ran at 28/08/2022 22:09:16)
-- ----------------------------------------------------------------
-- Complete schema required for the following classes:-
--     mydomain.model.Person
--
-- Table "PERSON" for classes [mydomain.model.Person]
CREATE TABLE "PERSON"
(
    "ID" BIGINT NOT NULL,
    "NAME" VARCHAR(255) BINARY NULL,
    CONSTRAINT "PERSON_PK" PRIMARY KEY ("ID")
) ENGINE=INNODB;

With 8.0.30, the type of "NAME" changes to TINYTEXT(255):

-- ----------------------------------------------------------------
-- DataNucleus SchemaTool (ran at 28/08/2022 21:26:53)
-- ----------------------------------------------------------------
-- Complete schema required for the following classes:-
--     mydomain.model.Person
--
-- Table "PERSON" for classes [mydomain.model.Person]
CREATE TABLE "PERSON"
(
    "ID" BIGINT NOT NULL,
    "NAME" TINYTEXT(255) NULL,
    CONSTRAINT "PERSON_PK" PRIMARY KEY ("ID")
) ENGINE=INNODB;

The TINYTEXT type does not accept a length argument, causing the generated schema to be invalid.

I didn't spot anything obvious in the Connector/J changelogs that could explain this behavior.

Test Case

I've put together a test setup to reproduce this here: https://github.com/nscuro/datanucleus-test-jdo/tree/rdbms-issue-446

• start-database.sh launches a MySQL 5.7 database with Docker
• generate-schema.sh uses the DN SchemaTool to generate the complete schema and write it to schema.sql

[andyjefferson]

The only thing different is the JDBC driver, so the JDBC driver has changed its support for some JDBC/SQL type. Using schematool "dbinfo" would tell you where the problem is.

[nscuro]

You're right, the default for the VARCHAR type has changed from VARCHAR(M) BINARY to TINYTEXT:

JDBC Type=VARCHAR sqlTypes=VARCHAR(M) BINARY,TINYTEXT (default=VARCHAR(M) BINARY)
    SQLTypeInfo : [JDBC-DRIVER]
      type : name = VARCHAR(M) BINARY, jdbcId = 12, localName = VARCHAR, createParams = 
      precision = 65535, allowsSpec = true, numPrecRadix = 10
      scale : min = 0, max = 0, fixedPrec = false
      literals : prefix = ', suffix = '
      nullable = 1, caseSensitive = true, searchable = 3, unsigned = false, autoIncrement = false

    SQLTypeInfo : [JDBC-DRIVER]
      type : name = TINYTEXT, jdbcId = 12, localName = TINYTEXT, createParams =  [CHARACTER SET charset_name] [COLLATE collation_name]
      precision = 255, allowsSpec = true, numPrecRadix = 10
      scale : min = 0, max = 0, fixedPrec = false
      literals : prefix = ', suffix = '
      nullable = 1, caseSensitive = true, searchable = 3, unsigned = false, autoIncrement = false

JDBC Type=VARCHAR sqlTypes=VARCHAR(M) BINARY,TINYTEXT (default=TINYTEXT)
    SQLTypeInfo : [JDBC-DRIVER]
      type : name = VARCHAR(M) BINARY, jdbcId = 12, localName = VARCHAR, createParams = 
      precision = 65535, allowsSpec = true, numPrecRadix = 10
      scale : min = 0, max = 0, fixedPrec = false
      literals : prefix = ', suffix = '
      nullable = 1, caseSensitive = true, searchable = 3, unsigned = false, autoIncrement = false

    SQLTypeInfo : [JDBC-DRIVER]
      type : name = TINYTEXT, jdbcId = 12, localName = TINYTEXT, createParams =  [CHARACTER SET charset_name] [COLLATE collation_name]
      precision = 255, allowsSpec = true, numPrecRadix = 10
      scale : min = 0, max = 0, fixedPrec = false
      literals : prefix = ', suffix = '
      nullable = 1, caseSensitive = true, searchable = 3, unsigned = false, autoIncrement = false

And it looks like TINYTEXT is reported as supporting a precision specification (allowsSpec = true), despite not actually doing so. Looking at the DN codebase, this seems to be a common issue with MySQL:

datanucleus-rdbms/src/main/java/org/datanucleus/store/rdbms/adapter/MySQLTypeInfo.java

Lines 71 to 84 in 5dae971

	private void fixAllowsPrecisionSpec()
	{
	if (typeName.equalsIgnoreCase("LONG VARCHAR") ||
	typeName.equalsIgnoreCase("BLOB") ||
	typeName.equalsIgnoreCase("MEDIUMBLOB") ||
	typeName.equalsIgnoreCase("LONGBLOB") ||
	typeName.equalsIgnoreCase("MEDIUMTEXT") ||
	typeName.equalsIgnoreCase("LONGTEXT") ||
	typeName.equalsIgnoreCase("TEXT"))
	{
	// Some MySQL types don't allow precision specification i.e "LONG VARCHAR(...)" is illegal
	allowsPrecisionSpec = false;
	}
	}

I raised a PR in #447 and verified that it fixes the issue.