MySQL catalog

The MySQL catalog connects Gravitino to a MySQL server over JDBC. One catalog reaches the whole server: each MySQL database is a schema, and each table in it is a table. Schemas and tables are both read-write.

MySQL table options such as the storage engine are exposed as table properties, so a table created through Gravitino can choose its engine and its first auto-increment value.

Quick start

The example connects a MySQL server as the catalog mysql_catalog and lists its databases as schemas. MySQL Connector/J does not ship in the image, so enable it in the chart first with gravitino.jdbcDrivers.enabled and gravitino.jdbcDrivers.mysql.enabled, as described in JDBC driver.

UI

1. Start the catalog. Open Connect → Catalogs and click + Catalog. Enter mysql_catalog as the Catalog name, keep Relational as the Catalog type, and choose MySQL under Databases and warehouses in Select provider.

2. Enter the connection. Under Connection, enter jdbc:mysql://{mysql_host}:3306 as the URL, and the User and Password of the MySQL account Gravitino connects as. The UI fills in jdbc-driver from the provider.

3. Test and create it. Click Test connection, then Create catalog.

4. List the schemas. In Explore, open mysql_catalog. The schemas listed are the MySQL databases, read from the server, with the system databases filtered out.

REST

1. Create the catalog. The public REST endpoint does not fill in jdbc-driver, so the request names it.

   Create a MySQL catalog

   curl -sS -X POST "https://{gravitino_host}/api/metalakes/{metalake}/catalogs" \
     -H "Accept: application/vnd.gravitino.v1+json" \
     -H "Content-Type: application/json" \
     -H "Authorization: Bearer $TOKEN" \
     -d '{
       "name": "mysql_catalog",
       "type": "RELATIONAL",
       "provider": "jdbc-mysql",
       "comment": "MySQL catalog",
       "properties": {
         "jdbc-url": "jdbc:mysql://{mysql_host}:3306",
         "jdbc-user": "{mysql_user}",
         "jdbc-password": "{mysql_password}",
         "jdbc-driver": "com.mysql.cj.jdbc.Driver"
       }
     }'

2. List the schemas. They are the MySQL databases, read from the server, with the system databases filtered out.

   List the schemas in the catalog

   curl -sS "https://{gravitino_host}/api/metalakes/{metalake}/catalogs/mysql_catalog/schemas" \
     -H "Accept: application/vnd.gravitino.v1+json" \
     -H "Authorization: Bearer $TOKEN"

Catalog properties

The MySQL catalog defines no properties of its own. It takes the jdbc-url, jdbc-driver, jdbc-user, jdbc-password, and pool properties described in Shared properties.

Use com.mysql.cj.jdbc.Driver as jdbc-driver. The URL names the server, such as jdbc:mysql://{mysql_host}:3306, and does not need a database, because the catalog lists every database the connecting account can see.

Driver version compatibility

The catalog requires MySQL Connector/J 8.0 or later. With an older driver the catalog fails to initialize with Mysql catalog does not support the jdbc driver version {version}, minimal required version is 8.0. The chart supplies Connector/J 8.0.33.

Connector/J releases before 8.0.16 do not report datetime precision reliably. With a driver whose version string starts with mysql-connector-java- and is below 8.0.16, Gravitino reads TIME, TIMESTAMP, and DATETIME columns without a precision and logs a warning for each one rather than returning a wrong precision.

Example warning log

WARN: MySQL driver version mysql-connector-java-8.0.11 is below 8.0.16,
columnSize may not be accurate for precision calculation.
Returning null for TIMESTAMP type precision. Driver version: mysql-connector-java-8.0.11

Schemas

A schema in this catalog is a MySQL database. Create, list, load, and drop are supported; alter is not. Creation issues CREATE DATABASE, and a schema comment or any schema property is rejected, because MySQL databases take neither.

