11.2.2.1 Lakehouse Limitations for all File Formats

MySQL HeatWave Lakehouse has the following limitations for all file formats. Review Other Limitations for all MySQL HeatWave query-related limitations.

Unsupported Items

MySQL HeatWave Lakehouse does not support the following:

• DML statements:

  • INSERT

  • UPDATE

  • DELETE

  • REPLACE

• The CREATE TABLESPACE statement.

• The following options for the CREATE TABLE statement:

  • AUTOEXTEND_SIZE

  • AVG_ROW_LENGTH

  • CHECKSUM

  • COMPRESS

  • CONNECTION

  • DATADIR

  • DELAY_KEY_WRITE

  • ENCRYPT

  • INDEXDIR

  • INSERT_METHOD

  • KEY_BLOCK_SIZE

  • MAX_ROWS

  • MIN_ROWS

  • PACK_KEYS

  • PASSWORD

  • ROW_FORMAT

  • STATS_AUTO_RECALC

  • STATS_PERSISTENT

  • STATS_SAMPLE_PAGES

  • UNION

• The default expression for a column definition for the CREATE TABLE statement.

• Creating temporary tables.

• Creating AUTO_INCREMENT columns.

  Therefore, Lakehouse is not compatible with REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE.

• Creating triggers.

• Running ANALYZE TABLE.

• Running ALTER TABLE statements that construct indexes, ADD or DROP columns, or add enforced check constraints.

• SELECT statements without RAPID as the secondary engine.

• Hidden columns.

• Index construction.

• Keys with column prefixes.

• Running CREATE TABLE or ALTER TABLE statement containing a STORAGE clause in the column attributes of a Lakehouse table.

• The use of CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP or NOW() as a default value for a timestamp column. Also an UPDATE statement with CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP or NOW(). Enable explicit_defaults_for_timestamp to use ALTER TABLE and CREATE TABLE statements with Lakehouse tables that have a timestamp column.

• A zone map on a JSON column.

• Stored generated columns in the JSON file format, unstructured format, Delta Lake tables, or the VECTOR data type.

• Stored generated columns over NOT SECONDARY columns.

• Stored generated columns over stored generated columns.

Other Limitations

MySQL HeatWave Lakehouse has the following limitations:

• As of MySQL 9.3.1, Lakehouse tables have the following column width limitations based on data type:

  • MEDIUMTEXT, LONGTEXT, and JSON: 4192192 bytes

  • TEXT: 65535 bytes

  • CHAR, VARCHAR, and VECTOR: 65532 bytes

    Note

    • Column width limitations apply after Lakehouse extracts and cleans data from files, and in the case of JSON data after converting it into JSON DOM representation internally.

    • The maximum row width is 4 MB. This includes the size of all the data and column metadata stored in the row. You cannot load a Lakehouse table that has multiple columns with a combined data size greater than 4 MB. This applies to intermediate relations and final query results as well.

  Before MySQL 9.3.1, the maximum column width is 65532 bytes.

• Do not create Lakehouse tables on the source DB in a replicated DB System if any of the replicas are outside MySQL HeatWave on OCI or MySQL HeatWave on AWS. This causes replication errors.

• Before MySQL 8.4.0-u2, a replication channel might fail if a MySQL HeatWave Cluster is added to a replica of a DB System, and later manually stopped.

• It is not possible to dump external tables using the MySQL Shell export utilities, such as dumpInstance(). External Lakehouse tables are not replicated to DB System storage and cannot be exported. To export DB System data from a Lakehouse enabled database, exclude the external tables with an excludeTables option.

• It is not possible to restore a backup from a Lakehouse enabled DB System to a standalone DB System.

• A Lakehouse enabled DB System can support a maximum of 512 nodes.

• Before MySQL 8.4.0, Lakehouse does not enforce any specified constraints on primary key, unique key, foreign key, and CHECK constraints. MySQL 8.4.0 removes this limitation for primary key and unique key constraints.

• Before MySQL 9.0.1-u1, the limit for the Lakehouse error message count is 100. As of MySQL 9.0.1-u1, Lakehouse supports max_error_count.

• The lakehouse_filter_warning_codes_list session variable has a limit of 50 codes and 250 characters.

• You can create up to 16 stored generated columns.

• The output of a stored generated column using const can be up to 1024 bytes.