Drop without cascade fails when the database still holds tables. Drop with cascade issues DROP DATABASE, which removes the database and every table in it.

Four names are reserved and cannot be used for a schema, compared without regard to case: mysql, information_schema, performance_schema, and sys. The same four are filtered out of schema listings.

Tables

Table create, load, list, rename, alter, and drop are supported. Purge is not: a purge request is rejected, so use drop. Listing returns base tables only, not views.

Partitioning, distribution, and sort orders are not supported, and a create that carries any of them is rejected. Table and column comments are written as MySQL COMMENT clauses.

Gravitino appends an identifier to the comment of each table it creates, in the form (From Gravitino, DO NOT EDIT: gravitino.v1.uid{id}). The UI and the API strip it from the comment they show. Leave it in place when you edit a table comment directly in MySQL.

Properties

Two table properties map to MySQL table options. Both can be set only when the table is created.

Property	MySQL option	Values
engine	ENGINE	InnoDB, MyISAM, MEMORY, CSV, ARCHIVE, BLACKHOLE, FEDERATED, ndbcluster, ndbinfo, MRG_MYISAM, or PERFORMANCE_SCHEMA
auto-increment-offset	AUTO_INCREMENT	An integer, the first value the auto-increment column assigns

When engine is omitted, MySQL uses its default storage engine. Some engines, such as FEDERATED, ndbinfo, MRG_MYISAM, and PERFORMANCE_SCHEMA, are not enabled by default and need server configuration first; FEDERATED, for example, needs federated=1 in the MySQL configuration file. See the MySQL documentation.

A loaded table reports engine, and auto-increment-offset when MySQL holds a value for it, both read from SHOW TABLE STATUS. The reported auto-increment-offset is the next value MySQL will assign, not necessarily the one given at creation.

Setting either property on an existing table is rejected, because both are immutable, and removing a property is rejected. A property the catalog does not declare is dropped without being written to MySQL.

Indexes

Primary key and unique key are supported, at creation and through an alter, and both are read back from MySQL. A primary key must be named PRIMARY or left unnamed; any other name is rejected. At creation, every primary key column must be declared not null. An index on a nested field is rejected.

Define primary and unique key indexes

{
  "indexes": [
    {
      "indexType": "primary_key",
      "name": "PRIMARY",
      "fieldNames": [["id"]]
    },
    {
      "indexType": "unique_key",
      "name": "id_name_uk",
      "fieldNames": [["id"], ["name"]]
    }
  ]
}

Deleting an index issues DROP INDEX. A delete without the if-exists flag fails when the table has no index of that name.

Auto increment

A table can have one auto-increment column, and it must belong to the primary key or to a unique key, or creation is rejected. An auto-increment column in a unique key must be not null. Adding an auto-increment column or turning auto increment on through an alter requires an Integer or Long column. Set the first value with the auto-increment-offset table property.

Define an auto-increment column

{
  "columns": [
    {
      "name": "id",
      "type": "integer",
      "comment": "id column comment",
      "nullable": false,
      "autoIncrement": true
    },
    {
      "name": "name",
      "type": "varchar(500)",
      "comment": "name column comment",
      "nullable": true,
      "autoIncrement": false
    }
  ],
  "indexes": [
    {
      "indexType": "primary_key",
      "name": "PRIMARY",
      "fieldNames": [["id"]]
    }
  ]
}

Supported changes

Change	Supported
Rename table	Yes, as the only change in its request
Update table comment	Yes
Add column	Yes, first, after a named column, or at the default position
Delete column	Yes
Rename column	Yes
Update column type	Yes, and the column's default is dropped
Update column nullable	Yes
Update column default	Yes
Update column comment	Yes
Update column position	Yes
Update column auto increment	Yes, and turning it on needs an Integer or Long column
Add or delete index	Yes, for primary key and unique key
Set property	No, both declared properties are immutable
Remove property	No

All changes in one request other than a rename are combined into one ALTER TABLE statement.

Updating a column's type redefines the column without a default, so set the default again in a separate request afterward.

Changing a column from nullable to not null is rejected when its default is NULL. A nullable column created without a default reads back with a NULL default, so give it a non-null default first.

Nested column names are rejected by every column change.

Identifier case and quoting

Gravitino does not fold the case of MySQL names. A name is sent to MySQL as given, quoted with backticks, and a table or column is matched by its exact name when it is loaded or altered.

Schema, table, and column names are 1 to 64 characters of letters from any script, digits, underscores, hyphens, $, /, or =. A backtick cannot appear in a name, so no escaping is needed inside the quotes.

Whether MySQL itself treats names as case-sensitive depends on the server's lower_case_table_names setting. When it is 1, MySQL stores database and table names in lowercase, so use lowercase names through Gravitino as well; a mixed-case name is stored in lowercase and no longer matches when the table is loaded.

Type mapping

Gravitino reads each MySQL type as the Gravitino type in its row, and creates each Gravitino type as the MySQL type in its row, except where the list under the table says otherwise.

MySQL type	Gravitino type
bit, bit(1)	Boolean
tinyint, tinyint unsigned	Byte, unsigned Byte
smallint, smallint unsigned	Short, unsigned Short
int, int unsigned	Integer, unsigned Integer
bigint, bigint unsigned	Long, unsigned Long
float	Float
double	Double
decimal(p,s), decimal(p,s) unsigned	Decimal(p, s)
date	Date
time(p)	Time, precision p
timestamp(p)	Timestamp with time zone, precision p
datetime(p)	Timestamp without time zone, precision p
char(n)	Char(n)
varchar(n)	Varchar(n)
text	String
binary, binary(1)	Binary
bit(n) where n is 2 or more	External type bit(n)
binary(n) where n is 2 or more	External type binary(n)
varbinary(n)	External type varbinary(n)
enum(...), set(...)	External type carrying the full definition
Any other type	External type carrying the MySQL type name

MySQL's timestamp converts values to UTC for storage and back to the session time zone on retrieval, while datetime does not, so timestamp maps to the time-zone-aware Gravitino type.

The MySQL driver reports binary(n), varbinary(n), bit(n), enum, and set without their length or values, so Gravitino reads the full definition from information_schema.columns and carries it as an external type. Other external types, such as mediumint, json, blob, and longtext, carry the type name alone.

When Gravitino creates a column:

• Time and both timestamp types without a precision become time, timestamp and datetime.
• Binary becomes binary with no length, which MySQL creates as a one-byte binary(1) column. Use an external type such as varbinary(255) or blob for variable-length binary data.
• An external type becomes the MySQL type it carries, so any MySQL type can be created by name.
• Fixed, UUID, List, Map, Struct, Union and the interval types have no mapping and are rejected.

Column default values

A default that starts with CURRENT_TIMESTAMP, with or without a fractional-second precision, is read back as the current timestamp default. NULL is read back as a null literal. A default on a numeric, decimal, date, time, timestamp, datetime, char, varchar, or text column is read back as a literal of the matching type, and a default on any other type is preserved as an unparsed expression.

When Gravitino writes a default, the current timestamp is written as CURRENT_TIMESTAMP, any other function expression is written inside parentheses, numeric literals are written bare, and all other literals are written in single quotes.

Error mapping

MySQL error codes are translated into Gravitino exceptions so that a caller sees the same failure types it would from any other catalog.

MySQL error	Gravitino exception
1007 database exists	Schema already exists
1008 cannot drop database, it does not exist	No such schema
1049 unknown database	No such schema
1050 table already exists	Table already exists
1051 unknown table	No such table
1146 table does not exist	No such table
Any error whose message contains Access denied	Connection failed

Any other error code produces a generic runtime exception carrying the MySQL message.

Related

• JDBC catalogs
• Catalog connections
• Tables and views